Trouble upgrading from 3.3.0 to 3.5.1


#1

In the table log_visit we have more that 52 million rows.
The update process is not finising while doing:

Executing ALTER TABLE `log_visit` MODIFY COLUMN `location_region` char(3) DEFAULT NULL;...
    [X] Error during plugin updates:

    * /var/www/piwik-3.5.1/core/Columns/Updater.php:
Error trying to execute the migration 'ALTER TABLE `log_visit` MODIFY COLUMN `location_region` char(3) DEFAULT NULL;'.
The error was: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction
    The update completed successfuly, however there were issues during the process. Please read the above descriptions for details. For further help:

Any suggestions on how to handle this?
I have maintenance mode enabled and loggin disabled.


(Lukas Winkler) #2

Hi,

I don’t have a solution but maybe these links help:

https://github.com/matomo-org/matomo/issues/6398
https://github.com/matomo-org/matomo/pull/12733


#3

Can I run ALTER TABLElog_visitMODIFY COLUMNlocation_regionchar(3) DEFAULT NULL; without the tansaction around it on the MySQL database? Will the PHP pick that up as a complete upgrade?


#4

@Lukas can the log_visit table also be change to MyISAM for some extra speed?
Or does Matomo only work with InnoDB? And can I run ALTER TABLElog_visitMODIFY COLUMNlocation_regionchar(3) DEFAULT NULL; manualy?


#5

We fixed it.
There was a long running query that was blocking the ALTER TABLE query.
Good practise tip:
Run: show full processlist;
kill long_running_task_id;