MDEV-16708: Unsupported commands for prepared statements

Withing this task the following changes were made:
- Added sending of metadata info in prepare phase for the admin related
  command (check table, checksum table, repair, optimize, analyze).

- Refactored implmentation of HELP command to support its execution in
  PS mode

- Added support for execution of LOAD INTO and XA- related statements
  in PS mode

- Modified mysqltest.cc to run statements in PS mode unconditionally
  in case the option --ps-protocol is set. Formerly, only those statements
  were executed using PS protocol that matched the hard-coded regular expression

- Fixed the following issues:
    The statement
      explain select (select 2)
    executed in regular and PS mode produces different results:

    MariaDB [test]> prepare stmt from "explain select (select 2)";
    Query OK, 0 rows affected (0,000 sec)
    Statement prepared
    MariaDB [test]> execute stmt;
    +------+-------------+-------+------+---------------+------+---------+------+------+----------------+
    | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
    +------+-------------+-------+------+---------------+------+---------+------+------+----------------+
    |    1 | PRIMARY     | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
    |    2 | SUBQUERY    | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
    +------+-------------+-------+------+---------------+------+---------+------+------+----------------+
    2 rows in set (0,000 sec)
    MariaDB [test]> explain select (select 2);
    +------+-------------+-------+------+---------------+------+---------+------+------+----------------+
    | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
    +------+-------------+-------+------+---------------+------+---------+------+------+----------------+
    |    1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
    +------+-------------+-------+------+---------------+------+---------+------+------+----------------+
    1 row in set, 1 warning (0,000 sec)

    In case the statement
      CREATE TABLE t1 SELECT * FROM (SELECT 1 AS a, (SELECT a+0)) a
    is run in PS mode it fails with the error
      ERROR 1054 (42S22): Unknown column 'a' in 'field list'.

- Uniform handling of read-only variables both in case the SET var=val
  statement is executed as regular or prepared statememt.

- Fixed assertion firing on handling LOAD DATA statement for temporary tables

- Relaxed assert condition in the function lex_end_stage1() by adding
  the commands SQLCOM_ALTER_EVENT, SQLCOM_CREATE_PACKAGE,
  SQLCOM_CREATE_PACKAGE_BODY to a list of supported command

- Removed raising of the error ER_UNSUPPORTED_PS in the function
  check_prepared_statement() for the ALTER VIEW command

- Added initialization of the data memember st_select_lex_unit::last_procedure
  (assign NULL value) in the constructor

  Without this change the test case main.ctype_utf8 fails with the following
  report in case it is run with the optoin --ps-protocol.
    mysqltest: At line 2278: query 'VALUES (_latin1 0xDF) UNION VALUES(_utf8'a' COLLATE utf8_bin)' failed: 2013: Lost connection

- The following bug reports were fixed:
      MDEV-24460: Multiple rows result set returned from stored
                  routine over prepared statement binary protocol is
                  handled incorrectly
      CONC-519: mariadb client library doesn't handle server_status and
                warnign_count fields received in the packet
                COM_STMT_EXECUTE_RESPONSE.

  Reasons for these bug reports have the same nature and caused by
  missing loop iteration on results sent by server in response to
  COM_STMT_EXECUTE packet.

  Enclosing of statements for processing of COM_STMT_EXECUTE response
  in the construct like
    do
    {
      ...
    } while (!mysql_stmt_next_result());
  fixes the above mentioned bug reports.

39 files changed, 2955 insertions, 373 deletions

diff --git a/client/mysqltest.cc b/client/mysqltest.cc
index 8d3046faae0..fb36c4b075d 100644
--- a/client/mysqltest.cc
+++ b/client/mysqltest.cc
@@ -258,7 +258,6 @@ static const char *opt_suite_dir, *opt_overlay_dir;
 static size_t suite_dir_len, overlay_dir_len;
 
 /* Precompiled re's */
-static regex_t ps_re;     /* the query can be run using PS protocol */
 static regex_t sp_re;     /* the query can be run as a SP */
 static regex_t view_re;   /* the query can be run as a view*/
 
@@ -8324,116 +8323,119 @@ void run_query_stmt(struct st_connection *cn, struct st_command *command,
     goto end;
   }
 
-  /*
-    When running in cursor_protocol get the warnings from execute here
-    and keep them in a separate string for later.
-  */
-  if (cursor_protocol_enabled && !disable_warnings)
-    append_warnings(&ds_execute_warnings, mysql);
-
-  /*
-    We instruct that we want to update the "max_length" field in
-    mysql_stmt_store_result(), this is our only way to know how much
-    buffer to allocate for result data
-  */
+  do
   {
-    my_bool one= 1;
-    if (mysql_stmt_attr_set(stmt, STMT_ATTR_UPDATE_MAX_LENGTH, (void*) &one))
-      die("mysql_stmt_attr_set(STMT_ATTR_UPDATE_MAX_LENGTH) failed': %d %s",
-          mysql_stmt_errno(stmt), mysql_stmt_error(stmt));
-  }
+    /*
+      When running in cursor_protocol get the warnings from execute here
+      and keep them in a separate string for later.
+    */
+    if (cursor_protocol_enabled && !disable_warnings)
+      append_warnings(&ds_execute_warnings, mysql);
 
-  /*
-    If we got here the statement succeeded and was expected to do so,
-    get data. Note that this can still give errors found during execution!
-    Store the result of the query if if will return any fields
-  */
-  if (mysql_stmt_field_count(stmt) && mysql_stmt_store_result(stmt))
-  {
-    handle_error(command, mysql_stmt_errno(stmt),
-                 mysql_stmt_error(stmt), mysql_stmt_sqlstate(stmt), ds);
-    goto end;
-  }
+    /*
+      We instruct that we want to update the "max_length" field in
+      mysql_stmt_store_result(), this is our only way to know how much
+      buffer to allocate for result data
+    */
+    {
+      my_bool one= 1;
+      if (mysql_stmt_attr_set(stmt, STMT_ATTR_UPDATE_MAX_LENGTH, (void*) &one))
+        die("mysql_stmt_attr_set(STMT_ATTR_UPDATE_MAX_LENGTH) failed': %d %s",
+            mysql_stmt_errno(stmt), mysql_stmt_error(stmt));
+    }
 
-  /* If we got here the statement was both executed and read successfully */
-  handle_no_error(command);
-  if (!disable_result_log)
-  {
     /*
-      Not all statements creates a result set. If there is one we can
-      now create another normal result set that contains the meta
-      data. This set can be handled almost like any other non prepared
-      statement result set.
+      If we got here the statement succeeded and was expected to do so,
+      get data. Note that this can still give errors found during execution!
+      Store the result of the query if if will return any fields
     */
-    if ((res= mysql_stmt_result_metadata(stmt)) != NULL)
+    if (mysql_stmt_field_count(stmt) && mysql_stmt_store_result(stmt))
     {
-      /* Take the column count from meta info */
-      MYSQL_FIELD *fields= mysql_fetch_fields(res);
-      uint num_fields= mysql_num_fields(res);
+      handle_error(command, mysql_stmt_errno(stmt),
+                   mysql_stmt_error(stmt), mysql_stmt_sqlstate(stmt), ds);
+      goto end;
+    }
 
-      if (display_metadata)
-        append_metadata(ds, fields, num_fields);
+    /* If we got here the statement was both executed and read successfully */
+    handle_no_error(command);
+    if (!disable_result_log)
+    {
+      /*
+        Not all statements creates a result set. If there is one we can
+        now create another normal result set that contains the meta
+        data. This set can be handled almost like any other non prepared
+        statement result set.
+      */
+      if ((res= mysql_stmt_result_metadata(stmt)) != NULL)
+      {
+        /* Take the column count from meta info */
+        MYSQL_FIELD *fields= mysql_fetch_fields(res);
+        uint num_fields= mysql_num_fields(res);
 
-      if (!display_result_vertically)
-        append_table_headings(ds, fields, num_fields);
+        if (display_metadata)
+          append_metadata(ds, fields, num_fields);
 
-      append_stmt_result(ds, stmt, fields, num_fields);
+        if (!display_result_vertically)
+          append_table_headings(ds, fields, num_fields);
 
-      mysql_free_result(res);     /* Free normal result set with meta data */
+        append_stmt_result(ds, stmt, fields, num_fields);
 
-      /*
-        Normally, if there is a result set, we do not show warnings from the
-        prepare phase. This is because some warnings are generated both during
-        prepare and execute; this would generate different warning output
-        between normal and ps-protocol test runs.
+        mysql_free_result(res);     /* Free normal result set with meta data */
 
-        The --enable_prepare_warnings command can be used to change this so
-        that warnings from both the prepare and execute phase are shown.
-      */
-      if (!disable_warnings && !prepare_warnings_enabled)
-        dynstr_set(&ds_prepare_warnings, NULL);
-    }
-    else
-    {
-      /*
-	This is a query without resultset
-      */
-    }
+        /*
+          Normally, if there is a result set, we do not show warnings from the
+          prepare phase. This is because some warnings are generated both during
+          prepare and execute; this would generate different warning output
+          between normal and ps-protocol test runs.
 
-    /*
-      Fetch info before fetching warnings, since it will be reset
-      otherwise.
-    */
-    if (!disable_info)
-      append_info(ds, mysql_stmt_affected_rows(stmt), mysql_info(mysql));
+          The --enable_prepare_warnings command can be used to change this so
+          that warnings from both the prepare and execute phase are shown.
+        */
+        if (!disable_warnings && !prepare_warnings_enabled)
+          dynstr_set(&ds_prepare_warnings, NULL);
+      }
+      else
+      {
+        /*
+          This is a query without resultset
+        */
+      }
 
-    if (display_session_track_info)
-      append_session_track_info(ds, mysql);
+      /*
+        Fetch info before fetching warnings, since it will be reset
+        otherwise.
+      */
+      if (!disable_info)
+        append_info(ds, mysql_stmt_affected_rows(stmt), mysql_info(mysql));
 
+      if (display_session_track_info)
+        append_session_track_info(ds, mysql);
 
-    if (!disable_warnings)
-    {
-      /* Get the warnings from execute */
 
-      /* Append warnings to ds - if there are any */
-      if (append_warnings(&ds_execute_warnings, mysql) ||
-          ds_execute_warnings.length ||
-          ds_prepare_warnings.length ||
-          ds_warnings->length)
+      if (!disable_warnings)
       {
-        dynstr_append_mem(ds, "Warnings:\n", 10);
-        if (ds_warnings->length)
-          dynstr_append_mem(ds, ds_warnings->str,
-                            ds_warnings->length);
-        if (ds_prepare_warnings.length)
-          dynstr_append_mem(ds, ds_prepare_warnings.str,
-                            ds_prepare_warnings.length);
-        if (ds_execute_warnings.length)
-          dynstr_append_mem(ds, ds_execute_warnings.str,
-                            ds_execute_warnings.length);
+        /* Get the warnings from execute */
+
+        /* Append warnings to ds - if there are any */
+        if (append_warnings(&ds_execute_warnings, mysql) ||
+            ds_execute_warnings.length ||
+            ds_prepare_warnings.length ||
+            ds_warnings->length)
+        {
+          dynstr_append_mem(ds, "Warnings:\n", 10);
+          if (ds_warnings->length)
+            dynstr_append_mem(ds, ds_warnings->str,
+                              ds_warnings->length);
+          if (ds_prepare_warnings.length)
+            dynstr_append_mem(ds, ds_prepare_warnings.str,
+                              ds_prepare_warnings.length);
+          if (ds_execute_warnings.length)
+            dynstr_append_mem(ds, ds_execute_warnings.str,
+                              ds_execute_warnings.length);
+        }
       }
     }
-  }
+  } while ( !mysql_stmt_next_result(stmt));
 
 end:
   if (!disable_warnings)
