MySQL Bugs: #25144: replication / binlog with view breaks

Bug #25144	replication / binlog with view breaks
Submitted:	18 Dec 2006 15:52	Modified:	15 Jul 2008 22:00
Reporter:	Martin Friebe (Gold Quality Contributor) (SCA)
Status:	Closed
Category:	Server: Locking	Severity:	S3 (Non-critical)
Version:	5.0.34-BK, 5.0.30	OS:	Linux (Linux, freebsd)
Assigned to:	Dmitri Lenev	Target Version:	6.0
Tags:	VIEW, replication, rt_q2_2007
Triage:	D2 (Serious)

Description:
I got a little confused with my own report in Bug #25140...

So below is the proper test case.

Dropping or altering a view, while executing an insert or update on it, will leat to the
binlog containing the statements in the wrong order.
The drop or alter view will be first in the binlog, followed by the update or
insert.

How to repeat:
#test
source include/master-slave.inc;

connection master;
create table t1 (a int, b int);
create table t2 (a int, b int);
create view  t3 as select * from t1;

connection master;
select get_lock('a',1);

connection master1;
send insert into t3 values (11, get_lock('a',200)*0 );

connection master;
alter view t3 as select * from t2;
do release_lock('a');

connection master1;
insert into t3 values (12, 3 );

select * from t1;
select * from t2;

save_master_pos;

connection slave;
sync_with_master;

select * from t1;
select * from t2;

# End of 5.0 tests

#result
stop slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
reset master;
reset slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
start slave;
create table t1 (a int, b int);
create table t2 (a int, b int);
create view  t3 as select * from t1;
select get_lock('a',1);
get_lock('a',1)
1
insert into t3 values (11, get_lock('a',200)*0 );
alter view t3 as select * from t2;
do release_lock('a');
insert into t3 values (12, 3 );
select * from t1;
a	b
11	0
select * from t2;
a	b
12	3
select * from t1;
a	b
11	0
select * from t2;
a	b
12	3

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.

test case and expected result

Attachment: rpl_view2.tgz (application/x-compressed-tar, text), 481 bytes.

Thank you for a bug report. Verified just as described with 5.0.34-BK on Linux. Test
produces the following result, different from expected:

openxs@suse:~/dbs/5.0/mysql-test> cat r/rpl_bug25144.log
stop slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
reset master;
reset slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
start slave;
create table t1 (a int, b int);
create table t2 (a int, b int);
create view  t3 as select * from t1;
select get_lock('a',1);
get_lock('a',1)
1
insert into t3 values (11, get_lock('a',200)*0 );
alter view t3 as select * from t2;
do release_lock('a');
insert into t3 values (12, 3 );
select * from t1;
a       b
11      0
select * from t2;
a       b
12      3
select * from t1;
a       b
select * from t2;
a       b
11      0
12      3

This is too large for 5.0. This can be fixed in 5.1. Bug#12093 is similar to this bug.

See WL#3726 Transactional DDL locking for all metadata objects

Note that this has been moved to target 5.2

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

ChangeSet@1.2578, 2007-09-04 13:32:01+04:00, dlenev@mockturtle.local +13 -0
  Tentative fix for bug#25144 "replication / binlog with view breaks".
  
  The fact that CREATE/ALTER/DROP VIEW statements on a view were not
  waiting for statements that were using this view to complete led
  to incorrect sequence of statements in the binary log in cases
  when one used statement based logging.
  
  This patch solves the problem by changing implementation of
  CREATE/ALTER and DROP VIEW statements in such way that they
  take exclusive meta-data lock on a view to be changed. Since
  statements that use views already take shared meta-data lock
  on them this ensures that statements that modify the view
  and statements that use it are properly isolated from each
  other and appear in correct order in binary log.
  
  Note that this patch is based on the series of previous patches
  that significantly change code responsible for meta-data locking.
  
  As always questions for reviewer are marked by QQ.

Patch has been approved and queued in bzr_mysql-6.0-3726

Queued in 6.0.6

Noted in 6.0.6 changelog.

Statements to create, alter, or drop a view were not waiting for
completion of statements that were using the view, which led to
incorrect sequences of statements in the binary log when
statement-based logging was enabled.