Bug #93948 XID inconsistency on master-slave with CTAS
Submitted: 16 Jan 8:23 Modified: 8 Feb 4:52
Reporter: Krunal Bauskar (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:8.0.13 OS:Any
Assigned to: CPU Architecture:Any

[16 Jan 8:23] Krunal Bauskar
Description:
When CTAS statement is executed on master and it is replicated on the slave (using async replication link) we see xid inconsistency on master and slave. Starting MySQL-8.0 DDL are atomic so xid is assigned to DDL too. As you can see below xid is assigned to DDL (CREATE PART OF CTAS) on slave but not on master.

Master server

| binlog.000006 |  948 | Query          |       100 |        1078 | use `test`; CREATE TABLE `t1` (
  `i` int(11) NOT NULL
) ENGINE=InnoDB               |
| binlog.000006 | 1078 | Anonymous_Gtid |       100 |        1153 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                 |
| binlog.000006 | 1153 | Query          |       100 |        1230 | BEGIN                                                                                |
| binlog.000006 | 1230 | Table_map      |       100 |        1278 | table_id: 72 (test.t1)                                                               |
| binlog.000006 | 1278 | Write_rows     |       100 |        1323 | table_id: 72 flags: STMT_END_F                                                       |
| binlog.000006 | 1323 | Xid            |       100 |        1354 | COMMIT /* xid=50 */                                                                  |
+---------------+------+----------------+-----------+-------------+--------------------------------------------------------------------------------------+
18 rows in set (0.01 sec)

Slave server

| binlog.000002 | 3425 | Query          |       100 |        3571 | use `test`; CREATE TABLE `t1` (
  `i` int(11) NOT NULL
) ENGINE=InnoDB /* xid=33 */  |
| binlog.000002 | 3571 | Anonymous_Gtid |       100 |        3653 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                 |
| binlog.000002 | 3653 | Query          |       100 |        3725 | BEGIN                                                                                |
| binlog.000002 | 3725 | Table_map      |       100 |        3773 | table_id: 72 (test.t1)                                                               |
| binlog.000002 | 3773 | Write_rows     |       100 |        3818 | table_id: 72 flags: STMT_END_F                                                       |
| binlog.000002 | 3818 | Xid            |       100 |        3849 | COMMIT /* xid=35 */                                                                  |
+---------------+------+----------------+-----------+-------------+--------------------------------------------------------------------------------------+
49 rows in set (0.00 sec)

How to repeat:
Set master-slave and execute following test-case

create database test; use test;
create table src_i (i int, primary key pk(i)) engine=innodb;
insert into src_i values (1), (2);
create table t1 engine=innodb as select * from src_i;

Just check show binlog events on master and slave.
[16 Jan 11:32] Umesh Shastry
Hello Krunal,

Thank you for the report and test case.

regards,
Umesh
[17 Jan 9:52] Sven Sandberg
Posted by developer:
 
It is not a bug that the XID is different on master and slave. In fact, we don't apply the XID on the slave, but we generate a new XID when applying the transaction. It is expected that they are different.

Since you reported a bug, does this affect you in any way? What breaks for you due to this?
[8 Feb 4:13] Krunal Bauskar
I see it is okay to have different xid on master and slave but shouldn't there be semantics consistency. if the slave has xid for create table why not master or say opposite behavior.
[8 Feb 4:52] Krunal Bauskar
I also see the comments hint towards logging complete CTAS with same XID.
(So master behavior seems to be as per design but slave behavior doesn't seems to comply with original design comment or thoughts).

  } else {
    /*
      Note SQLCOM_XA_COMMIT, SQLCOM_XA_ROLLBACK fall into this block.
      Even though CREATE-TABLE sub-statement of CREATE-TABLE-SELECT in
      RBR makes a turn here it is logged atomically with the SELECT
      Rows-log event part that determines the xid of the entire group.
    */
    event_logging_type = Log_event::EVENT_IMMEDIATE_LOGGING;
    event_cache_type = Log_event::EVENT_STMT_CACHE;

    DBUG_ASSERT(ddl_xid == binary_log::INVALID_XID);

    if (thd->rli_slave) thd->rli_slave->ddl_not_atomic = true;
  }