MySQL 5.5 lock_wait_timeout: patience is a virtue, and a locked server

mysql 5.5 lock_wait_timeout: patience is a virtue, and a locked server

this post comes from daniel nichter at the mysql performance blog.

like ovais said in implications of metadata locking changes in mysql 5.5 , the hot topic these days is mysql 5.6, but there was an important metadata locking change in mysql 5.5.  as i began to dig into the percona toolkit bug he reported concerning this change apropos pt-online-schema-change , i discovered something about lock_wait_timeout that shocked me.  from the mysql 5.5 docs for lock_wait_timeout :

this variable specifies the timeout in seconds for attempts to acquire metadata locks. the permissible values range from 1 to 31536000 (1 year). the default is 31536000.

translation: by default, mysql 5.5 will “meta-block” for 1 year!  in my humble opinion, this is a bug, especially given the various subtle and sometimes quiet ways that metadata locking can lock the server as ovais describes in his blog post.  the default for innodb_lock_wait_timeout, by comparison, is 50 seconds.  that’s reasonable, but 31536000 is not.  i would only set a timeout, or any kind of wait or interval value, to such a high value to play a practical joke on someone.

second, and finally, if: “this variable specifies the timeout in seconds for attempts to acquire metadata locks,” then why isn’t it called metadata_lock_wait_timeout?

in conclusion: be careful when upgrading to mysql 5.5 because the solstices may pass before your dll statement completes.