MySQL Bugs: #59271: MySQL/innodb crash with RELEASE build but not DEBUG build

Bug #59271	MySQL/innodb crash with RELEASE build but not DEBUG build
Submitted:	4 Jan 2011 7:57	Modified:	25 Apr 2012 13:57
Reporter:	Unreal Hshh	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	5.5.8	OS:	FreeBSD (8.1-RELEASE amd64)
Assigned to:		CPU Architecture:	Any
Tags:	innodb crash RELEASE DEBUG

Description:
I am big innodb table with 74249769 rows. While I do big update/delete/insert, mysqld will crash, query such as "delete from userukcompletion_innodb where category_name = 'emptymath';" which affected 2334822 rows.

That is affect on mysql 5.0/5.1/5.5.

If I compile mysql with debug, it worked correctly. If I compile mysql with RELEASE as normal, it will crash. I tried compile mysql with different CFLAGS/CXXFLAGS (with/without optimize), or newer gcc, it gave me same result. I also tried modified my.cnf or leave it default, same result.

gdb backtrace info (RELEASE build with -g) and mysql.log
http://www.google.com/notebook/public/08194812630946740614/BDQPVDAoQt--QgNUl

How to repeat:
MySQL compiled without WITH_DEBUG.

Big innodb table with big update/delete/insert query.

Please, send the results of:

show create table userukcompletion_innodb\G

and your my.cnf file content.

Based on stack trace and the fact that there are no BLOB columns in your table, the closest know bug is bug #59048. Please, check if your case is similar. Had you executed TRUNCATE or added index for this table recently?

My story.

1. I have 5.0.90 mysqld running with that big innodb table, that's source. mysqld crash while that table doing big update/delete. So I decide to upgrade mysql to 5.1 or 5.5.
2. After mysql binary upgraded. I run mysql_upgrade, it tell me that big innodb table cannot be upgraded, it must dump/restore.
3. mysqldump successfully. But while I restore it, mysqld crash.
4. alter table source engine=myisam; successfully.
5. alter table source engine=innodb; mysqld crash.

Because zfs snapshot can be easy rollback, each step of above, environment is clean.

6. create table userukcompletion_innodb like source; alter table userukcompletion_innodb engine=innodb;  Note: the source table already convert to myisam as step 4.
7. insert into userukcompletion_innodb select * from source; mysqld crash.

8. I wrote a shell script, to loop and limit insert rows. insert into userukcompletion_innodb select * from source where user_id between A and B; I make it 100k user_id to loop, about 3300k rows inserted each time. Finally, I got new innodb same as source, and mysql_upgrade --force tell me all tables are ok.

9. Now, I run "delete from userukcompletion_innodb where category_name = 'emptymath';", mysqld still crash. But it works while I compiled mysql with WITH_DEBUG.

I have no idea about it. I don't know if it same as TRUNCATE, but I havn't modified table index.

Please, check if the same problem still happens with a recent version, 5.5.22.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".