Bug #19967 W32 case insensitive tablenames cannot be transferred to Linux
Submitted: 20 May 2006 12:23 Modified: 17 Aug 2006 17:09
Reporter: Jan van Santbrink Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S4 (Feature request)
Version:5.0.20 OS:W32
Assigned to: CPU Architecture:Any

[20 May 2006 12:23] Jan van Santbrink
Description:
As MySQL under W32 is case insensitive, tables created as uppercase names are registered as lowercase in MySQL. Using mysqldump the names are dumped as lowercase names, and when transferred to MySQL under Linux, they will no longer be recognized bij the application as this OS is case sensitive

How to repeat:
Create a table in uppercase in MySQL W32 and make a dump using mysqldump

Suggested fix:
Store the names in the correct case in information_schema and use this for mysqldump instead of OS filenames
[21 May 2006 15:06] Valeriy Kravchuk
Please, send exact mysqldump command line that leads you to problems. I do not understand, what files are you writing about.
[23 May 2006 19:20] Jan van Santbrink
Original create statements:

CREATE TABLE M_ENTITY (
 MMD_ID INTEGER NOT NULL,
 MEN_ID INTEGER NOT NULL,
 MEN_NAME VARCHAR(100) NOT NULL,
 TABLE_NAME VARCHAR(32) NULL,
 MNEMONIC VARCHAR(9) NULL,
 MEN_ID_REF INTEGER NULL,
 SCR_ID INTEGER NULL,
 SCR_ID_REF INTEGER NULL,
 SCR_ID_REF2 INTEGER NULL, 
 SEQ_NR NUMERIC(5) NOT NULL,
 SEQ_NR_REF NUMERIC(5) NULL,
 SEQ_NR_REF2 NUMERIC(5) NULL,
 SEQ_NR_REF3 NUMERIC(5) NULL,
 SEQ_NR_REF4 NUMERIC(5) NULL,
 LAST_USER VARCHAR(100) NOT NULL,
 LAST_DATE DATETIME NOT NULL,
 LOCK_ID VARCHAR(100) NULL,
 LOCK_DATE DATETIME NULL, 
 CONSTRAINT M_ENTITY_PK
     PRIMARY KEY (MEN_ID, MMD_ID)) TYPE = InnoDB;

ALTER TABLE M_ENTITY
 ADD CONSTRAINT M_ENTITY_FK1
     FOREIGN KEY (MEN_ID_REF, MMD_ID)
         REFERENCES M_ENTITY (MEN_ID, MMD_ID);

ALTER TABLE M_ENTITY
 ADD CONSTRAINT M_ENTITY_FK2
     FOREIGN KEY (SCR_ID, MMD_ID)
         REFERENCES M_SCRIPT (SCR_ID, MMD_ID);

ALTER TABLE M_ENTITY
 ADD CONSTRAINT M_ENTITY_FK3
     FOREIGN KEY (SCR_ID_REF, MMD_ID)
         REFERENCES M_SCRIPT (SCR_ID, MMD_ID);

ALTER TABLE M_ENTITY
 ADD CONSTRAINT M_ENTITY_FK4
     FOREIGN KEY (SCR_ID_REF2, MMD_ID)
         REFERENCES M_SCRIPT (SCR_ID, MMD_ID);

ALTER TABLE M_ENTITY
 ADD CONSTRAINT M_ENTITY_FK5
     FOREIGN KEY (MMD_ID)
         REFERENCES M_METAMODEL (MMD_ID);

Result from mysqldump
-- MySQL dump 10.10
--
-- Host: localhost    Database: openm
-- ------------------------------------------------------
-- Server version	5.0.20-nt

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `m_entity`
--

