Merged 5.1 with maria 5.1

15 files changed, 2756 insertions, 281 deletions

diff --git a/mysql-test/suite/funcs_1/datadict/charset_collation.inc b/mysql-test/suite/funcs_1/datadict/charset_collation.inc
index f8fc58b082b..ae03996b073 100644
--- a/mysql-test/suite/funcs_1/datadict/charset_collation.inc
+++ b/mysql-test/suite/funcs_1/datadict/charset_collation.inc
@@ -8,7 +8,7 @@
 #
 # The amount and properties of character_sets/collations depend on the
 # build type
-# 2007-12 MySQL 5.0
+# 2007-12 MySQL 5.0, 2008-06 MySQL 5.1
 # ---------------------------------------------------------------------
 #
 # Variant 1 fits to
@@ -33,10 +33,22 @@
 # Variant 3 fits to
 #    version_comment       MySQL Community Server (GPL)
 #    version_comment       MySQL Cluster Server (Commercial)
+#    version_comment       MySQL Advanced Server (GPL)         5.1
+#    version_comment       MySQL Advanced Server (Commercial)  5.1
 #
 # Difference between variant 3 and 2 is within the collation properties
 # IS_COMPILED and SORTLEN.
 #
+# 2008-06 All time excluded variant is "vanilla".
+# How to build "vanilla":
+#    ./BUILD/autorun.sh
+#    ./configure
+#    ./make
+# Some properties of "vanilla"
+#    version_comment       Source distribution
+#    Compared to the variants 1 to 3 a lot of character sets are missing.
+#    Example: "ucs2_bin" is in variant 1 to 3 but not in "vanilla".
+#
 # Created:
 # 2007-12-18 mleich - remove the unstable character_set/collation subtests
 #                     from include/datadict-master.inc
diff --git a/mysql-test/suite/funcs_1/datadict/columns.inc b/mysql-test/suite/funcs_1/datadict/columns.inc
index 86f8afeff73..64318492b00 100644
--- a/mysql-test/suite/funcs_1/datadict/columns.inc
+++ b/mysql-test/suite/funcs_1/datadict/columns.inc
@@ -84,4 +84,3 @@ SELECT CHARACTER_OCTET_LENGTH / CHARACTER_MAXIMUM_LENGTH AS COL_CML,
 FROM information_schema.columns
 $my_where
 ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION;
-
diff --git a/mysql-test/suite/funcs_1/datadict/datadict_bug_12777.inc b/mysql-test/suite/funcs_1/datadict/datadict_bug_12777.inc
index 0bca30b1dc3..5ba969fe4cb 100644
--- a/mysql-test/suite/funcs_1/datadict/datadict_bug_12777.inc
+++ b/mysql-test/suite/funcs_1/datadict/datadict_bug_12777.inc
@@ -2,7 +2,7 @@
 #
 
 # columns in INFORMATION_SCHEMA with VARCHAR(4096) on Linux and Intel or AMD
-# processor are shown as VARCHAR(512) on Windows, VARCHAR(1023) on AIX and HPUX, 
+# processor are shown as VARCHAR(512) on Windows, VARCHAR(1023) on AIX and HPUX,
 # VARCHAR(1024) on Solaris10, ... see below and in bug #12777 for details.
 # So we need to replace the output for these systems. There may be other still
 # not tested / detected systems.
@@ -10,10 +10,10 @@
 # Setting the variables used below has been moved to the beginning of the datadict
 # tests to "suite/funcs_1/datadict/datadict_load.inc".
 #
-# SELECT character_maximum_length INTO @CML 
-#   FROM information_schema.columns 
-#  WHERE table_schema = 'information_schema' 
-#    AND table_name   = 'columns' 
+# SELECT character_maximum_length INTO @CML
+#   FROM information_schema.columns
+#  WHERE table_schema = 'information_schema'
+#    AND table_name   = 'columns'
 #    AND column_name  = 'table_catalog';
 
 # this enables the --replace_result only if needed, using this we never replace
@@ -31,7 +31,7 @@ if ($bug_12777_0512)
    --replace_result 512 4096  1536 12288
 }
 
