Temporary table causing replication to fail

时间：2008-02-22 来源：r_a

在slave上的error_log 080222 17:05:45 [ERROR] Slave SQL: Error 'There is no 'dlb'@'localhost' registered' on query. Default database: 'guibot'. Query: 'create temporary table account_char_num select id,sp_getcharcount(id) as charnum from account where regn=1 and type=121 and last_fail_time>'2008-01-01' group by id order by charnum', Error_code: 1449
080222 17:05:45 [Warning] Slave: There is no 'dlb'@'localhost' registered Error_code: 1449
080222 17:05:45 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000274' position 27694778
Mysql的解决方案：转自 http://forums.mysql.com/read.php?26,52539,52861#msg-52861 As KimSeong Loh said, indeed it can happen if your slave server was restarted. It's a known problem listed at
http://dev.mysql.com/doc/refman/5.0/en/replication-features.html :
Temporary tables are replicated except in the case where you shut down the slave server (not just the slave threads) and you have replicated temporary tables that are used in updates that have not yet been executed on the slave. If you shut down the slave server, the temporary tables needed by those updates are no longer available when the slave is restarted.
The URL also shows a workaround.
For the customers and users who hit this problem, we are preparing a new optional way of logging data changes in the master, what we call "row-based logging", where instead of writing the actual statements to its binary log, the master will write the row changes, then this problem will not exist anymore because temporary tables will not appear in the binary log anymore. We'll still feature and support the current way which can be called "statement-based logging".

Mr. Guilhem Bichot <[email protected]>
MySQL AB, Full-Time Software Developer
Bordeaux, France
www.mysql.com 文档中的相关说明：http://dev.mysql.com/doc/refman/5.0/en/replication-features.html

14.3.1.15. Replication and Temporary Tables

Temporary tables are replicated except in the case where you shut down the slave server (not just the slave threads) and you have replicated temporary tables that are used in updates that have not yet been executed on the slave. If you shut down the slave server, the temporary tables needed by those updates are no longer available when the slave is restarted. To avoid this problem, do not shut down the slave while it has temporary tables open. Instead, use the following procedure:

Issue a STOP SLAVE statement.
Use SHOW STATUS to check the value of the Slave_open_temp_tables variable.
If the value is 0, issue a mysqladmin shutdown command to stop the slave.
If the value is not 0, restart the slave threads with START SLAVE.
Repeat the procedure later until the Slave_open_temp_tables variable is 0 and you can stop the slave.