MySQL Bugs: #2994: lower_case_table_names=2 is not sensible for InnoDB

Bug #2994	lower_case_table_names=2 is not sensible for InnoDB
Submitted:	27 Feb 2004 7:26	Modified:	5 Mar 2004 4:40
Reporter:	Heikki Tuuri	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	4.0.18	OS:	MacOS (OS X)
Assigned to:	Michael Widenius	CPU Architecture:	Any

Description:
Hi!

InnoDB stores table names internally as binary strings. It cannot find table 'aBBaguu', if MySQL is asking for table 'abbaguu'.

From the manual:
"
Value Meaning

0 Table and database names are case sensitive and are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASE statement. This is the default on Unix systems.

1 Table and database names are not case sensitive and are stored on disk in lowercase. MySQL will convert all table names to lowercase on storage and lookup. This is the default on Windows and Mac OS X systems. (This option also applies to database names as of MySQL 4.0.2, and to table aliases as of 4.1.1.)

2 New in 4.0.18: Table and database names are not case sensitive, but are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASE statement. MySQL will convert all table names to lowercase on storage and lookup. Note: This works only on file systems that are not case sensitive!
"

Value 2 is not sensible for InnoDB. It cannot work that way.

On Windows InnoDB always sets all table and database names internally to lower case, therefore this bug does not happen on Windows.

Regards,

Heikki

................
List:MySQL General Discussion« Previous MessageNext Message »
From:Bruce DembeckiDate:February 27 2004 2:01pm
Subject:lower_case_table_names, Mac OSX, InnoDB and 4.0.18

There seems to be some issues we are experiencing with this new
lower_case_table_names variable introduced in 4.0.17 and modified in 4.0.18.
So much so that I can't upgrade to 4.0.18 at all.

Here is the startup log from a 4.0.18 mysqld:

040227 02:00:22 mysqld started
040227 2:00:22 Warning: Setting lower_case_table_names=2 because file
system for /mysqldata/ is case insensitive
InnoDB: Error: tablespace size stored in header is 2438400 pages, but
InnoDB: the sum of data file sizes is 2560000 pages
040227 2:00:23 InnoDB: Started
/usr/local/mysql/bin/mysqld: ready for connections.
Version: '4.0.18-standard-log' socket: '/tmp/mysql.sock' port: 3306
040227 2:00:23 InnoDB error:
Cannot find table jive_ebay_us/jiveuser from the internal data dictionary of
InnoDB though the .frm file for the table exists. Maybe you have deleted and
recreated InnoDB data files but have forgotten to delete the corresponding
.frm files of InnoDB tables, or you have moved .frm files to another
database? Look from section 15.1 of http://www.innodb.com/ibman.html how you
can resolve the problem.
ERROR: 1016 Can't open file: 'jiveuser.InnoDB'. (errno: 1)
040227 2:00:23 Slave: Error 'Can't open file: 'jiveuser.InnoDB'.
(errno:1)' on query 'UPDATE jiveUser SET some user data WHERE some
qualifer'. Default database: 'jive_ebay_us', Error_code: 1016
040227 2:00:23 Error running query, slave SQL thread aborted. Fix the
problem, and restart the slave SQL thread with "SLAVE START". We stopped at
log 'binary-log.008' position 781142831

So, let's walk through these... For starters mysqld decides on it's own it
will set lower_case_table_names=2 on it's own because "file system for
/mysqldata/ is case insensitive"... Yes, that's true, the file system is
case insensitive, it's Mac OSX 10.3. However because we have had issues with
this in the 4.0.17 introduction of this "feature" we have a statement in our
my.cnf file which specifically says "set-variable =
lower_case_table_names=0". Despite us manually telling MySQL that we want it
to be case sensitive in all cases it ignores our setting and chooses it's
own.

Now that it has ignored our request to be case sensitive all of the time for
all of the requests we are experiencing problems with InnoDB not recognizing
we have tables that we have. Specifically in the error cited above we have a
table called jiveUser, that is evident in the way the request is made to to
the database to "set jiveUser"... Yet InnoDB is failing because it can't
find jiveuser. So it seems that MySQL has behaved as promised with a
lower_case_table_names=2 value and converted the request to lower case...
InnoDB however can't find a lower case version of this table. Of course all
our table names have upper and lower case characters and this particular
entry happened to score the jackpot because it was the first query too come
along after we booted 4.0.18. It happens on all our databases, on all our
tables.

