1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
|
#
# MDEV-11815 SP variables of temporal data types do not replicate correctly
#
CREATE TABLE t1(a INT);
CREATE PROCEDURE p1()
BEGIN
DECLARE i INT DEFAULT 123;
DECLARE b8 BIT(8) DEFAULT 0x61;
DECLARE t0 TIME DEFAULT '01:01:01';
DECLARE t6 TIME(6) DEFAULT '01:01:01.123456';
DECLARE d DATE DEFAULT '2001-01-01';
DECLARE dt0 DATETIME DEFAULT '2001-01-01 01:01:01';
DECLARE dt6 DATETIME(6) DEFAULT '2001-01-01 01:01:01.123456';
DECLARE ts0 TIMESTAMP DEFAULT '2001-01-01 01:01:01';
DECLARE ts6 TIMESTAMP(6) DEFAULT '2001-01-01 01:01:01.123456';
INSERT INTO t1 VALUES (i=0x61);
INSERT INTO t1 VALUES (b8=0x61);
INSERT INTO t1 VALUES (t0=10101);
INSERT INTO t1 VALUES (t6=10101);
INSERT INTO t1 VALUES (d=20010101);
INSERT INTO t1 VALUES (dt0=20010101010101);
INSERT INTO t1 VALUES (dt6=20010101010101);
INSERT INTO t1 VALUES (ts0=20010101010101);
INSERT INTO t1 VALUES (ts6=20010101010101);
END;
$$
CALL p1;
DROP TABLE t1;
DROP PROCEDURE p1;
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(a INT)
master-bin.000001 # Gtid # # GTID #-#-#
master-bin.000001 # Query # # use `test`; CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`()
BEGIN
DECLARE i INT DEFAULT 123;
DECLARE b8 BIT(8) DEFAULT 0x61;
DECLARE t0 TIME DEFAULT '01:01:01';
DECLARE t6 TIME(6) DEFAULT '01:01:01.123456';
DECLARE d DATE DEFAULT '2001-01-01';
DECLARE dt0 DATETIME DEFAULT '2001-01-01 01:01:01';
DECLARE dt6 DATETIME(6) DEFAULT '2001-01-01 01:01:01.123456';
DECLARE ts0 TIMESTAMP DEFAULT '2001-01-01 01:01:01';
DECLARE ts6 TIMESTAMP(6) DEFAULT '2001-01-01 01:01:01.123456';
INSERT INTO t1 VALUES (i=0x61);
INSERT INTO t1 VALUES (b8=0x61);
INSERT INTO t1 VALUES (t0=10101);
INSERT INTO t1 VALUES (t6=10101);
INSERT INTO t1 VALUES (d=20010101);
INSERT INTO t1 VALUES (dt0=20010101010101);
INSERT INTO t1 VALUES (dt6=20010101010101);
INSERT INTO t1 VALUES (ts0=20010101010101);
INSERT INTO t1 VALUES (ts6=20010101010101);
END
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES ( NAME_CONST('i',123)=0x61)
master-bin.000001 # Query # # COMMIT
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES ( NAME_CONST('b8',_binary'a' COLLATE 'binary')=0x61)
master-bin.000001 # Query # # COMMIT
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES ( NAME_CONST('t0',TIME'01:01:01')=10101)
master-bin.000001 # Query # # COMMIT
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES ( NAME_CONST('t6',TIME'01:01:01.123456')=10101)
master-bin.000001 # Query # # COMMIT
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES ( NAME_CONST('d',DATE'2001-01-01')=20010101)
master-bin.000001 # Query # # COMMIT
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES ( NAME_CONST('dt0',TIMESTAMP'2001-01-01 01:01:01')=20010101010101)
master-bin.000001 # Query # # COMMIT
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES ( NAME_CONST('dt6',TIMESTAMP'2001-01-01 01:01:01.123456')=20010101010101)
master-bin.000001 # Query # # COMMIT
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES ( NAME_CONST('ts0',TIMESTAMP'2001-01-01 01:01:01')=20010101010101)
master-bin.000001 # Query # # COMMIT
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES ( NAME_CONST('ts6',TIMESTAMP'2001-01-01 01:01:01.123456')=20010101010101)
master-bin.000001 # Query # # COMMIT
master-bin.000001 # Gtid # # GTID #-#-#
master-bin.000001 # Query # # use `test`; DROP TABLE `t1` /* generated by server */
master-bin.000001 # Gtid # # GTID #-#-#
master-bin.000001 # Query # # use `test`; DROP PROCEDURE p1
#
# MDEV-16020 SP variables inside GROUP BY..WITH ROLLUP break replication
#
FLUSH LOGS;
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
BEGIN
DECLARE var INT DEFAULT 10;
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;
BEGIN
DECLARE atomic INT DEFAULT 20;
INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, atomic;
INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, atomic WITH ROLLUP;
INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, `atomic`;
INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, `atomic` WITH ROLLUP;
END;
BEGIN
DECLARE atomic ROW (atomic INT, xxx INT) DEFAULT (31,32);
INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, atomic.atomic;
INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, atomic.atomic WITH ROLLUP;
INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, `atomic`.`atomic`;
INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, `atomic`.`atomic` WITH ROLLUP;
INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, atomic.xxx;
INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, atomic.xxx WITH ROLLUP;
INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, `atomic`.`xxx`;
INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, `atomic`.`xxx` WITH ROLLUP;
END;
END;
$$
DROP TABLE t1,t2;
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000002 # Binlog_checkpoint # # master-bin.000002
master-bin.000002 # Gtid # # GTID #-#-#
master-bin.000002 # Query # # use `test`; CREATE TABLE t1 (d DATE)
master-bin.000002 # Gtid # # BEGIN GTID #-#-#
master-bin.000002 # Query # # use `test`; INSERT INTO t1 VALUES ('1985-05-13'),('1989-12-24')
master-bin.000002 # Query # # COMMIT
master-bin.000002 # Gtid # # GTID #-#-#
master-bin.000002 # Query # # use `test`; CREATE TABLE t2 (d DATE, c BIGINT)
master-bin.000002 # Gtid # # BEGIN GTID #-#-#
master-bin.000002 # Query # # use `test`; INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, NAME_CONST('var',10)
master-bin.000002 # Query # # COMMIT
master-bin.000002 # Gtid # # BEGIN GTID #-#-#
master-bin.000002 # Query # # use `test`; INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, NAME_CONST('var',10) WITH ROLLUP
master-bin.000002 # Query # # COMMIT
master-bin.000002 # Gtid # # BEGIN GTID #-#-#
master-bin.000002 # Query # # use `test`; INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, NAME_CONST('atomic',20)
master-bin.000002 # Query # # COMMIT
master-bin.000002 # Gtid # # BEGIN GTID #-#-#
master-bin.000002 # Query # # use `test`; INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, NAME_CONST('atomic',20) WITH ROLLUP
master-bin.000002 # Query # # COMMIT
master-bin.000002 # Gtid # # BEGIN GTID #-#-#
master-bin.000002 # Query # # use `test`; INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, NAME_CONST('atomic',20)
master-bin.000002 # Query # # COMMIT
master-bin.000002 # Gtid # # BEGIN GTID #-#-#
master-bin.000002 # Query # # use `test`; INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, NAME_CONST('atomic',20) WITH ROLLUP
master-bin.000002 # Query # # COMMIT
master-bin.000002 # Gtid # # BEGIN GTID #-#-#
master-bin.000002 # Query # # use `test`; INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, NAME_CONST('atomic.atomic',31)
master-bin.000002 # Query # # COMMIT
master-bin.000002 # Gtid # # BEGIN GTID #-#-#
master-bin.000002 # Query # # use `test`; INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, NAME_CONST('atomic.atomic',31) WITH ROLLUP
master-bin.000002 # Query # # COMMIT
master-bin.000002 # Gtid # # BEGIN GTID #-#-#
master-bin.000002 # Query # # use `test`; INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, NAME_CONST('atomic.atomic',31)
master-bin.000002 # Query # # COMMIT
master-bin.000002 # Gtid # # BEGIN GTID #-#-#
master-bin.000002 # Query # # use `test`; INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, NAME_CONST('atomic.atomic',31) WITH ROLLUP
master-bin.000002 # Query # # COMMIT
master-bin.000002 # Gtid # # BEGIN GTID #-#-#
master-bin.000002 # Query # # use `test`; INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, NAME_CONST('atomic.xxx',32)
master-bin.000002 # Query # # COMMIT
master-bin.000002 # Gtid # # BEGIN GTID #-#-#
master-bin.000002 # Query # # use `test`; INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, NAME_CONST('atomic.xxx',32) WITH ROLLUP
master-bin.000002 # Query # # COMMIT
master-bin.000002 # Gtid # # BEGIN GTID #-#-#
master-bin.000002 # Query # # use `test`; INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, NAME_CONST('atomic.xxx',32)
master-bin.000002 # Query # # COMMIT
master-bin.000002 # Gtid # # BEGIN GTID #-#-#
master-bin.000002 # Query # # use `test`; INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, NAME_CONST('atomic.xxx',32) WITH ROLLUP
master-bin.000002 # Query # # COMMIT
master-bin.000002 # Gtid # # GTID #-#-#
master-bin.000002 # Query # # use `test`; DROP TABLE `t1`,`t2` /* generated by server */
|
