## 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. <!-- For bugs please tell us what happens instead of the expected behavior --> <!-- If suggesting a change/improvement, explain the difference from current behavior --> ## 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](URL) 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. <!-- Not required, but if possible try to describe a fix or ideas --> <!-- how to implement the suggested change or improvement --> ## Steps to Reproduce (for Bugs) <!-- For UI bugs, please try to reproduce that on our demo (https://demo.matomo.cloud/) --> <!-- if that is possible, please include the URLs and steps how to reprduce it there --> <!-- For other bugs or UI bugs that can't be reproduced on Demo, please include any relevant --> <!-- URLs or commands your are using that may help us to reproduce the bug --> 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 <!-- How has this issue affected you? What are you trying to accomplish? --> <!-- Providing context helps us come up with a solution that is most useful in the real world --> <!-- If there already was a discussion on our community forum about this topic, please also --> <!-- include the link here, as the posts might provide some helpful information --> 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](URL) ## Your Environment <!-- Include as many relevant details about the environment you experienced the bug in --> <!-- You can find some of that information in the system check --> * 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 <!-- For UI Bugs please also tell us something about your environment --> ``