@@ -8719,8 +8721,7 @@ void run_query(struct st_connection *cn, struct st_command *command, int flags)
     statement already and we can't do it twice
   */
   if (ps_protocol_enabled &&
-      complete_query &&
-      match_re(&ps_re, query))
+      complete_query)
     run_query_stmt(cn, command, query, query_len, ds, &ds_warnings);
   else
     run_query_normal(cn, command, flags, query, query_len,
@@ -8795,9 +8796,9 @@ void init_re(void)
 {
   /*
     Filter for queries that can be run using the
-    MySQL Prepared Statements C API
+    Stored procedures
   */
-  const char *ps_re_str =
+  const char *sp_re_str =
     "^("
     "[[:space:]]*ALTER[[:space:]]+SEQUENCE[[:space:]]|"
     "[[:space:]]*ALTER[[:space:]]+TABLE[[:space:]]|"
@@ -8851,19 +8852,12 @@ void init_re(void)
     ")";
 
   /*
-    Filter for queries that can be run using the
-    Stored procedures
-  */
-  const char *sp_re_str =ps_re_str;
-
-  /*
     Filter for queries that can be run as views
   */
   const char *view_re_str =
     "^("
     "[[:space:]]*SELECT[[:space:]])";
 
-  init_re_comp(&ps_re, ps_re_str);
   init_re_comp(&sp_re, sp_re_str);
   init_re_comp(&view_re, view_re_str);
 }
@@ -8899,7 +8893,6 @@ int match_re(regex_t *re, char *str)
 
 void free_re(void)
 {
-  regfree(&ps_re);
   regfree(&sp_re);
   regfree(&view_re);
 }
diff --git a/mysql-test/main/func_time.test b/mysql-test/main/func_time.test
index 970d3e44a77..ccec4d18ea3 100644
--- a/mysql-test/main/func_time.test
+++ b/mysql-test/main/func_time.test
@@ -621,8 +621,10 @@ SET GLOBAL log_bin_trust_function_creators = 1;
 
 create table t1 (a timestamp default '2005-05-05 01:01:01',
                  b timestamp default '2005-05-05 01:01:01');
-delimiter //;
+--disable_warnings
 drop function if exists t_slow_sysdate;
+--enable_warnings
+delimiter //;
 create function t_slow_sysdate() returns timestamp
 begin
   do sleep(2);
diff --git a/mysql-test/main/get_diagnostics.result b/mysql-test/main/get_diagnostics.result
index bcecce95a00..fbd25dc603c 100644
--- a/mysql-test/main/get_diagnostics.result
+++ b/mysql-test/main/get_diagnostics.result
@@ -258,12 +258,10 @@ DROP PROCEDURE p1;
 # Test GET DIAGNOSTICS runtime
 #
 
-# GET DIAGNOSTICS cannot be the object of a PREPARE statement.
+# GET DIAGNOSTICS can be the object of a PREPARE statement.
 
 PREPARE stmt FROM "GET DIAGNOSTICS CONDITION 1 @var = CLASS_ORIGIN";
-ERROR HY000: This command is not supported in the prepared statement protocol yet
 PREPARE stmt FROM "GET DIAGNOSTICS @var = NUMBER";
-ERROR HY000: This command is not supported in the prepared statement protocol yet
 
 # GET DIAGNOSTICS does not clear the diagnostics area.
 
diff --git a/mysql-test/main/get_diagnostics.test b/mysql-test/main/get_diagnostics.test
index 4667ec727dd..d9faf184a62 100644
--- a/mysql-test/main/get_diagnostics.test
+++ b/mysql-test/main/get_diagnostics.test
@@ -331,12 +331,10 @@ DROP PROCEDURE p1;
 --echo #
 
 --echo
---echo # GET DIAGNOSTICS cannot be the object of a PREPARE statement.
+--echo # GET DIAGNOSTICS can be the object of a PREPARE statement.
 --echo
 
---error ER_UNSUPPORTED_PS
 PREPARE stmt FROM "GET DIAGNOSTICS CONDITION 1 @var = CLASS_ORIGIN";
---error ER_UNSUPPORTED_PS
 PREPARE stmt FROM "GET DIAGNOSTICS @var = NUMBER";
 
 --echo
diff --git a/mysql-test/main/ps.result b/mysql-test/main/ps.result
index c1334b0b9a7..70ea6870368 100644
--- a/mysql-test/main/ps.result
+++ b/mysql-test/main/ps.result
@@ -28,11 +28,9 @@ ERROR HY000: Unknown prepared statement handler (no_such_statement) given to DEA
 execute stmt1;
 ERROR HY000: Incorrect arguments to EXECUTE
 prepare stmt2 from 'prepare nested_stmt from "select 1"';
-ERROR HY000: This command is not supported in the prepared statement protocol yet
 prepare stmt2 from 'execute stmt1';
-ERROR HY000: This command is not supported in the prepared statement protocol yet
 prepare stmt2 from 'deallocate prepare z';
-ERROR HY000: This command is not supported in the prepared statement protocol yet
+deallocate prepare stmt2;
 prepare stmt3 from 'insert into t1 values (?,?)';
 set @arg1=5, @arg2='five';
 execute stmt3 using @arg1, @arg2;
@@ -2726,9 +2724,7 @@ ERROR 42000: FUNCTION test.func_1 does not exist
 drop function func_1;
 ERROR 42000: FUNCTION test.func_1 does not exist
 prepare abc from "create event xyz on schedule at now() do select 123";
-ERROR HY000: This command is not supported in the prepared statement protocol yet
 deallocate prepare abc;
-ERROR HY000: Unknown prepared statement handler (abc) given to DEALLOCATE PREPARE
 drop event if exists xyz;
 create event xyz on schedule every 5 minute disable do select 123;
 create procedure proc_1() alter event xyz comment 'xyz';
@@ -2748,9 +2744,7 @@ drop procedure proc_1;
 create function func_1() returns int begin alter event xyz comment 'xyz'; return 1; end|
 ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger
 prepare abc from "alter event xyz comment 'xyz'";
-ERROR HY000: This command is not supported in the prepared statement protocol yet
 deallocate prepare abc;
-ERROR HY000: Unknown prepared statement handler (abc) given to DEALLOCATE PREPARE
 drop event if exists xyz;
 create event xyz on schedule every 5 minute disable do select 123;
 create procedure proc_1() drop event xyz;
@@ -2765,9 +2759,7 @@ drop procedure proc_1;
 create function func_1() returns int begin drop event xyz; return 1; end|
 ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger
 prepare abc from "drop event xyz";
-ERROR HY000: This command is not supported in the prepared statement protocol yet
 deallocate prepare abc;
-ERROR HY000: Unknown prepared statement handler (abc) given to DEALLOCATE PREPARE
 drop table if exists t1;
 create table t1 (a int, b char(5)) engine=myisam;
 insert into t1 values (1, "one"), (2, "two"), (3, "three");
@@ -3087,15 +3079,15 @@ DROP TABLE t1;
 CREATE TABLE t1(f1 INT);
 INSERT INTO t1 VALUES (1),(1);
 PREPARE stmt FROM 'EXPLAIN SELECT 1 FROM t1 WHERE (SELECT (SELECT 1 FROM t1 GROUP BY f1))';
+Warnings:
+Note	1249	Select 2 was reduced during optimization
 EXECUTE stmt;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
-2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 3	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	Using temporary; Using filesort
 EXECUTE stmt;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
-2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 3	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	Using temporary; Using filesort
 DEALLOCATE PREPARE stmt;
 DROP TABLE t1;
@@ -4727,13 +4719,13 @@ ERROR HY000: Incorrect arguments to EXECUTE
 EXECUTE IMMEDIATE 'SELECT ?';
 ERROR HY000: Incorrect arguments to EXECUTE
 EXECUTE IMMEDIATE 'EXECUTE IMMEDIATE "SELECT 1"';
-ERROR HY000: This command is not supported in the prepared statement protocol yet
+1
+1
 EXECUTE IMMEDIATE 'PREPARE stmt FROM "SELECT 1"';
-ERROR HY000: This command is not supported in the prepared statement protocol yet
 EXECUTE IMMEDIATE 'EXECUTE stmt';
-ERROR HY000: This command is not supported in the prepared statement protocol yet
+1
+1
 EXECUTE IMMEDIATE 'DEALLOCATE PREPARE stmt';
-ERROR HY000: This command is not supported in the prepared statement protocol yet
 EXECUTE IMMEDIATE 'SELECT ?' USING _latin1'a'=_latin2'a';
 ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation '='
 EXECUTE IMMEDIATE 'SELECT ?' USING ROW(1,2);
@@ -4944,7 +4936,7 @@ ERROR 42000: You have an error in your SQL syntax; check the manual that corresp
 PREPARE stmt FROM CONCAT(NULL);
 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'NULL' at line 1
 EXECUTE IMMEDIATE ? USING 'SELECT 1';
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '? USING 'SELECT 1'' at line 1
+Got one of the listed errors
 EXECUTE IMMEDIATE 10;
 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '10' at line 1
 EXECUTE IMMEDIATE TIME'10:20:30';
diff --git a/mysql-test/main/ps.test b/mysql-test/main/ps.test
index 837fa6f2b6e..5933aea4a03 100644
--- a/mysql-test/main/ps.test
+++ b/mysql-test/main/ps.test
@@ -37,15 +37,11 @@ deallocate prepare no_such_statement;
 --error 1210
 execute stmt1;
 
-# Nesting ps commands is not allowed: 
---error ER_UNSUPPORTED_PS 
+# Nesting ps commands is now allowed:
 prepare stmt2 from 'prepare nested_stmt from "select 1"';
-
---error ER_UNSUPPORTED_PS 
 prepare stmt2 from 'execute stmt1';
-
---error ER_UNSUPPORTED_PS 
 prepare stmt2 from 'deallocate prepare z';
+deallocate prepare stmt2;
 
 # PS insert 
 prepare stmt3 from 'insert into t1 values (?,?)';
@@ -2763,12 +2759,11 @@ delimiter ;|
 select func_1(), func_1(), func_1() from dual;
 --error ER_SP_DOES_NOT_EXIST
 drop function func_1;
---error ER_UNSUPPORTED_PS
+
+# CREATE EVENT is now supported by prepared statements
 prepare abc from "create event xyz on schedule at now() do select 123";
---error ER_UNKNOWN_STMT_HANDLER
 deallocate prepare abc;
 
-
 --disable_warnings
 drop event if exists xyz;
 create event xyz on schedule every 5 minute disable do select 123;
@@ -2787,9 +2782,9 @@ delimiter |;
 --error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
 create function func_1() returns int begin alter event xyz comment 'xyz'; return 1; end|
 delimiter ;|
---error ER_UNSUPPORTED_PS
+
+# ALTER EVENT is now supported by prepared statements
 prepare abc from "alter event xyz comment 'xyz'";
---error ER_UNKNOWN_STMT_HANDLER
 deallocate prepare abc;
 
 
@@ -2808,9 +2803,8 @@ delimiter |;
 --error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
 create function func_1() returns int begin drop event xyz; return 1; end|
 delimiter ;|
---error ER_UNSUPPORTED_PS
+# DROP EVENT is now supported by prepared statements
 prepare abc from "drop event xyz";
---error ER_UNKNOWN_STMT_HANDLER
 deallocate prepare abc;
 
 
@@ -4199,16 +4193,12 @@ EXECUTE IMMEDIATE 'SELECT 1' USING @a;
 --error ER_WRONG_ARGUMENTS
 EXECUTE IMMEDIATE 'SELECT ?';
 
---error ER_UNSUPPORTED_PS
 EXECUTE IMMEDIATE 'EXECUTE IMMEDIATE "SELECT 1"';
 
---error ER_UNSUPPORTED_PS
 EXECUTE IMMEDIATE 'PREPARE stmt FROM "SELECT 1"';
 
---error ER_UNSUPPORTED_PS
 EXECUTE IMMEDIATE 'EXECUTE stmt';
 
---error ER_UNSUPPORTED_PS
 EXECUTE IMMEDIATE 'DEALLOCATE PREPARE stmt';
 
 --error ER_CANT_AGGREGATE_2COLLATIONS
@@ -4414,7 +4404,12 @@ EXECUTE IMMEDIATE CONCAT(NULL);
 --error ER_PARSE_ERROR
 PREPARE stmt FROM CONCAT(NULL);
 
---error ER_PARSE_ERROR
+# Expects any of the following errors CR_PARAMS_NOT_BOUND, ER_PARSE_ERROR.
+# ER_PARSE_ERROR is generated in case the EXECUTE IMMEDIATE statement
+# is sent as a regular statement via text protocol. The error
+# CR_PARAMS_NOT_BOUND is generated by the MySQL API function mysql_stmt_execute
+# when this test file is run with the option --ps-protocol
+--error 2031,ER_PARSE_ERROR
 EXECUTE IMMEDIATE ? USING 'SELECT 1';
 
 --error ER_PARSE_ERROR
diff --git a/mysql-test/main/ps_1general.result b/mysql-test/main/ps_1general.result
index 149c297621b..c42b3d07bbc 100644
--- a/mysql-test/main/ps_1general.result
+++ b/mysql-test/main/ps_1general.result
@@ -381,15 +381,10 @@ drop table t5 ;
 deallocate prepare stmt_do ;
 deallocate prepare stmt_set ;
 prepare stmt1 from ' prepare stmt2 from '' select 1 ''  ' ;
-ERROR HY000: This command is not supported in the prepared statement protocol yet
 prepare stmt1 from ' execute stmt2 ' ;
-ERROR HY000: This command is not supported in the prepared statement protocol yet
 prepare stmt1 from ' deallocate prepare never_prepared ' ;
-ERROR HY000: This command is not supported in the prepared statement protocol yet
 prepare stmt1 from 'alter view v1 as select 2';
-ERROR HY000: This command is not supported in the prepared statement protocol yet
 prepare stmt4 from ' use test ' ;
-ERROR HY000: This command is not supported in the prepared statement protocol yet
 prepare stmt3 from ' create database mysqltest ';
 create database mysqltest ;
 prepare stmt3 from ' drop database mysqltest ';
@@ -403,12 +398,9 @@ drop table t2 ;
 execute stmt3;
 ERROR 42S02: Table 'test.t2' doesn't exist
 prepare stmt3 from ' lock tables t1 read ' ;
-ERROR HY000: This command is not supported in the prepared statement protocol yet
 prepare stmt3 from ' unlock tables ' ;
-ERROR HY000: This command is not supported in the prepared statement protocol yet
 prepare stmt1 from ' load data infile ''<MYSQLTEST_VARDIR>/tmp/data.txt''
      into table t1 fields terminated by ''\t'' ';
-ERROR HY000: This command is not supported in the prepared statement protocol yet
 prepare stmt1 from ' select * into outfile ''<MYSQLTEST_VARDIR>/tmp/data.txt'' from t1 ';
 execute stmt1 ;
 prepare stmt1 from ' optimize table t1 ' ;
@@ -416,7 +408,6 @@ prepare stmt1 from ' analyze table t1 ' ;
 prepare stmt1 from ' checksum table t1 ' ;
 prepare stmt1 from ' repair table t1 ' ;
 prepare stmt1 from ' handler t1 open ';
-ERROR HY000: This command is not supported in the prepared statement protocol yet
 prepare stmt3 from ' commit ' ;
 prepare stmt3 from ' rollback ' ;
 prepare stmt4 from ' SET sql_mode=ansi ';
diff --git a/mysql-test/main/ps_1general.test b/mysql-test/main/ps_1general.test
index 47db79cb6c6..f98c05a69fb 100644
--- a/mysql-test/main/ps_1general.test
+++ b/mysql-test/main/ps_1general.test
@@ -406,19 +406,13 @@ deallocate prepare stmt_do ;
 deallocate prepare stmt_set ;
 
 ## nonsense like prepare of prepare,execute or deallocate
---error ER_UNSUPPORTED_PS 
 prepare stmt1 from ' prepare stmt2 from '' select 1 ''  ' ;
---error ER_UNSUPPORTED_PS 
 prepare stmt1 from ' execute stmt2 ' ;
---error ER_UNSUPPORTED_PS 
 prepare stmt1 from ' deallocate prepare never_prepared ' ;
 
-## We don't support alter view as prepared statements
---error ER_UNSUPPORTED_PS
 prepare stmt1 from 'alter view v1 as select 2';
 
 ## switch the database connection
---error ER_UNSUPPORTED_PS
 prepare stmt4 from ' use test ' ;
 
 ## create/drop database
@@ -435,9 +429,7 @@ drop table t2 ;
 --error ER_NO_SUCH_TABLE
 execute stmt3;
 ## lock/unlock
---error ER_UNSUPPORTED_PS
 prepare stmt3 from ' lock tables t1 read ' ;
---error ER_UNSUPPORTED_PS
 prepare stmt3 from ' unlock tables ' ;
 ## Load/Unload table contents
 
@@ -446,7 +438,6 @@ prepare stmt3 from ' unlock tables ' ;
 --remove_file $datafile
 
 --replace_result $MYSQLTEST_VARDIR <MYSQLTEST_VARDIR>
---error ER_UNSUPPORTED_PS
 eval prepare stmt1 from ' load data infile ''$datafile''
      into table t1 fields terminated by ''\t'' ';
 --replace_result $MYSQLTEST_VARDIR <MYSQLTEST_VARDIR>
@@ -461,7 +452,6 @@ prepare stmt1 from ' repair table t1 ' ;
 --remove_file $datafile
 
 ## handler
---error ER_UNSUPPORTED_PS
 prepare stmt1 from ' handler t1 open ';
 
 
diff --git a/mysql-test/main/ps_ddl.result b/mysql-test/main/ps_ddl.result
index 86a294b732d..d99aeb0fa54 100644
--- a/mysql-test/main/ps_ddl.result
+++ b/mysql-test/main/ps_ddl.result
@@ -2244,7 +2244,6 @@ SUCCESS
 drop table if exists t1;
 create table t1 (a varchar(20));
 prepare stmt from "load data infile '../std_data_ln/words.dat' into table t1";
-ERROR HY000: This command is not supported in the prepared statement protocol yet
 drop table t1;
 #
 # SQLCOM_SHOW_DATABASES
@@ -2516,7 +2515,6 @@ SUCCESS
 drop view if exists v1;
 create view v1 as select 1;
 prepare stmt from "alter view v1 as select 2";
-ERROR HY000: This command is not supported in the prepared statement protocol yet
 drop view v1;
 # Cleanup
 #
diff --git a/mysql-test/main/ps_ddl.test b/mysql-test/main/ps_ddl.test
index 694ee61fd0c..5a2a0f60a70 100644
--- a/mysql-test/main/ps_ddl.test
+++ b/mysql-test/main/ps_ddl.test
@@ -1880,7 +1880,6 @@ call p_verify_reprepare_count(8);
 drop table if exists t1;
 --enable_warnings
 create table t1 (a varchar(20));
---error ER_UNSUPPORTED_PS
 prepare stmt from "load data infile '../std_data_ln/words.dat' into table t1";
 drop table t1;
 
@@ -2202,7 +2201,6 @@ call p_verify_reprepare_count(17);
 drop view if exists v1;
 --enable_warnings
 create view v1 as select 1;
---error ER_UNSUPPORTED_PS
 prepare stmt from "alter view v1 as select 2";
 drop view v1;
 
diff --git a/mysql-test/main/ps_missed_cmds.result b/mysql-test/main/ps_missed_cmds.result
new file mode 100644
index 00000000000..f16cae445fb
--- /dev/null
+++ b/mysql-test/main/ps_missed_cmds.result
@@ -0,0 +1,910 @@
+SET @save_storage_engine= @@default_storage_engine;
+SET default_storage_engine= InnoDB;
+#
+# MDEV-16708: Unsupported commands for prepared statements
+#
+# Disable ps-protocol explicitly in order to test support of
+# prepared statements for use case when statements passed
+# to the server via text client-server protocol (in contrast
+# with binary protocol used in the test file
+# ps_missed_cmds_bin_prot.test)
+# Test case 1: Check that the statement 'LOAD DATA' is supported
+# by prepared statements
+# First, set up environment for use by the 'LOAD DATA' statement
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1);
+COMMIT;
+SELECT * INTO OUTFILE 'load.data' FROM t1;
+PREPARE stmt_1 FROM "LOAD DATA INFILE 'load.data' INTO TABLE t1";
+EXECUTE stmt_1;
+# Execute the same prepared statement the second time to check that
+# no internal structures used for handling the 'LOAD DATA' statement
+# were damaged.
+EXECUTE stmt_1;
+SELECT * FROM t1;
+a
+1
+1
+1
+# Clean up
+DEALLOCATE PREPARE stmt_1;
+DROP TABLE t1;
+# Test case 2: Check that the 'LOCK TABLE', 'UNLOCK TABLES' statements
+# are supported by prepared statements
+CREATE TABLE t1 (a INT);
+PREPARE stmt_1 FROM "LOCK TABLE t1 READ";
+EXECUTE stmt_1;
+# Execute the same prepared statement the second time to check that
+# no internal structures used for handling the 'LOCK TABLE READ'
+# statement were damaged.
+EXECUTE stmt_1;
+PREPARE stmt_1 FROM "UNLOCK TABLE";
+EXECUTE stmt_1;
+# Execute the same prepared statement the second time to check that
+# no internal structures used for handling the 'UNLOCK TABLE' statement
+# were damaged.
+EXECUTE stmt_1;
+PREPARE stmt_1 FROM "LOCK TABLE t1 WRITE";
+EXECUTE stmt_1;
+# Execute the same prepared statement the second time to check that
+# no internal structures used for handling the 'LOCK TABLE WRITE'
+# statement were damaged.
+EXECUTE stmt_1;
+# Clean up
+DEALLOCATE PREPARE stmt_1;
+UNLOCK TABLE;
+DROP TABLE t1;
+# Test case 3: Check that the 'USE' statement is supported by
+# prepared statements
+CREATE DATABASE mdev_16708_db;
+PREPARE stmt_1 FROM 'USE mdev_16708_db';
+EXECUTE stmt_1;
+# Execute the same prepared statement the second time to check that
+# no internal structures used for handling the 'USE' statement
+# were damaged.
+EXECUTE stmt_1;
+# Check that the current database has been changed
+SELECT DATABASE();
+DATABASE()
+mdev_16708_db
+# Clean up
+DEALLOCATE PREPARE stmt_1;
+USE test;
+DROP DATABASE mdev_16708_db;
+# Test case 4: Check that the 'ALTER DATABASE' statement is supported
+# by prepared statements
+CREATE DATABASE mdev_16708_db;
+PREPARE stmt_1 FROM "ALTER DATABASE mdev_16708_db COMMENT 'New comment'";
+EXECUTE stmt_1;
+# Execute the same prepared statement the second time to check that
+# no internal structures used for handling the 'ALTER DATABASE' statement
+# were damaged.
+EXECUTE stmt_1;
+# Clean up
+DEALLOCATE PREPARE stmt_1;
+USE test;
+DROP DATABASE mdev_16708_db;
+# Test case 5: Check that the 'CREATE FUNCTION/ALTER FUNCTION/
+# DROP FUNCTION' statements are supported by prepared statements
+PREPARE stmt_1 FROM 'CREATE FUNCTION f1() RETURNS INT RETURN 1';
+EXECUTE stmt_1;
+# Execute the same prepared statement the second time to check that
+# no internal structures used for handling the 'CREATE FUNCTION'
+# statement were damaged. The second attempt to execute the prepared
+# statement stmt_1 results in error ER_SP_ALREADY_EXISTS since
+# the stored function f() has been created on first run of stmt1.
+EXECUTE stmt_1;
+ERROR 42000: FUNCTION f1 already exists
+PREPARE stmt_1 FROM 'ALTER FUNCTION f1 SQL SECURITY INVOKER';
+EXECUTE stmt_1;
+# Execute the same prepared statement the second time to check that
+# no internal structures used for handling the 'ALTER FUNCTION'
+# statement were damaged.
+EXECUTE stmt_1;
+PREPARE stmt_1 FROM 'DROP FUNCTION f1';
+EXECUTE stmt_1;
+# Execute the same prepared statement the second time to check that
+# no internal structures used for handling 'DROP FUNCTION' statement
+# were damaged. The second attempt to run 'DROP FUNCTION f1' using
+# prepared statement expectedly results in issuing the error
+# ER_SP_DOES_NOT_EXIST since the stored function was dropped on first
+# executuon of the prepared statement stmt_1.
+EXECUTE stmt_1;
+ERROR 42000: FUNCTION test.f1 does not exist
+# Test case 6: Check that the 'CHECK TABLE' statement is supported
+# by prepared statements
+CREATE TABLE t1 (a INT);
+PREPARE stmt_1 FROM 'CHECK TABLE t1';
+EXECUTE stmt_1;
+Table	Op	Msg_type	Msg_text
+test.t1	check	status	OK
+# Execute the same prepared statement the second time to check that
+# no internal structures used for handling the 'CHECK TABLE' statement
+# were damaged.
+EXECUTE stmt_1;
+Table	Op	Msg_type	Msg_text
+test.t1	check	status	OK
+# Clean up
+DEALLOCATE PREPARE stmt_1;
+DROP TABLE t1;
+# Test case 7: Check that the BEGIN/SAVEPOINT/RELEASE SAVEPOINT
+# statements are supported by prepared statements
+# Set up environmentr for the test case
+CREATE TABLE t1 (a INT);
+PREPARE stmt_1 FROM 'BEGIN';
+EXECUTE stmt_1;
+# Execute the same prepared statement the second time to check that
+# no internal structures used for handling the 'BEGIN' statement
+# were damaged.
+EXECUTE stmt_1;
+INSERT INTO t1 VALUES (1);
+# Run 'SAVEPOINT s1' using prepared statements
+PREPARE stmt_2 FROM 'SAVEPOINT s1';
+EXECUTE stmt_2;
+# Execute the same prepared statement the second time to check that
+# no internal structures used for handling the 'SAVEPOINT' statement
+# were damaged.
+EXECUTE stmt_2;
+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 has been 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
+PREPARE stmt_3 FROM 'RELEASE SAVEPOINT s1';
+EXECUTE stmt_3;
+# Execute the same prepared statement the second time to check that
+# no internal structures used for handling the 'RELEASE' statement
+# were damaged. The second attempt to release the same savepoint
+# expectedly lead to error 'SAVEPOINT s1 does not exist'
+# that's just ignored.
+EXECUTE stmt_3;
+ERROR 42000: SAVEPOINT s1 does not exist
+# Clean up
+DEALLOCATE PREPARE stmt_1;
+DEALLOCATE PREPARE stmt_2;
+DEALLOCATE PREPARE stmt_3;
+DROP TABLE t1;
+# Test case 8: Check that the 'PURGE BINARY LOGS BEFORE' statement
+# is supported by prepared statements
+PREPARE stmt_1 FROM "PURGE BINARY LOGS BEFORE '2020-11-17'";
+EXECUTE stmt_1;
+# Execute the same prepared statement the second time to check that
+# no internal structures used for handling the 'PURGE BINARY LOGS BEFORE'
+# statement were damaged.
+EXECUTE stmt_1;
+# Check that the 'PURGE BINARY LOGS TO' statement is supported by
+# prepared statements
+PREPARE stmt_1 FROM "PURGE BINARY LOGS TO 'mariadb-bin.000063'";
+EXECUTE stmt_1;
+# Execute the same prepared statement the second time to check that
+# no internal structures used for handling the 'PURGE BINARY LOGS TO'
+# statement were damaged.
+EXECUTE stmt_1;
+# Test case 9: Check that the 'HANDLER OPEN/HANDLER READ/
+# HANDLER CLOSE' statements are supported by prepared statements
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1);
+INSERT INTO t1 VALUES (1);
+COMMIT;
+PREPARE stmt_1 FROM 'HANDLER t1 OPEN';
+EXECUTE stmt_1;
+# Execute the same prepared statement the second time to check that
+# no internal structures used for handling the 'HANDLER OPEN'
+# statement were damaged. Execution of this statement the second
+# time expectedly results in emitting the error ER_NONUNIQ_TABLE.
+# The same error is issued in case the statement 'HANDLER t1 OPEN' is
+# executed twice using a regular statement.
+EXECUTE stmt_1;
+ERROR 42000: Not unique table/alias: 't1'
+PREPARE stmt_2 FROM 'HANDLER t1 READ FIRST';
+PREPARE stmt_3 FROM 'HANDLER t1 READ NEXT';
+PREPARE stmt_4 FROM 'HANDLER t1 CLOSE';
+EXECUTE stmt_2;
+a
+1
+# Execute the same prepared statement the second time to check that
+# no internal structures used for handling the 'HANDLER READ FIRST'
+# statement were damaged.
+EXECUTE stmt_2;
+a
+1
+EXECUTE stmt_3;
+a
+1
+# Execute the same prepared statement the second time to check that
+# no internal structures used for handling the 'HANDLER READ NEXT'
+# statement were damaged.
+EXECUTE stmt_3;
+a
+EXECUTE stmt_4;
+# Execute the same prepared statement the second time to check that
+# no internal structures used for handling the 'HANDLER CLOSE'
+# statement were damaged. Execution of this statement the second
+# time results in emitting the error ER_UNKNOWN_TABLE. The same error
+# is issued in case the statement 'HANDLER t1 CLOSE' executed twice
+# using a regular statement.
+EXECUTE stmt_4;
+ERROR 42S02: Unknown table 't1' in HANDLER
+# Clean up
+DEALLOCATE PREPARE stmt_1;
+DEALLOCATE PREPARE stmt_2;
+DEALLOCATE PREPARE stmt_3;
+DEALLOCATE PREPARE stmt_4;
+DROP TABLE t1;
+# Test case 10: Check that the HELP statement
+# is supported by prepared statements
+PREPARE stmt_1 FROM "HELP `UPDATE`";
+EXECUTE stmt_1;
+name	description	example
+UPDATE	Syntax
+------ 
+Single-table syntax:
+ 
+UPDATE [LOW_PRIORITY] [IGNORE] table_reference 
+ [PARTITION (partition_list)]
+ SET col1={expr1|DEFAULT} [,col2={expr2|DEFAULT}] ...
+ [WHERE where_condition]
+ [ORDER BY ...]
+ [LIMIT row_count]
+ 
+Multiple-table syntax:
+ 
+UPDATE [LOW_PRIORITY] [IGNORE] table_references
+ SET col1={expr1|DEFAULT} [, col2={expr2|DEFAULT}] ...
+ [WHERE where_condition]
+ 
+Description
+----------- 
+For the single-table syntax, the UPDATE statement updates
+columns of existing rows in the named table with new values.
+The
+SET clause indicates which columns to modify and the values
+they should be given. Each value can be given as an
+expression, or the keyword
+DEFAULT to set a column explicitly to its default value. The
+WHERE clause, if given, specifies the conditions that
+identify
+which rows to update. With no WHERE clause, all rows are
+updated. If the ORDER BY clause is specified, the rows are
+updated in the order that is specified. The LIMIT clause
+places a limit on the number of rows that can be updated.
+ 
+The PARTITION clause was introduced in MariaDB 10.0. See
+Partition Pruning and Selection for details.
+ 
+Until MariaDB 10.3.2, for the multiple-table syntax, UPDATE
+updates rows in each
+table named in table_references that satisfy the conditions.
+In this case,
+ORDER BY and LIMIT cannot be used. This restriction was
+lifted in MariaDB 10.3.2 and both clauses can be used with
+multiple-table updates. An UPDATE can also reference tables
+which are located in different databases; see Identifier
+Qualifiers for the syntax.
+ 
+where_condition is an expression that evaluates to true for
+each row to be updated.
+ 
+table_references and where_condition are as
+specified as described in SELECT.
+ 
+Assignments are evaluated in left-to-right order, unless the
+SIMULTANEOUS_ASSIGNMENT sql_mode (available from MariaDB
+10.3.5) is set, in which case the UPDATE statement evaluates
+all assignments simultaneously. 
+ 
+You need the UPDATE privilege only for columns referenced in
+an UPDATE that are actually updated. You need only the
+SELECT privilege for any columns that are read but
+not modified. See GRANT.
+ 
+The UPDATE statement supports the following modifiers:
+If you use the LOW_PRIORITY keyword, execution of
+ the UPDATE is delayed until no other clients are reading
+from
+ the table. This affects only storage engines that use only
+table-level
+ locking (MyISAM, MEMORY, MERGE). See HIGH_PRIORITY and
+LOW_PRIORITY clauses for details.
+If you use the IGNORE keyword, the update statement does 
+ not abort even if errors occur during the update. Rows for
+which
+ duplicate-key conflicts occur are not updated. Rows for
+which columns are
+ updated to values that would cause data conversion errors
+are updated to the
+ closest valid values instead.
+ 
+UPDATE Statements With the Same Source and Target
+ 
+From MariaDB 10.3.2, UPDATE statements may have the same
+source and target.
+ 
+For example, given the following table:
+ 
+DROP TABLE t1;
+ 
+CREATE TABLE t1 (c1 INT, c2 INT);
+INSERT INTO t1 VALUES (10,10), (20,20);
+ 
+Until MariaDB 10.3.1, the following UPDATE statement would
+not work:
+ 
+UPDATE t1 SET c1=c1+1 WHERE c2=(SELECT MAX(c2) FROM t1);
+ERROR 1093 (HY000): Table 't1' is specified twice, 
+ both as a target for 'UPDATE' and as a separate source
+for data
+ 
+From MariaDB 10.3.2, the statement executes successfully:
+ 
+UPDATE t1 SET c1=c1+1 WHERE c2=(SELECT MAX(c2) FROM t1);
+ 
+SELECT * FROM t1;
+ 
++------+------+
+| c1 | c2 |
++------+------+
+| 10 | 10 |
+| 21 | 20 |
++------+------+
+ 
+Example
+ 
+Single-table syntax:
+ 
+UPDATE table_name SET column1 = value1, column2 = value2
+WHERE id=100;
+ 
+Multiple-table syntax:
+ 
+UPDATE tab1, tab2 SET tab1.column1 = value1, tab1.column2 =
+value2 WHERE tab1.id = tab2.id;
+ 
+
+
+URL: https://mariadb.com/kb/en/library/update/	
+# Execute the same prepared statement the second time to check that
+# no internal structures used for handling the 'HELP' statement
+# were damaged.
+EXECUTE stmt_1;
+name	description	example
+UPDATE	Syntax
+------ 
+Single-table syntax:
+ 
+UPDATE [LOW_PRIORITY] [IGNORE] table_reference 
+ [PARTITION (partition_list)]
+ SET col1={expr1|DEFAULT} [,col2={expr2|DEFAULT}] ...
+ [WHERE where_condition]
+ [ORDER BY ...]
+ [LIMIT row_count]
+ 
+Multiple-table syntax:
+ 
+UPDATE [LOW_PRIORITY] [IGNORE] table_references
+ SET col1={expr1|DEFAULT} [, col2={expr2|DEFAULT}] ...
+ [WHERE where_condition]
+ 
+Description
+----------- 
+For the single-table syntax, the UPDATE statement updates
+columns of existing rows in the named table with new values.
+The
+SET clause indicates which columns to modify and the values
+they should be given. Each value can be given as an
+expression, or the keyword
+DEFAULT to set a column explicitly to its default value. The
+WHERE clause, if given, specifies the conditions that
+identify
+which rows to update. With no WHERE clause, all rows are
+updated. If the ORDER BY clause is specified, the rows are
+updated in the order that is specified. The LIMIT clause
+places a limit on the number of rows that can be updated.
+ 
+The PARTITION clause was introduced in MariaDB 10.0. See
+Partition Pruning and Selection for details.
+ 
+Until MariaDB 10.3.2, for the multiple-table syntax, UPDATE
+updates rows in each
+table named in table_references that satisfy the conditions.
+In this case,
+ORDER BY and LIMIT cannot be used. This restriction was
+lifted in MariaDB 10.3.2 and both clauses can be used with
+multiple-table updates. An UPDATE can also reference tables
+which are located in different databases; see Identifier
+Qualifiers for the syntax.
+ 
+where_condition is an expression that evaluates to true for
+each row to be updated.
+ 
+table_references and where_condition are as
+specified as described in SELECT.
+ 
+Assignments are evaluated in left-to-right order, unless the
+SIMULTANEOUS_ASSIGNMENT sql_mode (available from MariaDB
+10.3.5) is set, in which case the UPDATE statement evaluates
+all assignments simultaneously. 
+ 
+You need the UPDATE privilege only for columns referenced in
+an UPDATE that are actually updated. You need only the
+SELECT privilege for any columns that are read but
+not modified. See GRANT.
+ 
+The UPDATE statement supports the following modifiers:
+If you use the LOW_PRIORITY keyword, execution of
+ the UPDATE is delayed until no other clients are reading
+from
+ the table. This affects only storage engines that use only
+table-level
+ locking (MyISAM, MEMORY, MERGE). See HIGH_PRIORITY and
+LOW_PRIORITY clauses for details.
+If you use the IGNORE keyword, the update statement does 
+ not abort even if errors occur during the update. Rows for
+which
+ duplicate-key conflicts occur are not updated. Rows for
+which columns are
+ updated to values that would cause data conversion errors
+are updated to the
+ closest valid values instead.
+ 
+UPDATE Statements With the Same Source and Target
+ 
+From MariaDB 10.3.2, UPDATE statements may have the same
+source and target.
+ 
+For example, given the following table:
+ 
+DROP TABLE t1;
+ 
+CREATE TABLE t1 (c1 INT, c2 INT);
+INSERT INTO t1 VALUES (10,10), (20,20);
+ 
+Until MariaDB 10.3.1, the following UPDATE statement would
+not work:
+ 
+UPDATE t1 SET c1=c1+1 WHERE c2=(SELECT MAX(c2) FROM t1);
+ERROR 1093 (HY000): Table 't1' is specified twice, 
+ both as a target for 'UPDATE' and as a separate source
+for data
+ 
+From MariaDB 10.3.2, the statement executes successfully:
+ 
+UPDATE t1 SET c1=c1+1 WHERE c2=(SELECT MAX(c2) FROM t1);
+ 
+SELECT * FROM t1;
+ 
++------+------+
+| c1 | c2 |
++------+------+
+| 10 | 10 |
+| 21 | 20 |
++------+------+
+ 
+Example
+ 
+Single-table syntax:
+ 
+UPDATE table_name SET column1 = value1, column2 = value2
+WHERE id=100;
+ 
+Multiple-table syntax:
+ 
+UPDATE tab1, tab2 SET tab1.column1 = value1, tab1.column2 =
+value2 WHERE tab1.id = tab2.id;
+ 
+
+
+URL: https://mariadb.com/kb/en/library/update/	
+# Test case 11: Check that the 'CREATE PROCEDURE' statement
+# is supported by prepared statements
+PREPARE stmt_1 FROM 'CREATE PROCEDURE p1() SET @a=1';
+EXECUTE stmt_1;
+# Execute the same prepared statement the second time to check that
+# no internal structures used for handling the 'CREATE PROCEDURE'
+# statement were damaged. Execution of this statement the second
+# time expectedly results in emitting the error ER_SP_ALREADY_EXISTS.
+# The same error is issued in case the 'HANDLER t1 OPEN' statement
+# is executed twice using a regular statement.
+EXECUTE stmt_1;
+ERROR 42000: PROCEDURE p1 already exists
+# Test case 12: Check that the 'ALTER PROCEDURE' statement is supported
+# by prepared statements.
+PREPARE stmt_1 FROM 'ALTER PROCEDURE p1 SQL SECURITY INVOKER';
+EXECUTE stmt_1;
+# Execute the same prepared statement the second time to check that
+# no internal structures used for handling the 'ALTER PROCEDURE'
+# statement were damaged.
+EXECUTE stmt_1;
+# Test case 13: Check that the 'DROP PROCEDURE' statement is supported
+# by prepared statements.
+PREPARE stmt_1 FROM 'DROP PROCEDURE p1';
+EXECUTE stmt_1;
+# Execute the same prepared statement the second time to check that
+# no internal structures used for handling the 'DROP PROCEDURE' statement
+# were damaged. Execution of this statement the second time expectedly
+# results in emitting the error ER_SP_DOES_NOT_EXIST.
+EXECUTE stmt_1;
+ERROR 42000: PROCEDURE test.p1 does not exist
+# Test case 14: Check that the 'CALL' statement is supported
+# by prepared statements.
+CREATE PROCEDURE p1() SET @a=1;
+PREPARE stmt_1 FROM 'CALL p1()';
+EXECUTE stmt_1;
+# Execute the same prepared statement the second time to check that
+# no internal structures used for handling the 'CALL' statement
+# were damaged.
+EXECUTE stmt_1;
+# Clean up
+DEALLOCATE PREPARE stmt_1;
+DROP PROCEDURE p1;
+# Test case 15: Check that the 'PREPARE FROM' statement can be executed
+# as a prepared statement.
+PREPARE stmt_1 FROM 'PREPARE stmt_2 FROM "SELECT 1"';
+EXECUTE stmt_1;
+# Execute the same prepared statement the second time to check that
+# no internal structures used for handling the 'PREPARE' statement
+# were damaged.
+EXECUTE stmt_1;
+# Now execute the prepared statement with the name stmt_2
+# It is expected that output contains the single row '1'
+EXECUTE stmt_2;
+1
+1
+# Clean up
+DEALLOCATE PREPARE stmt_1;
+DEALLOCATE PREPARE stmt_2;
+# Test case 16: Check that the 'EXECUTE' statement can be executed
+# as a prepared statement.
+PREPARE stmt_1 FROM 'SELECT 1';
+PREPARE stmt_2 FROM 'EXECUTE stmt_1';
+# Execute the statement stmt_2. Expected result is output of one row '1'
+EXECUTE stmt_2;
+1
+1
+# Execute the same prepared statement the second time to check that
+# no internal structures used for handling the 'EXECUTE' statement
+# were damaged.
+EXECUTE stmt_2;
+1
+1
+# Clean up
+DEALLOCATE PREPARE stmt_1;
+DEALLOCATE PREPARE stmt_2;
+# Test case 17: Check that the statement 'DEALLOCATE PREPARE'
+# can be executed as a prepared statement.
+PREPARE stmt_1 FROM 'SELECT 1';
+PREPARE stmt_2 FROM 'DEALLOCATE PREPARE stmt_1';
+# After the prepared statement 'stmt_2' be executed
+# the prepared statement stmt_1 will be deallocated.
+EXECUTE stmt_2;
+# Execute the same prepared statement the second time to check that
+# no internal structures used for handling the 'DEALLOCATE PREPARE'
+# statement were damaged. This time invocation results in the error
+# ER_UNKNOWN_STMT_HANDLER since the prepared statement stmt_1
+# has just been released. So, just ignore this error.
+EXECUTE stmt_2;
+ERROR HY000: Unknown prepared statement handler (stmt_1) given to DEALLOCATE PREPARE
+# Check that the stmt_1 doesn't no longer exist
+EXECUTE stmt_1;
+ERROR HY000: Unknown prepared statement handler (stmt_1) given to EXECUTE
+# Clean up
+DEALLOCATE PREPARE stmt_2;
+# Test case 18: Check that the 'CREATE VIEW' statement can be executed
+# as a prepared statement.
+# Create environment for the test case
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1);
+COMMIT;
+PREPARE stmt_1 FROM 'CREATE VIEW v1 AS SELECT * FROM t1';
+EXECUTE stmt_1;
+# Execute the same prepared statement the second time to check that
+# no internal structures used for handling the 'CREATE VIEW'
+# statement were damaged. The second execution of the prepared
+# statement stmt_1 results in error ER_TABLE_EXISTS_ERROR since
+# the view v1 does already exist. It is expected behaviour.
+EXECUTE stmt_1;
+ERROR 42S01: Table 'v1' already exists
+# Clean up
+DEALLOCATE PREPARE stmt_1;
+DROP VIEW v1;
+DROP TABLE t1;
+# Test case 19: Check that the 'CREATE TRIGGER' statement can be executed
+# as a prepared statement.
+CREATE TABLE t1 (a INT);
+PREPARE stmt_1 FROM 'CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW SET @a=1';
+EXECUTE stmt_1;
+# Execute the same prepared statement the second time to check that
+# no internal structures used for handling the 'CREATE VIEW' statement
+# were damaged. The second execution of the prepared statement stmt_1
+# results in error ER_TRG_ALREADY_EXISTS since the trigger trg1 does
+# already exist. It is expected behaviour.
+EXECUTE stmt_1;
+ERROR HY000: Trigger 'test.trg1' already exists
+# Test case 20: Check that the 'DROP TRIGGER' statement can be executed
+# as a prepared statement.
+# This test relies on presence of the trigger trg1 created by
+# the test case 19.
+PREPARE stmt_1 FROM 'DROP TRIGGER trg1';
+EXECUTE stmt_1;
+# Execute the same prepared statement the second time to check that
+# no internal structures used for handling the 'DROP TRIGGER' statement
+# were damaged. Execution of this statement the second time expectedly
+# results in emitting the error ER_TRG_DOES_NOT_EXIST.
+EXECUTE stmt_1;
+ERROR HY000: Trigger does not exist
+# Clean up
+DEALLOCATE PREPARE stmt_1;
+DROP TABLE t1;
+# Test case 21: Check that XA related SQL statements can be executed
+# as prepared statements.
+# Create the table t1 used by XA transaction.
+CREATE TABLE t1 (a INT);
+PREPARE stmt_1 FROM "XA START 'xid1'";
+PREPARE stmt_2 FROM "XA END 'xid1'";
+PREPARE stmt_3 FROM "XA PREPARE 'xid1'";
+PREPARE stmt_4 FROM "XA RECOVER";
+PREPARE stmt_5 FROM "XA COMMIT 'xid1'";
+PREPARE stmt_6 FROM "XA ROLLBACK 'xid1'";
+# Start a new XA transaction
+EXECUTE stmt_1;
+# Execute the same prepared statement the second time to check that
+# no internal structures used for handling the 'XA START' statement
+# were damaged. Execution of this statement the second time expectedly
+# results in emitting the error XAER_RMFAIL since there is active
+# XA transaction that has just been already.
+EXECUTE stmt_1;
+ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the  ACTIVE state
+INSERT INTO t1 VALUES (1);
+# End the current XA transaction
+EXECUTE stmt_2;
+# Execute the same prepared statement the second time to check that
+# no internal structures used for handling the 'XA END' statement
+# were damaged. Execution of this statement the second time expectedly
+# results in emitting the error XAER_RMFAIL since the XA transaction
+# with XID 'xid1' has been already ended.
+EXECUTE stmt_2;
+ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the  IDLE state
+# Prepare the current XA transaction for finalization
+EXECUTE stmt_3;
+# Execute the same prepared statement the second time to check that
+# no internal structures used for handling the 'XA END' statement
+# were damaged. Execution of this statement the second time expectedly
+# results in emitting the error XAER_RMFAIL since the XA transaction
+# with XID 'xid1' has been already ended.
+EXECUTE stmt_3;
+ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the  PREPARED state
+# Run XA RECOVER as a prepared statement
+EXECUTE stmt_4;
+formatID	gtrid_length	bqual_length	data
+1	4	0	xid1
+# And execute it yet another time to check that no internal structures
+# used for handling the statement 'XA RECOVER' were damaged.
+EXECUTE stmt_4;
+formatID	gtrid_length	bqual_length	data
+1	4	0	xid1
+# Commit the current XA transaction
+EXECUTE stmt_5;
+# Execute the same prepared statement the second time to check that
+# no internal structures used for handling the 'XA COMMIT' statement
+# were damaged. Execution of this statement the second time expectedly
+# results in emitting the error XAER_NOTA since the XA transaction
+# with XID 'xid1' has been finalized and no more exists.
+EXECUTE stmt_5;
+ERROR XAE04: XAER_NOTA: Unknown XID
+# Query the table t1 to check that it contains a record inserted by
+# the XA transaction just finished.
+SELECT * FROM t1;
+a
+1
+# Using prepared statements start a new XA transaction, INSERT a row
+# into the table t1, prepare the XA transaction and rollback it.
+# This use case is similar to precedence one except it does rollback
+# XA transaction instead commit it. Therefore every prepared statement
+# is executed only once except the last XA ROLLBACK.
+# Start a new XA transaction
+EXECUTE stmt_1;
+INSERT INTO t1 VALUES (1);
+# End the current XA transaction
+EXECUTE stmt_2;
+# Prepare the current XA transaction for finalization
+EXECUTE stmt_3;
+# Rolback the current XA transaction
+EXECUTE stmt_6;
+# Execute the same prepared statement the second time to check that
+# no internal structures used for handling the statement 'XA ROLLBACK'
+# were damaged. Execution of this statement the second time expectedly
+# results in emitting the error XAER_NOTA since the XA transaction
+# with XID 'xid1' has been finalized and no more exists.
+EXECUTE stmt_6;
+ERROR XAE04: XAER_NOTA: Unknown XID
+# Clean up
+DROP TABLE t1;
+DEALLOCATE PREPARE stmt_1;
+DEALLOCATE PREPARE stmt_2;
+DEALLOCATE PREPARE stmt_3;
+DEALLOCATE PREPARE stmt_4;
+DEALLOCATE PREPARE stmt_5;
+DEALLOCATE PREPARE stmt_6;
+# Test case 22: Check that the  CREATE SERVER/ALTER SERVER/DROP SERVER
+# statements can be executed as prepared statements.
+PREPARE stmt_1 FROM "CREATE SERVER s FOREIGN DATA WRAPPER mysql OPTIONS (USER 'u1', HOST '127.0.0.1')";
+PREPARE stmt_2 FROM "ALTER SERVER s OPTIONS (USER 'u2')";
+PREPARE stmt_3 FROM "DROP SERVER s";
+EXECUTE stmt_1;
+# Execute the same prepared statement the second time to check that
+# no internal structures used for handling the 'CREATE SERVER' statement
+# were damaged. Execution of this statement the second time expectedly
+# results in emitting the error ER_FOREIGN_SERVER_EXISTS
+# since a server with same has just been created.
+EXECUTE stmt_1;
+ERROR HY000: The foreign server, s, you are trying to create already exists
+EXECUTE stmt_2;
+# Execute the same prepared statement the second time to check that
+# no internal structures used for handling the 'ALTER SERVER' statement
+# were damaged.
+EXECUTE stmt_2;
+EXECUTE stmt_3;
+# Execute the same prepared statement the second time to check that
+# no internal structures used for handling the 'DROP SERVER' statement
+# were damaged. Execution of this statement the second time expectedly
+# results in emitting the error ER_FOREIGN_SERVER_DOESNT_EXIST
+# since the server with same has just been dropped.
+EXECUTE stmt_3;
+ERROR HY000: The foreign server name you are trying to reference does not exist. Data source error:  s
+# Clean up
+DEALLOCATE PREPARE stmt_1;
+DEALLOCATE PREPARE stmt_2;
+DEALLOCATE PREPARE stmt_3;
+# Test case 23: Check that the CREATE EVENT/ALTER EVENT/DROP EVENT
+# statements can be executed as a prepared statement
+PREPARE stmt_1 FROM "CREATE EVENT e1 ON SCHEDULE AT CURRENT_TIMESTAMP - INTERVAL 1 DAY ON COMPLETION PRESERVE DO SET @a=1";
+PREPARE stmt_2 FROM "ALTER EVENT e1 COMMENT 'New comment'";
+PREPARE stmt_3 FROM "DROP EVENT e1";
+# Create the event e1 that specifies time in past. Such event is created
+# just for the sake of its existence and never will be triggered.
+# Disable warnings temprorary in order to hide the following warnings
+# generated in result of execution the 'CREATE EVENT' statement:
+#   "1544 | Event execution time is in the past. Event has been disabled"
+#   "1105 | Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it."
+EXECUTE stmt_1;
+# Execute the same prepared statement the second time to check that
+# no internal structures used for handling the 'CREATE EVENT' statement
+# were damaged. Execution of this statement the second time expectedly
+# results in emitting the error ER_EVENT_ALREADY_EXISTS since the event
+# with the same name has just been created.
+EXECUTE stmt_1;
+ERROR HY000: Event 'e1' already exists
+# Alter event e1
+EXECUTE stmt_2;
+# Execute the same prepared statement the second time to check that
+# no internal structures used for handling the 'ALTER EVENT' statement
+# were damaged.
+EXECUTE stmt_2;
+# Drop event e1
+EXECUTE stmt_3;
+# Execute the same prepared statement the second time to check that
+# no internal structures used for handling the 'DROP EVENT' statement
+# were damaged. Execution of this statement the second time expectedly
+# results in emitting the error ER_EVENT_DOESNT_EXIST since the event
+# with the same name has just been dropped.
+EXECUTE stmt_3;
+ERROR HY000: Unknown event 'e1'
+# Clean up
+DEALLOCATE PREPARE stmt_1;
+DEALLOCATE PREPARE stmt_2;
+DEALLOCATE PREPARE stmt_3;
+# Test case 24: Check that the SIGNAL and RESIGNAL statements
+# can be executed as a prepared statement
+PREPARE stmt_1 FROM "SIGNAL SQLSTATE '45000' SET MYSQL_ERRNO=30001, MESSAGE_TEXT='Hello, world!'";
+EXECUTE stmt_1;
+ERROR 45000: Hello, world!
+# Execute the same prepared statement the second time to check that
+# no internal structures used for handling the 'SIGNAL' statement
+# were damaged.
+EXECUTE stmt_1;
+ERROR 45000: Hello, world!
+PREPARE stmt_1 FROM 'RESIGNAL SET MYSQL_ERRNO = 5';
+EXECUTE stmt_1;
+ERROR 0K000: RESIGNAL when handler not active
+# Execute the same prepared statement the second time to check that
+# no internal structures used for handling the 'RESIGNAL' statement
+# were damaged.
+EXECUTE stmt_1;
+ERROR 0K000: RESIGNAL when handler not active
+# Clean up
+DEALLOCATE PREPARE stmt_1;
+# Test case 25: Check that the 'SHOW RELAYLOG EVENTS' statement can be
+# executed as a prepared statement.
+PREPARE stmt_1 FROM 'SHOW RELAYLOG EVENTS';
+EXECUTE stmt_1;
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+# Execute the same prepared statement the second time to check that
+# no internal structures used for handling the 'SHOW RELAYLOG EVENTS'
+# statement were damaged.
+EXECUTE stmt_1;
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+# Clean up
+DEALLOCATE PREPARE stmt_1;
+# Test case 26: Check the 'GET DIAGNOSTICS' statement
+# can be executed as a prepared statement
+PREPARE stmt_1 FROM 'GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT';
+# 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
+EXECUTE stmt_1;
+# 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
+SELECT * FROM non_existent_table_1;
+ERROR 42S02: Table 'test.non_existent_table_1' doesn't exist
+# Execute the same prepared statement the second time to check that
+# no internal structures used for handling the
+# 'GET DIAGNOSTICS CONDITION' statement were damaged.
+EXECUTE stmt_1;
+# Clean up
+DEALLOCATE PREPARE stmt_1;
+# Test case 27: Check the the statement 'EXECUTE IMMEDIATE'
+# can be executed as a prepared statement
+PREPARE stmt_1 FROM "EXECUTE IMMEDIATE 'SELECT 1'";
+EXECUTE stmt_1;
+1
+1
+# Execute the same prepared statement the second time to check that
+# no internal structures used for handling the 'EXECUTE IMMEDIATE'
+# statement were damaged.
+# Clean up
+DEALLOCATE PREPARE stmt_1;
+# Test Test case 28: Check the statements 'BACKUP'/'BACKUP UNLOCK'
+# can be executed as a prepared statement
+CREATE TABLE t1 (a INT);
+PREPARE stmt_1 FROM 'BACKUP LOCK t1';
+PREPARE stmt_2 FROM 'BACKUP UNLOCK';
+EXECUTE stmt_1;
+# Execute the same prepared statement the second time to check that
+# no internal structures used for handling the 'BACKUP LOCK'
+# statement were damaged.
+EXECUTE stmt_1;
+EXECUTE stmt_2;
+# Execute the same prepared statement the second time to check that
+# no internal structures used for handling the 'BACKUP UNLOCK'
+# statement were damaged.
+EXECUTE stmt_2;
+# Clean up
+DROP TABLE t1;
+DEALLOCATE PREPARE stmt_1;
+DEALLOCATE PREPARE stmt_2;
+# Test Test case 29: Check the statements 'CREATE/ALTER/DROP TABLEPSPACE'
+# can be executed as a prepared statement
+PREPARE stmt_1 FROM "CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENGINE=InnoDB";
+PREPARE stmt_2 FROM "ALTER TABLESPACE ts1 ADD DATAFILE 'ts1_1.ibd' ENGINE=InnoDB";
+PREPARE stmt_3 FROM "DROP TABLESPACE ts1 ENGINE=InnoDB";
+EXECUTE stmt_1;
+# Execute the same prepared statement the second time to check that
+# no internal structures used for handling the 'CREATE TABLESPACE'
+# statement were damaged.
+EXECUTE stmt_1;
+EXECUTE stmt_2;
+# Execute the same prepared statement the second time to check that
+# no internal structures used for handling the 'ALTER TABLESPACE'
+# statement were damaged.
+EXECUTE stmt_2;
+EXECUTE stmt_3;
+# Execute the same prepared statement the second time to check that
+# no internal structures used for handling the 'DROP TABLESPACE'
+# statement were damaged.
+EXECUTE stmt_3;
+DEALLOCATE PREPARE stmt_1;
+DEALLOCATE PREPARE stmt_2;
+DEALLOCATE PREPARE stmt_3;
+SET default_storage_engine= @save_storage_engine;
diff --git a/mysql-test/main/ps_missed_cmds.test b/mysql-test/main/ps_missed_cmds.test
new file mode 100644
index 00000000000..b621ef6d611
--- /dev/null
+++ b/mysql-test/main/ps_missed_cmds.test
@@ -0,0 +1,758 @@
+--source include/have_innodb.inc
+
+if (`SELECT $PS_PROTOCOL != 0`)
+{
+   --skip Need regular protocol but ps-protocol was specified
+}
+
+SET @save_storage_engine= @@default_storage_engine;
+SET default_storage_engine= InnoDB;
+
+--echo #
+--echo # MDEV-16708: Unsupported commands for prepared statements
+--echo #
+
+--echo # Disable ps-protocol explicitly in order to test support of
+--echo # prepared statements for use case when statements passed
+--echo # to the server via text client-server protocol (in contrast
+--echo # with binary protocol used in the test file
+--echo # ps_missed_cmds_bin_prot.test)
+--disable_ps_protocol
+
+--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 'LOAD DATA' statement
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1);
+COMMIT;
+SELECT * INTO OUTFILE 'load.data' FROM t1;
+
+PREPARE stmt_1 FROM "LOAD DATA INFILE 'load.data' INTO TABLE t1";
+EXECUTE stmt_1;
+--echo # Execute the same prepared statement the second time to check that
+--echo # no internal structures used for handling the 'LOAD DATA' statement
+--echo # were damaged.
+EXECUTE stmt_1;
+SELECT * FROM t1;
+--echo # Clean up
+DEALLOCATE PREPARE stmt_1;
+DROP TABLE t1;
+--let $datadir= `select @@datadir`
+--remove_file $datadir/test/load.data
+
+--echo # Test case 2: Check that the 'LOCK TABLE', 'UNLOCK TABLES' statements
+--echo # are supported by prepared statements
+CREATE TABLE t1 (a INT);
+
+PREPARE stmt_1 FROM "LOCK TABLE t1 READ";
+EXECUTE stmt_1;
+--echo # Execute the same prepared statement the second time to check that
+--echo # no internal structures used for handling the 'LOCK TABLE READ'
+--echo # statement were damaged.
+EXECUTE stmt_1;
+
+PREPARE stmt_1 FROM "UNLOCK TABLE";
+EXECUTE stmt_1;
+--echo # Execute the same prepared statement the second time to check that
+--echo # no internal structures used for handling the 'UNLOCK TABLE' statement
+--echo # were damaged.
+EXECUTE stmt_1;
+
+PREPARE stmt_1 FROM "LOCK TABLE t1 WRITE";
+EXECUTE stmt_1;
+--echo # Execute the same prepared statement the second time to check that
+--echo # no internal structures used for handling the 'LOCK TABLE WRITE'
+--echo # statement were damaged.
+EXECUTE stmt_1;
+--echo # Clean up
+DEALLOCATE PREPARE stmt_1;
+UNLOCK TABLE;
+DROP TABLE t1;
+
+--echo # Test case 3: Check that the 'USE' statement is supported by
+--echo # prepared statements
+
+CREATE DATABASE mdev_16708_db;
+PREPARE stmt_1 FROM 'USE mdev_16708_db';
+EXECUTE stmt_1;
+--echo # Execute the same prepared statement the second time to check that
+--echo # no internal structures used for handling the 'USE' statement
+--echo # were damaged.
+EXECUTE stmt_1;
+
+--echo # Check that the current database has been changed
+SELECT DATABASE();
+
+--echo # Clean up
+DEALLOCATE PREPARE stmt_1;
+USE test;
+DROP DATABASE mdev_16708_db;
+
+--echo # Test case 4: Check that the 'ALTER DATABASE' statement is supported
+--echo # by prepared statements
+CREATE DATABASE mdev_16708_db;
+PREPARE stmt_1 FROM "ALTER DATABASE mdev_16708_db COMMENT 'New comment'";
+EXECUTE stmt_1;
+--echo # Execute the same prepared statement the second time to check that
+--echo # no internal structures used for handling the 'ALTER DATABASE' statement
+--echo # were damaged.
+EXECUTE stmt_1;
+
+--echo # Clean up
+DEALLOCATE PREPARE stmt_1;
+USE test;
+DROP DATABASE mdev_16708_db;
+
+--echo # Test case 5: Check that the 'CREATE FUNCTION/ALTER FUNCTION/
+--echo # DROP FUNCTION' statements are supported by prepared statements
+PREPARE stmt_1 FROM 'CREATE FUNCTION f1() RETURNS INT RETURN 1';
+EXECUTE stmt_1;
+--echo # Execute the same prepared statement the second time to check that
+--echo # no internal structures used for handling the 'CREATE FUNCTION'
+--echo # statement were damaged. The second attempt to execute the prepared
+--echo # statement stmt_1 results in error ER_SP_ALREADY_EXISTS since
+--echo # the stored function f() has been created on first run of stmt1.
+--error ER_SP_ALREADY_EXISTS
+EXECUTE stmt_1;
+
+PREPARE stmt_1 FROM 'ALTER FUNCTION f1 SQL SECURITY INVOKER';
+EXECUTE stmt_1;
+--echo # Execute the same prepared statement the second time to check that
+--echo # no internal structures used for handling the 'ALTER FUNCTION'
+--echo # statement were damaged.
+EXECUTE stmt_1;
+
+PREPARE stmt_1 FROM 'DROP FUNCTION f1';
+EXECUTE stmt_1;
+--echo # Execute the same prepared statement the second time to check that
+--echo # no internal structures used for handling 'DROP FUNCTION' statement
+--echo # were damaged. The second attempt to run 'DROP FUNCTION f1' using
+--echo # prepared statement expectedly results in issuing the error
+--echo # ER_SP_DOES_NOT_EXIST since the stored function was dropped on first
+--echo # executuon of the prepared statement stmt_1.
+--error ER_SP_DOES_NOT_EXIST
+EXECUTE stmt_1;
+
+--echo # Test case 6: Check that the 'CHECK TABLE' statement is supported
+--echo # by prepared statements
+CREATE TABLE t1 (a INT);
+PREPARE stmt_1 FROM 'CHECK TABLE t1';
+EXECUTE stmt_1;
+--echo # Execute the same prepared statement the second time to check that
+--echo # no internal structures used for handling the 'CHECK TABLE' statement
+--echo # were damaged.
+EXECUTE stmt_1;
+
+--echo # Clean up
+DEALLOCATE PREPARE stmt_1;
+DROP TABLE t1;
+
+--echo # Test case 7: Check that the BEGIN/SAVEPOINT/RELEASE SAVEPOINT
+--echo # statements are supported by prepared statements
+
+--echo # Set up environmentr for the test case
+CREATE TABLE t1 (a INT);
+
+PREPARE stmt_1 FROM 'BEGIN';
+EXECUTE stmt_1;
+--echo # Execute the same prepared statement the second time to check that
+--echo # no internal structures used for handling the 'BEGIN' statement
+--echo # were damaged.
+EXECUTE stmt_1;
+
+INSERT INTO t1 VALUES (1);
+--echo # Run 'SAVEPOINT s1' using prepared statements
+PREPARE stmt_2 FROM 'SAVEPOINT s1';
+EXECUTE stmt_2;
+--echo # Execute the same prepared statement the second time to check that
+--echo # no internal structures used for handling the 'SAVEPOINT' statement
+--echo # were damaged.
+EXECUTE stmt_2;
+
+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 has been 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;
+
+PREPARE stmt_3 FROM 'RELEASE SAVEPOINT s1';
+EXECUTE stmt_3;
+--echo # Execute the same prepared statement the second time to check that
+--echo # no internal structures used for handling the 'RELEASE' statement
+--echo # were damaged. The second attempt to release the same savepoint
+--echo # expectedly lead to error 'SAVEPOINT s1 does not exist'
+--echo # that's just ignored.
+--error ER_SP_DOES_NOT_EXIST
+EXECUTE stmt_3;
+
+--echo # Clean up
+DEALLOCATE PREPARE stmt_1;
+DEALLOCATE PREPARE stmt_2;
+DEALLOCATE PREPARE stmt_3;
+DROP TABLE t1;
+
+--echo # Test case 8: Check that the 'PURGE BINARY LOGS BEFORE' statement
+--echo # is supported by prepared statements
+PREPARE stmt_1 FROM "PURGE BINARY LOGS BEFORE '2020-11-17'";
+EXECUTE stmt_1;
+--echo # Execute the same prepared statement the second time to check that
+--echo # no internal structures used for handling the 'PURGE BINARY LOGS BEFORE'
+--echo # statement were damaged.
+EXECUTE stmt_1;
+
+--echo # Check that the 'PURGE BINARY LOGS TO' statement is supported by
+--echo # prepared statements
+PREPARE stmt_1 FROM "PURGE BINARY LOGS TO 'mariadb-bin.000063'";
+EXECUTE stmt_1;
+--echo # Execute the same prepared statement the second time to check that
+--echo # no internal structures used for handling the 'PURGE BINARY LOGS TO'
+--echo # statement were damaged.
+EXECUTE stmt_1;
+
+--echo # Test case 9: Check that the 'HANDLER OPEN/HANDLER READ/
+--echo # HANDLER CLOSE' statements are supported by prepared statements
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1);
+INSERT INTO t1 VALUES (1);
+COMMIT;
+
+PREPARE stmt_1 FROM 'HANDLER t1 OPEN';
+EXECUTE stmt_1;
+--echo # Execute the same prepared statement the second time to check that
+--echo # no internal structures used for handling the 'HANDLER OPEN'
+--echo # statement were damaged. Execution of this statement the second
+--echo # time expectedly results in emitting the error ER_NONUNIQ_TABLE.
+--echo # The same error is issued in case the statement 'HANDLER t1 OPEN' is
+--echo # executed twice using a regular statement.
+--error ER_NONUNIQ_TABLE
+EXECUTE stmt_1;
+
+PREPARE stmt_2 FROM 'HANDLER t1 READ FIRST';
+PREPARE stmt_3 FROM 'HANDLER t1 READ NEXT';
+PREPARE stmt_4 FROM 'HANDLER t1 CLOSE';
+
+EXECUTE stmt_2;
+--echo # Execute the same prepared statement the second time to check that
+--echo # no internal structures used for handling the 'HANDLER READ FIRST'
+--echo # statement were damaged.
+EXECUTE stmt_2;
+
+EXECUTE stmt_3;
+--echo # Execute the same prepared statement the second time to check that
+--echo # no internal structures used for handling the 'HANDLER READ NEXT'
+--echo # statement were damaged.
+EXECUTE stmt_3;
+
+EXECUTE stmt_4;
+--echo # Execute the same prepared statement the second time to check that
+--echo # no internal structures used for handling the 'HANDLER CLOSE'
+--echo # statement were damaged. Execution of this statement the second
+--echo # time results in emitting the error ER_UNKNOWN_TABLE. The same error
+--echo # is issued in case the statement 'HANDLER t1 CLOSE' executed twice
+--echo # using a regular statement.
+--error ER_UNKNOWN_TABLE
+EXECUTE stmt_4;
+
+--echo # Clean up
+DEALLOCATE PREPARE stmt_1;
+DEALLOCATE PREPARE stmt_2;
+DEALLOCATE PREPARE stmt_3;
+DEALLOCATE PREPARE stmt_4;
+DROP TABLE t1;
+
+--echo # Test case 10: Check that the HELP statement
+--echo # is supported by prepared statements
+PREPARE stmt_1 FROM "HELP `UPDATE`";
+EXECUTE stmt_1;
+--echo # Execute the same prepared statement the second time to check that
+--echo # no internal structures used for handling the 'HELP' statement
+--echo # were damaged.
+
+EXECUTE stmt_1;
+
+--echo # Test case 11: Check that the 'CREATE PROCEDURE' statement
+--echo # is supported by prepared statements
+PREPARE stmt_1 FROM 'CREATE PROCEDURE p1() SET @a=1';
+EXECUTE stmt_1;
+--echo # Execute the same prepared statement the second time to check that
+--echo # no internal structures used for handling the 'CREATE PROCEDURE'
+--echo # statement were damaged. Execution of this statement the second
+--echo # time expectedly results in emitting the error ER_SP_ALREADY_EXISTS.
+--echo # The same error is issued in case the 'HANDLER t1 OPEN' statement
+--echo # is executed twice using a regular statement.
+--error ER_SP_ALREADY_EXISTS
+EXECUTE stmt_1;
+
+--echo # Test case 12: Check that the 'ALTER PROCEDURE' statement is supported
+--echo # by prepared statements.
+# This test case relies on artefacts of the test case 11 (the procedure p1)
+
+PREPARE stmt_1 FROM 'ALTER PROCEDURE p1 SQL SECURITY INVOKER';
+EXECUTE stmt_1;
+--echo # Execute the same prepared statement the second time to check that
+--echo # no internal structures used for handling the 'ALTER PROCEDURE'
+--echo # statement were damaged.
+EXECUTE stmt_1;
+
+--echo # Test case 13: Check that the 'DROP PROCEDURE' statement is supported
+--echo # by prepared statements.
+# This test case relies on artefacts of the test case 11 (the procedure p1)
+PREPARE stmt_1 FROM 'DROP PROCEDURE p1';
+EXECUTE stmt_1;
+--echo # Execute the same prepared statement the second time to check that
+--echo # no internal structures used for handling the 'DROP PROCEDURE' statement
+--echo # were damaged. Execution of this statement the second time expectedly
+--echo # results in emitting the error ER_SP_DOES_NOT_EXIST.
+
+--error ER_SP_DOES_NOT_EXIST
+EXECUTE stmt_1;
+
+--echo # Test case 14: Check that the 'CALL' statement is supported
+--echo # by prepared statements.
+
+CREATE PROCEDURE p1() SET @a=1;
+PREPARE stmt_1 FROM 'CALL p1()';
+EXECUTE stmt_1;
+--echo # Execute the same prepared statement the second time to check that
+--echo # no internal structures used for handling the 'CALL' statement
+--echo # were damaged.
+EXECUTE stmt_1;
+
+--echo # Clean up
+DEALLOCATE PREPARE stmt_1;
+DROP PROCEDURE p1;
+
+--echo # Test case 15: Check that the 'PREPARE FROM' statement can be executed
+--echo # as a prepared statement.
+PREPARE stmt_1 FROM 'PREPARE stmt_2 FROM "SELECT 1"';
+EXECUTE stmt_1;
+--echo # Execute the same prepared statement the second time to check that
+--echo # no internal structures used for handling the 'PREPARE' statement
+--echo # were damaged.
+EXECUTE stmt_1;
+--echo # Now execute the prepared statement with the name stmt_2
+--echo # It is expected that output contains the single row '1'
+EXECUTE stmt_2;
+--echo # Clean up
+DEALLOCATE PREPARE stmt_1;
+DEALLOCATE PREPARE stmt_2;
+
+--echo # Test case 16: Check that the 'EXECUTE' statement can be executed
+--echo # as a prepared statement.
+PREPARE stmt_1 FROM 'SELECT 1';
+PREPARE stmt_2 FROM 'EXECUTE stmt_1';
+--echo # Execute the statement stmt_2. Expected result is output of one row '1'
+EXECUTE stmt_2;
+--echo # Execute the same prepared statement the second time to check that
+--echo # no internal structures used for handling the 'EXECUTE' statement
+--echo # were damaged.
+EXECUTE stmt_2;
+
+--echo # Clean up
+DEALLOCATE PREPARE stmt_1;
+DEALLOCATE PREPARE stmt_2;
+
+--echo # Test case 17: Check that the statement 'DEALLOCATE PREPARE'
+--echo # can be executed as a prepared statement.
+PREPARE stmt_1 FROM 'SELECT 1';
+PREPARE stmt_2 FROM 'DEALLOCATE PREPARE stmt_1';
+--echo # After the prepared statement 'stmt_2' be executed
+--echo # the prepared statement stmt_1 will be deallocated.
+EXECUTE stmt_2;
+--echo # Execute the same prepared statement the second time to check that
+--echo # no internal structures used for handling the 'DEALLOCATE PREPARE'
+--echo # statement were damaged. This time invocation results in the error
+--echo # ER_UNKNOWN_STMT_HANDLER since the prepared statement stmt_1
+--echo # has just been released. So, just ignore this error.
+--error ER_UNKNOWN_STMT_HANDLER
+EXECUTE stmt_2;
+
+--echo # Check that the stmt_1 doesn't no longer exist
+--error ER_UNKNOWN_STMT_HANDLER
+EXECUTE stmt_1;
+
+--echo # Clean up
+DEALLOCATE PREPARE stmt_2;
+
+--echo # Test case 18: Check that the 'CREATE VIEW' statement 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;
+
+PREPARE stmt_1 FROM 'CREATE VIEW v1 AS SELECT * FROM t1';
+EXECUTE stmt_1;
+--echo # Execute the same prepared statement the second time to check that
+--echo # no internal structures used for handling the 'CREATE VIEW'
+--echo # statement were damaged. The second execution of the prepared
+--echo # statement stmt_1 results in error ER_TABLE_EXISTS_ERROR since
+--echo # the view v1 does already exist. It is expected behaviour.
+
+--error ER_TABLE_EXISTS_ERROR
+EXECUTE stmt_1;
+
+--echo # Clean up
+DEALLOCATE PREPARE stmt_1;
+
+DROP VIEW v1;
+DROP TABLE t1;
+
+--echo # Test case 19: Check that the 'CREATE TRIGGER' statement can be executed
+--echo # as a prepared statement.
+CREATE TABLE t1 (a INT);
+PREPARE stmt_1 FROM 'CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW SET @a=1';
+EXECUTE stmt_1;
+--echo # Execute the same prepared statement the second time to check that
+--echo # no internal structures used for handling the 'CREATE VIEW' statement
+--echo # were damaged. The second execution of the prepared statement stmt_1
+--echo # results in error ER_TRG_ALREADY_EXISTS since the trigger trg1 does
+--echo # already exist. It is expected behaviour.
+--error ER_TRG_ALREADY_EXISTS
+EXECUTE stmt_1;
+
+--echo # Test case 20: Check that the 'DROP TRIGGER' statement can be executed
+--echo # as a prepared statement.
+--echo # This test relies on presence of the trigger trg1 created by
+--echo # the test case 19.
+PREPARE stmt_1 FROM 'DROP TRIGGER trg1';
+EXECUTE stmt_1;
+--echo # Execute the same prepared statement the second time to check that
+--echo # no internal structures used for handling the 'DROP TRIGGER' statement
+--echo # were damaged. Execution of this statement the second time expectedly
+--echo # results in emitting the error ER_TRG_DOES_NOT_EXIST.
+--error ER_TRG_DOES_NOT_EXIST
+EXECUTE stmt_1;
+
+--echo # Clean up
+DEALLOCATE PREPARE stmt_1;
+DROP TABLE t1;
+
+--echo # Test case 21: Check that XA related SQL statements can be executed
+--echo # as prepared statements.
+--echo # Create the table t1 used by XA transaction.
+CREATE TABLE t1 (a INT);
+PREPARE stmt_1 FROM "XA START 'xid1'";
+PREPARE stmt_2 FROM "XA END 'xid1'";
+PREPARE stmt_3 FROM "XA PREPARE 'xid1'";
+PREPARE stmt_4 FROM "XA RECOVER";
+PREPARE stmt_5 FROM "XA COMMIT 'xid1'";
+PREPARE stmt_6 FROM "XA ROLLBACK 'xid1'";
+
+--echo # Start a new XA transaction
+EXECUTE stmt_1;
+--echo # Execute the same prepared statement the second time to check that
+--echo # no internal structures used for handling the 'XA START' statement
+--echo # were damaged. Execution of this statement the second time expectedly
+--echo # results in emitting the error XAER_RMFAIL since there is active
+--echo # XA transaction that has just been already.
+--error ER_XAER_RMFAIL
+EXECUTE stmt_1;
+
+INSERT INTO t1 VALUES (1);
+
+--echo # End the current XA transaction
+EXECUTE stmt_2;
+--echo # Execute the same prepared statement the second time to check that
+--echo # no internal structures used for handling the 'XA END' statement
+--echo # were damaged. Execution of this statement the second time expectedly
+--echo # results in emitting the error XAER_RMFAIL since the XA transaction
+--echo # with XID 'xid1' has been already ended.
+--error ER_XAER_RMFAIL
+EXECUTE stmt_2;
+
+--echo # Prepare the current XA transaction for finalization
+EXECUTE stmt_3;
+--echo # Execute the same prepared statement the second time to check that
+--echo # no internal structures used for handling the 'XA END' statement
+--echo # were damaged. Execution of this statement the second time expectedly
+--echo # results in emitting the error XAER_RMFAIL since the XA transaction
+--echo # with XID 'xid1' has been already ended.
+--error ER_XAER_RMFAIL
+EXECUTE stmt_3;
+
+--echo # Run XA RECOVER as a prepared statement
+EXECUTE stmt_4;
+--echo # And execute it yet another time to check that no internal structures
+--echo # used for handling the statement 'XA RECOVER' were damaged.
+EXECUTE stmt_4;
+
+--echo # Commit the current XA transaction
+EXECUTE stmt_5;
+--echo # Execute the same prepared statement the second time to check that
+--echo # no internal structures used for handling the 'XA COMMIT' statement
+--echo # were damaged. Execution of this statement the second time expectedly
+--echo # results in emitting the error XAER_NOTA since the XA transaction
+--echo # with XID 'xid1' has been finalized and no more exists.
+--error ER_XAER_NOTA
+EXECUTE stmt_5;
+
+--echo # Query the table t1 to check that it contains a record inserted by
+--echo # the XA transaction just finished.
+SELECT * FROM t1;
+
+--echo # Using prepared statements start a new XA transaction, INSERT a row
+--echo # into the table t1, prepare the XA transaction and rollback it.
+--echo # This use case is similar to precedence one except it does rollback
+--echo # XA transaction instead commit it. Therefore every prepared statement
+--echo # is executed only once except the last XA ROLLBACK.
+
+--echo # Start a new XA transaction
+EXECUTE stmt_1;
+
+INSERT INTO t1 VALUES (1);
+
+--echo # End the current XA transaction
+EXECUTE stmt_2;
+
+--echo # Prepare the current XA transaction for finalization
+EXECUTE stmt_3;
+
+--echo # Rolback the current XA transaction
+EXECUTE stmt_6;
+
+--echo # Execute the same prepared statement the second time to check that
+--echo # no internal structures used for handling the statement 'XA ROLLBACK'
+--echo # were damaged. Execution of this statement the second time expectedly
+--echo # results in emitting the error XAER_NOTA since the XA transaction
+--echo # with XID 'xid1' has been finalized and no more exists.
+--error ER_XAER_NOTA
+EXECUTE stmt_6;
+
+--echo # Clean up
+DROP TABLE t1;
+DEALLOCATE PREPARE stmt_1;
+DEALLOCATE PREPARE stmt_2;
+DEALLOCATE PREPARE stmt_3;
+DEALLOCATE PREPARE stmt_4;
+DEALLOCATE PREPARE stmt_5;
+DEALLOCATE PREPARE stmt_6;
+
+--echo # Test case 22: Check that the  CREATE SERVER/ALTER SERVER/DROP SERVER
+--echo # statements can be executed as prepared statements.
+
+PREPARE stmt_1 FROM "CREATE SERVER s FOREIGN DATA WRAPPER mysql OPTIONS (USER 'u1', HOST '127.0.0.1')";
+PREPARE stmt_2 FROM "ALTER SERVER s OPTIONS (USER 'u2')";
+PREPARE stmt_3 FROM "DROP SERVER s";
+
+EXECUTE stmt_1;
+--echo # Execute the same prepared statement the second time to check that
+--echo # no internal structures used for handling the 'CREATE SERVER' statement
+--echo # were damaged. Execution of this statement the second time expectedly
+--echo # results in emitting the error ER_FOREIGN_SERVER_EXISTS
+--echo # since a server with same has just been created.
+--error ER_FOREIGN_SERVER_EXISTS
+EXECUTE stmt_1;
+
+EXECUTE stmt_2;
+--echo # Execute the same prepared statement the second time to check that
+--echo # no internal structures used for handling the 'ALTER SERVER' statement
+--echo # were damaged.
+EXECUTE stmt_2;
+
+EXECUTE stmt_3;
+--echo # Execute the same prepared statement the second time to check that
+--echo # no internal structures used for handling the 'DROP SERVER' statement
+--echo # were damaged. Execution of this statement the second time expectedly
+--echo # results in emitting the error ER_FOREIGN_SERVER_DOESNT_EXIST
+--echo # since the server with same has just been dropped.
+--error ER_FOREIGN_SERVER_DOESNT_EXIST
+EXECUTE stmt_3;
+
+--echo # Clean up
+DEALLOCATE PREPARE stmt_1;
+DEALLOCATE PREPARE stmt_2;
+DEALLOCATE PREPARE stmt_3;
+
+--echo # Test case 23: Check that the CREATE EVENT/ALTER EVENT/DROP EVENT
+--echo # statements can be executed as a prepared statement
+
+PREPARE stmt_1 FROM "CREATE EVENT e1 ON SCHEDULE AT CURRENT_TIMESTAMP - INTERVAL 1 DAY ON COMPLETION PRESERVE DO SET @a=1";
+
+PREPARE stmt_2 FROM "ALTER EVENT e1 COMMENT 'New comment'";
+
+PREPARE stmt_3 FROM "DROP EVENT e1";
+--echo # Create the event e1 that specifies time in past. Such event is created
+--echo # just for the sake of its existence and never will be triggered.
+--echo # Disable warnings temprorary in order to hide the following warnings
+--echo # generated in result of execution the 'CREATE EVENT' statement:
+--echo #   "1544 | Event execution time is in the past. Event has been disabled"
+--echo #   "1105 | Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it."
+
+--disable_warnings
+EXECUTE stmt_1;
+
+--echo # Execute the same prepared statement the second time to check that
+--echo # no internal structures used for handling the 'CREATE EVENT' statement
+--echo # were damaged. Execution of this statement the second time expectedly
+--echo # results in emitting the error ER_EVENT_ALREADY_EXISTS since the event
+--echo # with the same name has just been created.
+--error ER_EVENT_ALREADY_EXISTS
+EXECUTE stmt_1;
+
+--enable_warnings
+
+--echo # Alter event e1
+EXECUTE stmt_2;
+--echo # Execute the same prepared statement the second time to check that
+--echo # no internal structures used for handling the 'ALTER EVENT' statement
+--echo # were damaged.
+EXECUTE stmt_2;
+
+--echo # Drop event e1
+EXECUTE stmt_3;
+--echo # Execute the same prepared statement the second time to check that
+--echo # no internal structures used for handling the 'DROP EVENT' statement
+--echo # were damaged. Execution of this statement the second time expectedly
+--echo # results in emitting the error ER_EVENT_DOESNT_EXIST since the event
+--echo # with the same name has just been dropped.
+--error ER_EVENT_DOES_NOT_EXIST
+EXECUTE stmt_3;
+--echo # Clean up
+DEALLOCATE PREPARE stmt_1;
+DEALLOCATE PREPARE stmt_2;
+DEALLOCATE PREPARE stmt_3;
+
+--echo # Test case 24: Check that the SIGNAL and RESIGNAL statements
+--echo # can be executed as a prepared statement
+PREPARE stmt_1 FROM "SIGNAL SQLSTATE '45000' SET MYSQL_ERRNO=30001, MESSAGE_TEXT='Hello, world!'";
+--error 30001
+EXECUTE stmt_1;
+--echo # Execute the same prepared statement the second time to check that
+--echo # no internal structures used for handling the 'SIGNAL' statement
+--echo # were damaged.
+--error 30001
+EXECUTE stmt_1;
+
+PREPARE stmt_1 FROM 'RESIGNAL SET MYSQL_ERRNO = 5';
+--error ER_RESIGNAL_WITHOUT_ACTIVE_HANDLER
+EXECUTE stmt_1;
+--echo # Execute the same prepared statement the second time to check that
+--echo # no internal structures used for handling the 'RESIGNAL' statement
+--echo # were damaged.
+--error ER_RESIGNAL_WITHOUT_ACTIVE_HANDLER
+EXECUTE stmt_1;
+
+--echo # Clean up
+DEALLOCATE PREPARE stmt_1;
+
+--echo # Test case 25: Check that the 'SHOW RELAYLOG EVENTS' statement can be
+--echo # executed as a prepared statement.
+PREPARE stmt_1 FROM 'SHOW RELAYLOG EVENTS';
+EXECUTE stmt_1;
+--echo # Execute the same prepared statement the second time to check that
+--echo # no internal structures used for handling the 'SHOW RELAYLOG EVENTS'
+--echo # statement were damaged.
+EXECUTE stmt_1;
+--echo # Clean up
+DEALLOCATE PREPARE stmt_1;
+
+--echo # Test case 26: Check the 'GET DIAGNOSTICS' statement
+--echo # can be executed as a prepared statement
+PREPARE stmt_1 FROM 'GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT';
+
+--echo # Query from non existent table to fill the diagnostics area
+--echo # with information
+--error ER_NO_SUCH_TABLE
+SELECT * FROM non_existent_table_1;
+EXECUTE stmt_1;
+--echo # Check that information from diagnostics area has been retrieved
+SELECT @sqlstate, @errno, @text;
+
+--error ER_NO_SUCH_TABLE
+SELECT * FROM non_existent_table_1;
+--echo # Execute the same prepared statement the second time to check that
+--echo # no internal structures used for handling the
+--echo # 'GET DIAGNOSTICS CONDITION' statement were damaged.
+EXECUTE stmt_1;
+
+--echo # Clean up
+DEALLOCATE PREPARE stmt_1;
+
+--echo # Test case 27: Check the the statement 'EXECUTE IMMEDIATE'
+--echo # can be executed as a prepared statement
+
+PREPARE stmt_1 FROM "EXECUTE IMMEDIATE 'SELECT 1'";
+EXECUTE stmt_1;
+--echo # Execute the same prepared statement the second time to check that
+--echo # no internal structures used for handling the 'EXECUTE IMMEDIATE'
+--echo # statement were damaged.
+
+--echo # Clean up
+DEALLOCATE PREPARE stmt_1;
+
+--echo # Test Test case 28: Check the statements 'BACKUP'/'BACKUP UNLOCK'
+--echo # can be executed as a prepared statement
+CREATE TABLE t1 (a INT);
+PREPARE stmt_1 FROM 'BACKUP LOCK t1';
+PREPARE stmt_2 FROM 'BACKUP UNLOCK';
+
+EXECUTE stmt_1;
+--echo # Execute the same prepared statement the second time to check that
+--echo # no internal structures used for handling the 'BACKUP LOCK'
+--echo # statement were damaged.
+EXECUTE stmt_1;
+
+EXECUTE stmt_2;
+--echo # Execute the same prepared statement the second time to check that
+--echo # no internal structures used for handling the 'BACKUP UNLOCK'
+--echo # statement were damaged.
+EXECUTE stmt_2;
+
+--echo # Clean up
+DROP TABLE t1;
+DEALLOCATE PREPARE stmt_1;
+DEALLOCATE PREPARE stmt_2;
+
+--echo # Test Test case 29: Check the statements 'CREATE/ALTER/DROP TABLEPSPACE'
+--echo # can be executed as a prepared statement
+
+PREPARE stmt_1 FROM "CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENGINE=InnoDB";
+PREPARE stmt_2 FROM "ALTER TABLESPACE ts1 ADD DATAFILE 'ts1_1.ibd' ENGINE=InnoDB";
+PREPARE stmt_3 FROM "DROP TABLESPACE ts1 ENGINE=InnoDB";
+
+# Since MariaDB supports for tablespaces only on syntax level disable warnings
+# before run CREATE/ALTER/DROP TABLESPACE statements in order to exclude
+# the following in result output
+#   Warning 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE
+--disable_warnings
+
+EXECUTE stmt_1;
+--echo # Execute the same prepared statement the second time to check that
+--echo # no internal structures used for handling the 'CREATE TABLESPACE'
+--echo # statement were damaged.
+# Since the 'CREATE TABLESPACE' statement is supported by MariaDB on syntax
+# level only the second invocation of the CREATE TABLESPACE statement for
+# the same tablespace name doesn't lead to error.
+EXECUTE stmt_1;
+
+EXECUTE stmt_2;
+--echo # Execute the same prepared statement the second time to check that
+--echo # no internal structures used for handling the 'ALTER TABLESPACE'
+--echo # statement were damaged.
+EXECUTE stmt_2;
+
+EXECUTE stmt_3;
+--echo # Execute the same prepared statement the second time to check that
+--echo # no internal structures used for handling the 'DROP TABLESPACE'
+--echo # statement were damaged.
+
+# Since the 'DROP TABLESPACE' statement is supported by MariaDB on syntax
+# level only the second invocation of the DROP TABLESPACE statement for
+# the same tablespace name doesn't lead to an error that tablespace
+# doesn't exist.
+EXECUTE stmt_3;
+
+--enable_warnings
+
+DEALLOCATE PREPARE stmt_1;
+DEALLOCATE PREPARE stmt_2;
+DEALLOCATE PREPARE stmt_3;
+
+--enable_ps_protocol
+SET default_storage_engine= @save_storage_engine;
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..3b56d9a1a5b
--- /dev/null
+++ b/mysql-test/main/ps_missed_cmds_bin_prot.result
@@ -0,0 +1,264 @@
+#
+# 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
+HELP `ALTER SERVER`;
+name	description	example
+ALTER SERVER	Syntax
+------ 
+ALTER SERVER server_name
+ OPTIONS (option [, option] ...)
+ 
+Description
+----------- 
+Alters the server information for server_name, adjusting the
+specified
+options as per the CREATE SERVER command. The corresponding
+fields in the mysql.servers table are updated accordingly.
+This statement requires the SUPER privilege.
+ 
+Examples
+-------- 
+ALTER SERVER s OPTIONS (USER 'sally');
+ 
+
+
+URL: https://mariadb.com/kb/en/library/alter-server/	
+# 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 case 18: Check that the statements CREATE EVENT/ALTER EVENT/
+# DROP EVENT can be executed as a prepared statement
+# Create the event e1 that specifies time in past. Such event is created
+# just for the sake of its existence and never will be triggered.
+# Disable warnings temprorary in order to hide the following warnings
+# generated in result of execution the 'CREATE EVENT' statement:
+#   "1544 | Event execution time is in the past. Event has been disabled"
+#   "1105 | Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it."
+CREATE EVENT e1 ON SCHEDULE AT CURRENT_TIMESTAMP - INTERVAL 1 DAY ON COMPLETION PRESERVE DO SET @a=1;
+ALTER EVENT e1 COMMENT 'New comment';
+DROP EVENT IF EXISTS e1;
+# Test case 19: Check that the statement 'SHOW RELAYLOG EVENTS' can be
+# executed as a prepared statement.
+SHOW RELAYLOG EVENTS;
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+# Test case 20: Check the the statement 'EXECUTE IMMEDIATE'
+# can be executed as a prepared statement
+EXECUTE IMMEDIATE 'SELECT 1';
+1
+1
+# 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;
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 00000000000..10ce9ebd60a
--- /dev/null
+++ b/mysql-test/main/ps_missed_cmds_bin_prot.test
@@ -0,0 +1,298 @@
+--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;
+SELECT * INTO OUTFILE 'load.data' FROM t1;
+
+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
+HELP `ALTER SERVER`;
+
+--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 case 18: Check that the statements CREATE EVENT/ALTER EVENT/
+--echo # DROP EVENT can be executed as a prepared statement
+
+--echo # Create the event e1 that specifies time in past. Such event is created
+--echo # just for the sake of its existence and never will be triggered.
+--echo # Disable warnings temprorary in order to hide the following warnings
+--echo # generated in result of execution the 'CREATE EVENT' statement:
+--echo #   "1544 | Event execution time is in the past. Event has been disabled"
+--echo #   "1105 | Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it."
+
+--disable_warnings
+
+CREATE EVENT e1 ON SCHEDULE AT CURRENT_TIMESTAMP - INTERVAL 1 DAY ON COMPLETION PRESERVE DO SET @a=1;
+
+ALTER EVENT e1 COMMENT 'New comment';
+
+DROP EVENT IF EXISTS e1;
+
+--enable_warnings
+
+--echo # Test case 19: Check that the statement 'SHOW RELAYLOG EVENTS' can be
+--echo # executed as a prepared statement.
+SHOW RELAYLOG EVENTS;
+
+--echo # Test case 20: Check the the statement 'EXECUTE IMMEDIATE'
+--echo # can be executed as a prepared statement
+
+EXECUTE IMMEDIATE 'SELECT 1';
+
+--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';
+
+--echo # Test Test case 29: Check the statements 'CREATE/ALTER/DROP TABLEPSPACE'
+--echo # can be executed as a prepared statement
+
+# Since MariaDB supports for tablespaces only on syntax level disable warnings
+# before run CREATE/ALTER/DROP TABLESPACE statements in order to exclude
+# the following in result output
+#   Warning 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE
+--disable_warnings
+
+CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENGINE=InnoDB;
+ALTER TABLESPACE ts1 ADD DATAFILE 'ts1_1.ibd' ENGINE=InnoDB;
+DROP TABLESPACE ts1 ENGINE=InnoDB;
+
+--enable_warnings
+
+SET default_storage_engine= @save_storage_engine;
diff --git a/mysql-test/main/query_cache.result b/mysql-test/main/query_cache.result
index 33e41a760f2..3a81b648171 100644
--- a/mysql-test/main/query_cache.result
+++ b/mysql-test/main/query_cache.result
@@ -1098,7 +1098,7 @@ call p1()//
 a
 1
 2
