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
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
|
drop table if exists t1;
reset master;
create table t1 (a int);
prepare s from "insert into t1 values (@a),(?)";
set @a=98;
execute s using @a;
prepare s from "insert into t1 values (?)";
set @a=99;
execute s using @a;
prepare s from "insert into t1 select 100 limit ?";
set @a=100;
execute s using @a;
Warnings:
Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted
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 # # BEGIN GTID #-#-#
master-bin.000001 # User var # # @`a`=98
master-bin.000001 # Query # # use `test`; insert into t1 values (@a),(98)
master-bin.000001 # Query # # COMMIT
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; insert into t1 values (99)
master-bin.000001 # Query # # COMMIT
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; insert into t1 select 100 limit 100
master-bin.000001 # Query # # COMMIT
drop table t1;
#
# MDEV-10709 Expressions as parameters to Dynamic SQL
#
FLUSH LOGS;
SET TIMESTAMP=UNIX_TIMESTAMP('2001-01-02 10:20:30.123456');
CREATE TABLE t1 (a DECIMAL(30,8));
PREPARE stmt FROM 'INSERT INTO t1 VALUES (?)';
EXECUTE stmt USING 10;
EXECUTE stmt USING 11e0;
EXECUTE stmt USING 12.1;
EXECUTE stmt USING '13';
EXECUTE stmt USING CURRENT_DATE;
EXECUTE stmt USING MAKETIME(10,20,30);
EXECUTE stmt USING CURRENT_TIME;
EXECUTE stmt USING CURRENT_TIME(3);
EXECUTE stmt USING CURRENT_TIME(6);
EXECUTE stmt USING CURRENT_TIMESTAMP;
EXECUTE stmt USING CURRENT_TIMESTAMP(3);
EXECUTE stmt USING CURRENT_TIMESTAMP(6);
SELECT * FROM t1;
a
10.00000000
11.00000000
12.10000000
13.00000000
20010102.00000000
102030.00000000
102030.00000000
102030.12300000
102030.12345600
20010102102030.00000000
20010102102030.12300000
20010102102030.12345600
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 (a DECIMAL(30,8))
master-bin.000002 # Gtid # # BEGIN GTID #-#-#
master-bin.000002 # Query # # use `test`; INSERT INTO t1 VALUES (10)
master-bin.000002 # Query # # COMMIT
master-bin.000002 # Gtid # # BEGIN GTID #-#-#
master-bin.000002 # Query # # use `test`; INSERT INTO t1 VALUES (11)
master-bin.000002 # Query # # COMMIT
master-bin.000002 # Gtid # # BEGIN GTID #-#-#
master-bin.000002 # Query # # use `test`; INSERT INTO t1 VALUES (12.1)
master-bin.000002 # Query # # COMMIT
master-bin.000002 # Gtid # # BEGIN GTID #-#-#
master-bin.000002 # Query # # use `test`; INSERT INTO t1 VALUES ('13')
master-bin.000002 # Query # # COMMIT
master-bin.000002 # Gtid # # BEGIN GTID #-#-#
master-bin.000002 # Query # # use `test`; INSERT INTO t1 VALUES (DATE'2001-01-02')
master-bin.000002 # Query # # COMMIT
master-bin.000002 # Gtid # # BEGIN GTID #-#-#
master-bin.000002 # Query # # use `test`; INSERT INTO t1 VALUES (TIME'10:20:30')
master-bin.000002 # Query # # COMMIT
master-bin.000002 # Gtid # # BEGIN GTID #-#-#
master-bin.000002 # Query # # use `test`; INSERT INTO t1 VALUES (TIME'10:20:30')
master-bin.000002 # Query # # COMMIT
master-bin.000002 # Gtid # # BEGIN GTID #-#-#
master-bin.000002 # Query # # use `test`; INSERT INTO t1 VALUES (TIME'10:20:30.123')
master-bin.000002 # Query # # COMMIT
master-bin.000002 # Gtid # # BEGIN GTID #-#-#
master-bin.000002 # Query # # use `test`; INSERT INTO t1 VALUES (TIME'10:20:30.123456')
master-bin.000002 # Query # # COMMIT
master-bin.000002 # Gtid # # BEGIN GTID #-#-#
master-bin.000002 # Query # # use `test`; INSERT INTO t1 VALUES (TIMESTAMP'2001-01-02 10:20:30')
master-bin.000002 # Query # # COMMIT
master-bin.000002 # Gtid # # BEGIN GTID #-#-#
master-bin.000002 # Query # # use `test`; INSERT INTO t1 VALUES (TIMESTAMP'2001-01-02 10:20:30.123')
master-bin.000002 # Query # # COMMIT
master-bin.000002 # Gtid # # BEGIN GTID #-#-#
master-bin.000002 # Query # # use `test`; INSERT INTO t1 VALUES (TIMESTAMP'2001-01-02 10:20:30.123456')
master-bin.000002 # Query # # COMMIT
DROP TABLE t1;
SET TIMESTAMP=DEFAULT;
#
# MDEV-10585 EXECUTE IMMEDIATE statement
#
FLUSH LOGS;
CREATE TABLE t1 (a INT);
EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (101)';
SET @a=102;
EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (?)' USING @a;
SET @a=103;
SET @stmt='INSERT INTO t1 VALUES (?)';
EXECUTE IMMEDIATE @stmt USING @a;
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000003 # Binlog_checkpoint # # master-bin.000003
master-bin.000003 # Gtid # # GTID #-#-#
master-bin.000003 # Query # # use `test`; CREATE TABLE t1 (a INT)
master-bin.000003 # Gtid # # BEGIN GTID #-#-#
master-bin.000003 # Query # # use `test`; INSERT INTO t1 VALUES (101)
master-bin.000003 # Query # # COMMIT
master-bin.000003 # Gtid # # BEGIN GTID #-#-#
master-bin.000003 # Query # # use `test`; INSERT INTO t1 VALUES (102)
master-bin.000003 # Query # # COMMIT
master-bin.000003 # Gtid # # BEGIN GTID #-#-#
master-bin.000003 # Query # # use `test`; INSERT INTO t1 VALUES (103)
master-bin.000003 # Query # # COMMIT
DROP TABLE t1;
#
# MDEV-11360 Dynamic SQL: DEFAULT as a bind parameter
#
FLUSH LOGS;
CREATE TABLE t1 (a INT DEFAULT 10);
EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (Default)';
# The output of this query in 'Note' is a syntactically incorrect query.
# But as it's never logged, it's ok. It should be human readable only.
EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT ?' USING Default;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
Note 1003 select default AS `?`
EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (?)' USING Default;
CREATE PROCEDURE p1 ()
BEGIN
INSERT INTO t1 VALUES (Default);
# EXPLAIN should not be logged
EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT ?' USING Default;
EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (?)' USING Default;
END;
$$
CALL p1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
DROP PROCEDURE p1;
DROP TABLE t1;
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000004 # Binlog_checkpoint # # master-bin.000004
master-bin.000004 # Gtid # # GTID #-#-#
master-bin.000004 # Query # # use `test`; CREATE TABLE t1 (a INT DEFAULT 10)
master-bin.000004 # Gtid # # BEGIN GTID #-#-#
master-bin.000004 # Query # # use `test`; INSERT INTO t1 VALUES (Default)
master-bin.000004 # Query # # COMMIT
master-bin.000004 # Gtid # # BEGIN GTID #-#-#
master-bin.000004 # Query # # use `test`; INSERT INTO t1 VALUES (DEFAULT)
master-bin.000004 # Query # # COMMIT
master-bin.000004 # Gtid # # GTID #-#-#
master-bin.000004 # Query # # use `test`; CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`()
BEGIN
INSERT INTO t1 VALUES (Default);
# EXPLAIN should not be logged
EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT ?' USING Default;
EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (?)' USING Default;
END
master-bin.000004 # Gtid # # BEGIN GTID #-#-#
master-bin.000004 # Query # # use `test`; INSERT INTO t1 VALUES (Default)
master-bin.000004 # Query # # COMMIT
master-bin.000004 # Gtid # # BEGIN GTID #-#-#
master-bin.000004 # Query # # use `test`; INSERT INTO t1 VALUES (DEFAULT)
master-bin.000004 # Query # # COMMIT
master-bin.000004 # Gtid # # GTID #-#-#
master-bin.000004 # Query # # use `test`; DROP PROCEDURE p1
master-bin.000004 # Gtid # # GTID #-#-#
master-bin.000004 # Query # # use `test`; DROP TABLE `t1` /* generated by server */
#
#MDEV-14467 Item_param: replace {INT|DECIMAL|REAL|STRING|TIME}_VALUE with Type_handler
#
FLUSH LOGS;
CREATE TABLE t1 (a INT);
EXECUTE IMMEDIATE 'INSERT INTO t1 SELECT 1 LIMIT ?' USING 10;
Warnings:
Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted
EXECUTE IMMEDIATE 'INSERT INTO t1 SELECT 1 LIMIT ?' USING 10.1;
Warnings:
Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted
EXECUTE IMMEDIATE 'INSERT INTO t1 SELECT 1 LIMIT ?' USING 10.1e0;
Warnings:
Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted
EXECUTE IMMEDIATE 'INSERT INTO t1 SELECT 1 LIMIT ?' USING '10';
Warnings:
Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted
EXECUTE IMMEDIATE 'INSERT INTO t1 SELECT 1 LIMIT ?' USING TIME'10:10:10';
Warnings:
Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted
DROP TABLE t1;
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000005 # Binlog_checkpoint # # master-bin.000005
master-bin.000005 # Gtid # # GTID #-#-#
master-bin.000005 # Query # # use `test`; CREATE TABLE t1 (a INT)
master-bin.000005 # Gtid # # BEGIN GTID #-#-#
master-bin.000005 # Query # # use `test`; INSERT INTO t1 SELECT 1 LIMIT 10
master-bin.000005 # Query # # COMMIT
master-bin.000005 # Gtid # # BEGIN GTID #-#-#
master-bin.000005 # Query # # use `test`; INSERT INTO t1 SELECT 1 LIMIT 10
master-bin.000005 # Query # # COMMIT
master-bin.000005 # Gtid # # BEGIN GTID #-#-#
master-bin.000005 # Query # # use `test`; INSERT INTO t1 SELECT 1 LIMIT 10
master-bin.000005 # Query # # COMMIT
master-bin.000005 # Gtid # # BEGIN GTID #-#-#
master-bin.000005 # Query # # use `test`; INSERT INTO t1 SELECT 1 LIMIT 10
master-bin.000005 # Query # # COMMIT
master-bin.000005 # Gtid # # BEGIN GTID #-#-#
master-bin.000005 # Query # # use `test`; INSERT INTO t1 SELECT 1 LIMIT 101010
master-bin.000005 # Query # # COMMIT
master-bin.000005 # Gtid # # GTID #-#-#
master-bin.000005 # Query # # use `test`; DROP TABLE `t1` /* generated by server */
|
