Somehow in our Mysql database (Engine version: 5.7.22), two tables with the same name but with the different cases are created. Now I am not able to do any select/drop/rename operation on any of those tables.
Further, I checked in information schema, there exist multiple entries for the same.
I have gone through StackOverflow to find the solution but unable to do so.
How can I drop one of these tables, any help will be much appreciated.
The amp_poc schema has case sensitive collation allowing you to have two with the same name but different characters cases.
To see the default character set and collation for a given database, use these queries: USE db_name; SELECT @@character_set_database, @@collation_database
Alternatively, to display the values without changing the default database
SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA
You can drop either one just like any other table just be sure you have the character casing correct for the one you intended to delete! drop table or drop TABLE
Try restarting your server with
lower_case_table_names = 0
then drop your table using the exact casing of the name then switch the server back to
lower_case_table_names = 1
to prevent similar issues in the future. See https://dev.mysql.com/doc/refman/8.0/en/identifier-case-sensitivity.html
