MySQL Bugs: #77417: Applying create temporary table SQL on a slave with replicate-rewrite-db fails

Bug #77417	Applying create temporary table SQL on a slave with replicate-rewrite-db fails
Submitted:	19 Jun 2015 15:16	Modified:	4 Dec 2015 17:26
Reporter:	JAMES HETHERINGTON	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.6.19, 5.6.25, 5.7.8	OS:	Linux (ubuntu 14.04.2)
Assigned to:		CPU Architecture:	Any

Description:
We have created a slave database and are using replicate-rewrite-db as a parameter in the my.cnf as the slave db must have a different name to the master.  Applying the relay logs keeps failing with 
'2015-06-19 11:54:41 15607 [ERROR] Slave SQL: Error 'Table 'mdl_backup_ids_temp' already exists' on query. Default database: 'moodlerc_29Jun2015'. Query: 'CREATE TEMPORARY TABLE mdl_backup_ids_temp...
'

In the past we have created many slaves of this master successfully, but without using replicate-rewrite-db. 

I think the problem is a re-occurrence of an old MySQL bug (46861), in which clients which disconnect before dropping their temporary tables cause the master to write a 'drop temporary table masterdb.tablename' statement to the binary logs.  However this means that when run on the slave instance the DROP statement either runs against another db with a matching name to the original master or is not run at all, whereas it should be run against a slave database with the name specified in replicate-rewrite-db.  Thus when a CREATE TEMPORARY TABLE tabname statement comes up for execution it fails as the DROP has not been executed in that specific database.    

Looking at the binary logs on the master I can see some 
DROP TEMPORARY TABLE `mdl_backup_ids_temp` /* generated by server */
statements and some 
DROP TEMPORARY TABLE IF EXISTS `livemoodle`.`mdl_backup_ids_temp` /* generated by server */
statements, and it is the latter that I guess are the problem, the ones that the server has written when clients have not dropped their temporary tables before disconnecting, and which explicitly mention the database name. 

How to repeat:
Backup master database with mysqldump and master-data=2
Instantiate slave from dumpfile but with different db name
Add replicate-rewrite-db to slave my.cnf and restart instance
specify master_host, master_port, master_user etc on slave instance
paste in command to set master_log_file and master_log_pos from original dumpfile
start slave

2015-06-19 11:47:36 15607 [Warning] Slave: Table 'mdl_backup_ids_temp' already exists Error_code: 1050...

skipping error with SQL_SLAVE_SKIP_COUNTER results in it occurring again later.

Suggested fix:
stop the master writing dbname.tabname to binary logs when a client has disconnected without closing temporary table first, writing just the table name should work.

Alternatively add a replicate-rewrite-statement parameter which can intercept specific statements and rewrite them so we can strip the database name out of the statement.  There may already be such a thing, but I looked in the change-replication filter documentation and could not find it!

To try and replicate, I created a temporary table and dropped it with specifying the schema name on the command line, and in the binary logs the schema name was not there.

I then created a temporary table and dropped it without specifying the schema name on the command line, and in the binary logs the schema name was not there.

So this looks correct, but does not match my experience with the replicating of the production database with replicate_rewrite_db.

Hello James,

Thank you for the report.

Thanks,
Umesh

I have an SR open about this - 3-10954054701 : Replication with replicate-rewrite-db fails with temporary table already exists errors

I'm not sure if it is a bug or not.  The latest comment from Oracle says
'the rewrite stops working on the slave when the IF NOT EXISTS option is explicitly used by the application/client'.

test results

Attachment: 77417_mtr.txt (text/plain), 17.88 KiB.

Posted by developer:
 
Same reason as BUG#19942146, different symptom.

// 5.7.8 behaves same way

Thanks for your feedback. This has been fixed in upcoming versions and the following was noted in the 5.6.29 and 5.7.11 change logs:

As part of the fix for Bug #16290902, when writing a DROP TEMPORARY TABLE IF EXISTS query into the binary log, the query is no longer preceded by a USE `db` statement. Instead the query uses a fully qualified table name, for example DROP TEMPORARY TABLE IF EXISTS `db`.`t1`;. This changed the application of replicate-rewrite-db filter rules, as they work only on the default database specified in a USE statement. This caused slaves to fail when the resulting CREATE TEMPORARY TABLE was applied. The fix ensures that at the time of writing a DROP TEMPORARY TABLE IF EXISTS query into the binary log, a check is made for the default database. If it exists then the query is written as USE default_db in the binary log. If a default database is not present then the query is logged with the qualified table name.