MySQL Bugs: #44571: InnoDB Plugin crashes on ADD INDEX

Bug #44571	InnoDB Plugin crashes on ADD INDEX
Submitted:	30 Apr 2009 10:01	Modified:	20 Jun 2010 17:41
Reporter:	Sveta Smirnova	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB Plugin storage engine	Severity:	S1 (Critical)
Version:	5.1 bzr, plugin 1.0.3	OS:	Any
Assigned to:	John Russell	CPU Architecture:	Any

Description:
When doing statement ALTER TABLE foo ADD INDEX bar(baz) InnoDB plugin crashes with following entry in error log:

Version: '5.1.35-debug'  socket: '/tmp/mysql_ssmirnova.sock'  port: 33051  Source distribution
090430 11:46:39 [ERROR] Index foo$bar of dbname/foo has 2 columns unique inside InnoDB, but MySQL is asking statistics for 3 columns. Have you mixed up .frm files from different installations? See http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html

090430 11:46:39 [ERROR] Index foo$bar of dbname/foo has 2 columns unique inside InnoDB, but MySQL is asking statistics for 3 columns. Have you mixed up .frm files from different installations? See http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html

090430 11:46:40 [ERROR] Index foo$bar of dbname/foo has 2 columns unique inside InnoDB, but MySQL is asking statistics for 3 columns. Have you mixed up .frm files from different installations? See http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html

090430 11:46:43 [ERROR] Index foo$bar of dbname/foo has 2 columns unique inside InnoDB, but MySQL is asking statistics for 3 columns. Have you mixed up .frm files from different installations? See http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html

090430 11:46:44 [ERROR] Index foo$bar of dbname/foo has 2 columns unique inside InnoDB, but MySQL is asking statistics for 3 columns. Have you mixed up .frm files from different installations? See http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html

090430 11:46:45 [ERROR] Index foo$bar of dbname/foo has 2 columns unique inside InnoDB, but MySQL is asking statistics for 3 columns. Have you mixed up .frm files from different installations? See http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html

090430 11:46:45  InnoDB: Assertion failure in thread 2990918576 in file dict/dict0dict.c line 1704
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-recovery.html
InnoDB: about forcing recovery.
090430 11:46:45 - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=8384512
read_buffer_size=131072
max_used_connections=1
max_threads=151
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 337727 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0xa496e10
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0xb245c448 thread_stack 0x30000
./libexec/mysqld(my_print_stacktrace+0x26)[0x86d5c69]
./libexec/mysqld(handle_segfault+0x2da)[0x8276aba]
[0x2ce420]
/lib/libc.so.6(abort+0xf8)[0x316678]
./libexec/mysqld[0x84fc78f]
./libexec/mysqld[0x84fc1e5]
./libexec/mysqld[0x858414a]
./libexec/mysqld[0x853fd7a]
./libexec/mysqld[0x853fe8d]
./libexec/mysqld[0x853ffa2]
./libexec/mysqld[0x854c114]
./libexec/mysqld[0x854c20a]
./libexec/mysqld[0x84df4fd]
./libexec/mysqld(_Z17mysql_alter_tableP3THDPcS1_P24st_ha_create_informationP10TABLE_LISTP10Alter_infojP8st_orderb+0x2189)[0x83d6d05]
./libexec/mysqld(_Z21mysql_execute_commandP3THD+0x24c7)[0x828e1bf]
./libexec/mysqld(_Z11mysql_parseP3THDPKcjPS2_+0x230)[0x8295b48]
./libexec/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0xaac)[0x8296784]
./libexec/mysqld(_Z10do_commandP3THD+0x260)[0x82979a2]
./libexec/mysqld(handle_one_connection+0x115)[0x8284349]
/lib/libpthread.so.0[0x45fbd4]
/lib/libc.so.6(__clone+0x5e)[0x3b74fe]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0xa4cb4f8 = ALTER TABLE baz ADD INDEX baz$idx (col), ADD INDEX baz$field (field), ADD INDEX baz$field2 (field2)
thd->thread_id=5
thd->killed=NOT_KILLED
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

How to repeat:
1. Compile 5.1-bzr with innodb-1.0.3 plugin. Use "built-in" method: replace content of storage/innobase directory.
2. Load dump from the private comment

