effective innodb_lock_wait_timeout value check

Question

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?

Solution 1

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

Solution 2

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!