CRITICAL BUG in 4.1: innodb_file_per_table can corrupt secondary indexes

Hi!

http://bugs.mysql.com/bug.php?id=7496

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.

"
heikki@hundin:~/mysql-4.1/sql> ./mysqld
041222 17:14:36 InnoDB: Started; log sequence number 0 128463738
041222 17:14:36 [Warning] mysql.user table is not updated to new password
format
; Disabling new password usage until mysql_fix_privilege_tables is run
../mysqld: ready for connections.
Version: '4.1.9-debug-log' socket: '/home/heikki/bugsocket' port: 3307
Source
distribution
Error: index `idx_symbole` of table `test/alex1` contains 185811 entries,
should
be 185956
Error: index `idx_symbole3` of table `test/alex1` contains 184246 entries,
shoul
d be 185956
"

The corruption does not occur after a mysqld crash. It only follows from 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.

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.

This is the worst InnoDB corruption bug in 3 years. I apologize that it
slipped through tests.

Heikki


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org