Bug #73367 minimal row image breaks binlog if alter table with trigger and last_insert_id
Submitted: 23 Jul 2014 15:59 Modified: 24 Jul 2014 18:58
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Utilities: Binlog Events Severity:S2 (Serious)
Version:5.6.19 OS:Any
Assigned to: CPU Architecture:Any

[23 Jul 2014 15:59] Shane Bester
Description:
after doing a DDL on the table having a trigger, the binlog gets improperly written, and slaves may either have different results
or stop working with a duplicate key error on the auto_increment column:

[ERROR] Slave SQL: Could not execute Write_rows event on table test.t1; Duplicate entry '0' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY;

How to repeat:
Master started: 
./bin/mysqld_safe --no-defaults --port=3306 --log-bin --binlog-format=row --binlog-row-image=minimal --server-id=1 --tmpdir=. --socket=sock &

Slave started:
./bin/mysqld_safe --no-defaults --port=3307 --server-id=2 --tmpdir=. --socket=sock &

Setup slave:

---------
reset slave;
change master to master_host='127.0.0.1', master_port=3306, master_user='root';
start slave;
show slave status \G
--------

Run on master:

-------
flush logs;
set binlog_row_image='minimal';
drop table if exists t1,t2;
create table t1(a int,b int,primary key(a))engine=innodb;
create table t2(c int primary key not null auto_increment, d int)engine=innodb;
drop trigger if exists `t1_before_insert`;
set sql_mode='';

delimiter $

create trigger `t1_before_insert` before insert on `t1` for each row
begin
 insert into `t2`(d) values (new.b);
 set new.a=last_insert_id();
end $

delimiter ;

insert into t1(b) values (1);
insert into t1(b) values (2);
alter table t2 engine=innodb;
insert into t1(b) values (3);
insert into t1(b) values (4);
alter table t2 engine=innodb;
insert into t1(b) values (5);

select * from t1; ##compare to slave
select * from t2; ##compare to slave
-------------
[24 Jul 2014 18:58] MySQL Verification Team
I have discovered this is a duplicate of internal:
Bug#18684393 - METADATA CHANGES MIGHT CAUSE PROBLEMS WITH TRIGGER EXECUTION.