--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;