MySQL Bugs: #7496: innodb_file_per_table corrupts secondary indexes

Bug #7496	innodb_file_per_table corrupts secondary indexes
Submitted:	22 Dec 2004 21:50	Modified:	21 Jan 2005 16:34
Reporter:	Heikki Tuuri	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	4.1	OS:	Any (All)
Assigned to:	Heikki Tuuri	CPU Architecture:	Any

Description:
Hi!

A critical bug found: if one uses the 4.1 my.cnf option

innodb_file_per_table

to create tables, and some of the secondary index records are inserted to the InnoDB 'insert buffer', then after a normal mysqld shutdown InnoDB loses all those secondary index records! CHECK TABLE will print to the mysqld .err log that there are less records in the secondary index than in the clustered index.

The corruption does not occur after a mysqld crash. It only follows a normal shutdown.

Workarounds:

1) Only 'shut down' mysqld with killall -9 mysqld :).

or

2) Before shutdown, let the server be idle, so that SHOW INNODB STATUS shows the 'main thread waiting for server activity'. Then a normal shutdown is safe.

This is the worst InnoDB corruption bug in 3 years.

Regards,

Heikki

How to repeat:
Do heavy random inserts to the secondary indexes of a big table. Then some of the inserts end up in the insert buffer. Shut down mysqld quickly. At the next startup the table is corrupt.

Suggested fix:
Will be fixed in 4.1.9. We let InnoDB do a 'crash-like' startup always.

Fixed in the current 4.1 bk tree.
--Heikki

Fixed in 4.1.9.

Unfortunately, we introduced a bug in Windows, but that has an easy workaround:

innodb_flush_method=unbuffered

--Heikki