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

Oracle-style placeholder inside GROUP BY..WITH ROLLUP breaks replication

    XMLWordPrintable

Details

    Description

      Oracle-style PS placeholder substitution does not work well in queries with the WITH ROLLUP clause.
      As a result, a bad binary log is created and replication breaks.

      I put this file into mysql-test/t/ directory:

      --source include/not_embedded.inc
      --source include/have_binlog_format_statement.inc
       
      --disable_query_log
      reset master; # get rid of previous tests binlog
      --enable_query_log
       
      SET sql_mode=ORACLE;
       
      CREATE TABLE t1 (d DATE);
      INSERT INTO t1 VALUES ('1985-05-13'),('1989-12-24');
      CREATE TABLE t2 (d DATE, c BIGINT);
      DELIMITER $$;
      BEGIN
        EXECUTE IMMEDIATE 'INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, :param' USING 1;
        EXECUTE IMMEDIATE 'INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, :param WITH ROLLUP' USING 1;
      END;
      $$
      DELIMITER ;$$
      DROP TABLE t1,t2;
       
      --let $binlog_file = LAST
       
      source include/show_binlog_events.inc;
      

      and run "mtr" on it.
      It produced the following output:

      TEST                                      RESULT   TIME (ms) or COMMENT
      --------------------------------------------------------------------------
       
      worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 16000..16019
      SET sql_mode=ORACLE;
      CREATE TABLE t1 (d DATE);
      INSERT INTO t1 VALUES ('1985-05-13'),('1989-12-24');
      CREATE TABLE t2 (d DATE, c BIGINT);
      BEGIN
      EXECUTE IMMEDIATE 'INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, :param' USING 1;
      EXECUTE IMMEDIATE 'INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, :param WITH ROLLUP' USING 1;
      END;
      $$
      DROP TABLE t1,t2;
      include/show_binlog_events.inc
      Log_name	Pos	Event_type	Server_id	End_log_pos	Info
      master-bin.000001	#	Gtid	#	#	GTID #-#-#
      master-bin.000001	#	Query	#	#	use `test`; CREATE TABLE t1 (d DATE)
      master-bin.000001	#	Gtid	#	#	BEGIN GTID #-#-#
      master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1 VALUES ('1985-05-13'),('1989-12-24')
      master-bin.000001	#	Query	#	#	COMMIT
      master-bin.000001	#	Gtid	#	#	GTID #-#-#
      master-bin.000001	#	Query	#	#	use `test`; CREATE TABLE t2 (d DATE, c BIGINT)
      master-bin.000001	#	Gtid	#	#	BEGIN GTID #-#-#
      master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, 1
      master-bin.000001	#	Query	#	#	COMMIT
      master-bin.000001	#	Gtid	#	#	BEGIN GTID #-#-#
      master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, 1ROLLUP
      master-bin.000001	#	Query	#	#	COMMIT
      master-bin.000001	#	Gtid	#	#	GTID #-#-#
      master-bin.000001	#	Query	#	#	use `test`; DROP TABLE "t1","t2" /* generated by server */
      main.AAA                                 [ pass ]    154
      --------------------------------------------------------------------------
      The servers were restarted 0 times
      Spent 0.154 of 5 seconds executing testcases
      

      Notice:

      • in the first INSERT query the placeholder was correctly replaced to the constant 1:

        ... GROUP BY d, 1
        

      • in the second INSERT query the placeholder was not replaced correctly:

        ... GROUP BY d, 1ROLLUP
        

      Notice, there is no space between 1 and ROLLUP. This query returns a syntax error and therefore cannot be replayed on the slave side:

      ERROR 1054 (42S22): Unknown column '1ROLLUP' in 'group statement'
      

      Attachments

        Issue Links

          Activity

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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