The InnoDB group has not built or tested the InnoDB Plugin with 5.1.35.  All our tests so far have been with prior releases.  This functionality "should work", and does work with 5.1.3x releases for which Plugin 1.0.3 was tested.  It may be that some change in 5.1.35 other than with the Plugin is responsible.

A useful course of action is to test with 5.1.30/31/33 and examine differences between those releases and 5.1.34.

Ken,

I submitted this bug to support and tested it with 5.1.30 + plugin 1.0.3 with the same results.

thanks,

Ryan Huddleston

Thanks Ryan.

Odd thought given this error ... Maybe there is confusion 
between the index and column name.  Can you try with
different names for the index and column?

Here was the original error from the test case I submitted:

090430  0:42:42 [ERROR] Index ruleacts$arg_id of rightnow/ruleacts has 2 columns unique inside InnoDB, but MySQL is asking statistics for 3 columns. Have you mixed up .frm files from different installations? See http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html

090430  0:42:42  InnoDB: Assertion failure in thread 1074665792 in file dict/dict0dict.c line 1704
InnoDB: We intentionally generate a memory trap.

Confirmed bug exists if use MySQL version 5.1.33

Turns out this is actually 2 bugs. I narrowed it down to two small test cases:

Case #1 "mysqld crash"

CREATE TABLE orgs ( salesperson INT) ENGINE=InnoDB;
ALTER TABLE orgs CHANGE salesperson sales_acct_id INT;
ALTER TABLE orgs ADD INDEX orgs$sales_acct_id (sales_acct_id);

Case #2 "index error in error log"

CREATE TABLE `ruleacts` (
  `rule_key` int(11) NOT NULL DEFAULT '0',
  `seq` smallint(6) NOT NULL DEFAULT '0',
  `action` smallint(6) NOT NULL DEFAULT '0',
  `arg_id` smallint(6) DEFAULT NULL,
  `else_ind` TINYINT NOT NULL,
  KEY `ruleacts$arg_id` (`arg_id`)
) ENGINE=InnoDB; 
ALTER TABLE ruleacts ADD UNIQUE ruleacts$r$e$seq$act$a_id (rule_key,else_ind,seq,action,arg_id) ;

The above results in the error:

090504 21:47:03 [ERROR] Index ruleacts$arg_id of rightnow/ruleacts has 2 columns unique inside InnoDB, but MySQL is asking statistics for 3 columns. Have you mixed up .frm files from different installations? See http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html

The comment at [5 May 6:53] by Ryan Huddleston reports two issues.

Case #1 (ut_error assertion in dict_index_find_cols()) is that the MySQL and InnoDB data dictionaries will become out of sync when renaming columns. Columns will be only renamed in the MySQL data dictionary (.frm file), not in the InnoDB system tables. Fixing that might require an API change in MySQL 5.1. I believe that the ALTER TABLE API in MySQL 6.0 does support renaming columns.

Case #2 (error message printed out by ha_innobase::info()) is a different mismatch between the MySQL and InnoDB data dictionaries. table->s->keys==2, but ib_table->indexes.count==3, because the first index is the generated clustered index (there is no PRIMARY KEY or UNIQUE INDEX comprising NOT NULL columns). For some reason, table->keys[0].name=="ruleacts$r$e$seq$act$a_id" but ib_table->indexes.start->indexes.next.name=="ruleacts$arg_id". InnoDB assumes that the secondary indexes are defined in the same order in both MySQL and InnoDB. In case #2, the new index is added before the existing ones in MySQL, but after existing ones in InnoDB. This could be fixed in MySQL, by adding the created indexes last in the .frm file. If UNIQUE indexes must be listed before non-UNIQUE ones in the .frm file, then we must make InnoDB follow the same order.

For case #1 I assume the problem occurs because in 5.1 for certain alter statements only changes to frm are made such as renaming columns and changing defaults. If this is the case one question I have is why is this only occurring with the innodb plugin and not the innodb version built into 5.1? What changed between the two to break this?

thanks,

-Ryan

Ryan, as the built-in InnoDB does not implement "smart ALTER TABLE" aka "fast index creation", MySQL without the InnoDB Plugin should copy the entire table when you do anything to the table. Even if it renamed columns in the .frm file without telling InnoDB, the inconsistency would be removed when creating the index (copying all data to a temporary table with the new schema, dropping the original table, and renaming the temporary table to the original table).

