MySQL Bugs: #33569: Backup: tablespace not restored

Bug #33569	Backup: tablespace not restored
Submitted:	29 Dec 2007 0:44	Modified:	3 Apr 2009 3:14
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:	Jørgen Løland	CPU Architecture:	Any

Description:
I say BACKUP DATABASE with a database that contains a
Falcon table that is in a non-default tablespace.
I drop the tablespace.
I say RESTORE.
But the tablespace is not restored, the statement fails.

This is a consequence of the fact that Falcon tablespaces
are not inside databases. I believe that they should be.

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:
create tablespace tb add datafile '/usr/local/mysql/var/t36' engine=falcon;
drop database wa;
create database wa;
use wa
create table t (s1 int) engine=falcon tablespace tb;
insert into t values (1);
backup database wa to '/usr/local/mysql/var/t40';
drop database wa;
drop tablespace tb engine=falcon;
restore from '/usr/local/mysql/var/t40';

Thank you for the bug report. Verified as described.

Proposed Solution
-----------------
If a backup is run that includes a table with a tablespace, throw and error and abort. This will prevent users from creating useless backup image files.

Design
------
An additional method is needed to detect/retrieve the name of the tablespace in the table's CREATE command. This method shall be added to si_objects and named:

char *get_tablespace_for_table(TABLE *t);

It returns the name of the tablespace if defined. It returns NULL if no tablespace is defined or if the table is not a Falcon table. 

Concept of Operations
---------------------
Backup will check to see if there are any falcon tables. For each falcon table, a call is made to the method. If a name is returned (not NULL), an error is thrown similar to:

ERROR 0000: Tablespaces are not currently supported in online backup. Table 't1' uses tablespace 'sillyrabbit'.

Notes
-----
This could be extended once WL#4240 is implemented and that the method is needed for WL#4240. Additionally, once work for WL#4240 is complete, the error portion of this patch can be removed (as part of that work).

Ok, so we really need an index of available functions....

We already have a method to get the tablespace name...

get_tablespace_name().

So now it's easier...  :)

Patch ready for review.

This bug was fixed with the work for WL#4240 : 'Online Backup: save/restore table space information' and it's related worklogs.

It's still easy to get a restore failure, or an
unacceptable situation attributable to the restore.

All I have to do is create the tablespace with some
trivial difference, for example the case of the name
is different, or the extent size is different.

Here are four examples. I cleared the database files
before each run. I used mysql-6.0-backup pulled today.

Example #1:
create tablespace tb add datafile '/usr/local/mysql/var/t36' engine=falcon;
drop database wa;
create database wa;
use wa
create table t (s1 int) engine=falcon tablespace tb;
insert into t values (1);
backup database wa to '/usr/local/mysql/var/t40';
drop database wa;
drop tablespace tb engine=falcon;
create tablespace TB add datafile '/usr/local/mysql/var/t36' engine=falcon;
restore from '/usr/local/mysql/var/t40';

Example #2:
create tablespace tb add datafile '/usr/local/mysql/var/t36' engine=falcon;
drop database wa;
create database wa;
use wa
create table t (s1 int) engine=falcon tablespace tb;
insert into t values (1);
backup database wa to '/usr/local/mysql/var/t40';
drop database wa;
drop tablespace tb engine=falcon;
create tablespace tb add datafile '/usr/local/mysql/var/T36' engine=falcon;
restore from '/usr/local/mysql/var/t40';

Example #3:
create tablespace tb add datafile '/usr/local/mysql/var/t36' engine=falcon;
drop database wa;
create database wa;
use wa
create table t (s1 int) engine=falcon tablespace tb;
insert into t values (1);
backup database wa to '/usr/local/mysql/var/t40';
drop database wa;
drop tablespace tb engine=falcon;
create tablespace tb add datafile '/usr/local/mysql/var/T36' extent_size = 1024 engine=falcon;
restore from '/usr/local/mysql/var/t40';

