Shulou(Shulou.com)05/31 Report--

This article mainly introduces "BUG Analysis in MySQL". In daily operation, I believe many people have doubts about BUG analysis in MySQL. The editor consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubts about "BUG Analysis in MySQL". Next, please follow the editor to study!

▌ problem description

Recently, after upgrading the table of an important Mysql customer online from 5.6to 5.7, there was a "Duplicate key" error in the insertion process on master, and it occurred on both the active / standby and RO instances.

Take one of the tables as an example, the auto increment id viewed by the "show create table" command before migration is 1758609, and after migration it becomes 1758598. In fact, the maximum value of the self-increment column of the new table generated by migration is 1758609 with max.

Users use the Innodb engine, and according to the operation and maintenance students, have encountered similar problems before, restart can return to normal.

Troubleshooting ▌ kernel problems

As the user reported that the access on 5.6 was normal, an error was reported after switching to 5.7. Therefore, the first thing to suspect is that there is something wrong with the 5.7kernel, so the first reaction is to search the official bug list to see if there is a similar problem to avoid duplicating the car. After searching, it is found that there is a similar bug officially. Here is a brief introduction to this bug.

Background knowledge 1

Auto increment related parameters and data structure in Innodb engine

The main parameters include: innodb_autoinc_lock_mode is used to control the locking mode of obtaining self-increment, auto_increment_increment and auto_increment_offset are used to control the increment interval and start offset of self-increment columns.

The main structures involved include: the data dictionary structure, which stores the current auto increment value of the whole table and the protection lock; the transaction structure, which stores the number of rows processed within the transaction; and the handler structure, which stores the loop iteration information of multiple rows within the transaction.

