Failure Analysis | Remember the troubleshooting process of an unsuccessful mysql update

Author: Wang Xiang

A member of the Aikesheng DBA team, responsible for the operation and maintenance of the company's DMP products and the handling of customer MySQL issues. Good at database fault handling. Have a strong interest in database technology and python.

Source of this article: original submission

* Produced by the Aikesheng open source community, original content is not allowed to be used without authorization, please contact the editor and indicate the source for reprinting.

Contents of this article:

update update "unsuccessful"?

Preface

Problem scenario

MySQL appears "binlog is written but redo-log is not written"

• A brief look at the next two-stage submission process
• What is the significance of two-stage log writing?

The investigation is deadlocked

Check binlog

• Check all binlog records related to this id during this period

to sum up

update "Unsuccessful"?

Preface

The author recently solved a very tortuous problem, that is, the business reports that there is a piece of data to be updated and after it is successful, the query is still the old data. So I started to investigate all the way, and finally explained all the phenomena perfectly.

Write down the whole process as an article here, hoping to be helpful to readers. (The length may be a bit long, it’s definitely worth the money to finish reading it patiently~)

Problem scenario

Business Xiaoming: There is an order update, the update data returns success, but the database is still old data.

After reading this set of data, I was puzzled: "Not only is the modified data not valid, but utime is also different." So I logged into the database for query, and the result was indeed what Comrade Xiao Ming described. How to do it?

I checked the binlog record about this piece of data and indeed it was updated, then the problem is coming. Doesn't this mean:

I wrote the binlog but did not update the redo-log. Isn't this the data is inconsistent?

MySQL appears "binlog is written but redo-log is not written"

As we all know, MySQL has a WAL mechanism (write the log first, then write to the disk). We need to figure out whether there will be a situation where "binlog is written but redo-log is not written", we need to study the two-stage submission feature of this WAL mechanism.

Before talking about the two-phase commit transaction, let's talk about the transaction first.

Simply look at the next two-stage submission process

The sequence diagram of the two-phase submission:

Take a cursory look at the figure above, when MySQL wants to execute a transaction, it will be divided into two stages

The first stage (prepare stage): write redo-log and mark it as prepare state.

Then write binlog

The second stage (commit stage): write binlog and mark it as commit state.

the significance of logging in two phases?

Have you ever thought about such a thing, binlog is not turned on by default!

In other words, if you don't need the features that binlog brings to you at all, then you don't need MySQL to write binlog at all, and you don't need any two-phase commit.

Only one redolog is enough. No matter how your database crashes, the content recorded in redolog can always restore the data in your MySQL memory to the state before the crash.

Therefore, the main purpose of the two-phase submission is to ensure the safety and consistency of redolog and binlog data (emphasis on the key!!! Knock the stick on the blackboard 3 times). Only in these two log files are logically highly consistent. You can use redolog to help you restore the state in the database to the state before the crash, and use binlog to implement data backup, recovery, and master-slave replication. The two-phase commit mechanism can ensure that the logic of the two log files is highly consistent. No errors, no conflicts.

The investigation is deadlocked

Seeing this, we found that the two-phase commit guarantees the security and consistency of redolog and binlog data. Commit in binlog, redolog must be successful, that is:

is impossible to write the binlog but not the redo-log, and there will be no such problems as Xiao Ming described.

After repeated thinking, Zhen Mi (しんじつ)はいつもひとつ Hiragana: しんじつはいつもひとつ (there is only one truth).

That is, there is an omission in the description information. After the update and before the query, there must be a transaction that has operated on this record.

Check binlog

1. Check all binlogs related to this id during this period and extract relevant records

2. Find out the binlog of the transaction after the update and before the query

all binlog records related to this id during this period

How to check all binlog records related to this id during this period of time, so many binlogs. That can only write a script for batch processing.

file_list=$(ls mysql-bin.00*)
for i in file_list
do
    count=`mysqlbinlog -vv -d t100w.t_250w $i |grep -c "{主键id}"`
    [ $count -gt 0 ] && (echo $i $count)
done

## 代码解释：
# mysqlbinlog -d t100w.t_250w 只查看t100w库t_250w表的binlog
# grep -c 统计文件中搜索关键字的个数（等价于 select count(*) from table where id > ?）
# 通过ls获取到所有mysql-bin，通过for循环找到搜索关键字的个数大于0的文件,并打印文件名和统计个数

Then use the less command to search for the primary key id.

mysqlbinlog -vv -d t100w.t_250w mysql-bin.009820|less
# less内
# /主键id

Finally found the record, the truth became clear:

to sum up

Sometimes the problem is not that complicated. It is not the bottom layer of MySQL except for the problem, but "Xiao Ming" may not be able to accurately describe the problem clearly when providing information. Leading to some of our misjudgments. When dealing with problems, we should start with the angle of being closest to the problem. Thank you everyone
After reading a Caiji DBA's article. See you in the next article!