MySQL Bugs: #61548: autoinc value persistency breaks certain replication scenarios

Bug #61548	autoinc value persistency breaks certain replication scenarios
Submitted:	17 Jun 2011 8:55	Modified:	16 Nov 2012 13:07
Reporter:	Domas Mituzas	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.1 and others	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
in a simple statement based replication situation where:

1) Table A has AUTO-INC on master and does not have AUTO-INC on slave
2) Table B does not have trigger on master, but does have a trigger on a slave that inserts data to a table C which has AUTO-INC
3) INSERT happens to table A in one transaction, then INSERT or UPDATE happens on table B in another transaction

auto-inc on table C inherits the value of auto-inc on table A, hence causing replication failures. 

If auto-inc intvar in binlog did not persist past INSERT statement it was written for, this wouldn't happen. 

How to repeat:
reproduce the scenario described above

Suggested fix:
don't persist the auto-inc value past INSERT (and maybe destroy it at transaction boundary too)

It is easy to verify that insert_id is not "reset" in any way at transaction boundaries, until explicitly set next time auto-inc value is used on master:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysqlbinlog -uroot -proot "c:\Docume
nts and Settings\All Users\Application Data\MySQL\MySQL Server 5.1\data\toshiba-
user-bin.000010"
...
# at 256
#110617 12:44:00 server id 1  end_log_pos 390   Query   thread_id=2     exec_tim
e=0     error_code=0
SET TIMESTAMP=1308303840/*!*/;
create table a(c1 int auto_increment primary key, c2 int) engine=InnoDB
/*!*/;
# at 390
#110617 12:44:48 server id 1  end_log_pos 509   Query   thread_id=2     exec_tim
e=1     error_code=0
SET TIMESTAMP=1308303888/*!*/;
create table b(c1 int primary key, c2 int) engine=InnoDB
/*!*/;
# at 509
#110617 12:45:17 server id 1  end_log_pos 577   Query   thread_id=2     exec_tim
e=0     error_code=0
SET TIMESTAMP=1308303917/*!*/;
BEGIN
/*!*/;
# at 577
#110617 12:45:14 server id 1  end_log_pos 605   Intvar
SET INSERT_ID=1/*!*/;
# at 605
#110617 12:45:14 server id 1  end_log_pos 701   Query   thread_id=2     exec_tim
e=0     error_code=0
SET TIMESTAMP=1308303914/*!*/;
insert into a(c2) values (1), (2)
/*!*/;
# at 701
#110617 12:45:17 server id 1  end_log_pos 728   Xid = 10
COMMIT/*!*/;
# at 728
#110617 12:45:51 server id 1  end_log_pos 796   Query   thread_id=2     exec_tim
e=0     error_code=0
SET TIMESTAMP=1308303951/*!*/;
BEGIN
/*!*/;
# at 796
#110617 12:45:47 server id 1  end_log_pos 886   Query   thread_id=2     exec_tim
e=0     error_code=0
SET TIMESTAMP=1308303947/*!*/;
insert into b values (1, 2)
/*!*/;
# at 886
#110617 12:45:51 server id 1  end_log_pos 913   Xid = 13
COMMIT/*!*/;
# at 913
#110617 12:51:55 server id 1  end_log_pos 981   Query   thread_id=3     exec_tim
e=0     error_code=0
SET TIMESTAMP=1308304315/*!*/;
BEGIN
/*!*/;
# at 981
#110617 12:51:55 server id 1  end_log_pos 1009  Intvar
SET INSERT_ID=3/*!*/;
# at 1009
#110617 12:51:55 server id 1  end_log_pos 1100  Query   thread_id=3     exec_tim
e=0     error_code=0
SET TIMESTAMP=1308304315/*!*/;
insert into a(c2) values (3)
/*!*/;
# at 1100
#110617 12:51:55 server id 1  end_log_pos 1127  Xid = 21
COMMIT/*!*/;

Looks like the only way to solve this is for slave to "unset" insert_id somehow on commit/rollback when processing relay log. 

But I doubt we can guarantee non-breakable statement-based replication when tables on master and slave are defined differently anyway (see bug #45677 also). So I would not be surprised if this report will be considered as feature request or even "Won't fix" eventually.

the same problem is when changes are in same transaction - I probably made it too complicated in the description :-) so it has to be unset after each insert statement

regarding the "not supported" - the standard way to do schema changes is to do change on slave first, then om master. MySQL does not have functionality to do online schema changes on all replicating servers at once, and I'm not sure if dropping PK column (and adding another one) would work really well with RBR. 

current way of auto-inc replication is broken, and can be broken in a really simple test without any concurrency involved - intvars are attributed to wrong statements, this cannot be right.

would SET ONESHOT .. help ?

hey Shane, it probably would, if it would be a statement written to binlog. ATM it is a separate binary event type (with two subtypes), and it gets interpreted by replication thread in its own way.

See also Bug #67504

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Fixed in 5.5.30/5.6.10/trunk (currently tagged 5.7.1).

Closed.