MySQL Bugs: #7496: innodb_file_per_table corrupts secondary indexes

Bug #7496	innodb_file_per_table corrupts secondary indexes
Submitted:	22 Dec 2004 22:50	Modified:	21 Jan 2005 17:34
Reporter:	Heikki Tuuri
Status:	Closed
Category:	Server: InnoDB	Severity:	S1 (Critical)
Version:	4.1	OS:	Any (All)
Assigned to:	Heikki Tuuri	Target Version:

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