-drop procedure p1;
+drop procedure p1//
 create function f1() returns int
 begin
 Declare var1 int;
@@ -1111,13 +1111,13 @@ create procedure `p1`()
 begin
 select a, f1() from t1;
 end//
-SET @old_log_bin_trust_function_creators= @@global.log_bin_trust_function_creators;
-SET GLOBAL log_bin_trust_function_creators = 1;
+SET @old_log_bin_trust_function_creators= @@global.log_bin_trust_function_creators//
+SET GLOBAL log_bin_trust_function_creators = 1//
 call p1()//
 a	f1()
 1	2
 2	2
-SET GLOBAL log_bin_trust_function_creators = @old_log_bin_trust_function_creators;
+SET GLOBAL log_bin_trust_function_creators = @old_log_bin_trust_function_creators//
 drop procedure p1//
 drop function f1//
 drop table t1//
diff --git a/mysql-test/main/query_cache.test b/mysql-test/main/query_cache.test
index 939bf738fce..e49387dcaf4 100644
--- a/mysql-test/main/query_cache.test
+++ b/mysql-test/main/query_cache.test
@@ -810,7 +810,7 @@ open c1;
 select * from t1;
 end//
 call p1()//
-drop procedure p1;
+drop procedure p1//
 
 create function f1() returns int
 begin
