Bug #34732 mysqlbinlog does not print default values for auto_increment variables
Submitted: 21 Feb 2008 16:02 Modified: 26 Mar 2008 19:11
Reporter: Sven Sandberg Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.0, 5.1 OS:Any
Assigned to: Sven Sandberg
Tags: auto_increment, auto_increment_increment, auto_increment_offset, mysqlbinlog, replication
Triage: D3 (Medium)

[21 Feb 2008 16:02] Sven Sandberg
Description:
mysqlbinlog does not output the values of auto_increment_increment and auto_increment_offset if they are both 1. This is wrong, because a client that executes the binlog may have different values for these variables. In other words, the following scenario produces wrong results:

 - binlog is generated by a client that has default values for auto_increment_*

 - binlog is replayed using mysqlbinlog on a client that has other values for auto_increment_*

The same probably applies to more of the variables that are saved in the binlog (grep for '@@' in log_event.cc).

How to repeat:
Execute the following test case. Here, no replication is done. A binlog is created on slave, then replayed using mysqlbinlog on master, after setting @@global.auto_increment_*

source include/master-slave.inc;
source include/have_binlog_format_mixed_or_statement.inc;

--echo ==== on slave ====
connection slave;
STOP SLAVE;

CREATE TABLE t1 (a INT, b INT NOT NULL AUTO_INCREMENT KEY);
INSERT INTO t1(a) VALUES (1), (2), (3);
SELECT * FROM t1;

--echo ==== on master ====
connection master;
SET @@global.auto_increment_increment = 2;
SET @@global.auto_increment_offset = 2;
exec $MYSQL_BINLOG --force-if-open $MYSQLTEST_VARDIR/log/slave-bin.000001 | $MYSQL;
SELECT * FROM t1;

Suggested fix:
Make sure that all variables are written at least once by mysqlbinlog.
[27 Feb 2008 15:25] Sven Sandberg
I will fix this in 5.1 while fixing BUG#31168.
[27 Feb 2008 18:39] Omer Barnir
workaround: {echo 'set auto_increment_increment = <your value here>'; cat binlog-file.NNNN; } | mysql
[17 Mar 2008 4:00] Sven Sandberg
The patch pushed for BUG#31168 fixes this bug too.
[25 Mar 2008 11:23] Bugs System
Pushed into 5.1.24-rc
[26 Mar 2008 19:00] Bugs System
Pushed into 6.0.5-alpha
[26 Mar 2008 19:11] Jon Stephens
Documented bugfix in the 5.1.24 and 6.0.5 changelogs as follows:

        mysqlbinlog did not output the values of auto_increment_increment and
        auto_increment_offset when both were equal to their
        default values (for both of these variables, the default is 1). This
        meant that a binary log recorded by a client using the defaults for both
        variables and then replayed on another client using its own values for
        either or both of these variables produced erroneous results.
[2 Apr 2008 20:13] Jon Stephens
Also noted in the 5.1.23-ndb-6.3.11 changelog.