Re: The Order or events TABLE_MAP_EVENT and WRITE_ROW_EVENT

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.



On Mon, Jun 8, 2015 at 8:50 PM, shawn l.green <[email protected]> wrote:
> Hello Mansour,
>
>
> On 6/8/2015 12:05 PM, Mansour Al Akeel wrote:
>>
>> 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.
>>
>
> 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
>
> --
> MySQL Internals Mailing List
> For list archives: http://lists.mysql.com/internals
> To unsubscribe:    http://lists.mysql.com/internals
>

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