@@ -822,10 +822,10 @@ create procedure `p1`()
 begin
  select a, f1() from t1;
 end//
-SET @old_log_bin_trust_function_creators= @@global.log_bin_trust_function_creators;
-SET GLOBAL log_bin_trust_function_creators = 1;
+SET @old_log_bin_trust_function_creators= @@global.log_bin_trust_function_creators//
+SET GLOBAL log_bin_trust_function_creators = 1//
 call p1()//
-SET GLOBAL log_bin_trust_function_creators = @old_log_bin_trust_function_creators;
+SET GLOBAL log_bin_trust_function_creators = @old_log_bin_trust_function_creators//
 drop procedure p1//
 drop function f1//
 
diff --git a/mysql-test/main/signal.result b/mysql-test/main/signal.result
index b5b479db017..6a7f1c1e750 100644
--- a/mysql-test/main/signal.result
+++ b/mysql-test/main/signal.result
@@ -561,9 +561,7 @@ ERROR 42000: Bad SQLSTATE: '00001'
 # PART 2: non preparable statements
 #
 prepare stmt from 'SIGNAL SQLSTATE \'23000\'';
-ERROR HY000: This command is not supported in the prepared statement protocol yet
 prepare stmt from 'RESIGNAL SQLSTATE \'23000\'';
