How do I convert the MySQL database tables from MyIsam to InnoDB?

If you are upgrading from an old Matomo (Piwik) version (pre Matomo 2.1.0), you may be still using Myisam tables and getting errors such as « Waiting for table level lock ».

Such errors are occuring because your tables are not yet in InnoDB engine. You can convert your tables by using a specific SQL command. Note that the conversion of your big tables from Myisam to InnoDB will take some time and require a lot of RAM and space in the /tmp partition.

Run the following SQL query:

SET @DATABASE_NAME = 'name_of_your_matomo_db';

SELECT  CONCAT('ALTER TABLE `', table_name, '` ENGINE=InnoDB;') AS sql_statements
FROM    information_schema.tables AS tb
WHERE   table_schema = @DATABASE_NAME
AND     `ENGINE` = 'MyISAM'
AND     `TABLE_TYPE` = 'BASE TABLE'
ORDER BY table_name DESC;

Running this SQL query will generate a SQL ALTER statement which you can then execute to convert all your Matomo database tables from Myisam into InnoDB.

Copy paste the output of this SQL query and run this in your Matomo database and all your tables will be upgraded to InnoDB.

If your database is large, you may want first to temporarily enable the maintenance mode.