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