Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.3(EOL)
-
None
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
- relates to
-
-
- Closed
-
-
-
- Closed
-