Bug #46238 SHOW GRANTS converts names to lower case with lower_case_table_names=2
Submitted: 16 Jul 2009 18:12 Modified: 28 Apr 2010 8:00
Reporter: Ingo Strüwing Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:5.4 OS:Other (Windows, OS X)
Assigned to: CPU Architecture:Any

[16 Jul 2009 18:12] Ingo Strüwing
Description:
On Windows, if server runs with lower_case_table_names=2, SHOW GRANTS shows object names, like databse names and table names, in lower case.

With lower_case_table_names=2, names should be kept in original case, but compared in lower case.

The current behavior is a problem for BACKUP/RESTORE. If a backup is taken from a server with lower_case_table_names=2, it cannot be restored on a server with lower_case_table_names=0. It would restore the database in upper case, and could not restore the privileges, which refer to the database in lower case.

How to repeat:
CREATE DATABASE MYSQLTEST_DB2;
CREATE TABLE MYSQLTEST_DB2.TABLE2 (COL1 INT);
CREATE USER 'MYSQLTEST_USER2';
GRANT SELECT ON MYSQLTEST_DB2.* TO 'MYSQLTEST_USER2';
GRANT UPDATE ON MYSQLTEST_DB2.TABLE2 TO 'MYSQLTEST_USER2';

SHOW DATABASES LIKE 'MYSQLTEST_%';
SHOW TABLES FROM MYSQLTEST_DB2;
SHOW GRANTS FOR 'MYSQLTEST_USER2';

Suggested fix:
In sql_acl.cc we have several calls to my_casedn_str() if lower_case_table_names != 0. Some or all of the conditions might need to be changed to lower_case_table_names == 1.
[16 Jul 2009 18:23] Ingo Strüwing
Suggested triage values:
Defect: medium. The bahavior contrdicts the manual.
Workaround: partial. The restoring server could be restarted with lower_case_table_names=1. However, this might not always be possible.
Impact: substantial. Exchange of backups between Windows (lctn=2) and Linux (lctn=0) may not be exceptional.
[16 Jul 2009 21:11] Peter Laursen
Serious that this is 'S3*? In my opinion it is much worse than that!

lower_case_table names never worked properly since 4.0.  I have a 2+ year old bug report on that. 'verified' .. and absolutely no activity for the 2 years! :-(

2 years :-( :-( :-(
[6 Aug 2009 8:27] Bugs System
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)
[21 Apr 2010 9:51] Alexander Nozdrin
Actually this bug is not platform specific -- it can happen on any case-insensitive file system.

It does not happen on default Linux, because ext3 is case-sensitive.
It reproduces on Windows and Mac OS X.
[28 Apr 2010 7:57] Alexander Nozdrin
Asking for re-triage, because this bug was needed for Backup,
and Backup now is not in 5.5.

Moreover, Backup fails *only* if one backups and restores
with different lower_case_table_names values. So the problem
is even more unimportant.

Note, dump/restore also does not work in some cases with different
lower_case_table_names, which is a more important flaw!

It seems that this bug should wait for:
  - WL#922 Store regular identifiers in upper case
  - WL#923 Case-sensitive delimited identifiers
which gonna fix all platform incompatibilities caused by
different filesystem case-sensitivity.