5.1 does have smart alter table for column/index rename, changing default values and others:

http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

So is innodb crashing due to the frm being different than expected or is due to another reason? What is the scope of getting this issue fixed?

Ryan,

<Case #1>

The smart rename doesn't use storage engines.
So, the metadata mismatch between .frm and InnoDB occurs.
(I think it may be implicit bug of MySQL 5.1)

But internal InnoDB doesn't support "smart ALTER TABLE".
So, "ALTER TABLE" always recreates table based on the .frm file only
and is not affected by InnoDB metadata.

Though "smart ALTER TABLE" is affected by the mismatch and crashed.

*
I think the easiest first-aid is... disabling "smart ALTER TABLE" of plugin ....
"innobase_hton->alter_table_flags = NULL"

<Case #2>

This is only index ordering problem, so it may be easy to be fixed.
The case causes completely wrong statistics.

Yasufumi, Thanks for the update. Your workaround to disable fast index creation fixed both problems. I won't get fast index creation but the server will remain stable.

This is related to Bug #45124.

There seems to be a common root cause between Bug #44030, Bug #44571, and Bug #45124: MySQL is renaming columns without telling InnoDB (“smart” ALTER TABLE). Fix: MySQL should either communicate column renames to the storage engine or copy InnoDB tables when columns are renamed.

I have submitted this patch to internal review, along with a test case that crashes MySQL:

Index: dict/dict0dict.c
===================================================================
--- dict/dict0dict.c	(revision 5392)
+++ dict/dict0dict.c	(working copy)
@@ -1693,6 +1693,11 @@ dict_index_find_cols(
 		}
 
 		/* It is an error not to find a matching column. */
+		fputs("InnoDB: Error: no matching column for ", stderr);
+		ut_print_name(stderr, NULL, FALSE, field->name);
+		fputs(" in ", stderr);
+		dict_index_name_print(stderr, NULL, index);
+		fputs("!\n", stderr);
 		ut_error;
 
 found:

There is nothing else that can be done inside InnoDB, besides disabling fast index creation. The root cause is that the ALTER TABLE ... CHANGE will make the MySQL and InnoDB data dictionaries out of sync. That is something that has to be addressed within the Storage Engine API.

