Some tables not found after moving MySQL database from Linux to Windows

Question

I had a MySQL database which I was working with from Linux. Some of the schema/table names were in mixed case. I then needed to work with the same database on Windows, but because of the enormous size, doing mysqldump simply did not work (I know it's usually the best option, but I spent many days trying to make it work, to no avail). So, I copied the data files to an external drive and then set the Windows machine to look there when starting the MySQL server.

This worked great, except that I hadn't realized the mixed casing would be a problem on Windows. I can see the schemas/tables in the MySQL Workbench Navigator, but cannot perform any operations on them. If I try, I get error code 1146: Table 'x' doesn't exist.

The original database I was using on Linux, unfortunately, has been corrupted since then, so I am unable to go back and change the case of the schemas/tables there. I tried having the Linux machine read the copied data with the goal of changing the mixed case names, but it is no longer able to read the copied data since it's been modified on the Windows machine.

Is there some way I can enforce case sensitivity on Windows? Or is there some way I can change the cases of the schemas and table names without using MySQL (since it thinks those tables don't exist)? Any other solutions? I really need the data in the mixed case schemas/tables but currently see no way of getting it out.

Answer 1

You can force case sensitivity with lower_case_table_names

Add this to your my.ini

[mysqld]
lower_case_table_names=0

Since lower_case_table_names is not dynamic, you must then restart mysql as Administrator with

C:\> net stop mysql
C:\> net start mysql

The MySQL Documentation says the following

You should not set this variable to 0 if you are running MySQL on a system that has case-insensitive file names (such as Windows or OS X). If you set this variable to 0 on such a system and access MyISAM tablenames using different lettercases, index corruption may result. On Windows the default value is 1. On OS X, the default value is 2.

Please read my 2.5 year old post Is it okay to have different lower_case_table_names value on master and slave? for more clarification.

Please backup the data folder before doing any of this

You might have more issues if the tablename mentioned in constraints are case sensitive. You will have to post that as a separate question because it requires a deeper dive.