Bug #43596 Restore fails on case ins. server for databases who's names differ in case only
Submitted: 12 Mar 2009 13:47 Modified: 17 Mar 2010 17:43
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: Rafal Somla
Triage: Triaged: D4 (Minor)

[12 Mar 2009 13:47] Jørgen Løland
Description:
If a BACKUP is made of two databases who's names differ in case only (DB1 vs db1), RESTORE fails if executed on a case insensitive server.

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

How to repeat:
### 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)

Suggested fix:
I don't think this can ever be made to work, but RESTORE should fail with a meaningful error message.
[12 Mar 2009 13:48] Jørgen Løland
Related to bug#43363
[12 Mar 2009 20:41] Sveta Smirnova
Thank you for the report.

Which tree do you use?

I ask, because with main 6.0 tree I get error when backup:

mysql> show variables like 'lower_case_table_names';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 0     | 
+------------------------+-------+
1 row in set (0.00 sec)

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

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

mysql> create table X.t1 (i int);
Query OK, 0 rows affected (0.15 sec)

mysql> create table x.t2 (a char);
Query OK, 0 rows affected (0.11 sec)

mysql> insert into X.t1 values(1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into x.t2 values('a');
Query OK, 1 row affected (0.00 sec)

mysql> backup database X, x to 'casesensitive.bup';
ERROR 1753 (42000): Not unique database: 'x'
[13 Mar 2009 11:10] Jørgen Løland
I'm using mysql-6.0-backup, but it will be the same behavior in 6.0 main once bug#39063 has been merged from backup to main.
[13 Mar 2009 11:42] Sveta Smirnova
Thank you for the feedback.

Verified as described.
[24 Jun 2009 12:38] Rafal Somla
When tested on recent backup tree ( revision-id:jorgen.loland@sun.com-20090624080534-r8oeu2ky8v4p4qul) then RESTORE does not fail in the scenario described above. However, it fails to correctly restore the data as it puts tables from both databases 'X' and 'x' into a single restored database 'x', overwriting tables with the same names. E.g. consider this variant:

create database X;
create database x;
create table X.t1 (i int);
create table x.t1 (a char);
create table x.t2 (a char);
insert into X.t1 values(1);
insert into x.t1 values('a');
insert into x.t2 values('b');

backup database X, x to '/tmp/test.bkp';

drop database X;
drop database x;

restore from '/tmp/test.bkp';

If restored on case insensitive system, table x.t1 will overwrite table X.t1. Restored database 'x' will contain tables t1 and t2 with char column.

So, we still have a problem. We need to detect this situation and warn user/abort RESTORE.
[18 Aug 2009 19:38] Chuck Bell
Chuck steals another bug...
[19 Aug 2009 13:04] Chuck Bell
Preliminary Design Considerations
---------------------------------
During restore, the system shall detect when it is running on Windows or a machine that has lctn set to 1. If this is true, the system shall perform a duplicate object name check based on case insensitive check.

This shall require walking the catalog and looking for duplicate names within each object category starting with databases. As soon as a duplicate is detected, the system shall abort with an appropriate error message.

This design requires that the duplication check occur after the catalog is read and before any object is dropped (before the bulk of the restore begins).

There is no issue during backup as lctn issues will be caught by the server and/or OS.

Alternative Consideration
-------------------------
Do the duplication check on backup setting a variable in the catalog to indicate there is more than one object with the same case insensitive name. This may be more efficient but comes as a penalty for all cases where a) there are no duplicates, and b) the system is case sensitive and will never be restored on a case insensitive system.
[26 Aug 2009 20:33] Chuck Bell
Cannot proceed without buy-in from original developer of code to be changed. Changing status to 'Verified' in the interim.
[14 Sep 2009 21:56] Chuck Bell
On a lctn2 machine (e.g. windows) you can/not do the following:

