mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 02:46:29 +01:00
222 lines
6.8 KiB
Text
222 lines
6.8 KiB
Text
#
|
|
# 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;
|