-ERROR HY000: This command is not supported in the prepared statement protocol yet
 #
 # PART 3: runtime execution
 #
diff --git a/mysql-test/main/signal.test b/mysql-test/main/signal.test
index 22cfc080895..97cd4d9a5d0 100644
--- a/mysql-test/main/signal.test
+++ b/mysql-test/main/signal.test
@@ -669,10 +669,7 @@ delimiter ;$$
 --echo # PART 2: non preparable statements
 --echo #
 
---error ER_UNSUPPORTED_PS
 prepare stmt from 'SIGNAL SQLSTATE \'23000\'';
-
---error ER_UNSUPPORTED_PS
 prepare stmt from 'RESIGNAL SQLSTATE \'23000\'';
 
 --echo #
diff --git a/mysql-test/main/sp-dynamic.result b/mysql-test/main/sp-dynamic.result
index 9fa3bea1108..ad11763b523 100644
--- a/mysql-test/main/sp-dynamic.result
+++ b/mysql-test/main/sp-dynamic.result
@@ -64,9 +64,8 @@ execute stmt;
 deallocate prepare stmt;
 end|
 call p1()|
-ERROR HY000: This command is not supported in the prepared statement protocol yet
 call p1()|
-ERROR HY000: This command is not supported in the prepared statement protocol yet
+ERROR 42000: PROCEDURE p2 already exists
 drop procedure p1|
 create procedure p1()
 begin
