MySQL5.5 number of indexes in the INNODB engine the amount defined in MySQL with the number of indexes is an inconsistency problem

This error was found when viewing the MySQL error log, as follows:

160322 21:42:59 [ERROR] Table Baby/baby_order contains indexes inside InnoDB, which is different from the number of IND EXEs defined in the My

Sql

It probably means that the index number of the table Baby_order is inconsistent with the number of indexes defined in MySQL within the InnoDB engine

Why is there such a mistake?

Refer to the explanation of this article

https://www.percona.com/blog/2011/11/29/innodb-vs-mysql-index-counts/

I had a customer recently who a few strange errors in their mysqld.err log:

[ERROR] Table Database_name/table_name contains 8 indexes inside InnoDB, which is different from the number of indexes 7 defined I n the MySQL

1

[ERROR] Table Database_name/table_name contains 8 indexes inside InnoDB, which is different from the number of indexes 7 defined I n the MySQL

This customer is running Percona Server 5.1 and they got this error on the tables during a maintenance window when they w Ere adding indexes to the same tables.

We had a suspicion that it had something to does with Fast index creation in Innodb, and so it had been corrected when the ALTER TABLE completed because the errors had not recurred.

Reproducing the error on a test system are simple:

The way to reproduce the error:

Create an INNODB table

Make a copy of the. frm file

Do a ALTER TABLE to add an index

Then copy the old. frm file back to place

Re-open the table (might need a FLUSH TABLES or mysqld restart here)

From my testing, I saw that the error is only happened when the table is opened and not on every table access. So, it is a possibility that the indexes were out of sync and we weren ' t seeing new errors in the log simply because the Table Hadn ' t been re-opened.

But, before getting too crazy, how can we verify the problem still exists?  We need A-compare the output of SHOW CREATE TABLE to what Innodb thinks. What Innodb thinks are in the Innodb Data dictionary.

The first recommendation I got is to simply use the INFORMATION_SCHEMA. Innodb_sys_indexes table, which exists in Percona Server 5.1, but doesn ' t appear in MySQL until 5.6 (if the manual was to B  e trusted). I ' d probably consider this on a newer version of Percona Server or MysqL 5.6.

Another person (I-M looking at you, Baron) is adverse to trusting innodb_sys_indexes from some bad experiences with it, a nd suggested the Innodb Table monitor instead, see my next post for what that turned out, but this basically would regurgita Te the entire Innodb Data dictionary to the mysqld error log file.

You can force the table to close by flush table1 table2 and then reopen the table

If I had to does it over again, I think I ' d simply try Doing:flush TABLES table1, table2;  To force the tables to close and is reopened and simply see if the error message is comes back. That's might something of a performance impact, but it seems is the most stable.

In this case, it is turned out that the indexes were not-out of sync, so I didn ' t has to does anything to fix it.

However if I did has to fix it, I found on my test table that the extra index in INNODB could is removed by doing:

To fix this problem, you can execute the following statement to change the table engine to InnoDB

ALTER TABLE table_name Engine=innodb;

This, for course, rebuilds the whole table based on the. frm table definition and removes the existing index in Innodb, WHI  CH might not being desirable, but at least you can re-add it later. However, it's not the greatest thing to do on a live production database master If you can help it.

Another solution might be-to-figure out-what index is missing via the Innodb data dictionary (more on this in a minute), Create a separate table identical to the existing. FRM, add this index to it, and copy the new. Frm back over the original  . Kind of Scary.

My advice is to ensure the error still exists before trying to fix it.

This article is from "Shadow Knight" blog, please make sure to keep this source http://andylhz2009.blog.51cto.com/728703/1851161

MySQL5.5 number of indexes in the INNODB engine the amount defined in MySQL with the number of indexes is an inconsistency problem