MySQL Bugs: #34875: innodb_autoinc_lock

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:	None
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

Description:
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

http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html

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.

Input:
--Error 1238
SET global innodb_autoinc_lock_mode = 1;

Output:
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.

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

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.

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.