@@ -75,9 +74,8 @@ execute stmt;
 deallocate prepare stmt;
 end|
 call p1()|
-ERROR HY000: This command is not supported in the prepared statement protocol yet
 call p1()|
-ERROR HY000: This command is not supported in the prepared statement protocol yet
+ERROR 42000: PROCEDURE test.p2 does not exist
 drop procedure p1|
 create procedure p1()
 begin
@@ -295,15 +293,15 @@ select * from t1|
 id	stmt_text	status
 1	select 1	supported
 2	flush tables	supported
-3	handler t1 open as ha	not supported
+3	handler t1 open as ha	supported
 4	analyze table t1	supported
-5	check table t1	not supported
+5	check table t1	supported
 6	checksum table t1	supported
-7	check table t1	not supported
+7	check table t1	supported
 8	optimize table t1	supported
 9	repair table t1	supported
 10	describe extended select * from t1	supported
-11	help help	not supported
+11	help help	supported
 12	show databases	supported
 13	show tables	supported
 14	show table status	supported
@@ -316,7 +314,7 @@ id	stmt_text	status
 21	call p1()	supported
 22	foo bar	syntax error
 23	create view v1 as select 1	supported
-24	alter view v1 as select 2	not supported
+24	alter view v1 as select 2	supported
 25	drop view v1	supported
 26	create table t2 (a int)	supported
 27	alter table t2 add (b int)	supported
diff --git a/mysql-test/main/sp-dynamic.test b/mysql-test/main/sp-dynamic.test
index 3a467e26818..5749a83a27b 100644
--- a/mysql-test/main/sp-dynamic.test
+++ b/mysql-test/main/sp-dynamic.test
@@ -68,9 +68,8 @@ begin
   execute stmt;
   deallocate prepare stmt;
 end|
---error ER_UNSUPPORTED_PS
 call p1()|
---error ER_UNSUPPORTED_PS
+--error ER_SP_ALREADY_EXISTS
 call p1()|
 drop procedure p1|
 create procedure p1()
@@ -79,9 +78,8 @@ begin
   execute stmt;
   deallocate prepare stmt;
 end|
---error ER_UNSUPPORTED_PS
 call p1()|
---error ER_UNSUPPORTED_PS
+--error ER_SP_DOES_NOT_EXIST
 call p1()|
 drop procedure p1|
 #
diff --git a/mysql-test/main/sp-security.result b/mysql-test/main/sp-security.result
index 2c48883a509..2571f9d8696 100644
--- a/mysql-test/main/sp-security.result
+++ b/mysql-test/main/sp-security.result
@@ -347,6 +347,7 @@ create function bug12812() returns char(2)
 begin
 return 'ok';
 end;
+|
 create user user_bug12812@localhost IDENTIFIED BY 'ABC'|
 connect  test_user_12812,localhost,user_bug12812,ABC,test;
 SELECT test.bug12812()|
diff --git a/mysql-test/main/sp-security.test b/mysql-test/main/sp-security.test
index e7790bf703a..e11e8911b60 100644
--- a/mysql-test/main/sp-security.test
+++ b/mysql-test/main/sp-security.test
@@ -465,6 +465,7 @@ create function bug12812() returns char(2)
 begin
   return 'ok';
 end;
+|
 create user user_bug12812@localhost IDENTIFIED BY 'ABC'|
 --replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
 connect (test_user_12812,localhost,user_bug12812,ABC,test)|
diff --git a/sql/item.cc b/sql/item.cc
index 90ab78cec0a..3cede11a415 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -5531,14 +5531,9 @@ Item_field::fix_outer_field(THD *thd, Field **from_field, Item **reference)
   */
   Name_resolution_context *last_checked_context= context;
   Item **ref= (Item **) not_found_item;
-  SELECT_LEX *current_sel= thd->lex->current_select;
   Name_resolution_context *outer_context= 0;
   SELECT_LEX *select= 0;
-  /* Currently derived tables cannot be correlated */
-  if ((current_sel->master_unit()->first_select()->get_linkage() !=
-       DERIVED_TABLE_TYPE) &&
-      current_sel->master_unit()->outer_select())
-    outer_context= context->outer_context;
+  outer_context= context->outer_context;
 
   /*
     This assert is to ensure we have an outer contex when *from_field
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index c39fbf54f13..381f015ca96 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -1270,7 +1270,8 @@ Item_singlerow_subselect::select_transformer(JOIN *join)
   DBUG_ASSERT(join->thd == thd);
 
   SELECT_LEX *select_lex= join->select_lex;
-  Query_arena *arena= thd->stmt_arena;
+  Query_arena *arena, backup;
+  arena= thd->activate_stmt_arena_if_needed(&backup);
 
   if (!select_lex->master_unit()->is_unit_op() &&
       !select_lex->table_list.elements &&
@@ -1286,12 +1287,7 @@ Item_singlerow_subselect::select_transformer(JOIN *join)
       !(select_lex->item_list.head()->type() == FIELD_ITEM ||
 	select_lex->item_list.head()->type() == REF_ITEM) &&
       !join->conds && !join->having &&
-      /*
-        switch off this optimization for prepare statement,
-        because we do not rollback this changes
-        TODO: make rollback for it, or special name resolving mode in 5.0.
-      */
-      !arena->is_stmt_prepare_or_first_sp_execute()
+      thd->stmt_arena->state != Query_arena::STMT_INITIALIZED_FOR_SP
       )
   {
     have_to_be_excluded= 1;
@@ -1310,6 +1306,8 @@ Item_singlerow_subselect::select_transformer(JOIN *join)
     substitution->fix_after_pullout(select_lex->outer_select(),
                                     &substitution, TRUE);
   }
+  if (arena)
+    thd->restore_active_arena(arena, &backup);
   DBUG_RETURN(false);
 }
 
diff --git a/sql/protocol.h b/sql/protocol.h
index 0a309ee75be..f095ad68a34 100644
--- a/sql/protocol.h
+++ b/sql/protocol.h
@@ -188,10 +188,6 @@ public:
   bool net_send_error(THD *thd, uint sql_errno, const char *err,
                       const char* sqlstate);
   void end_statement();
-
-  friend int send_answer_1(Protocol *protocol, String *s1, String *s2,
-                           String *s3);
-  friend int send_header_2(Protocol *protocol, bool for_category);
 };
 
 
