| Bug #7972 | alter table with lower_case_table_names=2 changes table name to lowercase | ||
|---|---|---|---|
| Submitted: | 17 Jan 2005 20:31 | Modified: | 26 Feb 2005 0:52 |
| Reporter: | Don Thorp | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 4.1.7 | OS: | Windows (Windows XP SP2) |
| Assigned to: | CPU Architecture: | Any | |
[19 Jan 2005 11:02]
MySQL Verification Team
Hi, Thnak you for the report, but I can't reproduce it with 4.1.9-nt. Please, try latest version of MySQL server.
[25 Feb 2005 21:26]
Jim Monte
I have the same problem using 4.1.10-nt on Windows NT4 sp6a: mysql> create table Test(col1 int) Type=InnoDB; -------------- create table Test(col1 int) Type=InnoDB -------------- Query OK, 0 rows affected, 1 warning (0.05 sec) mysql> show tables; -------------- show tables -------------- +----------------+ | Tables_in_test | +----------------+ | Test | +----------------+ 1 row in set (0.00 sec) mysql> alter table Test add column Test2 int; -------------- alter table Test add column Test2 int -------------- Query OK, 0 rows affected (0.11 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show tables; -------------- show tables -------------- +----------------+ | Tables_in_test | +----------------+ | test | +----------------+ 1 row in set (0.00 sec) mysql> show variables like 'lower%'; -------------- show variables like 'lower%' -------------- +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | lower_case_file_system | ON | | lower_case_table_names | 2 | +------------------------+-------+ 2 rows in set (0.00 sec) mysql> show variables like 'version'; -------------- show variables like 'version' -------------- +---------------+-----------+ | Variable_name | Value | +---------------+-----------+ | version | 4.1.10-nt | +---------------+-----------+ 1 row in set (0.00 sec)
[26 Feb 2005 0:52]
MySQL Verification Team
According as stated in the Manual's Item: 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. With MyISAM: C:\mysql\bin>mysql -uroot tt Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 to server version: 4.1.10-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> create table Test ( -> col1 int -> ) Type=MyISAM; Query OK, 0 rows affected, 1 warning (0.07 sec) mysql> mysql> alter table Test add column Test2 int; Query OK, 0 rows affected (0.12 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> mysql> show tables; +--------------+ | Tables_in_tt | +--------------+ | Test | +--------------+ 1 row in set (0.00 sec) mysql>
[9 Mar 2005 19:53]
Jim Monte
Despite the documentation, *something* is maintaining the case of InnoDB table names since I can create a table, stop and restart both client and server and then a "SHOW TABLES;" command will display the table name with its proper case. At the very least, the tables should be consistently lowercased as soon as they are created so there are no surprises when they are altered. Of course, it would be better if the case could be maintained. The suggestion to always use lower case is a good one, but once a database has been created, it is too late to change table names without introducing many potential problems with applications already using existing names.
Description: With lower_case_table_names=2 on Windows, altering the table causes the table name to be lowercase. We frequently use databases on Windows and Linux and this is a problem. How to repeat: create table Test ( col1 int ) Type=InnoDB; alter table Test add column Test2 int; show tables;