Bug #25144 replication / binlog with view breaks
Submitted: 18 Dec 2006 14:52 Modified: 6 Mar 2010 19:54
Reporter: Martin Friebe (Gold Quality Contributor) (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Locking Severity:S3 (Non-critical)
Version:5.0.34-BK, 5.0.30 OS:Linux (Linux, freebsd)
Assigned to: Dmitry Lenev CPU Architecture:Any
Tags: replication, rt_q2_2007, VIEW
Triage: D2 (Serious)

[18 Dec 2006 14:52] Martin Friebe
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.
[18 Dec 2006 14:53] Martin Friebe
test case and expected result

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

[19 Dec 2006 21:17] Valeriy Kravchuk
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
[9 Jan 2007 21:37] Konstantin Osipov
This is too large for 5.0. This can be fixed in 5.1. Bug#12093 is similar to this bug.
[17 Feb 2007 13:04] Konstantin Osipov
See WL#3726 Transactional DDL locking for all metadata objects
[12 Jul 2007 15:49] Jeffrey Pugh
Note that this has been moved to target 5.2
[4 Sep 2007 9:16] Bugs System
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.
[18 Jun 2008 9:40] Konstantin Osipov
Patch has been approved and queued in bzr_mysql-6.0-3726
[28 Jun 2008 13:09] Konstantin Osipov
Queued in 6.0.6
[15 Jul 2008 20:00] Paul Dubois
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.
[16 Feb 2010 16:48] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100216101445-2ofzkh48aq2e0e8o) (version source revid:kostja@sun.com-20091230202224-tmxmnhhvlecoej8w) (merge vers: 6.0.14-alpha) (pib:16)
[16 Feb 2010 16:57] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100216101208-33qkfwdr0tep3pf2) (version source revid:kostja@sun.com-20091229121905-q27v9932ho9as2ws) (pib:16)
[17 Feb 2010 0:21] Paul Dubois
Setting report to Need Merge pending push of Celosia into release tree.
[6 Mar 2010 11:02] Bugs System
Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:vvaintroub@mysql.com-20100216221947-luyhph0txl2c5tc8) (merge vers: 5.5.99-m3) (pib:16)
[6 Mar 2010 19:54] Paul Dubois
Noted in 5.5.3 changelog.