The Order or events TABLE_MAP_EVENT and WRITE_ROW_EVENT

[Mansour Al Akeel]

In Row based replication setup, I have a trigger on one of the tables
in a replicated database.

The table "user" has a trigger that inserts values into "audit".

The sequence of events:

#150608 14:45:18 server id 1 end_log_pos 25066 CRC32 0xb2b66d43
Table_map: `drupal`.`users` mapped to number 101
# at 25066
#150608 14:45:18 server id 1 end_log_pos 25137 CRC32 0x8a2a06f9
Table_map: `drupal`.`audits` mapped to number 70
# at 25137
#150608 14:45:18 server id 1 end_log_pos 25211 CRC32 0x0ba847a5
Write_rows: table id 101
# at 25211
#150608 14:45:18 server id 1 end_log_pos 25315 CRC32 0xbd38092d
Write_rows: table id 70 flags: STMT_END_F
# at 25315
#150608 14:45:18 server id 1 end_log_pos 25346 CRC32 0x2044e691
Xid = 492
COMMIT/*!*/;
# at 25346
#150608 14:46:12 server id 1 end_log_pos 25428 CRC32 0x0e28bf95
Query thread_id=38 exec_time=0 error_code=0
SET TIMESTAMP=1433760372/*!*/;
BEGIN
/*!*/;
# at 25428
#150608 14:46:12 server id 1 end_log_pos 25484 CRC32 0x25d127a1
Table_map: `drupal`.`users` mapped to number 101
# at 25484
#150608 14:46:12 server id 1 end_log_pos 25555 CRC32 0x817735d4
Table_map: `drupal`.`audits` mapped to number 70
# at 25555
#150608 14:46:12 server id 1 end_log_pos 25629 CRC32 0x18031884
Write_rows: table id 101
# at 25629
#150608 14:46:12 server id 1 end_log_pos 25733 CRC32 0x50b5823a
Write_rows: table id 70 flags: STMT_END_F
# at 25733
#150608 14:46:12 server id 1 end_log_pos 25764 CRC32 0xf9855dc6
Xid = 500
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;



As we can see, I am getting:
TABLE_MAP_EVENT for users
TABLE_MAP_EVENT for audits
WRITE_ROWS for users
WRITE_ROWS for audits

from https://dev.mysql.com/doc/internals/en/table-map-event.html

"The first event used in Row Based Replication declares how a table
that is about to be changed is defined. "


Based on my understanding, I am expecting:

TABLE_MAP_EVENT for users
WRITE_ROWS_EVENT for users
TABLE_MAP_EVENT for audits
WRITE_ROWS_EVET for audits

I am missing something here ?

Thank you.

--
MySQL Internals Mailing List
For list archives: http://lists.mysql.com/internals
To unsubscribe: http://lists.mysql.com/internals

[shawn l.green]

Hello Mansour,

You are missing the boundaries to your transactions. Please look again
closely at the full sequence you quoted

In a transaction, you insert a row into user, which fires a trigger and
adds a row to audit. That looks like
begin
table maps for user and audit
row inserts for user and audit
commit

Then you repeat the sequence for a second row to the user table.

Can you see it now? Those are two sets of rows in two separate
transactions.

--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN

[Mansour Al Akeel]

Shawn,
Awesome. I really appreciate this. so to be on the same page, this is
a single transaction:

SET TIMESTAMP=1433759876/*!*/;
BEGIN
/*!*/;
# at 24592
#150608 14:37:56 server id 1 end_log_pos 24648 CRC32 0x11bdc8ef

Table_map: `drupal`.`users` mapped to number 101

# at 24648
#150608 14:37:56 server id 1 end_log_pos 24719 CRC32 0xb900e1ef

Table_map: `drupal`.`audits` mapped to number 70

# at 24719
#150608 14:37:56 server id 1 end_log_pos 24793 CRC32 0x2a2bfa39
Write_rows: table id 101
# at 24793
#150608 14:37:56 server id 1 end_log_pos 24897 CRC32 0x36d45fd1

Write_rows: table id 70 flags: STMT_END_F

# at 24897
#150608 14:37:56 server id 1 end_log_pos 24928 CRC32 0x88350518
Xid = 484
COMMIT/*!*/;


Now, my question is can we rely on the order widthin the same commit.
For example,

begin
table map for users and audits
insert row for users and audits
commit

do the insert row events happen in same order as the table_map_events ?
For example, can it be
WRITE_ROW_EVENT for audits, then users ??

Can we rely on the order to match TABLE_MAP_EVENT with WRITE_ROW_EVENT ??

Thank you.