I did an upgrade from 20.11.06 to 22.05.08 last night, and db_rev 210600001 (bug 28489) took something like an hour. The problem was probably that the sessions table was huge, and should have been cleaned regularly. But it wasn't. Here is what the update does: $dbh->do('DELETE FROM sessions'); $dbh->do('ALTER TABLE sessions MODIFY a_session LONGBLOB NOT NULL'); I expected the delete to be quick and altering an empty table should also be quick. But it wasn't. "show processlist" showed this for a long time: copy to tmp table | ALTER TABLE sessions MODIFY a_session LONGBLOB NOT NULL I wonder if the problem is that the DELETE and the ALTER is in the same transaction? Which means the "deleted" data needs to be kept track of, while the "alter" is running? Maybe this could be made more efficient for others with a large sessions table?
If I have understood correctly, while DELETE is performed, deleted rows are still kept in case of a rollback or crash (could be something else, here are some reasons and tips I found: https://mariadb.com/kb/en/big-deletes/). We faced similar problem while archiving our action_logs table. We ended up renaming action_logs as work_action_logs and creating a new action_logs based on it. Then after moving everything in archive table and back to action_logs work_action_logs is dropped. Could something similar work here? Rename sessions table e.g. drop_sessions -> create new sessions table -> drop renamed table -> alter new sessions table.