diff --git a/sql/set_var.cc b/sql/set_var.cc
index 8e2e8b12a06..724225f1058 100644
--- a/sql/set_var.cc
+++ b/sql/set_var.cc
@@ -825,6 +825,11 @@ int set_var::check(THD *thd)
 */
 int set_var::light_check(THD *thd)
 {
+  if (var->is_readonly())
+  {
+    my_error(ER_INCORRECT_GLOBAL_LOCAL_VAR, MYF(0), var->name.str, "read only");
+    return -1;
+  }
   if (var->check_type(type))
   {
     int err= type == OPT_GLOBAL ? ER_LOCAL_VARIABLE : ER_GLOBAL_VARIABLE;
diff --git a/sql/sql_admin.cc b/sql/sql_admin.cc
index aa66291929c..1c32e9d57cc 100644
--- a/sql/sql_admin.cc
+++ b/sql/sql_admin.cc
@@ -489,6 +489,38 @@ static bool wsrep_toi_replication(THD *thd, TABLE_LIST *tables)
 }
 #endif /* WITH_WSREP */
 
+
+/**
+  Collect field names of result set that will be sent to a client
+
+  @param      thd     Thread data object
+  @param[out] fields  List of fields whose metadata should be collected for
+                      sending to client
+*/
+
+void fill_check_table_metadata_fields(THD *thd, List<Item>* fields)
+{
+  Item *item;
+
+  item= new (thd->mem_root) Item_empty_string(thd, "Table", NAME_CHAR_LEN * 2);
+  item->set_maybe_null();
+  fields->push_back(item, thd->mem_root);
+
+  item= new (thd->mem_root) Item_empty_string(thd, "Op", 10);
+  item->set_maybe_null();
+  fields->push_back(item, thd->mem_root);
+
+  item= new (thd->mem_root) Item_empty_string(thd, "Msg_type", 10);
+  item->set_maybe_null();
+  fields->push_back(item, thd->mem_root);
+
+  item= new (thd->mem_root) Item_empty_string(thd, "Msg_text",
+                                              SQL_ADMIN_MSG_TEXT_SIZE);
+  item->set_maybe_null();
+  fields->push_back(item, thd->mem_root);
+}
+
+
 /*
   RETURN VALUES
     FALSE Message sent to net (admin operation went ok)
@@ -512,7 +544,6 @@ static bool mysql_admin_table(THD* thd, TABLE_LIST* tables,
 {
   TABLE_LIST *table;
   List<Item> field_list;
-  Item *item;
   Protocol *protocol= thd->protocol;
   LEX *lex= thd->lex;
   int result_code;
@@ -524,21 +555,8 @@ static bool mysql_admin_table(THD* thd, TABLE_LIST* tables,
   DBUG_ENTER("mysql_admin_table");
   DBUG_PRINT("enter", ("extra_open_options: %u", extra_open_options));
 
-  field_list.push_back(item= new (thd->mem_root)
-                       Item_empty_string(thd, "Table",
-                                         NAME_CHAR_LEN * 2), thd->mem_root);
-  item->set_maybe_null();
-  field_list.push_back(item= new (thd->mem_root)
-                       Item_empty_string(thd, "Op", 10), thd->mem_root);
-  item->set_maybe_null();
-  field_list.push_back(item= new (thd->mem_root)
-                       Item_empty_string(thd, "Msg_type", 10), thd->mem_root);
-  item->set_maybe_null();
-  field_list.push_back(item= new (thd->mem_root)
-                       Item_empty_string(thd, "Msg_text",
-                                         SQL_ADMIN_MSG_TEXT_SIZE),
-                       thd->mem_root);
-  item->set_maybe_null();
+  fill_check_table_metadata_fields(thd, &field_list);
+
   if (protocol->send_result_set_metadata(&field_list,
                             Protocol::SEND_NUM_ROWS | Protocol::SEND_EOF))
     DBUG_RETURN(TRUE);
diff --git a/sql/sql_admin.h b/sql/sql_admin.h
index d31726d32a4..0c7f1c3cee5 100644
--- a/sql/sql_admin.h
+++ b/sql/sql_admin.h
@@ -24,7 +24,7 @@ bool mysql_assign_to_keycache(THD* thd, TABLE_LIST* table_list,
 bool mysql_preload_keys(THD* thd, TABLE_LIST* table_list);
 int reassign_keycache_tables(THD* thd, KEY_CACHE *src_cache,
                              KEY_CACHE *dst_cache);
-
+void fill_check_table_metadata_fields(THD *thd, List<Item>* fields);
 /**
   Sql_cmd_analyze_table represents the ANALYZE TABLE statement.
 */
diff --git a/sql/sql_help.cc b/sql/sql_help.cc
index ce29846e2f8..f9932f11798 100644
--- a/sql/sql_help.cc
+++ b/sql/sql_help.cc
@@ -430,6 +430,46 @@ void get_all_items_for_category(THD *thd, TABLE *items, Field *pfname,
   DBUG_VOID_RETURN;
 }
 
+
+/**
+  Collect field names of HELP header that will be sent to a client
+
+  @param      thd         Thread data object
+  @param[out] field_list  List of fields whose metadata should be collected for
+                          sending to client
+*/
+
+static void fill_answer_1_fields(THD *thd, List<Item> *field_list)
+{
+  MEM_ROOT *mem_root= thd->mem_root;
+
+  field_list->push_back(new (mem_root) Item_empty_string(thd, "name", 64),
+                        mem_root);
+  field_list->push_back(new (mem_root) Item_empty_string(thd, "description",
+                                                         1000),
+                        mem_root);
+  field_list->push_back(new (mem_root) Item_empty_string(thd, "example", 1000),
+                        mem_root);
+}
+
+
+/**
+  Send metadata of an answer on help request to a client
+
+  @param protocol   protocol for sending
+*/
+
+static bool send_answer_1_metadata(Protocol *protocol)
+{
+  List<Item> field_list;
+
+  fill_answer_1_fields(protocol->thd, &field_list);
+  return protocol->send_result_set_metadata(&field_list,
+                                            Protocol::SEND_NUM_ROWS |
+                                            Protocol::SEND_EOF);
+}
+
+
 /*
   Send to client answer for help request
 
@@ -455,22 +495,11 @@ void get_all_items_for_category(THD *thd, TABLE *items, Field *pfname,
     0		Successeful send
 */
 
-int send_answer_1(Protocol *protocol, String *s1, String *s2, String *s3)
+static int send_answer_1(Protocol *protocol, String *s1, String *s2, String *s3)
 {
-  THD *thd= protocol->thd;
-  MEM_ROOT *mem_root= thd->mem_root;
   DBUG_ENTER("send_answer_1");
 
-  List<Item> field_list;
-  field_list.push_back(new (mem_root) Item_empty_string(thd, "name", 64),
-                       mem_root);
-  field_list.push_back(new (mem_root) Item_empty_string(thd, "description", 1000),
-                       mem_root);
-  field_list.push_back(new (mem_root) Item_empty_string(thd, "example", 1000),
-                       mem_root);
-
-  if (protocol->send_result_set_metadata(&field_list,
-                            Protocol::SEND_NUM_ROWS | Protocol::SEND_EOF))
+  if (send_answer_1_metadata(protocol))
     DBUG_RETURN(1);
 
   protocol->prepare_for_resend();
@@ -483,13 +512,39 @@ int send_answer_1(Protocol *protocol, String *s1, String *s2, String *s3)
 }
 
 
+/**
+  Collect field names of HELP header that will be sent to a client
+
+  @param      thd          Thread data object
+  @param[out] field_list   List of fields whose metadata should be collected for
+                           sending to client
+  @param      for_category need column 'source_category_name'
+*/
+
+static void fill_header_2_fields(THD *thd, List<Item> *field_list,
+                                 bool for_category)
+{
+  MEM_ROOT *mem_root= thd->mem_root;
+  if (for_category)
+    field_list->push_back(new (mem_root)
+                          Item_empty_string(thd, "source_category_name", 64),
+                          mem_root);
+  field_list->push_back(new (mem_root)
+                        Item_empty_string(thd, "name", 64),
+                        mem_root);
+  field_list->push_back(new (mem_root)
+                        Item_empty_string(thd, "is_it_category", 1),
+                        mem_root);
+}
+
+
 /*
   Send to client help header
 
   SYNOPSIS
    send_header_2()
     protocol       - protocol for sending
-    is_it_category - need column 'source_category_name'
+    for_category   - need column 'source_category_name'
 
   IMPLEMENTATION
    +-                    -+
@@ -504,22 +559,12 @@ int send_answer_1(Protocol *protocol, String *s1, String *s2, String *s3)
     result of protocol->send_result_set_metadata
 */
 
-int send_header_2(Protocol *protocol, bool for_category)
+static int send_header_2(Protocol *protocol, bool for_category)
 {
-  THD *thd= protocol->thd;
-  MEM_ROOT *mem_root= thd->mem_root;
   DBUG_ENTER("send_header_2");
   List<Item> field_list;
-  if (for_category)
-    field_list.push_back(new (mem_root)
-                         Item_empty_string(thd, "source_category_name", 64),
-                         mem_root);
-  field_list.push_back(new (mem_root)
-                       Item_empty_string(thd, "name", 64),
-                       mem_root);
-  field_list.push_back(new (mem_root)
-                       Item_empty_string(thd, "is_it_category", 1),
-                       mem_root);
+
+  fill_header_2_fields(protocol->thd, &field_list, for_category);
   DBUG_RETURN(protocol->send_result_set_metadata(&field_list,
                                                  Protocol::SEND_NUM_ROWS |
                                                  Protocol::SEND_EOF));
@@ -639,7 +684,6 @@ SQL_SELECT *prepare_simple_select(THD *thd, Item *cond,
     thd      Thread handler
     mask     mask for compare with name
     mlen     length of mask
-    tables   list of tables, used in WHERE
     table    goal table
     pfname   field "name" in table
 
@@ -650,8 +694,7 @@ SQL_SELECT *prepare_simple_select(THD *thd, Item *cond,
 */
 
 SQL_SELECT *prepare_select_for_name(THD *thd, const char *mask, size_t mlen,
-				    TABLE_LIST *tables, TABLE *table,
-				    Field *pfname, int *error)
+				    TABLE *table, Field *pfname, int *error)
 {
   MEM_ROOT *mem_root= thd->mem_root;
   Item *cond= new (mem_root)
@@ -668,6 +711,205 @@ SQL_SELECT *prepare_select_for_name(THD *thd, const char *mask, size_t mlen,
 }
 
 
+/**
+  Initialize the TABLE_LIST with tables used in HELP statement handling.
+
+  @param thd      Thread handler
+  @param tables   Array of four TABLE_LIST objects to initialize with data
+                  about the tables help_topic, help_category, help_relation,
+                  help_keyword
+*/
+
+static void initialize_tables_for_help_command(THD *thd, TABLE_LIST *tables)
+{
+  LEX_CSTRING MYSQL_HELP_TOPIC_NAME=    {STRING_WITH_LEN("help_topic") };
+  LEX_CSTRING MYSQL_HELP_CATEGORY_NAME= {STRING_WITH_LEN("help_category") };
+  LEX_CSTRING MYSQL_HELP_RELATION_NAME= {STRING_WITH_LEN("help_relation") };
+  LEX_CSTRING MYSQL_HELP_KEYWORD_NAME=  {STRING_WITH_LEN("help_keyword") };
+
+  tables[0].init_one_table(&MYSQL_SCHEMA_NAME, &MYSQL_HELP_TOPIC_NAME, 0,
+                           TL_READ);
+  tables[1].init_one_table(&MYSQL_SCHEMA_NAME, &MYSQL_HELP_CATEGORY_NAME, 0,
+                           TL_READ);
+  tables[2].init_one_table(&MYSQL_SCHEMA_NAME, &MYSQL_HELP_RELATION_NAME, 0,
+                           TL_READ);
+  tables[3].init_one_table(&MYSQL_SCHEMA_NAME, &MYSQL_HELP_KEYWORD_NAME, 0,
+                           TL_READ);
+  tables[0].next_global= tables[0].next_local=
+    tables[0].next_name_resolution_table= &tables[1];
+  tables[1].next_global= tables[1].next_local=
+    tables[1].next_name_resolution_table= &tables[2];
+  tables[2].next_global= tables[2].next_local=
+    tables[2].next_name_resolution_table= &tables[3];
+}
+
+
+/**
+  Setup tables and fields for query.
+
+  @param thd               Thread handler
+  @param first_select_lex  SELECT_LEX of the parsed statement
+  @param tables            Array of tables used in handling of the HELP
+                           statement
+  @param used_fields       Array of fields used in handling of the HELP
+                           statement
+
+  @return false on success, else true.
+*/
+
+template <size_t M, size_t N>
+static bool init_items_for_help_command(THD *thd,
+                                        SELECT_LEX *first_select_lex,
+                                        TABLE_LIST (&tables)[M],
+                                        st_find_field (& used_fields)[N])
+{
+  List<TABLE_LIST> leaves;
+
+  /*
+    Initialize tables and fields to be usable from items.
+    tables do not contain VIEWs => we can pass 0 as conds
+  */
+  first_select_lex->context.table_list=
+    first_select_lex->context.first_name_resolution_table=
+      &tables[0];
+
+  if (setup_tables(thd, &first_select_lex->context,
+                   &first_select_lex->top_join_list,
+                   &tables[0], leaves, false, false))
+    return true;
+
+  memcpy((char*) used_fields, (char*) init_used_fields,
+         sizeof(used_fields[0]) * N);
+  if (init_fields(thd, &tables[0], used_fields, N))
+    return true;
+
+  for (size_t i= 0; i < M; i++)
+    tables[i].table->file->init_table_handle_for_HANDLER();
+
+  return false;
+}
+
+
+/**
+  Prepare (in the sense of prepared statement) the HELP statement.
+
+  @param thd          Thread handler
+  @param mask         string value passed to the HELP statement
+  @oaram[out] fields  fields for result set metadata
+
+  @return false on success, else true.
+*/
+
+bool mysqld_help_prepare(THD *thd, const char *mask, List<Item> *fields)
+{
+  TABLE_LIST tables[4];
+  st_find_field used_fields[array_elements(init_used_fields)];
+  SQL_SELECT *select;
+
+  List<String> topics_list;
+
+  Sql_mode_instant_remove sms(thd, MODE_PAD_CHAR_TO_FULL_LENGTH);
+  initialize_tables_for_help_command(thd, tables);
+
+  /*
+    HELP must be available under LOCK TABLES.
+    Reset and backup the current open tables state to
+    make it possible.
+  */
+  start_new_trans new_trans(thd);
+
+  if (open_system_tables_for_read(thd, tables))
+    return true;
+
+  auto cleanup_and_return= [&](bool ret)
+  {
+    thd->commit_whole_transaction_and_close_tables();
+    new_trans.restore_old_transaction();
+    return ret;
+  };
+
+  if (init_items_for_help_command(thd, thd->lex->first_select_lex(),
+                                  tables, used_fields))
+    return cleanup_and_return(false);
+
+  size_t mlen= strlen(mask);
+  int error;
+
+  /*
+    Prepare the query 'SELECT * FROM help_topic WHERE name LIKE mask'
+    for execution
+  */
+  if (!(select=
+        prepare_select_for_name(thd,mask, mlen, tables[0].table,
+                                used_fields[help_topic_name].field, &error)))
+    return cleanup_and_return(true);
+
+  String name, description, example;
+  /*
+    Run the query 'SELECT * FROM help_topic WHERE name LIKE mask'
+  */
+  int count_topics= search_topics(thd, tables[0].table, used_fields,
+                                  select, &topics_list,
+                                  &name, &description, &example);
+  delete select;
+
+  if (thd->is_error())
+    return cleanup_and_return(true);
+
+  if (count_topics == 0)
+  {
+    int UNINIT_VAR(key_id);
+    /*
+      Prepare the query 'SELECT * FROM help_keyword WHERE name LIKE mask'
+      for execution
+    */
+    if (!(select=
+        prepare_select_for_name(thd, mask, mlen, tables[3].table,
+                                used_fields[help_keyword_name].field,
+                                &error)))
+      return cleanup_and_return(true);
+
+    /*
+      Run the query 'SELECT * FROM help_keyword WHERE name LIKE mask'
+    */
+    count_topics= search_keyword(thd,tables[3].table, used_fields, select,
+                                 &key_id);
+    delete select;
+    count_topics= (count_topics != 1) ? 0 :
+        get_topics_for_keyword(thd, tables[0].table, tables[2].table,
+                               used_fields, key_id, &topics_list, &name,
+                               &description, &example);
+
+  }
+
+  if (count_topics == 0)
+  {
+    if (!(select=
+        prepare_select_for_name(thd, mask, mlen, tables[1].table,
+                                used_fields[help_category_name].field,
+                                &error)))
+      return cleanup_and_return(true);
+
+    List<String> categories_list;
+    int16 category_id;
+    int count_categories= search_categories(thd, tables[1].table, used_fields,
+                                            select,
+                                            &categories_list,&category_id);
+    delete select;
+    if (count_categories == 1)
+      fill_header_2_fields(thd, fields, true);
+    else
+      fill_header_2_fields(thd, fields, false);
+  }
+  else if (count_topics == 1)
+    fill_answer_1_fields(thd, fields);
+  else
+    fill_header_2_fields(thd, fields, false);
+
+  return cleanup_and_return(false);
+}
+
+
 /*
   Server-side function 'help'
 
@@ -685,30 +927,15 @@ static bool mysqld_help_internal(THD *thd, const char *mask)
   Protocol *protocol= thd->protocol;
   SQL_SELECT *select;
   st_find_field used_fields[array_elements(init_used_fields)];
-  List<TABLE_LIST> leaves;
   TABLE_LIST tables[4];
   List<String> topics_list, categories_list, subcategories_list;
   String name, description, example;
   int count_topics, count_categories, error;
   size_t mlen= strlen(mask);
-  size_t i;
   MEM_ROOT *mem_root= thd->mem_root;
-  LEX_CSTRING MYSQL_HELP_TOPIC_NAME=    {STRING_WITH_LEN("help_topic") };
-  LEX_CSTRING MYSQL_HELP_CATEGORY_NAME= {STRING_WITH_LEN("help_category") };
-  LEX_CSTRING MYSQL_HELP_RELATION_NAME= {STRING_WITH_LEN("help_relation") };
-  LEX_CSTRING MYSQL_HELP_KEYWORD_NAME=  {STRING_WITH_LEN("help_keyword") };
   DBUG_ENTER("mysqld_help");
 
-  tables[0].init_one_table(&MYSQL_SCHEMA_NAME, &MYSQL_HELP_TOPIC_NAME, 0, TL_READ);
-  tables[1].init_one_table(&MYSQL_SCHEMA_NAME, &MYSQL_HELP_CATEGORY_NAME, 0, TL_READ);
-  tables[2].init_one_table(&MYSQL_SCHEMA_NAME, &MYSQL_HELP_RELATION_NAME, 0, TL_READ);
-  tables[3].init_one_table(&MYSQL_SCHEMA_NAME, &MYSQL_HELP_KEYWORD_NAME, 0, TL_READ);
-  tables[0].next_global= tables[0].next_local= 
-    tables[0].next_name_resolution_table= &tables[1];
-  tables[1].next_global= tables[1].next_local= 
-    tables[1].next_name_resolution_table= &tables[2];
-  tables[2].next_global= tables[2].next_local= 
-    tables[2].next_name_resolution_table= &tables[3];
+  initialize_tables_for_help_command(thd, tables);
 
   /*
     HELP must be available under LOCK TABLES. 
@@ -720,25 +947,12 @@ static bool mysqld_help_internal(THD *thd, const char *mask)
   if (open_system_tables_for_read(thd, tables))
     goto error2;
 
-  /*
-    Init tables and fields to be usable from items
-    tables do not contain VIEWs => we can pass 0 as conds
-  */
-  thd->lex->first_select_lex()->context.table_list=
-    thd->lex->first_select_lex()->context.first_name_resolution_table=
-    &tables[0];
-  if (setup_tables(thd, &thd->lex->first_select_lex()->context,
-                   &thd->lex->first_select_lex()->top_join_list,
-                   tables, leaves, FALSE, FALSE))
+  if (init_items_for_help_command(thd, thd->lex->first_select_lex(),
+                                  tables, used_fields))
     goto error;
-  memcpy((char*) used_fields, (char*) init_used_fields, sizeof(used_fields));
-  if (init_fields(thd, tables, used_fields, array_elements(used_fields)))
-    goto error;
-  for (i=0; i<sizeof(tables)/sizeof(TABLE_LIST); i++)
-    tables[i].table->file->init_table_handle_for_HANDLER();
 
   if (!(select=
-	prepare_select_for_name(thd,mask,mlen,tables,tables[0].table,
+	prepare_select_for_name(thd,mask,mlen,tables[0].table,
 				used_fields[help_topic_name].field,&error)))
     goto error;
 
@@ -754,7 +968,7 @@ static bool mysqld_help_internal(THD *thd, const char *mask)
   {
     int UNINIT_VAR(key_id);
     if (!(select=
-          prepare_select_for_name(thd,mask,mlen,tables,tables[3].table,
+          prepare_select_for_name(thd,mask,mlen,tables[3].table,
                                   used_fields[help_keyword_name].field,
                                   &error)))
       goto error;
@@ -773,7 +987,7 @@ static bool mysqld_help_internal(THD *thd, const char *mask)
     int16 category_id;
     Field *cat_cat_id= used_fields[help_category_parent_category_id].field;
     if (!(select=
-          prepare_select_for_name(thd,mask,mlen,tables,tables[1].table,
+          prepare_select_for_name(thd,mask,mlen,tables[1].table,
                                   used_fields[help_category_name].field,
                                   &error)))
       goto error;
@@ -841,7 +1055,7 @@ static bool mysqld_help_internal(THD *thd, const char *mask)
 	send_variant_2_list(mem_root,protocol, &topics_list, "N", 0))
       goto error;
     if (!(select=
-          prepare_select_for_name(thd,mask,mlen,tables,tables[1].table,
+          prepare_select_for_name(thd,mask,mlen,tables[1].table,
                                   used_fields[help_category_name].field,&error)))
       goto error;
     search_categories(thd, tables[1].table, used_fields,
diff --git a/sql/sql_help.h b/sql/sql_help.h
index cb3314b756c..b0117649f03 100644
--- a/sql/sql_help.h
+++ b/sql/sql_help.h
@@ -25,4 +25,6 @@ class THD;
 
 bool mysqld_help (THD *thd, const char *text);
 
+bool mysqld_help_prepare(THD *thd, const char *text, List<Item> *fields);
+
 #endif /* SQL_HELP_INCLUDED */
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 45e5f2381bd..6df1996409c 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -869,7 +869,7 @@ public:
   // Ensures that at least all members used during cleanup() are initialized.
   st_select_lex_unit()
     : union_result(NULL), table(NULL),  result(NULL), fake_select_lex(NULL),
-      cleaned(false), bag_set_op_optimized(false),
+      last_procedure(NULL),cleaned(false), bag_set_op_optimized(false),
       have_except_all_or_intersect_all(false)
   {
   }
diff --git a/sql/sql_load.cc b/sql/sql_load.cc
index 49de7f73cab..2f1ee0b11bd 100644
--- a/sql/sql_load.cc
+++ b/sql/sql_load.cc
@@ -44,6 +44,8 @@
 
 #include "wsrep_mysqld.h"
 
+#include "scope.h"  // scope_exit
+
 extern "C" int _my_b_net_read(IO_CACHE *info, uchar *Buffer, size_t Count);
 
 class XML_TAG {
@@ -444,6 +446,12 @@ int mysql_load(THD *thd, const sql_exchange *ex, TABLE_LIST *table_list,
   if (check_duplic_insert_without_overlaps(thd, table, handle_duplicates) != 0)
     DBUG_RETURN(true);
 
+  auto scope_cleaner = make_scope_exit(
+    [&fields_vars]() {
+      fields_vars.empty();
+    }
+  );
+
   if (!fields_vars.elements)
   {
     Field_iterator_table_ref field_iterator;
@@ -471,6 +479,7 @@ int mysql_load(THD *thd, const sql_exchange *ex, TABLE_LIST *table_list,
   }
   else
   {						// Part field list
+    scope_cleaner.release();
     /* TODO: use this conds for 'WITH CHECK OPTIONS' */
     if (setup_fields(thd, Ref_ptr_array(),
                      fields_vars, MARK_COLUMNS_WRITE, 0, NULL, 0) ||
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index c99d3fb3e75..9fc64f891d6 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -3432,7 +3432,7 @@ bool run_set_statement_if_requested(THD *thd, LEX *lex)
 */
 
 int
-mysql_execute_command(THD *thd)
+mysql_execute_command(THD *thd, bool is_called_from_prepared_stmt)
 {
   int res= 0;
   int  up_result= 0;
@@ -5259,7 +5259,7 @@ mysql_execute_command(THD *thd)
 
   } while (0);
   /* Don't do it, if we are inside a SP */
-  if (!thd->spcont)
+  if (!thd->spcont && !is_called_from_prepared_stmt)
   {
     sp_head::destroy(lex->sphead);
     lex->sphead= NULL;
diff --git a/sql/sql_parse.h b/sql/sql_parse.h
index eddaa9d87e1..ebe3fe97114 100644
--- a/sql/sql_parse.h
+++ b/sql/sql_parse.h
@@ -100,7 +100,7 @@ bool multi_delete_set_locks_and_link_aux_tables(LEX *lex);
 void create_table_set_open_action_and_adjust_tables(LEX *lex);
 int bootstrap(MYSQL_FILE *file);
 bool run_set_statement_if_requested(THD *thd, LEX *lex);
-int mysql_execute_command(THD *thd);
+int mysql_execute_command(THD *thd, bool is_called_from_prepared_stmt=false);
 enum dispatch_command_return
 {
   DISPATCH_COMMAND_SUCCESS=0,
diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc
index 2b6cbce98e1..1510844a7e7 100644
--- a/sql/sql_prepare.cc
+++ b/sql/sql_prepare.cc
@@ -89,6 +89,7 @@ When one supplies long data for a placeholder:
 #include "unireg.h"
 #include "sql_class.h"                          // set_var.h: THD
 #include "set_var.h"
+#include "sql_admin.h" // fill_check_table_metadata_fields
 #include "sql_prepare.h"
 #include "sql_parse.h" // insert_precheck, update_precheck, delete_precheck
 #include "sql_base.h"  // open_normal_and_derived_tables
@@ -105,6 +106,8 @@ When one supplies long data for a placeholder:
 #include "sql_cursor.h"
 #include "sql_show.h"
 #include "sql_repl.h"
+#include "sql_help.h"    // mysqld_help_prepare
+#include "sql_table.h"   // fill_checksum_table_metadata_fields
 #include "slave.h"
 #include "sp_head.h"
 #include "sp.h"
@@ -129,6 +132,7 @@ static const uint PARAMETER_FLAG_UNSIGNED= 128U << 8;
 #include "wsrep_mysqld.h"
 #include "wsrep_trans_observer.h"
 #endif /* WITH_WSREP */
+#include "xa.h"           // xa_recover_get_fields
 
 /**
   A result class used to send cursor rows using the binary protocol.
@@ -179,6 +183,7 @@ public:
   my_bool iterations;
   my_bool start_param;
   my_bool read_types;
+
 #ifndef EMBEDDED_LIBRARY
   bool (*set_params)(Prepared_statement *st, uchar *data, uchar *data_end,
                      uchar *read_pos, String *expanded_query);
@@ -1255,11 +1260,17 @@ insert_params_from_actual_params_with_log(Prepared_statement *stmt,
   DBUG_RETURN(0);
 }
 
-/**
+
+/*
   Validate INSERT statement.
 
   @param stmt               prepared statement
-  @param tables             global/local table list
+  @param table_list         global/local table list
+  @param fields             list of the table's fields to insert values
+  @param values_list        values to be inserted into the table
+  @param update_fields      the update fields.
+  @param update_values      the update values.
+  @param duplic             a way to handle duplicates
 
   @retval
     FALSE             success
@@ -1267,29 +1278,18 @@ insert_params_from_actual_params_with_log(Prepared_statement *stmt,
     TRUE              error, error message is set in THD
 */
 
-static bool mysql_test_insert(Prepared_statement *stmt,
-                              TABLE_LIST *table_list,
-                              List<Item> &fields,
-                              List<List_item> &values_list,
-                              List<Item> &update_fields,
-                              List<Item> &update_values,
-                              enum_duplicates duplic)
+static bool mysql_test_insert_common(Prepared_statement *stmt,
+                                     TABLE_LIST *table_list,
+                                     List<Item> &fields,
+                                     List<List_item> &values_list,
+                                     List<Item> &update_fields,
+                                     List<Item> &update_values,
+                                     enum_duplicates duplic)
 {
   THD *thd= stmt->thd;
   List_iterator_fast<List_item> its(values_list);
   List_item *values;
-  DBUG_ENTER("mysql_test_insert");
-
-  /*
-    Since INSERT DELAYED doesn't support temporary tables, we could
-    not pre-open temporary tables for SQLCOM_INSERT / SQLCOM_REPLACE.
-    Open them here instead.
-  */
-  if (table_list->lock_type != TL_WRITE_DELAYED)
-  {
-    if (thd->open_temporary_tables(table_list))
-      goto error;
-  }
+  DBUG_ENTER("mysql_test_insert_common");
 
   if (insert_precheck(thd, table_list))
     goto error;
@@ -1357,6 +1357,44 @@ error:
 
 
 /**
+  Open temporary tables if required and validate INSERT statement.
+
+  @param stmt               prepared statement
+  @param tables             global/local table list
+
+  @retval
+    FALSE             success
+  @retval
+    TRUE              error, error message is set in THD
+*/
+
+static bool mysql_test_insert(Prepared_statement *stmt,
+                              TABLE_LIST *table_list,
+                              List<Item> &fields,
+                              List<List_item> &values_list,
+                              List<Item> &update_fields,
+                              List<Item> &update_values,
+                              enum_duplicates duplic)
+{
+  THD *thd= stmt->thd;
+
+  /*
+    Since INSERT DELAYED doesn't support temporary tables, we could
+    not pre-open temporary tables for SQLCOM_INSERT / SQLCOM_REPLACE.
+    Open them here instead.
+  */
+  if (table_list->lock_type != TL_WRITE_DELAYED)
+  {
+    if (thd->open_temporary_tables(table_list))
+      return true;
+  }
+
+  return mysql_test_insert_common(stmt, table_list, fields, values_list,
+                                  update_fields, update_values, duplic);
+}
+
+
+/**
   Validate UPDATE statement.
 
   @param stmt               prepared statement
@@ -2274,6 +2312,83 @@ static int mysql_test_handler_read(Prepared_statement *stmt,
 
 
 /**
+  Send metadata to a client on PREPARE phase of XA RECOVER statement
+  processing
+
+  @param stmt  prepared statement
+
+  @return 0 on success, 1 on failure, 2 in case metadata was already sent
+*/
+
+static int mysql_test_xa_recover(Prepared_statement *stmt)
+{
+  THD *thd= stmt->thd;
+  List<Item> field_list;
+
+  xa_recover_get_fields(thd, &field_list, nullptr);
+  return send_stmt_metadata(thd, stmt, &field_list);
+}
+
+
+/**
+  Send metadata to a client on PREPARE phase of HELP statement processing
+
+  @param stmt  prepared statement
+
+  @return 0 on success, 1 on failure, 2 in case metadata was already sent
+*/
+
+static int mysql_test_help(Prepared_statement *stmt)
+{
+  THD *thd= stmt->thd;
+  List<Item> fields;
+
+  if (mysqld_help_prepare(thd, stmt->lex->help_arg, &fields))
+    return 1;
+
+  return send_stmt_metadata(thd, stmt, &fields);
+}
+
+
+/**
+  Send metadata to a client on PREPARE phase of admin related statements
+  processing
+
+  @param stmt  prepared statement
+
+  @return 0 on success, 1 on failure, 2 in case metadata was already sent
+*/
+
+static int mysql_test_admin_table(Prepared_statement *stmt)
+{
+  THD *thd= stmt->thd;
+  List<Item> fields;
+
+  fill_check_table_metadata_fields(thd, &fields);
+  return send_stmt_metadata(thd, stmt, &fields);
+}
+
+
+/**
+  Send metadata to a client on PREPARE phase of CHECKSUM TABLE statement
+  processing
+
+  @param stmt  prepared statement
+
+  @return 0 on success, 1 on failure, 2 in case metadata was already sent
+*/
+
+static int mysql_test_checksum_table(Prepared_statement *stmt)
+{
+  THD *thd= stmt->thd;
+  List<Item> fields;
+
+  fill_checksum_table_metadata_fields(thd, &fields);
+  return send_stmt_metadata(thd, stmt, &fields);
+}
+
+
+/**
   Perform semantic analysis of the parsed tree and send a response packet
   to the client.
 
@@ -2347,6 +2462,13 @@ static bool check_prepared_statement(Prepared_statement *stmt)
                            lex->duplicates);
     break;
 
+  case SQLCOM_LOAD:
+    res= mysql_test_insert_common(stmt, tables, lex->field_list,
+                                  lex->many_values,
+                                  lex->update_list, lex->value_list,
+                                  lex->duplicates);
+    break;
+
   case SQLCOM_UPDATE:
     res= mysql_test_update(stmt, tables);
     /* mysql_test_update returns 2 if we need to switch to multi-update */
@@ -2483,11 +2605,6 @@ static bool check_prepared_statement(Prepared_statement *stmt)
     }
     break;
   case SQLCOM_CREATE_VIEW:
-    if (lex->create_view->mode == VIEW_ALTER)
-    {
-      my_message(ER_UNSUPPORTED_PS, ER_THD(thd, ER_UNSUPPORTED_PS), MYF(0));
-      goto error;
-    }
     res= mysql_test_create_view(stmt);
     break;
   case SQLCOM_DO:
@@ -2515,6 +2632,38 @@ static bool check_prepared_statement(Prepared_statement *stmt)
     /* Statement and field info has already been sent */
     DBUG_RETURN(res == 1 ? TRUE : FALSE);
 
+  case SQLCOM_XA_RECOVER:
+    res= mysql_test_xa_recover(stmt);
+    if (res == 2)
+      /* Statement and field info has already been sent */
+      DBUG_RETURN(false);
+    break;
+
+  case SQLCOM_HELP:
+    res= mysql_test_help(stmt);
+    if (res == 2)
+      /* Statement and field info has already been sent */
+      DBUG_RETURN(false);
+    break;
+
+  case SQLCOM_ANALYZE:
+  case SQLCOM_ASSIGN_TO_KEYCACHE:
+  case SQLCOM_CHECK:
+  case SQLCOM_OPTIMIZE:
+  case SQLCOM_PRELOAD_KEYS:
+  case SQLCOM_REPAIR:
+    res= mysql_test_admin_table(stmt);
+    if (res == 2)
+      /* Statement and field info has already been sent */
+      DBUG_RETURN(false);
+    break;
+
+  case SQLCOM_CHECKSUM:
+    res= mysql_test_checksum_table(stmt);
+    if (res == 2)
+      /* Statement and field info has already been sent */
+      DBUG_RETURN(false);
+    break;
     /*
       Note that we don't need to have cases in this list if they are
       marked with CF_STATUS_COMMAND in sql_command_flags
@@ -2532,9 +2681,6 @@ static bool check_prepared_statement(Prepared_statement *stmt)
   case SQLCOM_ROLLBACK_TO_SAVEPOINT:
   case SQLCOM_TRUNCATE:
   case SQLCOM_DROP_VIEW:
-  case SQLCOM_REPAIR:
-  case SQLCOM_ANALYZE:
-  case SQLCOM_OPTIMIZE:
   case SQLCOM_CHANGE_MASTER:
   case SQLCOM_RESET:
   case SQLCOM_FLUSH:
@@ -2547,15 +2693,12 @@ static bool check_prepared_statement(Prepared_statement *stmt)
   case SQLCOM_CREATE_DB:
   case SQLCOM_DROP_DB:
   case SQLCOM_ALTER_DB_UPGRADE:
-  case SQLCOM_CHECKSUM:
   case SQLCOM_CREATE_USER:
   case SQLCOM_ALTER_USER:
   case SQLCOM_RENAME_USER:
   case SQLCOM_DROP_USER:
   case SQLCOM_CREATE_ROLE:
   case SQLCOM_DROP_ROLE:
-  case SQLCOM_ASSIGN_TO_KEYCACHE:
-  case SQLCOM_PRELOAD_KEYS:
   case SQLCOM_GRANT:
   case SQLCOM_GRANT_ROLE:
   case SQLCOM_REVOKE:
@@ -2564,22 +2707,10 @@ static bool check_prepared_statement(Prepared_statement *stmt)
   case SQLCOM_KILL:
   case SQLCOM_COMPOUND:
   case SQLCOM_SHUTDOWN:
-    break;
-
   case SQLCOM_PREPARE:
   case SQLCOM_EXECUTE:
   case SQLCOM_DEALLOCATE_PREPARE:
   default:
-    /*
-      Trivial check of all status commands. This is easier than having
-      things in the above case list, as it's less chance for mistakes.
-    */
-    if (!(sql_command_flags[sql_command] & CF_STATUS_COMMAND))
-    {
-      /* All other statements are not supported yet. */
-      my_message(ER_UNSUPPORTED_PS, ER_THD(thd, ER_UNSUPPORTED_PS), MYF(0));
-      goto error;
-    }
     break;
   }
   if (res == 0)
@@ -3484,7 +3615,7 @@ static void mysql_stmt_execute_common(THD *thd,
   SQLCOM_EXECUTE implementation.
 
     Execute prepared statement using parameter values from
-    lex->prepared_stmt_params and send result to the client using
+    lex->prepared_stmt.params() and send result to the client using
     text protocol. This is called from mysql_execute_command and
     therefore should behave like an ordinary query (e.g. not change
     global THD data, such as warning count, server status, etc).
@@ -5042,7 +5173,7 @@ bool Prepared_statement::execute(String *expanded_query, bool open_cursor)
       MYSQL_QUERY_EXEC_START(thd->query(), thd->thread_id, thd->get_db(),
                              &thd->security_ctx->priv_user[0],
                              (char *) thd->security_ctx->host_or_ip, 1);
-      error= mysql_execute_command(thd);
+      error= mysql_execute_command(thd, true);
       MYSQL_QUERY_EXEC_DONE(error);
     }
     else
@@ -6147,5 +6278,3 @@ extern "C" int execute_sql_command(const char *command,
 }
 
 #endif /*!EMBEDDED_LIBRARY*/
-
-
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index 16ac5e06809..66baff2efc1 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -11267,12 +11267,35 @@ bool mysql_recreate_table(THD *thd, TABLE_LIST *table_list, bool table_copy)
 }
 
 
+/**
+  Collect field names of result set that will be sent to a client in result of
+  handling the CHECKSUM TABLE statement.
+
+  @param      thd     Thread data object
+  @param[out] fields  List of fields whose metadata should be collected for
+                      sending to client
+ */
+
+void fill_checksum_table_metadata_fields(THD *thd, List<Item> *fields)
+{
+  Item *item;
+
+  item= new (thd->mem_root) Item_empty_string(thd, "Table", NAME_LEN*2);
+  item->set_maybe_null();
+  fields->push_back(item, thd->mem_root);
+
+  item= new (thd->mem_root) Item_int(thd, "Checksum", (longlong) 1,
+                                     MY_INT64_NUM_DECIMAL_DIGITS);
+  item->set_maybe_null();
+  fields->push_back(item, thd->mem_root);
+}
+
+
 bool mysql_checksum_table(THD *thd, TABLE_LIST *tables,
                           HA_CHECK_OPT *check_opt)
 {
   TABLE_LIST *table;
   List<Item> field_list;
-  Item *item;
   Protocol *protocol= thd->protocol;
   DBUG_ENTER("mysql_checksum_table");
 
@@ -11282,15 +11305,8 @@ bool mysql_checksum_table(THD *thd, TABLE_LIST *tables,
   */
   DBUG_ASSERT(! thd->in_sub_stmt);
 
-  field_list.push_back(item= new (thd->mem_root)
-                       Item_empty_string(thd, "Table", NAME_LEN*2),
-                       thd->mem_root);
-  item->set_maybe_null();
-  field_list.push_back(item= new (thd->mem_root)
-                       Item_int(thd, "Checksum", (longlong) 1,
-                                MY_INT64_NUM_DECIMAL_DIGITS),
-                       thd->mem_root);
-  item->set_maybe_null();
+  fill_checksum_table_metadata_fields(thd, &field_list);
+
   if (protocol->send_result_set_metadata(&field_list,
                             Protocol::SEND_NUM_ROWS | Protocol::SEND_EOF))
     DBUG_RETURN(TRUE);
diff --git a/sql/sql_table.h b/sql/sql_table.h
index 81894999aac..aacb6c99f15 100644
--- a/sql/sql_table.h
+++ b/sql/sql_table.h
@@ -176,6 +176,8 @@ bool mysql_rename_table(handlerton *base, const LEX_CSTRING *old_db,
 bool mysql_backup_table(THD* thd, TABLE_LIST* table_list);
 bool mysql_restore_table(THD* thd, TABLE_LIST* table_list);
 
+template<typename T> class List;
+void fill_checksum_table_metadata_fields(THD *thd, List<Item> *fields);
 bool mysql_checksum_table(THD* thd, TABLE_LIST* table_list,
                           HA_CHECK_OPT* check_opt);
 bool mysql_rm_table(THD *thd,TABLE_LIST *tables, bool if_exists,
@@ -204,7 +206,6 @@ int write_bin_log(THD *thd, bool clear_error,
 int write_bin_log_with_if_exists(THD *thd, bool clear_error,
                                  bool is_trans, bool add_if_exists);
 
-template<typename T> class List;
 void promote_first_timestamp_column(List<Create_field> *column_definitions);
 
 /*
diff --git a/sql/xa.cc b/sql/xa.cc
index e0defcb92ed..af7c7388c57 100644
--- a/sql/xa.cc
+++ b/sql/xa.cc
@@ -1001,23 +1001,29 @@ static my_bool xa_recover_callback_verbose(XID_cache_element *xs,
 }
 
 
-bool mysql_xa_recover(THD *thd)
+/**
+  Collect field names of result set that will be sent to a client in result of
+  handling XA RECOVER statement.
+
+  @param      thd     Thread data object
+  @param[out] fields  List of fields whose metadata should be collected for
+                      sending to client
+*/
+
+void xa_recover_get_fields(THD *thd, List<Item> *field_list,
+                           my_hash_walk_action *action)
 {
-  List<Item> field_list;
-  Protocol *protocol= thd->protocol;
   MEM_ROOT *mem_root= thd->mem_root;
-  my_hash_walk_action action;
-  DBUG_ENTER("mysql_xa_recover");
 
-  field_list.push_back(new (mem_root)
-                       Item_int(thd, "formatID", 0,
-                                MY_INT32_NUM_DECIMAL_DIGITS), mem_root);
-  field_list.push_back(new (mem_root)
-                       Item_int(thd, "gtrid_length", 0,
-                                MY_INT32_NUM_DECIMAL_DIGITS), mem_root);
-  field_list.push_back(new (mem_root)
-                       Item_int(thd, "bqual_length", 0,
-                                MY_INT32_NUM_DECIMAL_DIGITS), mem_root);
+  field_list->push_back(new (mem_root)
+      Item_int(thd, "formatID", 0,
+	  MY_INT32_NUM_DECIMAL_DIGITS), mem_root);
+  field_list->push_back(new (mem_root)
+      Item_int(thd, "gtrid_length", 0,
+	  MY_INT32_NUM_DECIMAL_DIGITS), mem_root);
+  field_list->push_back(new (mem_root)
+      Item_int(thd, "bqual_length", 0,
+	  MY_INT32_NUM_DECIMAL_DIGITS), mem_root);
   {
     uint len;
     CHARSET_INFO *cs;
@@ -1026,18 +1032,30 @@ bool mysql_xa_recover(THD *thd)
     {
       len= SQL_XIDSIZE;
       cs= &my_charset_utf8mb3_general_ci;
-      action= (my_hash_walk_action) xa_recover_callback_verbose;
+      if (action)
+	*action= (my_hash_walk_action) xa_recover_callback_verbose;
     }
     else
     {
       len= XIDDATASIZE;
       cs= &my_charset_bin;
-      action= (my_hash_walk_action) xa_recover_callback_short;
+      if (action)
+	*action= (my_hash_walk_action) xa_recover_callback_short;
     }
 
-    field_list.push_back(new (mem_root)
-                         Item_empty_string(thd, "data", len, cs), mem_root);
+    field_list->push_back(new (mem_root)
+	Item_empty_string(thd, "data", len, cs), mem_root);
   }
+}
+
+bool mysql_xa_recover(THD *thd)
+{
+  List<Item> field_list;
+  Protocol *protocol= thd->protocol;
+  my_hash_walk_action action;
+  DBUG_ENTER("mysql_xa_recover");
+
+  xa_recover_get_fields(thd, &field_list, &action);
 
   if (protocol->send_result_set_metadata(&field_list,
                             Protocol::SEND_NUM_ROWS | Protocol::SEND_EOF))
diff --git a/sql/xa.h b/sql/xa.h
index 0b2d0696642..a9b06e427c6 100644
--- a/sql/xa.h
+++ b/sql/xa.h
@@ -53,4 +53,7 @@ bool trans_xa_rollback(THD *thd);
 bool trans_xa_detach(THD *thd);
 bool mysql_xa_recover(THD *thd);
 
+void xa_recover_get_fields(THD *thd, List<Item> *field_list,
+                           my_hash_walk_action *action);
+
 #endif /* XA_INCLUDED */