A view cannot have the same name as a table.
A procedure can have the same name as another object.
A function can have the name as another object.
An event can have the same name as another object.
A trigger can have the same name as another object.

For all objects, you cannot create a second object with the same name.

The impact of this finding is the algorithm for searching the catalog must be per-object of a specific type.
[14 Sep 2009 21:56] Chuck Bell
On a lctn2 machine (e.g. windows) you can/not do the following:

A view cannot have the same name as a table.
A procedure can have the same name as another object.
A function can have the name as another object.
An event can have the same name as another object.
A trigger can have the same name as another object.

For all objects, you cannot create a second object with the same name.

The impact of this finding is the algorithm for searching the catalog must be per-object of a specific type.
[16 Sep 2009 21:19] Chuck Bell
Setting back to verified. Developer discovered showstopper bug during development. The solution requires fix for BUG#47386. Preliminary patch attached (but useless until problem resolved).
[16 Sep 2009 21:20] Chuck Bell
Preliminary patch.

Attachment: 43596_1.diff (application/octet-stream, text), 7.78 KiB.

[26 Nov 2009 12:27] Rafal Somla
Even on case sensitive server, with lctn=0, some object names are case sensitive. With the attached genimages.test I could confirm that only for these object types (supported by backup) it is possible to have two objects whose names differ in case only:

- databases,
- tables,
- views,
- triggers.

For stored routines and events it is not possible to have 2 objects whose names differ in case only. Thus when detecting duplicates, it is enough to do case insensitive comparison for stored routines and events.
[26 Nov 2009 12:28] Rafal Somla
Test case to generate backup images.

Attachment: genimages.test (, text), 1.61 KiB.

[26 Nov 2009 12:29] Rafal Somla
Sorry, my previous comment should start with: "Even on case sensitive server, with lctn=0, some object names are case insensitive"
[1 Dec 2009 14:26] Rafal Somla
PROPOSED SOLUTION
-----------------
The main idea of the solution is to move object name handling to si_objects 
module. Each object being backed-up/restored is identified by obs::Obj 
instance. Once created, this instance will produce a correct "canonical" 
name of the object with get_name() method. For example, on lctn=1 system, 
names of some of the objects (databses, tables etc) are converted to lower-case.

New method obs::Obj::has_name(const String *name) will be added which tells 
if given name refers to the object for which method was called.

During restore, before adding next object to the catalogue (Restore_info 
instance) it will be checked if an object with the same name (as determined 
by has_name() method) already exist in it. If yes, RESTORE will be 
interrupted with error. This should fix the issue reported in this bug.

To implement this idea these main changes will be made (+ all other changes 
necessary to make everything work together):

1. In backup kernel, bcat_add_item() function. For each item being restored, 
first a corresponding obs::Obj instance is created which identifies the 
object in further operations. When adding the object to the restore 
catalogue (using Restore_info::add_X() methods), instead of explicitly 
passing object name (which might be in wrong case), the obs::Obj instance is 
passed instead. Thanks to this always a correct name is used (as produced by 
obs::Obj::get_name()). Also, a pointer to obs::Obj instance is stored within 
the catalogue for future use. For example, it is used later to call 
has_name() method.

2. The Restore_info implementation. In add_X() methods checks are made if 
the object already exists. These checks are done using new 
Image_info::has_X() methods.

3. Image_info implementation. Change signatures of add_*() methods so that 
each added object is represented by obs::Obj instance (not by its name). 
Define new has_X() methods which check if object with given name already 
exists in the catalogue. This check will be done using obs::Obj::has_name() 
method. Also change Image_info::Obj implementation using assumption that 
each instance must store a pointer to corresponding obs::Obj instance.

