Update Succeeds but Data Not Changed? Investigating MySQL Binlog vs Redo‑Log and Two‑Phase Commit

The author, a DBA from 爱可生, describes a real‑world issue where an UPDATE statement reports success but the queried data is still old, prompting a deep dive into MySQL's write‑ahead logging (WAL) and the two‑phase commit mechanism.

MySQL first writes a redo‑log in the prepare phase and then records the binlog; the commit phase writes the binlog with a commit flag. This design ensures consistency between redo‑log and binlog, enabling crash recovery, backup, and replication.

The article emphasizes that, because the prepare phase always writes the redo‑log before the binlog, a situation where the binlog is written without a corresponding redo‑log update should not occur, contradicting the initial symptom.

Through careful analysis, the author discovers that the reported inconsistency stems from missing transaction information in the description rather than a MySQL bug. The true cause is an unnoticed transaction that altered the record between the update and the query.

To locate the relevant binlog entries, the author writes a shell script that iterates over all MySQL binary log files, uses mysqlbinlog to filter by database/table, and greps for the primary key ID, printing files with matching entries.

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的文件,并打印文件名和统计个数

After identifying the suspect binlog file, the author uses less to search for the primary key ID, ultimately finding the transaction that caused the discrepancy.

The conclusion stresses that many database issues are not due to engine faults but to incomplete or inaccurate problem descriptions, and recommends approaching troubleshooting from the closest possible angle to the observed symptom.