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

Answer Answered by Kristian Nielsen in this comment.

Yes, this behaviour should be guaranteed.

Whatever order the rows were updated in on the master, the row events will contain row updates in the same order, and matching before/after images. And applying the row updates one by one in order on the slave will result in the same data as on the master.

It though a very interesting/wierd case as you wrote, I wasn't aware that this kind of query was even possible.

(Answer probably too old to be useful to the author, just adding it for reference.)

Comments

Comments loading...
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.