Problems updating Matomo from version 3.5.1 to version 4.2.1

Support & Bugs
1

Hello,

Let’s see if someone can help me. I am carrying out the process of updating the version 3.5.1 of Matomo to the latest version that they have released, version 4.2.1. As in other updates I have followed the documentation on how to update, launching the command ./console core: update. After a few hours, I have the following output on the screen, indicating that Matomo has been updated correctly but that there are errors trying to launch the ALTER TABLE statement:

    * /var/www/educacion/cau_ce/estadisticasweb_test/core/Columns/Updater.php:
Error trying to execute the migration 'ALTER TABLE `piwik_log_visit` MODIFY COLUMN `visitor_count_visits` INT(11) UNSIGNED NOT NULL DEFAULT 0, MODIFY COLUMN `visit_total_interactions` MEDIUMINT UNSIGNED DEFAULT 0, MODIFY COLUMN `referer_name` VARCHAR(255) NULL, MODIFY COLUMN `referer_url` VARCHAR(1500) NULL, MODIFY COLUMN `config_browser_name` VARCHAR(40) NULL, ADD COLUMN `config_client_type` TINYINT( 1 ) NULL DEFAULT NULL, MODIFY COLUMN `location_region` char(3) DEFAULT NULL, MODIFY COLUMN `location_region` char(3) DEFAULT NULL;'.
The error was: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'location_region' in 'piwik_log_visit'
    * /var/www/educacion/cau_ce/estadisticasweb_test/core/Columns/Updater.php:
Error trying to execute the migration 'ALTER TABLE `piwik_log_visit` MODIFY COLUMN `visitor_count_visits` INT(11) UNSIGNED NOT NULL DEFAULT 0, MODIFY COLUMN `visit_total_interactions` MEDIUMINT UNSIGNED DEFAULT 0, MODIFY COLUMN `referer_name` VARCHAR(255) NULL, MODIFY COLUMN `referer_url` VARCHAR(1500) NULL, MODIFY COLUMN `config_browser_name` VARCHAR(40) NULL, ADD COLUMN `config_client_type` TINYINT( 1 ) NULL DEFAULT NULL, MODIFY COLUMN `location_region` char(3) DEFAULT NULL, MODIFY COLUMN `location_region` char(3) DEFAULT NULL;'.
The error was: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'location_region' in 'piwik_log_visit'
    * /var/www/educacion/cau_ce/estadisticasweb_test/core/Columns/Updater.php:
Error trying to execute the migration 'ALTER TABLE `piwik_log_visit` MODIFY COLUMN `visitor_count_visits` INT(11) UNSIGNED NOT NULL DEFAULT 0, MODIFY COLUMN `visit_total_interactions` MEDIUMINT UNSIGNED DEFAULT 0, MODIFY COLUMN `referer_name` VARCHAR(255) NULL, MODIFY COLUMN `referer_url` VARCHAR(1500) NULL, MODIFY COLUMN `config_browser_name` VARCHAR(40) NULL, ADD COLUMN `config_client_type` TINYINT( 1 ) NULL DEFAULT NULL, MODIFY COLUMN `location_region` char(3) DEFAULT NULL, MODIFY COLUMN `location_region` char(3) DEFAULT NULL;'.
The error was: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'location_region' in 'piwik_log_visit'
    The update completed successfuly, however there were issues during the process. Please read the above descriptions for details. For further help:

    * Check the [ Matomo FAQ ] which explains most common errors during update.
    * Ask your system administrator - they may be able to help you with the error which is most likely related to your server or MySQL setup.

Matomo has been successfully updated!

When I access the URL where I have Matomo deployed via the web, I get the attached screen also in this thread, where it says that the database is out-of-date.

Screenshot_11
Screenshot_11821×607 99.6 KB

What’s going wrong? How can I solve this problem?

Thank you very much in advance

2

Hello,

This comment could help adding the missing column:

3

Thanks for the reply!

I have reviewed the corresponding table piwik_log_visit and it already has that field location_region added in version 3.5.1 which is where I am currently.

It is true that before showing these errors when launching an ALTER TABLE, a message of “Error during plugin update” appears much higher up.

4

If you look at the end of the erroring SQL statement you’ll see:

 MODIFY COLUMN `location_region` char(3) DEFAULT NULL, 
 MODIFY COLUMN `location_region` char(3) DEFAULT NULL;'.

so its trying to modify the same column twice which is why SQL objects. The column is in the table, just not there twice!

So why is this happening? I suspect 2 bugs.

  • Bug 1
    The matomo upgrade code that creates the SQL statement get confused by obsolete files and isn’t checking that the same column is modified twice.

  • Bug 2
    The install instructions for matomo don’t seem to take account of obsolete files left behind.

At matomo upgrade guide down at section

  1. Replace the Matomo files with the latest version

it says

" It is not needed to delete your old Matomo files before transferring the new ones. The new files should overwrite all the old files with the same names."

I believe the error you are seeing occurs because it is necessary to delete obsolete Matomo files. The above instructions don’t make this expicit.

Otherwise files that are deleted in future releases get left behind, and cause the grief you are seeing.

In my case, some of the files like the following (dated 28 Apr 2020, v3.13.5?) got left behind

/var/www/piwik/plugins/DevicePlugins/Columns/PluginDirector.php
/var/www/piwik/plugins/DevicePlugins/Columns/PluginGears.php
/var/www/piwik/plugins/Actions/Columns/InteractionPosition.php
/var/www/piwik/plugins/VisitorInterest/Columns/VisitsByDaysSinceLastVisit.php

These files are no longer included in the piwik.zip file from the matomo 4.2.1 release (dated 1 Mar 2021).

I suspect these define columns that are no longer in the table and confuse the upgrade SQL statements which Bug 1 fails to spot. It also leads to later errors like

Column not found: 1054 Unknown column ‘config_director’ in ‘field list’

or

PHP Fatal error:  Uncaught Error: Call to undefined method 
Piwik\Tracker\Request::getDaysSinceLastVisit() 
in /var/www/piwik/plugins/VisitorInterest/Columns/VisitsByDaysSinceLastVisit.php:38

My solution was to find all files not dated 1 Mar 2021 and renaming them out of my /var/www/piwik/plugins directory to a tmp directory. Alternatively, save the config/config.ini.php file, delete the old piwik directory, copy over the contents of the new piwik.zip file and then return the saved config/config.ini.php file.