How to manually apply database updates for a new Matomo version

When using an unsupported database configuration or provider it may not be possible for the automatic Matomo update to apply the necessary changes to the database.

For example, Planet Scale does not allow the main database to be updated directly and this will cause the update to fail with an error code.

In this case it may be possible to apply the database changes manually and instruct the update process to ignore the error code, which should allow the update to complete successfully.

Familiarity with using the command line and access to execute SQL queries on your database server are required for this procedure.
The required ignore_error_codes option is only available on Matomo version 4.12 or greater.

1) Applying the database changes

To see the database statements required for the update run the command ./console core:update This will show output similar to:

*** Update ***

Database Upgrade Required

Your Matomo database is out-of-date, and must be upgraded before you can continue.

Matomo database will be upgraded from version 4.12.0 to the new version 5.1.0.

*** Note: this is a Dry Run ***

ALTER TABLE `log_visit` DROP INDEX `index_idsite_idvisitor`;
ALTER TABLE `log_visit` ADD INDEX `index_idsite_idvisitor` (`idsite`, `idvisitor`, `visit_last_action_time` DESC);
ALTER TABLE `user` ADD UNIQUE KEY uniq_email (`email`);

*** End of Dry Run ***

A database upgrade is required. Execute update? (y/N)
  • Press ‘N’ to avoid executing the update.
  • Copy the SQL statements between *** Note: this is a Dry Run *** and *** End of Dry Run *** lines to the clipboard.
  • Paste the SQL statements into you database administration tool and execute them.

Do not proceed to step 2 if the statements failed to execute successfully or you may leave your system in a non-functioning state.

2) Tell the updater to ignore the database error code

  • If the automatic updates failed with an error such as ERROR 1105 (HY000): direct DDL is disabled then make a note of the error code, 1105 in this case.
  • Edit the config/config.ini.php file and add ignore_error_codes[] = 1105 to the [database] section. You may add multiple error codes to be ignored, one per line.
  • Re-run the update with ./console core:update which should now complete successfully.

3) Remove the ignore error codes setting

After the update has completed it is strongly recommended to remove or disable the ignore_error_codes[] config setting added in step 2 otherwise the next Matomo update will appear to complete first time but the database changes will not be applied. All three steps of this procedure will need to be repeated for each Matomo update.

Previous FAQ: How do I select IP addresses or Visitor ID from the SQL database and convert binary table fields?