MySQL Bugs: #43363: Backup: Backup on case sensitive server restore on case insensitive server fails

Bug #43363	Backup: Backup on case sensitive server restore on case insensitive server fails
Submitted:	4 Mar 2009 10:44	Modified:	25 Apr 2009 23:58
Reporter:	Jørgen Løland	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Backup	Severity:	S3 (Non-critical)
Version:	6.0	OS:	Any
Assigned to:	Jørgen Løland	CPU Architecture:	Any

Description:
When BACKUP is executed on a server with case sensitive database names (i.e., Linux), and a database name contains upper case characters, RESTORE fails if executed on a case insensitive server (e.g., Windows or lower_case_table_names=1)

Example:

On Linux:
---------
create database X;
create table X.t1 (i int);
insert into X.t1 values(1);

backup database X to 'my.bup';

On Windows:
-----------
restore from 'my.bup';
ERROR 1146 (42S02): Table 'X.t1' doesn't exist
show warnings;
+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Error   | 1146 | Table 'X.t1' doesn't exist                  | 
| Error   | 1672 | Open and lock tables failed in RESTORE      | 
| Warning | 1764 | Operation aborted - data might be corrupted | 
+---------+------+---------------------------------------------+

How to repeat:
You'll need a server running on a machine that can handle case sensitive (CS) file names (e.g., Linux). 

## On case sensitive server ##
mysql> create database X;
mysql> create table X.t1 (i int);
mysql> insert into X.t1 values(1);

mysql> backup database X to 'uppercase.bup';
mysql> exit;

## On case insensitive server (e.g., start server with lower_case_table_names=1)
mysql> restore from 'uppercase.bup';
ERROR 1146 (42S02): Table 'X.t1' doesn't exist
mysql> show warnings;
+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Error   | 1146 | Table 'X.t1' doesn't exist                  | 
| Error   | 1672 | Open and lock tables failed in RESTORE      | 
| Warning | 1764 | Operation aborted - data might be corrupted | 
+---------+------+---------------------------------------------+

Suggested fix:
A similar problem has been observed for BACKUP. See bug#39063. For BACKUP, the problem is fixed by converting upper case database names to lower case if the server is case insensitive. A similar approach may solve this problem as well.

A related problem: Two databases with same name but different case are backed up and attempted restored on a case insensitive server:

### On CS server
mysql> create database X;
mysql> create database x;
mysql> create table X.t1 (i int);
mysql> create table x.t2 (a char);
mysql> insert into X.t1 values(1);
mysql> insert into x.t2 values('a');

mysql> backup database X, x to 'casesensitive.bup';
+-----------+
| backup_id |
+-----------+
| 272       | 
+-----------+
1 row in set (1.06 sec)

mysql> drop database X;
Query OK, 1 row affected (0.01 sec)

mysql> drop database x;
Query OK, 1 row affected (0.02 sec)

mysql> restore from 'casesensitive.bup';
+-----------+
| backup_id |
+-----------+
| 273       | 
+-----------+
1 row in set (0.03 sec)

mysql> select * from X.t1;
+------+
| i    |
+------+
|    1 | 
+------+
1 row in set (0.00 sec)

mysql> select * from x.t2;
+------+
| a    |
+------+
| a    | 
+------+
1 row in set (0.00 sec)

###  restart server, now case insensitive (lower_case_table_names=1)

mysql> create database X;
mysql> create database x;
ERROR 1007 (HY000): Can't create database 'x'; database exists

### As expected - just checking case insensitivity

mysql> drop database X;
Query OK, 0 rows affected (0.01 sec)

mysql> restore from 'casesensitive.bup';
ERROR 1146 (42S02): Table 'X.t1' doesn't exist
mysql> show warnings;
+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Error   | 1146 | Table 'X.t1' doesn't exist                  | 
| Error   | 1672 | Open and lock tables failed in RESTORE      | 
| Warning | 1764 | Operation aborted - data might be corrupted | 
+---------+------+---------------------------------------------+
3 rows in set (0.00 sec)

Bug#43220 may be related to this

This bug report will be for the first case above (not the "duplicate name" one). 

Bug#43596 has been created for the duplicate dbname case where databases DB1 and db1 are both in a backup image that is RESTORED on a case insensitive server.

Database and table creation is not the problem. Even in a lower_case_table_names=1 server, "create database `X`" works (creates a database with lower case name `x`). 

The problem is that RESTORE tries to lock table `X.t1`, and while CREATE TABLE is not case sensitive in this server environment, lock table is.

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

2803 Jorgen Loland	2009-03-20
      Bug#43363 - Backup: Backup on case sensitive server restore on case insensitive server fails
      
      Before, RESTORE on a case insensitive server would fail if the backup image contained databases or tables with upper case names. 
      
      Now, RESTORE is able to handle this case by converting the names to lowercase in the restore catalog as long as there are no duplicate names after the conversion. The duplicate name problem is logged as bug 43596.

Approved pending acceptance and fulfillment of requests made in patch review email.

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

2809 Jorgen Loland	2009-04-01
      Bug#43363 - Backup: Backup on case sensitive server restore on case insensitive server fails
            
      Before, RESTORE on a case insensitive server would fail if the backup image contained databases or tables with upper case names. 
            
      Now, RESTORE is able to handle this case by converting the names to lowercase in the restore catalog as long as there are no duplicate names after the conversion. The duplicate name problem is logged as bug 43596.
     @ mysql-test/suite/backup/r/backup_namecase.result
        Test that RESTORE on a case insensitive server works even if the database names are added to the catalog in upper case.
     @ mysql-test/suite/backup/t/backup_namecase-master.opt
        Make backup_namecase run on case insensitive server.
     @ mysql-test/suite/backup/t/backup_namecase.test
        Test that RESTORE on a case insensitive server works even if the database names are added to the catalog in upper case.
     @ sql/backup/kernel.cc
        Store database names and table names in lower case in the catalog if RESTORE is performed on a case insensitive server.

Good to push.

Pushed into 6.0.11-alpha (revid:alik@sun.com-20090423070920-e5lq3vrrqi016z2c) (version source revid:alik@sun.com-20090423070920-e5lq3vrrqi016z2c) (merge vers: 6.0.11-alpha) (pib:6)

Noted in 6.0.11 changelog.

RESTORE on a case-insensitive server failed if the backup image
contained databases or tables with uppercase names. Now, RESTORE
handles this case by converting the names to lowercase in the restore
catalog, as long as there are no duplicate names after the
conversion.