3

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
  • 1
    Run SHOW CREATE TABLE tablename; and add the output in the question. Probably related to this bug: bugs.mysql.com/bug.php?id=70654 – ypercubeᵀᴹ Jun 12 '15 at 12:56
  • Also add the exact version of mysql you use. An upgrade will probably solve the issue, if it's this bug. – ypercubeᵀᴹ Jun 12 '15 at 12:58
  • @ypercubeᵀᴹ:i am assuming 'innodb' means 'innodb data dictionary' in this context .Any idea whats the meaning of 'mysql' here.. – kasi Mar 3 '16 at 12:16
  • I meant you should add the version of MySQL version you run. You can find it by running select @@version ; Also what is the exact ALTER statement that produces this error? – ypercubeᵀᴹ Mar 3 '16 at 12:32
  • I am using mysql 5.5.40 community version. 'alter table table_name add index index_name(column_name)' is the statement i have used. – kasi Mar 31 '16 at 12:10
1

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;
|improve this answer|||||
  • yes i tried..but the real question is why its happening..and what is mysql in this context. – kasi Jun 15 '15 at 4:57

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.