Bug #34875 innodb_autoinc_lock_mode is read only
Submitted: 27 Feb 2008 10:25 Modified: 27 Feb 2008 21:50
Reporter: Rizwan Maredia Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.1.22, 5.1.23 OS:Windows
Assigned to: Paul DuBois CPU Architecture:Any
Tags: innodb_autoinc_lock_mode

[27 Feb 2008 10:25] Rizwan Maredia
innodb_autoinc_lock_mode can not be set at runtime. It can only be selected. The following article says that it can be set at runtime


Here is the excerpt that claim this.

Beginning with MySQL 5.1.22, there is a new configuration parameter that controls how InnoDB uses locking when generating values for AUTO_INCREMENT columns. This parameter can be set using the --innodb-autoinc-lock-mode  option at mysqld startup, or at runtime by setting the global innodb_autoinc_lock_mode  system variable.

How to repeat:
Here is how I tested this variable in mysql test environment.

--Error 1238
SET global innodb_autoinc_lock_mode = 1;

SET global innodb_autoinc_lock_mode = 1;
ERROR HY000: Variable 'innodb_autoinc_lock_mode' is a read only variable

Suggested fix:
Make this variable mutable at runtime or change documentation and article.
[27 Feb 2008 10:41] Valeriy Kravchuk
Thank you for a problem report. Verified just as described:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.23-rc-community-debug MySQL Community Server - Debug (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SET global innodb_autoinc_lock_mode = 1;
ERROR 1238 (HY000): Variable 'innodb_autoinc_lock_mode' is a read only variable
mysql> SET @@innodb_autoinc_lock_mode = 1;
ERROR 1238 (HY000): Variable 'innodb_autoinc_lock_mode' is a read only variable
[27 Feb 2008 18:55] Paul DuBois
I noticed this myself today. I will check with Oracle whether this is deliberate and update the manual if so. (Otherwise, it'll need to be reclassified as a server bug.)

Assigning to myself.
[27 Feb 2008 21:50] Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Confirmed that this variable is settable only at server startup. I'll remove the statement from the manual that it can be set at runtime.