4. In si_objects module. Currently and obs::Obj instance for a given object 
can be created only when serialization data for the object is provided. This  
serialization data is stored in the instance and then later used in 
obs::Obj::create() method. Since now we want to create obs::Obj instances 
early in the restore process, before meta data is available, the design will 
be changed a bit: get_X() functions will create instances without 
serialization data which will be passed to obs::Obj::create() method. Also, 
new obs::Obj::has_name() method will be introduced.
[4 Dec 2009 15:15] Bugs System
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/92899
[8 Dec 2009 15:34] Chuck Bell
Approved pending changes
[9 Dec 2009 11:56] Ingo Strüwing
Approved pending changes.
[10 Dec 2009 11:25] Bugs System
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/93477
[10 Dec 2009 12:41] Bugs System
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/93498
[10 Dec 2009 12:44] Rafal Somla
pushed to mysql-6.0-backup tree.
revid::rafal.somla@sun.com-20091210124049-98jyu3nog0vmhbvg
[11 Dec 2009 10:23] Bugs System
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/93637

2908 Rafal Somla	2009-12-11
      PB fixes - there were errors in some tests pushed with BUG#43596.
     @ mysql-test/suite/backup/t/backup_cs_names.test
        Skip test on systems with lctn!=0.
     @ mysql-test/suite/backup/t/backup_cs_names_lctn0.test
        Skip test on systems with lctn!=0.
     @ mysql-test/suite/backup/t/backup_cs_names_lctn1.test
        - Skip test on systems with lctn!=1.
        - Fix error constant names.
     @ mysql-test/suite/backup/t/backup_cs_names_lctn2.test
        Skip test on systems with lctn!=2.
     @ mysql-test/suite/backup/t/backup_events.test
        Mask warnings whose order can be non-deterministic.
     @ mysql-test/suite/backup/t/backup_thd_delete.test
        Skip test on systems which do not have debug sync facility.
[14 Dec 2009 20:54] Bugs System
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/93988

2910 Chuck Bell	2009-12-14
      BUG#43596 : Restore fails on case ins. server for databases who's 
                  names differ in case only
      
      The test rpl_backup_shutdown fails on valgrind in pushbuild due to
      a missing delete of a new object.
      
      This patch adds the missing delete statement.
[15 Dec 2009 18:38] Bugs System
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/94244
[15 Dec 2009 18:43] Bugs System
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/94245

2909 Chuck Bell	2009-12-15
      PB fixes - there were errors in some tests pushed with BUG#43596.
     @ mysql-test/suite/backup/t/backup_cs_names.test
        Skip test on systems with lctn!=0.
     @ mysql-test/suite/backup/t/backup_cs_names_lctn0.test
        Skip test on systems with lctn!=0.
     @ mysql-test/suite/backup/t/backup_cs_names_lctn1.test
        - Skip test on systems with lctn!=1.
        - Fix error constant names.
     @ mysql-test/suite/backup/t/backup_cs_names_lctn2.test
        Skip test on systems with lctn!=2.
     @ mysql-test/suite/backup/t/backup_events.test
        Mask warnings whose order can be non-deterministic.
     @ mysql-test/suite/backup/t/backup_thd_delete.test
        Skip test on systems which do not have debug sync facility.
[15 Dec 2009 19:23] Bugs System
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/94255
[15 Dec 2009 19:31] Bugs System
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/94256

2909 Chuck Bell	2009-12-15
      PB fixes - there were errors in some tests pushed with BUG#43596.
     @ mysql-test/suite/backup/r/backup_events.result
        Corrected result file.
     @ mysql-test/suite/backup/r/backup_thd_delete.result
        Corrected result file.
     @ mysql-test/suite/backup/t/backup_cs_names.test
        Skip test on systems with lctn!=0.
     @ mysql-test/suite/backup/t/backup_cs_names_lctn0.test
        Skip test on systems with lctn!=0.
     @ mysql-test/suite/backup/t/backup_cs_names_lctn1.test
        - Skip test on systems with lctn!=1.
        - Fix error constant names.
     @ mysql-test/suite/backup/t/backup_cs_names_lctn2.test
        Skip test on systems with lctn!=2.
     @ mysql-test/suite/backup/t/backup_events.test
        Mask warnings whose order can be non-deterministic.
     @ mysql-test/suite/backup/t/backup_thd_delete.test
        Skip test on systems which do not have debug sync facility.
