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

[22 Dec 2004 21:50] Heikki Tuuri
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.
[29 Dec 2004 21:16] Heikki Tuuri
Fixed in the current 4.1 bk tree.
--Heikki
[21 Jan 2005 16:34] Heikki Tuuri
Fixed in 4.1.9.

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

innodb_flush_method=unbuffered

--Heikki