MySQL table listed twice with different case name

Question

I have a MySQL database table which has been listed twice with case sensitive name.

Both table names are pointing to same table, for example Admin and admin

When I checked information_schema it is listed as below:

mysql> SELECT  TABLE_CATALOG,  TABLE_NAME ,  TABLE_TYPE, ENGINE, CREATE_TIME   
FROM information_schema.tables 
where table_schema='school';

How do I clean up this mess?

Answer 1

Usually MySQL does not allow you to create the table with case-sensitive. It will show the error as :

ERROR 1050 (42S01): Table 'admin' already exists

But MySQL allows you to create a temp table with a existing name because they don't have the same "scope". A temporary table is visible in the session only, and it is dropped at session ending. If you have the same name, MySQL "hide" the original table until you drop your temp table.

I would suggest you to take a backup of the existing data and update the MySql version to 5.7 .

Answer 2

I have the same problem after a pb on AWS RDS (Aurora) upgrade. My legacy database use lower case names. Cluster parameter name : lower_case_table_names

During the upgrade operation you have to know that after setting the cluster parameter name you need absolutely to restart the DB, and only after do upgrade. I tried to set the parameter and upgrade in the same time, and it was not working.

At this time when i dump my db i have twice the data, on reimport it is imported twice so twice the time but not other impact.

If you want to remove from the dump the twiced tables. You can split the dump in per table files, and so reimport only the good ones in a fresh database. A good tool is https://github.com/kedarvj/mysqldumpsplitter/

If it can help