Mysql – innodb , thesql Indexes difference when adding index

innodbMySQL

When I am altering one of my MySQL table to add an index I am getting below error in error log.

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

  1. What would be the exact root cause of this issue. Is the table not closed properly? I have read altering the table may remove the extra index which is causing the issue and we may create it later. But I want to know the exact reason why this is happening.

  2. I am assuming inside innodb 4 indexes means those indexes definition updated in table .frm file and .ibd file along with ibdata1. But what does it mean by "inside mysql"?

Edit (add CREATE)

CREATE TABLE b (
    p datetime DEFAULT NULL, 
    q datetime DEFAULT NULL, 
    r bigint(20) NOT NULL, 
    s varchar(25) DEFAULT NULL, 
    t varchar(50) DEFAULT NULL, 
    u datetime DEFAULT NULL, 
    v varchar(50) DEFAULT NULL, 
    w varchar(20) DEFAULT NULL, 
    x longtext, 
    y longtext, 
    z bigint(20) DEFAULT NULL, 
    k bigint(20) DEFAULT NULL, 
    PRIMARY KEY (r), 
    KEY e (z), 
    KEY f (u), 
    KEY g (s)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Best Answer

Try a no-op ALTER:

ALTER TABLE tablename ENGINE=InnoDB;

(Suggested in this forum.)

(Edits)

I do not know why the problem happened.

Here is another 'fix' that might work:

CREATE TABLE b_new LIKE b;
INSERT INTO b_new
    SELECT * FROM b;
RENAME TABLE b TO b_old, b_new TO b;
DROP TABLE b_old;
Related Question