MySQL Bugs: #93948: XID inconsistency on master-slave with CTAS

Bug #93948	XID inconsistency on master-slave with CTAS
Submitted:	16 Jan 2019 8:23	Modified:	8 Feb 2019 4:52
Reporter:	Krunal Bauskar	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

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.

Hello Krunal,

Thank you for the report and test case.

regards,
Umesh

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?

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.

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;
  }