Shulou(Shulou.com)06/01 Report--

I met a bug of mysql last night:

170112 22:26:06 InnoDB: Assertion failure in thread 139900811020032 in file / export/home/pb2/build/sb_0-2859905-1295553452.13/mysql

-5.5.9/storage/innobase/ibuf/ibuf0ibuf.c line 4147

InnoDB: Failing assertion: page_get_n_recs (page) > 1

InnoDB: We intentionally generate a memory trap.

InnoDB: Submit a detailed bug report to http://bugs.mysql.com.

InnoDB: If you get repeated assertion failures or crashes, even

InnoDB: immediately after the mysqld startup, there may be

InnoDB: corruption in the InnoDB tablespace. Please refer to

InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html

InnoDB: about forcing recovery.

170112 22:26:06-mysqld got signal 6

This could be because you hit a bug. It is also possible that this binary

Or one of the libraries it was linked against is corrupt, improperly built

Or misconfigured. This error can also be caused by malfunctioning hardware.

We will try our best to scrape up some info that will hopefully help diagnose

The problem, but since we have already crashed, something is definitely wrong

And this may fail.

The database automatically restarts and falls into a dead loop when doing recovery. Unable to start the database.

Bug searched the web about this:

The data is transferred from the cloud community.

Summary: we know that in MySQL5.5, insert buffer has a different term, called change buffer, which caches operations on secondary indexes and does not merge until the actual page is read into bp, which can effectively improve performance when IO-bound and there are many secondary indexes on the table. But there is a sore bug, which was completely fused in version 5.5.31.

We know that in MySQL5.5, insert buffer has a different term, called change buffer, which caches operations on secondary indexes and does not merge until the actual page is read into bp, which can effectively improve performance when IO-bound and there are many secondary indexes on the table.

But there is a sore bug, which was completely fix in version 5.5.31. If you unfortunately encounter the following assertion error crash, congratulations on your success:

InnoDB: Failing assertion: page_get_n_recs (page) > 1

The problem can be traced back to mid-January 2012, three or four days before the Spring Festival, when an online library unfortunately triggered the bug, causing crash and unable to restart. At that time, the processing method was to use innodb froce recovery to get up, while turning off innodb purge thread (another bug, setting a larger innodb force recovery cannot start mysqld), and then dump the data to rebuild the library.

For an earlier discussion of the bug, see http://bugs.mysql.com/bug.php?id=61104, but bug#61104 does not completely fix the bug, but just removes the assertion so that the user can set up the instance and execute a DDL to rebuild the secondary index of the table

Later, Percona's Alexey Kopytov put forward the root cause of the bug on buglist (http://bugs.mysql.com/bug.php?id=66819) is that deleting ibuf records and applying Ibuf records are not atomic, that is, they are not in a mtr, so hanging up at an inappropriate point in time may lead to inability to crash recovery. In fact, even if we set innodb_change_buffer to inserts is not safe.

Later, the bug was dropped by fix, but after I saw the diff, I found that the fix was incomplete and there was still a problem with the DELETE scene. So I complained on Facebook, and Percona's Valeriy Kravchuk was eager to help confirm..

It mainly involves two functions

> ibuf_merge_or_delete_for_page, which is the main function for change buffer merging records on a block

For Purge operations, that is, the IBUF_OP_DELETE type:

After ibuf_delete is executed, ibuf_btr_pcur_commit_specify_mtr is performed directly, redo is submitted, and then the ibuf record (ibuf_delete_rec) is deleted.

> ibuf_delete_rec, each time a merge of change buffer records is completed, this function is called to delete it from the Ibuf tree

In the ibuf_delete_rec function, when btr_cur_optimistic_delete fails, it will first go to commit mitr (call ibuf_btr_pcur_commit_specify_mtr), and then open a new mtr to do btr_cur_pessimistic_delete.

We know that Innodb does crash recovery through the redo log written by mtr. If we crash between the success of the merge data and the deletion of the ibuf record, then it is possible that the data record has been updated, but the ibuf record has not been deleted, thus triggering the aforementioned assertion failure (when doing ibuf_delete, we want to delete the record, but find that the record on the page has been deleted … )

That is to say, there may be problems for all types of change buffer operations, but for purge operations, the probability is higher because it has two risk points.

The first official fix, in MySQL5.5.29, fixed a problem in ibuf_delete_rec by tagging and deleting ibuf entry before deleting it pessimistically.

Http://bazaar.launchpad.net/~mysql/mysql-server/5.5/revision/3979

The official second fix, in MySQL5.5.31, fixes problems in ibuf_merge_or_delete_for_page

Http://bazaar.launchpad.net/~mysql/mysql-server/5.5/revision/4177

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.