MySQL Bugs: #5185: mysqldump for windows database gets table names with different case

Bug #5185	mysqldump for windows database gets table names with different case
Submitted:	24 Aug 2004 16:59	Modified:	8 Feb 2005 19:35
Reporter:	Olof Tjerngen	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S3 (Non-critical)
Version:	3.23, 4.0, 4.1	OS:	Windows (Windows)
Assigned to:	Reggie Burnett	CPU Architecture:	Any

Description:
A mysqldump from a windows machine generated different cases for tablenames for drop table Service... and create table service...
This makes it hard to read into a Linux MySQL installation.

Full list below, also note that it's dumped twice. Unsettling.

$ grep -i serviceorder file.sql
-- Table structure for table 'Serviceorder'
DROP TABLE IF EXISTS Serviceorder;
CREATE TABLE serviceorder (
-- Dumping data for table 'Serviceorder'
-- Table structure for table 'Serviceordertotal'
DROP TABLE IF EXISTS Serviceordertotal;
CREATE TABLE serviceordertotal (
-- Dumping data for table 'Serviceordertotal'
-- Table structure for table 'Serviceorder'
DROP TABLE IF EXISTS Serviceorder;
CREATE TABLE serviceorder (
-- Dumping data for table 'Serviceorder'
-- Table structure for table 'Serviceordertotal'
DROP TABLE IF EXISTS Serviceordertotal;
CREATE TABLE serviceordertotal (
-- Dumping data for table 'Serviceordertotal'

I'm using mysql -f when reading it to ignore this problem.

How to repeat:
Unknown, database not created inhouse.

Suggested fix:
Either pick one source for the name in mysqldump, or change the server so only one version of the name is possible.

Please reverify with 4.0, and 4.1 releases.

We found this behaviour:
If you use "mysqldump db mysql", then everything is dumped fine.
If you use "mysqldump db MYSQL", then it dumps
"DROP TABLE IF EXISTS MYSQL"  and "INSERT INTO MYSQL" with
wrong case.

The solution is to use "SHOW TABLES LIKE 'MYSQL'",
and to use its output instead of the name coming from the command
line argument. This garantees that all queries print table name in
the same case.

Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Additional info:

I can't repeat this on 4.1.

Verified with 4.1.7

How to repeat:
1. start MySQL server with lower_case_table_names=1
2. create database and table, for example:
create database c;
use c;
create table T1(id int);
3. E:\MySQL4_1_7\bin>mysqldump -uroot --opt c T1 > c.sql

Output of the mysqldump:

--
-- Table structure for table `T1`
--

DROP TABLE IF EXISTS `T1`;
CREATE TABLE `t1` (
  `id` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `T1`
--

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

Fixed in 4.1 -> merged to 5.0

Mentioned in 4.1.10 and 5.0.3 change notes.