0

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?

|improve this question|||||
  • 1
    Delete your foto. Execute SHOW CREATE TABLE tablename; on both servers, copy returned table scripts and paste them into your question. And - where is your question? – Akina Feb 20 at 9:29
  • @Akina Okay, as for where is my question, I want to fix the error Table contains 4 indexes inside InnoDB, which is different from the number of indexes 3 defined in MySQL – Lynob Feb 20 at 9:31
  • @Akina There's only 1 server – Lynob Feb 20 at 9:33
  • This error is showing I do NOT see any error... where is it? the number of indexes 3 defined in MySQL Where this data is taken from? – Akina Feb 20 at 9:35
  • @Akina From mariadb error log apparently, I don't have access to the server, it's managed by the client offline (no internet access) – Lynob Feb 20 at 9:37
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.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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