| Bug #16381 | multiple tables query update AND replication slave replicate-do-table | ||
|---|---|---|---|
| Submitted: | 11 Jan 2006 18:10 | Modified: | 30 Jan 2006 12:08 |
| Reporter: | Pierre-Yves Goubet | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | Ver 14.12 Distrib 5.0.17 | OS: | Linux (Gentoo Linux - kernel 2.6.14) |
| Assigned to: | CPU Architecture: | Any | |
[12 Jan 2006 8:29]
Valeriy Kravchuk
Thank you for a problem report. Please, send the exact content of my.cnf files from master and slave, and exact sequence of statements that demonstrates the problem you described.
[16 Jan 2006 14:56]
Pierre-Yves Goubet
An INSERT, UPDATE, etc into `othertable` table is properly replicated and executed on the slave. But a query that joins 2 tables is tried to be executed on the slave. update table1, table2 set table2.data=table1.data where table1.ID=table2.ID; --> Error 'Table 'db.table1' doesn't exist' on query. Default database: 'db'. Query: 'update table1, table2 set table2.value=table1.value where table1.ID=table2.ID'
[30 Jan 2006 12:08]
Valeriy Kravchuk
Thank you for a problem report. Sorry, but I was not able to repeat the problem as you described on latest 5.0.19-BK build on Linux:
openxs@suse:~/dbs/5.0> bin/mysqld_safe --log-warnings &
[2] 9022
WARNING: Found /home/openxs/dbs/5.0/var/my.cnf
Datadir is deprecated place for my.cnf, please move it to /home/openxs/dbs/5.0
openxs@suse:~/dbs/5.0> Starting mysqld daemon with databases from /home/openxs/dbs/5.0/var
openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.19
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: repl
Master_Port: 3340
Connect_Retry: 60
Master_Log_File: mysql-bin.002
Read_Master_Log_Pos: 2857
Relay_Log_File: suse-relay-bin.000005
Relay_Log_Pos: 811
Relay_Master_Log_File: mysql-bin.002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table: test.othertable
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 2857
Relay_Log_Space: 811
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
1 row in set (0.00 sec)
On master:
mysql> CREATE TABLE `othertable` (
-> `ID` int(10) unsigned NOT NULL auto_increment,
-> `data` varchar(32) NOT NULL,
-> PRIMARY KEY (`ID`)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.05 sec)
mysql> CREATE TABLE `table1` (
-> `ID` int(11) NOT NULL auto_increment,
-> `value` varchar(32) NOT NULL,
-> PRIMARY KEY (`ID`)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO `table1` VALUES (10, 'data1');
Query OK, 1 row affected (0.01 sec)
mysql> CREATE TABLE `table2` (
-> `ID` int(11) NOT NULL auto_increment,
-> `value` varchar(32) NOT NULL,
-> PRIMARY KEY (`ID`)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO `table2` VALUES (10, '');
Query OK, 1 row affected (0.00 sec)
mysql> insert into `othertable` (data) values('abc');
Query OK, 1 row affected (0.01 sec)
Back on slave:
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| loadtable |
| othertable |
+----------------+
2 rows in set (0.00 sec)
mysql> select * from othertable;
+----+------+
| ID | data |
+----+------+
| 1 | abc |
+----+------+
1 row in set (0.00 sec)
On master:
mysql> update `othertable` set data='ABC';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update `table1` set `value` = 'DATA1';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update table1, table2 set table2.value=table1.value where table1.ID=table2.ID;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
On slave:
mysql> select * from othertable;
+----+------+
| ID | data |
+----+------+
| 1 | ABC |
+----+------+
1 row in set (0.00 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| loadtable |
| othertable |
+----------------+
2 rows in set (0.01 sec)
mysql> exit
Bye
openxs@suse:~/dbs/5.0> tail var/suse.err
060128 09:30:07 mysqld ended
060128 09:30:46 mysqld started
060128 9:30:46 InnoDB: Started; log sequence number 0 43655
060128 9:30:46 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=suse-relay-bin' to avoid this problem.
060128 9:30:46 [Note] /home/openxs/dbs/5.0/libexec/mysqld: ready for connections.
Version: '5.0.19' socket: '/tmp/mysql.sock' port: 3306 Source distribution
060128 9:30:47 [Note] Slave I/O thread: connected to master 'repl@localhost:3340', replication started in log 'mysql-bin.002' at position 2194
060128 9:30:47 [Note] Slave SQL thread initialized, starting replication in log
'mysql-bin.002' at position 2194, relay log './suse-relay-bin.000003' position:
1798
openxs@suse:~/dbs/5.0>
So, I see no errors and warnings since slave startup. Please, try to repeat with newer version, 5.0.18. Looks like the bug, if any, is already fixed.
Description: Replication breaks if replicate-do-table speficied on the slave and master execute an update query on multiple tables. Error on the slave : Error 'Table 'db.othertable' doesn't exist' on query. Default database: 'db'. Query: 'update table1, table2 set ..' How to repeat: 1 master server that do logbin for replication. 1 slave server that replicate 1 table from the master (replicate-do-table=db.tablename parameter). The query 'update table1, table2 set table1.field1=table2.field2 where ...' breaks the replication because the slave does not have the tables {table1} and {table2} but just the table {tablename}. An easy way to restore the replication is : STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; START SLAVE; But, the query should have been executed on the slave because of the replicate-do-table parameter.