Re: Query with AFTER UPDATE trigger not visible in binary logs

[David Raimosson]

Hi!

Thanks for your swift reply!

I don't think the documentation you refer to gives an answer to any of my questions. It does however explain the difference in behavior for the binary log when switching between STATEMENT- and ROW-based logging, which I tried on a development machine.

For statement based logging only the query activating the trigger is registered in the binary log.
For row based logging one should see the row updates for both the query activating the trigger and the queries executed within the trigger. This works fine for AFTER INSERT triggers, but not for AFTER UPDATE triggers, it seems. I think all such information is really in the binlog, it's just not being extracted into human-readable format by the mysqlbinlog tool.

This is an example from our production server binlog, btw, with some data masked out:

--------------------------------------------
BINLOG EXTRACTION - Production example START
--------------------------------------------
# at 64022210
#170719 11:33:01 server id 100 end_log_pos 64022303 CRC32 0xec92272d Query thread_id=3837905 exec_time=0 error_code=0
SET TIMESTAMP=1500456781/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
BEGIN
/*!*/;
# at 64022303
#170719 11:33:01 server id 100 end_log_pos 64022458 CRC32 0x6ea2e1aa Table_map: `---`.`tbl_A` mapped to number 77
# at 64022458
#170719 11:33:01 server id 100 end_log_pos 64022526 CRC32 0x5ea1a025 Table_map: `---`.`tbl_B` mapped to number 70
# at 64022526
#170719 11:33:01 server id 100 end_log_pos 64022573 CRC32 0x12c12357 Write_rows: table id 70 flags: STMT_END_F

BINLOG '
TSdvWRNkAAAAmwAAALrn0AMAAE0AAAAAAAMAEnByZWZpbGxlZGF1dG9tYXRlbgAGbWVtYmVyADMD
A/4PDw8PDxIDDwEDBAEBAQQEAwESEgEBAQEBAQMBAQMBD/z8Aw8P/A8DAwMSEhIBDwMk/gEMAGQA
MgAyAPoAABQABAQEAAD6AAICMgAyAAIyAAAAAP8AOrX+f/3fBqrhom4=
TSdvWRNkAAAARAAAAP7n0AMAAEYAAAAAAAMAEnByZWZpbGxlZGF1dG9tYXRlbgAIc3luY2RhdGEA
Ag8DAjIAACWgoV4=
TSdvWR5kAAAALwAAAC3o0AMAAEYAAAAAAAEAAgAC//wGbWVtYmVyBcgMAFcjwRI=
'/*!*/;
### INSERT INTO `---`.`tbl_B`
### SET
### @1='tbl_A' /* VARSTRING(50) meta=50 nullable=0 is_null=0 */
### @2=-integer_ID- /* INT meta=0 nullable=0 is_null=0 */
# at 64022573
#170719 11:33:01 server id 100 end_log_pos 64022604 CRC32 0x7e9f2998 Xid = 272463013
COMMIT/*!*/;

--------------------------------------------
BINLOG EXTRACTION - Production example END
--------------------------------------------

As you can see, the query mapped to "tbl_A" is not displayed in the output, only the query executed as a result of the trigger, mapped to "tbl_B".

Best regards

David