Table contains 4 indexes inside InnoDB, which is different from the number of indexes 3 defined in MySQL

Question

The database is an old version of MariaDB, the equivalent of MySQL 5.5. The table contains those columns

id
auth
old_auth
device_id_fk

This error is showing

Table contains 4 indexes inside InnoDB, which is different from the number of indexes 3 defined in MySQL

The results of SHOW CREATE TABLE

CREATE TABLE `wtb_device_auths` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `device_id_fk` int(11) NOT NULL,

  `auth` varchar(255) COLLATE utf8_unicode_ci NOT NULL,

  `old_auth` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,

  PRIMARY KEY (`id`),

  KEY `auth_index` (`auth`),

  KEY `auth1_index` (`device_id_fk`),

  KEY `auth2_index` (`old_auth`)

) ENGINE=InnoDB AUTO_INCREMENT=627642 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

How to fix it?

Answer 1

The error is related to the difference between Innodb definition and the frm file definition.

It probably occurred because of a crash during an ALTER TABLE or similar statement at some point.

To restore the consistency an ALTER TABLE wtb_device_auths FORCE will recreate the table (while exclusively locking it). This recreation should make both the innodb and frm definitions consistent.