MySQL Bugs: #33574: Backup: restore failure if temporary table exists

Bug #33574	Backup: restore failure if temporary table exists
Submitted:	29 Dec 2007 1:09	Modified:	15 Nov 2008 1:17
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Backup	Severity:	S3 (Non-critical)
Version:	6.0.5-alpha-debug	OS:	Linux (SUSE 10 64-bit)
Assigned to:	Rafal Somla	CPU Architecture:	Any

Description:
I create a table.
I create a temporary table with the same name.
I say BACKUP DATABASE.
I say RESTORE.
The database table is not restored.

Built from mysql-6.0-backup source with BUILD/compile-pentium-debug-max.
Last changeset = "ChangeSet@1.2753, 2007-12-20 15:32:22-05:00".

How to repeat:
/* Ignore error messages and warnings seen during execution of this script. */
use test
drop database wj;
create database wj;
use wj
create table t (s1 int) engine=innodb;
insert into t values (1);
create temporary table t (s1 int);
insert into t values (2);
select * from t;
backup database wj to '/usr/local/mysql/var/t74';
drop database wj;
show tables;
use wj
show tables;
restore from '/usr/local/mysql/var/t74';
select * from t;
create temporary table t (tmp_s1 int);
quit

Now shut down mysqld ...
/usr/local/mysql/bin/mysqladmin --user=root shutdown

Restart mysqld.
Look for table t.
It is not there.

Thank you for the bug report. Verified as described.

There is early reported bug #32212 also.

Set bug #32212 as duplicate of this bug here.

REFINED PROBLEM DESCRIPTION.

There are two separate problems. First problem is that it is not possible to DROP a database if a temporary table was created within it. This will make RESTORE to fail, because one of the first things a RESTORE operation does is to execute DROP DATABASE statements for the databases being restored. This problem is addressed by BUG#30099.

Another problem, related to backup and si_services, is that a temporary table will occlude a normal table with the same name. Consider this situation:

USE db;
CREATE TABLE t (s1 int);
CREATE TEMPORARY TABLE t (s1 int);
BACKUP DATABASE db TO 'db.bkp';

During BACKUP operation the code will call si_services functions to get a definition (serialization string) of table db.t. To get a create statement for the table, si_services code will open the table and use store_create_info() on the opened table. But open_normal_and_derived_tables(thd, table_list, 0) used to open the table will open the temporary table 'db.t', not the regular table 'db.t'. As the result, backup code will get create statement for the temporary table. Then, during RESTORE, even if everything works ok, the restored table 't' will be a temporary table which is wrong. For example it will not be seen from other connections.

A test case illustrating this:

CREATE DATABASE db;
USE db;
CREATE TABLE t (s1 int);
INSERT INTO t VALUES (1);
CREATE TEMPORARY TABLE t (s1 int);
INSERT INTO t VALUES (2);
BACKUP DATABASE db TO 'db.bkp';

DROP TABLE t;
RESTORE FROM 'db.bkp';
USE db;
SHOW CREATE TABLE t;  # <== see that t is a temporary table
SELECT * FROM t;      # <== see that it contains (2), not (1).

connect (addconroot, localhost, root,,);
connection addconroot;
USE db;
SELECT * FROM t;     # <== fails because there is no table db.t

The fact that after restore table contains data from the temporary table, indicates that the built-in drivers are also confused. When they open table 'db.t' to read rows from it, they get the temporary table opened, instead of the regular table.

Another aspect of this problem. The code selecting a backup driver to handle given table, gets access to the handlerton of that table (function get_storage_engine() in backup_info.h) by opening the table with open_temporary_table() function. In the above situation, the function correctly opens the regular table, *not* the temporary one. This can be seen using the following test script:

CREATE DATABASE db;
USE db;
CREATE TABLE t (s1 int) ENGINE=innodb;
INSERT INTO t VALUES (1);
CREATE TEMPORARY TABLE t (s1 int) ENGINE=myisam;
INSERT INTO t VALUES (2);
BACKUP DATABASE db TO 'db.bkp';
SELECT * FROM mysql.backup_history; # See that 'drivers' column reports 
                                    # "Snapshot", not "MyISAM".

Note: The same problem affects table locking done by the built-in drivers. If a temporary table exists with the same name as a regural one, the temporary table is locked, not the regular one. Other connections can access and modify the non-tmp table.

PROPOSED SOLUTION
=================

There is MYSQL_OPEN_SKIP_TEMPORARY option which can be passed to open_and_lock_tables_derived(...) and cause it to ignore any temporary tables.

Thus a solution is to use this option in all places where tables are opened for backup/restore purposes:
- in the built-in drivers,
- in the si_objects functions.

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

2718 Rafal Somla	2008-10-29
      BUG#33574, BUG#34903 (Backup: restore failure if temporary table exists)
      
      Before: If a temporary table existed with the same name as a regular one, BACKUP
      saved the temporary table.
      
      After: Temporary tables are ignored by BACKUP command.

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

2718 Rafal Somla	2008-10-29
      BUG#33574, BUG#34903 (Backup: restore failure if temporary table exists)
      
      Before: If a temporary table existed with the same name as a regular one, 
      BACKUP saved the temporary table.
      
      After: Temporary tables are ignored by BACKUP command.

Good to push

Ok to push.

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

2722 Rafal Somla	2008-10-30
      BUG#33574, BUG#34903 (Backup: restore failure if temporary table exists)
      
      Before: If a temporary table existed with the same name as a regular one, 
      BACKUP saved the temporary table.
      
      After: Temporary tables are ignored by BACKUP command.

Pushed into 6.0.9-alpha  (revid:rafal.somla@sun.com-20081030122954-tipkk855c94ehves) (version source revid:jorgen.loland@sun.com-20081114134411-xypyf8wyjc2nm3ly) (pib:5)

Noted in 6.0.9 changelog.

If a TEMPORARY table existed with the same name as a regular table,
BACKUP DATABASE saved the temporary table, causing a subsequent
RESTORE to fail.