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?
I found the answer. I need to run a query: show variables like 'innodb_lock_wait_timeout';
.
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!
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With