MySQL Bugs: #35583: mysqlbinlog replay fails with ERROR 1146 when temp tables are used

Bug #35583	mysqlbinlog replay fails with ERROR 1146 when temp tables are used
Submitted:	26 Mar 2008 19:01	Modified:	4 Feb 2009 11:40
Reporter:	Gerald Cavanaugh	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.0.51a	OS:	Any (CentOS 5, FreeBSD)
Assigned to:	Zhenxing He	CPU Architecture:	Any
Tags:	mysqlbinlog, replay, table, temp, temporary

Description:
Using binary distribution: mysql-5.0.51a-linux-x86_64-glibc23

If you use temporary tables, and do a binlog-replay using mysqlbinlog results in:

ERROR 1146 (42S02) at line 22: Table 'food.tempmeat' doesn't exist

This totally breaks incremental backups using bin-logs if you happen to use temporary tables. Please note that I am talking about a single thread, where all statements referring to the temp table are logged from creation to destruction. It seems to be a problem with "@@session.pseudo_thread_id" missing before "create temp table" statement.

How to repeat:
Steps to reproduce:

1. Make a table in the 'food' DB:
use food;
CREATE TABLE `meat` (
`meatid` int(10) unsigned NOT NULL auto_increment,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`meatid`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

-----------------------
2. "flush logs" and note the bin-log file name.
3. Make a temp table:
"create temporary table tempmeat like meat;"
4. Do something with temp table:
"insert into tempmeat values (null,'beef');"
5. Use temp table to update real table:
"insert into meat select null, name from tempmeat;"
6. "flush logs"
-----------------------
7. Move meat aside, and recreate it (just for replay):
"rename table meat to meat_old;"
"create table meat like meat_old;"
8. From command-line:
"mysqlbinlog mysql-bin.000002 | mysql food"

This is where you get:
ERROR 1146 (42S02) at line 22: Table 'food.tempmeat' doesn't exist

What I would have expected would be a replay of the binlog we created above, and that the 'meat' and 'meat_old' tables would match (both having a single row with name 'beef').

Suggested fix:
I believe the cause is the omission of "SET @@session.pseudo_thread_id" before "create temporary table" statement in the binlog. If I add the "SET @@session.pseudo_thread_id=blah" statement by hand, the error goes away, and the binlog replay succeeds in restoring my data to its proper state. It seems like "create temporary table" statements should always set "@@session.pseudo_thread_id" no matter what.

Verified as described:

$ less etc/my.cnf

[mysqld]
port = 6850
socket = /tmp/mysql50bk.sock
log-bin=mysql-bin
[client]
port = 6850
socket = /tmp/mysql50bk.sock

1st Shell:
[mysql50bk]$ ./libexec/mysqld --defaults-file=/home/myhome/mysql50bk/etc/my.cnf &

2nd Shell:
[mysql50bk]$ ./bin/mysql -u superuser

mysql> create database bug35583;
Query OK, 1 row affected (0.00 sec)

mysql> \u bug35583
Database changed

mysql> create table t(id serial, t varchar(255)) auto_increment=5;
Query OK, 0 rows affected (0.01 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

mysql> create temporary table tempt like t;Query OK, 0 rows affected (0.01 sec)

mysql> insert into tempt(t) values('abc');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t select id,t from tempt;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

mysql> rename table t to t_old;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t like t_old;
Query OK, 0 rows affected (0.00 sec)

back to 1st Shell:
[mysql50bk]$ ./bin/mysqlbinlog var/mysql-bin.000002 | ./bin/mysql bug35583
ERROR 1146 (42S02) at line 23: Table 'bug35583.tempt' doesn't exist

I found this bug after being seriously bitten by it today.  

Since the bug report is 9 months old I was just wondering if there is any progress wrt fixing it.

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/61640

2636 He Zhenxing	2008-12-15
      BUG#35583 mysqlbinlog replay fails with ERROR 1146 when temp tables are used
      
      When using CREATE TEMPORARY TABLE LIKE to create a temporary table, 
      or using TRUNCATE to delete all rows of a temporary table, they 
      did not set the tmp_table_used flag, and cause the omission of
      "SET @@session.pseudo_thread_id" when dumping binlog with mysqlbinlog,
      and cause error when replay the statements.
      
      This patch fixed the problem by setting tmp_table_used in these two
      cases.

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/62765

2636 He Zhenxing	2009-01-09
      BUG#35583 mysqlbinlog replay fails with ERROR 1146 when temp tables are used
      
      When using CREATE TEMPORARY TABLE LIKE to create a temporary table, 
      or using TRUNCATE to delete all rows of a temporary table, they 
      did not set the tmp_table_used flag, and cause the omission of
      "SET @@session.pseudo_thread_id" when dumping binlog with mysqlbinlog,
      and cause error when replay the statements.
      
      This patch fixed the problem by setting tmp_table_used in these two
      cases.

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/62770

2636 He Zhenxing	2009-01-09
      BUG#35583 mysqlbinlog replay fails with ERROR 1146 when temp tables are used
      
      When using CREATE TEMPORARY TABLE LIKE to create a temporary table, 
      or using TRUNCATE to delete all rows of a temporary table, they 
      did not set the tmp_table_used flag, and cause the omission of
      "SET @@session.pseudo_thread_id" when dumping binlog with mysqlbinlog,
      and cause error when replay the statements.
      
      This patch fixed the problem by setting tmp_table_used in these two
      cases.

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/62952

2636 He Zhenxing	2009-01-12
      BUG#35583 mysqlbinlog replay fails with ERROR 1146 when temp tables are used
      
      When using CREATE TEMPORARY TABLE LIKE to create a temporary table, 
      or using TRUNCATE to delete all rows of a temporary table, they 
      did not set the tmp_table_used flag, and cause the omission of
      "SET @@session.pseudo_thread_id" when dumping binlog with mysqlbinlog,
      and cause error when replay the statements.
      
      This patch fixed the problem by setting tmp_table_used in these two
      cases.

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/63313

2732 He Zhenxing	2009-01-15 [merge]
      Merge BUG#35583 from 5.0 to 5.1-bugteam

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/63672

2970 He Zhenxing	2009-01-21 [merge]
      Merge BUG#35583 from 5.1-bugteam to 6.0-bugteam

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/63702

2749 He Zhenxing	2009-01-21
      Post fix after merge BUG#35583

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/63703

2971 He Zhenxing	2009-01-21 [merge]
      Merge post fix for BUG#35583 from 5.1-bugteam to 6.0-bugteam

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/64302

2749 Alfranio Correia	2009-01-28
      BUG#35583 mysqlbinlog replay fails with ERROR 1146 when temp tables are used
            
      When using CREATE TEMPORARY TABLE LIKE to create a temporary table, 
      or using TRUNCATE to delete all rows of a temporary table, they 
      did not set the tmp_table_used flag, and cause the omission of
      "SET @@session.pseudo_thread_id" when dumping binlog with mysqlbinlog,
      and cause error when replay the statements.
            
      This patch fixed the problem by setting tmp_table_used in these two
      cases. (Done by He Zhenxing 2009-01-12)

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/64361

2983 Alfranio Correia	2009-01-28 [merge]
      Merge BUG#35583 from 5.1-bugteam to 6.0-bugteam (Done by He Zhenxing 2009-01-12)

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/64740

2767 Luis Soares	2009-01-31
      BUG#35583: mysqlbinlog replay fails with ERROR 1146 when temp tables are used
      
      The test case proposed by the bugfix fails in bugteam trees after merging new 
      mtr from main. The failure is due to the fact that the binlog file location has
      changed and is no more under $MYSQLTEST_VARDIR/log.
      
      This patch fixes the test failure by setting the correct path to the binlog 
      file.

Pushed into 5.1.32 (revid:joro@sun.com-20090203090549-gos3v4320vimrzg6) (version source revid:azundris@mysql.com-20090202131035-zuf2kdg8u8r177fi) (merge vers: 5.1.32) (pib:6)

Documented bugfix in the 5.1.32 changelog as follows:

        mysqlbinlog replay of CREATE TEMPORARY TABLE LIKE statements and
        of TRUNCATE statements used on temporary tables failed with
        Error 1146 (Table ... doesn't exist).

Set status to NDI pending push to 6.0 tree.

Pushed into 6.0.10-alpha (revid:kostja@sun.com-20090204104420-mw1i2u9lum4bxjo6) (version source revid:luis.soares@sun.com-20090131214944-6iw4khdxmohwdyze) (merge vers: 6.0.10-alpha) (pib:6)

Fix also documented in the 6.0.10 changelog. Closed.

Is there any plan to fix this for 5.0?  I'm sure that most people affected by this bug are still on 5.0.

This is not planned for 5.0, since 5.1 is GA and there are no open
support issues on this bug.

Pushed into 5.1.32-ndb-6.3.23 (revid:tomas.ulin@sun.com-20090217131017-6u8qz1edkjfiobef) (version source revid:tomas.ulin@sun.com-20090203133556-9rclp06ol19bmzs4) (merge vers: 5.1.32-ndb-6.3.22) (pib:6)

Pushed into 5.1.32-ndb-6.4.3 (revid:tomas.ulin@sun.com-20090217134419-5ha6xg4dpedrbmau) (version source revid:tomas.ulin@sun.com-20090203133556-9rclp06ol19bmzs4) (merge vers: 5.1.32-ndb-6.3.22) (pib:6)

Pushed into 5.1.32-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090217134216-5699eq74ws4oxa0j) (version source revid:tomas.ulin@sun.com-20090202111723-1zzwax187rtls913) (merge vers: 5.1.32-ndb-6.2.17) (pib:6)