I recently wrote about non-deterministic queries in the replication stream. That’s resolved by using either MIXED or ROW based replication rather than STATEMENT based.
Another thing that’s not fully handled by STATEMENT based replication is temporary tables. Imagine the following:
- Master: CREATE TEMPORARY TABLE rpltmpbreak (i INT);
- Wait for slave to replicate this statement, then stop and start mysqld (not just STOP/START SLAVE)
- Master: INSERT INTO rpltmpbreak VALUES (1);
- Slave: SHOW SLAVE STATUS \G
If for any reason a slave server shuts down and restarts after the temp table creation, replication will break because the temporary table will no longer exist on the restarted slave server. It’s obvious when you think about it, but nevertheless it’s quite …[Read more]