25

I have a a problem whereby my production server runs Unix and my dev server runs MS Windows. The problem is that Windows is not case sensitive and Unix is. So my table set on the production server uses Uppercase table names ie "Customers" and on Windows the table names are in lowercase "customers".

All this is fine until you need to get data from one box to another and your SQL export says insert into "customers" in lowercase, and presto "Unkown table customers". Because the production server is currently on a shared hosting plan i cant change the settings and install the key that ignores case.

So my question, is there a way to get Windows to convert the tables back to the correct case or is there some setting I can include in the export SQL file so that i can upload data without this problem.

Thanks

UPDATE

Here is what I discovered for anybody else having this issue.

If you have already set up your tables running MySQL on Windows adding lower_case_table_names=2 to your my.cnf or my.ini file will not change the case of your tables automatically even if they were originally created using uppercase or mixed case names.

CREATE TABLE "MyTable" will create a new table "mytable" not "MyTable" even when lower_case_table_names=2 is set in your my.cnf file.

To get around this problem use this method

  1. Make a copy of your original table
  2. Drop your original table
  3. Rename your copy table using the correct case.

This is the only way it will work. Hope this helps somebody.

gus
  • 765
  • 3
  • 14
  • 26
  • Do you have the option of changing your development version of MySql to be case sensitive? – Patrick McDonald Jul 23 '09 at 15:05
  • because it runs on Windows, no. This document explains the problem but has no solution for my issue. What i normally have to do is just do a bulk find and replace in the SQL file to change the table names case. http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html – gus Jul 23 '09 at 15:17

3 Answers3

22

Taken from dev.mysql.com:

To avoid data transfer problems arising from lettercase of database or table names, you have two options:

  • Use lower_case_table_names=1 on all systems. The main disadvantage with this is that when you use SHOW TABLES or SHOW DATABASES, you do not see the names in their original lettercase.
  • Use lower_case_table_names=0 on Unix and lower_case_table_names=2 on Windows. This preserves the lettercase of database and table names. The disadvantage of this is that you must ensure that your statements always refer to your database and table names with the correct lettercase on Windows. If you transfer your statements to Unix, where lettercase is significant, they do not work if the lettercase is incorrect.

Exception: If you are using InnoDB tables and you are trying to avoid these data transfer problems, you should set lower_case_table_names to 1 on all platforms to force names to be converted to lowercase.

If you plan to set the lower_case_table_names system variable to 1 on Unix, you must first convert your old database and table names to lowercase before stopping mysqld and restarting it with the new variable setting.

codemonkey
  • 2,661
  • 2
  • 24
  • 34
  • 6
    I don't think you actually have to change anything on the Unix box to use the second set of advice, because `lower_case_table_names` is 0 by default. – chaos Jul 23 '09 at 15:26
  • Oh, and +1; I'm not sure my answer *won't* fix it, but I'm pretty sure this *will*. – chaos Jul 23 '09 at 15:28
  • ... i didn't mean to offend by posting content from dev.mysql... i wasn't aware of this mysql option myself and i'm almost exclusively a database developer. – codemonkey Jul 23 '09 at 15:31
  • I think you have it correct codemonkey. lower_case_table_names=2 may do it. But i wonder will the export tablenames still be in lower case? I will have to test it and let you guys know. – gus Jul 23 '09 at 17:15
  • the reason i missed that is the tiny print. In option 0 they say "case-insensitive file system" and in option 2 they say "on file systems that are not case sensitive!" I missed the "NOT" part DOH! – gus Jul 23 '09 at 17:19
1

There is one easy solution:

Always name your tablenames lowercase.

Wolfgang Blessen
  • 900
  • 10
  • 29
-1

The universal guiding philosophy of Windows with respect to case is "case-insensitive, case stored". That means Windows never intends to discard your case, so it's a little mysterious why your tables on Windows are lower-case.

Apologies if this is a dumb question, but have you tried renaming the tables on the Windows machine so they have the correct case pattern?

chaos
  • 122,029
  • 33
  • 303
  • 309
  • Yes I have but as according to this doc http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html it wont work. – gus Jul 23 '09 at 15:14
  • I don't see anything in that document that implies that. What are you interpreting that way? – chaos Jul 23 '09 at 15:22