@mattab opened this Issue on July 9th 2015 Owner

Few users reported having this error after upgrading to 2.14.0: Column not found: 1054 Unknown column 'log_visit.location_browser_lang' in 'field list'"

in this forum post and this one and few other ones.

The workaround is to manually run the SQL query: ALTER TABLE piwik_log_visit ADD COLUMN location_browser_lang VARCHAR(20) NOT NULL;

In general, I'm wondering why some users have this problem? currently I don't understand how this could occur.

@benjaminpick commented on July 9th 2015

For me it is the column 'log_visit.config_os' that is missing since the update ... (I also had to add config_browser_name, config_browser_version, and some of the other config-columns. I updated by uploading the files via FTP.)

@mattab commented on July 9th 2015 Owner

@benjaminpick is your Administration > System check showing green ticks everywhere?

To fix the issue, try this workaround: http://forum.piwik.org/read.php?2,127838,page=1#msg-127967

@benjaminpick commented on July 9th 2015

I see an error message behind: always_populate_raw_post_data=-1 . All the other checks are OK.
Also, he is complaining that I didn't upload the .phpstorm.meta.php but I guess this is irrelevant.

@chriscroome commented on July 9th 2015

I have also found this forum post, I tried posting a follow up there but got this error message:

Les informations que vous avez envoy�es ont �t� rejet�es, parce qu'elles semblent envoy�es par un robot d'envois automatiques. Si votre navigateur n'a pas de javascript, ou s'il est d�sactiv�, cela peut-�tre la raison de l'�chec. Le Javavascript doit �tre activ� pour que ce formulaire fonctionne.

I have green ticks for everything in the System Check, the error message via cron, recieved soon after the upgrade to 2.14.0:

ERROR CoreConsole[2015-07-09 11:05:07] Got invalid response from API request: ?module=API&method=API.get&idSite=1&period=day&date=last52&format=php&trigger=archivephp. Response was 'a:2:{s:6:"result";s:5:"error";s:7:"message";s:104:"SQLSTATE[42S22]: Column not found: 1054 Unknown column 'log_visit.location_browser_lang' in 'field list'";}'
ERROR CoreConsole[2015-07-09 11:05:07] Empty or invalid response '' for website id 1, Time elapsed: 2.943s, skipping
ERROR CoreConsole[2015-07-09 11:05:09] Got invalid response from API request: ?module=API&method=API.get&idSite=11&period=day&date=last52&format=php&trigger=archivephp. Response was 'a:2:{s:6:"result";s:5:"error";s:7:"message";s:104:"SQLSTATE[42S22]: Column not found: 1054 Unknown column 'log_visit.location_browser_lang' in 'field list'";}'
ERROR CoreConsole[2015-07-09 11:05:09] Empty or invalid response '' for website id 11, Time elapsed: 2.274s, skipping
ERROR CoreConsole[2015-07-09 11:05:09] 4 total errors during this script execution, please investigate and try and fix these errors.

  [Exception]
  4 total errors during this script execution, please investigate and try and fix these errors.

core:archive [--url="..."] [--force-all-websites] [--force-all-periods[="..."]] [--force-timeout-for-periods[="..."]] [--skip-idsites[="..."]] [--skip-all-segments] [--force-idsites[="..."]] [--force-periods[="..."]] [--force-date-last-n="..."] [--force-date-range[="..."]] [--force-idsegments="..."] [--concurrent-requests-per-website[="..."]] [--disable-scheduled-tasks] [--accept-invalid-ssl-certificate]

The steps I followed doing the upgrade are documented here.

@mattab commented on July 9th 2015 Owner

@chriscroome check your server error log, what is the error in there?

@chriscroome commented on July 9th 2015

I tried the suggestion in #issue-94023907

mysql> ALTER TABLE piwik_log_visit ADD COLUMN location_browser_lang VARCHAR(20) NOT NULL;
ERROR 1146 (42S02): Table 'stats.piwik_log_visit' doesn't exist
mysql> ALTER TABLE log_visit ADD COLUMN location_browser_lang VARCHAR(20) NOT NULL;
Query OK, 2484765 rows affected (1 min 45.87 sec)
Records: 2484765  Duplicates: 0  Warnings: 0

