MySQL Bugs: #34903: Backup: restore failure if view and temporary

Bug #34903	Backup: restore failure if view and temporary
Submitted:	27 Feb 2008 19:42	Modified:	3 Nov 2008 10:51
Reporter:	Peter Gulutzan	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Backup	Severity:	S3 (Non-critical)
Version:	6.0.5-alpha-debug	OS:	Linux (SUSE 10 \| 32-bit)
Assigned to:	Rafal Somla	CPU Architecture:	Any

Description:
I'm using mysql-6.0-backup.

I create a base table t.
I create a view of base table t.
I create a temporary basetable t.
I say BACKUP DATABASE.
I say RESTORE.
The restore fails because "database exists"
(that's wrong, the database should be overwritten).
I drop the database.
I say RESTORE again.
The restore fails because of a temporary table
(that's wrong, the temporary table is gone).

See also
Bug#33574 Backup: restore failure if temporary table exists

How to repeat:
mysql> create database d3;
Query OK, 1 row affected (0.00 sec)

mysql> use d3
Database changed
mysql> create table t (s1 int);
Query OK, 0 rows affected (0.41 sec)

mysql> create view v as select * from t;
Query OK, 0 rows affected (0.00 sec)

mysql> create temporary table t (s1 int);
Query OK, 0 rows affected (0.00 sec)

mysql> backup database d3 to '6';
+-----------+
| backup_id |
+-----------+
| 8         |
+-----------+
1 row in set (0.18 sec)

mysql> restore from '6';
ERROR 1007 (HY000): Can't create database 'd3'; database exists
mysql> drop database d3;
Query OK, 1 row affected (0.00 sec)

mysql> restore from '6';
ERROR 1352 (HY000): View's SELECT refers to a temporary table 't'

Thank you for the bug report. Verified as described.

create database d3;
use d3
create table t (s1 int);
create view v as select * from t;
create temporary table t (s1 int);
backup database d3 to '6';
restore from '6';
drop database d3;
restore from '6';

The second problem:

(...)
I drop the database.
I say RESTORE again.
The restore fails because of a temporary table
(that's wrong, the temporary table is gone).

is likely caused by bug#30099 - "rename/drop database ignores temp tables". The temporary table is not deleted by "drop database"

Seems like both problems reported here are due to bug#30099

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.

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.

This is a duplicate of BUG#33574 which was fixed now (patch queued).

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