2

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

enter image description here

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';

enter image description here

How do I clean up this mess?

halfer
  • 19,824
  • 17
  • 99
  • 186
Dhaval Patel
  • 608
  • 7
  • 15
  • SELECT DISTINCT ABLE_CATALOG, TABLE_NAME , TABLE_TYPE, ENGINE, CREATE_TIME FROM information_schema.tables where table_schema='school'; – Travis Aug 12 '19 at 15:56
  • I suspect you created the two tables, then noticed that you had the case sensitive table option, then changed it to case-insensitive. – Barmar Aug 12 '19 at 16:29
  • You probably need to remove the extra table using the filesystem. Someone on [dba.se] may be able to provide detailed instructions. – Barmar Aug 12 '19 at 16:31
  • Yes I had change mode to have table case-insensitive ( I am using AWS RDS). However both the table are pointing to same records set. So I can not drop table. Is is something like Alice ? – Dhaval Patel Aug 13 '19 at 06:34
  • Is there any way to remove duplicate row from information_schema.tables ?? – Dhaval Patel Aug 13 '19 at 16:15

1 Answers1

-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 .

Rajat Dabade
  • 314
  • 3
  • 6
  • Not true. On Unix table names are case sensitive by default. https://stackoverflow.com/questions/6134006/are-table-names-in-mysql-case-sensitive – Barmar Aug 12 '19 at 16:35