There is nothing in the Nginx error log, the php-fpm log, in the Piwik archive log there is this:

INFO CoreConsole[2015-07-09 10:05:16] Error: Empty or invalid response '' for website id 11, Time elapsed: 1.357s, skipping
INFO CoreConsole[2015-07-09 11:05:01] ---------------------------
INFO CoreConsole[2015-07-09 11:05:01] INIT
INFO CoreConsole[2015-07-09 11:05:01] Running Piwik 2.14.0 as Super User
INFO CoreConsole[2015-07-09 11:05:01] ---------------------------
INFO CoreConsole[2015-07-09 11:05:01] NOTES
INFO CoreConsole[2015-07-09 11:05:01] - Reports for today will be processed at most every 1800 seconds. You can change this value in Piwik UI > Settings > General Settings.
INFO CoreConsole[2015-07-09 11:05:01] - Reports for the current week/month/year will be refreshed at most every 3600 seconds.
INFO CoreConsole[2015-07-09 11:05:01] - Archiving was last executed without error 1 hours 58 min ago
INFO CoreConsole[2015-07-09 11:05:03] - Will process 2 websites with new visits since 1 hours 58 min , IDs: 1, 11
INFO CoreConsole[2015-07-09 11:05:03] ---------------------------
INFO CoreConsole[2015-07-09 11:05:03] START
INFO CoreConsole[2015-07-09 11:05:03] Starting Piwik reports archiving...
INFO CoreConsole[2015-07-09 11:05:04] Will pre-process for website id = 1, period = day, date = last52
INFO CoreConsole[2015-07-09 11:05:04] - pre-processing all visits
INFO CoreConsole[2015-07-09 11:05:07] Will pre-process for website id = 11, period = day, date = last52
INFO CoreConsole[2015-07-09 11:05:07] - pre-processing all visits
INFO CoreConsole[2015-07-09 11:05:09] Done archiving!
INFO CoreConsole[2015-07-09 11:05:09] ---------------------------
INFO CoreConsole[2015-07-09 11:05:09] SUMMARY
INFO CoreConsole[2015-07-09 11:05:09] Total visits for today across archived websites: 0
INFO CoreConsole[2015-07-09 11:05:09] Archived today's reports for 0 websites
INFO CoreConsole[2015-07-09 11:05:09] Archived week/month/year for 0 websites
INFO CoreConsole[2015-07-09 11:05:09] Skipped 13 websites: no new visit since the last script execution
INFO CoreConsole[2015-07-09 11:05:09] Skipped 0 websites day archiving: existing daily reports are less than 1800 seconds old
INFO CoreConsole[2015-07-09 11:05:09] Skipped 0 websites week/month/year archiving: existing periods reports are less than 3600 seconds old
INFO CoreConsole[2015-07-09 11:05:09] Total API requests: 0
INFO CoreConsole[2015-07-09 11:05:09] done: 0/2 0%, 0 vtoday, 0 wtoday, 0 wperiods, 0 req, 5948 ms, 4 errors.
INFO CoreConsole[2015-07-09 11:05:09] Time elapsed: 5.949s
INFO CoreConsole[2015-07-09 11:05:09] ---------------------------
INFO CoreConsole[2015-07-09 11:05:09] SCHEDULED TASKS
INFO CoreAdminHome[2015-07-09 11:05:09] Starting Scheduled tasks...
INFO CoreAdminHome[2015-07-09 11:05:09] Scheduler: executing task Piwik\Plugins\ExamplePlugin\Tasks.myTask...
INFO CoreAdminHome[2015-07-09 11:05:09] Scheduler: finished. Time elapsed: 0.000s
INFO CoreAdminHome[2015-07-09 11:05:09] done
INFO CoreConsole[2015-07-09 11:05:09] ---------------------------
INFO CoreConsole[2015-07-09 11:05:09] ---------------------------
INFO CoreConsole[2015-07-09 11:05:09] SUMMARY OF ERRORS
INFO CoreConsole[2015-07-09 11:05:09] Error: Got invalid response from API request: ?module=API&method=API.get&idSite=1&period=day&date=last52&format=php&trigger=archivephp. Response was 'a:2:{s:6:"result";s:5:"error";s:7:"message";s:104:"SQLSTATE[42S22]: Column not found: 1054 Unknown column 'log_visit.location_browser_lang' in 'field list'";}'
INFO CoreConsole[2015-07-09 11:05:09] Error: Empty or invalid response '' for website id 1, Time elapsed: 2.943s, skipping
INFO CoreConsole[2015-07-09 11:05:09] Error: Got invalid response from API request: ?module=API&method=API.get&idSite=11&period=day&date=last52&format=php&trigger=archivephp. Response was 'a:2:{s:6:"result";s:5:"error";s:7:"message";s:104:"SQLSTATE[42S22]: Column not found: 1054 Unknown column 'log_visit.location_browser_lang' in 'field list'";}'
INFO CoreConsole[2015-07-09 11:05:09] Error: Empty or invalid response '' for website id 11, Time elapsed: 2.274s, skipping
@mattab commented on July 9th 2015 Owner

