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