obsolete files left over from previous releases cause errors in 4.2.1 upgrade database restructure and runtime #17399

Expected Behavior

When you run

php "$PUBLIC_DIR/console" core:update

to upgrade matomo from 3.13.5 to 4.2.1, the database restructure should succeed:

Executing UPDATE piwik_archive_numeric_2021_03 SET name = 'done' WHERE name = 'done.';... Done. [291 / 294]

Executing ALTER TABLE piwik_log_visit MODIFY COLUMN profilable TINYINT(1) NULL, MODIFY COLUMN visitor_seconds_since_first INT(11) UNSIGNED NULL, MODIFY COLUMN visitor_seconds_since_order INT(11) UNSIGNED NULL, 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 visitor_seconds_since_last INT(11) UNSIGNED NULL, MODIFY COLUMN location_region char(3) DEFAULT NULL;... Done. [292 / 294]

Executing ALTER TABLE piwik_log_conversion MODIFY COLUMN visitor_seconds_since_first INT(11) UNSIGNED NULL, MODIFY COLUMN visitor_seconds_since_order INT(11) UNSIGNED NULL, MODIFY COLUMN visitor_count_visits INT(11) UNSIGNED NOT NULL DEFAULT 0, MODIFY COLUMN referer_name VARCHAR(255) NULL, ADD 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;... Done. [293 / 294]

Executing ALTER TABLE piwik_log_link_visit_action MODIFY COLUMN search_cat VARCHAR(200) NULL, MODIFY COLUMN search_count INTEGER(10) UNSIGNED NULL, ADD COLUMN idaction_product_cat INT(10) UNSIGNED NULL, ADD COLUMN idaction_product_cat2 INT(10) UNSIGNED NULL, ADD COLUMN idaction_product_cat3 INT(10) UNSIGNED NULL, ADD COLUMN idaction_product_cat4 INT(10) UNSIGNED NULL, ADD COLUMN idaction_product_cat5 INT(10) UNSIGNED NULL, ADD COLUMN idaction_product_name INT(10) UNSIGNED NULL, ADD COLUMN product_price DOUBLE NULL, ADD COLUMN idaction_product_sku INT(10) UNSIGNED NULL;... Done. [294 / 294]

Matomo has been successfully updated!

Current Behavior

output as follows:

Executing UPDATE `piwik_archive_numeric_2021_03` SET `name` = 'done' WHERE `name` = 'done.';... Done. [291 / 294]

Error trying to execute the migration 'ALTER TABLE `piwik_log_visit` MODIFY COLUMN `profilable` TINYINT(1) NULL, MODIFY COLUMN `visitor_seconds_since_first` INT(11) UNSIGNED NULL, MODIFY COLUMN `visitor_seconds_since_order` INT(11) UNSIGNED NULL, 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 `visitor_seconds_since_last` INT(11) UNSIGNED 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'