DROP TABLE IF EXISTS `m_entity`;
CREATE TABLE `m_entity` (
  `MMD_ID` int(11) NOT NULL,
  `MEN_ID` int(11) NOT NULL,
  `MEN_NAME` varchar(100) collate latin1_general_cs NOT NULL,
  `TABLE_NAME` varchar(32) collate latin1_general_cs default NULL,
  `MNEMONIC` varchar(9) collate latin1_general_cs default NULL,
  `MEN_ID_REF` int(11) default NULL,
  `SCR_ID` int(11) default NULL,
  `SCR_ID_REF` int(11) default NULL,
  `SCR_ID_REF2` int(11) default NULL,
  `SEQ_NR` decimal(5,0) NOT NULL,
  `SEQ_NR_REF` decimal(5,0) default NULL,
  `SEQ_NR_REF2` decimal(5,0) default NULL,
  `SEQ_NR_REF3` decimal(5,0) default NULL,
  `SEQ_NR_REF4` decimal(5,0) default NULL,
  `LAST_USER` varchar(100) collate latin1_general_cs NOT NULL,
  `LAST_DATE` datetime NOT NULL,
  `LOCK_ID` varchar(100) collate latin1_general_cs default NULL,
  `LOCK_DATE` datetime default NULL,
  PRIMARY KEY  (`MEN_ID`,`MMD_ID`),
  KEY `M_ENTITY_I1` (`MEN_ID_REF`,`MMD_ID`),
  KEY `M_ENTITY_I2` (`SCR_ID`,`MMD_ID`),
  KEY `M_ENTITY_I3` (`SCR_ID_REF`,`MMD_ID`),
  KEY `M_ENTITY_I4` (`SCR_ID_REF2`,`MMD_ID`),
  KEY `M_ENTITY_I5` (`MMD_ID`),
  KEY `M_ENTITY_I6` (`MMD_ID`,`MNEMONIC`),
  CONSTRAINT `M_ENTITY_FK1` FOREIGN KEY (`MEN_ID_REF`, `MMD_ID`) REFERENCES `m_entity` (`MEN_ID`, `MMD_ID`),
  CONSTRAINT `M_ENTITY_FK2` FOREIGN KEY (`SCR_ID`, `MMD_ID`) REFERENCES `m_script` (`SCR_ID`, `MMD_ID`),
  CONSTRAINT `M_ENTITY_FK3` FOREIGN KEY (`SCR_ID_REF`, `MMD_ID`) REFERENCES `m_script` (`SCR_ID`, `MMD_ID`),
  CONSTRAINT `M_ENTITY_FK4` FOREIGN KEY (`SCR_ID_REF2`, `MMD_ID`) REFERENCES `m_script` (`SCR_ID`, `MMD_ID`),
  CONSTRAINT `M_ENTITY_FK5` FOREIGN KEY (`MMD_ID`) REFERENCES `m_metamodel` (`MMD_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs;

--
-- Dumping data for table `m_entity`
--

/*!40000 ALTER TABLE `m_entity` DISABLE KEYS */;
LOCK TABLES `m_entity` WRITE;
INSERT INTO `m_entity` VALUES (3,654,'Master','MASTER','MAS',NULL,NULL,NULL,NULL,'6',NULL,NULL,NULL,NULL,'om','2006-05-22 21:21:15',NULL,NULL),(9,654,'Question','QUESTION','QUE',NULL,NULL,NULL,NULL,'13',NULL,NULL,NULL,NULL,'om','2006-05-22 21:31:16',NULL,NULL),(13,654,'Product','PRODUCT','PRD',NULL,NULL,NULL,NULL,'5',NULL,NULL,NULL,NULL,'om','2006-05-22 21:32:27',NULL,NULL),(3,655,'Field','FIELD','FLD',NULL,NULL,NULL,NULL,'4',NULL,NULL,NULL,NULL,'om','2006-05-22 21:21:14',NULL,NULL),(11,655,'Class','CLASS','CLS',NULL,NULL,NULL,NULL,'2',NULL,NULL,NULL,NULL,'om','2006-05-22 21:32:06',NULL,NULL),(13,655,'Customer','CUSTOMER','CUS',NULL,NULL,NULL,NULL,'2',NULL,NULL,NULL,NULL,'om','2006-05-22 21:32:26',NULL,NULL),(11,656,'Operation','OPERATION','OPR',NULL,NULL,NULL,NULL,'4',NULL,NULL,NULL,NULL,'om','2006-05-22 21:32:07',NULL,NULL),(13,656,'Activity','ACTIVITY','ACT',NULL,NULL,NULL,NULL,'1',NULL,NULL,NULL,NULL,'om','2006-05-22 21:32:26',NULL,NULL),(3,657,'Element type','ELEMENT_TYPE','TYP',NULL,NULL,NULL,NULL,'3',NULL,NULL,NULL,NULL,'om','2006-05-22 21:21:14',NULL,NULL),(7,657,'Functionary','FUNCTIONARY','OFF',NULL,NULL,NULL,NULL,'4','1',NULL,NULL,NULL,'om','2006-05-22 21:26:03',NULL,NULL),(13,657,'Process','PROCESS','PRO',NULL,NULL,NULL,NULL,'4',NULL,NULL,NULL,NULL,'om','2006-05-22 21:32:27',NULL,NULL),(11,658,'File','SOURCEFILE','FIL',NULL,NULL,NULL,NULL,'3',NULL,NULL,NULL,NULL,'om','2006-05-22 21:32:06',NULL,NULL),(13,658,'Subcontractor','SUBCONTRACTOR','SCN',NULL,NULL,NULL,NULL,'6',NULL,NULL,NULL,NULL,'om','2006-05-22 21:32:28',NULL,NULL),(3,659,'Department','DEPARTMENT','DEP',NULL,NULL,NULL,NULL,'1',NULL,NULL,NULL,NULL,'om','2006-05-22 21:21:13',NULL,NULL),(9,659,'Department','DEPARTMENT','DEP',NULL,NULL,NULL,NULL,'1',NULL,NULL,NULL,NULL,'om','2006-05-22 21:31:12',NULL,NULL),(3,661,'Item group','TAB_PAGE','ITG',NULL,NULL,NULL,NULL,'5',NULL,NULL,NULL,NULL,'om','2006-05-22 21:21:15',NULL,NULL),(11,661,'Variable','VARIABLE','VAR',NULL,NULL,NULL,NULL,'7',NULL,NULL,NULL,NULL,'om','2006-05-22 21:32:08',NULL,NULL),(9,662...remainder of lines removed...);
UNLOCK TABLES;
/*!40000 ALTER TABLE `m_entity` ENABLE KEYS */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

Although the original creates were in uppercase, the dump creates the table in lowercase. Transferring the dump to Linux and running it would result in lowercase tables names, so the application won't find them as Linux is case sensitive.
[11 Jun 2006 15:05] Valeriy Kravchuk
Please, send the results of:

mysql> show variables like 'lower%';

from the environment (mysql command line client?) where you createdthat tables initially.
[19 Jun 2006 19:33] Jan van Santbrink
+------------------------+-------+
| Variable_name              | Value  |
+------------------------+-------+
| lower_case_file_system  | OFF    |
| lower_case_table_names| 1        |
+------------------------+-------+
2 rows in set (0.00 sec)
[17 Jul 2006 17:09] Valeriy Kravchuk
Now, please, send the results of:

mysql> show variables like 'lower%';

from the environment (mysql command line client), on Linux, where you tried to restore that tables. (lower_case_table_names = 0 by default on Linux.)

Please, read the manual (http://dev.mysql.com/doc/refman/5.0/en/name-case-sensitivity.html):

"
...
Exception: If you are using InnoDB tables, you should set lower_case_table_names to 1 on all platforms to force names to be converted to lowercase.
..."

Doesn't that manual page explain the results you got?
[17 Aug 2006 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".