MySQL Bugs: #46266: RESTORE fails on a server with lower_case_table

Bug #46266	RESTORE fails on a server with lower_case_table_names=2
Submitted:	17 Jul 2009 15:04	Modified:	29 Aug 2009 23:18
Reporter:	Ingo Strüwing	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Backup	Severity:	S2 (Serious)
Version:	5.4	OS:	Windows
Assigned to:	Rafal Somla	CPU Architecture:	Any

Description:
The magic triple is:
- lower_case_table_names=2 (this means Windows)
- upper-case database name
- grant on the database or something in it

#
# Check lower_case_table_names
SELECT @@lower_case_table_names;
@@lower_case_table_names
2
#
# Create upper-case database
CREATE DATABASE MYSQLTEST_DB2;
#
# Grant a privilege on the database
GRANT SELECT ON MYSQLTEST_DB2.* TO 'mysqltest_user2';
#
# Backup
BACKUP DATABASE MYSQLTEST_DB2 TO 'mysqltest_db2.bak';
backup_id
###
#
# Restore
RESTORE FROM 'mysqltest_db2.bak' OVERWRITE;
ERROR HY000: The grant '21 'mysqltest_user2'@'%
25 SELECT ON mysqltest_db2.*
32 SET cha' failed. Database not included in the backup image.

How to repeat:
--echo #
--echo # Check lower_case_table_names
SELECT @@lower_case_table_names;
--echo #
--echo # Create upper-case database
CREATE DATABASE MYSQLTEST_DB2;
--echo #
--echo # Create a privilege on the database
GRANT SELECT ON MYSQLTEST_DB2.* TO 'mysqltest_user2';
--echo #
--echo # Backup
--replace_column 1 ###
BACKUP DATABASE MYSQLTEST_DB2 TO 'mysqltest_db2.bak';
--echo #
--echo # Restore
--error ER_BACKUP_GRANT_WRONG_DB
RESTORE FROM 'mysqltest_db2.bak' OVERWRITE;

Suggested fix:
Compare database names case insensitively for lower_case_table_names=1 or 2, as stated in the reference manual: http://dev.mysql.com/doc/refman/6.0/en/server-system-variables.html#sysvar_lower_case_tabl...

Suggested triage values:
Defect: serious. Can't restore backups.
Workaround: unacceptable. One could restart the server with lower_case_table_names=1. But that could create other problems. One could (hex)edit the backup image file and upcase the database names in the grant statements.
Impact: substantial. lower_case_table_names=2 may not be too exceptional on Windows.

A comment to the statment "lower_case_table_names=2 may not be too exceptional on Windows"

I'd rather say they almost everybody using *nix for production and Windows for development/test has "lower_case_table_names=2 in configuration on Windows (if they know about the option).

It is not serious - it is very serious!  But reports with lower_case_table_names have been largely ignored for 2-3 years now. No activity at all it seems. There are more reports.

I don't think this is a backup bug. The mysql.db tables contains the database-level privileges for users. I ran the test case but used 'M' for the database and 'joe'@'user' for the user. This is what is in the mysql.db table:

*************************** 15. row ***************************
                 Host: user
                   Db: m
                 User: joe
          Select_priv: Y
          Insert_priv: N
          Update_priv: N
          Delete_priv: N
          Create_priv: N
            Drop_priv: N
           Grant_priv: N
      References_priv: N
           Index_priv: N
           Alter_priv: N
Create_tmp_table_priv: N
     Lock_tables_priv: N
     Create_view_priv: N
       Show_view_priv: N
  Create_routine_priv: N
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: N
         Trigger_priv: N
          Backup_priv: N
         Restore_priv: N

Clearly, when the si_objects code reconstructs the grant, it will use 'm'. Thus, the backup code is doing its job. I think the problem lies in how the system is behaving WRT lower_case_table_names and grant statements.

Pushed into 5.4.4-alpha (revid:alik@sun.com-20090806082225-qssc912qdv1mm6xv) (version source revid:ingo.struewing@sun.com-20090720092748-euvku4kthos51btb) (merge vers: 5.4.4-alpha) (pib:11)

Incidentally, this problem has been fixed with the patch for BUG#43444, since the patch removed the code checking privilege serialization strings.

I will add a test case for confirming that the problem is fixed.

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

2854 Rafal Somla	2009-08-11
      Bug #46266 - RESTORE fails on a server with lower_case_table_names=2
      
      This problem was fixed by BUG#43444. Enabling test cases which 
      previously failed due to this bug. They will act as a regression test.
     @ mysql-test/suite/backup/t/backup_xpfm_compat_restore_lctn2.test
        Enabling test cases which were previously failing because 
        of the bug.

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

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

Pushed to team tree (sever ver 5.4.4).
revision-id:rafal.somla@sun.com-20090813093400-uqm13abvfhvpvd1d

Pushed into 5.4.4-alpha (revid:alik@sun.com-20090828100112-r73xkx8dhekz5bbb) (version source revid:jorgen.loland@sun.com-20090818084812-rlq2mh37241doswu) (merge vers: 5.4.4-alpha) (pib:11)

Not in any released version. No changelog entry needed.