Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-37279

RBR skips engine for CREATE TABLE .. SELECT in ORACLE mode, further replication failure

    XMLWordPrintable

Details

    Description

      The basic problem is this:

      --source include/have_binlog_format_row.inc
      --source include/master-slave.inc
       
      SET sql_mode=ORACLE;
      CREATE TABLE t ENGINE=Aria SELECT 1;
      SHOW BINLOG EVENTS;
       
      --sync_slave_with_master
      SHOW CREATE TABLE t;
       
      --connection master
      DROP TABLE t;
      --source include/rpl_end.inc
      

      main 7215fe789480c228a91f50ff4f047ea89c16f041

      SET sql_mode=ORACLE;
      CREATE TABLE t ENGINE=Aria SELECT 1;
      SHOW BINLOG EVENTS;
      Log_name	Pos	Event_type	Server_id	End_log_pos	Info
      master-bin.000001	4	Format_desc	1	256	Server ver: 12.1.0-MariaDB-asan-debug-log, Binlog ver: 4
      master-bin.000001	256	Gtid_list	1	285	[]
      master-bin.000001	285	Binlog_checkpoint	1	329	master-bin.000001
      master-bin.000001	329	Gtid	1	371	BEGIN GTID 0-1-1
      master-bin.000001	371	Query	1	0	use "test"; CREATE TABLE "t" (
        "1" int(1) NOT NULL
      )
      master-bin.000001	480	Annotate_rows	1	0	CREATE TABLE t ENGINE=Aria SELECT 1
      master-bin.000001	538	Table_map	1	0	table_id: 32 (test.t)
      master-bin.000001	582	Write_rows_v1	1	0	table_id: 32 flags: STMT_END_F
      master-bin.000001	620	Query	1	702	COMMIT
      connection slave;
      SHOW CREATE TABLE t;
      Table	Create Table
      t	CREATE TABLE `t` (
        `1` int(1) NOT NULL
      ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
      

      That is, if the servers are running in ORACLE mode and CREATE .. SELECT uses a non-default engine on the primary, RBR generates the CREATE statements without the engine, according to the rules of ORACLE mode; so when it is executed on the replica, the table is created with the default storage engine.
      It creates an immediate discrepancy, which can soon be followed by replication abort, e.g. if DDL that is allowed for the primary table engine is executed successfully and written to the binlog, but cannot be done with the replica table engine due to its different capabilities.

      Note that it's unimportant that in the test case the sql_mode is only set on the session level on the primary node; it is all the same even if both servers are configured with ORACLE mode from startup, as it happens in real-life setups.

      Attachments

        Activity

          People

            bnestere Brandon Nesterenko
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.