Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

effective innodb_lock_wait_timeout value check

Tags:

sql

mysql

innodb

I have a table with lakhs of rows. Now, suddenly I need to create a varchar column index. Also, I need to perform some operations using that column. But its giving innodb_lock_wait_timeout exceeded error. I googled it and changed the value of innodb_lock_wait_timeout to 500 in my.ini file in my mysql folder. But Its still giving the same error. I need to be sure if the value has actually been changed or not. How can I check the effective innodb_lock_wait_timeout value?

like image 200
Sourabh Avatar asked Jun 06 '13 10:06

Sourabh


2 Answers

I found the answer. I need to run a query: show variables like 'innodb_lock_wait_timeout';.

like image 73
Sourabh Avatar answered Oct 28 '22 21:10

Sourabh


There can be a difference between your command and the server settings:

For Example:

SHOW GLOBAL VARIABLES LIKE '%INNODB_LOCK_WAIT_TIMEOUT%';  -- Default 50 seconds

SET @@SESSION.innodb_lock_wait_timeout = 30; -- innodb_lock_wait_timeout changed in your session 

-- These queries will produce identical results, as they are synonymous 

SHOW VARIABLES LIKE '%INNODB_LOCK_WAIT_TIMEOUT%';  -- but is now 30 seconds

SHOW SESSION VARIABLES LIKE '%INNODB_LOCK_WAIT_TIMEOUT%';  -- and still is 30 seconds

Any listed variable in the MySQL Documentation can be changed in your session, potentially producing a varied result!

Anything with a Variable Scope of "Both Global & Session" like sysvar_innodb_lock_wait_timeout, can potentially contain a different value.

Hope this helps!

like image 44
JayRizzo Avatar answered Oct 28 '22 20:10

JayRizzo