…-in InnoDB, then you can further clarify to yourself that the ALTER in your test case is waiting on InnoDB row locks by setting innodb_lock_wait_timeout… innodb_lock_wait_timeout=1; alter table t1 engine=innodb; And the alter will timeout after approximately a second with the following error: ERROR 1205 (HY000): Lock wait timeout…
Post: MySQL 5.6 Compatible Percona Toolkit 2.2 Released
March 25, 2013 By 2 Comments
… its tries and wait time between tries for all operations are configurable. Also, we removed –lock-wait-timeout which set innodb_lock_wait_timeout because that now conflicts, or is at least confused with, lock_wait_timeout (introduced in MySQL 5.5) for metadata locks. Now…
Post: MySQL 5.5 lock_wait_timeout: patience is a virtue, and a locked server
February 28, 2013 By 5 Comments
… 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…
Post: ACTIVE with Locks – Now thats a problem !
June 2, 2011 By 2 Comments
… some other query will try to lock the same rows it will have to wait… possibly until innodb_lock_wait_timeout elapses and when it completes with error. Second because if you have some updated rows Innodb will…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
….commanual innodb_flush_methodblogpercona.commanual innodb_force_recoveryblogpercona.commanual Innodb_have_atomic_builtinsblogpercona.commanual innodb_io_capacityblogpercona.commanual innodb_lock_wait_timeoutblogpercona.commanual innodb_locks_unsafe_for_binlogblogpercona.commanual innodb…
Post: Implications of Metadata Locking Changes in MySQL 5.5
February 1, 2013 By 9 Comments
… | Waiting for table metadata lock | ALTER TABLE test_2 DROP COLUMN c | | 7 | msandbox | localhost | test | Query | 195 | Waiting for table metadata lock | select… with unintended consequences. A workaround here would be to set lock_wait_timeout variable to a very low value and then execute the…): session2 > set session lock_wait_timeout=1;CREATE TABLE t1(i int(11) not null auto_increment primary key) ENGINE=InnoDB; However, I feel…
Post: InnoDB's gap locks
March 27, 2012 By 15 Comments
… 1205 (HY000): Lock wait timeout exceeded; try restarting transaction transaction2 > INSERT INTO t VALUES(29); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting…need to create locks to prevent that from happening. 2- innodb_locks_unsafe_for_binlog = 1. Disables the gap locks except for…
Comment: MySQL Server Memory Usage
September 1, 2006 By 21 Comments
… MySQL innodb_additional_mem_pool_size=20M innodb_file_io_threads=4 innodb_lock_wait_timeout=15 # CHANGED from 50 innodb_log_files_in_group=3 innodb_flush_log_at_trx_commit=0 #innodb…
Comment: Database problems in MySQL/PHP Applications
November 6, 2006 By 13 Comments
…-time=5 log-slow-queries=/apps/log/slow-query interactive_timeout=300 wait_timeout=300 thread_cache = 40 max_connections=500 query_cache… buffer pool size innodb_log_file_size = 1000M #innodb_log_buffer_size = 32M #innodb_flush_log_at_trx_commit = 1 #innodb_lock_wait_timeout = 50 [mysqldump] quick…
Post: Pitfalls of converting to InnoDB
February 25, 2007 By 16 Comments
…clients to convert their current database from MyISAM tables to InnoDB. The transfer by itself in most cases is almost …broken by new unexpected errors 1205 (ER_LOCK_WAIT_TIMEOUT) Lock wait timeout expired. Transaction was rolled back. 1213 (ER_LOCK_DEADLOCK) Transaction deadlock. You should rerun…
