MySQL Bugs: #34732: mysqlbinlog does not print default values for auto

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	CPU Architecture:	Any
Tags:	auto_increment, auto_increment_increment, auto_increment_offset, mysqlbinlog, replication

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.

I will fix this in 5.1 while fixing BUG#31168.

workaround: {echo 'set auto_increment_increment = <your value here>'; cat binlog-file.NNNN; } | mysql

The patch pushed for BUG#31168 fixes this bug too.

Pushed into 5.1.24-rc

Pushed into 6.0.5-alpha

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.

Also noted in the 5.1.23-ndb-6.3.11 changelog.