Column not found: 1054 Unknown column 'log_visit.location_browser_lang' in 'field list'" #8304

mattab · 2015-07-09T11:32:11Z

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.

The text was updated successfully, but these errors were encountered:

benjaminpick · 2015-07-09T11:37:30Z

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 · 2015-07-09T11:39:25Z

@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 · 2015-07-09T11:42:17Z

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 · 2015-07-09T11:44:05Z

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 · 2015-07-09T11:50:03Z

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

chriscroome · 2015-07-09T11:54:30Z

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 · 2015-07-09T11:57:04Z

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

chriscroome · 2015-07-09T11:58:32Z

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 · 2015-07-09T12:00:33Z

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 · 2015-07-09T12:03:08Z

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@localhost"
5 2-23 * * * www-data /web/stats.transitionnetwork.org/piwik/console core:archive --url=http://stats.transitionnetwork.org/ >> /var/log/piwik-archive.log

chriscroome · 2015-07-09T12:05:14Z

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 · 2015-07-09T13:08:26Z

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 · 2015-07-10T06:06:10Z

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: #1060 - Duplicate column name 'location_browser_lang'

chriscroome · 2015-07-13T10:34:01Z

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 · 2015-07-13T18:47:45Z

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 · 2015-07-15T12:24:00Z

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 · 2015-07-15T17:21:01Z

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 · 2015-07-16T08:06:50Z

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

We will investigate this issue for sure!

sgiehl · 2015-07-16T08:10:04Z

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 · 2015-07-16T08:14:37Z

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 · 2015-07-16T09:48:59Z

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

chriscroome · 2015-07-16T09:59:56Z

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 · 2015-07-16T10:45:08Z

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

quba · 2015-07-17T14:09:38Z

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 · 2015-07-17T15:29:38Z

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 · 2015-07-17T15:35:22Z

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 · 2015-07-17T16:00:36Z

+1 Same issue

ghost · 2015-07-17T18:51:47Z

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 · 2015-07-17T22:32:11Z

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 · 2015-07-19T05:30:39Z

Same problem here after upgrading from Piwik 2.9.1.

diosmosis · 2015-07-19T21:31:29Z

@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 · 2015-07-20T09:16:57Z

@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 · 2015-07-20T10:18:43Z

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 · 2015-07-20T10:43:33Z

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 · 2015-07-20T10:47:47Z

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.

alexandrejobin · 2015-07-20T17:00:29Z

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 · 2015-07-21T06:56:36Z

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 · 2015-07-21T08:07:44Z

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

…efines the same, make sure to correctly mark the dimension as uninstalled

…y uninstalled works

tsteur · 2015-07-21T09:56:27Z

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 · 2015-07-21T10:06:03Z

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.

…alled

tsteur · 2015-07-21T10:49:56Z

Sure thing. I issued a PR for both already

mattab · 2015-07-24T13:49:22Z

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

zanematthew · 2015-07-24T14:25:12Z

Great!

bmoore9 · 2015-07-30T17:45:44Z

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 · 2015-07-31T06:14:05Z

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 · 2015-07-31T15:17:23Z

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 |
+--------------------+-------------------+-----------+

mattab added the Bug For errors / faults / flaws / inconsistencies etc. label Jul 9, 2015

benjaminpick mentioned this issue Jul 9, 2015

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'log_visit.config_resolution' in 'field list' #8306

Closed

ThaDafinser mentioned this issue Jul 10, 2015

core:update verbosity + changelog announcement #8318

Closed

mattab added this to the Short term milestone Jul 15, 2015

mattab added the Major Indicates the severity or impact or benefit of an issue is much higher than normal but not critical. label Jul 16, 2015

mattab modified the milestones: 2.15.0, Short term Jul 16, 2015

tsteur added a commit that referenced this issue Jul 21, 2015

refs #8304 make sure to not uninstall a dimension if another plugin d…

764b3b8

…efines the same, make sure to correctly mark the dimension as uninstalled

This was referenced Jul 21, 2015

Set method Dimension::getId as final #8387

Merged

Uninstall dimension only if not defined by another plugin #8388

Merged

tsteur added a commit that referenced this issue Jul 21, 2015

refs #8304 added a test to make sure marking component as successfull…

b02a94d

…y uninstalled works

tsteur added a commit that referenced this issue Jul 21, 2015

refs #8304 fix some dimensions were not marked as successfully uninst…

d1bf938

…alled

tsteur mentioned this issue Jul 21, 2015

Update to fix some dimensions were not marked as successfully uninstalled #8389

Merged

mattab closed this as completed Jul 24, 2015

Column not found: 1054 Unknown column 'log_visit.location_browser_lang' in 'field list'" #8304

Column not found: 1054 Unknown column 'log_visit.location_browser_lang' in 'field list'" #8304

Comments

mattab commented Jul 9, 2015

benjaminpick commented Jul 9, 2015

mattab commented Jul 9, 2015

benjaminpick commented Jul 9, 2015

chriscroome commented Jul 9, 2015

mattab commented Jul 9, 2015

chriscroome commented Jul 9, 2015

mattab commented Jul 9, 2015

chriscroome commented Jul 9, 2015

mattab commented Jul 9, 2015

chriscroome commented Jul 9, 2015

chriscroome commented Jul 9, 2015

lolalalol commented Jul 9, 2015

heidarymr commented Jul 10, 2015

chriscroome commented Jul 13, 2015

SteffiMania commented Jul 13, 2015

mattab commented Jul 15, 2015

benjaminpick commented Jul 15, 2015 via email

mattab commented Jul 16, 2015

sgiehl commented Jul 16, 2015

mattab commented Jul 16, 2015

oparoz commented Jul 16, 2015

chriscroome commented Jul 16, 2015

Kousaeng commented Jul 16, 2015

quba commented Jul 17, 2015

ChristianGfK commented Jul 17, 2015

quba commented Jul 17, 2015

zanematthew commented Jul 17, 2015

ghost commented Jul 17, 2015

codifex commented Jul 17, 2015

jpfleury commented Jul 19, 2015

diosmosis commented Jul 19, 2015

ChristianGfK commented Jul 20, 2015

quba commented Jul 20, 2015

ChristianGfK commented Jul 20, 2015

tsteur commented Jul 20, 2015

alexandrejobin commented Jul 20, 2015

tsteur commented Jul 21, 2015

tsteur commented Jul 21, 2015

tsteur commented Jul 21, 2015

quba commented Jul 21, 2015

tsteur commented Jul 21, 2015

mattab commented Jul 24, 2015

zanematthew commented Jul 24, 2015

bmoore9 commented Jul 30, 2015

tsteur commented Jul 31, 2015

bmoore9 commented Jul 31, 2015