InnoDB, What would cause: "db1/tableABC contains 6 indexes inside InnoDB, which is different from the number of indexes 5 defined in MySQL"

Question

MySQL 5.5.24 . We have InnoDB tables that grew almost by double (from 50GB to 100GB - file-per-table enabled) in a few weeks due to a project we are working on. The tables were freshly dumped and restored just before that. Suddenly I noticed this error in error.log a few weeks ago, it has not reocurred:

"Error "db1/tableABC contains 6 indexes inside InnoDB, which is different from the number of indexes 5 defined in MySQL"

Here is the 'show create table';

*************************** 1. row ***************************
       Table: tableABC
Create Table: CREATE TABLE `tableABC` (
  `UID` bigint(20) NOT NULL AUTO_INCREMENT,
  `VERSION` int(11) NOT NULL DEFAULT '0',
  `IMPORT_PAGE_NUMBER` int(11) NOT NULL DEFAULT '0',
  `MODEL_INTEGRATION_IMPORT_INSTANCE_UID` bigint(20) NOT NULL,
  `MODEL_INTEGRATION_IMPORT_FIELD_MAPPING_UID` bigint(20) DEFAULT NULL,
  `MODEL_INTEGRATION_IMPORT_FIELD_UID` bigint(20) DEFAULT NULL,
  `FIELD_TYPE` int(11) NOT NULL,
  `TICKER_CODE` varchar(12) NOT NULL,
  `FIELD_CODE` varchar(255) NOT NULL,
  `ACCOUNT_NAME` varchar(255) DEFAULT NULL,
  `IMPORT_VALUE` longtext NOT NULL,
  `VALUE_DATE` datetime DEFAULT NULL,
  `IS_HISTORIC` tinyint(4) NOT NULL DEFAULT '1',
  `PERIOD_CODE` varchar(4) DEFAULT NULL,
  `YEAR` char(4) DEFAULT NULL,
  `CONSOLIDATION_PRINCIPLE` varchar(255) DEFAULT 'NA',
  `UNIT_MULTIPLIER` double DEFAULT NULL,
  `CURRENCY_ISO` varchar(3) DEFAULT NULL,
  `LINE_NUMBER` bigint(20) DEFAULT '0',
  `COLUMN_NUMBER` bigint(20) DEFAULT '0',
  `PERIOD_START_DATE` datetime DEFAULT NULL,
  `PERIOD_END_DATE` datetime DEFAULT NULL,
  `PARSE_ERROR` varchar(255) DEFAULT NULL,
  `VALIDATE_ERROR` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`UID`),
  KEY `FK_miii_data_miif_mapping` (`MODEL_INTEGRATION_IMPORT_FIELD_MAPPING_UID`),
  KEY `FK_miii_data_miif` (`MODEL_INTEGRATION_IMPORT_FIELD_UID`),
  KEY `FK_MIIIData_MIIInstance` (`MODEL_INTEGRATION_IMPORT_INSTANCE_UID`,`IMPORT_PAGE_NUMBER`),
  KEY `i_ticker` (`MODEL_INTEGRATION_IMPORT_INSTANCE_UID`,`TICKER_CODE`),
  KEY `TICKER_CODE_INDEX` (`TICKER_CODE`),
  CONSTRAINT `FK_MIIInstanceData_MIIInstance` FOREIGN KEY (`MODEL_INTEGRATION_IMPORT_INSTANCE_UID`) REFERENCES `ModelIntegrationImportInstance` (`UID`),
  CONSTRAINT `FK_miii_data_miif` FOREIGN KEY (`MODEL_INTEGRATION_IMPORT_FIELD_UID`) REFERENCES `ModelIntegrationField` (`UID`),
  CONSTRAINT `FK_miii_data_miif_mapping` FOREIGN KEY (`MODEL_INTEGRATION_IMPORT_FIELD_MAPPING_UID`) REFERENCES `ModelIntegrationImportFieldMapping` (`UID`)
) ENGINE=InnoDB AUTO_INCREMENT=83483107 DEFAULT CHARSET=latin1

Any ideas? Do I need to rebuild the indexes? Would running mysqlcheck -a (analyze table) or mysqlcheck -o (optimize/alter table on innodb) or both do this?

Answer 1

You have 6 indexes

  PRIMARY KEY (`UID`),
  KEY `FK_miii_data_miif_mapping` (`MODEL_INTEGRATION_IMPORT_FIELD_MAPPING_UID`),
  KEY `FK_miii_data_miif` (`MODEL_INTEGRATION_IMPORT_FIELD_UID`),
  KEY `FK_MIIIData_MIIInstance` (`MODEL_INTEGRATION_IMPORT_INSTANCE_UID`,`IMPORT_PAGE_NUMBER`),
  KEY `i_ticker` (`MODEL_INTEGRATION_IMPORT_INSTANCE_UID`,`TICKER_CODE`),
  KEY `TICKER_CODE_INDEX` (`TICKER_CODE`),

The PRIMARY KEY is in the gen_clust_index (aka Clustered Index). All secondary index entries include a corresponding PRIMARY KEY entry.

I would mysqldump that table and reload it into a test DB server

Next, I would run CHECKSUM TABLE db1.tableABC; or mysqlchk against db1.tableABC in production and the test DB.

If the checksum values match, you should be OK.

If they do not match or you are not sure, run this on the production server

ALTER TABLE db1.tableABC ENGINE=InnoDB;

This will rebuild the table and its indexes.

If that error ever materializes after this, there may be a data dictionary problem inside ibdata1. Your final solution would be to dump all databases, shutdown mysql, delete ibdata1, ib_logfile0, ib_logfile1, start mysql, reload all data.

I posted this InnoDB Cleanup Process in StackOverflow back on Oct 29, 2010