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?
Best Answer
You have 6 indexes
The
PRIMARY KEY
is in the gen_clust_index (aka Clustered Index). All secondary index entries include a correspondingPRIMARY KEY
entry.I would mysqldump that table and reload it into a test DB server
Next, I would run
CHECKSUM TABLE db1.tableABC;
ormysqlchk
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
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