MySQL Bugs: #53165: Setting innodb_change_buffering=DEFAULT produces incorrect result

Bug #53165	Setting innodb_change_buffering=DEFAULT produces incorrect result
Submitted:	26 Apr 2010 15:15	Modified:	28 Jul 2010 19:34
Reporter:	Paul DuBois	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB Plugin storage engine	Severity:	S3 (Non-critical)
Version:	mysql-5.5	OS:	Any
Assigned to:	Jimmy Yang	CPU Architecture:	Any

Description:
Setting a system variable to DEFAULT should set it to its default value. For innodb_change_buffering, setting it to DEFAULT produces a value different from the value it has when the server starts. (I did not change it in the my.cnf file.)

http://www.innodb.com/doc/innodb_plugin-1.0/innodb-performance.html#innodb-performance-cha... says:

"Beginning with InnoDB Plugin 1.0.3, you can control whether InnoDB performs insert buffering with the system configuration parameter innodb_change_buffering. The allowed values of innodb_change_buffering are none (do not buffer any operations) and inserts (buffer insert operations, the default). You can set the value of this parameter in the MySQL option file (my.cnf or my.ini) or change it dynamically with the SET GLOBAL command, which requires the SUPER privilege. Changing the setting affects the buffering of new operations; the merging of already buffered entries is not affected."

That indicates that the default value is "inserts." But assigning DEFAULT results in a different value:

mysql> select @@global.innodb_change_buffering;
+----------------------------------+
| @@global.innodb_change_buffering |
+----------------------------------+
| inserts                          |
+----------------------------------+
1 row in set (0.00 sec)

mysql> set global innodb_change_buffering=default;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.innodb_change_buffering;
+----------------------------------+
| @@global.innodb_change_buffering |
+----------------------------------+
| none                             |
+----------------------------------+
1 row in set (0.00 sec)

How to repeat:
See above.

Suggested fix:
Assigning DEFAULT should result in "inserts".

Verified just as described with current mysql-trunk from bzr:

77-52-4-109:trunk openxs$ bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.5-m3-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>  select @@global.innodb_change_buffering;
+----------------------------------+
| @@global.innodb_change_buffering |
+----------------------------------+
| inserts                          |
+----------------------------------+
1 row in set (0.01 sec)

mysql> set global innodb_change_buffering=default;
Query OK, 0 rows affected (0.00 sec)

mysql>  select @@global.innodb_change_buffering;
+----------------------------------+
| @@global.innodb_change_buffering |
+----------------------------------+
| none                             |
+----------------------------------+
1 row in set (0.00 sec)

The default value shall be "all" as stated in the 5.5 manual.

http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_change_bufferi...

Note that the same problem occurs in MySQL 5.1 (which uses InnoDB Plugin 1.0.7 currently). Will a fix be made for plugin 1.0.x, and will assigning DEFAULT result in a value of "inserts"?

Yes, Paul, I fixed that separately in 5.1 plugin branch. There added a few more options in 5.5, including "all", while in 5.1 the default should be "insert".

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/107020

3043 Jimmy Yang	2010-04-29
      Fix bug #53165 Setting innodb_change_buffering=DEFAULT produces incorrect
      result

Fix committed for mysql-5.5 with following revsion:
 3063 Jimmy Yang        2010-05-04
      Fix bug #53165, Setting innodb_change_buffering=DEFAULT produces incorrect result.

Fix hand ported to mysql-5.1 plugin with following revision
3444 Jimmy Yang        2010-05-04
      Port fix for 53165 to InnoDB 5.1 plugin. The change buffering options
      are different in 5.1 comparing to that of 5.5, so a hand port is
      necessary to avoid wrong default option to be set by a simple
      branch merge.

Pushed into 5.5.5-m3 (revid:alik@sun.com-20100615080459-smuswd9ooeywcxuc) (version source revid:marko.makela@oracle.com-20100505100507-6kcd2hf32hruxbv7) (merge vers: 5.1.47) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100615080558-cw01bzdqr1bdmmec) (version source revid:marko.makela@oracle.com-20100505100507-6kcd2hf32hruxbv7) (pib:16)

Fixed in 5.1.48 and 5.5.5.

Noted in 5.1.48, 5.5.5 changelogs.

Setting the innodb_change_buffering system variable to DEFAULT
produced an incorrect result.