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
|
#
# MDEV-16708: Unsupported commands for prepared statements
#
SET @save_storage_engine= @@default_storage_engine;
SET default_storage_engine= InnoDB;
# Test case 1: Check that the statement 'LOAD DATA' is supported
# by prepared statements
# First, set up environment for use by the statement 'LOAD DATA'
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1);
COMMIT;
SELECT * INTO OUTFILE 'load.data' FROM t1;
LOAD DATA INFILE 'load.data' INTO TABLE t1;
SELECT * FROM t1;
a
1
1
# Clean up
DROP TABLE t1;
# Test case 2: Check that the statements 'LOCK TABLE', 'UNLOCK TABLES'
# are supported by prepared statements
CREATE TABLE t1 (a INT);
LOCK TABLE t1 READ;
UNLOCK TABLE;
LOCK TABLE t1 WRITE;
# Clean up
UNLOCK TABLE;
DROP TABLE t1;
# Test case 3: Check that the statement 'USE' is supported by
# prepared statements
CREATE DATABASE mdev_16708_db;
USE mdev_16708_db;
# Check that the current database has been changed
SELECT DATABASE();
DATABASE()
mdev_16708_db
# Clean up
USE test;
DROP DATABASE mdev_16708_db;
# Test case 4: Check that the statement 'ALTER DATABASE' is supported
# by prepared statements
CREATE DATABASE mdev_16708_db;
ALTER DATABASE mdev_16708_db COMMENT 'New comment on database';
# Clean up
DROP DATABASE mdev_16708_db;
# Test case 5: Check that the statements 'CREATE FUNCTION/ALTER FUNCTION/
# DROP FUNCTION' are supported by prepared statements
CREATE FUNCTION f1() RETURNS INT RETURN 1;
ALTER FUNCTION f1 SQL SECURITY INVOKER;
DROP FUNCTION f1;
# Test case 6: Check that the statements 'CHECK TABLE' is supported
# by prepared statements
CREATE TABLE t1 (a INT);
CHECK TABLE t1;
Table Op Msg_type Msg_text
test.t1 check status OK
# Clean up
DROP TABLE t1;
# Test case 7: Check that the statements BEGIN/SAVEPOINT/
# RELEASE SAVEPOINT is supported by prepared statements
# Set up environmentr for the test case
CREATE TABLE t1 (a INT);
BEGIN;
INSERT INTO t1 VALUES (1);
SAVEPOINT s1;
INSERT INTO t1 VALUES (2);
# Expected rows: '1' and '2'
SELECT * FROM t1;
a
1
2
# Rollback the last row inserted ('2')
ROLLBACK TO SAVEPOINT s1;
# Expected output from t1 after transaction was rolled back
# to the savepoint is '1'. If it is case then the statement SAVEPOINT
# was handled successfully with prepared statement
SELECT * FROM t1;
a
1
RELEASE SAVEPOINT s1;
# Clean up
DROP TABLE t1;
# Test case 8: Check that the statements 'PURGE BINARY LOGS BEFORE'
# is supported by prepared statements
PURGE BINARY LOGS BEFORE '2020-11-17';
# Check that the statements 'PURGE BINARY LOGS TO' is supported by
# prepared statements
PURGE BINARY LOGS TO 'mariadb-bin.000063';
# Test case 9: Check that the statements 'HANDLER OPEN/HANDLER READ/
# HANDLER CLOSE' are supported by prepared statements
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (1);
COMMIT;
HANDLER t1 OPEN;
HANDLER t1 READ FIRST;
a
1
HANDLER t1 READ NEXT;
a
1
HANDLER t1 CLOSE;
# Clean up
DROP TABLE t1;
# Test case 10: Check that the statements 'HELP'
# is supported by prepared statements
INSERT INTO mysql.help_topic VALUES (0, 'Tamagotchi', 0, 'This digital pet is not a KB article', 'no example', 'https://tamagotchi.com/');
HELP `Tamagotchi`;
name description example
Tamagotchi This digital pet is not a KB article no example
DELETE FROM mysql.help_topic WHERE help_topic_id = 0;
# Test case 11: Check that the statements CREATE/ALTER/DROP PROCEDURE
# are supported by prepared statements
CREATE PROCEDURE p1() SET @a=1;
ALTER PROCEDURE p1 SQL SECURITY INVOKER;
DROP PROCEDURE p1;
# Test case 12: Check that the statement 'CALL' is supported
# by prepared statements.
CREATE PROCEDURE p1() SET @a=1;
CALL p1();
# Check that the @a variable has been set
SELECT @a;
@a
1
DROP PROCEDURE p1;
# Test case 13: Check that the statements PREPARE FROM/EXECUTE/
# DEALLOCAT PREPARE can be executed as prepared statements.
PREPARE stmt_1 FROM 'SELECT 1';
# Now execute the prepared statement with the name stmt_1
# It is expected that output contains the single row '1'
EXECUTE stmt_1;
1
1
DEALLOCATE PREPARE stmt_1;
# Test case 14: Check that the statement 'CREATE VIEW' can be executed
# as a prepared statement.
# Create environment for the test case
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1);
COMMIT;
CREATE VIEW v1 AS SELECT * FROM t1;
# Query the view. Expected result is the row '1'
SELECT * FROM v1;
a
1
# Clean up
DROP VIEW v1;
DROP TABLE t1;
# Test case 15: Check that the statements CREATE/DROP TRIGGER can be executed
# as prepared statements.
CREATE TABLE t1 (a INT);
CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW SET @a=1;
DROP TRIGGER trg1;
DROP TABLE t1;
# Test case 16: Check that XA related SQL statements can be executed in
# as prepared statements.
# Create the table t1 used by XA transaction.
CREATE TABLE t1 (a INT);
XA START 'xid1';
INSERT INTO t1 VALUES (1);
XA END 'xid1';
XA PREPARE 'xid1';
XA RECOVER;
formatID gtrid_length bqual_length data
1 4 0 xid1
XA COMMIT 'xid1';
# Query the table t1 to check that it contains a record inserted by XA
# transaction just committed.
SELECT * FROM t1;
a
1
# Check that XA ROLLBACK is supported by prepared statements
# First, clean up the table t1 that was filled by just
# committed XA transaction
TRUNCATE TABLE t1;
XA START 'xid1';
INSERT INTO t1 VALUES (1);
XA END 'xid1';
XA PREPARE 'xid1';
XA RECOVER;
formatID gtrid_length bqual_length data
1 4 0 xid1
XA ROLLBACK 'xid1';
# Query the table t1 to check that it doesn't contain a record
# inserted by XA transaction just rollbacked.
SELECT * FROM t1;
a
# Clean up
DROP TABLE t1;
# Test case 17: Check that the statements CREATE SERVER/ALTER SERVER/
# DROP SERVER can be executed
# as a prepared statement.
CREATE SERVER s FOREIGN DATA WRAPPER mysql OPTIONS (USER 'u1', HOST '127.0.0.1');
ALTER SERVER s OPTIONS (USER 'u2');
DROP SERVER s;
# Test Test case 21: Check the statements 'BACKUP'/'BACKUP STAGE'
# can be executed as a prepared statement
CREATE TABLE t1 (a INT);
BACKUP LOCK t1;
BACKUP UNLOCK;
BACKUP STAGE START;
BACKUP STAGE BLOCK_COMMIT;
BACKUP STAGE END;
DROP TABLE t1;
# Test case 22: Check the the statement 'GET DIAGNOSTICS'
# can be executed as a prepared statement
# Query from non existent table to fill the diagnostics area with information
SELECT * FROM non_existent_table_1;
ERROR 42S02: Table 'test.non_existent_table_1' doesn't exist
GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
# Check that information from diagnostics area has been retrieved
SELECT @sqlstate, @errno, @text;
@sqlstate @errno @text
42S02 1146 Table 'test.non_existent_table_1' doesn't exist
# Clean up
# Test case 23: Check that the statements SIGNAL and RESIGNAL can be executed as
# a prepared statement
SIGNAL SQLSTATE '45000' SET MYSQL_ERRNO=30001, MESSAGE_TEXT='Hello, world!';
ERROR 45000: Hello, world!
RESIGNAL SET MESSAGE_TEXT = 'New error message';
ERROR 0K000: RESIGNAL when handler not active
SET default_storage_engine= @save_storage_engine;
|