@chriscroome your tables don't have piwik_ prefix so try: ALTER TABLE log_visit ADD COLUMN location_browser_lang VARCHAR(20) NOT NULL;

@chriscroome commented on July 9th 2015

Right, if you look at the comment above I did run the SQL without the table prefix.

Site ID 11 uses the WP-Piwik WordPress module: http://reconomy.org/

@mattab commented on July 9th 2015 Owner

If you get error Unknown column 'log_visit.location_browser_lang' in 'field list' then somehow it didn't work, can you try again or verify this is the error message you still get?

@chriscroome commented on July 9th 2015

The error message above was from before the SQL was run -- the SQL appears to have run without a problem.

The cron job, /etc/cron.d/piwik-archive contains:

MAILTO="root<a class='mention' href='https://github.com/localhost'>@localhost</a>"
5 2-23 * * * www-data /web/stats.transitionnetwork.org/piwik/console core:archive --url=http://stats.transitionnetwork.org/ >> /var/log/piwik-archive.log
@chriscroome commented on July 9th 2015

Running the cron job manually:

su - www-data -s /bin/bash
$ /web/stats.transitionnetwork.org/piwik/console core:archive --url=http://stats.transitionnetwork.org/ >> /var/log/piwik-archive.log
ERROR CoreConsole[2015-07-09 12:04:25] Got invalid response from API request: ?module=API&method=API.get&idSite=1&period=day&date=last52&format=php&trigger=archivephp. Response was 'a:2:{s:6:"result";s:5:"error";s:7:"message";s:92:"SQLSTATE[42S22]: Column not found: 1054 Unknown column 'log_visit.config_os' in 'field list'";}'
ERROR CoreConsole[2015-07-09 12:04:25] Empty or invalid response '' for website id 1, Time elapsed: 1.724s, skipping
ERROR CoreConsole[2015-07-09 12:04:27] Got invalid response from API request: ?module=API&method=API.get&idSite=11&period=day&date=last52&format=php&trigger=archivephp. Response was 'a:2:{s:6:"result";s:5:"error";s:7:"message";s:92:"SQLSTATE[42S22]: Column not found: 1054 Unknown column 'log_visit.config_os' in 'field list'";}'
ERROR CoreConsole[2015-07-09 12:04:27] Empty or invalid response '' for website id 11, Time elapsed: 1.754s, skipping
ERROR CoreConsole[2015-07-09 12:04:27] 4 total errors during this script execution, please investigate and try and fix these errors.

  [Exception]                                                                                    
  4 total errors during this script execution, please investigate and try and fix these errors.  

core:archive [--url="..."] [--force-all-websites] [--force-all-periods[="..."]] [--force-timeout-for-periods[="..."]] [--skip-idsites[="..."]] [--skip-all-segments] [--force-idsites[="..."]] [--force-periods[="..."]] [--force-date-last-n="..."] [--force-date-range[="..."]] [--force-idsegments="..."] [--concurrent-requests-per-website[="..."]] [--disable-scheduled-tasks] [--accept-invalid-ssl-certificate]
@lolalalol commented on July 9th 2015

