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

[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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.042 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.