Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Thank you for accepting my answer !!! +1 !!! –  RolandoMySQLDBA Nov 30 '12 at 22:23

1 Answer 1

up vote 1 down vote accepted

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.