Description:
When dropping or altering a view, while executing an insert or update on it, the binlog will contain the statements in the wrong order.
The drop or alter view will be first in the binlog, followed by the update or insert.
Therefore replication of the statements is not possible
In the examples, open 2 mysql sessions. The user log is only used for timing.
How to repeat:
#prepare:
drop table if exists b1; create table b1 (a int, b int);
drop table if exists b2; create table b2 (a int, b int);
drop view if exists v1; create view v1 as select * from b1;
# mysql 1
select get_lock('a',1);
reset master;
# mysql 2 (will wait)
insert into v1 values (11, get__lock('a',200) );
# mysql 1
alter view v1 as select * from b1;
do release_lock('a');
# wait for mysql 2 to execute...
flush logs;
drop table if exists b1;
### same with update and alter
drop table if exists b1; create table b1 (a int, b int);
insert into b1 values(1,2);
drop view if exists v1; create view v1 as select * from b1;
# mysql 1
select get_lock('a',1);
reset master;
# mysql 2
update v1 set a=1 where b = get_lock('a',200);
# mysql 1
drop view v1;
do release_lock('a');
flush logs;
drop table if exists b1;
Suggested fix:
Either make "drop view" / "alter view" waiting for any insert/update query on the view to finish
OR include the masters definition of the view in the binlog. (as special comment, or as rewrite)
The last option (bin log contains rewritten sql) will affect do-replicate config.
Description: When dropping or altering a view, while executing an insert or update on it, the binlog will contain the statements in the wrong order. The drop or alter view will be first in the binlog, followed by the update or insert. Therefore replication of the statements is not possible In the examples, open 2 mysql sessions. The user log is only used for timing. How to repeat: #prepare: drop table if exists b1; create table b1 (a int, b int); drop table if exists b2; create table b2 (a int, b int); drop view if exists v1; create view v1 as select * from b1; # mysql 1 select get_lock('a',1); reset master; # mysql 2 (will wait) insert into v1 values (11, get__lock('a',200) ); # mysql 1 alter view v1 as select * from b1; do release_lock('a'); # wait for mysql 2 to execute... flush logs; drop table if exists b1; ### same with update and alter drop table if exists b1; create table b1 (a int, b int); insert into b1 values(1,2); drop view if exists v1; create view v1 as select * from b1; # mysql 1 select get_lock('a',1); reset master; # mysql 2 update v1 set a=1 where b = get_lock('a',200); # mysql 1 drop view v1; do release_lock('a'); flush logs; drop table if exists b1; Suggested fix: Either make "drop view" / "alter view" waiting for any insert/update query on the view to finish OR include the masters definition of the view in the binlog. (as special comment, or as rewrite) The last option (bin log contains rewritten sql) will affect do-replicate config.