Description:
Doing a simultaneous update on two tables using a single update statement does not seem to propagate correctly to slave servers - if the slave server is configured to slave only a subset of the data....using replicate-wild-do-table/replicate-do-table
A simultaneous query being of the form
UPDATE [table1], [table2] SET [table1].field=[mary],[table2].field=[sally] WHERE [table1].key=bill and [table2].key=bill
A full database slave seems to however correctly update it's tables when the query is executed.
How to repeat:
-**- On Master Server -**-
mysql> update jobs,jobs_search_opt set jobs.sites='|1|47|2|11|7|9|16|24|25|50|42|',jobs_search_opt.sites='1,2,7,9,11,16,24,25,42,47,50' where jobs.job_id=2936801 and jobs_search_opt.job_id=2936801;
Query OK, 2 rows affected (0.02 sec)
Rows matched: 2 Changed: 2 Warnings: 0
* checking in the master's binary logfile
hyperion:/var/log/mysql# mysqlbinlog mysql-bin.185 > /tmp/check.sql
#050302 21:21:11 server id 1 log_pos 235131578 Query thread_id=2335590 exec_time=0 error_code=0
SET TIMESTAMP=1109798471;
update jobs,jobs_search_opt set jobs.sites='|1|47|2|11|7|9|16|24|25|50|42|',jobs_search_opt.sites='1,2,7,9,11,16,24,25,42,47
,50' where jobs.job_id=2936801 and jobs_search_opt.job_id=2936801;
* checking in the masters' database
mysql> select sites from jobs where job_id=2936801;
+--------------------------------+
| sites |
+--------------------------------+
| |1|47|2|11|7|9|16|24|25|50|42| |
+--------------------------------+
1 row in set (0.00 sec)
mysql> select sites from jobs_search_opt where job_id=2936801;
+------------------------------+
| sites |
+------------------------------+
| 1,2,7,9,11,16,24,25,42,47,50 |
+------------------------------+
1 row in set (0.00 sec)
--- On Slave Server ---
* but query has not replicated to the slave ??
-- Slave config --
replicate-do-table=planet.login_db
replicate-do-table=planet.adverts
replicate-wild-do-table=planet.jobs%
mysql> select sites from jobs_search_opt where job_id=2936801;
+---------------------------------+
| sites |
+---------------------------------+
| 1,2,7,9,11,16,24,25,27,42,47,50 |
+---------------------------------+
1 row in set (0.00 sec)
mysql> select sites from jobs where job_id=2936801;
+-----------------------------------+
| sites |
+-----------------------------------+
| |1|47|2|11|7|9|16|24|25|50|42|27| |
+-----------------------------------+
1 row in set (0.00 sec)
mysql> show slave status;
+---------------------+-------------+-------------+---------------+-----------------+---------------------+----------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+------------+------------+--------------+---------------------+-----------------+
| Master_Host | Master_User | Master_Port | Connect_retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_do_db | Replicate_ignore_db | Last_errno | Last_error | Skip_counter | Exec_master_log_pos | Relay_log_space |
+---------------------+-------------+-------------+---------------+-----------------+---------------------+----------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+------------+------------+--------------+---------------------+-----------------+
| hyperion.production | repl | 3306 | 60 | mysql-bin.185 | 235904750 | arnold-relay-bin.012 | 956834648 | mysql-bin.185 | Yes | Yes | | | 0 | | 0 | 235904750 | 956834648 |
+---------------------+-------------+-------------+---------------+-----------------+---------------------+----------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+------------+------------+--------------+---------------------+-----------------+
1 row in set (0.00 sec)
Suggested fix:
The workaround is to do the query as two separate updates, which appear to work correctly.
