MySQL Bugs: #70832: replication fails on truncate of temporary tables

Bug #70832	replication fails on truncate of temporary tables
Submitted:	6 Nov 2013 11:54	Modified:	4 Jan 2015 18:30
Reporter:	Steven Hartland	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.6.14	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	replication, temporary table, truncate table

Description:
Since upgrading to 5.6.14 from 5.5.12 and changing to binlog MIXED, replication between master and slave is now failing randomly with:-
Last_Errno: 1146
Last_Error: Worker 0 failed executing transaction '' at master log mysql-bin.022330, end_log_pos 450870590; Error 'Table 'adverts.tmp_ad_zone_impression' doesn't exist' on query. Default database: 'adverts'. Query: 'TRUNCATE TABLE `tmp_ad_zone_impression`'

The key thing seems to be that table tmp_ad_zone_impression is a temporary table.

Sometimes start slave seems to be enough for the slave to continue, sometimes not.

The 5.6 docs state:-
"In MySQL 5.6, you can switch from statement-based to row-based binary logging mode even when temporary tables have been created. However, while using the row-based format, the MySQL server cannot determine the logging mode that was in effect when a given temporary table was created. For this reason, the server in such cases logs a DROP TEMPORARY TABLE IF EXISTS statement for each temporary table that still exists for a given client session when that session ends. While this means that it is possible that an unnecessary DROP TEMPORARY TABLE statement might be logged in some cases, the statement is harmless, and does not cause an error even if the table does not exist, due to the presence of the IF NOT EXISTS option."

I believe this is likely to be related to the problem, and I suspect "IF NOT EXISTS" is not always being correctly applied.

How to repeat:
Its not clear what the exact replication steps are, but it does seem key that the table being truncated is a temporary table.

We think something like the following but we have been unable to reproduce manually:
con1> create temporary table wibble (wibbleid int);
con1> truncate table wibble;
con2> create temporary table wibble (wibbleid int);
con1> disconnect; // which drops table temporary wibble
con2> truncate table wibble;

Thank you for the report.

Please send us full master and slave error log files and part of mysql-bin.022330 near end_log_pos 450870590;

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

i am experiencing the same thing as well - using 2 servers in master-master mode: 

Server version: 5.6.19-67.0-56-log Percona XtraDB Cluster (GPL), Release rel67.0, Revision 824, WSREP version 25.6, wsrep_25.6.r4111

Here's my logs below:

2014-08-31 00:32:30 27790 [ERROR] Slave SQL: Error 'Table 'eboundservices_adserver.tmp_ad_zone_impression' doesn't exist' on query. Default database: 'eboundservices_adserver'. Query: 'CREATE INDEX `tmp_ad_zone_impression_tmp_ad_zone_impression_ad_id` ON `tmp_ad_zone_impression` (`ad_id`)', Error_code: 1146
2014-08-31 00:32:30 27790 [Warning] WSREP: RBR event 1 Query apply warning: 1, 36661692
2014-08-31 00:32:30 27790 [Warning] WSREP: Ignoring error for TO isolated action: source: 2b2f5822-3060-11e4-a72c-3ee4392feac9 version: 3 local: 0 state: APPLYING flags: 65 conn_id: 38300 trx_id: -1 seqnos (l: 704143, g: 36661692, s: 36661690, d: 36661691, ts: 808256040429870)
2014-08-31 00:32:30 27790 [ERROR] Slave SQL: Error 'Table 'eboundservices_adserver.tmp_ad_zone_impression' doesn't exist' on query. Default database: 'eboundservices_adserver'. Query: 'CREATE INDEX `tmp_ad_zone_impression_tmp_ad_zone_impression_zone_id` ON `tmp_ad_zone_impression` (`zone_id`)', Error_code: 1146
2014-08-31 00:32:30 27790 [Warning] WSREP: RBR event 1 Query apply warning: 1, 36661700
2014-08-31 00:32:30 27790 [Warning] WSREP: Ignoring error for TO isolated action: source: 2b2f5822-3060-11e4-a72c-3ee4392feac9 version: 3 local: 0 state: APPLYING flags: 65 conn_id: 38300 trx_id: -1 seqnos (l: 704151, g: 36661700, s: 36661695, d: 36661699, ts: 808256371506761)
2014-08-31 01:02:47 27790 [ERROR] Slave SQL: Error 'Table 'eboundservices_adserver.tmp_ad_zone_impression' doesn't exist' on query. Default database: 'eboundservices_adserver'. Query: 'CREATE INDEX `tmp_ad_zone_impression_tmp_ad_zone_impression_ad_id` ON `tmp_ad_zone_impression` (`ad_id`)', Error_code: 1146
2014-08-31 01:02:47 27790 [Warning] WSREP: RBR event 1 Query apply warning: 1, 36719761
2014-08-31 01:02:47 27790 [Warning] WSREP: Ignoring error for TO isolated action: source: 2b2f5822-3060-11e4-a72c-3ee4392feac9 version: 3 local: 0 state: APPLYING flags: 65 conn_id: 52530 trx_id: -1 seqnos (l: 762669, g: 36719761, s: 36719760, d: 36719760, ts: 810061694643755)
2014-08-31 01:02:47 27790 [ERROR] Slave SQL: Error 'Table 'eboundservices_adserver.tmp_ad_zone_impression' doesn't exist' on query. Default database: 'eboundservices_adserver'. Query: 'CREATE INDEX `tmp_ad_zone_impression_tmp_ad_zone_impression_zone_id` ON `tmp_ad_zone_impression` (`zone_id`)', Error_code: 1146
2014-08-31 01:02:48 27790 [Warning] WSREP: RBR event 1 Query apply warning: 1, 36719782
2014-08-31 01:02:48 27790 [Warning] WSREP: Ignoring error for TO isolated action: source: 2b2f5822-3060-11e4-a72c-3ee4392feac9 version: 3 local: 0 state: APPLYING flags: 65 conn_id: 52530 trx_id: -1 seqnos (l: 762690, g: 36719782, s: 36719770, d: 36719781, ts: 810065462573646)

Thank you for the feedback.

Please provide binary log from master with events which caused errors.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".