Mysql – ALTER TABLE … ROW_FORMAT=Compressed times out

mariadbMySQL

I'm trying to change a table with the command:

ALTER TABLE ... ROW_FORMAT=Compressed

This fails after 15 minutes, the log says:

InnoDB: Error: semaphore wait has lasted > 600 seconds
InnoDB: We intentionally crash the server, because it appears to be hung.

and I'm left with a large temporary table:

-rw-rw---- 1 mysql mysql 37G Jun  1 10:53 #sql-ib788-3264739711.ibd
-rw-rw---- 1 mysql mysql 15K Jun  1 10:37 #sql-13e5_95.frm

Whereas the table is only 100MB. I'm using MariaDB 10.0.16 on SLES 12.

I've actually backed up this table and restored it on a test VM running openSUSE 13.2/MariaDB 10.0.13 and the ALTER TABLE takes 60 seconds. Unfortunately I need to perform this process on the actual server.

The table has approximately 100,000 rows. If I cut it down to 1,000ish rows the statement works (the exact number of rows depends on whether I remove from the start or end of the table).

The log reports the InnoDB status and I noticed this:

---TRANSACTION 8665481870, not started
MySQL thread id 4, OS thread handle 0x7f8bf01b3700, query id 709 localhost user1 altering table
ALTER TABLE mdl_user ROW_FORMAT=Compressed
---TRANSACTION 8665481869, ACTIVE 253 sec
mysql tables in use 1, locked 1
MySQL thread id 4, OS thread handle 0x7f8bf01b3700, query id 709 localhost user1 altering table
ALTER TABLE mdl_user ROW_FORMAT=Compressed
Trx read view will not see trx with id >= 8665481871, sees < 8665481870

Which looks to me like it's trying to use two threads which are deadlocked although I may be misunderstanding what this is telling me.

What's going wrong?

Best Answer

Please note that I am not a MySQL developer, I use MS SQL Server. But the behavior in your post suggests the following:

It does not look like a deadlock to me, especially with the error messages:

InnoDB: Error: semaphore wait has lasted > 600 seconds
InnoDB: We intentionally crash the server, because it appears to be hung.

A deadlock normally would quickly determine which connection to roll back instead of processing up to 600 seconds before crashing the server.

Likely what is happening is that the table metadata change (the ALTER TABLE) cannot happen while there are other transactions using the table.

http://dev.mysql.com/doc/refman/5.6/en/metadata-locking.html

The link says, in part: "To ensure transaction serializability, the server must not permit one session to perform a data definition language (DDL) statement on a table that is used in an uncompleted explicitly or implicitly started transaction in another session."

EDIT: Sorry for the misunderstanding regarding deadlocks.

Per this post, https://stackoverflow.com/questions/24860111/warning-a-long-semaphore-wait

Ensure that: Innodb_adaptive_hash_index=0

Related Question