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
Triage: Triaged: D3 (Medium)

[26 Apr 2010 15:15] Paul Dubois
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".
[26 Apr 2010 15:32] Valeriy Kravchuk
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)
[27 Apr 2010 22:15] Jimmy Yang
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...
[27 Apr 2010 22:57] Paul Dubois
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"?
[28 Apr 2010 0:05] Jimmy Yang
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".
[30 Apr 2010 0:47] Bugs System
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
[5 May 2010 4:59] Jimmy Yang
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.
[15 Jun 2010 8:08] Bugs System
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)
[15 Jun 2010 8:23] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100615080558-cw01bzdqr1bdmmec) (version source revid:marko.makela@oracle.com-20100505100507-6kcd2hf32hruxbv7) (pib:16)
[28 Jul 2010 0:14] Calvin Sun
Fixed in 5.1.48 and 5.5.5.
[28 Jul 2010 19:34] Paul Dubois
Noted in 5.1.48, 5.5.5 changelogs.

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