[MDEV-33945] Problem in ignoring the GTD ddl event with START TRANSACTION - Jira

Details

Type: Bug
Status: Open (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.6.17
Fix Version/s: None
Component/s: Replication
Labels:
None
Environment:
RHEL 8

Description

To process the Write_rows-event, our application expects START TRANSACTION, followed by Table_map_event followed by the Commit or XID event. Normally when we create table and perform an insert , the event sequence is ,
ddl(No start transaction) with event flag 29 => query event with CREATE statement => START TRANSACTION => Table_map_event => Write_rows_event => COMMIT/Xid

# at 1033

#240417 10:13:42 server id 1  end_log_pos 1075 CRC32 0xb0848488

# Position

#           |Timestamp   |Type |Master ID   |Size        |Master Pos  |Flags

#      409  |d6 a0 1f 66 |a2   |01 00 00 00 |2a 00 00 00 |33 04 00 00 |08 00

#      41c  9d 00 00 00 00 00 00 00  00 00 00 00 29 00 00 00  |............)...|

#      42c  00 00 00 88 84 84 b0                              |.......|

# Event:        GTID 0-1-157 ddl

/*!100001 SET @@session.gtid_seq_no=157*//*!*/;

# at 1075

#240417 10:13:42 server id 1  end_log_pos 1194 CRC32 0x4b03dd96

# Position

#           |Timestamp   |Type |Master ID   |Size        |Master Pos  |Flags

#      433  |d6 a0 1f 66 |02   |01 00 00 00 |77 00 00 00 |aa 04 00 00 |00 00

#      446  6f 00 00 00 00 00 00 00  00 00 00 23 00 00 00 00  |o..........#....|

#      456  00 01 01 00 00 20 54 00  00 00 00 06 03 73 74 64  |..... T......std|

#      466  04 21 00 21 00 08 00 81  b5 0f 00 00 00 00 00 00  |.!.!............|

#      476  00 63 72 65 61 74 65 20  74 61 62 6c 65 20 61 74  |.create table at|

#      486  73 73 72 63 2e 65 6d 70  32 28 69 64 20 69 6e 74  |ssrc.emp2(id int|

#      496  2c 20 6e 61 6d 65 20 63  68 61 72 28 31 30 29 29  |, name char(10))|

#      4a6  96 dd 03 4b                                       |...K|

# Event:        Query   thread_id=111   exec_time=0     error_code=0    xid=4021

SET TIMESTAMP=1713348822/*!*/;

create table atssrc.emp2(id int, name char(10))

/*!*/;

# at 1194

#240417 10:13:51 server id 1  end_log_pos 1236 CRC32 0xdcc70b50

# Position

#           |Timestamp   |Type |Master ID   |Size        |Master Pos  |Flags

#      4aa  |df a0 1f 66 |a2   |01 00 00 00 |2a 00 00 00 |d4 04 00 00 |08 00

#      4bd  9e 00 00 00 00 00 00 00  00 00 00 00 0c 00 00 00  |................|

#      4cd  00 00 00 50 0b c7 dc                              |...P...|

# Event:        GTID 0-1-158 trans

/*!100001 SET @@session.gtid_seq_no=158*//*!*/;

START TRANSACTION

/*!*/;

# at 1236

#240417 10:13:51 server id 1  end_log_pos 1304 CRC32 0x2c909cfd

# Position

#           |Timestamp   |Type |Master ID   |Size        |Master Pos  |Flags

#      4d4  |df a0 1f 66 |13   |01 00 00 00 |44 00 00 00 |18 05 00 00 |00 00

#      4e7  02 02 00 00 00 00 01 00  06 61 74 73 73 72 63 00  |.........atssrc.|

#      4f7  04 65 6d 70 32 00 02 03  fe 02 fe 0a 03 01 01 00  |.emp2...........|

#      507  02 01 08 04 08 02 69 64  04 6e 61 6d 65 fd 9c 90  |......id.name...|

#      517  2c                                                |,|

# Event:        Table_map: `atssrc`.`emp2` mapped to number 514

# at 1304

#240417 10:13:51 server id 1  end_log_pos 1344 CRC32 0x1ea671be

# Position

#           |Timestamp   |Type |Master ID   |Size        |Master Pos  |Flags

#      518  |df a0 1f 66 |17   |01 00 00 00 |28 00 00 00 |40 05 00 00 |00 00

#      52b  02 02 00 00 00 00 01 00  02 ff fc 01 00 00 00 01  |................|

#      53b  61 be 71 a6 1e                                    |a.q..|

# Event:        Write_rows: table id 514 flags: STMT_END_F

BINLOG '

36AfZhMBAAAARAAAABgFAAAAAAICAAAAAAEABmF0c3NyYwAEZW1wMgACA/4C/goDAQEAAgEIBAgC

aWQEbmFtZf2ckCw=

36AfZhcBAAAAKAAAAEAFAAAAAAICAAAAAAEAAv/8AQAAAAFhvnGmHg==

'/*!*/;

# Number of rows: 1

# at 1344

#240417 10:13:51 server id 1  end_log_pos 1375 CRC32 0x23408d81

# Position

#           |Timestamp   |Type |Master ID   |Size        |Master Pos  |Flags

#      540  |df a0 1f 66 |10   |01 00 00 00 |1f 00 00 00 |5f 05 00 00 |00 00

#      553  b6 0f 00 00 00 00 00 00  81 8d 40 23              |..........@#|

# Event:        Xid = 4022

COMMIT/*!*/;

In certain cases for some users, we are getting the Write_rows_event without getting the START TRANSACTION before that. The events sequence is,
ddl (START TRANSACTION with flag event flag 29) => Query_event with CREATE => Table_map_event => Write_rows_event => Xid / COMMIT

# at 11353452

#240320 11:01:08 server id 1  end_log_pos 11353494 CRC32 0x1872309c

# Position

#           |Timestamp   |Type |Master ID   |Size        |Master Pos  |Flags

#   ad3d6c  |f4 c1 fa 65 |a2   |01 00 00 00 |2a 00 00 00 |96 3d ad 00 |08 00

#   ad3d7f  e0 f6 47 0b 00 00 00 00  00 00 00 00 28 00 00 00  |..G.........(...|

#   ad3d8f  00 00 00 9c 30 72 18                              |....0r.|

# Event:        GTID 0-1-189265632 ddl

/*!100001 SET @@session.gtid_seq_no=189265632*//*!*/;

START TRANSACTION

/*!*/;

# at 11353494

#240320 11:01:08 server id 1  end_log_pos 11357469 CRC32 0xd7743134

# Position

#           |Timestamp   |Type |Master ID   |Size        |Master Pos  |Flags

#   ad3d96  |f4 c1 fa 65 |02   |01 00 00 00 |87 0f 00 00 |1d 4d ad 00 |00 00

#   ad3da9  90 3d 47 00 00 00 00 00  05 00 00 1a 00 00 00 00  |.=G.............|

#   ad3db9  00 00 01 00 00 20 54 00  00 00 00 06 03 73 74 64  |..... T......std|

# ........

# ......

# Event:        Query   thread_id=4668816       exec_time=0     error_code=0    xid=0

SET TIMESTAMP=1710932468/*!*/;

CREATE TABLE `tabInvoice Receipt_BKP` (

  `name` varchar(190) NOT NULL,

.........

/*!*/;

# at 11357469

#240320 11:01:08 server id 1  end_log_pos 11359193 CRC32 0x7eacc934

# Position

#           |Timestamp   |Type |Master ID   |Size        |Master Pos  |Flags

#   ad4d1d  |f4 c1 fa 65 |13   |01 00 00 00 |bc 06 00 00 |d9 53 ad 00 |00 00

#   ad4d30  f6 2d 01 00 00 00 01 00  05 6a 70 70 66 72 00 16  |.-.......jppfr..|

# ....

# ....

# #   ad53d0  5f 66 6c 61 67 34 c9 ac  7e                       |_flag4..~|

# Event:        Table_map: `jppfr`.`tabInvoice Receipt_BKP` mapped to number 77302

...

....

#240320 11:01:08 server id 1  end_log_pos 11367215 CRC32 0xa6cadb95

# Position

#           |Timestamp   |Type |Master ID   |Size        |Master Pos  |Flags

#   ad53d9  |f4 c1 fa 65 |17   |01 00 00 00 |56 1f 00 00 |2f 73 ad 00 |00 00

#   ad53ec  f6 2d 01 00 00 00 00 00  55 ff ff ff ff ff ff ff  |.-......U.......|

...

...

'/*!*/;

# Number of rows: 9761

# at 16406657

#240320 11:01:08 server id 1  end_log_pos 16406688 CRC32 0x28307fa8

# Position

#           |Timestamp   |Type |Master ID   |Size        |Master Pos  |Flags

#   fa5881  |f4 c1 fa 65 |10   |01 00 00 00 |1f 00 00 00 |a0 58 fa 00 |00 00

#   fa5894  04 5b 46 06 00 00 00 00  a8 7f 30 28              |.[F.......0(|

# Event:        Xid = 105274116

COMMIT/*!*/;

There are few differences here in this case than the previous one. In the previous case the GTID ddl event does not have the START TRANSACTION and kind of stand alone DDL with the event flag as 28. I think the difference in the flag is because of of the GTID event flag FL_STANDALONE = 1 for the stand alone DDL , considering all the other parameters same.
And so in this case, the DML events comes as a separate event.

In the later case, the Write_rows_event does not come separately and both the DDL and the DML events both are part of the same transaction. The GTID event ddl is START TRANSACTION in this case.
We want the ddl always come as a stand alone ddl and do not want DDL and DML events under the same transaction.
I tried to look around the documentation but could not figure out clearly what causes the DDL and the DML events in the same transaction.

What causes the DDL and the DML events in the same transaction and how can we make sure that we always get DDL as a standalone ddl event.

Problem in ignoring the GTD ddl event with START TRANSACTION

Details

Description

Attachments

Activity

People

Dates

Git Integration