MySQL Bugs: #9660: alter table changes case of table name on Windows

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

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.

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.

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>

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.

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.

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.

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

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

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

Fixed in 4.1.13 and 5.0.7.

Noted in 4.1.13, 5.0.7 changelogs.