Bug #16323 lower_case_table_names breaks case sensitive directories

• Submitted: 10 Jan 2006 0:05
• Modified: 17 Sep 2007 8:45
• Reporter: Cristian Gafton
• Status: Can't repeat
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.0.19-BK, 5.0.18
• OS: Linux (Linux)
• Assigned to: Tatiana Azundris Nuernberg
• CPU Architecture: Any
• Tags: qc

[10 Jan 2006 0:05] Cristian Gafton

Description:
When running with 'lower_case_table_names = 1' enabled on Linux (or other operating systems that have case-sensitive filesystems), the MySQL server wrngly applies a LOWER() call to the _entire_ pathname of a database instead of just the table name.

For example:
datadir=/srv/MySQL
lower_case_table_names = 1
default-table-type = INNODB

CREATE TEMPORARY TABLE foo(id integer);
OperationalError: (1049, "Unknown database 'mysql')

Same testcase works when running with datadir=/srv/mysql

How to repeat:
see attached script

[10 Jan 2006 0:08] Cristian Gafton

Script that demonstrates the broken pathname lookup when using lower_case_table_names

Attachment: broken-mysql.sh (application/x-sh, text), 1.47 KiB.

[10 Jan 2006 9:07] Valeriy Kravchuk

Thank you for a bug report. Verified just as described, with your test script on 5.0.19-BK (ChangeSet@1.2020, 2006-01-06 08:34:55-08:00) on Linux:

[openxs@Fedora 5.0]$ bash broken-mysql.sh
ERROR 1049 (42000) at line 1: Unknown database 'test'

This is the result when data dirctory contains uppercase letters. If they are all lowercase, script works OK.

[11 Sep 2006 10:03] 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/11683

ChangeSet@1.2272, 2006-09-11 03:02:48-07:00, [e-mail] +5 -0
  Fix for bug #16323 (lower_case_table_names breaks case sensitive directories)
  
  Problem:  Code is lowercasing *everything* in some paths, instead
  of just the database/table name.
  
  Solution:  Only lowercase database/table name.  Added fn to help with
  conversion.

[17 Sep 2007 8:45] Tatiana Azundris Nuernberg

[mysqld]
datadir=/tmp/MySQL
lower_case_table_names = 1
default-table-type = INNODB

Server version: 5.0.48-debug Source distribution
mysql> use test;
Database changed
mysql> show variables like 'data%';
+---------------+-------------+
| Variable_name | Value       |
+---------------+-------------+
| datadir       | /tmp/MySQL/ |
+---------------+-------------+
1 row in set (0.01 sec)

mysql> CREATE TEMPORARY TABLE foobar(id integer) engine=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE CatWoman(id integer) engine=innodb;
Query OK, 0 rows affected (0.04 sec)

mysql> show create table catwoman;
+----------+----------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                 |
+----------+----------------------------------------------------------------------------------------------+
| catwoman | CREATE TABLE `catwoman` (
  `id` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 | 
+----------+----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

/tmp/MySQL/test/foobaz.frm
/tmp/MySQL/test/catwoman.frm