Same for me. Upgrade from 2.8 to 2.14 and fields were missing in table log_visit.
This solution worked for me to : http://forum.piwik.org/read.php?2,127838,page=1#msg-127877

@heidarymr commented on July 10th 2015

i have this problem too:Column not found: 1054 Unknown column 'log_visit.location_browser_lang' in 'field list'"
and run this query: ALTER TABLE piwik_log_visit ADD COLUMN location_browser_lang VARCHAR(20) NOT NULL;
but i get this error: <a href='/1060'>#1060</a> - Duplicate column name 'location_browser_lang'

@chriscroome commented on July 13th 2015

I ran this SQL to fix the issue:

mysql> ALTER TABLE log_visit ADD COLUMN config_os char(3) NOT NULL;
Query OK, 2484765 rows affected (1 min 21.69 sec)
Records: 2484765  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE log_visit ADD COLUMN config_browser_name varchar(10) NOT NULL;
Query OK, 2484765 rows affected (1 min 3.00 sec)
Records: 2484765  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE log_visit ADD COLUMN config_browser_version varchar(20) NOT NULL;
Query OK, 2484765 rows affected (1 min 21.36 sec)
Records: 2484765  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE log_visit ADD COLUMN config_resolution varchar(9) NOT NULL;
Query OK, 2484765 rows affected (1 min 8.46 sec)
Records: 2484765  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE log_visit ADD COLUMN config_pdf tinyint(1) NOT NULL;
Query OK, 2484765 rows affected (59.04 sec)
Records: 2484765  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE log_visit ADD COLUMN config_flash tinyint(1) NOT NULL;
Query OK, 2484765 rows affected (1 min 4.59 sec)
Records: 2484765  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE log_visit ADD COLUMN config_director tinyint(1) NOT NULL;
Query OK, 2484765 rows affected (1 min 13.14 sec)
Records: 2484765  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE log_visit ADD COLUMN config_quicktime tinyint(1) NOT NULL;
Query OK, 2484765 rows affected (1 min 29.80 sec)
Records: 2484765  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE log_visit ADD COLUMN config_realplayer tinyint(1) NOT NULL;
Query OK, 2484765 rows affected (1 min 8.96 sec)
Records: 2484765  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE log_visit ADD COLUMN config_windowsmedia tinyint(1) NOT NULL;
Query OK, 2484765 rows affected (1 min 0.62 sec)
Records: 2484765  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE log_visit ADD COLUMN config_gears tinyint(1) NOT NULL;
Query OK, 2484765 rows affected (1 min 2.21 sec)
Records: 2484765  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE log_visit ADD COLUMN config_silverlight tinyint(1) NOT NULL;
Query OK, 2484765 rows affected (1 min 21.97 sec)
Records: 2484765  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE log_visit ADD COLUMN config_java tinyint(1) NOT NULL;
Query OK, 2484765 rows affected (1 min 13.32 sec)
Records: 2484765  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE log_visit ADD COLUMN config_cookie tinyint(1) NOT NULL; 
Query OK, 2484765 rows affected (1 min 6.25 sec)
Records: 2484765  Duplicates: 0  Warnings: 0

The cron job now runs without any errors.

@SteffiMania commented on July 13th 2015

Hi!

Same me:

Column not found: 1054 Unknown column 'log_visit.location_browser_lang' in 'field list'"
and run this query: ALTER TABLE piwik_log_visit ADD COLUMN location_browser_lang VARCHAR(20) NOT NULL;
but i get this error: #1060 - Duplicate column name 'location_browser_lang'

what can I do?

@mattab commented on July 15th 2015 Owner

There are two possible reasons for this bug (experienced by many users):

  • Either the columns were never created (for some reason)
  • Or the column got deleted during upgrade (for some reason)

Would anyone in this thread know whether you used to have those columns, or do you know whether these columns were never there in your tables?

@benjaminpick commented on July 15th 2015

