What a tragedy! The ancient bug!!! of MySQL

Guide

This is MySQL8.0 fixed one of the ancient Bug, in 2003 by Percona's CEO (at that time should not Percona bar) proposed bug#199, light see this bug number on the faces of an ancient era of vicissitudes of breath.

The essence of the problem is the way InnoDB initializes the auto_increment, and at each restart, always calculates the maximum self-increment value on the table as the maximum, and the next assignment starts with that value. This means that if a record is deleted from the right leaf node in Btree, the self-increment may be reused after the restart. This can cause problems in a number of scenarios, including but not limited to: primary and Standby switching, historical data migration, and other scenarios. In the bug#199 of a lot of replies, you can see a lot of peer complaints.

The official restoration is more elegant, without altering any existing storage, but by redo log. The patch is based on the framework of the wl#7816, to understand the patch, you have to see what changes wl#7816 made, in order to solve this problem, InnoDB use an engine private system table + special redo log way, Solve the problem of corruption tag persistence within the engine itself. The approximate idea is:

1. Writes a redo log when the index corruption is found, but does not update the data dictionary
2. A InnoDB engine-private system table called DD Buffer table is introduced, and the index corruption bit is deposited before each checkpoint.
3. At the time of crash recovery, read the index corruption bit from redo log and dd Buffer table, merge the results, and mark the tables and indexed objects in memory.

Initialize Persister

There are currently only two types of persister, one for corruption bit persistence, one for self-increment column persistence, and the corresponding class for:

Persister:    |--corruptedindexpersister    |--autoincpersister

Persister corresponds to the Global object dict_persist_t::p ersisters, the corresponding persister can be found by type persistent_type_t, currently only pm_index_corrupted and pm_ Table_auto_inc, but from the annotation point of view, the future will certainly do more expansion, persister at the start of the call function Dict_persist_init initialization.

The new system table

The new system table name is Sys_table_info_buffer, the corresponding management class is Ddtablebuffer, and the pointer is stored in Dict_persist->table_buffer.

The system table contains two columns: table_id and Blob type metadata (ref Ddtablebuffer::init), and the metadata column contains all the metadata that needs to be persisted.

Write back Ddtablebuffer

There are several scenarios in which memory modifications are written back to Ddtablebuffer:

1. Before doing checkpoint (Log_checkpoint), all table objects on the Dirty_dict_tables list, corresponding to persist metadata, need to be written back to Ddtablebuffer (dict_persist_ To_dd_table_buffer)
2. When a Table object is evicted from memory (Dict_table_remove_from_cache_low), it tries to write back if necessary.
3. After you have DDL the table that contains the self-increment column, you need to persist the counter, which in the following function calls Dict_table_set_and_persist_autoinc:

Table

The process of writing back is also relatively simple (Dict_table_persist_to_dd_table_buffer_low):

1. Initialize the metadata data that needs to be written back through the Table object: Corrupt index and Autoinc value (dict_init_dynamic_metadata)
2. Build the record value, insert the Ddtablebuffer system table (Ddtablebuffer::replace () and, if the record exists, perform a pessimistic update operation
3. The Diry_status of the Table object is modified to metadata_buffered, which indicates that there is a buffer metadata

Recovery and Startup

When a crash is resumed, when parsing to log Mlog_table_dynamic_meta (metadatarecover::p arsemetadatalog), parsing is performed and the parsed data is stored in the collection (Metadatarecover: : m_tables), if there is an item of the same Table-id, replace it and make sure it is always up to date.

After completing the recovery, the meta information collected is temporarily stored in the Srv_dict_metadata, followed by apply (Srv_dict_recover_on_restart), apply the process is relatively simple, loading table objects, The Table object is then updated (metadatarecover::apply), for example, for autoinc columns, the larger value is always selected.

finally

This bug has been hanging for quite a long time, do not rule out this bug as InnoDB "characteristics" of the classmate, be sure to notice this change ...

This article was reproduced from: http://www.linuxprobe.com/mysql-bug.html

Free to provide the latest Linux technology tutorials Books, for open-source technology enthusiasts to do more and better: http://www.linuxprobe.com/

What a tragedy! The ancient bug!!! of MySQL