mysql lower case windows upper case unix linux problem – solved

I had a peculiar problem recently, the phpmyadmin in my windows 7 had all tables in lower cases. The one on my webserver on linux had all upper cases.

When exporting tables to the web server I always had the case mismatch problem and my scripts wouldn’t run with the correct data. I had to always export in my windows 7 machine in text format, open each table and change the table names to upper case. As you can imagine, with a lot of tables this can be daunting, plus human typo errors. When you have to do this regularly this becomes terrible. Then some tables are huge and saving and opening them to edit in text editor is a pain.

So I went around looking for the solution, only to find everyone having the same problem. One guy had it solved but it wasn’t explained properly, so I tried it half heartedly and gave up.

After some thinking, I came across the solution. It may not work for you but it did for me.

My specs – local:

  • Windows 7
  • Apache 2.2.11
  • PHP 5.3
  • mySQL 5.1.36

I set about changing the table names in my local Windows machine to upper case, that would solve my problem. I can then export directly to gzip and upload!

Steps:

  1. Go to your my.ini file of your MySQLand add this line at the end
    lower_case_table_names=2
    This tells MySQL to preserve cases for table names. This file should be located in your bin directory of your MySQL install folder
  2. Restart your MySQL
  3. Now simply go to phpmyadmin, rename the table to any temp name, then rename the temp name back to the original with upper case.

Thats it!

Best of luck!

This entry was posted in Database, MySQL and tagged . Bookmark the permalink.

Comments are closed.