Yes these columns were there before. (I don't have a backup to compare
but there was definitly OS information on my data before, which I lost
during the upgrade.)

@mattab commented on July 16th 2015 Owner

@benjaminpick and everyone: which version of Piwik did you upgraded from?

We will investigate this issue for sure!

@sgiehl commented on July 16th 2015 Member

I've asked that in the german forums... Most of them updated from 2.13.1, but some also from older versions like 2.10.0

@mattab commented on July 16th 2015 Owner

Hi @tsteur
We have a possibly serious issue with the columns being dropped.
Could you investigate / try to reproduce this bug when you're back?

Note: these columns were moved from one plugin to another, maybe this bug occurs when column/dimensions are moved from one plugin to another, and our column detector somehow gets confused and drops the columns? The weird thing is that it only affects a few users and not all consistently.

added Major label for this bug

@oparoz commented on July 16th 2015

Same problem: Upgrade from 2.13.x. PHP 5.4, MariaDB 10 with custom prefix.

@chriscroome commented on July 16th 2015

We have plain text backups of our MySQL database, looking in the oldest one (2015-05-18) and grepping for one of the missing columns (config_os) confirms that they were there prior to the upgrade that removed them:

  `config_os` char(3)   `config_os` char(3) NOT NULL,
  `config_os_version` varchar(100) DEFAULT NULL, INSERT INTO `log_visit` (`idvisit`, `idsite`, `idvisitor`, `visitor_localtime`, `visitor_returning`, `visitor_count_visits`, `visitor_days_since_last`, `visitor_days_since_order`, `visitor_days_since_first`, `visit_first_action_time`, `visit_last_action_time`, `visit_exit_idaction_url`, `visit_exit_idaction_name`, `visit_entry_idaction_url`, `visit_entry_idaction_name`, `visit_total_actions`, `visit_total_searches`, `visit_total_events`, `visit_total_time`, `visit_goal_converted`, `visit_goal_buyer`, `referer_type`, `referer_name`, `referer_url`, `referer_keyword`, `config_id`, `config_os`, `config_os_version`, `config_browser_name`, `config_browser_version`, `config_device_type`, `config_device_brand`, `config_device_model`, `config_resolution`, `config_pdf`, `config_flash`, `config_java`, `config_director`, `config_quicktime`, `config_realplayer`, `config_windowsmedia`, `config_gears`, `config_silverlight`, `config_cookie`, `location_ip`, `location_browser_lang`, `location_country`, `location_region`, `location_city`, `location_latitude`, `location_longitude`, `custom_var_k1`, `custom_var_v1`, `custom_var_k2`, `custom_var_v2`, `custom_var_k3`, `custom_var_v3`, `custom_var_k4`, `custom_var_v4`, `custom_var_k5`, `custom_var_v5`, `location_provider`, `location_geoip_continent`, `location_geoip_country`, `location_geoip_city`, `location_geoip_latitude`, `location_geoip_longitude`, `user_id`, `config_browser_engine`) VALUES NOT NULL, ...
@Kousaeng commented on July 16th 2015

We have the same problem: Upgrade from 2.13.0 with "The Manual Three-Step Update", MySQLDB

@chriscroome commented on July 16th 2015

"maybe this bug occurs when column/dimensions are moved from one plugin to another, and our column detector somehow gets confused and drops the columns?"

The server it happened to me on has no plugins installed.

@chriscroome commented on July 16th 2015

The server it happened to me on is documented here, in case if you need more details:

The steps followed for the last upgrade are documented here:

@ChristianGfK commented on July 17th 2015

We also experienced this after upgrading to 2.14.0.
Kind of scary to see this when looking at your stats:
bpmaood

I added the columns mentioned in the forum post and visitor tracking resumed.
Is the data collected between upgrading to 2.14.0 (yesterday midday) and me noticing and doing this workaround lost?
Was there even data collected? How do I check?

Also, the post mentions something about software/OS statistics being lost/dropped. That doesn't appear to be the case for me, but can I check and make sure?

I do have daily mysql dumps available going back a month, if necessary.