I don't know what we are doing wrong here... Any value of
lower_case_table_names results in 4.0.18 not running (OK, it runs, but our
applications don't run with it), so we can not upgrade to 4.0.18. MySQL
seems to be ignoring our my.cnf startup value for lower_case_table_names and
InnoDB seems to be unable to find our tables if MySQL changes the case to
lower case.

Best Regards, Bruce

How to repeat:
See above.

Suggested fix:
For Bruce, the only solution is to allow the user to set

lower_case_table_names=0

on OS X.

Generally, InnoDB could set table and database names always internally to lower case on OS X, too. But why not remove the whole option 2?

The problem has not surfaced on OS X before 4.0.17, because users have been very careful and always used the same case for table names in CREATE TABLE and all the queries. Then it is ok to set lower_case_table_names=0. We could require users to be careful also in the future.

Also, option 2 introduces a new GOTCHA: a sloppy user will mix character cases on OS X, and then encounters a portability problem when porting to another Unix.

OK, so to summarize Heikki's excellent and rapid input InnoDB can't currently deal with 
lower_case_table_names=2 and from my experience to date MySQL 4.0.18 on Mac OSX 10.3 won't 
run in any mode other than lower_case_table_names=2.

Shortest term fix for my immediate problem would be to tell me how I can change 
lower_case_table_names to a different value, ideally we want 0 because our applications are well 
written and always work knowing that case is sensitive.

It seems that there is a second bug here in that on Mac OSX MySQL sets itself to 
lower_case_table_names=2 ignoring or overwriting our "set-variable = 
lower_case_table_names=0" line in my.cnf. For what it is worth we have also tried using the line 
formated as "lower_case_table_names=0".

So in combination the fact that InnoDB and lower_case_table_names=2 don't mix and the fact that 
Mac OSX is overwriting our settings and using lower_case_table_names=2 means we can't use 
4.0.18 at all.

Bruce, use lower_case_table_names=1
it won't be changed to lower_case_table_names=2 automatically

How about this way to fix the value 2: MySQL could look at the name of the .frm file, and internally convert all table names to the same case as the in the name of the .frm file. The way the user specifies the name in an arbitrary SQL query (except CREATE) should not affect the internal name of the table. I think this is the most logical way on case-insensitive file systems.

Regards,

Heikki

Sergei suggested:

Bruce, use lower_case_table_names=1 it won't be changed to lower_case_table_names=2
automatically

The manual states that a value of 1 behaves like:

Table and database names are not case sensitive and are stored on disk in lowercase. MySQL will
convert all table names to lowercase on storage and lookup. This is the default on Windows and
Mac OS X systems. (This option also applies to database names as of MySQL 4.0.2, and to table
aliases as of 4.1.1.)

I have 50 databases with 36 tables each containing mixed case table names, already stored in
InnoDB data... it may be true that with lower_case_table_names=1 new table and database names
are stored on disk in lowercase, but it won't affect the 1500+ tables I already have in InnoDB
storage space with mixed case table names and of course MySQL will convert all the table names
to lowercase as queries come in, so they still won't match what InnoDB has in it's filespace.

Using any valid value of lower_case_table_names with 4.0.18 generates errors such as the one
described above. In the case of a value of 0 or 2 it uses 2 and as Heikki says 2 and InnoDB aren't
compatible. In the case of 1 it converts incoming queries to lower case table names and that
doesn't match what InnoDB already has... so every attempt to use 4.0.18 has failed with MySQL
and InnoDB unable to sort out what tables they are being asked about.

The only truly workable solution here is to use a value of 0 and have it behave in a case sensitive
manner, but for reasons I don't understand, it won't do that... I understand the desire to add this
feature to make the MySQL product more idiot proof so even people who don't understand the
concept of case sensitive file systems or who write sloppy code can successfully use MySQL, but
the result of this implementation as it currently stands is to penalize those who write good
applications and follow the rules as our tables are now unrecognized by MySQL.

I'm fine in principal with you setting the default value for OSX to 1, and even making the features
on the 2 value available (although I don't think Heikki is too pleased on that issue), but don't offer
these features at the expense of the original functionality... we need to have an option to use the
behavior specified by lower_case_table_names=0... you can't be converting our queries but not
our existing tables.

As to the thought of converting our code to be all lower case, it isn't going to happen... we have 20
live application servers serving a total of 2 instances for each of 50 applications accessing 6 MySQL
servers with ~1500 tables in InnoDB... I could write a script to relatively painlessly ALTER all my
tables to MyISAM and back again to InnoDB under 4.0.18 which would result in them all being
stored with lower case table names, but it would take hours and hours to run... in the meanwhile
we'd be offline, and we can't be offline for hours and hours. Once that was done it would take
many weeks of engineering resources to go through all our application code and convert all the
table names in the software.

We followed the rules of good programming and wrote everything so it would be case sensitive. We
didn't break this.

Side issue... assuming you and Heikki work out a solution and somewhere along the way
lower_case_table_names=2 becomes workable... what is the performance overhead involved in
changing every incoming query's table names to lower case on the fly? I'm sure it is tiny looked at
on it's own... but we do over 300 Million queries each week, 300 Million times a tiny perfomance
overhead becomes big system resource drain guys... Another argument why we need to be able to
use lower_case_table_names=0 regardless of our platform.

Bruce

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information
about accessing the source trees is available at
http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Thanks for the good description in this bug report. I hope that the workaround we have added in the 4.0.18 tree (fix will be in 4.0.19 and 4.1.2) will help solve your problems.

The reason we introduced lower_case_table_names=2 was that if you use MyISAM tables on a case insensitive file system and use them with mixed case, then you will get an index file corruption as the MyISAM table handler will not notice that you are using the same files in conflicting operations.

lower_case_table_names=2 also allows on to move files between different file system, independent of the case sensitiviness of them (as long you follow some simple roles). A lot of MySQL users on windows have requested this feature from us.

What was left out of the documentation is that if you use lower_case_table_names=2 then all InnoDB tables will be stored and retrived in lower case (to ensure that the user can use them case insensitive). We verified that there was no problem on windows, but missed to test this properly on Mac OS X on a system with old InnoDB tables :( (I didn't know that InnoDB stored files differently between these operation systems).

The way to get this to work is to:

- Make a mysqldump of all InnoDB tables
- Drop them from the database
- Start mysqld with --lower-case-table-names=2
- Insert them again.

To make life a little easyer for people that gets hit by this bug on MacOS X we have done the following:

- Added a lot of documentation about this issue in the MySQL manual.
- If you specify --lower-case-table-names=0 on the command line or in an option file MySQL will honor this flag (it will not automaticly change it to 2). If the file system is case insensitive it will write a warning in the error log that if one is not carefull one can crash an MyISAM index.

Regards,
Monty