Solved! ndbcluster ALTER TABLE yields “ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction”

This is mostly a reminder to myself… but maybe someone else will find it useful as well.

I migrated some databases to ndbcluster (some of the tables were fairly decent sized… 9GB for 1 table spanning 220M records), and was running into a problem where an ALTER TABLE to change the storage engine was spewing out some cryptic error message like so:

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Cryptic mainly because it was coming from MyISAM, which doesn’t have record locking. Long story short is it’s not a setting in your my.ini file for mysqld, rather a setting in your config.ini for ndbd. The TransactionDeadlockDetectionTimeout setting defaults to 1200 (1.2 seconds), I ended up raising it to 24 hours just for purposes of migrating existing tables to ndbcluster (the setting of 86400000 ms is 24 hours).

Being relatively new to MySQL Cluster, it was also a good opportunity to practice making a config change and doing a rolling restart of the ndbd nodes to have no downtime.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>