@chriscroome commented on July 17th 2015

I have the same, no stats between the upgrade to 2.14.0 and before the manual SQL updates:

stats

@quba commented on July 17th 2015 Contributor

@chriscroome it should be possible to replay this date range from access log files.

@ChristianGfK commented on July 17th 2015

@quba Meaning that Piwik did actually not track anything in this timeframe? It's not just a display issue?

What about the data stored in those fields that were dropped? That's probably lost forever?

@quba commented on July 17th 2015 Contributor

If this column was actually dropped then the only option is to restore this data from a backup/access log files. Reports for historical data are most likely aggregated so you will have access to these information. It won't work only in case you decide to rearchive something or if you decide to create a new segment and would like to have also old data segmented (to display segmented report that was created by using this column's data or to segment using values from this particular column).

@ChristianGfK commented on July 17th 2015

We run an archiving job every hour, so loss should be minimized by that.

However, I tried replaying access logs (both the regular visits and the tracking API requests), and the visits contained in those seem to not show up at all, even after forcing archival again (as suggested by the log analytics script)?!

@quba commented on July 17th 2015 Contributor

Log import summary should show you how many requests were replayed. It's also possible that you have to invalidate this date range in order to re-archive. But before doing so, just confirm that there is raw data for this date range (you can calculate number of rows available e.g. in the log_visit table - please remember to use indexed fields - idsite and visit_last_action_time).

@zanematthew commented on July 17th 2015

+1 Same issue

@kbojens commented on July 17th 2015

Note: these columns were moved from one plugin to another, maybe this bug occurs when column/dimensions are moved from one plugin to another, and our column detector somehow gets confused and drops the columns? The weird thing is that it only affects a few users and not all consistently.

Well, that's what happenend during the update today (2.10 -> 2.14.1) and took hours:

ALTER TABLE piwik_log_visit DROP COLUMN location_browser_lang

After that the UserLanguage Plugin stopped working:

WARNING: /var/www/piwik/plugins/UserLanguage/Visitor.php(24): Notice - Undefined index: location_browser_lang - Piwik 2.14.1 - Please report this message in the Piwik forums: http://forum.piwik.org (please do a search first as it might have been reported already)

So, right now I am readding the column again like you proposed in the first post which will take me some hours.

@codifex commented on July 17th 2015

Hello, I have the same problem here (2.13.1 updated to 2.14 and also 2.13.1 updated to 2.14.1).
I am using a custom prefix on the tables.

The Piwik system test shows an error for 'always_populate_raw_post_data', no other errors.

The update was performed manually by overwriting the files on the server with the current version and performing the database update offered when accessing Piwik.

The columns were present prior to the update.

After performing the update in addition the icons for browser and OS of visits were missing and some other error messages/warnings appeared (e.g., when opening the page normally showing statistics for all websites a red Piwik warning was shown (server overload, contact admin if the problem occurs again), but only when selecting the current date).

@jpfleury commented on July 19th 2015

Same problem here after upgrading from Piwik 2.9.1.

@diosmosis commented on July 19th 2015 Member

@mattab Think I found the cause for this bug: https://github.com/piwik/piwik/blob/master/core/Updates/2.14.0-b1.php#L20

The update uninstalls the UserSettings plugin which shared dimensions w/ the various other plugins used now. If the dimension files still exist on an install, the Plugin manager will uninstall the dimensions, removing the columns.

FYI, can't work on a fix right now, hopefully this helps.

@ChristianGfK commented on July 20th 2015

@quba invalidating is something other than re-archiving?
In any case, even though the log analytics script claims to have imported ~11k visits, none are to be seen: https://gist.github.com/ChristianGfK/b97a258c868d7d04eac2
The date range begins when I updated from 2.13.1 to 2.14.0 and ends when I updated to 2.14.1, discovered this problem and added the missing columns back, so Piwik resumed recording visits.

I'm assuming this is not related to this issue, but more a general problem with the log analytics script?
It is keeping me from fixing this mess, though. :-|

@quba commented on July 20th 2015 Contributor

