@Joey3000 opened this Issue on January 31st 2016 Contributor

Issue

Following issue appears on saving any user settings (module=UsersManager&action=userSettings) after Piwik has been updated from 2.15.0 to 2.15.1-b2 or later (tested: 2.16.1-b1 and 2.16.1-rc1):

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'use_12_hour_clock' in 'field list'

And it indeed does not exist in the user_language table when checked with phpMyAdmin. Only login and language are there.

Test Procedure

  1. Start with fully installed version 2.15.0
  2. Update to 2.15.1-b2 by deleting all old files (but keeping the DB content) and uploading the new files via FTP
  3. Login (no matter as which user)
  4. Try changing (e.g. the "Report date to load by default") and saving the user settings ==> The above error appears as user notice (page doesn't reload like on successful save)

    Notes

@tsteur commented on January 31st 2016 Member

There should have been a database update (as an update is defined in 2.15.1-b1).

The following query should fix it.

ALTER TABLE `user_language` ADD COLUMN `use_12_hour_clock` TINYINT(1) NOT NULL DEFAULT 0 AFTER `language`

or

ALTER TABLE `piwik_user_language` ADD COLUMN `use_12_hour_clock` TINYINT(1) NOT NULL DEFAULT 0 AFTER `language`

if your database table prefix is piwik_.

Is there a chance that you skipped the database update or so?

@Joey3000 commented on January 31st 2016 Contributor

Is there a chance that you skipped the database update or so?

No, I select "Reuse the existing tables".

Turns out that doUpdate() and getMigrationQueries() in https://github.com/piwik/piwik/blob/2.16.0-rc1/plugins/LanguagesManager/Updates/2.15.1-b1.php don't get called for some reason. (Tested placing exit('called'); into them.) If, on the other hand, I move the update script from the LanguagesManager plugin into /core/Updates by reversing https://github.com/piwik/piwik/commit/07229d109d80e66cad3e4eb3f7bd2635d5c599fb, then they get called and the table gets updated correctly.

@Joey3000 commented on February 1st 2016 Contributor

I inserted following in https://github.com/piwik/piwik/blob/2.16.0-rc1/core/Updater.php#L482 on 2.15.1-b2:

  • exit(json_encode($plugins));

    Result includes the LanguagesManager plugin:

{"CorePluginsAdmin":{},"CoreAdminHome":{},"CoreHome":{},"WebsiteMeasurable":{},"Diagnostics":{},"CoreVisualizations":{},"Proxy":{},"API":{},"ExamplePlugin":{},"Widgetize":{},"Transitions":{},"LanguagesManager":{},"Actions":{},"Dashboard":{},"MultiSites":{},"Referrers":{},"UserLanguage":{},"DevicesDetection":{},"Goals":{},"Ecommerce":{},"SEO":{},"Events":{"metadataDimensions":{"eventCategory":["Events_EventCategory","log_link_visit_action.idaction_event_category"],"eventAction":["Events_EventAction","log_link_visit_action.idaction_event_action"],"eventName":["Events_EventName","log_link_visit_action.idaction_name"]}},"UserCountry":{},"VisitsSummary":{},"VisitFrequency":{},"VisitTime":{},"VisitorInterest":{},"ExampleAPI":{},"ExampleRssWidget":{},"Feedback":{},"Monolog":{},"Login":{},"UsersManager":{},"SitesManager":{},"Installation":{},"CoreUpdater":{},"CoreConsole":{},"ScheduledReports":{},"UserCountryMap":{},"Live":{},"CustomVariables":{},"PrivacyManager":{},"ImageGraph":{},"Annotations":{},"MobileMessaging":{},"Overlay":{},"SegmentEditor":{},"Insights":{},"Morpheus":{},"Contents":{},"BulkTracking":{},"Resolution":{},"DevicePlugins":{},"Heartbeat":{},"Intl":{}}
  • exit(json_encode($componentsToCheck));

    Result does not include the LanguagesManager plugin:

{"core":"2.15.1-b2","CoreAdminHome":"2.15.1-b2","Diagnostics":"2.15.1-b2","Monolog":"2.15.1-b2","Login":"2.15.1-b2","UsersManager":"2.15.1-b2","SitesManager":"2.15.1-b2","Installation":"2.15.1-b2","Intl":"2.15.1-b2","log_visit.user_id":"VARCHAR(200) NULL","log_visit.visit_first_action_time":"DATETIME NOT NULL","log_visit.visit_goal_buyer":"TINYINT(1) NOT NULL","log_visit.visit_goal_converted":"TINYINT(1) NOT NULL","log_visit.visitor_days_since_first":"SMALLINT(5) UNSIGNED NOT NULL1","log_visit.visitor_days_since_order":"SMALLINT(5) UNSIGNED NOT NULL1","log_visit.visitor_returning":"TINYINT(1) NOT NULL1","log_visit.visitor_count_visits":"SMALLINT(5) UNSIGNED NOT NULL1","log_visit.visit_entry_idaction_name":"INTEGER(11) UNSIGNED NOT NULL","log_visit.visit_entry_idaction_url":"INTEGER(11) UNSIGNED NOT NULL","log_visit.visit_exit_idaction_name":"INTEGER(11) UNSIGNED NOT NULL","log_visit.visit_exit_idaction_url":"INTEGER(11) UNSIGNED NULL DEFAULT 0","log_visit.visit_total_actions":"SMALLINT(5) UNSIGNED NOT NULL","log_visit.visit_total_searches":"SMALLINT(5) UNSIGNED NOT NULL","log_visit.referer_keyword":"VARCHAR(255) NULL1","log_visit.referer_name":"VARCHAR(70) NULL1","log_visit.referer_type":"TINYINT(1) UNSIGNED NULL1","log_visit.referer_url":"TEXT NOT NULL","log_visit.location_browser_lang":"VARCHAR(20) NOT NULL","log_visit.config_browser_engine":"VARCHAR(10) NOT NULL","log_visit.config_browser_name":"VARCHAR(10) NOT NULL","log_visit.config_browser_version":"VARCHAR(20) NOT NULL","log_visit.config_device_brand":"VARCHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL","log_visit.config_device_model":"VARCHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL","log_visit.config_device_type":"TINYINT( 100 ) NULL DEFAULT NULL","log_visit.config_os":"CHAR(3) NOT NULL","log_visit.config_os_version":"VARCHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL","log_visit.visit_total_events":"SMALLINT(5) UNSIGNED NOT NULL","log_visit.visitor_localtime":"TIME NOT NULL","log_visit.visitor_days_since_last":"SMALLINT(5) UNSIGNED NOT NULL","log_visit.config_resolution":"VARCHAR(9) NOT NULL","log_visit.config_cookie":"TINYINT(1) NOT NULL","log_visit.config_director":"TINYINT(1) NOT NULL","log_visit.config_flash":"TINYINT(1) NOT NULL","log_visit.config_gears":"TINYINT(1) NOT NULL","log_visit.config_java":"TINYINT(1) NOT NULL","log_visit.config_pdf":"TINYINT(1) NOT NULL","log_visit.config_quicktime":"TINYINT(1) NOT NULL","log_visit.config_realplayer":"TINYINT(1) NOT NULL","log_visit.config_silverlight":"TINYINT(1) NOT NULL","log_visit.config_windowsmedia":"TINYINT(1) NOT NULL","log_visit.visit_total_time":"SMALLINT(5) UNSIGNED NOT NULL","log_visit.location_city":"varchar(255) DEFAULT NULL1","log_visit.location_country":"CHAR(3) NOT NULL1","log_visit.location_latitude":"float(10, 6) DEFAULT NULL1","log_visit.location_longitude":"float(10, 6) DEFAULT NULL1","log_visit.location_region":"char(2) DEFAULT NULL1","log_link_visit_action.server_time":"DATETIME NOT NULL","log_link_visit_action.idaction_name":"INTEGER(10) UNSIGNED","log_link_visit_action.idaction_url":"INTEGER(10) UNSIGNED DEFAULT NULL","log_link_visit_action.time_spent_ref_action":"INTEGER(10) UNSIGNED NOT NULL","log_link_visit_action.idaction_event_action":"INTEGER(10) UNSIGNED DEFAULT NULL","log_link_visit_action.idaction_event_category":"INTEGER(10) UNSIGNED DEFAULT NULL","log_link_visit_action.idaction_content_interaction":"INTEGER(10) UNSIGNED DEFAULT NULL","log_link_visit_action.idaction_content_name":"INTEGER(10) UNSIGNED DEFAULT NULL","log_link_visit_action.idaction_content_piece":"INTEGER(10) UNSIGNED DEFAULT NULL","log_link_visit_action.idaction_content_target":"INTEGER(10) UNSIGNED DEFAULT NULL","log_conversion.revenue_discount":"float default NULL","log_conversion.revenue":"float default NULL","log_conversion.revenue_shipping":"float default NULL","log_conversion.revenue_subtotal":"float default NULL","log_conversion.revenue_tax":"float default NULL"}
  • exit(json_encode($componentsWithUpdateFile));

    None in the result:

[]
@Joey3000 commented on February 1st 2016 Contributor

The root cause is that the LanguagesManager plugin is not recognized as "installed" in https://github.com/piwik/piwik/blob/2.16.0-rc1/core/Updater.php#L463. Adding it to https://github.com/piwik/piwik/blob/2.16.0-rc1/config/global.ini.php#L804 makes the update script in the LanguagesManager plugin work.

BUT: That breaks installation on an empty DB - the user_language table does not get created. I guess because the plugin is considered already installed and so its install() (https://github.com/piwik/piwik/blob/2.16.0-rc1/plugins/LanguagesManager/LanguagesManager.php#L128) does not get called.

The simplest solution might be reversing 07229d1. I don't know.

@tsteur commented on February 1st 2016 Member

I believe we fixed this in https://github.com/piwik/piwik/commit/ea30714acff275cff51930d6f4312722e4cb2916#diff-9dba5f51c7c7ac664f0bbf968dfbafa2

which should be included in 2.15.1-b2

What I think happened in this case is that it tried to install the plugin LanguagesManager on each request but always failed because the table already existed. Therefore it never got added to PluginsInstalled and therefore never received any update.

Before 2.15.1-b2 we always silently ignored these kinda errors so there was no chance for you to notice this. From 2.15.1-b2, if a plugin fails to install, there should now be an error message shown in the UI.

I reckon we can close this issue but want to wait for feedback

@Joey3000 commented on February 2nd 2016 Contributor

I'm not sure if this is the same issue you describe, but this issue still exists on the latest 2.16.0-rc1. This issue occurs due to the complete wipe of all Piwik files before uploading new ones (step 2 of the test procedure). Specifically, due to the included deletion of "config.ini.php". Because Piwik's list of installed Plugins is in the "config.ini.php", not in the DB. And when that file is gone - the default installed plugins list in "global.ini.php" applies. Since the LanguagesManager plugin is not one of them, its update script does not run. Neither does the user_language table get re-created by the install() of the LanguagesManager plugin, because it already exists - in which case the creation fails silently.

The way it is, if "config.ini.php" is deleted on the update, then only plugins which are considered "installed" by default (i.e. which are in the installed plugins list in "global.ini.php") can have an own update script. And the LanguagesManager plugin is not one of them.

I had been under impression that deleting "config.ini.php" is a valid action - the official way to trigger a re-installation (see https://piwik.org/faq/how-to-install/#faq_37). When done on updating Piwik though, it causes this issue. (For the first time for me, after many updates the same way.)

Summarizing, I guess this is a non-issue and rather my mistake. I'm going to leave "config.ini.php" in place on future updates. The update guide (https://piwik.org/docs/update/#the-manual-three-step-update) mentions the need to back up "config.ini.php". Maybe it could specifically require that one must leave "config.ini.php" in place - that would make it clearer. (Although I hadn't checked it in years assuming that it hadn't changed in a while. So that it wouldn't have prevented this issue for me this time. But maybe for other people.)

P.S.: The back-up-and-restore guide (https://piwik.org/faq/how-to-install/#faq_138) already makes clear that "config.ini.php" needs to be kept. I had thought that it's only for the DB login data (and the rest is adjusted automatically), but learned in this lesson that not just for that.

So, feel free to close this.

@tsteur commented on February 2nd 2016 Member

Neither does the user_language table get re-created by the install() of the LanguagesManager plugin, because it already exists - in which case the creation fails silently.

Actually, I understand the problem fully now and you will also see it from my answers further below. Problem is that one should not delete config.ini.php when updating. What happened here?

The LanguagesManager had user_language table already installed with login and language column. When you updated with the deleted config.ini.php it failed to install the table because it already existed. However, the installer was expected to add column use_12_hour_clock but it couldn't add it because the table already existed. At the same time the updater wouldn't execute the update because the plugin was not installed (yet) and afterwards record the version of the used Piwik version so it won't try to install the update again. It was evaluated at the same time whether plugin needs to be installed or updated. The updater should possibly rather check the installed version in the database instead of only checking whether it is installed. Or it should try to do it in 2 runs: First try to install, then run needed updates. We have various issues with our updater as mentioned in https://github.com/piwik/piwik/issues/5985 and need to refactor it at some point.

The way it is, if "config.ini.php" is deleted on the update, then only plugins which are considered "installed" by default (i.e. which are in the installed plugins list in "global.ini.php") can have an own update script. And the LanguagesManager plugin is not one of them.

That's correct and expected behaviour. Question is: Why is the plugin not added to PluginsInstalled.

I had been under impression that deleting "config.ini.php" is a valid action

Yes that should work. Only information about already installed plugins and about custom activated plugins gets lost. When updating Piwik, the config.ini.php should never be deleted as you noticed in the upgrade guide.

Maybe it could specifically require that one must leave "config.ini.php" in place - that would make it clearer.

How are you doing the update? Would we be able to detect it? So far, when config.ini.php is missing we assume someone is reinstalling Piwik. We cannot really detect whether someone is just "updating" I think.

Update: I think we could compare version in Piwik with the version recorded in database and in case we notice someone updated the code base, we could show some kind of warning or info. We should recommend to replace the current files with the files of the installed Piwik version and then install updates afterwards. We would still need to allow users to continue this process possibly in case they want to repair their Piwik or something.

I had thought that it's only for the DB login data (and the rest is adjusted automatically),

We had a discussion about moving plugins list to database a few times but one problem might be for example that someone has different plugins installed on different servers and it may lead to errors. Eg when a plugin needs to install something locally on each server but there can be also other problems. Also you might want to enable some features only on some servers of your Piwik installation.

@tsteur commented on February 2nd 2016 Member

Added the following to #5985

  • We should run the updater in two steps and first try to install all plugins and then perform all needed updates if the recorded component version is lower than the current used version see https://github.com/piwik/piwik/issues/9666#issuecomment-178272978
  • We always should as well check the recorded component version when deciding whether we need to perform an installation. This is a use case see https://github.com/piwik/piwik/issues/9666#issuecomment-178272978 when deleting config.ini.php and then updating Piwik. Problem is when there are not only database changes but also local changes (eg modifying piwik.js). Local changes would always need to be executed while database changes only might need to be executed when recorded component version in database is lower. Maybe we can separate those two use cases in the installer with different methods or so.
@tsteur commented on February 2nd 2016 Member

@mattab what are your thoughts on detecting whether someone is updating Piwik and has no config.ini.php. We could recommend to use an older code base / Piwik version instead.

@Joey3000 commented on February 2nd 2016 Contributor

Thanks for looking into this!

Actually, I understand the problem fully now and you will also see it from my answers further below.

Yep! :)

How are you doing the update? Would we be able to detect it?

The update procedure is in the Test Procedure in the original post.
Update: Sorry, I noticed it's not very detailed. Additional steps:
2.2 Point the browser to the Piwik installation and proceed as on a re-installation
2.3 On the "Database Setup" page, enter the same data as used before the update
2.4 On the "Creating the Tables" page, select "Reuse the existing tables"
2.5 On the "Congratulations" page, just click on "Continue to Piwik". (The "Enable Do Not Track support" and "Anonymize the last byte(s)..." will be shown as they were before the update.)

@mattab commented on February 2nd 2016 Member

what are your thoughts on detecting whether someone is updating Piwik and has no config.ini.php. We could recommend to use an older code base / Piwik version instead.

if this use case is not fully supported, +1 to fail with a clear explicit error message that invites user to first restore old files, install Piwik, then update to latest. It's kinda similar to the error notification we display when the DB schema is found to be newer than the Piwik version being deployed on server.

@tsteur commented on February 4th 2016 Member

FYI: Another user experienced this issue here: https://github.com/piwik/piwik/issues/9708

@vjl commented on February 13th 2016

Just upgraded to 2.16 today via a "replace all files except for config/config.ini.php" method and I hit this bug when trying to update my user prefs to use the 12-hour time format. So I didn't delete my prior config.ini.php file, and I did successfully update the database to 2.16 when prompted.

@caugner commented on November 20th 2016

Issue still exists in 2.17.1. I fixed it using @tsteur's workaround.

@mr-manuel commented on March 13th 2017

Issue still exists in 3.0.2. Fixes also with @tsteur 's workaround. Is this database change missing in the updater?

@DevDaveo commented on April 15th 2017

+1
Also I can confirm this issue exists in 3.0.3.

@GJNilsen commented on June 7th 2018

Same error in 3.5.1

@sgiehl commented on June 7th 2018 Member

@GJNilsen which version did you update from?

@GJNilsen commented on June 7th 2018

The latest piwik.. Btw, all visitor stats are zero. Only the live view shows the correct number of visitors.

@Ark74 commented on September 7th 2018

Just found it on 3.6.0.

Powered by GitHub Issue Mirror