Can't upgrade from 3.13.5-rc1 to the new version 4.4.1

Support & Bugs
1

Our Matomo database is about 300GB. We hit a MySQL Server has gone away at this update clause every time. We are running the manual upgrade process. We have tried now 5 times, each attempt takes many hours.

Any thoughts on how to proceed?

I’ve gone through each of these recommendations:

Would like to proceed with this option, but not sure how to do the first step (execute the remaining queries)

* execute the remaining queries in the update that failed
* manually update the `option` table in your Matomo database, setting the value of version_core to the version of the failed update

Executing ALTER TABLE piwik_log_conversion ;… Done. [290 / 682]
Executing INSERT IGNORE INTO piwik_option (option_name, option_value) VALUES (‘version_log_conversion.visitor_seconds_since_first’, ‘INT(11) UNSIGNED NULL’);… Done. [291 / 682]
Executing INSERT IGNORE INTO piwik_option (option_name, option_value) VALUES (‘version_log_conversion.visitor_seconds_since_order’, ‘INT(11) UNSIGNED NULL’);… Done. [292 / 682]
Executing UPDATE piwik_log_link_visit_action SET search_cat = if(custom_var_k4 = ‘_pk_scat’, custom_var_v4, search_cat), search_count = if(custom_var_k5 = ‘_pk_scount’, custom_var_v5, search_count) WHERE custom_var_k4 = ‘_pk_scat’ or custom_var_kW ARNING [2021-08-27 18:13:30] 13844 /var/www/html/libs/Zend/Db/Adapter/Pdo/Abstract.php(263): Warning - PDO::exec(): MySQL server has gone away - Matomo 4.4.1 - Please report this message in the Matomo forums: https://forum.matomo.org (please do a s earch first as it might have been reported already)
WARNING [2021-08-27 18:13:30] 13844 /var/www/html/libs/Zend/Db/Adapter/Pdo/Abstract.php(263): Warning - PDO::exec(): Error reading result set’s header - Matomo 4.4.1 - Please report this message in the Matomo forums: https://forum.matomo.org (pleas e do a search first as it might have been reported already)
ERROR [2021-08-27 18:13:30] 13844 There was an error while updating the javascript tracker: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away

[X] Critical Error during the update process:

* /var/www/html/core/Updates/4.0.0-b1.php:
Error trying to execute the migration 'UPDATE piwik_log_link_visit_action SET search_cat = if(custom_var_k4 = '_pk_scat', custom_var_v4, search_cat), search_count = if(custom_var_k5 = '_pk_scount', custom_var_v5, search_count) WHERE custom_var_k  4 = '_pk_scat' or custom_var_k5 = '_pk_scount';'.
The error was: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away

The above is the core error message. It should help explain the cause, but if you require further help please:

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

If you are an advanced user and encounter an error in the database upgrade:

* identify and correct the source of the problem (e.g., memory_limit or max_execution_time)
* execute the remaining queries in the update that failed
* manually update the `option` table in your Matomo database, setting the value of version_core to the version of the failed update
* re-run the updater (through the browser or command-line) to continue with the remaining updates
* report the problem (and solution) so that Matomo can be improved

ERROR [2021-08-27 18:13:30] 13844 Uncaught exception: /var/www/html/plugins/CoreUpdater/Commands/Update.php(267): Matomo could not be updated! See above for more information. [Query: , CLI mode: 1]

2

P.s our piwik_log_link_visit_action table is 86.4GB

3

FYI, we deleted a ton of log data, running this command, which, in our case took 9 days (so far!)

./console core:delete-logs-data --dates=2015-01-01,2020-12-31 --limit 1000

That reduced the row count in piwik_log_link_visit_action significantly, but did not release any disk space.

We ran these commands which had no effect on disk space, not sure exactly what they did

./console core:purge-old-archive-data all
./console database:optimize-archive-tables all

Finally, I ran

mysqlcheck -o to optimize the tables manually,

Before:

/dev/nvme0n1p1 582G 241G 341G 42% /

After:

/dev/nvme0n1p1 582G 167G 416G 29% /

Effectively releasing 74GB of disk space.

I think that ./console database:optimize-archive-tables is either failing silently, or doesn’t cover some tables handled by mysqlcheck -o (E.g. piwik_log_link_visit_action, which in our case was 86GB.

We are still waiting for core:delete-logs-data to complete, but I feel optimistic given all this clean up and optimization that we will be able to upgrade to 4.4.1

I will say that there are a lot of FAQ articles that talk about database maintenance, but I did not see this mentioned.

1 Like
4

For posterity, I wanted to note here that I had to make a few further adjustments to the server:

1, I increased the innodb_buffer_pool_size to 8GB, which is just shy of the total amount of RAM on this server
2. I had to add a swap disk, because some of the operations were taking upwards of 12GB of memory to complete, especially the “UPDATE” operations.