Here's the API call to invalidate reports (it means that data won't be deleted but re-archived during next archiving process):
index.php?module=API&method=CoreAdminHome.invalidateArchivedReports&format=xml&token_auth=&idSites=&dates=2015-07-12&period=day

However first check if you have raw data for the date range that you've imported (e.g. by using the visitor log).

@ChristianGfK commented on July 20th 2015

Thanks, I'll give that a try. My piwik_log_visit table contains data for the days in question (2015-07-16, 2015-07-17), just not enough data.
And import_logs.py seems to be in no mood at all to do what it's supposed to do.
It's like it's in a permanent dry-run mode. It claims it's importing requests, but they're not showing up in piwik_log_visit (I assume that's where they're supposed to go) or the web UI.

@tsteur commented on July 20th 2015 Owner

The update uninstalls the UserSettings plugin which shared dimensions w/ the various other plugins used now. If the dimension files still exist on an install, the Plugin manager will uninstall the dimensions, removing the columns.

Good find. In different versions we removed dimensions out of the UserSettings plugin. language was removed in 2.11.0, was there in https://github.com/piwik/piwik/tree/2.10.0/plugins/UserSettings/Columns and eg in 2.7.0 there were many: https://github.com/piwik/piwik/tree/2.7.0/plugins/UserSettings/Columns .

In theory, when we remove files, they should be removed on the a user's Piwik as well. Possibly this did not work in some cases on previous updates for various reasons. Maybe file permissions problems or maybe someone updated from a version that did not yet contain this logic of removing core files that no longer exist. In 2.14.0 we then uninstalled this plugin UserSettings and if there were still some dimensions in that plugin we removed it (which is correct behaviour in general).

Not sure what a proper fix for this issue could be. If files need to be removed during an update and it does not work, we should maybe display an error asking to remove those files manually. Maybe when working on #5985 we can take such things into consideration.

@skoub commented on July 20th 2015

after applying the alter query in the first post, i had another error: General error: 1364 Field 'location_browser_lang' doesn't have a default value. I had to reactivate the UserLanguage plugin to solve the error.

@tsteur commented on July 21st 2015 Owner

Not sure what a proper fix for this issue could be. If files need to be removed during an update and it does not work, we should maybe display an error asking to remove those files manually. Maybe when working on #5985 we can take such things into consideration.

Actually, one thing we could do before uninstalling a dimension is to check whether the same dimension is provided by another plugin (with DB field, $columnName & $columnType) and if so, not uninstall it. This would have prevented at least this "bug"

@tsteur commented on July 21st 2015 Owner

Just FYI: Another question is why the columns were not installed automatically afterwards again (although it was good in this case so we could find this issue). I'm working on a fix to prevent this issue in the future but noticed the entry in the option table was not removed. For the system it was still marked as installed. This might be some race condition with Updater and Plugin/Uninstall

@tsteur commented on July 21st 2015 Owner

FYI: I'm working on an update that will remove some option entries so the wrongly removed dimensions will be at least re-installed again automatically if still needed.

@quba commented on July 21st 2015 Contributor

Great! But for sure we need to make sure that such dimensions are not removed (in case other plugin implements them) because we don't want to lose raw data.

@tsteur commented on July 21st 2015 Owner

Sure thing. I issued a PR for both already

@mattab commented on July 24th 2015 Owner

Thanks everyone for reporting the issue - it has been fixed in #8389 and #8388 - we will release 2.14.2 shortly

@zanematthew commented on July 24th 2015

Great!

@bmoore9 commented on July 30th 2015

I am attempting a staged update from 2.13.1 to 2.14.2 and still getting this issue.

What the update says:
Database Upgrade Required

Your Piwik database is out-of-date, and must be upgraded before you can continue.

Piwik database will be upgraded from version 2.13.1 to the new version 2.14.2.

*** Note: this is a Dry Run ***

DROP TABLE IF EXISTS `piwik_site_setting`;
CREATE TABLE `piwik_site_setting` (
              idsite INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT,
              `setting_name` VARCHAR(255) NOT NULL,
              `setting_value` LONGTEXT NOT NULL,
                  PRIMARY KEY(idsite, setting_name)
                ) ENGINE=TokuDB DEFAULT CHARSET=utf8;