[17 Dec 2009 12:44] Bugs System
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/94733

2918 Rafal Somla	2009-12-17
      BUG#43596 - test fixes.
      
      Tests which expect that names which differ in case only should not
      be run on systems which do not support it (notably, windows). Originally
      they were checking for lctn=0 but this turns out to not be enough. E.g.,
      on windows, even if lctn=0 still names which differ in case only are
      not allowed.
      
      Fixed by adding requirement for a case sensitive file system.
     @ mysql-test/suite/backup/t/backup_cs_names.test
        Requie case sensitive filesystem as lctn=0 alone is not enough.
     @ mysql-test/suite/backup/t/backup_cs_names_lctn0-master.opt
        Set lctn=0 explicitly
     @ mysql-test/suite/backup/t/backup_cs_names_lctn0.test
        Requie case sensitive filesystem as lctn=0 alone is not enough.
[17 Dec 2009 14:49] Bugs System
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/94771

2918 Rafal Somla	2009-12-17
      BUG#43596 - test fixes.
      
      Tests which expect that names which differ in case only are allowed 
      should not be run on systems which do not support it (notably, Windows).
      Originally they were checking for lctn=0 but this turns out to be not
      sufficient. E.g., on Windows, even if lctn=0 still names which differ 
      in case only are not allowed.
      
      Fixed by adding requirement for a case sensitive file system.
     @ mysql-test/suite/backup/t/backup_cs_names.test
        Requrie case sensitive filesystem as lctn=0 alone is not enough.
     @ mysql-test/suite/backup/t/backup_cs_names_lctn0-master.opt
        Set lctn=0 explicitly
     @ mysql-test/suite/backup/t/backup_cs_names_lctn0.test
        Requrie case sensitive filesystem as lctn=0 alone is not enough.
[17 Dec 2009 14:51] Bugs System
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/94772

2918 Rafal Somla	2009-12-17
      BUG#43596 - test fixes.
      
      Tests which expect that names which differ in case only are allowed 
      should not be run on systems which do not support it (notably, Windows).
      Originally they were checking for lctn=0 but this turns out to be not
      sufficient. E.g., on Windows, even if lctn=0 still names which differ 
      in case only are not allowed.
      
      Fixed by adding requirement for a case sensitive file system.
     @ mysql-test/suite/backup/t/backup_cs_names.test
        Requrie case sensitive filesystem as lctn=0 alone is not enough.
     @ mysql-test/suite/backup/t/backup_cs_names_lctn0-master.opt
        Set lctn=0 explicitly
     @ mysql-test/suite/backup/t/backup_cs_names_lctn0.test
        Requrie case sensitive filesystem as lctn=0 alone is not enough.
[20 Feb 2010 9:16] Bugs System
Pushed into 6.0.14-alpha (revid:ingo.struewing@sun.com-20100218152520-s4v1ld76bif06eqn) (version source revid:ingo.struewing@sun.com-20100119103538-wtp5alpz4p2jayl5) (merge vers: 6.0.14-alpha) (pib:16)
[1 Mar 2010 20:51] Rafal Somla
Hi Paul,

Yes, the fix uses the proposed design. It is in the patch(es) from 10 Dec. The patch contains a high level description of the implemented changes. Then there are several follow-up patches which fix failing tests detected by PB.
[17 Mar 2010 17:43] Paul Dubois
Noted in 6.0.14 changelog.

On systems with case-insensitive file systems or with
lower_case_table_names=1, RESTORE did not detect collisions between
object names that differed only in lowercase and could not be
restored. Now it checks the backup image before deleting any objects
for such collisions and terminates the restore operation with an
error.