Problems updating Matomo from version 3.0.4 to version 4.3.1

m-ikeda · December 3, 2021, 7:24am

I am carrying out the process of updating the version 3.0.4 of Matomo to the latest version that they have released, version 4.3.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/html/matomo_pub/core/Columns/Updater.php:
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;'.
The error was: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
    * SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
    * SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
    * SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
    * SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
    * SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
    * SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
    * SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
    * SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
    * SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
    * SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
    * SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
    * SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
    * SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
    * SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
    * SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
    * SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
    * SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
    * SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
    * SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
    * SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
    * SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
    * SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
    * SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
    * SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
    * SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
    * SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
    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!

I found the following error in mysql.

Version: '5.7.35-enterprise-commercial-advanced-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Enterprise Server - Advanced Edition (Commercial)
2021-11-22T00:43:00.866462Z 2 [ERROR] InnoDB: Failed to find tablespace for table `matomo_extra_db`.`piwik_access` in the cache. Attempting to load the tablespace with space id 2845
2021-11-22T00:43:00.866499Z 2 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2021-11-22T00:43:00.866505Z 2 [ERROR] InnoDB: The error means the system cannot find the path specified.
2021-11-22T00:43:00.866509Z 2 [ERROR] InnoDB: Cannot open datafile for read-only: './matomo_extra_db/piwik_access.ibd' OS error: 71
2021-11-22T00:43:00.866513Z 2 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2021-11-22T00:43:00.866515Z 2 [ERROR] InnoDB: The error means the system cannot find the path specified.
2021-11-22T00:43:00.866519Z 2 [ERROR] InnoDB: Could not find a valid tablespace file for `matomo_extra_db/piwik_access`. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
2021-11-22T00:43:00.866556Z 2 [Warning] InnoDB: Orphan table encountered during DROP DATABASE. This is possible if '`matomo_extra_db`.`piwik_access`.frm' was lost.
2021-11-22T00:43:00.866561Z 2 [Warning] InnoDB: Missing .ibd file for table `matomo_extra_db`.`piwik_access`.

2021-11-22T02:17:13.349190Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4146ms. The settings might not be optimal. (flushed=2006 and evicted=0, during the time.)
2021-11-22T02:17:42.476140Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4314ms. The settings might not be optimal. (flushed=2009 and evicted=0, during the time.)
2021-11-22T02:17:50.253047Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4032ms. The settings might not be optimal. (flushed=2013 and evicted=0, during the time.)
2021-11-22T02:19:08.773656Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4006ms. The settings might not be optimal. (flushed=2009 and evicted=0, during the time.)
2021-11-22T02:19:24.939157Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4119ms. The settings might not be optimal. (flushed=2008 and evicted=0, during the time.)
2021-11-22T02:19:53.827854Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4069ms. The settings might not be optimal. (flushed=2007 and evicted=0, during the time.)
2021-11-22T02:19:59.024485Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4196ms. The settings might not be optimal. (flushed=2010 and evicted=0, during the time.)
2021-11-22T02:20:08.031872Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4175ms. The settings might not be optimal. (flushed=2009 and evicted=0, during the time.)
2021-11-22T02:26:23.033410Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4046ms. The settings might not be optimal. (flushed=2008 and evicted=0, during the time.)
2021-11-22T02:28:38.860046Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4283ms. The settings might not be optimal. (flushed=2008 and evicted=0, during the time.)
2021-11-22T02:29:07.360645Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4208ms. The settings might not be optimal. (flushed=2008 and evicted=0, during the time.)
2021-11-22T02:30:51.064692Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4034ms. The settings might not be optimal. (flushed=2008 and evicted=0, during the time.)
2021-11-22T02:31:18.613033Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4222ms. The settings might not be optimal. (flushed=2009 and evicted=0, during the time.)
2021-11-22T02:31:28.961822Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4284ms. The settings might not be optimal. (flushed=2011 and evicted=0, during the time.)
2021-11-22T02:31:48.338442Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4177ms. The settings might not be optimal. (flushed=2006 and evicted=0, during the time.)
2021-11-22T02:31:58.631626Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4397ms. The settings might not be optimal. (flushed=2011 and evicted=0, during the time.)
2021-11-22T02:32:08.925859Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4264ms. The settings might not be optimal. (flushed=2009 and evicted=0, during the time.)
2021-11-22T02:32:19.240627Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4346ms. The settings might not be optimal. (flushed=2012 and evicted=0, during the time.)
2021-11-22T02:32:29.457662Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4112ms. The settings might not be optimal. (flushed=2012 and evicted=0, during the time.)
2021-11-22T02:32:39.442781Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4155ms. The settings might not be optimal. (flushed=2010 and evicted=0, during the time.)
2021-11-22T02:32:59.127232Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4054ms. The settings might not be optimal. (flushed=2010 and evicted=0, during the time.)
2021-11-22T02:34:31.019772Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4279ms. The settings might not be optimal. (flushed=2009 and evicted=0, during the time.)
2021-11-22T02:34:41.600644Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4150ms. The settings might not be optimal. (flushed=2008 and evicted=0, during the time.)
2021-11-22T02:35:17.007496Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4069ms. The settings might not be optimal. (flushed=2010 and evicted=0, during the time.)
2021-11-22T02:35:27.908746Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4145ms. The settings might not be optimal. (flushed=2011 and evicted=0, during the time.)
2021-11-22T02:35:35.053821Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4124ms. The settings might not be optimal. (flushed=2010 and evicted=0, during the time.)
2021-11-22T02:35:49.543565Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4375ms. The settings might not be optimal. (flushed=2010 and evicted=0, during the time.)
2021-11-22T02:35:56.435129Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4057ms. The settings might not be optimal. (flushed=2011 and evicted=0, during the time.)
2021-11-22T02:36:31.812236Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4428ms. The settings might not be optimal. (flushed=2009 and evicted=0, during the time.)
2021-11-22T02:36:42.846210Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4240ms. The settings might not be optimal. (flushed=2011 and evicted=0, during the time.)

Environment

MySQL Version 5.7.35
PHP Version 7.4.6

What’s going wrong? How can I solve this problem?
Thank you very much in advance.

m-ikeda · December 7, 2021, 1:21am

Do I need to update Matomo from 3.0.4 to 4.0.0 first?

heurteph-ei · December 7, 2021, 8:32am

Maybe it can be a good idea to try

MisterGenest · November 1, 2023, 2:13pm

Generally, the upgrade works well if you upgrade to the final version of the major release you are on (for you this is Matomo 3). Then upgrade to the newest version of the next release (currently 4.15.2).

You are on the final Matomo 3 already (3.14) so we recommend going in a single step to the newest version of Matomo 4.

By the way, as of this month we just released 5. So you would update from 3.14 to 4.15.2. Then you would update to 5 if you wish. (Matomo 5 will give a warning if you try to update to it without being on 4.15 already.)