MySQL drop index bug

Problem description:

When drop index index_name on table_name, if table_name is in upper case, it will cause access to the information_schema.statistics table in the future.

Environment: MySQL 5.1.46 Win32 version.

Example:

Use test;

Create Table if not exists tci_inst

(

Fciid
Int unsigned,

Fcidver
Int unsigned,

Fciname
Varchar (256)

);

Create index ind_tci_fciid on tci_inst (fciid, fcidver );

Select * From information_schema.statistics where table_name = 'tci _ inst' and index_name = 'ind _ tci_fciid ';

Two records should be output. Two records are actually output, correct!

Select count (*) from information_schema.statistics where table_name = 'tci _ inst' and index_name = 'ind _ tci_fciid ';

2 should be output. Actual output 2, correct!
Drop index ind_tci_fciid on tci_inst; (Note that tci_inst is capitalized here)

Select * From information_schema.statistics where table_name = 'tci _ inst' and index_name = 'ind _ tci_fciid ';

0 records should be output. Correct!

Select count (*) from information_schema.statistics where table_name = 'tci _ inst' and index_name = 'ind _ tci_fciid ';

0 should be output, but 2 is output! Error !!!

Select table_name, index_nameFrom information_schema.statistics where table_name = 'tci _ inst' and index_name = 'ind _ tci_fciid ';

Two records can be output! Error !!!

If drop index ind_tci_fciid on tci_inst; (change to lower case here)

Then everything is normal.

Source:

I have studied the problem of drop index if exists today and found this problem when studying this problem.

Because MySQL does not support drop index if exists, it is troublesome to write the installation/upgrade script,

Therefore, a stored procedure is written for judgment and deletion.

Determine whether the index exists by judging the table_name and index_name of information_schema.statistics.

If yes, delete the index.

However, since all tables are created in uppercase, the stored procedure is also called with an uppercase table name,

The first execution is correct. When the second execution is performed, the system should judge that the table index does not exist. However, due to this bug, the system misjudges the existence.

Correct only after restart MySQL

Improvement:

During the study of MySQL, maybe the table names should all be set to lower case?

In MySQL programming, tables are all named in lower case?