This part of the Internet has a better introduction of the article, see: (https://www.cnblogs.com/zengkefu/p/5683258.html).

Background knowledge 2

The process of accessing and modifying autoincrement in mysql and Innodb engine

(1) the preservation and recovery of autoincrement value when data dictionary structure (dict_table_t) is swapped in and out. When swapping out, save the autoincrement in the global mapping table, and then eliminate the dict_table_t in memory. Restore to the dict_table_t structure when switching in by looking up the global mapping table. The related functions are dict_table_add_to_cache and dict_table_remove_from_cache_low.

(2) row_import, table truncate process updates autoincrement.

(3) when handler first open, it will query the value of the largest self-increasing column in the current table, and use the value of the maximum column plus 1 to initialize the value of autoinc in the data_dict_t structure of the table.

(4) insert process. The stack of related modifications to autoinc is as follows:

In the third step of ha_innobase::write_row:write_row, call the update_auto_increment function in the handler handle to update the value of auto increment: call the API Innodb to obtain a self-increment, and adjust the value of the acquired self-increment according to the value of the current auto_increment related variables; at the same time, set the value of the next self-increment column to be processed by the current handler. Ha_innobase::get_auto_increment: get the current auto increment value in dict_tabel and update the value of the next auto increment to the data dictionary based on the global parameters ha_innobase::dict_table_autoinc_initialize: update the value of auto increment, if the specified value is larger than the current value. Handler::set_next_insert_id: sets the value of the self-increment column of the next row to be processed in the current transaction.

(5) update_row. For the "INSERT INTO t (C1 and c2) VALUES (XMagne y) ON DUPLICATE KEY UPDATE" statement, you need to advance the value of auto increment regardless of whether the row pointed to by the unique index column exists or not. The related code is as follows:

If (error = = DB_SUCCESS & & table- > next_number_field & & new_row = = table- > record [0] & & thd_sql_command (m_user_thd) = = SQLCOM_INSERT & & trx- > duplicates) {ulonglong auto_inc;. Auto_inc = table- > next_number_field- > val_int (); auto_inc = innobase_next_autoinc (auto_inc, 1, increment, offset, col_max_value); error = innobase_set_max_autoinc (auto_inc); … }

From the perspective of our actual business processes, our errors can only involve insert and update processes.

BUG 76872 / 88321: "InnoDB AUTO_INCREMENT produces same value twice"

(1) Overview of bug: when autoinc_lock_mode is greater than 0 and auto_increment_increment is greater than 1, a "duplicate key" error occurs when multiple threads perform insert operations on the table at the same time after the system is restarted.

(2) cause analysis: after restart, innodb will set the value of autoincrement to max (id) + 1. At this point, when inserting for the first time, the write_row process calls handler::update_auto_increment to set the autoinc-related information. First, get the value of the current autoincrement (that is, max (id) + 1) through ha_innobase::get_auto_increment, and modify the value of the next autoincrement to next _ id according to the relevant parameters of autoincrement.

When auto_increment_increment is greater than 1, max (id) + 1 is not greater than next_id. After handler::update_auto_increment obtains the value returned by the engine layer, in order to prevent some engines from not taking the current auto increment parameters into account when calculating the self-increment, it will re-calculate the self-increment of the current line according to the parameters. Since the global parameters are taken into account inside the Innodb, the self-increment calculated by the handle layer for the self-incrementing id returned by Innodb is also next_id, and a row with self-increment id to next_id will be inserted soon.

The handler layer sets the next autoincrement value at the end of the write_row based on the current row value next_id. If another thread is in the process of inserting during the next autoincrement that write_row has not yet set up the table, then the self-increment it gets will also be next_id. This leads to repetition.

(3) solution: the global autoincrement parameter is taken into account when obtaining the self-increment column within the engine, so that the self-increment obtained by the first insert thread after restart is not max (id) + 1, but next_id, and then set the value of the next autoincrement according to next_id. Because this process is lock-protected, other threads will not get duplicate values when they get the autoincrement.

From the above analysis, this bug occurs only when autoinc_lock_mode > 0 and auto_increment_increment > 1. The actual online business sets both parameters to 1, so you can rule out the possibility that this bug causes online problems.

On-site Analysis and recurrence Verification of ▍

Since the official bug failed to solve our problem, we have to stand on our own feet and start with the error phenomenon.

(1) analyze the rules of max id and autoincrement as the ON UPDATE CURRENT_TIMESTAMP column is set in the user's table, you can grab the max id, autoincrement and the recently updated records of all the wrong tables to see if there are any rules. The captured information is as follows:

At first glance, this error is quite regular. The update time column is the last time it was inserted or modified. Combined with the values of auto increment and max id, it looks like the last batch of transactions only updated the self-incrementing id of the row and did not update the auto increment value.

Reminiscent of the introduction to the usage of auto increment in the [official documentation], the update operation can only update the self-increasing id without triggering auto increment promotion. Following this line of thinking, I tried to recreate the user's scene. The reproduction method is as follows:

At the same time, we also see the operation of update self-incrementing column in binlog. As shown in the figure:

However, because binlog is in ROW format, we can't tell whether a problem with the kernel caused the change in the self-increment column or the user's own update. So we contacted the customer for confirmation, and the user was sure that there was no self-increment operation.

So where on earth did these self-increments come from?

(2) analyzing the user's tables and sql statements to continue the analysis, it is found that the user has three types of tables (hz_notice_stat_sharding, hz_notice_group_stat_sharding,hz_freeze_balance_sharding), all of which have self-increasing primary keys.

But both of the first two have autoinc errors, except for the hz_freeze_balance_sharding table.

Is it because the user accesses the two tables differently? Grab the user's sql statement, sure enough, the first two tables use replace into operations, and the last table uses update operations. Is it the problem caused by the replace into statement? Search the official bug and found another suspected bug.

Bug # 87861: "Replace into causes master/slave have different auto_increment offset values"

Reason:

(1) Mysql is actually implemented for replace into through delete + insert statements, but in ROW binlog format, update type logs are recorded to binlog. The Insert statement updates the autoincrement,update synchronously, but not.

(2) replace into operates in delete+insert mode on Master, and autoincrement is normal. After copying to slave based on ROW format, the slave machine plays back according to the update operation, and only updates the value of the self-increasing key in the row, not the autoincrement.

Therefore, there will be a situation in which max (id) is greater than autoincrement on the slave machine. At this point, the binlog records all the column values for the insert operation in ROW mode, and the self-incrementing id is not reassigned when played back on slave, so there is no error. But if slave cuts master, there will be a "Duplicate key" error when you encounter an Insert operation.

(3) because the user migrates from 5.6to 5.7and then inserts directly on 5.7.This is equivalent to slave switching, so an error will be reported.

▍ solution

Possible solutions on the business side:

(1) change binlog to mixed or statement format

(2) replace replace into with Insert on duplicate key update

Possible solutions on the kernel side:

(1) if you encounter a replace into statement in ROW format, record the logevent in statement format and record the original statement to binlog.

(2) record the logevent of the replace into statement as a delete event and an insert event in ROW format.

At this point, the study of "BUG Analysis in MySQL" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.