MySQL Bugs: #71043: innodb_lock_wait

Bug #71043	innodb_lock_wait_timeout read only
Submitted:	30 Nov 2013 11:29	Modified:	2 Dec 2013 8:44
Reporter:	Kali Charan Pandava	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.1.42	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	innodb_lock_wait_timeout read only

Description:
innodb_lock_wait_timeout read only in 5.1.42 version when i am trying to set 
innodb_lock_wait_timeout to some other value than default value

How to repeat:
SET GLOBAL innodb_lock_wait_timeout = 120;

or

SET innodb_lock_wait_timeout = 120;

I believe documentation explains:

http://dev.mysql.com/doc/innodb/1.1/en/innodb-other-changes-innodb_lock_wait_timeout.html

"In MySQL 5.5 and higher, the configuration parameter innodb_lock_wait_timeout can be set at runtime with the SET GLOBAL or SET SESSION statement. Changing the GLOBAL setting requires the SUPER privilege and affects the operation of all clients that subsequently connect. Any client can change the SESSION setting for innodb_lock_wait_timeout, which affects only that client."

(but not like that before MySQL 5.5. In older versions it can only be changed at server startup (using a command line option or an options file))

Peter
(not a MySQL/Oracle person)

I have tried setting the value "set innodb_lock_wait_timeout=900;" in proc while running the proc...it is giving the exception that the property is read-only..

The link is provided is not helpful in my situation.

What do you refer to as 'proc'? I do not understand this term in this context.

But the documentation page I linked to says clearly that (*ONLY*) from MySQL 5.5x this variable can be set dynamically.  So in my understanding what you experience is expected with MySQL 5.1x

-- Peter

As Peter explained: it is a dynamic parameter only in 5.5, not in 5.1, so this not working in 5.1 is not a bug, and even less so "S1 (Critical)"

-- 
Hartmut (not a MySQL/Oracle person anymore)

Hello Kali Charan,

Imho this is a documented behavior and clearly explained in docs - "For the built-in InnoDB, this variable can be set only at server startup. For InnoDB Plugin, it can be set at startup or changed at runtime, and has both global and session values."

Since you are using 5.1.42, which means built-in Innodb is used and hence you cannot set this variable dynamically.

In MySQL 5.5 onwards, the InnoDB Plugin is also included, but it becomes the built-in version of InnoDB in MySQL Server, replacing the version previously included as the built-in InnoDB engine.

More detail in manual page - http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_lock_wait_time...
                             http://dev.mysql.com/doc/innodb/1.1/en/innodb-other-changes-innodb_lock_wait_timeout.html

As Peter and Hartmut explained you in earlier notes, this is not a bug.

Thanks,
Umesh