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