Bug #33386 Row based replication misses cross database create statements
Submitted: 20 Dec 2007 0:16 Modified: 3 Aug 2008 11:19
Reporter: bhushan uparkar Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.1.22 OS:Any
Assigned to: CPU Architecture:Any

[20 Dec 2007 0:16] bhushan uparkar
Description:
I am using MySql 5.1.22 on debian linux i.e. Linux bhushandev 2.6.18-5-amd64 #1 SMP Tue Oct 2 20:37:02 UTC 2007 x86_64 GNU/Linux

Here is part of replication configuration from master "/etc/mysql/my.cnf"

server-id               = 51
log_bin                 = /var/lib/mysql/mysql-bin.log
expire_logs_days        = 3
max_binlog_size         = 100M
binlog_format           = ROW
=================
Here is replication configuration part from slave "/etc/mysql2/my.cnf"

master-host=bhushandev.rmd.admob.int
master-user=XXXX
master-password=XXXXX
master-connect-retry=60
master-port=3306

======
 As noted above I use Row based replication for master-slave replication. While doing cross database create tables and subsequently populating them caused my replication to break. On slave I see the following error message,

  Last_SQL_Error: Error 'Table 'circle_data.class_222' doesn't exist' on opening table `circle_data`.`class_222`

I noticed that the mater do not records cross database create statements i.e. Data Defination statements, but records cross database DML statements correctly.
Please see details in "How To Repeat" sections
 

How to repeat:
I use mysql 5.1.22 alongwith ROW based replication. 

I have 3 databases,
zoomla
bozo
circle_data

I did the following on MASTER,

use zoomla;

Create table circle_data.class_22
     ( name varchar(50) , id integer primary key auto_increment );

create table circle_data.class_222  engine=innodb as select * from
circle_data.class_22 limit 0;

insert into circle_data.class_222(name) values('admob');

And then on SLAVE, I run into following error

Error 'Table 'circle_data.class_222' doesn't exist' on opening table
`circle_data`.`class_222`

The slave got insert data but not the table creation. 

Suggestion:

  There is work around for this problem, as follows
  use circle_data;
  Create table class_222  engine=innodb as select * from class_22 limit
0;
  # We should avoid the database name from create/drop/alter statements.

Note:
 When I switched to statement level replication, the slave created the
table as well as ppopulated i.e. it did not missed create table event.

Let me know if you have questions/comments.

Thanks,
Bhushan

P.S. We like 5.1 it has helped us a lot, and by fixing bugs similar to above will make this product great.

Suggested fix:
Ideally the row based replication should catch all Data Defination statements i.e. DDL.
[8 Jan 2008 20:11] Susanne Ebrecht
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html
[3 Aug 2008 11:19] Sven Sandberg
This is not closed. It is the same bug as BUG#34707. I'm setting this to duplicate since BUG#34707 contains more information.