*** End of Dry Run ***

What is really happening though is an ALTER TABLE piwik_log_visit DROP COLUMN location_browser_lang. When I tried 2.14.1 it proceeded to drop other columns as well. I canceled this update so I don't know if it would continue to delete others.

Is the dropping of this column expected? If so, why doesn't it show up in the dry run?

I have no third party plugins and the only plugins that are disabled are the Example* and DBStats plugin.

@tsteur commented on July 31st 2015 Owner

Dropping of this column is not expected. It doesn't show up in the dry run because the updater disables a plugin which can lead to further queries in another update. As the plugin was not disabled yet when collecting all the queries for the dry run, those queries won't be visible in the initial one.

I'm not quite sure why it still wants to drop the column. Maybe the opcache was not invalidated correctly. Do you know if you disabled any plugins in your Piwik installation? Eg Resolution or DevicesDetection?

Maybe we could provide another update that forces the deletion of old "UserSettings Dimensions" just to be sure in case something else goes wrong. Referring once more to the refactoring of the updater in #5985

@bmoore9 commented on July 31st 2015

This is a list of the plugins activated on my system. It has been this way since I first installed Piwik just over a year ago. Resolution and DevicesDetection have always been enabled. I did follow through with the update on my dev setup and it actually only dropped the location_browser_lang column. When I tried upgrading previously to 2.14.1 there were a bunch more columns it removed from the piwik_log_visit table.

I just want to be able to successfully update to 2.14.2. If you have any suggestions of things I can try manually I have a dev setup I can try it on.

+--------------------+-------------------+-----------+
| Plugin | Core or optional? | Status |
+--------------------+-------------------+-----------+
| PrivacyManager | Core | Activated |
| CustomVariables | Core | Activated |
| ImageGraph | Core | Activated |
| Annotations | Core | Activated |
| Overlay | Core | Activated |
| MobileMessaging | Core | Activated |
| Live | Core | Activated |
| UserCountryMap | Core | Activated |
| Feedback | Core | Activated |
| Provider | Core | Activated |
| CoreUpdater | Core | Activated |
| CoreConsole | Core | Activated |
| ScheduledReports | Core | Activated |
| SegmentEditor | Core | Activated |
| Morpheus | Core | Activated |
| Ecommerce | Core | Activated |
| Insights | Core | Activated |
| UserLanguage | Core | Activated |
| Monolog | Core | Activated |
| Diagnostics | Core | Activated |
| DevicePlugins | Core | Activated |
| Resolution | Core | Activated |
| ZenMode | Core | Activated |
| DevicesDetection | Core | Activated |
| LeftMenu | Core | Activated |
| Contents | Core | Activated |
| BulkTracking | Core | Activated |
| ExampleRssWidget | Core | Activated |
| ExampleAPI | Core | Activated |
| API | Core | Activated |
| Proxy | Core | Activated |
| ExamplePlugin | Core | Activated |
| Widgetize | Core | Activated |
| Transitions | Core | Activated |
| CoreVisualizations | Core | Activated |
| CoreHome | Core | Activated |
| UsersManager | Core | Activated |
| CoreAdminHome | Core | Activated |
| SitesManager | Core | Activated |
| Installation | Core | Activated |
| CorePluginsAdmin | Core | Activated |
| LanguagesManager | Core | Activated |
| Actions | Core | Activated |
| VisitsSummary | Core | Activated |
| UserCountry | Core | Activated |
| VisitFrequency | Core | Activated |
| VisitTime | Core | Activated |
| VisitorInterest | Core | Activated |
| Events | Core | Activated |
| SEO | Core | Activated |
| MultiSites | Core | Activated |
| Dashboard | Core | Activated |
| Referrers | Core | Activated |
| UserSettings | Core | Activated |
| Goals | Core | Activated |
| Login | Core | Activated |
+--------------------+-------------------+-----------+

This Issue was closed on July 24th 2015
Powered by GitHub Issue Mirror