Update update “unsuccessful”?


Update update “unsuccessful”?


The author has recently solved a very tortuous problem, that is, after the business response has a piece of data to be updated and is successful, the query is still old data. So he began to investigate all the way, and finally perfectly explained all the phenomena. Here, the whole process is written down in an article, hoping to be helpful to readers. (the length may be a little long. After reading it patiently, it’s definitely worth it ~)

Problem scenario

Xiao Ming: there is an order update. The updated data is returned successfully, but the old data is still in the database.


After reading this group of data, I was puzzled: “not only did the modified data not take effect, but uptime was also different”. So I logged in to the database for query, and the result was exactly what Comrade Xiao Ming described. What should I do?

After turning over the binlog record statement of this data, it is indeed updated, so the problem comes. This does not mean:

​ Binlog was written, but it was notredo-logThe data is not consistent with the update of the database?

MySQL displays “binlog is written but redo log is not written”“

As we all know, MySQL has a wal mechanism (write logs first, and then write to disk). To find out whether there will be a case of “writing binlog but not redo log”, we need to study the two-stage submission feature of the wal mechanism.

Before we talk about two-phase commit, let’s talk about transactions.

Take a brief look at the next two-stage submission process

Sequence diagram of two-stage submission:

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

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

Then write binlog


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


What is the significance of two-stage logging?

Have you ever thought that binlog is not turned on by default!

In other words, if you don’t need the features brought to you by binlog, you don’t need Mysql to write binlog, and you don’t need any two-phase commit.

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

Therefore, the main purpose of two-stage submission is to ensure the security and consistency of redolog and binlog data (highlight!!! Tap the blackboard three times with a crutch). Only the two log files are logically highly consistent. You can safely use redolog to help you restore the state in the database to the state before crash, and use binlog to realize 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

​ After seeing this, we find that two-stage submission ensures the security and consistency of redolog and binlog data. If you commit in binlog, redolog must be successful, that is to say:

It is impossible to write binlog but not redo log. There will be no problem as described by Xiao Ming.


After thinking over and over again, Zhenhu (しんじつ) はいつつ Hiragana: しんじつはいつ(There is only one truth


That is, there are omissions in the description information. There must be a transaction to operate the record after updating and before querying.


Troubleshooting binlog

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

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

Check all binlog records related to this ID during this period

How to check all binlog records related to this ID during this period? There are so many binlogs. Then you can only write a script for batch processing.


file_list=$(ls mysql-bin.00*)
for i in file_list
    count=`mysqlbinlog -vv -d t100w.t_ 250W $I | grep - C "{primary key ID}"`
	[ $count -gt 0 ] && (echo $i $count)

##Code interpretation:
# mysqlbinlog -d t100w.t_ 250W view only t100w library t_ Binlog of 250W table
#Grep - C counts the number of search keywords in the file (equivalent to select count (*) from table where id >?)
#Get all MySQL bin through ls, find the files with the number of search keywords greater than 0 through the for loop, and print the file name and count the number


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

mysqlbinlog -vv -d t100w.t_250w mysql-bin.009820|less
#Less inside
#/ primary key ID

Finally found the record and the truth came out:



Sometimes the problem is not so complex. It is not the underlying problem of MySQL, but that “Xiaoming” may not be able to accurately describe the problem when providing information. It led to some miscalculations. In dealing with the problem, we should start from the perspective closest to the problem. Thank you for reading the article of a famous chicken DBA. See you next article!


Recommended Today


Supervisor [note] Supervisor – H view supervisor command help Supervisorctl – H view supervisorctl command help Supervisorctl help view the action command of supervisorctl Supervisorctl help any action to view the use of this action 1. Introduction Supervisor is a process control system. Generally speaking, it can monitor your process. If the process exits abnormally, […]