1 files changed, 261 insertions, 0 deletions
diff --git a/mysql-test/main/ps_missed_cmds_bin_prot.test b/mysql-test/main/ps_missed_cmds_bin_prot.test
new file mode 100644
index 00000000..954ffb1b
--- /dev/null
+++ b/mysql-test/main/ps_missed_cmds_bin_prot.test
@@ -0,0 +1,261 @@
+--echo #
+--echo # MDEV-16708: Unsupported commands for prepared statements
+--echo #
+
+if (`SELECT $PS_PROTOCOL = 0`)
+{
+   --skip Need ps-protocol
+}
+
+--source include/have_innodb.inc
+
+SET @save_storage_engine= @@default_storage_engine;
+SET default_storage_engine= InnoDB;
+
+--echo # Test case 1: Check that the statement 'LOAD DATA' is supported
+--echo # by prepared statements
+
+--echo # First, set up environment for use by the statement 'LOAD DATA'
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1);
+COMMIT;
+--disable_ps2_protocol
+SELECT * INTO OUTFILE 'load.data' FROM t1;
+--enable_ps2_protocol
+
+LOAD DATA INFILE 'load.data' INTO TABLE t1;
+SELECT * FROM t1;
+--echo # Clean up
+DROP TABLE t1;
+--let $datadir= `select @@datadir`
+--remove_file $datadir/test/load.data
+
+--echo # Test case 2: Check that the statements 'LOCK TABLE', 'UNLOCK TABLES'
+--echo # are supported by prepared statements
+CREATE TABLE t1 (a INT);
+
+LOCK TABLE t1 READ;
+UNLOCK TABLE;
+
+LOCK TABLE t1 WRITE;
+--echo # Clean up
+UNLOCK TABLE;
+DROP TABLE t1;
+
+--echo # Test case 3: Check that the statement 'USE' is supported by
+--echo # prepared statements
+
+CREATE DATABASE mdev_16708_db;
+USE mdev_16708_db;
+
+--echo # Check that the current database has been changed
+SELECT DATABASE();
+
+--echo # Clean up
+USE test;
+DROP DATABASE mdev_16708_db;
+
+--echo # Test case 4: Check that the statement 'ALTER DATABASE' is supported
+--echo # by prepared statements
+CREATE DATABASE mdev_16708_db;
+ALTER DATABASE mdev_16708_db COMMENT 'New comment on database';
+
+--echo # Clean up
+DROP DATABASE mdev_16708_db;
+
+--echo # Test case 5: Check that the statements 'CREATE FUNCTION/ALTER FUNCTION/
+--echo # DROP FUNCTION' are supported by prepared statements
+CREATE FUNCTION f1() RETURNS INT RETURN 1;
+
+ALTER FUNCTION f1 SQL SECURITY INVOKER;
+
+DROP FUNCTION f1;
+
+--echo # Test case 6: Check that the statements 'CHECK TABLE' is supported
+--echo # by prepared statements
+CREATE TABLE t1 (a INT);
+CHECK TABLE t1;
+--echo # Clean up
+DROP TABLE t1;
+
+--echo # Test case 7: Check that the statements BEGIN/SAVEPOINT/
+--echo # RELEASE SAVEPOINT is supported by prepared statements
+
+--echo # 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);
+--echo # Expected rows: '1' and '2'
+SELECT * FROM t1;
+--echo # Rollback the last row inserted ('2')
+ROLLBACK TO SAVEPOINT s1;
+--echo # Expected output from t1 after transaction was rolled back
+--echo # to the savepoint is '1'. If it is case then the statement SAVEPOINT
+--echo # was handled successfully with prepared statement
+SELECT * FROM t1;
+
+RELEASE SAVEPOINT s1;
+
+--echo # Clean up
+DROP TABLE t1;
+
+--echo # Test case 8: Check that the statements 'PURGE BINARY LOGS BEFORE'
+--echo # is supported by prepared statements
+PURGE BINARY LOGS BEFORE '2020-11-17';
+
+--echo # Check that the statements 'PURGE BINARY LOGS TO' is supported by
+--echo # prepared statements
+PURGE BINARY LOGS TO 'mariadb-bin.000063';
+
+--echo # Test case 9: Check that the statements 'HANDLER OPEN/HANDLER READ/
+--echo # 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;
+HANDLER t1 READ NEXT;
+HANDLER t1 CLOSE;
+
+--echo # Clean up
+DROP TABLE t1;
+
+--echo # Test case 10: Check that the statements 'HELP'
+--echo # is supported by prepared statements
+# avoid existing articles that may get updated.
+INSERT INTO mysql.help_topic VALUES (0, 'Tamagotchi', 0, 'This digital pet is not a KB article', 'no example', 'https://tamagotchi.com/');
+HELP `Tamagotchi`;
+DELETE FROM mysql.help_topic WHERE help_topic_id = 0;
+
+--echo # Test case 11: Check that the statements CREATE/ALTER/DROP PROCEDURE
+--echo # are supported by prepared statements
+CREATE PROCEDURE p1() SET @a=1;
+ALTER PROCEDURE p1 SQL SECURITY INVOKER;
+DROP PROCEDURE p1;
+
+--echo # Test case 12: Check that the statement 'CALL' is supported
+--echo # by prepared statements.
+
+CREATE PROCEDURE p1() SET @a=1;
+CALL p1();
+
+--echo # Check that the @a variable has been set
+SELECT @a;
+DROP PROCEDURE p1;
+
+--echo # Test case 13: Check that the statements PREPARE FROM/EXECUTE/
+--echo # DEALLOCAT PREPARE can be executed as prepared statements.
+PREPARE stmt_1 FROM 'SELECT 1';
+
+--echo # Now execute the prepared statement with the name stmt_1
+--echo # It is expected that output contains the single row '1'
+EXECUTE stmt_1;
+
+DEALLOCATE PREPARE stmt_1;
+
+--echo # Test case 14: Check that the statement 'CREATE VIEW' can be executed
+--echo # as a prepared statement.
+--echo # Create environment for the test case
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1);
+COMMIT;
+
+CREATE VIEW v1 AS SELECT * FROM t1;
+--echo # Query the view. Expected result is the row '1'
+SELECT * FROM v1;
+--echo # Clean up
+DROP VIEW v1;
+DROP TABLE t1;
+
+--echo # Test case 15: Check that the statements CREATE/DROP TRIGGER can be executed
+--echo # 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;
+
+--echo # Test case 16: Check that XA related SQL statements can be executed in
+--echo # as prepared statements.
+--echo # 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;
+XA COMMIT 'xid1';
+--echo # Query the table t1 to check that it contains a record inserted by XA
+--echo # transaction just committed.
+SELECT * FROM t1;
+
+--echo # Check that XA ROLLBACK is supported by prepared statements
+
+--echo # First, clean up the table t1 that was filled by just
+--echo # committed XA transaction
+TRUNCATE TABLE t1;
+XA START 'xid1';
+INSERT INTO t1 VALUES (1);
+XA END 'xid1';
+XA PREPARE 'xid1';
+XA RECOVER;
+XA ROLLBACK 'xid1';
+
+--echo # Query the table t1 to check that it doesn't contain a record
+--echo # inserted by XA transaction just rollbacked.
+SELECT * FROM t1;
+
+--echo # Clean up
+DROP TABLE t1;
+
+--echo # Test case 17: Check that the statements CREATE SERVER/ALTER SERVER/
+--echo # DROP SERVER can be executed
+--echo # 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;
+
+--echo # Test Test case 21: Check the statements 'BACKUP'/'BACKUP STAGE'
+--echo # 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;
+
+--echo # Test case 22: Check the the statement 'GET DIAGNOSTICS'
+--echo # can be executed as a prepared statement
+
+--echo # Query from non existent table to fill the diagnostics area with information
+--error ER_NO_SUCH_TABLE
+SELECT * FROM non_existent_table_1;
+GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
+--echo # Check that information from diagnostics area has been retrieved
+SELECT @sqlstate, @errno, @text;
+--echo # Clean up
+
+--echo # Test case 23: Check that the statements SIGNAL and RESIGNAL can be executed as
+--echo # a prepared statement
+
+--error 30001
+SIGNAL SQLSTATE '45000' SET MYSQL_ERRNO=30001, MESSAGE_TEXT='Hello, world!';
+
+--error ER_RESIGNAL_WITHOUT_ACTIVE_HANDLER
+RESIGNAL SET MESSAGE_TEXT = 'New error message';
+
+--enable_warnings
+
+SET default_storage_engine= @save_storage_engine;