MySQL Bugs: #79348: Corrupt indexes causing assertion errors after upgrading to 5.6.24

Bug #79348	Corrupt indexes causing assertion errors after upgrading to 5.6.24
Submitted:	19 Nov 2015 21:07	Modified:	31 Aug 2021 0:59
Reporter:	Soumya K	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.6.24	OS:	Ubuntu (precise)
Assigned to:		CPU Architecture:	Any

Description:
After upgrading from 5.1 -> 5.5.44 -> 5.6.24 several MySQL servers crash with an assertion error (InnoDB: Assertion failure in thread 1234 in file fil0fil.cc line 5608). Its almost always a corrupt index according to the error message. This was never the case before when we were on 5.1. Dropping the table and restoring from backup works but obviously is a loss of service since the server keeps crashing.

Table structure associated with the corrupt index in this case is below. 

 CREATE TABLE `wp_options` (
  `option_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `option_name` varchar(64) NOT NULL DEFAULT '',
  `option_value` longtext NOT NULL,
  `autoload` varchar(20) NOT NULL DEFAULT 'yes',
  PRIMARY KEY (`option_id`),
  UNIQUE KEY `option_name` (`option_name`)
) ENGINE=InnoDB AUTO_INCREMENT=549714 DEFAULT CHARSET=utf8 

 

CONFIG:
******

[mysqld_safe]
open_files_limit = 65535
[mysqld]
log-warnings
log-queries-not-using-indexes
innodb_file_per_table
bind-address	= x.x.x.x
user		= mysql
socket=/path/to/mysql/triodia/mysql.triodia.sock
general_log=1
general_log_file=/path/to/mysql/logs/mysql.triodia.log
slow_query_log=1
slow_query_log_file=/path/to/mysql/logs/mysql.triodia.slow
log-error=/path/to/mysql/logs/mysql.triodia.err
server-id=5153
table_open_cache=2048
thread_cache_size=20
table_definition_cache=3000
ft_min_word_len=3
innodb_buffer_pool_size=512M
innodb_log_buffer_size=2M
innodb_log_file_size=64M
innodb_open_files=1000
join_buffer_size=1M
key_buffer_size=32M
max_allowed_packet=16M
max_connections=1200
max_connect_errors=10000
max_user_connections=300
open_files_limit=65535
query_cache_limit=5M
query_cache_size=50M
read_buffer_size=1M
sort_buffer_size=1M
myisam_recover_options=BACKUP,FORCE
tmpdir=/path/to/mysql/tmp
wait_timeout=14400
explicit_defaults_for_timestamp=1
performance_schema=OFF

How to repeat:
Cannot repeat.

error log

Attachment: triodia.err (application/octet-stream, text), 11.52 KiB.

Could this be related to text/longtext columns?

Per https://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-3.html, mysql_upgrade should fix tables requiring a rebuild by automatically executing a alter table force on them. But that does not seem to be the case.

mysql>  check table wp_options for upgrade;
+---------------------------+-------+----------+--------------------------------------------------------------------------------------------------+
| Table                     | Op    | Msg_type | Msg_text                                                                                         |
+---------------------------+-------+----------+--------------------------------------------------------------------------------------------------+
| database_name.wp_options         | check | error    | Table rebuild required. Please do "ALTER TABLE `wp_options` FORCE" or dump/reload to fix it! |
+---------------------------+-------+----------+--------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table wp_options force;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>  check table wp_options for upgrade;
+---------------------------+-------+----------+----------+
| Table                     | Op    | Msg_type | Msg_text |
+---------------------------+-------+----------+----------+
| database_name.wp_options | check | status   | OK       |
+---------------------------+-------+----------+----------+
1 row in set (0.00 sec)

On some of these tables I notice, the auto_increment value jumps to a very large value.

For example, in previous nights backup:
CREATE TABLE `wp_options` (
  `option_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `option_name` varchar(64) NOT NULL DEFAULT '',
  `option_value` longtext NOT NULL,
  `autoload` varchar(20) NOT NULL DEFAULT 'yes',
  PRIMARY KEY (`option_id`),
  UNIQUE KEY `option_name` (`option_name`)
) ENGINE=InnoDB AUTO_INCREMENT=70902 DEFAULT CHARSET=latin1;

mysql> check table wp_options;
+---------------------------+-------+----------+-----------------------------
------------------------+
| Table                     | Op    | Msg_type | Msg_text
                        |
+---------------------------+-------+----------+-----------------------------
------------------------+
| wp_options | check | Warning  | InnoDB: The B-tree of index
"PRIMARY" is corrupted. |
| wp_options | check | Warning  | InnoDB: Index "option_name"
is marked as corrupted  |
| wp_options | check | error    | Corrupt
                        |
+---------------------------+-------+----------+-----------------------------
------------------------+
3 rows in set (0.17 sec)

 CREATE TABLE `wp_options` (
  `option_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `option_name` varchar(64) NOT NULL DEFAULT '',
  `option_value` longtext NOT NULL,
  `autoload` varchar(20) NOT NULL DEFAULT 'yes',
  PRIMARY KEY (`option_id`),
  UNIQUE KEY `option_name` (`option_name`)
) ENGINE=InnoDB AUTO_INCREMENT=7953766394816129397 DEFAULT CHARSET=latin1

I've also upgraded to some instances to 5.6.27 but this is still an issue. Can someone please help?

Thank you for the bug report. Are you able to provide a 5.1 wp_options dump file wit some data inserts?. Thanks.

Do you need data of a wp_options table that had corrupt index? Or just any 5.1 wp_options data?

Thank you for the feedback. Just the insert data command so I create/populate the table on 5.1 and then upgrade 5.5->5.6. Thanks.

Sure. However, not all wp_options table has this issue (we have thousands of different databases with this table). I don't think you can recreate the issue.

wp_options.sql

Attachment: wp_options (application/octet-stream, text), 444.81 KiB.

Any update on this?

Any update on this?

Not repeatable test case was provided.

This turned out to be a problem with our custom kernel.