Bug #9660 alter table changes case of table name on Windows
Submitted: 5 Apr 2005 18:44 Modified: 3 Jun 2005 23:54
Reporter: Thom Krystofiak Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.10a-nt OS:Windows (Windows)
Assigned to: Jim Winstead CPU Architecture:Any

[5 Apr 2005 18:44] Thom Krystofiak
Description:
On Windows, an alter table command will change the case of a mixed case table name to all lower case if you have lower_case_table_names set to the recommended "2".

How to repeat:
Be sure you have lower_case_table_names=2

create table mixedCase(bar int);

Check that the table was created with mixed case.

alter table mixedCase alter bar set default '6';

Now see that the tablename is changed to all lower case.

If you set lower_case_table_names=0 (not recommended in the MySQL docs for Windows due to possible index corruption), then this problem does not occur.
[5 Apr 2005 18:55] MySQL Verification Team
Could you please verify in your my.ini file if the default storage engine
is InnoDB, if yes then that engine converts in the background to
lower case. If you want to use MyISAM table with mixed cases then
include in the create table command the storage engine or remove
the default storage engine from the configuration file.
[5 Apr 2005 19:10] MySQL Verification Team
I forgot to paste the sample:

c:\mysql\bin>mysqld-max-nt --console --standalone --lower-case-table-names=2
050405 16:07:24  InnoDB: Started; log sequence number 0 43634
mysqld-max-nt: ready for connections.
Version: '4.1.10-nt-max'  socket: ''  port: 3306  MySQL Community Edition (GPL)

c:\mysql\bin>mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.10-nt-max

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database utest;
Query OK, 1 row affected (0.02 sec)

mysql> use utest;
Database changed
mysql> create table mixedCase(bar int);
Query OK, 0 rows affected (0.07 sec)

mysql> show tables;
+-----------------+
| Tables_in_utest |
+-----------------+
| mixedCase       |
+-----------------+
1 row in set (0.00 sec)

mysql> alter table mixedCase alter bar set default '6';
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show tables;
+-----------------+
| Tables_in_utest |
+-----------------+
| mixedCase       |
+-----------------+
1 row in set (0.01 sec)

mysql> show create table mixedCase\G
*************************** 1. row ***************************
       Table: mixedCase
Create Table: CREATE TABLE `mixedCase` (
  `bar` int(11) default '6'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql>
[5 Apr 2005 19:14] Thom Krystofiak
my.ini has default-storage-engine=INNODB.  Are you saying that there is no way to preserve mixed case with INNODB?  Mixed case persists on Linux (where we have lower_case_table_names=0) and on Windows if we set lower_case_table_names=0, though that setting is not the recommended one.
[5 Apr 2005 19:31] MySQL Verification Team
From the Manual:

http://dev.mysql.com/doc/mysql/en/innodb-restrictions.html

On Windows, InnoDB always stores database and table names internally in lowercase. To move databases in binary format from Unix to Windows or from Windows to Unix, you should have all database and table names in lowercase.
[5 Apr 2005 21:52] Thom Krystofiak
Please clarify.  On Windows, almost all of our tables are INNODB, and they are all created with mixed case table names.  With lower_case_table_names set to 0 or 2, we find that the mixed case names persist forever the way we create them (except for this ALTER TABLE problem).  In 4.0, even an ALTER TABLE statement did not change them to lower case - but now we find that in 4.1, ALTER TABLE changes mixed case table names to all lower case (unless we set lower_case_table_names to 0).  The reason this seems like a bug is that (1) the mixed case names were rock-solid in 4.0 even with INNODB, and (2) the mixed case names persist even in 4.1 for INNODB tables except for this strange side-effect of ALTER TABLE.
[6 Apr 2005 12:29] Heikki Tuuri
Hi!

Some notes: InnoDB's internal table names cannot affect the case that MySQL assigns to the table name in the .frm file. Thus, this bug is not in InnoDB. Internally, InnoDB has all table names in lower case in Windows.

Having lower_case_table_names=0 is safe for InnoDB type tables. The danger of corruption is only for MyISAM tables.

I consider this case change a bug, because the .frm file does not keep its case for an InnoDB type table.

Regards,

Heikki
[26 May 2005 3:27] 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/internals/25291
[27 May 2005 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/internals/25318
[2 Jun 2005 1:11] Jim Winstead
Fixed in 4.1.13 and 5.0.7.
[3 Jun 2005 23:54] Paul DuBois
Noted in 4.1.13, 5.0.7 changelogs.