-# aix52, aix52-64bit, hp3750, hp3750-64bit, hpux11, hpux11-64bit, 
+# aix52, aix52-64bit, hp3750, hp3750-64bit, hpux11, hpux11-64bit,
 if ($bug_12777_1023)
 {
    #                nnnn       3*n
diff --git a/mysql-test/suite/funcs_1/datadict/datadict_load.inc b/mysql-test/suite/funcs_1/datadict/datadict_load.inc
index ab1211e9c9f..e3013249faf 100644
--- a/mysql-test/suite/funcs_1/datadict/datadict_load.inc
+++ b/mysql-test/suite/funcs_1/datadict/datadict_load.inc
@@ -12,14 +12,14 @@
 --disable_query_log
 
 # ------------------------------------------------------------------------------
-# Get the size of ONE known colum and check the size against some values to 
-# be able to use the correct --replace_result statement. Using this only the 
-# one pair of 'wrong' values is replaced and not all occurrencies of all 
+# Get the size of ONE known colum and check the size against some values to
+# be able to use the correct --replace_result statement. Using this only the
+# one pair of 'wrong' values is replaced and not all occurrencies of all
 # possible pairs of values. See bug #12777 for details.
-SELECT character_maximum_length INTO @CML 
-  FROM information_schema.columns 
- WHERE table_schema = 'information_schema' 
-   AND table_name   = 'columns' 
+SELECT character_maximum_length INTO @CML
+  FROM information_schema.columns
+ WHERE table_schema = 'information_schema'
+   AND table_name   = 'columns'
    AND column_name  = 'table_catalog';
 
 let $bug_12777_0512= `SELECT @CML =  512`;
@@ -53,7 +53,7 @@ let $SERVER_NAME= `SELECT DISTINCT host FROM mysql.user WHERE host NOT In ("loca
 #  load tables
 #  -----------
 #
-#  this was part of the 4 files $<engine>_datadict.test, but it has been moved 
+#  this was part of the 4 files $<engine>_datadict.test, but it has been moved
 #  here to have only one place where all preparation for the test is done.
 #
 ################################################################################
diff --git a/mysql-test/suite/funcs_1/datadict/is_key_column_usage.inc b/mysql-test/suite/funcs_1/datadict/is_key_column_usage.inc
new file mode 100644
index 00000000000..098b8c6eca2
--- /dev/null
+++ b/mysql-test/suite/funcs_1/datadict/is_key_column_usage.inc
@@ -0,0 +1,362 @@
+# suite/funcs_1/datadict/is_key_column_usage.inc
+#
+# Check the layout of information_schema.key_column_usage and the impact of
+# CREATE/ALTER/DROP TABLE/VIEW/SCHEMA/COLUMN ... on it.
+#
+# Note:
+#    This test is not intended
+#    - to show information about the all time existing tables
+#      within the databases information_schema and mysql
+#    - for checking storage engine properties
+#      Therefore please do not alter $engine_type.
+#
+# Author:
+# 2008-01-23 mleich WL#4203 Reorganize and fix the data dictionary tests of
+#                           testsuite funcs_1
+#                   Create this script based on older scripts and new code.
+# Last Change:
+# 2008-06-11 mleich Move t/is_key_column_usage.test to this file and
+#                   create variants for embedded/non embedded server.
+#
+
+let $engine_type = MEMORY;
+
+let $is_table = KEY_COLUMN_USAGE;
+
+# The table INFORMATION_SCHEMA.KEY_COLUMN_USAGE must exist
+eval SHOW TABLES FROM information_schema LIKE '$is_table';
+
+--echo #######################################################################
+--echo # Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT
+--echo #######################################################################
+# Ensure that every INFORMATION_SCHEMA table can be queried with a SELECT
+# statement, just as if it were an ordinary user-defined table.
+#
+--source suite/funcs_1/datadict/is_table_query.inc
+
+
+--echo #########################################################################
+--echo # Testcase 3.2.7.1: INFORMATION_SCHEMA.KEY_COLUMN_USAGE layout
+--echo #########################################################################
+# Ensure that the INFORMATION_SCHEMA.KEY_COLUMN_USAGE table has the following
+# columns, in the following order:
+#
+# CONSTRAINT_CATALOG (always shows NULL),
+# CONSTRAINT_SCHEMA (shows the database, or schema, in which an accessible
+#       constraint, or index, resides),
+# CONSTRAINT_NAME (shows the name of the accessible constraint),
+# TABLE_CATALOG (always shows NULL),
+# TABLE_SCHEMA (shows the database, or schema, in which the table constrained
+#       by that constraint resides),
+# TABLE_NAME (shows the name of the table constrained by the constraint),
+# COLUMN_NAME (shows the name of a column that is the index key, or part of
+#       the index key),
+# ORDINAL_POSITION (shows the ordinal position of the column within the
+#       constraint index),
+# POSITION_IN_UNIQUE_CONSTRAINT (shows, for a foreign key column, the ordinal
+#       position of the referenced column within the referenced unique index;
+#       otherwise NULL).
+# added with 5.0.6:
+# REFERENCED_TABLE_SCHEMA,
+# REFERENCED_TABLE_NAME,
+# REFERENCED_COLUMN_NAME
+#
+--source suite/funcs_1/datadict/datadict_bug_12777.inc
+eval DESCRIBE          information_schema.$is_table;
+--source suite/funcs_1/datadict/datadict_bug_12777.inc
+eval SHOW CREATE TABLE information_schema.$is_table;
+--source suite/funcs_1/datadict/datadict_bug_12777.inc
+eval SHOW COLUMNS FROM information_schema.$is_table;
+
+# Note: Retrieval of information within information_schema.columns about
+#       information_schema.key_column_usage is in is_columns_is.test.
+
+# Show that CONSTRAINT_CATALOG and TABLE_CATALOG are always NULL.
+SELECT constraint_catalog, constraint_schema, constraint_name, table_catalog,
+       table_schema, table_name, column_name
+FROM information_schema.key_column_usage
+WHERE constraint_catalog IS NOT NULL OR table_catalog IS NOT NULL;
+
+
+--echo ########################################################################################
+--echo # Testcase 3.2.7.2 + 3.2.7.3: INFORMATION_SCHEMA.KEY_COLUMN_USAGE accessible information
+--echo ########################################################################################
+# 3.2.7.2:  Ensure that the table shows the relevant information on every column, defined to
+#           be part of an index key, which is accessible to the current user or to PUBLIC.
+# 3.2.7.3:  Ensure that the table does not show any information on any indexed column that is
+#           not accessible to the current user or PUBLIC.
+#
+--disable_warnings
+DROP DATABASE IF EXISTS db_datadict;
+--enable_warnings
+CREATE DATABASE db_datadict;
+
+--error 0,ER_CANNOT_USER
+DROP   USER 'testuser1'@'localhost';
+CREATE USER 'testuser1'@'localhost';
+--error 0,ER_CANNOT_USER
+DROP   USER 'testuser2'@'localhost';
+CREATE USER 'testuser2'@'localhost';
+
+USE db_datadict;
+
+--replace_result $engine_type <engine_type>
+eval
+CREATE TABLE t1_1
+  (f1 INT NOT NULL, PRIMARY KEY(f1),
+   f2 INT,          INDEX f2_ind(f2))
+ENGINE = $engine_type;
+GRANT SELECT ON t1_1 to 'testuser1'@'localhost';
+
+--replace_result $engine_type <engine_type>
+eval
+CREATE TABLE t1_2
+  (f1 INT NOT NULL, PRIMARY KEY(f1),
+   f2 INT,          INDEX f2_ind(f2))
+ENGINE = $engine_type;
+GRANT SELECT ON t1_2 to 'testuser2'@'localhost';
+#FIXME: add foreign keys
+
+let $select= SELECT * FROM information_schema.key_column_usage
+WHERE table_name LIKE 't1_%'
+ORDER BY constraint_catalog, constraint_schema, constraint_name,
+         table_catalog, table_schema, table_name, ordinal_position;
+
+# show view of user root
+eval $select;
+
+--echo # Establish connection testuser1 (user=testuser1)
+--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
+connect (testuser1, localhost, testuser1, , db_datadict);
+eval $select;
+
+--echo # Establish connection testuser2 (user=testuser2)
+--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
+connect (testuser2, localhost, testuser2, , db_datadict);
+eval $select;
+
+# Cleanup
+--echo # Switch to connection default and close connections testuser1, testuser2
+connection default;
+disconnect testuser1;
+disconnect testuser2;
+DROP USER 'testuser1'@'localhost';
+DROP USER 'testuser2'@'localhost';
+DROP TABLE t1_1;
+DROP TABLE t1_2;
+DROP DATABASE IF EXISTS db_datadict;
+
+
+--echo ########################################################################################
+--echo # Testcase 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.KEY_COLUMN_USAGE modifications
+--echo ########################################################################################
+# 3.2.1.13: Ensure that the creation of any new database object (e.g. table or
+#           column) automatically inserts all relevant information on that
+#           object into every appropriate INFORMATION_SCHEMA table.
+# 3.2.1.14: Ensure that the alteration of any existing database object
+#           automatically updates all relevant information on that object in
+#           every appropriate INFORMATION_SCHEMA table.
+# 3.2.1.15: Ensure that the dropping of any existing database object
+#           automatically deletes all relevant information on that object from
+#           every appropriate INFORMATION_SCHEMA table.
+#
+--disable_warnings
+DROP DATABASE IF EXISTS db_datadict;
+DROP TABLE IF EXISTS test.t1_my_table;
+--enable_warnings
+CREATE DATABASE db_datadict;
+
+SELECT table_name FROM information_schema.key_column_usage
+WHERE table_name LIKE 't1_my_table%';
+--replace_result $engine_type <engine_type>
+eval
+CREATE TABLE test.t1_my_table
+   (f1 CHAR(12), f2 TIMESTAMP, f4 BIGINT, PRIMARY KEY(f1,f2))
+DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci
+ENGINE = $engine_type;
+# Settings used in CREATE TABLE must be visible
+# in information_schema.key_column_usage.
+--vertical_results
+SELECT * FROM information_schema.key_column_usage
+WHERE table_name = 't1_my_table';
+--horizontal_results
+#
+# Check modification of TABLE_NAME
+SELECT DISTINCT table_name FROM information_schema.key_column_usage
+WHERE table_name LIKE 't1_my_table%';
+RENAME TABLE test.t1_my_table TO test.t1_my_tablex;
+SELECT DISTINCT table_name FROM information_schema.key_column_usage
+WHERE table_name LIKE 't1_my_table%';
+#
+# Check modification of TABLE_SCHEMA
+SELECT DISTINCT table_schema,table_name FROM information_schema.key_column_usage
+WHERE table_name = 't1_my_tablex';
+RENAME TABLE test.t1_my_tablex TO db_datadict.t1_my_tablex;
+SELECT DISTINCT table_schema,table_name FROM information_schema.key_column_usage
+WHERE table_name = 't1_my_tablex';
+#
+# Check modification of COLUMN_NAME
+SELECT DISTINCT table_name, column_name FROM information_schema.key_column_usage
+WHERE table_name = 't1_my_tablex'
+ORDER BY table_name, column_name;
+ALTER TABLE db_datadict.t1_my_tablex CHANGE COLUMN f1 first_col CHAR(12);
+SELECT DISTINCT table_name, column_name FROM information_schema.key_column_usage
+WHERE table_name = 't1_my_tablex'
+ORDER BY table_name, column_name;
+#
+# Note: The size of the column list and the not very selective qualification
+#       is intended. I want to see that the schema names are equal and
+#       all records about 't1_my_tablex'.
+let $my_select = SELECT constraint_schema, constraint_name, table_schema,
+table_name, column_name, ordinal_position
+FROM information_schema.key_column_usage
+WHERE table_name = 't1_my_tablex'
+ORDER BY constraint_schema, constraint_name, table_schema,
+         table_name, ordinal_position;
+#
+# Check ADD INDEX being not UNIQUE (does not show up in key_column_usage)
+eval $my_select;
+CREATE INDEX f2 ON db_datadict.t1_my_tablex(f2);
+eval $my_select;
+DROP INDEX f2 ON db_datadict.t1_my_tablex;
+#
+# Check ADD UNIQUE INDEX without name explicit assigned
+eval $my_select;
+ALTER TABLE db_datadict.t1_my_tablex ADD UNIQUE (f2);
+eval $my_select;
+DROP INDEX f2 ON db_datadict.t1_my_tablex;
+#
+# Check ADD UNIQUE INDEX with name explicit assigned
+eval $my_select;
+ALTER TABLE db_datadict.t1_my_tablex ADD UNIQUE my_idx (f2);
+eval $my_select;
+DROP INDEX my_idx ON db_datadict.t1_my_tablex;
+eval $my_select;
+ALTER TABLE db_datadict.t1_my_tablex ADD UNIQUE my_idx (f4,first_col);
+eval $my_select;
+#
+# Check DROP COLUMN
+eval $my_select;
+ALTER TABLE db_datadict.t1_my_tablex
+DROP COLUMN first_col;
+eval $my_select;
+#
+# Check impact of DROP TABLE
+SELECT table_name, column_name
+FROM information_schema.key_column_usage
+WHERE table_name = 't1_my_tablex'
+ORDER BY table_name, column_name;
+DROP TABLE db_datadict.t1_my_tablex;
+SELECT table_name, column_name
+FROM information_schema.key_column_usage
+WHERE table_name = 't1_my_tablex';
+#
+# No UNIQUE CONSTRAINT -> no entry in key_column_usage
+SELECT table_name FROM information_schema.key_column_usage
+WHERE table_name = 't1_my_tablex';
+--replace_result $engine_type <engine_type>
+eval
+CREATE TABLE db_datadict.t1_my_tablex
+ENGINE = $engine_type AS
+SELECT 1 AS f1;
+SELECT table_name FROM information_schema.key_column_usage
+WHERE table_name = 't1_my_tablex';
+# UNIQUE CONSTRAINT -> entry in key_column_usage
+ALTER TABLE db_datadict.t1_my_tablex ADD PRIMARY KEY(f1);
+SELECT table_name FROM information_schema.key_column_usage
+WHERE table_name = 't1_my_tablex';
+#
+# Check impact of DROP SCHEMA
+SELECT table_name FROM information_schema.key_column_usage
+WHERE table_name = 't1_my_tablex';
+DROP DATABASE db_datadict;
+SELECT table_name FROM information_schema.key_column_usage
+WHERE table_name = 't1_my_tablex';
+#
+
+
+--echo ########################################################################
+--echo # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
+--echo #           DDL on INFORMATION_SCHEMA table are not supported
+--echo ########################################################################
+# 3.2.1.3:  Ensure that no user may execute an INSERT statement on any
+#           INFORMATION_SCHEMA table.
+# 3.2.1.4:  Ensure that no user may execute an UPDATE statement on any
+#           INFORMATION_SCHEMA table.
+# 3.2.1.5:  Ensure that no user may execute a DELETE statement on any
+#           INFORMATION_SCHEMA table.
+# 3.2.1.8:  Ensure that no user may create an index on an
+#           INFORMATION_SCHEMA table.
+# 3.2.1.9:  Ensure that no user may alter the definition of an
+#           INFORMATION_SCHEMA table.
+# 3.2.1.10: Ensure that no user may drop an INFORMATION_SCHEMA table.
+# 3.2.1.11: Ensure that no user may move an INFORMATION_SCHEMA table to any
+#           other database.
+# 3.2.1.12: Ensure that no user may directly add to, alter, or delete any data
+#           in an INFORMATION_SCHEMA table.
+#
+--disable_warnings
+DROP DATABASE IF EXISTS db_datadict;
+DROP TABLE IF EXISTS db_datadict.t1;
+--enable_warnings
+CREATE DATABASE db_datadict;
+--replace_result $engine_type <engine_type>
+eval
+CREATE TABLE db_datadict.t1 (f1 BIGINT)
+ENGINE = $engine_type;
+
+# Note(mleich):
+# 1. We can get here different error messages.
+# 2. We do not want to unify the individual messages to the far to unspecific
+#    'Got one of the listed errors'.
+let $my_error_message =
+##### The previous statement must fail ######
+# Server type  | expected error name      | expected error message
+# --------------------------------------------------------------------------------------------------------------------
+# not embedded | ER_DBACCESS_DENIED_ERROR | ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
+# embedded     | ER_NON_INSERTABLE_TABLE  | ERROR HY000: The target table schemata of the INSERT is not insertable-into
+#              | or similar               |              or similar
+;
+
+--disable_abort_on_error
+INSERT INTO information_schema.key_column_usage
+       (constraint_schema, constraint_name, table_name)
+VALUES (          'mysql',       'primary',       'db');
+if (!$mysql_errno)
+{
+   --echo $my_error_message
+   exit;
+}
+--error ER_DBACCESS_DENIED_ERROR
+INSERT INTO information_schema.key_column_usage
+SELECT * FROM information_schema.key_column_usage;
+
+--error ER_DBACCESS_DENIED_ERROR
+UPDATE information_schema.key_column_usage
+SET  table_name = 'db1' WHERE constraint_name = 'primary';
+
+--error ER_DBACCESS_DENIED_ERROR
+DELETE FROM information_schema.key_column_usage WHERE table_name = 't1';
+--error ER_DBACCESS_DENIED_ERROR
+TRUNCATE information_schema.key_column_usage;
+
+--error ER_DBACCESS_DENIED_ERROR
+CREATE INDEX i3 ON information_schema.key_column_usage(table_name);
+
+--error ER_DBACCESS_DENIED_ERROR
+ALTER TABLE information_schema.key_column_usage ADD f1 INT;
+
+--error ER_DBACCESS_DENIED_ERROR
+DROP TABLE information_schema.key_column_usage;
+
+--error ER_DBACCESS_DENIED_ERROR
+ALTER TABLE information_schema.key_column_usage
+RENAME db_datadict.key_column_usage;
+--error ER_DBACCESS_DENIED_ERROR
+ALTER TABLE information_schema.key_column_usage
+RENAME information_schema.xkey_column_usage;
+--enable_abort_on_error
+
+# Cleanup
+DROP TABLE db_datadict.t1;
+DROP DATABASE db_datadict;
diff --git a/mysql-test/suite/funcs_1/datadict/is_routines.inc b/mysql-test/suite/funcs_1/datadict/is_routines.inc
new file mode 100644
index 00000000000..f81e8ef7c3a
--- /dev/null
+++ b/mysql-test/suite/funcs_1/datadict/is_routines.inc
@@ -0,0 +1,523 @@
+# suite/funcs_1/datadict/is_routines.inc
+#
+# Check the layout of information_schema.routines and the impact of
+# CREATE/ALTER/DROP PROCEDURE/FUNCTION ... on it.
+#
+# Note:
+#    This test is not intended
+#    - to show information about the all time existing routines (there are no
+#      in the moment) within the databases information_schema and mysql
+#    - for checking storage engine properties
+#      Therefore please do not alter $engine_type and $other_engine_type.
+#
+# Author:
+# 2008-01-23 mleich WL#4203 Reorganize and fix the data dictionary tests of
+#                           testsuite funcs_1
+#                   Create this script based on older scripts and new code.
+# Last Change:
+# 2008-06-11 mleich Move t/is_routines.test to this file and
+#                   create variants for embedded/non embedded server.
+#
+
+let $engine_type       = MEMORY;
+let $other_engine_type = MyISAM;
+
+let $is_table = ROUTINES;
+
+# The table INFORMATION_SCHEMA.TABLES must exist
+eval SHOW TABLES FROM information_schema LIKE '$is_table';
+
+--echo #######################################################################
+--echo # Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT
+--echo #######################################################################
+# Ensure that every INFORMATION_SCHEMA table can be queried with a SELECT
+# statement, just as if it were an ordinary user-defined table.
+#
+--source suite/funcs_1/datadict/is_table_query.inc
+
+
+--echo #########################################################################
+--echo # Testcase 3.2.8.1: INFORMATION_SCHEMA.ROUTINES layout
+--echo #########################################################################
+# Ensure that the INFORMATION_SCHEMA.ROUTINES table has the following columns,
+# in the following order:
+#
+# SPECIFIC_NAME (shows the name of an accessible stored procedure, or routine),
+# ROUTINE_CATALOG (always shows NULL),
+# ROUTINE_SCHEMA (shows the database, or schema, in which the routine resides),
+# ROUTINE_NAME (shows the same stored procedure name),
+# ROUTINE_TYPE (shows whether the stored procedure is a procedure or a function),
+# DTD_IDENTIFIER (shows, for a function, the complete data type definition of
+#         the value the function will return; otherwise NULL),
+# ROUTINE_BODY (shows the language in which the stored procedure is written;
+#         currently always SQL),
+# ROUTINE_DEFINITION (shows as much of the routine body as is possible in the
+#         allotted space),
+# EXTERNAL_NAME (always shows NULL),
+# EXTERNAL_LANGUAGE (always shows NULL),
+# PARAMETER_STYLE (shows the routine's parameter style; always SQL),
+# IS_DETERMINISTIC (shows whether the routine is deterministic),
+# SQL_DATA_ACCESS (shows the routine's defined sql-data-access clause value),
+# SQL_PATH (always shows NULL),
+# SECURITY_TYPE (shows whether the routine's defined security_type is 'definer'
+#         or 'invoker'),
+# CREATED (shows the timestamp of the time the routine was created),
+# LAST_ALTERED (shows the timestamp of the time the routine was last altered),
+# SQL_MODE (shows the sql_mode setting at the time the routine was created),
+# ROUTINE_COMMENT (shows the comment, if any, defined for the routine;
+#         otherwise NULL),
+# DEFINER (shows the user who created the routine).
+# Starting with MySQL 5.1
+# CHARACTER_SET_CLIENT
+# COLLATION_CONNECTION
+# DATABASE_COLLATION
+#
+--source suite/funcs_1/datadict/datadict_bug_12777.inc
+eval DESCRIBE          information_schema.$is_table;
+--source suite/funcs_1/datadict/datadict_bug_12777.inc
+--replace_result ENGINE=MyISAM "" ENGINE=MARIA "" " PAGE_CHECKSUM=1" "" " PAGE_CHECKSUM=0" ""
+eval SHOW CREATE TABLE information_schema.$is_table;
+--source suite/funcs_1/datadict/datadict_bug_12777.inc
+eval SHOW COLUMNS FROM information_schema.$is_table;
+
+USE test;
+--disable_warnings
+DROP PROCEDURE IF EXISTS sp_for_routines;
+DROP FUNCTION  IF EXISTS function_for_routines;
+--enable_warnings
+CREATE PROCEDURE sp_for_routines()      SELECT 'db_datadict';
+CREATE FUNCTION function_for_routines() RETURNS INT RETURN 0;
+
+# Show that the column values of
+#   ROUTINE_CATALOG, EXTERNAL_NAME, EXTERNAL_LANGUAGE, SQL_PATH are always NULL
+# and
+#   ROUTINE_BODY, PARAMETER_STYLE are 'SQL'
+# and
+#   SPECIFIC_NAME = ROUTINE_NAME.
+SELECT specific_name,routine_catalog,routine_schema,routine_name,routine_type,
+       routine_body,external_name,external_language,parameter_style,sql_path
+FROM information_schema.routines
+WHERE routine_catalog   IS NOT NULL OR external_name   IS NOT NULL
+   OR external_language IS NOT NULL OR sql_path        IS NOT NULL
+   OR routine_body      <> 'SQL'    OR parameter_style <> 'SQL'
+   OR specific_name     <> routine_name;
+
+DROP PROCEDURE sp_for_routines;
+DROP FUNCTION  function_for_routines;
+
+
+--echo ################################################################################
+--echo # Testcase 3.2.8.2 + 3.2.8.3: INFORMATION_SCHEMA.ROUTINES accessible information
+--echo ################################################################################
+# 3.2.8.2:  Ensure that the table shows the relevant information on every SQL-invoked
+#           routine (i.e. stored procedure) which is accessible to the current user
+#           or to PUBLIC.
+# 3.2.8.3:  Ensure that the table does not show any information on any stored procedure
+#           that is not accessible to the current user or PUBLIC.
+#
+--disable_warnings
+DROP DATABASE IF EXISTS db_datadict;
+DROP DATABASE IF EXISTS db_datadict_2;
+--enable_warnings
+
+CREATE DATABASE db_datadict;
+USE db_datadict;
+--replace_result $other_engine_type <other_engine_type>
+eval
+CREATE TABLE res_6_408002_1(f1 CHAR(3), f2 TEXT(25), f3 DATE, f4 INT)
+ENGINE = $other_engine_type;
+INSERT INTO res_6_408002_1(f1, f2, f3, f4)
+VALUES('abc', 'xyz', '1989-11-09', 0815);
+--disable_warnings
+DROP PROCEDURE IF EXISTS sp_6_408002_1;
+--enable_warnings
+delimiter //;
+CREATE PROCEDURE sp_6_408002_1()
+BEGIN
+   SELECT * FROM db_datadict.res_6_408002_1;
+END//
+delimiter ;//
+
+CREATE DATABASE db_datadict_2;
+USE db_datadict_2;
+--replace_result $other_engine_type <other_engine_type>
+eval
+CREATE TABLE res_6_408002_2(f1 CHAR(3), f2 TEXT(25), f3 DATE, f4 INT)
+ENGINE = $other_engine_type;
+INSERT INTO res_6_408002_2(f1, f2, f3, f4)
+VALUES('abc', 'xyz', '1990-10-03', 4711);
+--disable_warnings
+DROP PROCEDURE IF EXISTS sp_6_408002_2;
+--enable_warnings
+delimiter //;
+CREATE PROCEDURE sp_6_408002_2()
+BEGIN
+   SELECT * FROM db_datadict_2.res_6_408002_2;
+END//
+delimiter ;//
+
+--error 0,ER_CANNOT_USER
+DROP   USER 'testuser1'@'localhost';
+CREATE USER 'testuser1'@'localhost';
+--error 0,ER_CANNOT_USER
+DROP   USER 'testuser2'@'localhost';
+CREATE USER 'testuser2'@'localhost';
+--error 0,ER_CANNOT_USER
+DROP   USER 'testuser3'@'localhost';
+CREATE USER 'testuser3'@'localhost';
+
+
+GRANT SELECT  ON db_datadict_2.* TO 'testuser1'@'localhost';
+GRANT EXECUTE ON db_datadict_2.* TO 'testuser1'@'localhost';
+
+GRANT EXECUTE ON db_datadict.*   TO 'testuser1'@'localhost';
+GRANT SELECT  ON db_datadict.*   TO 'testuser2'@'localhost';
+
+GRANT EXECUTE ON PROCEDURE db_datadict_2.sp_6_408002_2
+TO 'testuser2'@'localhost';
+GRANT EXECUTE ON db_datadict_2.* TO 'testuser2'@'localhost';
+FLUSH PRIVILEGES;
+
+--echo # Establish connection testuser1 (user=testuser1)
+--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
+connect (testuser1, localhost, testuser1, , db_datadict);
+--replace_column 16 "YYYY-MM-DD hh:mm:ss" 17 "YYYY-MM-DD hh:mm:ss"
+SELECT * FROM information_schema.routines;
+
+--echo # Establish connection testuser2 (user=testuser2)
+--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
+connect (testuser2, localhost, testuser2, , db_datadict);
+--replace_column 16 "YYYY-MM-DD hh:mm:ss" 17 "YYYY-MM-DD hh:mm:ss"
+SELECT * FROM information_schema.routines;
+
+--echo # Establish connection testuser3 (user=testuser3)
+--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
+connect (testuser3, localhost, testuser3, , test);
+--replace_column 16 "YYYY-MM-DD hh:mm:ss" 17 "YYYY-MM-DD hh:mm:ss"
+SELECT * FROM information_schema.routines;
+
+# Cleanup
+--echo # Switch to connection default and close connections testuser1,testuser2,testuser3
+connection default;
+disconnect testuser1;
+disconnect testuser2;
+disconnect testuser3;
+
+DROP USER 'testuser1'@'localhost';
+DROP USER 'testuser2'@'localhost';
+DROP USER 'testuser3'@'localhost';
+
+USE test;
+DROP DATABASE db_datadict;
+DROP DATABASE db_datadict_2;
+
+
+--echo #########################################################################
+--echo # 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.ROUTINES modifications
+--echo #########################################################################
+# 3.2.1.13: Ensure that the creation of any new database object (e.g. table or
+#           column) automatically inserts all relevant information on that
+#           object into every appropriate INFORMATION_SCHEMA table.
+# 3.2.1.14: Ensure that the alteration of any existing database object
+#           automatically updates all relevant information on that object in
+#           every appropriate INFORMATION_SCHEMA table.
+# 3.2.1.15: Ensure that the dropping of any existing database object
+#           automatically deletes all relevant information on that object from
+#           every appropriate INFORMATION_SCHEMA table.
+#
+# Some more tests are in t/information_schema_routines.test which exists
+# in MySQL 5.1 and up only.
+#
+--disable_warnings
+DROP DATABASE IF EXISTS db_datadict;
+--enable_warnings
+CREATE DATABASE db_datadict;
+
+SELECT * FROM information_schema.routines WHERE routine_schema = 'db_datadict';
+USE db_datadict;
+CREATE PROCEDURE sp_for_routines()      SELECT 'db_datadict';
+CREATE FUNCTION function_for_routines() RETURNS INT RETURN 0;
+--vertical_results
+--replace_column 16 <created> 17 <modified>
+SELECT * FROM information_schema.routines WHERE routine_schema = 'db_datadict'
+ORDER BY routine_name;
+--horizontal_results
+
+ALTER PROCEDURE sp_for_routines SQL SECURITY INVOKER;
+ALTER FUNCTION function_for_routines COMMENT 'updated comments';
+--vertical_results
+--replace_column 16 <created> 17 <modified>
+SELECT * FROM information_schema.routines WHERE routine_schema = 'db_datadict'
+ORDER BY routine_name;
+--horizontal_results
+
+DROP PROCEDURE sp_for_routines;
+DROP FUNCTION function_for_routines;
+SELECT * FROM information_schema.routines WHERE routine_schema = 'db_datadict';
+
+CREATE PROCEDURE sp_for_routines()      SELECT 'db_datadict';
+CREATE FUNCTION function_for_routines() RETURNS INT RETURN 0;
+--vertical_results
+--replace_column 16 <created> 17 <modified>
+SELECT * FROM information_schema.routines WHERE routine_schema = 'db_datadict'
+ORDER BY routine_name;
+--horizontal_results
+use test;
+DROP DATABASE db_datadict;
+SELECT * FROM information_schema.routines WHERE routine_schema = 'db_datadict';
+
+
+--echo #########################################################################
+--echo # 3.2.8.4: INFORMATION_SCHEMA.ROUTINES routine body too big for
+--echo #          ROUTINE_DEFINITION column
+--echo #########################################################################
+# Ensure that a stored procedure with a routine body that is too large to fit
+# into the INFORMATION_SCHEMA.ROUTINES.ROUTINE_DEFINITION column correctly shows
+# as much of the information as is possible within the allotted size.
+#
+--disable_warnings
+DROP DATABASE IF EXISTS db_datadict;
+--enable_warnings
+CREATE DATABASE db_datadict;
+USE db_datadict;
+#
+--replace_result $other_engine_type <other_engine_type>
+eval
+CREATE TABLE db_datadict.res_6_408004_1
+       (f1 LONGTEXT , f2 MEDIUMINT , f3 LONGBLOB , f4 REAL , f5 YEAR)
+ENGINE = $other_engine_type;
+INSERT INTO db_datadict.res_6_408004_1
+VALUES ('abc', 98765 , 99999999 , 98765, 10);
+#
+--replace_result $other_engine_type <other_engine_type>
+eval
+CREATE TABLE db_datadict.res_6_408004_2
+       (f1 LONGTEXT , f2 MEDIUMINT , f3 LONGBLOB , f4 REAL , f5 YEAR)
+ENGINE = $other_engine_type;
+INSERT INTO db_datadict.res_6_408004_2
+VALUES ('abc', 98765 , 99999999 , 98765, 10);
+
+--echo # Checking the max. possible length of (currently) 4 GByte is not
+--echo # in this environment here.
+
+delimiter //;
+CREATE PROCEDURE sp_6_408004 ()
+BEGIN
+   DECLARE done INTEGER DEFAULt 0;
+   DECLARE variable_number_1 LONGTEXT;
+   DECLARE variable_number_2 MEDIUMINT;
+   DECLARE variable_number_3 LONGBLOB;
+   DECLARE variable_number_4 REAL;
+   DECLARE variable_number_5 YEAR;
+   DECLARE cursor_number_1 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
+   DECLARE cursor_number_2 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
+   DECLARE cursor_number_3 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
+   DECLARE cursor_number_4 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
+   DECLARE cursor_number_5 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
+   DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
+   BEGIN
+      OPEN cursor_number_1;
+      WHILE done <> 1 DO
+         FETCH cursor_number_1
+         INTO variable_number_1, variable_number_2, variable_number_3,
+              variable_number_4, variable_number_5;
+         IF done <> 0 THEN
+            INSERT INTO res_6_408004_2
+            VALUES (variable_number_1, variable_number_2, variable_number_3,
+                    variable_number_4, variable_number_5);
+         END IF;
+      END WHILE;
+      BEGIN
+         BEGIN
+            SET done = 0;
+            OPEN cursor_number_2;
+            WHILE done <> 1 DO
+               FETCH cursor_number_2
+               INTO variable_number_1, variable_number_2, variable_number_3,
+                    variable_number_4, variable_number_5;
+               IF done <> 0 THEN
+                  INSERT INTO res_6_408004_2
+                  VALUES(variable_number_1, variable_number_2, variable_number_3,
+                         variable_number_4, variable_number_5);
+               END IF;
+            END WHILE;
+         END;
+         SET done = 0;
+         OPEN cursor_number_3;
+         WHILE done <> 1 DO
+            FETCH cursor_number_3
+            INTO variable_number_1, variable_number_2, variable_number_3,
+                 variable_number_4, variable_number_5;
+            IF done <> 0 THEN
+               INSERT INTO res_6_408004_2
+               VALUES(variable_number_1, variable_number_2, variable_number_3,
+                      variable_number_4, variable_number_5);
+            END IF;
+         END WHILE;
+      END;
+   END;
+   BEGIN
+      SET done = 0;
+      OPEN cursor_number_4;
+      WHILE done <> 1 DO
+         FETCH cursor_number_4
+         INTO variable_number_1, variable_number_2, variable_number_3,
+              variable_number_4, variable_number_5;
+         IF done <> 0 THEN
+            INSERT INTO res_6_408004_2
+            VALUES (variable_number_1, variable_number_2, variable_number_3,
+                    variable_number_4, variable_number_5);
+         END IF;
+      END WHILE;
+   END;
+   BEGIN
+      SET @a='test row';
+      SELECT @a;
+      SELECT @a;
+      SELECT @a;
+   END;
+   BEGIN
+      SET done = 0;
+      OPEN cursor_number_5;
+      WHILE done <> 1 DO
+         FETCH cursor_number_5
+         INTO variable_number_1, variable_number_2, variable_number_3,
+              variable_number_4, variable_number_5;
+         IF done <> 0 THEN
+            INSERT INTO res_6_408004_2
+            VALUES (variable_number_1, variable_number_2, variable_number_3,
+                    variable_number_4, variable_number_5);
+         END IF;
+      END WHILE;
+   END;
+   BEGIN
+      SET @a='test row';
+      SELECT @a;
+      SELECT @a;
+      SELECT @a;
+   END;
+END//
+delimiter ;//
+
+CALL db_datadict.sp_6_408004 ();
+SELECT * FROM db_datadict.res_6_408004_2;
+
+--vertical_results
+--replace_column 16 "YYYY-MM-DD hh:mm:ss" 17 "YYYY-MM-DD hh:mm:ss"
+SELECT *, LENGTH(routine_definition) FROM information_schema.routines
+WHERE routine_schema = 'db_datadict';
+--horizontal_results
+
+# Cleanup
+DROP DATABASE db_datadict;
+# ----------------------------------------------------------------------------------------------
+
+
+--echo ########################################################################
+--echo # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
+--echo #           DDL on INFORMATION_SCHEMA table are not supported
+--echo ########################################################################
+# 3.2.1.3:  Ensure that no user may execute an INSERT statement on any
+#           INFORMATION_SCHEMA table.
+# 3.2.1.4:  Ensure that no user may execute an UPDATE statement on any
+#           INFORMATION_SCHEMA table.
+# 3.2.1.5:  Ensure that no user may execute a DELETE statement on any
+#           INFORMATION_SCHEMA table.
+# 3.2.1.8:  Ensure that no user may create an index on an INFORMATION_SCHEMA table.
+# 3.2.1.9:  Ensure that no user may alter the definition of an
+#           INFORMATION_SCHEMA table.
+# 3.2.1.10: Ensure that no user may drop an INFORMATION_SCHEMA table.
+# 3.2.1.11: Ensure that no user may move an INFORMATION_SCHEMA table to any
+#           other database.
+# 3.2.1.12: Ensure that no user may directly add to, alter, or delete any data
+#           in an INFORMATION_SCHEMA table.
+#
+--disable_warnings
+DROP DATABASE IF EXISTS db_datadict;
+--enable_warnings
+CREATE DATABASE db_datadict;
+USE db_datadict;
+CREATE PROCEDURE sp_for_routines() SELECT 'db_datadict';
+USE test;
+
+# Note(mleich):
+# 1. We can get here different error messages.
+# 2. We do not want to unify the individual messages to the far to unspecific
+#    'Got one of the listed errors'.
+let $my_error_message =
+##### The previous statement must fail ######
+# Server type  | expected error name      | expected error message
+# --------------------------------------------------------------------------------------------------------------------
+# not embedded | ER_DBACCESS_DENIED_ERROR | ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
+# embedded     | ER_NON_INSERTABLE_TABLE  | ERROR HY000: The target table schemata of the INSERT is not insertable-into
+#              | or similar               |              or similar
+;
+
+--disable_abort_on_error
+INSERT INTO information_schema.routines (routine_name, routine_type )
+VALUES ('p2', 'procedure');
+if (!$mysql_errno)
+{
+   --echo $my_error_message
+   exit;
+}
+UPDATE information_schema.routines SET routine_name = 'p2'
+WHERE routine_body = 'sql';
+if (!$mysql_errno)
+{
+   --echo $my_error_message
+   exit;
+}
+DELETE FROM information_schema.routines ;
+if (!$mysql_errno)
+{
+   --echo $my_error_message
+   exit;
+}
+TRUNCATE information_schema.routines ;
+if (!$mysql_errno)
+{
+   --echo $my_error_message
+   exit;
+}
+CREATE INDEX i7 ON information_schema.routines (routine_name);
+if (!$mysql_errno)
+{
+   --echo $my_error_message
+   exit;
+}
+ALTER TABLE information_schema.routines  ADD f1 INT;
+if (!$mysql_errno)
+{
+   --echo $my_error_message
+   exit;
+}
+ALTER TABLE information_schema.routines  DISCARD TABLESPACE;
+if (!$mysql_errno)
+{
+   --echo $my_error_message
+   exit;
+}
+DROP TABLE information_schema.routines ;
+if (!$mysql_errno)
+{
+   --echo $my_error_message
+   exit;
+}
+ALTER TABLE information_schema.routines RENAME db_datadict.routines;
+if (!$mysql_errno)
+{
+   --echo $my_error_message
+   exit;
+}
+ALTER TABLE information_schema.routines RENAME information_schema.xroutines;
+if (!$mysql_errno)
+{
+   --echo $my_error_message
+   exit;
+}
+--enable_abort_on_error
+
+# Cleanup
+DROP DATABASE db_datadict;
+
diff --git a/mysql-test/suite/funcs_1/datadict/is_schemata.inc b/mysql-test/suite/funcs_1/datadict/is_schemata.inc
new file mode 100644
index 00000000000..29e1f6af4ef
--- /dev/null
+++ b/mysql-test/suite/funcs_1/datadict/is_schemata.inc
@@ -0,0 +1,300 @@
+# suite/funcs_1/datadict/is_schemata.inc
+#
+# Check the layout of information_schema.schemata, permissions and the impact of
+# CREATE/ALTER/DROP SCHEMA on it.
+#
+# Note:
+#    This test is not intended
+#    - to show information about the all time existing databases
+#      information_schema and mysql
+#    - for checking storage engine properties
+#
+# Author:
+# 2008-01-23 mleich WL#4203 Reorganize and fix the data dictionary tests of
+#                           testsuite funcs_1
+#                   Create this script based on older scripts and new code.
+# Last Change:
+# 2008-06-11 mleich Move t/is_schemata.test to this file and
+#                   create variants for embedded/non embedded server.
+#
+
+let $is_table = SCHEMATA;
+
+# The table INFORMATION_SCHEMA.SCHEMATA must exist
+eval SHOW TABLES FROM information_schema LIKE '$is_table';
+
+--echo #######################################################################
+--echo # Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT
+--echo #######################################################################
+# Ensure that every INFORMATION_SCHEMA table can be queried with a SELECT
+# statement, just as if it were an ordinary user-defined table.
+#
+--source suite/funcs_1/datadict/is_table_query.inc
+
+
+--echo #########################################################################
+--echo # Testcase 3.2.9.1: INFORMATION_SCHEMA.SCHEMATA layout;
+--echo #########################################################################
+# Ensure that the INFORMATION_SCHEMA.SCHEMATA table has the following columns,
+# in the following order:
+#
+# CATALOG_NAME (always shows NULL),
+# SCHEMA_NAME (shows the name of a database, or schema, on which the current
+#        user or PUBLIC has privileges),
+# DEFAULT_CHARACTER_SET_NAME (shows the name of that database's default
+#        character set),
+# DEFAULT_COLLATION_NAME (shows the database defaul collation)
+# SQL_PATH (always shows NULL).
+#
+--source suite/funcs_1/datadict/datadict_bug_12777.inc
+eval DESCRIBE          information_schema.$is_table;
+--source suite/funcs_1/datadict/datadict_bug_12777.inc
+eval SHOW CREATE TABLE information_schema.$is_table;
+--source suite/funcs_1/datadict/datadict_bug_12777.inc
+eval SHOW COLUMNS FROM information_schema.$is_table;
+
+# Note: Retrieval of information within information_schema.columns about
+#       information_schema.schemata is in is_columns_is.test.
+
+# Show that CATALOG_NAME and SQL_PATH are always NULL.
+SELECT catalog_name, schema_name, sql_path
+FROM information_schema.schemata
+WHERE catalog_name IS NOT NULL or sql_path IS NOT NULL;
+
+
+--echo ###############################################################################
+--echo # Testcases 3.2.9.2+3.2.9.3: INFORMATION_SCHEMA.SCHEMATA accessible information
+--echo ###############################################################################
+# 3.2.9.2 Ensure that the table shows the relevant information for every
+#         database on which the current user or PUBLIC have privileges.
+# 3.2.9.3 Ensure that the table does not show any information on any databases
+#         on which the current user and PUBLIC have no privileges.
+#
+# Note: Check of content within information_schema.schemata about the databases
+#       information_schema and mysql is in
+#       suite/funcs_1/t/is_schemata_is_mysql.test.
+#
+--disable_warnings
+DROP DATABASE IF EXISTS db_datadict_1;
+DROP DATABASE IF EXISTS db_datadict_2;
+--enable_warnings
+CREATE DATABASE db_datadict_1;
+CREATE DATABASE db_datadict_2;
+
+--error 0,ER_CANNOT_USER
+DROP   USER 'testuser1'@'localhost';
+CREATE USER 'testuser1'@'localhost';
+--error 0,ER_CANNOT_USER
+DROP   USER 'testuser2'@'localhost';
+CREATE USER 'testuser2'@'localhost';
+--error 0,ER_CANNOT_USER
+DROP   USER 'testuser3'@'localhost';
+CREATE USER 'testuser3'@'localhost';
+
+GRANT SELECT ON db_datadict_1.* to 'testuser1'@'localhost';
+GRANT SELECT ON db_datadict_1.* to 'testuser2'@'localhost';
+GRANT SELECT ON db_datadict_2.* to 'testuser2'@'localhost';
+
+let $my_select = SELECT * FROM information_schema.schemata
+WHERE schema_name LIKE 'db_datadict_%' ORDER BY schema_name;
+let $my_show = SHOW DATABASES LIKE 'db_datadict_%';
+
+eval $my_select;
+--sorted_result
+eval $my_show;
+
+--echo # Establish connection testuser1 (user=testuser1)
+--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
+connect (testuser1, localhost, testuser1, , db_datadict_1);
+# Shows db_datadict_1
+eval $my_select;
+--sorted_result
+eval $my_show;
+
+--echo # Establish connection testuser2 (user=testuser2)
+--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
+connect (testuser2, localhost, testuser2, , db_datadict_2);
+# Shows db_datadict_1 and db_datadict_2
+eval $my_select;
+--sorted_result
+eval $my_show;
+
+--echo # Establish connection testuser3 (user=testuser3)
+--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
+connect (testuser3, localhost, testuser3, , test);
+# Shows neither db_datadict_1 nor db_datadict_2
+eval $my_select;
+--sorted_result
+eval $my_show;
+
+# Cleanup
+--echo # Switch to connection default and close connections testuser1,testuser2,testuser3
+connection default;
+disconnect testuser1;
+disconnect testuser2;
+disconnect testuser3;
+DROP USER 'testuser1'@'localhost';
+DROP USER 'testuser2'@'localhost';
+DROP USER 'testuser3'@'localhost';
+DROP DATABASE db_datadict_1;
+DROP DATABASE db_datadict_2;
+
+
+--echo #################################################################################
+--echo # Testcases 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.SCHEMATA modifications
+--echo #################################################################################
+# 3.2.1.13: Ensure that the creation of any new database object (e.g. table or
+#           column) automatically inserts all relevant information on that
+#           object into every appropriate INFORMATION_SCHEMA table.
+# 3.2.1.14: Ensure that the alteration of any existing database object
+#           automatically updates all relevant information on that object in
+#           every appropriate INFORMATION_SCHEMA table.
+# 3.2.1.15: Ensure that the dropping of any existing database object
+#           automatically deletes all relevant information on that object from
+#           every appropriate INFORMATION_SCHEMA table.
+#
+--disable_warnings
+DROP DATABASE IF EXISTS db_datadict;
+--enable_warnings
+
+SELECT * FROM information_schema.schemata WHERE schema_name = 'db_datadict';
+CREATE DATABASE db_datadict CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci';
+SELECT * FROM information_schema.schemata WHERE schema_name = 'db_datadict';
+
+# Check modify default CHARACTER SET
+SELECT schema_name, default_character_set_name
+FROM information_schema.schemata WHERE schema_name = 'db_datadict';
+ALTER SCHEMA db_datadict CHARACTER SET 'utf8';
+SELECT schema_name, default_character_set_name
+FROM information_schema.schemata WHERE schema_name = 'db_datadict';
+ALTER SCHEMA db_datadict CHARACTER SET 'latin1';
+
+# Check modify default COLLATION
+SELECT schema_name, default_collation_name FROM information_schema.schemata
+WHERE schema_name = 'db_datadict';
+ALTER SCHEMA db_datadict COLLATE 'latin1_general_cs';
+SELECT schema_name, default_collation_name FROM information_schema.schemata
+WHERE schema_name = 'db_datadict';
+
+# Check DROP DATABASE
+SELECT schema_name
+FROM information_schema.schemata WHERE schema_name = 'db_datadict';
+DROP DATABASE db_datadict;
+SELECT schema_name
+FROM information_schema.schemata WHERE schema_name = 'db_datadict';
+
+
+--echo ########################################################################
+--echo # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
+--echo #           DDL on INFORMATION_SCHEMA tables are not supported
+--echo ########################################################################
+# 3.2.1.3:  Ensure that no user may execute an INSERT statement on any
+#           INFORMATION_SCHEMA table.
+# 3.2.1.4:  Ensure that no user may execute an UPDATE statement on any
+#           INFORMATION_SCHEMA table.
+# 3.2.1.5:  Ensure that no user may execute a DELETE statement on any
+#           INFORMATION_SCHEMA table.
+# 3.2.1.8:  Ensure that no user may create an index on an INFORMATION_SCHEMA table.
+# 3.2.1.9:  Ensure that no user may alter the definition of an
+#           INFORMATION_SCHEMA table.
+# 3.2.1.10: Ensure that no user may drop an INFORMATION_SCHEMA table.
+# 3.2.1.11: Ensure that no user may move an INFORMATION_SCHEMA table to any
+#           other database.
+# 3.2.1.12: Ensure that no user may directly add to, alter, or delete any data
+#           in an INFORMATION_SCHEMA table.
+#
+--disable_warnings
+DROP DATABASE IF EXISTS db_datadict;
+--enable_warnings
+CREATE DATABASE db_datadict CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci';
+
+# Note(mleich):
+# 1. We can get here different error messages.
+# 2. We do not want to unify the individual messages to the far to unspecific
+#    'Got one of the listed errors'.
+let $my_error_message =
+##### The previous statement must fail ######
+# Server type  | expected error name      | expected error message
+# --------------------------------------------------------------------------------------------------------------------
+# not embedded | ER_DBACCESS_DENIED_ERROR | ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
+# embedded     | ER_NON_INSERTABLE_TABLE  | ERROR HY000: The target table schemata of the INSERT is not insertable-into
+#              | or similar               |              or similar
+;
+
+--disable_abort_on_error
+INSERT INTO information_schema.schemata
+       (catalog_name, schema_name, default_character_set_name, sql_path)
+VALUES (NULL, 'db1', 'latin1', NULL);
+if (!$mysql_errno)
+{
+   --echo $my_error_message
+   exit;
+}
+INSERT INTO information_schema.schemata
+SELECT * FROM information_schema.schemata;
+if (!$mysql_errno)
+{
+   --echo $my_error_message
+   exit;
+}
+UPDATE information_schema.schemata
+SET default_character_set_name = 'utf8'
+WHERE schema_name = 'db_datadict';
+if (!$mysql_errno)
+{
+   --echo $my_error_message
+   exit;
+}
+UPDATE information_schema.schemata SET catalog_name = 't_4711';
+if (!$mysql_errno)
+{
+   --echo $my_error_message
+   exit;
+}
+DELETE FROM information_schema.schemata WHERE schema_name = 'db_datadict';
+if (!$mysql_errno)
+{
+   --echo $my_error_message
+   exit;
+}
+TRUNCATE information_schema.schemata;
+if (!$mysql_errno)
+{
+   --echo $my_error_message
+   exit;
+}
+CREATE INDEX i1 ON information_schema.schemata(schema_name);
+if (!$mysql_errno)
+{
+   --echo $my_error_message
+   exit;
+}
+ALTER TABLE information_schema.schemata ADD f1 INT;
+if (!$mysql_errno)
+{
+   --echo $my_error_message
+   exit;
+}
+DROP TABLE information_schema.schemata;
+if (!$mysql_errno)
+{
+   --echo $my_error_message
+   exit;
+}
+ALTER TABLE information_schema.schemata RENAME db_datadict.schemata;
+if (!$mysql_errno)
+{
+   --echo $my_error_message
+   exit;
+}
+ALTER TABLE information_schema.schemata RENAME information_schema.xschemata;
+if (!$mysql_errno)
+{
+   --echo $my_error_message
+   exit;
+}
+--enable_abort_on_error
+
+# Cleanup
+DROP DATABASE db_datadict;
+
diff --git a/mysql-test/suite/funcs_1/datadict/is_tables.inc b/mysql-test/suite/funcs_1/datadict/is_tables.inc
new file mode 100644
index 00000000000..6d47c33e715
--- /dev/null
+++ b/mysql-test/suite/funcs_1/datadict/is_tables.inc
@@ -0,0 +1,475 @@
+# suite/funcs_1/datadict/is_tables.inc
+#
+# Check the layout of information_schema.tables and the impact of
+# CREATE/ALTER/DROP TABLE/VIEW/SCHEMA ... on it.
+#
+# Note:
+#    This test is not intended
+#    - to show information about the all time existing tables
+#      within the databases information_schema and mysql
+#    - for checking storage engine properties
+#      Therefore please do not alter $engine_type and $other_engine_type.
+#      Some results of the subtests depend on the storage engines assigned.
+#
+# Author:
+# 2008-01-23 mleich WL#4203 Reorganize and fix the data dictionary tests of
+#                           testsuite funcs_1
+#                   Create this script based on older scripts and new code.
+# Last Change:
+# 2008-06-11 mleich Move t/is_tables.test to this file and
+#                   create variants for embedded/non embedded server.
+#
+
+let $engine_type       = MEMORY;
+let $other_engine_type = MyISAM;
+
+let $is_table = TABLES;
+
+# The table INFORMATION_SCHEMA.TABLES must exist
+eval SHOW TABLES FROM information_schema LIKE '$is_table';
+
+--echo #######################################################################
+--echo # Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT
+--echo #######################################################################
+# Ensure that every INFORMATION_SCHEMA table can be queried with a SELECT
+# statement, just as if it were an ordinary user-defined table.
+#
+--source suite/funcs_1/datadict/is_table_query.inc
+
+
+--echo #########################################################################
+--echo # Testcase 3.2.12.1: INFORMATION_SCHEMA.TABLES layout
+--echo #########################################################################
+# Ensure that the INFORMATION_SCHEMA.TABLES table has the following columns,
+# in the following order:
+#
+# TABLE_CATALOG (always shows NULL),
+# TABLE_SCHEMA (shows the name of the database, or schema, in which an
+#       accessible table resides),
+# TABLE_NAME (shows the name of a table which the current user may access),
+# TABLE_TYPE (shows whether the table is a BASE TABLE, a TEMPORARY table,
+#       or a VIEW),
+# ENGINE (shows the storage engine used for the table),
+# VERSION (shows the version number of the table's .frm file),
+# ROW_FORMAT (shows the table's row storage format; either FIXED, DYNAMIC
+#       or COMPRESSED),
+# TABLE_ROWS (shows the number of rows in the table),
+# AVG_ROW_LENGTH (shows the average length of the table's rows),
+# DATA_LENGTH (shows the length of the table's data file),
+# MAX_DATA_LENGTH (shows the maximum length of the table's data file),
+# INDEX_LENGTH (shows the length of the index file associated with the table),
+# DATA_FREE (shows the number of allocated, unused bytes),
+# AUTO_INCREMENT (shows the next AUTO_INCREMENT value, where applicable),
+# CREATE_TIME (shows the timestamp of the time the table was created),
+# UPDATE_TIME (shows the timestamp of the time the table's data file was
+#       last updated),
+# CHECK_TIME (shows the timestamp of the time the table was last checked),
+# TABLE_COLLATION (shows the table's default collation),
+# CHECKSUM (shows the live checksum value for the table, if any; otherwise NULL),
+# CREATE_OPTIONS (shows any additional options used in the table's definition;
+#       otherwise NULL),
+# TABLE_COMMENT (shows the comment added to the table's definition;
+#       otherwise NULL).
+#
+--source suite/funcs_1/datadict/datadict_bug_12777.inc
+eval DESCRIBE          information_schema.$is_table;
+--source suite/funcs_1/datadict/datadict_bug_12777.inc
+eval SHOW CREATE TABLE information_schema.$is_table;
+--source suite/funcs_1/datadict/datadict_bug_12777.inc
+eval SHOW COLUMNS FROM information_schema.$is_table;
+
+# Note: Retrieval of information within information_schema.columns about
+#       information_schema.tables is in is_columns_is.test.
+
+# Show that TABLE_CATALOG is always NULL.
+SELECT table_catalog, table_schema, table_name
+FROM information_schema.tables WHERE table_catalog IS NOT NULL;
+
+
+--echo ################################################################################
+--echo # Testcase 3.2.12.2 + 3.2.12.3: INFORMATION_SCHEMA.TABLES accessible information
+--echo ################################################################################
+# 3.2.12.2: Ensure that the table shows the relevant information on every base table
+#           and view on which the current user or PUBLIC has privileges.
+# 3.2.12.3: Ensure that the table does not show any information on any tables
+#           on which the current user and public have no privileges.
+#
+# Note: Check of content within information_schema.tables about tables within
+#       database            is in
+#       mysql               is_tables_mysql.test
+#       information_schema  is_tables_is.test
+#       test%               is_tables_<engine>.test
+#
+--disable_warnings
+DROP DATABASE IF EXISTS db_datadict;
+--enable_warnings
+CREATE DATABASE db_datadict;
+
+--error 0,ER_CANNOT_USER
+DROP   USER 'testuser1'@'localhost';
+CREATE USER 'testuser1'@'localhost';
+GRANT CREATE, CREATE VIEW, INSERT, SELECT ON db_datadict.*
+   TO 'testuser1'@'localhost' WITH GRANT OPTION;
+--error 0,ER_CANNOT_USER
+DROP   USER 'testuser2'@'localhost';
+CREATE USER 'testuser2'@'localhost';
+--error 0,ER_CANNOT_USER
+DROP   USER 'testuser3'@'localhost';
+CREATE USER 'testuser3'@'localhost';
+
+--replace_result $engine_type <engine_type>
+eval
+CREATE TABLE db_datadict.tb1 (f1 INT, f2 INT, f3 INT)
+ENGINE = $engine_type;
+
+GRANT SELECT ON db_datadict.tb1 TO 'testuser1'@'localhost';
+GRANT ALL    ON db_datadict.tb1 TO 'testuser2'@'localhost' WITH GRANT OPTION;
+
+let $my_select = SELECT * FROM information_schema.tables
+WHERE table_schema = 'db_datadict' ORDER BY table_name;
+let $my_show = SHOW TABLES FROM db_datadict;
+
+--echo # Establish connection testuser1 (user=testuser1)
+--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
+connect (testuser1, localhost, testuser1, , db_datadict);
+# tb2 is not granted to anyone
+--replace_result $engine_type <engine_type>
+eval
+CREATE TABLE tb2 (f1 DECIMAL)
+ENGINE = $engine_type;
+--replace_result $engine_type <engine_type>
+eval
+CREATE TABLE tb3 (f1 VARCHAR(200))
+ENGINE = $engine_type;
+GRANT SELECT ON db_datadict.tb3 to 'testuser3'@'localhost';
+GRANT INSERT ON db_datadict.tb3 to 'testuser2'@'localhost';
+CREATE VIEW v3 AS SELECT * FROM tb3;
+GRANT SELECT ON db_datadict.v3 to 'testuser3'@'localhost';
+
+if ($have_bug_32285)
+{
+--disable_ps_protocol
+}
+# We do not want to check here values affected by
+# - the storage engine used
+# - Operating system / Filesystem
+# - start time of test
+# 1 TABLE_CATALOG
+# 2 TABLE_SCHEMA
+# 3 TABLE_NAME
+# 4 TABLE_TYPE
+# 5 ENGINE           affected by storage engine used
+# 6 VERSION
+# 7 ROW_FORMAT       affected by storage engine used
+# 8 TABLE_ROWS
+# 9 AVG_ROW_LENGTH   affected by storage engine used
+# 10 DATA_LENGTH     affected by storage engine used and maybe OS
+# 11 MAX_DATA_LENGTH affected by storage engine used and maybe OS
+# 12 INDEX_LENGTH    affected by storage engine used and maybe OS
+# 13 DATA_FREE       affected by storage engine used and maybe OS
+# 14 AUTO_INCREMENT
+# 15 CREATE_TIME     depends roughly on start time of test (*)
+# 16 UPDATE_TIME     depends roughly on start time of test (*)
+# 17 CHECK_TIME      depends roughly on start time of test and storage engine (*)
+# 18 TABLE_COLLATION
+# 19 CHECKSUM        affected by storage engine used
+# 20 CREATE_OPTIONS
+# 21 TABLE_COMMENT   affected by some storage engines
+# (*) In case of view or temporary table NULL.
+--replace_column 5 "#ENG#"  7 "#RF#"  9 "#ARL#"  10 "#DL#"  11 "#MDL#"  12 "#IL#"  13 "#DF#"  15 "#CRT"  16 "#UT#" 17 "#CT#"  19 "#CS#"
+eval $my_select;
+--enable_ps_protocol
+--sorted_result
+eval $my_show;
+
+--echo # Establish connection testuser2 (user=testuser2)
+--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
+connect (testuser2, localhost, testuser2, , db_datadict);
+if ($have_bug_32285)
+{
+--disable_ps_protocol
+}
+--replace_column 5 "#ENG#"  7 "#RF#"  9 "#ARL#"  10 "#DL#"  11 "#MDL#"  12 "#IL#"  13 "#DF#"  15 "#CRT"  16 "#UT#" 17 "#CT#"  19 "#CS#"
+eval $my_select;
+--enable_ps_protocol
+--sorted_result
+eval $my_show;
+
+--echo # Establish connection testuser3 (user=testuser3)
+--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
+connect (testuser3, localhost, testuser3, , db_datadict);
+if ($have_bug_32285)
+{
+--disable_ps_protocol
+}
+--replace_column 5 "#ENG#"  7 "#RF#"  9 "#ARL#"  10 "#DL#"  11 "#MDL#"  12 "#IL#"  13 "#DF#"  15 "#CRT"  16 "#UT#" 17 "#CT#"  19 "#CS#"
+eval $my_select;
+--enable_ps_protocol
+--sorted_result
+eval $my_show;
+
+--echo # Switch to connection default (user=root)
+connection default;
+# we see only 'public' tables
+if ($have_bug_32285)
+{
+--disable_ps_protocol
+}
+--replace_column 5 "#ENG#"  7 "#RF#"  9 "#ARL#"  10 "#DL#"  11 "#MDL#"  12 "#IL#"  13 "#DF#"  15 "#CRT"  16 "#UT#" 17 "#CT#"  19 "#CS#"
+eval $my_select;
+--enable_ps_protocol
+--sorted_result
+eval $my_show;
+
+# Cleanup
+--echo # Close connection testuser1, testuser2, testuser3
+disconnect testuser1;
+disconnect testuser2;
+disconnect testuser3;
+DROP USER 'testuser1'@'localhost';
+DROP USER 'testuser2'@'localhost';
+DROP USER 'testuser3'@'localhost';
+DROP DATABASE db_datadict;
+
+
+--echo #########################################################################
+--echo # 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.TABLES modifications
+--echo #########################################################################
+# 3.2.1.13: Ensure that the creation of any new database object (e.g. table or
+#           column) automatically inserts all relevant information on that
+#           object into every appropriate INFORMATION_SCHEMA table.
+# 3.2.1.14: Ensure that the alteration of any existing database object
+#           automatically updates all relevant information on that object in
+#           every appropriate INFORMATION_SCHEMA table.
+# 3.2.1.15: Ensure that the dropping of any existing database object
+#           automatically deletes all relevant information on that object from
+#           every appropriate INFORMATION_SCHEMA table.
+#
+--disable_warnings
+DROP TABLE IF EXISTS test.t1_my_table;
+DROP DATABASE IF EXISTS db_datadict;
+--enable_warnings
+CREATE DATABASE db_datadict;
+
+SELECT table_name FROM information_schema.tables
+WHERE table_name LIKE 't1_my_table%';
+--replace_result $engine_type <engine_type>
+eval
+CREATE TABLE test.t1_my_table (f1 BIGINT)
+DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci
+COMMENT = 'Initial Comment' ENGINE = $engine_type;
+# Settings used in CREATE TABLE must be visible in information_schema.tables.
+--vertical_results
+--replace_column 5 "#ENG#"  7 "#RF#"  9 "#ARL#"  10 "#DL#"  11 "#MDL#"  12 "#IL#"  13 "#DF#"  15 "#CRT"  16 "#UT#" 17 "#CT#"  19 "#CS#"
+SELECT * FROM information_schema.tables
+WHERE table_name = 't1_my_table';
+--horizontal_results
+#
+# Check modification of TABLE_NAME
+SELECT table_name FROM information_schema.tables
+WHERE table_name LIKE 't1_my_table%';
+RENAME TABLE test.t1_my_table TO test.t1_my_tablex;
+SELECT table_name FROM information_schema.tables
+WHERE table_name LIKE 't1_my_table%';
+#
+# Check modification of TABLE_SCHEMA
+SELECT table_schema,table_name FROM information_schema.tables
+WHERE table_name = 't1_my_tablex';
+RENAME TABLE test.t1_my_tablex TO db_datadict.t1_my_tablex;
+SELECT table_schema,table_name FROM information_schema.tables
+WHERE table_name = 't1_my_tablex';
+#
+# Check modification of ENGINE
+--replace_result $engine_type <engine_type>
+SELECT table_name, engine FROM information_schema.tables
+WHERE table_name = 't1_my_tablex';
+--replace_result $other_engine_type <other_engine_type>
+eval
+ALTER TABLE db_datadict.t1_my_tablex
+ENGINE = $other_engine_type;
+--replace_result $other_engine_type <other_engine_type>
+SELECT table_name, engine FROM information_schema.tables
+WHERE table_name = 't1_my_tablex';
+#
+# Check modification of TABLE_ROWS
+SELECT table_name, table_rows FROM information_schema.tables
+WHERE table_name = 't1_my_tablex';
+INSERT INTO db_datadict.t1_my_tablex VALUES(1),(2);
+SELECT table_name, table_rows FROM information_schema.tables
+WHERE table_name = 't1_my_tablex';
+#
+# Check indirect modification of TABLE_COLLATION
+SELECT table_name, table_collation FROM information_schema.tables
+WHERE table_name = 't1_my_tablex';
+ALTER TABLE db_datadict.t1_my_tablex DEFAULT CHARACTER SET utf8;
+SELECT table_name, table_collation FROM information_schema.tables
+WHERE table_name = 't1_my_tablex';
+# Check direct modification of TABLE_COLLATION
+SELECT table_name, table_collation FROM information_schema.tables
+WHERE table_name = 't1_my_tablex';
+ALTER TABLE db_datadict.t1_my_tablex
+DEFAULT CHARACTER SET latin1 COLLATE latin1_german1_ci;
+SELECT table_name, table_collation FROM information_schema.tables
+WHERE table_name = 't1_my_tablex';
+#
+# Check modification of TABLE_COMMENT
+SELECT table_name, TABLE_COMMENT FROM information_schema.tables
+WHERE table_name = 't1_my_tablex';
+ALTER TABLE db_datadict.t1_my_tablex COMMENT 'Changed Comment';
+SELECT table_name, TABLE_COMMENT FROM information_schema.tables
+WHERE table_name = 't1_my_tablex';
+#
+# Check modification of AUTO_INCREMENT
+SELECT table_name, AUTO_INCREMENT FROM information_schema.tables
+WHERE table_name = 't1_my_tablex';
+ALTER TABLE db_datadict.t1_my_tablex
+ADD f2 BIGINT AUTO_INCREMENT, ADD PRIMARY KEY (f2);
+SELECT table_name, AUTO_INCREMENT FROM information_schema.tables
+WHERE table_name = 't1_my_tablex';
+#
+# Check modification of ROW_FORMAT
+SELECT table_name, ROW_FORMAT FROM information_schema.tables
+WHERE table_name = 't1_my_tablex';
+ALTER TABLE db_datadict.t1_my_tablex ROW_FORMAT = dynamic;
+SELECT table_name, ROW_FORMAT FROM information_schema.tables
+WHERE table_name = 't1_my_tablex';
+#
+# Check "growth" of UPDATE_TIME and modification of CHECKSUM
+SELECT table_name, checksum FROM information_schema.tables
+WHERE table_name = 't1_my_tablex';
+ALTER TABLE db_datadict.t1_my_tablex CHECKSUM = 1;
+SELECT table_name, checksum IS NOT NULL FROM information_schema.tables
+WHERE table_name = 't1_my_tablex';
+SELECT UPDATE_TIME, checksum INTO @UPDATE_TIME, @checksum
+FROM information_schema.tables
+WHERE table_name = 't1_my_tablex';
+#   Enforce a time difference bigger than the smallest unit (1 second).
+--real_sleep 1.1
+INSERT INTO db_datadict.t1_my_tablex SET f1 = 3;
+SELECT UPDATE_TIME > @UPDATE_TIME
+    AS "Is current UPDATE_TIME bigger than before last INSERT?"
+FROM information_schema.tables
+WHERE table_name = 't1_my_tablex';
+SELECT checksum <> @checksum
+    AS "Is current CHECKSUM different than before last INSERT?"
+FROM information_schema.tables
+WHERE table_name = 't1_my_tablex';
+#
+# Information is used later
+SELECT CREATE_TIME INTO @CREATE_TIME FROM information_schema.tables
+WHERE table_name = 't1_my_tablex';
+#
+# Check impact of DROP TABLE
+SELECT table_name FROM information_schema.tables
+WHERE table_name LIKE 't1_my_table%';
+DROP TABLE db_datadict.t1_my_tablex;
+SELECT table_name FROM information_schema.tables
+WHERE table_name LIKE 't1_my_table%';
+#
+# Check "growth" of CREATE_TIME
+--replace_result $other_engine_type <other_engine_type>
+eval
+CREATE TABLE test.t1_my_tablex (f1 BIGINT)
+ENGINE = $other_engine_type;
+SELECT CREATE_TIME > @CREATE_TIME
+    AS "Is current CREATE_TIME bigger than for the old dropped table?"
+FROM information_schema.tables
+WHERE table_name = 't1_my_tablex';
+DROP TABLE test.t1_my_tablex;
+#
+# Check a VIEW
+CREATE VIEW test.t1_my_tablex AS SELECT 1;
+--vertical_results
+SELECT * FROM information_schema.tables
+WHERE table_name = 't1_my_tablex';
+--horizontal_results
+DROP VIEW test.t1_my_tablex;
+SELECT table_name FROM information_schema.tables
+WHERE table_name = 't1_my_tablex';
+#
+# Check a temporary table
+--replace_result $other_engine_type <other_engine_type>
+eval
+CREATE TEMPORARY TABLE test.t1_my_tablex
+ENGINE = $other_engine_type
+   AS SELECT 1;
+--vertical_results
+SELECT table_name, table_type FROM information_schema.tables
+WHERE table_name = 't1_my_tablex';
+--horizontal_results
+DROP TEMPORARY TABLE test.t1_my_tablex;
+#
+# Check impact of DROP SCHEMA
+--replace_result $engine_type <engine_type>
+eval
+CREATE TABLE db_datadict.t1_my_tablex
+ENGINE = $engine_type AS
+SELECT 1;
+SELECT table_name FROM information_schema.tables
+WHERE table_name = 't1_my_tablex';
+DROP DATABASE db_datadict;
+SELECT table_name FROM information_schema.tables
+WHERE table_name = 't1_my_tablex';
+
+
+--echo ########################################################################
+--echo # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
+--echo #           DDL on INFORMATION_SCHEMA tables are not supported
+--echo ########################################################################
+# 3.2.1.3:  Ensure that no user may execute an INSERT statement on any
+#           INFORMATION_SCHEMA table.
+# 3.2.1.4:  Ensure that no user may execute an UPDATE statement on any
+#           INFORMATION_SCHEMA table.
+# 3.2.1.5:  Ensure that no user may execute a DELETE statement on any
+#           INFORMATION_SCHEMA table.
+# 3.2.1.8:  Ensure that no user may create an index on an
+#           INFORMATION_SCHEMA table.
+# 3.2.1.9:  Ensure that no user may alter the definition of an
+#           INFORMATION_SCHEMA table.
+# 3.2.1.10: Ensure that no user may drop an INFORMATION_SCHEMA table.
+# 3.2.1.11: Ensure that no user may move an INFORMATION_SCHEMA table to any
+#           other database.
+# 3.2.1.12: Ensure that no user may directly add to, alter, or delete any data
+#           in an INFORMATION_SCHEMA table.
+#
+--disable_warnings
+DROP DATABASE IF EXISTS db_datadict;
+--enable_warnings
+CREATE DATABASE db_datadict;
+--replace_result $engine_type <engine_type>
+eval
+CREATE TABLE db_datadict.t1 (f1 BIGINT)
+ENGINE = $engine_type;
+
+--error ER_DBACCESS_DENIED_ERROR
+INSERT INTO information_schema.tables
+SELECT * FROM information_schema.tables;
+
+--error ER_DBACCESS_DENIED_ERROR
+UPDATE information_schema.tables SET table_schema = 'test'
+WHERE table_name = 't1';
+
+--error ER_DBACCESS_DENIED_ERROR
+DELETE FROM information_schema.tables WHERE table_name = 't1';
+--error ER_DBACCESS_DENIED_ERROR
+TRUNCATE information_schema.tables;
+
+--error ER_DBACCESS_DENIED_ERROR
+CREATE INDEX my_idx_on_tables ON information_schema.tables(table_schema);
+
+--error ER_DBACCESS_DENIED_ERROR
+ALTER TABLE information_schema.tables DROP PRIMARY KEY;
+--error ER_DBACCESS_DENIED_ERROR
+ALTER TABLE information_schema.tables ADD f1 INT;
+
+--error ER_DBACCESS_DENIED_ERROR
+DROP TABLE information_schema.tables;
+
+--error ER_DBACCESS_DENIED_ERROR
+ALTER TABLE information_schema.tables RENAME db_datadict.tables;
+--error ER_DBACCESS_DENIED_ERROR
+ALTER TABLE information_schema.tables RENAME information_schema.xtables;
+
+# Cleanup
+DROP DATABASE db_datadict;
+
diff --git a/mysql-test/suite/funcs_1/datadict/is_triggers.inc b/mysql-test/suite/funcs_1/datadict/is_triggers.inc
new file mode 100644
index 00000000000..df3e6e7d2b6
--- /dev/null
+++ b/mysql-test/suite/funcs_1/datadict/is_triggers.inc
@@ -0,0 +1,259 @@
+# suite/funcs_1/datadict/is_triggers.inc
+#
+# Check the layout of information_schema.triggers and the impact of
+# CREATE/ALTER/DROP TABLE/VIEW/SCHEMA ... on it.
+#
+# Note:
+#    This test is not intended
+#    - to show information about the all time existing triggers
+#      (there are no in the moment) within the databases information_schema
+#      and mysql
+#    - for checking storage engine properties
+#      Therefore please do not alter $engine_type and $other_engine_type.
+#
+# Author:
+# 2008-01-23 mleich WL#4203 Reorganize and fix the data dictionary tests of
+#                           testsuite funcs_1
+#                   Create this script based on older scripts and new code.
+# Last Change:
+# 2008-06-11 mleich Move t/is_triggers.test to this file and
+#                   create variants for embedded/non embedded server.
+#
+
+let $engine_type       = MEMORY;
+let $other_engine_type = MyISAM;
+
+let $is_table = TRIGGERS;
+
+# The table INFORMATION_SCHEMA.TRIGGERS must exist
+eval SHOW TABLES FROM information_schema LIKE '$is_table';
+
+--echo #######################################################################
+--echo # Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT
+--echo #######################################################################
+# Ensure that every INFORMATION_SCHEMA table can be queried with a SELECT
+# statement, just as if it were an ordinary user-defined table.
+#
+--source suite/funcs_1/datadict/is_table_query.inc
+
+
+--echo #########################################################################
+--echo # Testcase 3.2.12.1: INFORMATION_SCHEMA.TRIGGERS layout
+--echo #########################################################################
+# Ensure that the INFORMATION_SCHEMA.TRIGGERS table has the following columns,
+# in the following order:
+#
+# TRIGGER_CATALOG             NULL
+# TRIGGER_SCHEMA              name of the database in which the trigger occurs
+# TRIGGER_NAME
+# EVENT_MANIPULATION          event associated with the trigger
+#                             ('INSERT', 'DELETE', or 'UPDATE')
+# EVENT_OBJECT_CATALOG        NULL
+# EVENT_OBJECT_SCHEMA         database in which the table associated with the
+#                             trigger occurs
+# EVENT_OBJECT_TABLE          name of the table associated with the trigger
+# ACTION_ORDER                0
+# ACTION_CONDITION            NULL
+# ACTION_STATEMENT
+# ACTION_ORIENTATION          ROW
+# ACTION_TIMING               'BEFORE' or 'AFTER'
+# ACTION_REFERENCE_OLD_TABLE  NULL
+# ACTION_REFERENCE_NEW_TABLE  NULL
+# ACTION_REFERENCE_OLD_ROW    OLD
+# ACTION_REFERENCE_NEW_ROW    NEW
+# CREATED                     NULL (0)
+# SQL_MODE                    server SQL mode that was in effect at the time
+#                             when the trigger was created
+#                             (also used during trigger execution)
+# DEFINER                     who defined the trigger
+#
+--source suite/funcs_1/datadict/datadict_bug_12777.inc
+eval DESCRIBE          information_schema.$is_table;
+--source suite/funcs_1/datadict/datadict_bug_12777.inc
+--replace_result ENGINE=MyISAM "" ENGINE=MARIA "" " PAGE_CHECKSUM=1" "" " PAGE_CHECKSUM=0" ""
+eval SHOW CREATE TABLE information_schema.$is_table;
+--source suite/funcs_1/datadict/datadict_bug_12777.inc
+eval SHOW COLUMNS FROM information_schema.$is_table;
+
+
+# Note: Retrieval of information within information_schema.columns about
+#       information_schema.tables is in is_columns_is.test.
+
+# Show that several columns are always NULL.
+SELECT * FROM information_schema.triggers
+WHERE trigger_catalog IS NOT NULL OR event_object_catalog IS NOT NULL
+   OR action_condition IS NOT NULL OR action_reference_old_table IS NOT NULL
+   OR action_reference_new_table IS NOT NULL;
+
+
+--echo ##################################################################################
+--echo # Testcase 3.2.18.2 + 3.2.18.3: INFORMATION_SCHEMA.TRIGGERS accessible information
+--echo ##################################################################################
+# 3.2.18.2: Ensure that the table shows the relevant information on every
+#           trigger on which the current user or PUBLIC has privileges.
+# 3.2.18.3: Ensure that the table does not show any information on any trigger
+#           on which the current user and public have no privileges.
+# The SUPER (before 5.1.22) or TRIGGER (since 5.1.22) privilege is required for
+# - creation of triggers
+# - retrieval in INFORMATION_SCHEMA.TRIGGERS (affects size of result set)
+#
+--disable_warnings
+DROP DATABASE IF EXISTS db_datadict;
+--enable_warnings
+CREATE DATABASE db_datadict;
+--error 0,ER_CANNOT_USER
+DROP   USER 'testuser1'@'localhost';
+CREATE USER 'testuser1'@'localhost';
+--error 0,ER_CANNOT_USER
+DROP   USER 'testuser2'@'localhost';
+CREATE USER 'testuser2'@'localhost';
+--error 0,ER_CANNOT_USER
+DROP   USER 'testuser3'@'localhost';
+CREATE USER 'testuser3'@'localhost';
+--error 0,ER_CANNOT_USER
+DROP   USER 'testuser4'@'localhost';
+CREATE USER 'testuser4'@'localhost';
+
+GRANT TRIGGER ON *.* TO 'testuser1'@'localhost';
+GRANT TRIGGER ON *.* TO 'testuser3'@'localhost';
+GRANT TRIGGER ON *.* TO 'testuser4'@'localhost';
+GRANT ALL ON db_datadict.* TO 'testuser1'@'localhost' WITH GRANT OPTION;
+
+let $my_select = SELECT * FROM information_schema.triggers
+WHERE trigger_name = 'trg1';
+let $my_show =   SHOW TRIGGERS FROM db_datadict;
+--echo # Establish connection testuser1 (user=testuser1)
+--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
+connect (testuser1, localhost, testuser1, , db_datadict);
+--replace_result $engine_type <engine_type>
+eval
+CREATE TABLE db_datadict.t1 (f1 INT, f2 INT, f3 INT)
+ENGINE = $engine_type;
+CREATE TRIGGER trg1 BEFORE INSERT
+ON db_datadict.t1 FOR EACH ROW SET @test_before = 2, new.f1 = @test_before;
+GRANT ALL ON db_datadict.t1 TO 'testuser2'@'localhost';
+REVOKE TRIGGER ON db_datadict.t1 FROM 'testuser2'@'localhost';
+GRANT SELECT ON db_datadict.t1 TO 'testuser3'@'localhost';
+eval $my_select;
+eval $my_show;
+
+--echo # Establish connection testuser2 (user=testuser2)
+--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
+connect (testuser2, localhost, testuser2, , db_datadict);
+SHOW GRANTS FOR 'testuser2'@'localhost';
+--echo # No TRIGGER Privilege --> no result for query
+eval $my_select;
+eval $my_show;
+
+--echo # Establish connection testuser3 (user=testuser3)
+--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
+connect (testuser3, localhost, testuser3, , test);
+SHOW GRANTS FOR 'testuser3'@'localhost';
+--echo # TRIGGER Privilege + SELECT Privilege on t1 --> result for query
+eval $my_select;
+eval $my_show;
+
+--echo # Establish connection testuser4 (user=testuser4)
+--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
+connect (testuser4, localhost, testuser4, , test);
+SHOW GRANTS FOR 'testuser4'@'localhost';
+--echo # TRIGGER Privilege + no SELECT Privilege on t1 --> result for query
+--disable_abort_on_error
+SELECT * FROM db_datadict.t1;
+DESC db_datadict.t1;
+eval $my_select;
+eval $my_show;
+
+--echo # Switch to connection default and close connections testuser1 - testuser4
+connection default;
+disconnect testuser1;
+disconnect testuser2;
+disconnect testuser3;
+disconnect testuser4;
+eval $my_select;
+eval $my_show;
+DROP USER 'testuser1'@'localhost';
+DROP USER 'testuser2'@'localhost';
+DROP USER 'testuser3'@'localhost';
+DROP USER 'testuser4'@'localhost';
+DROP DATABASE db_datadict;
+
+
+--echo #########################################################################
+--echo # 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.TRIGGERS modifications
+--echo #########################################################################
+# 3.2.1.13: Ensure that the creation of any new database object (e.g. table or
+#           column) automatically inserts all relevant information on that
+#           object into every appropriate INFORMATION_SCHEMA table.
+# 3.2.1.14: Ensure that the alteration of any existing database object
+#           automatically updates all relevant information on that object in
+#           every appropriate INFORMATION_SCHEMA table.
+# 3.2.1.15: Ensure that the dropping of any existing database object
+#           automatically deletes all relevant information on that object from
+#           every appropriate INFORMATION_SCHEMA table.
+# FIXME: To be implemented
+
+
+--echo ########################################################################
+--echo # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
+--echo #           DDL on INFORMATION_SCHEMA tables are not supported
+--echo ########################################################################
+# 3.2.1.3:  Ensure that no user may execute an INSERT statement on any
+#           INFORMATION_SCHEMA table.
+# 3.2.1.4:  Ensure that no user may execute an UPDATE statement on any
+#           INFORMATION_SCHEMA table.
+# 3.2.1.5:  Ensure that no user may execute a DELETE statement on any
+#           INFORMATION_SCHEMA table.
+# 3.2.1.8:  Ensure that no user may create an index on an
+#           INFORMATION_SCHEMA table.
+# 3.2.1.9:  Ensure that no user may alter the definition of an
+#           INFORMATION_SCHEMA table.
+# 3.2.1.10: Ensure that no user may drop an INFORMATION_SCHEMA table.
+# 3.2.1.11: Ensure that no user may move an INFORMATION_SCHEMA table to any
+#           other database.
+# 3.2.1.12: Ensure that no user may directly add to, alter, or delete any data
+#           in an INFORMATION_SCHEMA table.
+#
+--disable_warnings
+DROP DATABASE IF EXISTS db_datadict;
+--enable_warnings
+CREATE DATABASE db_datadict;
+--replace_result $engine_type <engine_type>
+eval
+CREATE TABLE db_datadict.t1 (f1 BIGINT)
+ENGINE = $engine_type;
+CREATE TRIGGER db_datadict.trg1 BEFORE INSERT
+ON db_datadict.t1 FOR EACH ROW SET @test_before = 2, new.f1 = @test_before;
+
+--error ER_DBACCESS_DENIED_ERROR
+INSERT INTO information_schema.triggers
+SELECT * FROM information_schema.triggers;
+
+--error ER_DBACCESS_DENIED_ERROR
+UPDATE information_schema.triggers SET trigger_schema = 'test'
+WHERE table_name = 't1';
+
+--error ER_DBACCESS_DENIED_ERROR
+DELETE FROM information_schema.triggers WHERE trigger_name = 't1';
+--error ER_DBACCESS_DENIED_ERROR
+TRUNCATE information_schema.triggers;
+
+--error ER_DBACCESS_DENIED_ERROR
+CREATE INDEX my_idx_on_triggers ON information_schema.triggers(trigger_schema);
+
+--error ER_DBACCESS_DENIED_ERROR
+ALTER TABLE information_schema.triggers DROP PRIMARY KEY;
+--error ER_DBACCESS_DENIED_ERROR
+ALTER TABLE information_schema.triggers ADD f1 INT;
+
+--error ER_DBACCESS_DENIED_ERROR
+DROP TABLE information_schema.triggers;
+
+--error ER_DBACCESS_DENIED_ERROR
+ALTER TABLE information_schema.triggers RENAME db_datadict.triggers;
+--error ER_DBACCESS_DENIED_ERROR
+ALTER TABLE information_schema.triggers RENAME information_schema.xtriggers;
+
+# Cleanup
+DROP DATABASE db_datadict;
+
diff --git a/mysql-test/suite/funcs_1/datadict/is_views.inc b/mysql-test/suite/funcs_1/datadict/is_views.inc
new file mode 100644
index 00000000000..b04904c2eba
--- /dev/null
+++ b/mysql-test/suite/funcs_1/datadict/is_views.inc
@@ -0,0 +1,307 @@
+# suite/funcs_1/datadict/is_views.inc
+#
+# Check the layout of information_schema.views and the impact of
+# CREATE/ALTER/DROP TABLE/VIEW/SCHEMA ... on it.
+#
+# Note:
+#    - This test should not check storage engine properties.
+#    - Please do not change the storage engines used within this test
+#      except you know that the impact is acceptable.
+#      Some storage engines might not support the modification of
+#      properties like in the following tests.
+#
+# Author:
+# 2008-01-23 mleich WL#4203 Reorganize and fix the data dictionary tests of
+#                           testsuite funcs_1
+#                   Create this script based on older scripts and new code.
+# Last Change:
+# 2008-06-11 mleich Move t/is_views.test to this file and
+#                   create variants for embedded/non embedded server.
+#
+
+let $engine_type       = MEMORY;
+let $other_engine_type = MyISAM;
+
+let $is_table = VIEWS;
+
+# The table INFORMATION_SCHEMA.VIEWS must exist
+eval SHOW TABLES FROM information_schema LIKE '$is_table';
+
+--echo #######################################################################
+--echo # Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT
+--echo #######################################################################
+# Ensure that every INFORMATION_SCHEMA table can be queried with a SELECT
+# statement, just as if it were an ordinary user-defined table.
+#
+--source suite/funcs_1/datadict/is_table_query.inc
+
+
+--echo #########################################################################
+--echo # Testcase 3.2.13.1: INFORMATION_SCHEMA.VIEWS layout
+--echo #########################################################################
+# Ensure that the INFORMATION_SCHEMA.VIEWS table has the following columns,
+# in the following order:
+#
+# TABLE_CATALOG (always shows NULL),
+# TABLE_SCHEMA (shows the database, or schema, in which an accessible
+#       view resides),
+# TABLE_NAME (shows the name of a view accessible to the current user),
+# VIEW_DEFINITION (shows the SELECT statement that makes up the
+#       view's definition),
+# CHECK_OPTION (shows the value of the WITH CHECK OPTION clause used to define
+#       the view, either NONE, LOCAL or CASCADED),
+# IS_UPDATABLE (shows whether the view is an updatable view),
+# DEFINER (added with 5.0.14),
+# SECURITY_TYPE (added with 5.0.14).
+#
+--source suite/funcs_1/datadict/datadict_bug_12777.inc
+eval DESCRIBE          information_schema.$is_table;
+--source suite/funcs_1/datadict/datadict_bug_12777.inc
+--replace_result ENGINE=MyISAM "" ENGINE=MARIA "" " PAGE_CHECKSUM=1" "" " PAGE_CHECKSUM=0" ""
+eval SHOW CREATE TABLE information_schema.$is_table;
+--source suite/funcs_1/datadict/datadict_bug_12777.inc
+eval SHOW COLUMNS FROM information_schema.$is_table;
+
+# Note: Retrieval of information within information_schema.columns about
+#       information_schema.views is in is_columns_is.test.
+
+# Show that TABLE_CATALOG is always NULL.
+SELECT table_catalog, table_schema, table_name
+FROM information_schema.views WHERE table_catalog IS NOT NULL;
+
+
+--echo ################################################################################
+--echo # Testcase 3.2.13.2 + 3.2.13.3: INFORMATION_SCHEMA.VIEWS accessible information
+--echo ################################################################################
+# 3.2.13.2: Ensure that the table shows the relevant information on every view for
+#           which the current user or PUBLIC has the SHOW CREATE VIEW privilege.
+# 3.2.13.3: Ensure that the table does not show any information on any views for which
+#           the current user and PUBLIC have no SHOW CREATE VIEW privilege.
+#
+--disable_warnings
+DROP DATABASE IF EXISTS db_datadict;
+--enable_warnings
+CREATE DATABASE db_datadict;
+
+--error 0,ER_CANNOT_USER
+DROP   USER 'testuser1'@'localhost';
+CREATE USER 'testuser1'@'localhost';
+--error 0,ER_CANNOT_USER
+DROP   USER 'testuser2'@'localhost';
+CREATE USER 'testuser2'@'localhost';
+--error 0,ER_CANNOT_USER
+DROP   USER 'test_no_views'@'localhost';
+CREATE USER 'test_no_views'@'localhost';
+
+--replace_result $engine_type <engine_type>
+eval
+CREATE TABLE db_datadict.t1(f1 INT, f2 INT, f3 INT)
+ENGINE = $engine_type;
+CREATE VIEW db_datadict.v_granted_to_1 AS SELECT * FROM db_datadict.t1;
+CREATE VIEW db_datadict.v_granted_glob AS SELECT f2, f3 FROM db_datadict.t1;
+
+GRANT SELECT ON db_datadict.t1 TO 'testuser1'@'localhost';
+GRANT SELECT ON db_datadict.v_granted_to_1 TO 'testuser1'@'localhost';
+GRANT SHOW VIEW, CREATE VIEW ON db_datadict.* TO 'testuser2'@'localhost';
+
+let $select = SELECT * FROM information_schema.views
+WHERE table_schema = 'db_datadict' ORDER BY table_name;
+eval $select;
+
+--echo # Establish connection testuser1 (user=testuser1)
+--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
+connect (testuser1, localhost, testuser1, , test);
+eval $select;
+
+--echo # Establish connection testuser2 (user=testuser2)
+--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
+connect (testuser2, localhost, testuser2, , test);
+eval $select;
+
+--echo # Establish connection test_no_views (user=test_no_views)
+--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
+connect (test_no_views, localhost, test_no_views, , test);
+eval $select;
+
+# Cleanup
+--echo # Switch to connection default and close all other connections
+connection default;
+disconnect testuser1;
+disconnect testuser2;
+disconnect test_no_views;
+DROP USER 'testuser1'@'localhost';
+DROP USER 'testuser2'@'localhost';
+DROP USER 'test_no_views'@'localhost';
+DROP DATABASE db_datadict;
+
+--echo #########################################################################
+--echo # 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.VIEWS modifications
+--echo #########################################################################
+# 3.2.1.13: Ensure that the creation of any new database object (e.g. table or
+#           column) automatically inserts all relevant information on that
+#           object into every appropriate INFORMATION_SCHEMA table.
+# 3.2.1.14: Ensure that the alteration of any existing database object
+#           automatically updates all relevant information on that object in
+#           every appropriate INFORMATION_SCHEMA table.
+# 3.2.1.15: Ensure that the dropping of any existing database object
+#           automatically deletes all relevant information on that object from
+#           every appropriate INFORMATION_SCHEMA table.
+#
+--disable_warnings
+DROP TABLE IF EXISTS test.t1_my_table;
+DROP DATABASE IF EXISTS db_datadict;
+--enable_warnings
+CREATE DATABASE db_datadict;
+--replace_result $engine_type <engine_type>
+eval
+CREATE TABLE test.t1_table (f1 BIGINT, f2 CHAR(10))
+DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci
+ENGINE = $engine_type;
+--error 0,ER_CANNOT_USER
+DROP   USER 'testuser1'@'localhost';
+CREATE USER 'testuser1'@'localhost';
+
+# Check just created VIEW
+SELECT * FROM information_schema.views
+WHERE table_name LIKE 't1_%';
+CREATE VIEW test.t1_view AS SELECT DISTINCT f1 FROM test.t1_table;
+SELECT * FROM information_schema.views
+WHERE table_name LIKE 't1_%';
+#
+# Check modification of DEFINER, SECURITY_TYPE, IS_UPDATABLE, VIEW_DEFINITION,
+# CHECK_OPTION
+SELECT table_name,definer FROM information_schema.views
+WHERE table_name = 't1_view';
+ALTER DEFINER = 'testuser1'@'localhost' VIEW test.t1_view AS
+SELECT DISTINCT f1 FROM test.t1_table;
+# The next result set could suffer from
+# Bug#22763  	Disrepancy between SHOW CREATE VIEW and I_S.VIEWS
+# because the VIEW definition is missing.
+# Therefore we exclude the problematic columns from the result set.
+SELECT table_name,definer,security_type FROM information_schema.views
+WHERE table_name LIKE 't1_%';
+ALTER DEFINER = 'root'@'localhost' SQL SECURITY INVOKER VIEW test.t1_view AS
+SELECT f1 FROM test.t1_table WITH LOCAL CHECK OPTION;
+SELECT table_name,definer,security_type FROM information_schema.views
+WHERE table_name LIKE 't1_%';
+#
+# Check modification of TABLE_SCHEMA
+SELECT table_schema,table_name FROM information_schema.views
+WHERE table_name LIKE 't1_%'
+ORDER BY table_schema,table_name;
+--error ER_FORBID_SCHEMA_CHANGE
+RENAME TABLE test.t1_view TO db_datadict.t1_view;
+# Workaround for missing move to another database
+DROP VIEW test.t1_view;
+CREATE VIEW db_datadict.t1_view AS SELECT * FROM test.t1_table;
+SELECT table_schema,table_name FROM information_schema.views
+WHERE table_name LIKE 't1_%'
+ORDER BY table_schema,table_name;
+#
+# Check modification of TABLE_NAME
+SELECT table_name FROM information_schema.views
+WHERE table_name LIKE 't1_%'
+ORDER BY table_name;
+RENAME TABLE db_datadict.t1_view TO db_datadict.t1_viewx;
+SELECT table_name FROM information_schema.views
+WHERE table_name LIKE 't1_%'
+ORDER BY table_name;
+#
+# Check impact of DROP VIEW
+SELECT table_name FROM information_schema.views
+WHERE table_name LIKE 't1_%'
+ORDER BY table_name;
+DROP VIEW db_datadict.t1_viewx;
+SELECT table_name FROM information_schema.views
+WHERE table_name LIKE 't1_%'
+ORDER BY table_name;
+CREATE VIEW db_datadict.t1_view AS SELECT * FROM test.t1_table;
+#
+# Check impact of DROP base TABLE of VIEW
+SELECT table_name FROM information_schema.views
+WHERE table_name LIKE 't1_%'
+ORDER BY table_name;
+DROP TABLE test.t1_table;
+SELECT table_name FROM information_schema.views
+WHERE table_name LIKE 't1_%'
+ORDER BY table_name;
+--replace_result $engine_type <engine_type>
+eval
+CREATE TABLE test.t1_table (f1 BIGINT, f2 CHAR(10))
+DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci COMMENT = 'Initial Comment'
+ENGINE = $engine_type;
+#
+# Check impact of DROP SCHEMA
+SELECT table_name FROM information_schema.views
+WHERE table_name LIKE 't1_%'
+ORDER BY table_name;
+DROP DATABASE db_datadict;
+SELECT table_name FROM information_schema.views
+WHERE table_name LIKE 't1_%'
+ORDER BY table_name;
+
+# Cleanup
+DROP USER 'testuser1'@'localhost';
+DROP TABLE test.t1_table;
+
+--echo ########################################################################
+--echo # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
+--echo #           DDL on INFORMATION_SCHEMA table are not supported
+--echo ########################################################################
+# 3.2.1.3:  Ensure that no user may execute an INSERT statement on any
+#           INFORMATION_SCHEMA table.
+# 3.2.1.4:  Ensure that no user may execute an UPDATE statement on any
+#           INFORMATION_SCHEMA table.
+# 3.2.1.5:  Ensure that no user may execute a DELETE statement on any
+#           INFORMATION_SCHEMA table.
+# 3.2.1.8:  Ensure that no user may create an index on an
+#           INFORMATION_SCHEMA table.
+# 3.2.1.9:  Ensure that no user may alter the definition of an
+#           INFORMATION_SCHEMA table.
+# 3.2.1.10: Ensure that no user may drop an INFORMATION_SCHEMA table.
+# 3.2.1.11: Ensure that no user may move an INFORMATION_SCHEMA table to any
+#           other database.
+# 3.2.1.12: Ensure that no user may directly add to, alter, or delete any data
+#           in an INFORMATION_SCHEMA table.
+#
+--disable_warnings
+DROP DATABASE IF EXISTS db_datadict;
+--enable_warnings
+CREATE DATABASE db_datadict;
+CREATE VIEW db_datadict.v1 AS SELECT 1;
+
+--error ER_DBACCESS_DENIED_ERROR
+INSERT INTO information_schema.views
+SELECT * FROM information_schema.views;
+--error ER_DBACCESS_DENIED_ERROR
+INSERT INTO information_schema.views(table_schema, table_name)
+VALUES ('db2', 'v2');
+
+--error ER_DBACCESS_DENIED_ERROR
+UPDATE information_schema.views SET table_schema = 'test'
+WHERE table_name = 't1';
+
+--error ER_DBACCESS_DENIED_ERROR
+DELETE FROM information_schema.views WHERE table_name = 't1';
+--error ER_DBACCESS_DENIED_ERROR
+TRUNCATE information_schema.views;
+
+--error ER_DBACCESS_DENIED_ERROR
+CREATE INDEX my_idx_on_views ON information_schema.views(table_schema);
+
+--error ER_DBACCESS_DENIED_ERROR
+ALTER TABLE information_schema.views DROP PRIMARY KEY;
+--error ER_DBACCESS_DENIED_ERROR
+ALTER TABLE information_schema.views ADD f1 INT;
+
+--error ER_DBACCESS_DENIED_ERROR
+DROP TABLE information_schema.views;
+
+--error ER_DBACCESS_DENIED_ERROR
+ALTER TABLE information_schema.views RENAME db_datadict.views;
+--error ER_DBACCESS_DENIED_ERROR
+ALTER TABLE information_schema.views RENAME information_schema.xviews;
+
+# Cleanup
+DROP DATABASE db_datadict;
+
diff --git a/mysql-test/suite/funcs_1/datadict/processlist_priv.inc b/mysql-test/suite/funcs_1/datadict/processlist_priv.inc
index 62dc0abd107..9c0ebda24b6 100644
--- a/mysql-test/suite/funcs_1/datadict/processlist_priv.inc
+++ b/mysql-test/suite/funcs_1/datadict/processlist_priv.inc
@@ -16,7 +16,7 @@
 #        That means our SHOW PROCESSLIST can come too early.           #
 #     Solution:                                                        #
 #        Close the connections at the end of the test.                 #
-#     Example2:                                                        #
+#     Example2 (2008-08-14 again observed):                            #
 #     1. connection X: SHOW PROCESSLIST/GRANT ... etc.                 #
 #     2. Switch to connection  Y                                       #
 #     3. SHOW PROCESSLIST might present a record like                  #
@@ -53,7 +53,10 @@
 #                    WL#3982 Test information_schema.processlist       #
 #                                                                      #
 # Last update:                                                         #
-# 2007-08-14 mleich  Corrections                                       #
+# 2008-08-14 mleich  Bug#38270 Test "processlist_priv_ps" fails on     #
+#                              varying "processlist" output            #
+#                    - Replace one sleep by a poll routines            #
+#                    - Remove or disable superfluous sleeps            #
 #                                                                      #
 ########################################################################
 
@@ -86,15 +89,28 @@ USE information_schema;
 --echo 1 Prepare test.
 --echo   connection default (user=root)
 --echo ####################################################################################
+if (`SELECT COUNT(*) <> 1 FROM processlist`)
+{
+   --echo This test expects one connection to the server.
+   --echo Expectation: USER  HOST       DB                  COMMAND  STATE      INFO
+   --echo Expectation: root  localhost  information_schema  Query    executing  SELECT USER,HOST,DB,COMMAND,STATE,INFO FROM processlist ORDER BY ID
+   --echo But we found in the moment:
+   SELECT USER,HOST,DB,COMMAND,STATE,INFO FROM processlist ORDER BY ID;
+   --echo Maybe
+   --echo - the base configuration (no of parallel auxiliary sessions) of the server has changed
+   --echo - a parallel test intended for another server accidently connected to our current one
+   --echo We cannot proceed in this situation. Abort
+   exit;
+}
 
 --echo ####################################################################################
 --echo 1.1 Create two user
 --echo ####################################################################################
 # access to info tables as normal user
---disable_abort_on_error
+--error 0, ER_CANNOT_USER
 DROP USER ddicttestuser1@'localhost';
+--error 0, ER_CANNOT_USER
 DROP USER ddicttestuser2@'localhost';
---enable_abort_on_error
 CREATE USER ddicttestuser1@'localhost';
 CREATE USER ddicttestuser2@'localhost';
 SET PASSWORD FOR ddicttestuser1@'localhost' = PASSWORD('ddictpass');
@@ -111,13 +127,21 @@ connect (con100,localhost,ddicttestuser1,ddictpass,information_schema);
 --echo   SHOW/SELECT shows all processes/threads.
 --echo ####################################################################################
 connection default;
+# Avoid Bug#38270 Test "processlist_priv_ps" fails on varying "processlist" output
+#    This subtest expects that the connection con100 is in state 'Sleep'.
+#    Poll till the connection con100 is in state COMMAND = 'Sleep'.
+let $wait_timeout= 10;
+let $wait_condition=
+SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST
+WHERE DB = 'information_schema' AND COMMAND = 'Sleep' AND USER = 'ddicttestuser1';
+--source include/wait_condition.inc
 --replace_result ENGINE=MyISAM "" ENGINE=MARIA "" " PAGE_CHECKSUM=1" "" " PAGE_CHECKSUM=0" ""
 eval SHOW CREATE TABLE $table;
---replace_column 1 ID 6 TIME
+--replace_column 1 ID 3 HOST_NAME 6 TIME
 eval SHOW $table;
---replace_column 1 ID 6 TIME
+--replace_column 1 ID 3 HOST_NAME 6 TIME
 eval SELECT * FROM $table $select_where ORDER BY id;
---replace_column 1 ID 6 TIME
+--replace_column 1 ID 3 HOST_NAME 6 TIME
 eval SELECT $columns FROM $table $select_where ORDER BY id;
 --source suite/funcs_1/datadict/datadict_priv.inc
 --real_sleep 0.3
@@ -128,13 +152,16 @@ eval SELECT $columns FROM $table $select_where ORDER BY id;
 connection con100;
 --echo   SHOW/SELECT shows only the processes (1) of the user.
 --echo ####################################################################################
+# No need for poll routine here.
+# The current state of the default session might depend on load of testing box
+# but "ddicttestuser1" must not see anything of the root session.
 --replace_result ENGINE=MyISAM "" ENGINE=MARIA "" " PAGE_CHECKSUM=1" "" " PAGE_CHECKSUM=0" ""
 eval SHOW CREATE TABLE $table;
---replace_column 1 ID 6 TIME
+--replace_column 1 ID 3 HOST_NAME 6 TIME
 eval SHOW $table;
---replace_column 1 ID 6 TIME
+--replace_column 1 ID 3 HOST_NAME 6 TIME
 eval SELECT * FROM $table $select_where ORDER BY id;
---replace_column 1 ID 6 TIME
+--replace_column 1 ID 3 HOST_NAME 6 TIME
 eval SELECT $columns FROM $table $select_where ORDER BY id;
 --source suite/funcs_1/datadict/datadict_priv.inc
 --real_sleep 0.3
@@ -156,9 +183,9 @@ GRANT PROCESS ON *.* TO ddicttestuser1@'localhost' IDENTIFIED BY 'ddictpass';
 --echo ####################################################################################
 connection con100;
 SHOW GRANTS;
---replace_column 1 ID 6 TIME
+--replace_column 1 ID 3 HOST_NAME 6 TIME
 SHOW processlist;
---replace_column 1 ID 6 TIME
+--replace_column 1 ID 3 HOST_NAME 6 TIME
 SELECT * FROM information_schema.processlist;
 --real_sleep 0.3
 
@@ -168,9 +195,9 @@ SELECT * FROM information_schema.processlist;
 --echo ####################################################################################
 connect (con101,localhost,ddicttestuser1,ddictpass,information_schema);
 SHOW GRANTS;
---replace_column 1 ID 6 TIME
+--replace_column 1 ID 3 HOST_NAME 6 TIME
 SHOW processlist;
---replace_column 1 ID 6 TIME
+--replace_column 1 ID 3 HOST_NAME 6 TIME
 SELECT * FROM information_schema.processlist;
 --real_sleep 0.3
 
@@ -190,9 +217,9 @@ GRANT PROCESS ON *.* TO ''@'localhost';
 --echo ####################################################################################
 connect (anonymous1,localhost,'',,information_schema);
 SHOW GRANTS;
---replace_column 1 ID 6 TIME
+--replace_column 1 ID 3 HOST_NAME 6 TIME
 SHOW processlist;
---replace_column 1 ID 6 TIME
+--replace_column 1 ID 3 HOST_NAME 6 TIME
 SELECT * FROM information_schema.processlist;
 --real_sleep 0.3
 
@@ -212,15 +239,15 @@ connect (con102,localhost,ddicttestuser1,ddictpass,information_schema);
 --echo     ddicttestuser1 are visible.
 --echo ####################################################################################
 SHOW GRANTS;
---replace_column 1 ID 6 TIME
+--replace_column 1 ID 3 HOST_NAME 6 TIME
 SHOW processlist;
---replace_column 1 ID 6 TIME
+--replace_column 1 ID 3 HOST_NAME 6 TIME
 SELECT * FROM information_schema.processlist;
 --real_sleep 0.3
 
 
 --echo ####################################################################################
---echo 7 Revoke PROCESS privilege from anonymous user + disconnect ddicttestuser1
+--echo 7 Revoke PROCESS privilege from anonymous user
 --echo   connection default (user=root)
 --echo ####################################################################################
 connection default;
@@ -237,10 +264,10 @@ SHOW GRANTS FOR ''@'localhost';
 if ($fixed_bug_30395)
 {
 # Bug#30395 strange results after REVOKE PROCESS ON *.* FROM ...
---replace_column 1 ID 6 TIME
+--replace_column 1 ID 3 HOST_NAME 6 TIME
 SHOW processlist;
 }
---replace_column 1 ID 6 TIME
+--replace_column 1 ID 3 HOST_NAME 6 TIME
 SELECT * FROM information_schema.processlist;
 --real_sleep 0.3
 
@@ -259,9 +286,9 @@ connect (con103,localhost,ddicttestuser1,ddictpass,information_schema);
 --echo     Only the processes of ddicttestuser1 user are visible.
 --echo ####################################################################################
 SHOW GRANTS FOR 'ddicttestuser1'@'localhost';
---replace_column 1 ID 6 TIME
+--replace_column 1 ID 3 HOST_NAME 6 TIME
 SHOW processlist;
---replace_column 1 ID 6 TIME
+--replace_column 1 ID 3 HOST_NAME 6 TIME
 SELECT * FROM information_schema.processlist;
 --real_sleep 0.3
 
@@ -281,9 +308,9 @@ connect (con104,localhost,ddicttestuser1,ddictpass,information_schema);
 --echo     Only the processes of ddicttestuser1 are visible.
 --echo ####################################################################################
 SHOW GRANTS FOR 'ddicttestuser1'@'localhost';
---replace_column 1 ID 6 TIME
+--replace_column 1 ID 3 HOST_NAME 6 TIME
 SHOW processlist;
---replace_column 1 ID 6 TIME
+--replace_column 1 ID 3 HOST_NAME 6 TIME
 SELECT * FROM information_schema.processlist;
 --real_sleep 0.3
 
@@ -328,9 +355,9 @@ connect (con200,localhost,ddicttestuser2,ddictpass,information_schema);
 --echo      ddicttestuser2 has now the PROCESS privilege and sees all connections
 --echo ####################################################################################
 SHOW GRANTS FOR 'ddicttestuser2'@'localhost';
---replace_column 1 ID 6 TIME
+--replace_column 1 ID 3 HOST_NAME 6 TIME
 SHOW processlist;
---replace_column 1 ID 6 TIME
+--replace_column 1 ID 3 HOST_NAME 6 TIME
 SELECT * FROM information_schema.processlist;
 --real_sleep 0.3
 
@@ -349,9 +376,9 @@ connect (con201,localhost,ddicttestuser2,ddictpass,information_schema);
 --echo      ddicttestuser2 has no more the PROCESS privilege and can only see own connects
 --echo ####################################################################################
 SHOW GRANTS;
---replace_column 1 ID 6 TIME
+--replace_column 1 ID 3 HOST_NAME 6 TIME
 SHOW processlist;
---replace_column 1 ID 6 TIME
+--replace_column 1 ID 3 HOST_NAME 6 TIME
 SELECT * FROM information_schema.processlist;
 --real_sleep 0.3
 
@@ -372,9 +399,9 @@ connect (con107,localhost,ddicttestuser1,ddictpass,information_schema);
 SHOW GRANTS FOR 'ddicttestuser1'@'localhost';
 --error ER_ACCESS_DENIED_ERROR
 GRANT PROCESS ON *.* TO 'ddicttestuser2'@'localhost';
---replace_column 1 ID 6 TIME
+--replace_column 1 ID 3 HOST_NAME 6 TIME
 SHOW processlist;
---replace_column 1 ID 6 TIME
+--replace_column 1 ID 3 HOST_NAME 6 TIME
 SELECT * FROM information_schema.processlist;
 --real_sleep 0.3
 
@@ -396,9 +423,9 @@ connect (con108,localhost,ddicttestuser1,ddictpass,information_schema);
 --echo      Therefore the missing SELECT privilege does not affect SELECTs on PROCESSLIST.
 --echo ####################################################################################
 SHOW GRANTS FOR 'ddicttestuser1'@'localhost';
---replace_column 1 ID 6 TIME
+--replace_column 1 ID 3 HOST_NAME 6 TIME
 SHOW processlist;
---replace_column 1 ID 6 TIME
+--replace_column 1 ID 3 HOST_NAME 6 TIME
 SELECT * FROM information_schema.processlist;
 --real_sleep 0.3
 
diff --git a/mysql-test/suite/funcs_1/datadict/processlist_val.inc b/mysql-test/suite/funcs_1/datadict/processlist_val.inc
index 83b7d55d435..ee5347fa529 100644
--- a/mysql-test/suite/funcs_1/datadict/processlist_val.inc
+++ b/mysql-test/suite/funcs_1/datadict/processlist_val.inc
@@ -1,310 +1,466 @@
-########### suite/funcs_1/datadict/processlist_val.inc #################
-#                                                                      #
-# Testing of values within INFORMATION_SCHEMA.PROCESSLIST              #
-#                                                                      #
-# Ensure that the values fit to the current event of the connection    #
-# and especially that they change if a connection does nothing or      #
-# runs some SQL.                                                       #
-# Examples:                                                            #
-# - change the default database                                        #
-# - send some time no SQL command to the server                        #
-# - send a long running query                                          #
-#                                                                      #
-# Note(mleich):                                                        #
-# 1. Please inform me if this test fails because of timing problems.   #
-#    I tried to avoid instabilities but the values within the column   #
-#    TIME are very sensible to fluctuations of the machine load.       #
-#    I had to unify some TIME values with "--replace_result" in cases  #
-#    where they are too unstable.                                      #
-# 2. Storage engine variants of this test do not make sense.           #
-#    -  I_S tables use the MEMORY storage engine whenever possible.    #
-#    -  There are some I_S table which need column data types which    #
-#       are not supported by MEMORY. Example: LONGTEXT/BLOB            #
-#       MyISAM will be used for such tables.                           #
-#       The column PROCESSLIST.INFO is of data type LONGTEXT           #
-#           ----> MyISAM                                               #
-#    -  There is no impact of the GLOBAL(server) or SESSION default    #
-#       storage engine setting on the engine used for I_S tables.      #
-#       That means we cannot get NDB or InnoDB instead.                #
-# 3. The SHOW (FULL) PROCESSLIST command are for comparison.           #
-#    The main test target is INFORMATION_SCHEMA.PROCESSLIST !          #
-#                                                                      #
-# Creation:                                                            #
-# 2007-08-09 mleich Implement this test as part of                     #
-#                   WL#3982 Test information_schema.processlist        #
-# 2008-01-05 HHunger Changed time test and long statement test.        #
-#                    It could happen that the long statement test      #
-#                    fail due to timing problems.                      #          #                                                                      #
-########################################################################
+########### suite/funcs_1/datadict/processlist_val.inc #########################
+#                                                                              #
+# Testing of values within INFORMATION_SCHEMA.PROCESSLIST                      #
+#                                                                              #
+# Ensure that the values fit to the current state of the connection and        #
+# especially that they change if a connection does nothing or runs some SQL.   #
+# runs some SQL.                                                               #
+# Examples:                                                                    #
+# - change the default database                                                #
+# - send some period of time no SQL command to the server                      #
+# - send a long running query                                                  #
+#                                                                              #
+# Note(mleich):                                                                #
+# 1. Please inform me if this test fails because of timing problems.           #
+# 2. Storage engine variants of this test do not make sense.                   #
+#    -  I_S tables use the MEMORY storage engine whenever possible.            #
+#    -  There are some I_S tables which need column data types which are not   #
+#       supported by MEMORY. Example: LONGTEXT/BLOB                            #
+#       MyISAM will be used for such tables.                                   #
+#       The column PROCESSLIST.INFO is of data type LONGTEXT ----> MyISAM      #
+#    -  There is no impact of the GLOBAL(server) or SESSION default storage    #
+#       engine setting on the engine used for I_S tables.                      #
+#       That means we cannot get NDB or InnoDB instead.                        #
+# 3. The SHOW (FULL) PROCESSLIST command are for comparison.                   #
+#    The main test target is INFORMATION_SCHEMA.PROCESSLIST !                  #
+# 4. Attention:                                                                #
+#    The values of the PROCESSLIST columns HOST and TIME tend to cause         #
+#    problems and therefore their printing has to be suppressed.               #
+#    Examples of the exact values:                                             #
+#    HOST: 'localhost' (UNIX derivates)                                        #
+#          'localhost:<varying_port>' (WINDOWS)                                #
+#    TIME: In many cases within this test 0 seconds but if the testing box is  #
+#          overloaded we might get up to 2 seconds.                            #
+#    Solution:                                                                 #
+#          --replace_column 1 <ID> 3 <HOST_NAME> 6 <TIME>                      #
+# 5. How to debug the script?                                                  #
+#    <graphical diff tool>                          \                          #
+#        suite/funcs_1/datadict/processlist_val.inc \                          #
+#        <Result|Reject|Log file>                                              #
+#    I tweaked a lot of the script lines around "echo" so that you will get a  #
+#    lot of useful synchronisation.                                            #
+#                                                                              #
+# Creation:                                                                    #
+# 2007-08-09 mleich Implement this test as part of                             #
+#                   WL#3982 Test information_schema.processlist                #
+#                                                                              #
+# Last Modification:                                                           #
+# 2008-07-04 mleich Fix for                                                    #
+#                   Bug#37853 Test "funcs_1.processlist_val_ps" fails in       #
+#                             various ways                                     #
+#                       - issues with printing of port (Win only)              #
+#                       - too optimistic assumptions about timing              #
+#                   + corrections of logic in poll routines                    #
+#                   + minor improvements                                       #
+################################################################################
 
 # Basic preparations
---disable_abort_on_error
-DROP USER ddicttestuser1@'localhost';
---enable_abort_on_error
-CREATE USER ddicttestuser1@'localhost';
-GRANT ALL ON *.* TO ddicttestuser1@'localhost';
-REVOKE PROCESS ON *.* FROM ddicttestuser1@'localhost';
-SET PASSWORD FOR ddicttestuser1@'localhost' = PASSWORD('ddictpass');
+--error 0, ER_CANNOT_USER
+DROP USER test_user@'localhost';
+CREATE USER test_user@'localhost';
+GRANT ALL ON *.* TO test_user@'localhost';
+REVOKE PROCESS ON *.* FROM test_user@'localhost';
+SET PASSWORD FOR test_user@'localhost' = PASSWORD('ddictpass');
 --disable_warnings
 DROP TABLE IF EXISTS test.t1;
 --enable_warnings
 CREATE TABLE test.t1 (f1 BIGINT);
+USE test;
 
 
+echo
 # Show the definition of the PROCESSLIST table
 #--------------------------------------------------------------------------
+;
 --replace_result ENGINE=MyISAM "" ENGINE=MARIA "" " PAGE_CHECKSUM=1" "" " PAGE_CHECKSUM=0" ""
 SHOW CREATE TABLE INFORMATION_SCHEMA.PROCESSLIST;
 
-
-# Ensure that the values follow the changing default database and statement
+echo
+# Ensure that the information about the own connection is correct.
 #--------------------------------------------------------------------------
-# - We have now exact one connection. -> One record
-SELECT COUNT(*) FROM INFORMATION_SCHEMA.PROCESSLIST;
-# - Other expected values
+;
+# Expected values
 #   - USER = 'root'
-#   - HOST = 'localhost'
+#   - HOST (printed value is unified), the exact values are often like
+#        UNIX: 'localhost'
+#        WIN: 'localhost:<some port>'
 #   - DB = 'test'
-#   - Command = 'Query'
-#   - TIME = 0, I hope the testing machines are all time fast enough
-#   - State IS NULL
+#   - Command IN (no protocol -> 'Query', ps-protocol -> 'Execute')
+#   - TIME (printed value will be unified), the exact values are like
+#          "normal" load: 0 (seconds)
+#          "heavy" load: 1 or more (seconds)
+#   - State 'executing'
 #   - INFO must contain the corresponding SHOW/SELECT PROCESSLIST
-USE test;
---replace_column 1 <ID>
+#
+# 1. Just dump what we get
+--replace_column 1 <ID> 3 <HOST_NAME> 6 <TIME>
 SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;
---replace_column 1 <ID>
+--replace_column 1 <ID> 3 <HOST_NAME> 6 <TIME>
 SHOW FULL PROCESSLIST;
 #
-# Expect to see now DB = 'information_schema'
+# Determine the connection id of the current connection (default)
+SET @default_id = CONNECTION_ID();
+#
+# 2. There must be exact one connection with @default_id;
+SELECT COUNT(*)  = 1 AS "Expect exact one connection with this id"
+FROM INFORMATION_SCHEMA.PROCESSLIST WHERE ID = @default_id;
+#
+# 3. Check the remaining stuff
+SELECT COUNT(*)  = 1 AS "Expect 1"
+FROM INFORMATION_SCHEMA.PROCESSLIST WHERE ID = @default_id
+     AND USER = 'root' AND DB = 'test' AND Command IN('Query','Execute')
+     AND State = 'executing';
+#
+# 4. Change the DB
 USE information_schema;
---replace_column 1 <ID> 6 <TIME>
-SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;
---replace_column 1 <ID> 6 <TIME>
-SHOW FULL PROCESSLIST;
+SELECT COUNT(*)  = 1 AS "Is the DB correct?"
+FROM INFORMATION_SCHEMA.PROCESSLIST
+WHERE ID = @default_id AND DB = 'information_schema';
 #
-# Expect to see now INFO = 'SELECT INFO FROM INFORMATION_SCHEMA.PROCESSLIST;'
-SELECT INFO FROM INFORMATION_SCHEMA.PROCESSLIST;
+# 5. Change the statement
+let $my_statement =
+SELECT @my_info := INFO FROM INFORMATION_SCHEMA.PROCESSLIST
+WHERE ID = @default_id;
+eval $my_statement;
+eval
+SELECT @my_info = '$my_statement'
+    AS 'Is the content of PROCESSLIST.INFO correct?';
+#
+# 6. TIME must have a reasonable value
+SELECT COUNT(*) = 1 AS "Has TIME a reasonable value?"
+FROM INFORMATION_SCHEMA.PROCESSLIST
+WHERE ID = @default_id AND 0 <= TIME < 10;
 
 
-# Ensure that the values for an additional connection are correct
+echo
+# Ensure that the information about an inactive connection is correct.
 #--------------------------------------------------------------------------
-SELECT ID INTO @my_proclist_id FROM INFORMATION_SCHEMA.PROCESSLIST;
---echo
---echo ----- establish connection ddicttestuser1 (user = ddicttestuser1) -----
-connect (ddicttestuser1,localhost,ddicttestuser1,ddictpass,information_schema);
+;
+echo
+# ----- establish connection con1 (user = test_user) -----
+;
+connect (con1,localhost,test_user,ddictpass,information_schema);
 #
---echo
---echo ----- switch to connection default (user = root) -----
+echo
+# ----- switch to connection default (user = root) -----
+;
 connection default;
-# - We have now a second connection.
-#   First working phase for this connection is "Connect".
-#   This is a very short phase and the likelihood to meet it is
-#   - nearly zero on average boxes with low parallel load
-#   - around some percent on weak or overloaded boxes
-#     (Bug#32153 Status output differs - scheduling ?)
-#   Therefore we poll till we reach the long lasting phase with:
-#   - USER = ddicttestuser1
-#   - HOST = 'localhost'
+# We have now a second connection.
+# First working phase for the new connection is with Command = 'Connect'.
+# This is a very short phase and the likelihood to meet it is
+# - nearly zero on average boxes with low parallel load
+# - around some percent on weak or overloaded boxes
+#   (Bug#32153 Status output differs - scheduling ?)
+# Therefore we do not try to catch this state.
+# We poll till we reach the long lasting phase with Command = 'Sleep'.
+#   - USER = 'test_user'
 #   - DB = 'information_schema'
 #   - Command = 'Sleep'
-#   - TIME >= 0  Overloaded boxes can cause that we do not hit TIME = 0.
-#   - State IS NULL
-#   - INFO must be empty
-#
-let $wait_condition= SELECT id,user,host,db,command,@time:=time,state,info FROM INFORMATION_SCHEMA.PROCESSLIST
-        WHERE COMMAND = 'Sleep' AND TIME > 0;
+#   - State is empty
+#   - INFO IS NULL
+echo
+# Poll till the connection con1 is in state COMMAND = 'Sleep'.
+;
+let $wait_timeout= 10;
+let $wait_condition= SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST
+                     WHERE COMMAND = 'Sleep' AND USER = 'test_user';
 --source include/wait_condition.inc
+# 1. Just dump what we get
+--replace_column 1 <ID> 3 <HOST_NAME> 6 <TIME>
+SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;
+--replace_column 1 <ID> 3 <HOST_NAME> 6 <TIME>
+SHOW FULL PROCESSLIST;
 #
-# Expect to hit TIME > 1.
-SELECT @time > 0;
+# Pull ID and TIME of the second connection
+SELECT ID,TIME INTO @test_user_con1_id,@time FROM INFORMATION_SCHEMA.PROCESSLIST
+WHERE COMMAND = 'Sleep' AND USER = 'test_user';
 #
---echo # Sleep some time
-# The value of TIME must increase and reach 2 after some sleeps.
-let $wait_timeout= 4;
-let $wait_condition= SELECT id,user,host,db,command,@time2:=time,state,info FROM INFORMATION_SCHEMA.PROCESSLIST
-        WHERE TIME > @time;
---source include/wait_condition.inc
-# 
-# Expect to hit TIME > @time
-SELECT @time < @time2;
+# 2. The second connection must (behaviour at least since 2007) have an
+#    ID = ID_of_previous_connection + 1
+SELECT @test_user_con1_id = @default_id + 1
+    AS "Did we got the next higher PROCESSLIST ID?";
+#
+# 3. TIME must have a reasonable value
+SELECT 0 <=  @time < 10 AS "Has TIME a reasonable value?";
 #
+# 4. HOST must be for both connections similar (varying port on Win)
+SELECT COUNT(*) = 2 AS "Is HOST LIKE 'localhost%'?"
+FROM INFORMATION_SCHEMA.PROCESSLIST
+WHERE HOST LIKE 'localhost%';
 #
-# The second connection must have an ID = my ID + 1;
-SELECT ID = @my_proclist_id + 1 FROM INFORMATION_SCHEMA.PROCESSLIST
-WHERE USER = 'ddicttestuser1';
+# 5. Check the remaining stuff
+SELECT COUNT(*)  = 1 AS "Expect 1"
+FROM INFORMATION_SCHEMA.PROCESSLIST WHERE ID = @test_user_con1_id
+     AND USER = 'test_user' AND DB = 'information_schema'
+     AND Command = 'Sleep' AND State = '' AND INFO IS NULL;
+#
+# 6. Check that TIME increases
+let $wait_timeout= 10;
+let $wait_condition= SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST
+                     WHERE COMMAND = 'Sleep' AND USER = 'test_user'
+                       AND TIME > @time;
+--source include/wait_condition.inc
 
 
-# Ensure that the user ddicttestuser1 sees only connections with his username
+echo
+# Ensure that the user test_user sees only connections with his username
 # because he has not the PROCESS privilege.
 #----------------------------------------------------------------------------
---echo
---echo ----- switch to connection ddicttestuser1 (user = ddicttestuser1) -----
-connection ddicttestuser1;
---replace_column 1 <ID> 6 <TIME>
+;
+echo
+# ----- switch to connection con1 (user = test_user) -----
+;
+connection con1;
+--replace_column 1 <ID> 3 <HOST_NAME> 6 <TIME>
 SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;
---replace_column 1 <ID> 6 <TIME>
+--replace_column 1 <ID> 3 <HOST_NAME> 6 <TIME>
 SHOW FULL PROCESSLIST;
 
 
-# Ensure that the user ddicttestuser1 sees all connections with his username.
+echo
+# Ensure that the user test_user sees all connections with his username.
 #----------------------------------------------------------------------------
---echo
---echo ----- establish connection con2 (user = ddicttestuser1) ------
-connect (con2,localhost,ddicttestuser1,ddictpass,information_schema);
-#
-# If the testing box is under heavy load we might see connection ddicttestuser1
-# within the short phase INFO = 'SHOW FULL PROCESSLIST' and STATE = 'Writing to net'.
-let $wait_condition= SELECT COUNT(*) FROM INFORMATION_SCHEMA.PROCESSLIST
-        WHERE COMMAND = 'Sleep';
+;
+echo
+----- establish connection con2 (user = test_user) ------
+;
+connect (con2,localhost,test_user,ddictpass,information_schema);
+echo
+# ----- switch to connection default (user = root) -----
+;
+connection default;
+# If the testing box is under heavy load we might see within some of the
+# next queries connection
+# con2 with Command = 'Connect'
+# con1 with INFO = 'SHOW FULL PROCESSLIST' and STATE = 'Writing to net'
+# Both phases are too short to be checked.
+echo
+# Poll till all connections of 'test_user' are in a state with COMMAND = 'Sleep'
+;
+let $wait_condition= SELECT COUNT(*) = 2 FROM INFORMATION_SCHEMA.PROCESSLIST
+        WHERE USER = 'test_user' AND COMMAND = 'Sleep';
 --source include/wait_condition.inc
---replace_column 1 <ID> 6 <TIME>
+echo
+# ----- switch to connection con2 (user = test_user) -----
+;
+connection con2;
+# Just dump what we get
+--replace_column 1 <ID> 3 <HOST_NAME> 6 <TIME>
 SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;
---replace_column 1 <ID> 6 <TIME>
+--replace_column 1 <ID> 3 <HOST_NAME> 6 <TIME>
 SHOW FULL PROCESSLIST;
 #
---echo
---echo ----- switch to connection default (user = root) -----
+echo
+# ----- switch to connection default (user = root) -----
+;
 connection default;
---echo ----- close connection con2 -----
-disconnect con2;
+# Pull the ID of con2, we will need it later
+SELECT ID INTO @test_user_con2_id FROM INFORMATION_SCHEMA.PROCESSLIST
+WHERE ID <> @test_user_con1_id
+  AND USER = 'test_user' AND DB = 'information_schema';
 
 
-# Ensure we see correct values if a connection is during work
+echo
+# Ensure we get correct information about a connection during work
 #----------------------------------------------------------------------------
---echo
---echo ----- switch to connection ddicttestuser1 (user = ddicttestuser1) -----
-connection ddicttestuser1;
-# "Organise" a long running command to be observed by the root user
---echo
---echo
---echo # Send a long enough running statement to the server, but do not
---echo # wait till the result comes back. We will pull this later.
-send SELECT sleep(2.5),'Command time';
+;
+echo
+# ----- switch to connection con2 (user = test_user) -----
+;
+connection con2;
+# "Organise" a long running command to be observed by the root user.
+echo
+# Send a long enough running statement to the server, but do not
+# wait till the result comes back.
+;
+# Worst case scenario (=high I/O load on testing box):
+# - My experience:
+#   Up to 2 seconds runtime per SELECT ... FROM INFORMATION_SCHEMA.PROCESSLIST
+#   in rare cases.
+# - The following sequence contains ~ 4 of such SELECTs
+# Therefore we sleep 10 seconds.
+let $sleep_command =
+SELECT sleep(10), 17;
+send;
+eval $sleep_command;
 #
---echo
---echo ----- switch to connection default (user = root) -----
+echo
+# ----- switch to connection default (user = root) -----
+;
 connection default;
-# Sleep a bit so that we can be nearly sure that we see the SELECT of ddicttestuser1.
-# Expect to see within the processlist the other connection just during statement
-# execution.
-#   - USER = ddicttestuser1
-#   - HOST = 'localhost'
+echo
+# Poll till connection con2 is in state 'User sleep'.
+;
+# Expect to see within the processlist the other connection just during
+# statement execution.
+#   - USER = 'test_user'
 #   - DB = 'information_schema'
 #   - Command = 'Query'(run without --ps-protocol)/'Execute' (run --ps-protocol)
-#   - TIME = 1, Attention: check with TIME = 0 is not stable
-#   - State IS NULL
-#   - INFO = "SELECT sleep(2.5),'Command time'"
---echo # Sleep some time
-# The command must be after some time in work by the server.
-# So poll till INFO is no more NULL and TIME > 0.
-let $wait_condition= SELECT id,user,host,db,command,@time:=time,state,info FROM INFORMATION_SCHEMA.PROCESSLIST
-        WHERE INFO IS NOT NULL AND TIME > 0;
+#   - TIME >= 0
+#   - State = 'User sleep'
+#   - INFO = $sleep_command
+let $wait_condition=
+SELECT COUNT(*) FROM INFORMATION_SCHEMA.PROCESSLIST
+WHERE ID = @test_user_con2_id AND Command IN('Query','Execute')
+  AND State = 'User sleep' AND INFO IS NOT NULL ;
 --source include/wait_condition.inc
+# 1. Just dump what we get
+--replace_column 1 <ID> 3 <HOST_NAME> 6 <TIME>
+SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;
+--replace_column 1 <ID> 3 <HOST_NAME> 6 <TIME>
+SHOW FULL PROCESSLIST;
 #
-# Expect to see TIME > 0;
-SELECT @time > 0;
-#
---echo # Sleep some time
-# The value of TIME must increase and reach 2 after some sleeps.
-let $wait_timeout= 2;
-let $wait_condition= SELECT id,user,host,db,command,@time2:=time,state,info FROM INFORMATION_SCHEMA.PROCESSLIST
-        WHERE TIME > @time;
+# Pull some information about the connection con2
+SELECT STATE, TIME, INFO INTO @state, @time, @info
+FROM INFORMATION_SCHEMA.PROCESSLIST
+WHERE ID = @test_user_con2_id;
+# 2. TIME must have a reasonable value
+SELECT 0 <= @time < 10 AS "Has TIME a reasonable value?";
+# 3. STATE must be 'User sleep'
+SELECT @state = 'User sleep' AS "Has STATE the expected value?";
+# 4. INFO must fit
+eval SELECT @info = '$sleep_command' AS "Has INFO the expected value?";
+# 5. Check that TIME increases
+let $wait_timeout= 10;
+let $wait_condition= SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST
+    WHERE ID = @test_user_con2_id AND INFO IS NOT NULL AND TIME > @time;
 --source include/wait_condition.inc
-#
-# Expect to see @time < @time2
-SELECT @time < @time2;
-#
---echo
---echo ----- switch to connection ddicttestuser1 (user = ddicttestuser1) -----
-connection ddicttestuser1;
---echo # Pull("reap") the result set from the statement executed with "send".
+echo
+# ----- switch to connection con2 (user = testuser) -----
+;
+connection con2;
+echo
+# Pull("reap") the result set from the statement executed with "send".
+;
 reap;
+echo
+# ----- switch to connection default (user = root) -----
+;
+connection default;
+echo
+# Poll till all connections of 'test_user' are in a state with COMMAND = 'Sleep'
+;
+let $wait_timeout= 10;
+let $wait_condition= SELECT COUNT(*) = 2 FROM INFORMATION_SCHEMA.PROCESSLIST
+                     WHERE COMMAND = 'Sleep' AND USER = 'test_user';
+--source include/wait_condition.inc
 
 
-# Ensure that SHOW/SELECT processlist can handle extreme long commands
+echo
+# Ensure that we see that a connection "hangs" when colliding with a
+# WRITE TABLE LOCK
 #----------------------------------------------------------------------------
---echo
---echo
---echo # Send a long (21 KB code and runtime = 2 seconds) statement to the server,
---echo # but do not wait till the result comes back. We will pull this later.
-# Please do not change the next statement.
-# The annoying long line is intended. Many short lines would be a different test.
-send SELECT sleep(2),'BEGIN this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.END' AS "my_monster_statement";
+;
+LOCK TABLE test.t1 WRITE;
 #
---echo
---echo ----- switch to connection default (user = root) -----
+echo
+# ----- switch to connection con2 (user = test_user) -----
+;
+connection con2;
+echo
+# Send a statement to the server, but do not wait till the result
+# comes back. We will pull this later.
+;
+send
+SELECT COUNT(*) FROM test.t1;
+echo
+# ----- switch to connection default (user = root) -----
+;
 connection default;
---echo # Sleep some time
-# The command must be after some time in work by the server.
-# There is a short phase with STATE IS NULL followed by a longer phase
-# with STATE = 'executing'.
-# So poll till INFO is no more NULL AND STATE = 'executing'.
-let $wait_timeout= 9;
+echo
+# Poll till INFO is no more NULL and State = 'Locked'.
+;
 let $wait_condition= SELECT COUNT(*) FROM INFORMATION_SCHEMA.PROCESSLIST
-       WHERE INFO IS NOT NULL AND STATE = 'executing';
+        WHERE INFO IS NOT NULL AND STATE = 'Locked';
 --source include/wait_condition.inc
 #
-# Expect to see that SELECT/SHOW PROCESSLIST can handle my statement monster.
---replace_column 1 <ID> 5 <COMMAND> 6 <TIME> 7 <STATE>
+# Expect to see the state 'Locked' for the third connection because the SELECT
+# collides with the WRITE TABLE LOCK.
+--replace_column 1 <ID> 3 <HOST_NAME> 6 <TIME>
 SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;
---replace_column 1 <ID> 5 <COMMAND> 6 <TIME> 7 <STATE>
-SHOW FULL PROCESSLIST;
-# SHOW PROCESSLIST truncates INFO after 100 characters.
---replace_column 1 <ID> 5 <COMMAND> 6 <TIME> 7 <STATE>
-SHOW PROCESSLIST;
---echo
---echo ----- switch to connection ddicttestuser1 (user = ddicttestuser1) -----
-connection ddicttestuser1;
---echo # Pull("reap") the result set from the monster statement executed with "send".
+UNLOCK TABLES;
+#
+echo
+# ----- switch to connection con2 (user = test_user) -----
+;
+connection con2;
+echo
+# Pull("reap") the result set from the statement executed with "send".
+;
 reap;
 
-# Ensure that we see that a connection "hangs" when colliding with a
-# WRITE TABLE LOCK
+
+echo
+# Ensure that SHOW/SELECT processlist can handle extreme long commands
 #----------------------------------------------------------------------------
---echo
---echo ----- switch to connection default (user = root) -----
+;
+# We do not want to waste runtime, therefore we run the following test based
+# on "Lock collision" and not with some "sleep(10)".
+echo
+# ----- switch to connection default (user = root) -----
+;
 connection default;
 LOCK TABLE test.t1 WRITE;
 #
---echo
---echo ----- switch to connection ddicttestuser1 (user = ddicttestuser1) -----
-connection ddicttestuser1;
---echo # Send a statement to the server, but do not wait till the result
---echo # comes back. We will pull this later.
-send SELECT COUNT(*) FROM test.t1;
-#
---echo
---echo ----- switch to connection default (user = root) -----
+echo
+# ----- switch to connection con2 (user = test_user) -----
+;
+connection con2;
+echo
+# Send a long (~20 KB code) statement to the server, but do not wait
+# till the result comes back. We will pull this later.
+;
+let $string=
+`SELECT CONCAT('BEGIN-',
+        REPEAT('This is the representative of a very long statement.',400),
+        '-END')`;
+let $my_statement =
+SELECT count(*),'$string' AS "Long string" FROM test.t1;
+send;
+eval $my_statement;
+echo
+# ----- switch to connection default (user = root) -----
+;
 connection default;
---echo # Sleep some time
-# The command must be after some time in work by the server.
-# So poll till INFO is no more NULL.
-let $wait_timeout= 4;
+echo
+# Poll till INFO is no more NULL and State = 'Locked'.
+;
 let $wait_condition= SELECT COUNT(*) FROM INFORMATION_SCHEMA.PROCESSLIST
         WHERE INFO IS NOT NULL AND STATE = 'Locked';
 --source include/wait_condition.inc
-#
-# Expect to see the state 'Locked' for the second connection because the SELECT
-# collides with the WRITE TABLE LOCK.
---replace_column 1 <ID> 6 <TIME>
+echo
+# Expect result:
+# Statement                                      Content of INFO
+# SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST   Complete statement
+# SHOW FULL PROCESSLIST                          Complete statement
+# SHOW PROCESSLIST                               statement truncated after 100 char
+;
+--replace_column 1 <ID> 3 <HOST_NAME> 5 <COMMAND> 6 <TIME> 7 <STATE>
 SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;
---replace_column 1 <ID> 6 <TIME>
+--replace_column 1 <ID> 3 <HOST_NAME> 5 <COMMAND> 6 <TIME> 7 <STATE>
 SHOW FULL PROCESSLIST;
+--replace_column 1 <ID> 3 <HOST_NAME> 5 <COMMAND> 6 <TIME> 7 <STATE>
+SHOW PROCESSLIST;
 UNLOCK TABLES;
-#
---echo
---echo ----- switch to connection ddicttestuser1 (user = ddicttestuser1) -----
-connection ddicttestuser1;
---echo # Pull("reap") the result set from the statement executed with "send".
+echo
+# ----- switch to connection con2 (user = test_user) -----
+;
+connection con2;
+echo
+# Pull("reap") the result set from the monster statement executed with "send".
+;
 reap;
 
 
 # Cleanup
---echo
---echo ----- switch to connection default (user = root) -----
+echo
+# ----- switch to connection default (user = root) -----
+;
 connection default;
---echo
---echo ----- close connection ddicttestuser1 -----
-disconnect ddicttestuser1;
-DROP USER ddicttestuser1@'localhost';
+echo
+----- disconnect con1 and con2 -----
+;
+disconnect con1;
+disconnect con2;
+DROP USER test_user@'localhost';
 DROP TABLE test.t1;
diff --git a/mysql-test/suite/funcs_1/datadict/tables.inc b/mysql-test/suite/funcs_1/datadict/tables.inc
new file mode 100644
index 00000000000..5aa072d184c
--- /dev/null
+++ b/mysql-test/suite/funcs_1/datadict/tables.inc
@@ -0,0 +1,55 @@
+# suite/funcs_1/datadict/tables.inc
+#
+# Auxiliary script to be sourced by
+#    is_tables_<engine>.test
+#
+# The variable $engine_type has to be assigned before sourcing ths script.
+#
+# Author:
+# 2008-06-04 mleich Create this script based on older scripts and new code.
+#
+
+# Just have some tables within different databases.
+--disable_warnings
+DROP DATABASE IF EXISTS test1;
+DROP DATABASE IF EXISTS test2;
+--enable_warnings
+CREATE DATABASE test1;
+CREATE DATABASE test2;
+
+--replace_result $engine_type <engine_to_be_used>
+eval CREATE TABLE test1.t1  (f1 VARCHAR(20)) ENGINE = $engine_type;
+--replace_result $engine_type <engine_to_be_used>
+eval CREATE TABLE test1.t2  (f1 VARCHAR(20)) ENGINE = $engine_type;
+--replace_result $engine_type <engine_to_be_used>
+eval CREATE TABLE test2.t1 (f1 VARCHAR(20)) ENGINE = $engine_type;
+
+--source suite/funcs_1/datadict/tables2.inc
+SHOW TABLES FROM test1;
+SHOW TABLES FROM test2;
+
+# Create a low privileged user.
+# Note: The database db_datadict is just a "home" for the low privileged user
+#       and not in the focus of testing.
+--error 0,ER_CANNOT_USER
+DROP   USER testuser1@localhost;
+CREATE USER testuser1@localhost;
+GRANT SELECT ON test1.* TO testuser1@localhost;
+
+--echo # Establish connection testuser1 (user=testuser1)
+--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
+connect (testuser1,localhost,testuser1,,test1);
+--source suite/funcs_1/datadict/tables2.inc
+SHOW TABLES FROM test1;
+# The lowprivileged user testuser1 will get here an error.
+--disable_abort_on_error
+SHOW TABLES FROM test2;
+--enable_abort_on_error
+
+--echo # Switch to connection default and close connection testuser1
+connection default;
+disconnect testuser1;
+DROP USER testuser1@localhost;
+
+DROP DATABASE test1;
+DROP DATABASE test2;
diff --git a/mysql-test/suite/funcs_1/datadict/tables1.inc b/mysql-test/suite/funcs_1/datadict/tables1.inc
index a03304028f6..2e054a9dcfb 100644
--- a/mysql-test/suite/funcs_1/datadict/tables1.inc
+++ b/mysql-test/suite/funcs_1/datadict/tables1.inc
@@ -2,8 +2,8 @@
 #
 # Auxiliary script to be sourced by
 #    is_tables_mysql.test
+#    is_tables_mysql_embedded.test
 #    is_tables_is.test
-#    is_tables_<engine>.test
 #
 # Author:
 # 2008-01-23 mleich WL#4203 Reorganize and fix the data dictionary tests of
@@ -12,28 +12,27 @@
 #
 
 --disable_warnings
-DROP DATABASE IF EXISTS db_datadict;
+DROP DATABASE IF EXISTS test1;
 --enable_warnings
-CREATE DATABASE db_datadict;
+CREATE DATABASE test1;
 
 --source suite/funcs_1/datadict/tables2.inc
 
 # Create a low privileged user.
-# Note: The database db_datadict is just a "home" for the low privileged user
+# Note: The database test1 is just a "home" for the low privileged user
 #       and not in the focus of testing.
 --error 0,ER_CANNOT_USER
 DROP   USER testuser1@localhost;
 CREATE USER testuser1@localhost;
-GRANT SELECT ON db_datadict.* TO testuser1@localhost;
+GRANT SELECT ON test1.* TO testuser1@localhost;
 
 --echo # Establish connection testuser1 (user=testuser1)
 --replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
-connect (testuser1,localhost,testuser1,,db_datadict);
+connect (testuser1,localhost,testuser1,,test1);
 --source suite/funcs_1/datadict/tables2.inc
 
 --echo # Switch to connection default and close connection testuser1
 connection default;
 disconnect testuser1;
 DROP USER testuser1@localhost;
-DROP DATABASE db_datadict;
-
+DROP DATABASE test1;
diff --git a/mysql-test/suite/funcs_1/datadict/tables2.inc b/mysql-test/suite/funcs_1/datadict/tables2.inc
index 5606ea827f0..f9bb296eeaa 100644
--- a/mysql-test/suite/funcs_1/datadict/tables2.inc
+++ b/mysql-test/suite/funcs_1/datadict/tables2.inc
@@ -29,6 +29,8 @@
 let $innodb_pattern = 'InnoDB free';
 let $ndb_pattern    = 'number_of_replicas';
 --vertical_results
+# We do not unify the engine name here, because the rowformat is
+# specific to the engine.
 --replace_result Dynamic DYNAMIC_OR_PAGE Page DYNAMIC_OR_PAGE MyISAM MYISAM_OR_MARIA MARIA MYISAM_OR_MARIA
 --replace_column  8 "#TBLR#" 9 "#ARL#" 10 "#DL#" 11 "#MDL#" 12 "#IL#" 13 "#DF#" 15 "#CRT#" 16 "#UT#" 17 "#CT#" 20 "#CO#" 21 "#TC#"
 eval
@@ -45,4 +47,3 @@ FROM information_schema.tables
 $my_where
 ORDER BY table_schema,table_name;
 --horizontal_results
-