Example #4:
create tablespace tb add datafile '/usr/local/mysql/var/t36' engine=falcon;
drop database wa;
create database wa;
use wa
Database changed
mysql> create table t (s1 int) engine=falcon tablespace tb;
insert into t values (1);
backup database wa to '/usr/local/mysql/var/t40';
drop database wa;
drop tablespace tb engine=falcon;
restore from '/usr/local/mysql/var/t40';
show create table wa;
show create table t;
backup database wa to '/usr/local/mysql/var/t41';
drop database wa;
drop tablespace tb engine=falcon;
create tablespace tb add datafile '/usr/local/mysql/var/t37' engine=falcon;
retore from '/usr/local/mysql/var/t41';
restore from '/usr/local/mysql/var/t41';
drop tablespace tb engine=falcon;
create tablespace tb add datafile '/usr/local/mysql/var/test/t36' engine=falcon;
restore from '/usr/local/mysql/var/t41';
drop tablespace tb engine=falcon;
create tablespace tb add datafile '/usr/local/mysql/var/test/T36' engine=falcon;
restore from '/usr/local/mysql/var/t41';
drop tablespace tb engine=falcon;
create tablespace tb add datafile '/usr/local/mysql/var/T36' engine=falcon;
restore from '/usr/local/mysql/var/t41';
drop tablespace tb engine=falcon;
drop database wa;
drop tablespace tb engine=falcon;
create tablespace TB add datafile '/usr/local/mysql/var/T36' engine=falcon;
restore from '/usr/local/mysql/var/t41';
drop tablespace tb engine=falcon;
drop tablespace TB engine=falcon;
create tablespace tb add datafile '/usr/local/mysql/var/T36' engine=falcon;
restore from '/usr/local/mysql/var/t41';
drop database wa;
drop tablespace tb engine=falcon;
create tablespace tb add datafile '/usr/local/mysql/var/t36' extent_size = 1024 engine=falcon;
restore from '/usr/local/mysql/var/t41';

This bug requires BUG#35257 to be fixed first.

Created a related bug: 
Bug#42402 - Restore crashes server if tablespace does not exist

Apart from the crashing bug reported above (which is not a tablespace case-sensitivity issue), this bug seems to be fixed by bug#35257.

A proposed fix for this bug is to extend the backup_tablespace.test to include case-sensitive tablespace tests.

Everything did not pass - restore after creating a new tablespace using a completely different file still does not work:

create tablespace ts add datafile 'afile' engine=falcon;
create database...;
create table...;
backup database...;
drop tablespace ts...;
create tablespace ts add datafile 'differentfile' engine=falcon;
restore from...;
-> Fails

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

2777 Jorgen Loland	2009-02-23
      Bug#33569 - Backup: tablespace not restored
      
      Previously, RESTORE had a lot of restrictions for when an existing tablespace the restore depended upon could be used. Basically, the "create tablespace" statements had to be equal case-sensitive Strings, or RESTORE would error.
      
      There is no need for this kind of restriction. If RESTORE requires a tablespace 'x' to restore a table, it should use the existing one if a tablespace with name 'x' exists. Otherwise, the tablespace should be created using the backed up definition.

Patch approved with minor request.

Good 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/67832

2780 Jorgen Loland	2009-02-27
      Bug#33569 - Backup: tablespace not restored
            
      Previously, RESTORE had a lot of restrictions for when an existing tablespace the restore depended upon could be used. Basically, the "create tablespace" statements had to be equal case-sensitive Strings, or RESTORE would error.
            
      There is no need for this kind of restriction. If RESTORE requires a tablespace 'x' to restore a table, it should use the existing one if a tablespace with name 'x' exists. Otherwise, the tablespace should be created using the backed up definition.

Pushed into 6.0.11-alpha (revid:alik@sun.com-20090326121822-pt84kzxxayzho4mn) (version source revid:rafal.somla@sun.com-20090302164601-znhm4tadplfi2iqu) (merge vers: 6.0.11-alpha) (pib:6)

Noted in 6.0.11 changelog.

RESTORE often would not correctly identify the tablespace into which
a Falcon table should be restored.