This is a read-only copy of the MariaDB Knowledgebase generated on 2024-11-15. For the latest, interactive version please visit https://mariadb.com/kb/.

Row order in binary log event

A binary log contains events. Insert/update/delete events may contain multiple rows, because they may insert/update/delete multiple rows.

There are some tricky cases when the order of row updates is important, e.g. in queries like this:

UPDATE test SET id = id + 1 ORDER BY id DESC;

So, I'm interested to know, what is the order of event rows? Is it guaranteed to be a chronological order? Is there any documentation on this?

I'm actually more interested in this weird case when a table is updated twice in one query:

MariaDB [mydatabase]> create table test (id int, value1 char(10), value2 char(10), primary key(id));
Query OK, 0 rows affected (0.01 sec)

MariaDB [mydatabase]> insert into test values (1, 'a', 'b');
Query OK, 1 row affected (0.00 sec)

MariaDB [mydatabase]> update test, test as t set test.value1='x', t.value2='y';
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

MariaDB [mydatabase]> update test, test as t set t.value1='u', test.value2='v';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

and here is the events that I see:

WriteRowsEvent: [
  {"values": {"id": 1, "value1": "a", "value2": "b"}}
]
UpdateRowsEvent: [
  {"before_values": {"id": 1, "value1": "a", "value2": "b"}, "after_values": {"id": 1, "value1": "x", "value2": "b"}},
  {"before_values": {"id": 1, "value1": "x", "value2": "b"}, "after_values": {"id": 1, "value1": "x", "value2": "y"}}
]
UpdateRowsEvent: [
  {"before_values": {"id": 1, "value1": "x", "value2": "y"}, "after_values": {"id": 1, "value1": "x", "value2": "v"}},
  {"before_values": {"id": 1, "value1": "x", "value2": "v"}, "after_values": {"id": 1, "value1": "u", "value2": "v"}}
]

It seems like the order is chronological within one event: i.e. after_values[x] is always equal to before_values[y] iff x < y (for the same id), and the last after_values is the last state of the record. Is this behavior guaranteed?

Thanks

- Andrey

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.