MySQL Bugs: #6633: InnoDB crashes when converting table from MyISAM to InnoDB

Bug #6633	InnoDB crashes when converting table from MyISAM to InnoDB
Submitted:	15 Nov 2004 11:41	Modified:	30 Nov 2004 16:24
Reporter:	jocelyn fournier (Silver Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	4.1.8 bktree	OS:	Linux (Linux)
Assigned to:	Marko Mäkelä	CPU Architecture:	Any

Description:
Hi,

When trying to convert a MyISAM table to InnoDB, InnoDB crashes :

InnoDB: Assertion failure in thread 1785966 in file lock0lock.c line 407
InnoDB: Failing assertion: lock->type_mode == (LOCK_TABLE | LOCK_IX)

I encountered this crash when trying to create a bug report for this one : 
initially my table was in InnoDB format and when trying to change one column, InnoDB crashes :

ALTER TABLE inscrit2 CHANGE password_key password_key varchar(20) binary NULL;

InnoDB: Assertion failure in thread 1097796 in file ha_innodb.cc line 2327
InnoDB: Failing assertion: prebuilt->trx->mysql_n_tables_locked == 2

Don't know if it's the same bug.

Thanks,
  Jocelyn

How to repeat:
Download 

ftp://support.mysql.com/pub/mysql/secret/innoDB_crash.tar.gz

Then execute :

ALTER TABLE inscrit2 ENGINE=InnoDB;

For bug n°2, when table is in InnoDB format :

ALTER TABLE inscrit2 CHANGE password_key password_key varchar(20) binary NULL;

Hi Jocelyn,
Many thanks for your report. The bugs are most likely caused by my patch to ALTER TABLE, so that it would commit the inserts to the temporary table after every 10,000 rows. The logic only accounts for the case when the table already is in InnoDB format, and I didn't test the conversion from MyISAM to InnoDB. My bad.

Hi Marko,

So the second bug is probably not related to the first one ? (since for the second crash, the table was already in InnoDB format).

Thanks !
  Jocelyn

Marko,

only put

fprintf(stderr, ...)

instead of ut_a(...), if the error is not absolutely fatal.

We cannot test all types of ALTER TABLE, and what is MySQL's lock behavior in them.

Regards,

Heikki

Jocelyn,
Both assertion failures are related to my ALTER TABLE modification. I've now committed a fix to the assertion failure in ha_innodb.cc. Next, I'll try to reproduce the second failure with your test case.

Jocelyn,
I was able to reproduce the assertion failure with your test data in lock_get_ix_table(), even after my fix to ha_innodb.cc applied. I'll make another patch tomorrow.

Jocelyn,
I'm sorry for the delay; I had to work on other things.
I've now pushed a fix that corrects both problems exposed by your test case. ALTER TABLE from other engine to InnoDB caused an assertion failure. Also, ALTER TABLE or OPTIMIZE TABLE will no longer attempt to commit after every 10,000 rows if the table is originally in InnoDB format, and there are other locks than a single IX or IS lock on it. This means that the intermediate commits are disabled for tables containing an auto_increment field.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

I made a better fix that allows intermediate commits for tables containing auto_increment columns, and pushed it to the 4.1 tree.

See also Bug #42152.