Bug #48350 truncate temporary table crashes replication
Submitted: 27 Oct 2009 14:13 Modified: 15 Mar 2010 4:54
Reporter: Kees Hoekzema Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Row Based Replication ( RBR ) Severity:S2 (Serious)
Version:5.1.37, 5.1.40-enterprise-gpl-advanced-log, 5.1.41 OS:Linux
Assigned to: Libing Song CPU Architecture:Any
Tags: replication, temporary table, truncate

[27 Oct 2009 14:13] Kees Hoekzema 
Description:
We are using temporary tables, and sometimes we want to clean them halfway the script. To do that we use truncate table.

After switching to row based replication this breaks the replication.

How to repeat:
Easy way to halt replication with binlog_format=ROW

On the master:
Server version: 5.1.40-enterprise-gpl-advanced-log MySQL Enterprise Server - Advanced Edition (GPL)
SET BINLOG_FORMAT=ROW;
use test;
CREATE TEMPORARY TABLE IF NOT EXISTS foo_bar_table (ID INT PRIMARY KEY);
TRUNCATE foo_bar_table;

In the binlog:
TRUNCATE foo_bar_table

On the slave:
show slave status\G
..
Last_SQL_Error: Error 'Table 'test.foo_bar_table' doesn't exist' on query. Default database: 'test'. Query: 'TRUNCATE foo_bar_table'
1 row in set (0.00 sec)

Suggested fix:
Maybe have the binlog check if a temporary table is being truncated, and if it is, do not replicate that statement.
[27 Oct 2009 17:31] Valeriy Kravchuk 
I think this is related to http://bugs.mysql.com/bug.php?id=30309.
[29 Oct 2009 5:54] Valeriy Kravchuk 
Primitive test case for this bug

Attachment: rpl_bug48350.test (application/octet-stream, text), 154 bytes.
[29 Oct 2009 6:13] Valeriy Kravchuk 
Verified just as described with recent 5.1.41 from bzr. Indeed, we get errors on slave like:

Last_SQL_Error	Error 'Table 'test.t1' doesn't exist' on query. Default database: 'test'. Query: 'TRUNCATE t1'

only when binlog_format is set to ROW and t1 is a temporary table.
[29 Oct 2009 16:39] Valeriy Kravchuk 
Same problem is repeatable with 5.1.37 on Mac OS X (official binaries), so this is not a recent regression.
[12 Nov 2009 8:24] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/90183

3197 Li-Bing.Song@sun.com	2009-11-12
      Bug #48350  	truncate temporary table crashes replication
      
      All statements operating on temporary tables should not be binlogged if masters
      are in row-based mode. However, 'TRUNCATE ...' statement for a temporary table 
      is still binlogged if a master is in row-based mode. When the log event is executed
      on slave, it can not found the table being truncated. So it reports an error and 
      aborts.
      
      After this patch, 'TRUNCATE ...' statement for a temporary table will not be
      binlogged, if masters are in row-based mode.
[17 Nov 2009 15:11] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/90679

3202 Li-Bing.Song@sun.com	2009-11-17
      Bug #48350  	truncate temporary table crashes replication
      
      In RBR, All statements operating on temporary tables should not be binlogged.
      Despite this fact, after executing 'TRUNCATE... ' on a temporary table, 
      the command is still logged, even if in row-based mode. Consequently, this raises
      problems in the slave as the table may not exist, resulting in an
      execution failure. Ultimately, this causes the slave to report
      an error and abort.
      
      After this patch, 'TRUNCATE ...' statement on a temporary table will not be
      binlogged in RBR.
[22 Nov 2009 5:10] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/91233

3214 Li-Bing.Song@sun.com	2009-11-22
      Bug #48350  	truncate temporary table crashes replication
      
      In RBR, All statements operating on temporary tables should not be binlogged.
      Despite this fact, after executing 'TRUNCATE... ' on a temporary table, 
      the command is still logged, even if in row-based mode. Consequently, this raises
      problems in the slave as the table may not exist, resulting in an
      execution failure. Ultimately, this causes the slave to report
      an error and abort.
      
      After this patch, 'TRUNCATE ...' statement on a temporary table will not be
      binlogged in RBR.
[23 Nov 2009 8:05] Libing Song 
Pushed into mysql-5.1-bugteam and merged into mysql-pe
[2 Dec 2009 8:07] Bugs System 
Pushed into 5.1.42 (revid:joro@sun.com-20091202080033-mndu4sxwx19lz2zs) (version source revid:davi.arnaut@sun.com-20091125130912-d7hrln14ef7y5d7i) (merge vers: 5.1.42) (pib:13)
[8 Dec 2009 15:12] Jon Stephens 
Documented in the 5.1.42 changelog as follows:

        When using row-based logging, TRUNCATE TABLE was written to the
        binary log even if the affected table was temporary, causing
        replication to fail.

Closed.
[16 Dec 2009 8:34] Bugs System 
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091216083311-xorsasf5kopjxshf) (version source revid:alik@sun.com-20091214191830-wznm8245ku8xo702) (merge vers: 6.0.14-alpha) (pib:14)
[16 Dec 2009 8:42] Bugs System 
Pushed into 5.5.0-beta (revid:alik@sun.com-20091216082430-s0gtzibcgkv4pqul) (version source revid:alexey.kopytov@sun.com-20091124083136-iqm136jm31sfdwg3) (merge vers: 5.5.0-beta) (pib:14)
[16 Dec 2009 8:48] Bugs System 
Pushed into mysql-next-mr (revid:alik@sun.com-20091216083231-rp8ecpnvkkbhtb27) (version source revid:alik@sun.com-20091212203859-fx4rx5uab47wwuzd) (merge vers: 5.6.0-beta) (pib:14)
[16 Dec 2009 15:49] Jon Stephens 
Also documented in the 5.5.1, 5.6.0, and 6.0.14 changelogs. Closed.
[8 Mar 2010 0:05] Paul DuBois 
5.6.0 changelog entry unneeded.
[12 Mar 2010 14:06] Bugs System 
Pushed into 5.1.44-ndb-7.0.14 (revid:jonas@mysql.com-20100312135944-t0z8s1da2orvl66x) (version source revid:jonas@mysql.com-20100312115609-woou0te4a6s4ae9y) (merge vers: 5.1.44-ndb-7.0.14) (pib:16)
[12 Mar 2010 14:22] Bugs System 
Pushed into 5.1.44-ndb-6.2.19 (revid:jonas@mysql.com-20100312134846-tuqhd9w3tv4xgl3d) (version source revid:jonas@mysql.com-20100312060623-mx6407w2vx76h3by) (merge vers: 5.1.44-ndb-6.2.19) (pib:16)
[12 Mar 2010 14:36] Bugs System 
Pushed into 5.1.44-ndb-6.3.33 (revid:jonas@mysql.com-20100312135724-xcw8vw2lu3mijrhn) (version source revid:jonas@mysql.com-20100312103652-snkltsd197l7q2yg) (merge vers: 5.1.44-ndb-6.3.33) (pib:16)
[15 Mar 2010 4:54] Jon Stephens 
No additional changelog entries needed. Setting back to Closed state.