Thanks Marko. So with this change will the alter result in an error (instead of crash)? Would it be possible if it throws an error to fall back to rebuilding the whole table (basically turn off fast_index creation for that one statement?

The fix is in 1.0.4. Marko - please answer Ryan's last question. Thanks!

Ryan, InnoDB Plugin 1.0.4 will still crash on your test case:

DROP TABLE IF EXISTS orgs;
CREATE TABLE orgs ( salesperson INT) ENGINE=InnoDB;
ALTER TABLE orgs CHANGE salesperson sales_acct_id INT;
ALTER TABLE orgs ADD INDEX orgs$sales_acct_id (sales_acct_id);

All that it does differently from 1.0.3 is that it prints additional information before the ut_error.

You are right that we could probably do better: fall back to the old implementation in this case. I tried that, but failed. If ha_innobase::add_index() returns HA_ERR_WRONG_COMMAND (the return value defined in the virtual method handler::add_index()), MySQL will not fall back to table copying, but display an error message:

ERROR 1031 (HY000) at line 4: Table storage engine for 'orgs' doesn't have this option

It would be better to return HA_ERR_CRASHED instead of crashing the entire server, like this:

ERROR 1034 (HY000) at line 4: Incorrect key file for table 'orgs'; try to repair it

InnoDB Plugin 1.0.5 will return an error instead of crashing:

Incorrect key file for table 'bug44571'; try to repair it

This bug can be worked around by creating another table and copying the original table to it. In the created table, MySQL and InnoDB will agree on the column names until someone renames columns again.

Pushed into 5.1.41 (revid:joro@sun.com-20091014143611-cphb0enjlx6lpat1) (version source revid:satya.bn@sun.com-20091008112837-dxbi1c2rjngpktug) (merge vers: 5.1.40) (pib:13)

Noted in 5.1.41 changelog.

With InnoDB Plugin, renaming a table column and then creating an
index on the renamed column caused a server crash to to the .frm file
and the InnoDB data directory going out of sync. Now InnoDB Plugin
1.0.5 returns an error instead: ERROR 1034 (HY000): Incorrect key
file for table 'tbl_name'; try to repair it. To work around the
problem, create another table with the same structure and copy the
original table to it. 

Setting report to NDI pending push to 5.5.x+.

Pushed into 6.0.14-alpha (revid:alik@sun.com-20091022063126-l0qzirh9xyhp0bpc) (version source revid:alik@sun.com-20091019135554-s1pvptt6i750lfhv) (merge vers: 6.0.14-alpha) (pib:13)

Pushed into 5.5.0-beta (revid:alik@sun.com-20091022060553-znkmxm0g0gm6ckvw) (version source revid:alik@sun.com-20091019131022-2o2ymjfjjoraq833) (merge vers: 5.5.0-beta) (pib:13)

Noted in 5.5.0, 6.0.14 changelogs.

Pushed into 5.1.41-ndb-7.1.0 (revid:jonas@mysql.com-20091218102229-64tk47xonu3dv6r6) (version source revid:jonas@mysql.com-20091218095730-26gwjidfsdw45dto) (merge vers: 5.1.41-ndb-7.1.0) (pib:15)

Pushed into 5.1.41-ndb-6.2.19 (revid:jonas@mysql.com-20091218100224-vtzr0fahhsuhjsmt) (version source revid:jonas@mysql.com-20091217101452-qwzyaig50w74xmye) (merge vers: 5.1.41-ndb-6.2.19) (pib:15)

Pushed into 5.1.41-ndb-6.3.31 (revid:jonas@mysql.com-20091218100616-75d9tek96o6ob6k0) (version source revid:jonas@mysql.com-20091217154335-290no45qdins5bwo) (merge vers: 5.1.41-ndb-6.3.31) (pib:15)

Pushed into 5.1.41-ndb-7.0.11 (revid:jonas@mysql.com-20091218101303-ga32mrnr15jsa606) (version source revid:jonas@mysql.com-20091218064304-ezreonykd9f4kelk) (merge vers: 5.1.41-ndb-7.0.11) (pib:15)

To clarify/summarize:

This bug report shows 2 bugs:

------------- Bug #1 ------------- 
Signal 6 Server crash. This was resolved/addressed in this bug report. 
 
Fixed in releases: 5.1.41 (which is using InnoDB Plugin 1.0.5), 5.5.0, 6.0.14.

Note: MySQL versions before 5.1.41 with InnoDB Plugin versions 1.0.3 or 1.0.4 may generate the signal 6 crash.

Summary of bug #1: 

 With InnoDB Plugin, renaming a table column and then creating an index on the renamed column 
 caused a server crash to to the .frm file and the InnoDB data directory going out of sync.

------------- Bug #2 ------------- 
Error in error log: 'Index foo$bar of dbname/foo has 2 columns unique inside InnoDB, but MySQL is asking statistics for 3 columns.'

This will be addressed in: bug #47622. 

Summary of bug #2:

 Error is caused by mis-ordering of indexes. 

This error can be observed as:

 <Using plugin version 1.0.3>

  [ERROR] Index foo$bar of dbname/foo has 2 columns unique inside InnoDB, 
  but MySQL is asking statistics for 3 columns. Have you mixed up .frm files from different
  installations? See http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html

 <Using plugin version 1.0.4> 

  InnoDB: Error: no matching column for fieldname in indexname
  + The same 2/3 error as above

 <Using plugin version 1.0.5> 

  Incorrect key file for table 'tablename'; try to repair it
  + The same 2/3 error as above

Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.

Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.

Pushed into 5.5.5-m3 (revid:alik@sun.com-20100615080459-smuswd9ooeywcxuc) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (merge vers: 5.1.47) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100615080558-cw01bzdqr1bdmmec) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (pib:16)

Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Added text to change log:

A mismatch between index information maintained within the .frm files and the
corresponding information in the InnoDB system tablespace could produce this error: ...(2/3 error text)...

I fix this bug in mysql 5.7.12，how to fix it?

I hit this bug in mysql 5.7.12, how to fix it.

zhongyu ren, upgrade. Your version is very old and likely a security liability. This is not professional advice.