| 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: | |
| Category: | MySQL Server: Backup | Severity: | S3 (Non-critical) |
| Version: | 6.0 | OS: | Any |
| Assigned to: | Rafal Somla | CPU Architecture: | Any |
[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.
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.