MySQL Bugs: #1493: mysqldump puts table names in different cases

Bug #1493	mysqldump puts table names in different cases
Submitted:	6 Oct 2003 22:07	Modified:	9 Apr 2008 6:17
Reporter:	Ilia Chirchov	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	./mysqldump Ver 9.08 Distrib 4.0.15a	OS:	Any (unknown-freebsd4.8 (i386))
Assigned to:	Sergei Golubchik	CPU Architecture:	Any

Description:
When dumping a database located on windows machine with mysqldump from freebsd machine the resulting code looks like:

CREATE DATABASE /*!32312 IF NOT EXISTS*/ COMP2000;

USE COMP2000;

--
-- Table structure for table 'SYS_GRP'
--

DROP TABLE IF EXISTS `SYS_GRP`;
CREATE TABLE `sys_grp` (
  `grpid` smallint(4) unsigned NOT NULL auto_increment,
  `cnt` smallint(5) unsigned NOT NULL default '0',
  PRIMARY KEY  (`grpid`),
  KEY `grpid` (`grpid`,`cnt`)
) TYPE=MyISAM;

--
-- Dumping data for table 'SYS_GRP'
--

/*!40000 ALTER TABLE `SYS_GRP` DISABLE KEYS */;
LOCK TABLES `SYS_GRP` WRITE;
INSERT INTO `SYS_GRP` VALUES (1,4157);

the difference in cases for CREATE statement and all others makes the dump unusable unless manually edited.

How to repeat:
Well, I ran on freebsd machine

./mysqldump --all --complete-insert --all-databases --host=xx.xx.xx.xx --user=root --quick --add-drop-table --quote-names|./mysql -u root

where host is windows machine

Suggested fix:
do not change cases

problem fixes by renaming files of myisam database in mysql data directory into lower case.

The issue here is the Windows behavior which is from NT and above
SENSITIVE for write and INSENSITIVE for read. I am assigning here
PeterG for to analyze this case as feature request for MySQL
to handle it internally and not dependent of OS (Windows).

According Sergei's request here the 'show tables' and 'show create table'
results on Windows:

mysql> create table MYmixTABLE (id int);
Query OK, 0 rows affected (0.03 sec)

mysql> show tables\G

*************************** 2. row ***************************
Tables_in_test: mymixtable

3 rows in set (0.00 sec)

mysql> show create table MYmixTABLE\G
*************************** 1. row ***************************
       Table: MYmixTABLE
Create Table: CREATE TABLE `mymixtable` (
  `id` int(11) default NULL
) TYPE=MyISAM
1 row in set (0.01 sec)

We failed to repeat the bug :(

Illia, what is the version of the server ? The mysqld server that you use on windows ?
mysqldump version is not releant, as it's the server issue.

I have this on a win2k machine.

it seems to be a problem with mysqld use of the file system on ntfs

ntfs is case-sensitive.

the os ontop of it is NOT.

hence it is case preserving, (there are more caveats but you need to funny stuff to get to them)

the proper solution is not UPPERCASE the data files on create table xxx

sorry, here are my specs for win2k(cygwin) laptop, which has same type problems.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 40 to server version: 4.0.15-max-debug

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

mysql> \s
--------------
c:\mysql\bin\mysql.exe  Ver 12.21 Distrib 4.0.15, for Win95/Win98 (i32)

Connection id:          40
Current database:       rejistor
Current user:           rejistor@localhost
SSL:                    Not in use
Server version:         4.0.15-max-debug
Protocol version:       10
Connection:             localhost via TCP/IP
Client characterset:    latin1
Server characterset:    latin1
TCP port:               3306
Uptime:                 3 days 21 hours 48 min 17 sec

Threads: 1  Questions: 824  Slow queries: 0  Opens: 63  Flush tables: 1  Open tables: 0  Queries per second avg: 0.002 Memory in use: 8318K  Max memory used: 8880K
--------------

mysql>

Many thanks for writing a bug report.

Besides of the lower/upper case problem between Unix and Windows this problem don't occur on newer versions of MySQL.
I will close this bug now, because there is no MySQL bug anymore.