|
SP variable 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
|
|
|
CREATE TABLE t1 (d DATE);
|
INSERT INTO t1 VALUES ('1985-05-13'),('1989-12-24');
|
CREATE TABLE t2 (d DATE, c BIGINT);
|
DELIMITER $$;
|
BEGIN NOT ATOMIC
|
DECLARE var INT;
|
INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, var;
|
INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, var WITH ROLLUP;
|
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
|
CREATE TABLE t1 (d DATE);
|
INSERT INTO t1 VALUES ('1985-05-13'),('1989-12-24');
|
CREATE TABLE t2 (d DATE, c BIGINT);
|
BEGIN NOT ATOMIC
|
DECLARE var INT;
|
INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, var;
|
INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, var WITH ROLLUP;
|
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, NAME_CONST('var',NULL)
|
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, var NAME_CONST('var',NULL) ROLLUP
|
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 ] 54
|
--------------------------------------------------------------------------
|
Notice:
- in the first INSERT query the variable var was correctly replaced to the NAME_CONST expression:
... GROUP BY d, NAME_CONST('var',NULL)
|
- in the second INSERT query the variable was not removed and the NAME_CONST expression was put on a wrong place.
... GROUP BY d, var NAME_CONST('var',NULL) ROLLUP
|
The logged query for the second INSERT does not even get parsed:
MariaDB [test]> INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, var NAME_CONST('var',NULL) ROLLUP;
|
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'NAME_CONST('var',NULL) ROLLUP' at line 1
|
|