Note the repetition of the modify column location_region at the end of the statement (I've added line breaks for clarity), which I believe causes the error.

Possible Solution

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

https://matomo.org/docs/update/#the-manual-three-step-update

down at section

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.

Steps to Reproduce (for Bugs)

1. Get a database from the 3.13.5 release. Copy some obsolete files into the /var/www/piwik/plugins directory. In my case, the obsolete files (i.e. files without a 1 March 2021 date) were:

   drwxr-xr-x 2 www-data www-data 4096 Apr 28 2020 ./CoreHome/angularjs/dialogtoggler
   -rw-r--r-- 1 www-data www-data 3253 Apr 28 2020 ./CoreHome/angularjs/dialogtoggler/dialogtoggler.controller.js
   -rw-r--r-- 1 www-data www-data 710 Apr 28 2020 ./CoreHome/angularjs/dialogtoggler/dialogtoggler.directive.js
   -rw-r--r-- 1 www-data www-data 3589 Apr 28 2020 ./CoreHome/angularjs/dialogtoggler/dialogtoggler-urllistener.service.js
   -rw-r--r-- 1 www-data www-data 1363 May 13 2019 ./CoreHome/angularjs/dialogtoggler/ngdialog.less
   -rw-r--r-- 1 www-data www-data 5845 Apr 28 2020 ./CoreHome/javascripts/donate.js
   -rw-r--r-- 1 www-data www-data 13527 Oct 29 2019 ./CoreHome/javascripts/iframeResizer.contentWindow.min.js
   -rw-r--r-- 1 www-data www-data 12917 Oct 29 2019 ./CoreHome/javascripts/iframeResizer.min.js
   -rw-r--r-- 1 www-data www-data 2383 Apr 28 2020 ./CoreHome/LoginWhitelist.php
   -rw-r--r-- 1 www-data www-data 75831 Apr 28 2020 ./Morpheus/fonts/piwik.svg
   -rw-r--r-- 1 www-data www-data 26300 Apr 28 2020 ./Morpheus/fonts/piwik.ttf
   -rw-r--r-- 1 www-data www-data 26376 Apr 28 2020 ./Morpheus/fonts/piwik.woff
   -rw-r--r-- 1 www-data www-data 10936 Apr 28 2020 ./Morpheus/fonts/piwik.woff2
   -rw-r--r-- 1 www-data www-data 2636 May 13 2019 ./Morpheus/images/paypal_subscribe.png
   -rw-r--r-- 1 www-data www-data 4045 May 13 2019 ./Morpheus/images/smileyprog_0.png
   -rw-r--r-- 1 www-data www-data 4268 May 13 2019 ./Morpheus/images/smileyprog_1.png
   -rw-r--r-- 1 www-data www-data 4292 May 13 2019 ./Morpheus/images/smileyprog_2.png
   -rw-r--r-- 1 www-data www-data 4589 May 13 2019 ./Morpheus/images/smileyprog_3.png
   -rw-r--r-- 1 www-data www-data 4733 May 13 2019 ./Morpheus/images/smileyprog_4.png
   -rw-r--r-- 1 www-data www-data 477 Jun 24 2019 ./TagManager/Template/Variable/PreConfigured/FormDestionationVariable.php
   drwxr-xr-x 3 www-data www-data 4096 Mar 27 16:04 ./Actions/Columns
   -rw-r--r-- 1 www-data www-data 1448 Apr 28 2020 ./Actions/Columns/InteractionPosition.php
   -rw-r--r-- 1 www-data www-data 806 Apr 28 2020 ./API/Renderer/Json2.php
   -rw-r--r-- 1 www-data www-data 2227 Apr 28 2020 ./API/Renderer/Php.php
   -rw-r--r-- 1 www-data www-data 757 Oct 29 2019 ./CoreUpdater/ReleaseChannel/Latest2XBeta.php
   -rw-r--r-- 1 www-data www-data 689 Oct 29 2019 ./CoreUpdater/ReleaseChannel/Latest2XStable.php
   -rw-r--r-- 1 www-data www-data 763 Apr 28 2020 ./CoreUpdater/ReleaseChannel/Latest3XBeta.php
   -rw-r--r-- 1 www-data www-data 695 Apr 28 2020 ./CoreUpdater/ReleaseChannel/Latest3XStable.php
   -rw-r--r-- 1 www-data www-data 759 Apr 28 2020 ./CustomVariables/Columns/SearchCategory.php
   drwxr-xr-x 2 www-data www-data 4096 Mar 27 16:06 ./DevicePlugins/Columns
   -rw-r--r-- 1 www-data www-data 789 Apr 28 2020 ./DevicePlugins/Columns/PluginDirector.php
   -rw-r--r-- 1 www-data www-data 785 Apr 28 2020 ./DevicePlugins/Columns/PluginGears.php
   -rw-r--r-- 1 www-data www-data 2786 Apr 28 2020 ./Diagnostics/Diagnostic/LoadDataInfileCheck.php
   drwxr-xr-x 2 www-data www-data 4096 Apr 28 2020 ./GeoIp2/Columns
   -rw-r--r-- 1 www-data www-data 462 Apr 28 2020 ./GeoIp2/Columns/Region.php
   -rw-r--r-- 1 www-data www-data 697 Apr 28 2020 ./SegmentEditor/SegmentList.php
   drwxr-xr-x 2 www-data www-data 4096 Apr 28 2020 ./UserCountry/angularjs/location-provider-updater
   -rw-r--r-- 1 www-data www-data 5824 Apr 28 2020 ./UserCountry/angularjs/location-provider-updater/location-provider-updater.controller.js
   -rw-r--r-- 1 www-data www-data 1172 Apr 28 2020 ./UserCountry/angularjs/location-provider-updater/location-provider-updater.directive.js
   -rw-r--r-- 1 www-data www-data 1556 Apr 28 2020 ./UserCountry/Columns/Provider.php
   -rw-r--r-- 1 www-data www-data 24951 Apr 28 2020 ./UserCountry/GeoIPAutoUpdater.php
   drwxr-xr-x 2 www-data www-data 4096 Apr 28 2020 ./UserCountry/LocationProvider/GeoIp
   -rw-r--r-- 1 www-data www-data 11176 Apr 28 2020 ./UserCountry/LocationProvider/GeoIp/Pecl.php
   -rw-r--r-- 1 www-data www-data 8313 Apr 28 2020 ./UserCountry/LocationProvider/GeoIp.php
   -rw-r--r-- 1 www-data www-data 14862 Apr 28 2020 ./UserCountry/LocationProvider/GeoIp/Php.php
   -rw-r--r-- 1 www-data www-data 10381 Apr 28 2020 ./UserCountry/LocationProvider/GeoIp/ServerBased.php
   -rw-r--r-- 1 www-data www-data 540 Apr 28 2020 ./UserCountry/Tasks.php
   -rw-r--r-- 1 www-data www-data 3115 Apr 28 2020 ./UserCountry/templates/_updaterManage.twig
   -rw-r--r-- 1 www-data www-data 377 Apr 28 2020 ./UserCountry/templates/_updaterNextRunTime.twig
   drwxr-xr-x 2 www-data www-data 4096 Mar 27 16:07 ./VisitorInterest/Columns
   -rw-r--r-- 1 www-data www-data 1062 Apr 28 2020 ./VisitorInterest/Columns/VisitsByDaysSinceLastVisit.php

run:

php "$PUBLIC_DIR/console" core:update

Context

I was trying to update the matomo release from 3.13.5 to 4.2.1.

See my reply #4 in https://forum.matomo.org/t/problems-updating-matomo-from-version-3-5-1-to-version-4-2-1/41019

Your Environment

• Matomo Version: 4.2.1
• PHP Version: 8.0
• Server Operating System: debian 10.8 for amd64
• Additionally installed plugins: none as far as I know

``

Correction: add step 2.

Steps to Reproduce (for Bugs)

1. Get a database from the 3.13.5 release.
2. Download matomo 4.2.1 piwik.zip file, unzip into /var/www/piwik.
3. Copy some obsolete files into the /var/www/piwik/plugins directory.

Hi @satphil Thanks for creating the issue. I'm quite sure we already had a similar report in the past, but wasn't able to find it on the fly.
The colum location_region actually should have already been there before Matomo 4. Do you maybe have an backup from before the update where you could check if that column was there before or not?

Hi Stefan,

Are you saying this because the error message says "column not found"?
That's not the error.

Can I urge you to re-read the issue I logged as I specifically address that
misleading error message?

Thanks

Phil

On Sun, 28 Mar 2021, 03:09 Stefan Giehl, @.***> wrote:

Hi @satphil https://github.com/satphil Thanks for creating the issue.
I'm quite sure we already had a similar report in the past, but wasn't able
to find it on the fly.
The colum location_region actually should have already been there before
Matomo 4. Do you maybe have an backup from before the update where you
could check if that column was there before or not?

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/matomo-org/matomo/issues/17399#issuecomment-808755385,
or unsubscribe
https://github.com/notifications/unsubscribe-auth/ADTPHICAMRKYDYTGVZ2YTEDTFX7LXANCNFSM4Z4ROPNQ
.

Hi @satphil, thanks for creating this issue!

I can confirm the faq on the three step upgrade omits the requirement to remove no longer used files. In the OneClickUpdater we explicitly do this (for the reasons described in the issue description): https://github.com/matomo-org/matomo/blob/4.x-dev/plugins/CoreUpdater/Model.php#L26-L36, the manual upgrade should mirror this.

I'm not sure what the best solution here is since we don't keep a list of files that were removed but diff between what is in the archive and the current install, but it might require a new core command. Or some pre tested bash functions. But the process will become more complicated since it's no longer just a "FTP and upload".

" 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."

The initial files should be removed before uploading the new files and the guide should be updated ideally. There has been a similar issue before (I think eg https://github.com/matomo-org/matomo/issues/17181). Otherwise there might be still some dimensions that should be removed etc.

"the guide should be updated ideally" -> "the guide should be updated
urgently",

(unless, of course, leaving the discovery by sysadmins that old files
should have been deleted is some sort of Matomo secret initiation ritual
:-).

On Mon, 29 Mar 2021, 06:43 Thomas Steur, @.***> wrote:

" 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."

The initial files should be removed before uploading the new files and the
guide should be updated ideally. There has been a similar issue before (I
think eg #17181 https://github.com/matomo-org/matomo/issues/17181).
Otherwise there might be still some dimensions that should be removed etc.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/matomo-org/matomo/issues/17399#issuecomment-808948768,
or unsubscribe
https://github.com/notifications/unsubscribe-auth/ADTPHIGUZDEDVHPGPNLGIS3TF6BFHANCNFSM4Z4ROPNQ
.

This is very disturbing.

Please either:

• make core:update clean obsolete files (core application + shipped plugins)
• update documentation to state this needs to be done manually
  Also consider updating https://matomo.org/changelog/matomo-4-0-0/ to make an explicit statement about it.

While the former would involve way more work but would eventually be way more rewarding (keeping the promise of simplicity), the latter could be done swiftly.

@Berbe we are already planning to improve it for the next release.

I went to upgrade recently and was wondering why everything was failing. Only after hunting down this issue was I able to find that I needed to remove all the old files first. The documentation still says to just copy and override existing files. This should really be at least changed in the documentation.

@Berbe 2021-04-21
Please either:

• [...]
• update documentation to state this needs to be done manually
  Also consider updating https://matomo.org/changelog/matomo-4-0-0/ to make an explicit statement about it.

[...] the latter could be done swiftly.

@sgiehl 2021-04-21
@Berbe we are already planning to improve it for the next release.

@mattab removed this from the 4.3.0 milestone on 26 May
@mattab added this to the 4.4.0 milestone on 26 May
@tsteur removed this from the 4.4.0 milestone on 23 Jul
@tsteur added this to the 4.7.0 milestone on 23 Jul

@tsteur would you mind to summarize what we should do here? Is it only about updating the documentation? Or shall we also implement a possibility to automatically remove those files in the UI or maybe within core:update?

The first step would be definitely to update the guide.

Not sure what else we can do to solve this issue in the future so it won't happen again? Can we do something that eg when files are left there, the updates still work?

Maybe we can detect files that shouldn't be there and ignore them? Or does it maybe make sense before the DB upgrade to show again if some files need to be deleted?

Or maybe we could detect when this error happens and give users clear instructions on what to do solve this issue? Like remove these files and try again?

I'm assuming there are various ways but I'm not too deep into it right now to know how to best solve the issue without taking too much time and while providing a good user experience.

The matomo software implicitly depends on the presence or absence of files, so it is imperative that obsolete files are removed during upgrades. Commands like rsync have switches like "--delete-after - receiver deletes extraneous files from dest dirs after transfer, not during".
One approach, mentioned in my original bug report: save theconfig/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.

@mattab this would be something we should schedule ASAP to at least update the documentation to prevent some manually updating users to run into this issue