MySQL Bugs: #23894: mysqlbinlog outputs SET INSERT_ID=n statements unnecesarily with --database

Bug #23894	mysqlbinlog outputs SET INSERT_ID=n statements unnecesarily with --database
Submitted:	2 Nov 2006 16:29	Modified:	27 Apr 2012 16:41
Reporter:	James Day	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.0.27	OS:	Linux (RHES3,Windows XP)
Assigned to:	Daogang Qu	CPU Architecture:	Any
Tags:	bfsm_2006_12_07

Description:
mysqlbinlog --database=bar N-bin.000002 >002bar.txt

will output all the SET INSERT_ID=n assignments in the binary log even if the database bar isn't mentioned in the binary log.

How to repeat:
use test
drop table if exists foo;
create table foo (foo int, bar int auto_increment, unique key (bar)) engine=innodb;
set autocommit=0;
insert into foo (foo) values (1);
commit;
insert into foo (foo, bar) values (2, bar+100);
commit;
drop table if exists foo;

\bin\mysqlbinlog --database=bar N-bin.000002 >002bar.txt

produces this filtered binary log output:

/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
# at 4
#061102 16:21:52 server id 5632  end_log_pos 98 	Start: binlog v 4, server v 5.0.27-community-log created 061102 16:21:52 at startup
ROLLBACK;
# at 98
# at 185
# at 330
# at 398
#061102 16:22:09 server id 5632  end_log_pos 28 	Intvar
SET INSERT_ID=1;
# at 426
# at 521
#061102 16:22:09 server id 5632  end_log_pos 548 	Xid = 9
COMMIT;
# at 548
# at 616
# at 725
#061102 16:22:09 server id 5632  end_log_pos 752 	Xid = 11
COMMIT;
# at 752
# at 839
#061102 16:22:26 server id 5632  end_log_pos 858 	Stop
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

There's no need for the SET INSERT_ID=1 line to be there.

Also no need for the commits but that's similar bug #23890.

Suggested fix:
Don't emit the set of insert id unless it's necessary.

Hi,
  Will this be fixed at 5.1.x? 
  If I have executed:

use db1;
create table test( id int auto_increment primary key ) engine=innodb;
insert into test values(NULL);
insert into test values(NULL);

use test;
create table test2( id int auto_increment primary key ) engine=innodb;
insert into test2 values(NULL);

  Then the binlog( --database=db1 ) seems like:
use db1;
#< cut off some operations >
SET INSERT_ID=2
#< cut off some operations, still >

#< operations on the TEST database >
SET INSERT_ID=1

  So, when I use the binlog to recover my data, the INSERT_ID of db1 will be set to 1, which leads to primary key conflict if I try to execute the following SQL:
  insert into db1.test values(NULL);

  I think this problem is critical...

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/80998

2789 Dao-Gang.Qu@sun.com	2009-08-18
      Bug #23894  	mysqlbinlog outputs SET INSERT_ID=n statements unnecesarily with --database
      
      mysqlbinlog --database=bar N-bin.000003 >003bar.txt
      will output all the SET INSERT_ID=n assignments in the binary log even if the database argument 
      'bar' isn't mentioned in the binary log. The COMMITS isn't associated with database in the 
      binary log, so the SET INSERT_ID=n assignments will be output in any situation.
      
      To fix the problem, we need record and update the using database from context. So that 
      the SET INSERT_ID=n assignments will be output in the following two cases:
      1. The database argument is consistent with the using database.
      2. The database argument is NULL.
     @ client/mysqlbinlog.cc
        Added database_using[] array to record and update the using database from context.

Fixed in 5.6+. Documented in the 5.6.5 changelog as follows:

        mysqlbinlog --database=dbname included all SET INSERT_ID=n
        assignments from the binary log in its output, even if the
        database dbname was not mentioned in the binary log. This was
        due to the fact that COMMIT statements were not associated with
        any database in the binary log. Now in such cases, the current
        database is tracked so that only the SET INSERT_ID assignments
        relevant to the context of changes to tables in database dbname
        are printed.

Closed.