| Bug #62634 | MySQL crashes with an assertion failure during update statement on InnoDB table | ||
|---|---|---|---|
| Submitted: | 5 Oct 2011 20:13 | Modified: | 27 Jan 2012 18:50 |
| Reporter: | Jeff Bilyk | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S1 (Critical) |
| Version: | 5.5.15, 5.5.16 | OS: | Linux (Alpine Linux 2.2.3) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | InnoDB assert crash update | ||
[5 Oct 2011 20:14]
Jeff Bilyk
script to generate database backup file
Attachment: generate-db-dump.sh (application/octet-stream, text), 3.73 KiB.
[5 Oct 2011 20:16]
Jeff Bilyk
Another note: we have been able to replicate this problem on 4 different physical servers.
[5 Oct 2011 20:33]
Jeff Bilyk
Forgot to post the section of the mysql error log (which is now attached) that has the assertion failure: 111005 15:59:03 InnoDB: Assertion failure in thread 974969760 in file btr0pcur.c line 423 InnoDB: Failing assertion: page_is_comp(next_page) == page_is_comp(page)
[5 Oct 2011 20:33]
Jeff Bilyk
mysql error log
Attachment: dev02.err (application/octet-stream, text), 5.51 KiB.
[5 Oct 2011 20:37]
Jeff Bilyk
Initially had typo - this has been tested and replicated on 5.5.14 and 5.5.15, but not 5.5.16.
[6 Oct 2011 11:13]
MySQL Verification Team
I couldn't repeat with current source server, please try version 5.2.16 and if the crash still exists please provide the my.cnf. Thanks.
[6 Oct 2011 13:10]
Jeff Bilyk
error file from 5.5.16 run
Attachment: dev02-5.5.16.err (application/octet-stream, text), 5.65 KiB.
[6 Oct 2011 13:11]
Jeff Bilyk
my.cnf used up to and including 5.5.16
Attachment: my-5.5.16.cnf.txt (text/plain), 4.77 KiB.
[6 Oct 2011 13:11]
Jeff Bilyk
screen output from run with 5.5.16
Attachment: stdout-5.5.16.txt (text/plain), 3.51 KiB.
[6 Oct 2011 13:14]
Jeff Bilyk
Hi Miguel, I upgraded one of the servers that I was having the issue on to 5.5.16 and was able to replicate the crash. I attached the my.cnf file that we have been using, along with the error log and stdout contents. The error from this run (in the dev02-5.5.16.err file) was: 111006 8:58:39 InnoDB: Assertion failure in thread 1059802016 in file btr0pcur.c line 396 InnoDB: Failing assertion: page_is_comp(next_page) == page_is_comp(page)
[6 Oct 2011 13:16]
Jeff Bilyk
script referenced in stdout - same as stress queries from original post
Attachment: stressQueries.sql (application/octet-stream, text), 223 bytes.
[17 Oct 2011 20:21]
Jeff Bilyk
A colleague help to narrow down the scope of the issue. It appears that this is only replicable when using InnoDB (Antelope) with compact row format. If Barracuda is enabled and dynamic row format is used, then I can no longer replicate the issue.
[27 Oct 2011 17:37]
Jeff Bilyk
Easier way to reproduce error
Attachment: demonstrateCrash.sql (application/octet-stream, text), 10.00 KiB.
[27 Oct 2011 17:38]
Jeff Bilyk
I've added an updated script which makes the process of replicating this bug much simpler. Please let me know if there is any other information that's needed to replicate. Thanks.
[27 Oct 2011 17:48]
Jeff Bilyk
Raising severity of the issue since when MySQl (InnoDB actually) crashes, after a few loops of crashing, it will eventually not start up cleanly anymore, causing a service outage, requiring a database restore.
[27 Oct 2011 19:09]
MySQL Verification Team
Thank you for the feedback. Are you using a debug compiled server? how long take to you to get the crash?. +----------+ | COUNT(*) | +----------+ | 3400000 | +----------+ 1 row in set (4.89 sec) Query OK, 100000 rows affected (29.38 sec) Records: 100000 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (1 min 1.53 sec) Rows matched: 3500000 Changed: 0 Warnings: 0 +----------+ | COUNT(*) | +----------+ | 3500000 | +----------+ 1 row in set (5.02 sec) Ctrl-C -- sending "KILL QUERY 1" to server ... Ctrl-C -- query aborted. ERROR 1317 (70100): Query execution was interrupted ^CTerminate batch job (Y/N)? ^C H:\dbs>55 H:\dbs>h:\dbs\5.5\bin\mysql -uroot --port=3540 --prompt="mysql 5.5 >" Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.5.18-log Source distribution Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql 5.5 >show variables like "innodb_file_format"; +--------------------+----------+ | Variable_name | Value | +--------------------+----------+ | innodb_file_format | Antelope | +--------------------+----------+ 1 row in set (0.02 sec) mysql 5.5 >
[28 Oct 2011 20:15]
Chris Wall
Output when we run demonstrateCrash.sql
Attachment: demonstrateCrashOutput.txt (text/plain), 335 bytes.
[28 Oct 2011 20:16]
Chris Wall
Error log from the crash when we run demonstrateCrash.sql
Attachment: TestBox.err (text/plain), 4.93 KiB.
[28 Oct 2011 20:17]
Chris Wall
Innodb file format information
Attachment: MySqlShowVariables.txt (text/plain), 746 bytes.
[28 Oct 2011 20:18]
Chris Wall
Current configuration
Attachment: my.cnf (application/octet-stream, text), 723 bytes.
[28 Oct 2011 20:30]
Chris Wall
If we run demonstrateCrash.sql with the Antelope version of InnoDB, we will get a crash within 5 minutes. If we run demonstrateCrash.sql with the Barracuda version of InnoDB by uncommenting the lines #innodb_file_per_table #innodb_file_format = Barracuda in the my.cnf file, we no longer get a crash.
[27 Dec 2011 18:50]
Sveta Smirnova
Thank you for the feedback. I too can not repeat described behavior. Please try with current version 5.5.19 and if problem still exists provide name of MySQL package you use.
[28 Jan 2012 1:00]
Bugs System
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".
Description: MySQL using InnoDB with one testing database with a single table with 700,000 rows as a starting point. When running an update query on all rows, the InnoDB engine will cause an assertion failure which will cause MySQL to crash and leave it in such a state that it is unable to be started up. The database then needs to be restored from a backup. How to repeat: 1. Initialize a new instance of MySQL (fresh install). 2. Using the attached script (generate-db-dump.sh), generate a MySQL database backup file. 3. Restore the file (the one generated in step 2) to MySQL. 4. Run the following queries as a load on the database (sometimes multiple runs are needed to replicate the issue): USE `Tester`; UPDATE Test SET Message = 'Swamping Message'; INSERT INTO Test (Message) SELECT Message FROM Test ORDER BY TestId LIMIT 200000;