1 files changed, 227 insertions, 0 deletions
diff --git a/mysql-test/main/ps_missed_cmds_bin_prot.result b/mysql-test/main/ps_missed_cmds_bin_prot.result
new file mode 100644
index 00000000000..0d75d2b84fc
--- /dev/null
+++ b/mysql-test/main/ps_missed_cmds_bin_prot.result
@@ -0,0 +1,227 @@
+#
+# 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
+# Test Test case 29: Check the statements 'CREATE/ALTER/DROP TABLEPSPACE'
+# can be executed as a prepared statement
+CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENGINE=InnoDB;
+ALTER TABLESPACE ts1 ADD DATAFILE 'ts1_1.ibd' ENGINE=InnoDB;
+DROP TABLESPACE ts1 ENGINE=InnoDB;
+SET default_storage_engine= @save_storage_engine;