Jose Rodriguez Salinas

Jose Rodriguez Salinas

The curious case of missing binlog events

If you've ever relied on MySQL's binary log (binlog) for change data capture, replication debugging, or auditing, you might have run into a fun surprise: some statements you know executed simply don't show up in the binlog. Let's talk about why.

A quick refresher on the binlog

The binlog records changes that modify data. MySQL supports different binlog formats (STATEMENT, ROW, and MIXED) but the key thing they all share is that the binlog exists to capture data changes. Not intentions. Not attempts. Changes.

The setup

Say you're building a database to track crew members aboard the Hail Mary:

CREATE TABLE crew (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    status VARCHAR(20) DEFAULT 'alive'
);

INSERT INTO crew VALUES (1, 'Ryland Grace', 'alive'), (2, 'Yao', 'deceased'), (3, 'Ilyukhina', 'deceased');

Now you run:

UPDATE crew SET status = 'alive' WHERE id = 1;

Ryland Grace is already alive. Nothing changed. If you're using ROW format and you go parse the binlog with mysqlbinlog, you won't find a row event for this statement. There's no UPDATE_ROWS_EVENT because no row was actually modified.

Why this matters

If you're building a CDC (change data capture) pipeline, maybe feeding events into Kafka, Debezium, or a custom consumer, you might expect every UPDATE to produce an event downstream. It won't. The binlog only records what the storage engine actually changed.

This is especially sneaky in a few scenarios:

Idempotent writes: If your application retries writes or applies the same update multiple times, only the first one (the one that actually changes data) shows up.

Conditional updates that match no rows: An UPDATE ... WHERE that matches zero rows produces no row events either.

UPDATE crew SET name = 'Rocky' WHERE id = 999;
-- No matching row, no binlog row event. Rocky isn't in this table (yet).

DELETEs on absent rows: Same deal. DELETE FROM crew WHERE id = 999 with no matching row means no DELETE_ROWS_EVENT.

INSERT ... ON DUPLICATE KEY UPDATE that changes nothing: This one is particularly deceptive. Say Ryland Grace already exists with status = 'alive' and you run:

INSERT INTO crew (id, name, status) VALUES (1, 'Ryland Grace', 'alive')
    ON DUPLICATE KEY UPDATE status = 'alive';

The duplicate key is hit, so MySQL falls into the UPDATE path. But the value is already 'alive'. No data changed. In ROW format, you get no WRITE_ROWS_EVENT and no UPDATE_ROWS_EVENT. The statement executed, the engine looked at the row, shrugged, and moved on.

This bites especially hard because INSERT ... ON DUPLICATE KEY UPDATE is a common upsert pattern. Your application thinks it's "writing" data every time, but if the values are identical to what's already on disk, the binlog stays silent. If your CDC pipeline is expecting an event for every upsert call, you'll have gaps you can't explain without knowing this behavior.

ROW vs STATEMENT format

In STATEMENT format, the SQL statement itself is logged regardless of whether rows changed, so you will see it. But ROW format (the default and recommended format for replication safety) only logs actual row images. No change, no event.

This means switching binlog formats can change what your downstream consumers see. If you're migrating from STATEMENT to ROW, be aware that some events your pipeline was counting on may vanish.

How to observe this

Fire up mysqlbinlog and see for yourself:

# flush logs so we get a clean file
mysql -e "FLUSH LOGS;"

# run a no-op update
mysql -e "UPDATE test.crew SET status = 'alive' WHERE id = 1;"

# parse the latest binlog
mysqlbinlog --verbose /var/lib/mysql/binlog.000002

You'll see a QUERY event for the BEGIN and a XID event for the commit, but between them, no UPDATE_ROWS_EVENT. The transaction was opened and committed, but the engine had nothing to write.

The takeaway

The binlog is not a log of what you asked MySQL to do. It's a log of what MySQL actually did to your data. If you're parsing binlogs for CDC, replication monitoring, or auditing, keep this in mind:

  • No row change = no row event in ROW format.
  • STATEMENT format logs the SQL text regardless, but comes with its own set of replication pitfalls.
  • Don't assume a 1:1 mapping between application queries and binlog events.

Next time an event goes "missing" from your pipeline, check whether the data actually changed. Chances are, MySQL is telling you the truth. Nothing happened.

← Back to home