diff options
Diffstat (limited to 'mysql-test/suite/funcs_1/t')
93 files changed, 6179 insertions, 907 deletions
diff --git a/mysql-test/suite/funcs_1/t/charset_collation_1.test b/mysql-test/suite/funcs_1/t/charset_collation_1.test new file mode 100644 index 00000000000..7415220455e --- /dev/null +++ b/mysql-test/suite/funcs_1/t/charset_collation_1.test @@ -0,0 +1,30 @@ +# Tests checking the content of the information_schema tables +# character_sets +# collations +# collation_character_set_applicability +# +# Content variant 1 which should fit to +# Enterprise or Classic builds (binaries provided by MySQL) +# Pushbuilds +# Source builds without "max" +# +# Please read suite/funcs_1/datadict/charset_collation.inc for +# additional information. +# +# Created: +# 2007-12-18 mleich - remove the unstable character_set/collation subtests +# from include/datadict-master.inc +# - create this new test +# + +if (`SELECT EXISTS (SELECT 1 FROM information_schema.collations + WHERE collation_name = 'utf8_general_cs') + OR ( @@version_comment NOT LIKE '%Source%' + AND @@version_comment NOT LIKE '%Enterprise%' + AND @@version_comment NOT LIKE '%Classic%' + AND @@version_comment NOT LIKE '%Pushbuild%')`) +{ + skip Test needs Enterprise, Classic , Pushbuild or Source-without-max build; +} + +--source suite/funcs_1/datadict/charset_collation.inc diff --git a/mysql-test/suite/funcs_1/t/charset_collation_2.test b/mysql-test/suite/funcs_1/t/charset_collation_2.test new file mode 100644 index 00000000000..d4924953b7d --- /dev/null +++ b/mysql-test/suite/funcs_1/t/charset_collation_2.test @@ -0,0 +1,24 @@ +# Tests checking the content of the information_schema tables +# character_sets +# collations +# collation_character_set_applicability +# +# Content variant 2 (compile from source with "max") +# +# Please read suite/funcs_1/datadict/charset_collation.inc for +# additional information. +# +# Created: +# 2007-12-18 mleich - remove the unstable character_set/collation subtests +# from include/datadict-master.inc +# - create this new test +# + +if (`SELECT @@version_comment NOT LIKE '%Source%' + OR NOT EXISTS (SELECT 1 FROM information_schema.collations + WHERE collation_name = 'utf8_general_cs')`) +{ + skip Test needs Source build with "max"; +} + +--source suite/funcs_1/datadict/charset_collation.inc diff --git a/mysql-test/suite/funcs_1/t/charset_collation_3.test b/mysql-test/suite/funcs_1/t/charset_collation_3.test new file mode 100644 index 00000000000..0701b96896f --- /dev/null +++ b/mysql-test/suite/funcs_1/t/charset_collation_3.test @@ -0,0 +1,24 @@ +# Tests checking the content of the information_schema tables +# character_sets +# collations +# collation_character_set_applicability +# +# Content variant 3 which should fit to +# Community and Cluster builds (binaries provided by MySQL) +# +# Please read suite/funcs_1/datadict/charset_collation.inc for +# additional information. +# +# Created: +# 2007-12-18 mleich - remove the unstable character_set/collation subtests +# from include/datadict-master.inc +# - create this new test +# + +if (`SELECT @@version_comment NOT LIKE '%Community%' + AND @@version_comment NOT LIKE '%Cluster%'`) +{ + skip Test needs Community or Cluster build; +} + +--source suite/funcs_1/datadict/charset_collation.inc diff --git a/mysql-test/suite/funcs_1/t/datadict_help_tables_build.test b/mysql-test/suite/funcs_1/t/datadict_help_tables_build.test deleted file mode 100644 index 370fcf0375c..00000000000 --- a/mysql-test/suite/funcs_1/t/datadict_help_tables_build.test +++ /dev/null @@ -1,73 +0,0 @@ -###### suite/funcs_1/t/datadict_help_tables_dev.test ##### -# -# Check the information about the help tables within -# INFORMATION_SCHEMA.TABLES/INFORMATION_SCHEMA.STATISTICS -# -# Variant for use during build tests (non empty help tables) -# -# Creation: -# 2007-08-25 mleich Add this test as compensation for the -# checks removed within datadict_master.inc. -# - -let $c_help_category= `SELECT COUNT(*) FROM mysql.help_category`; -let $c_help_keyword= `SELECT COUNT(*) FROM mysql.help_keyword`; -let $c_help_relation= `SELECT COUNT(*) FROM mysql.help_relation`; -let $c_help_topic= `SELECT COUNT(*) FROM mysql.help_topic`; - -if (`SELECT $c_help_category + $c_help_keyword + $c_help_relation - + $c_help_topic = 0`) -{ - --skip # Test requires non empty help tables = Build test configuration -} - -# We reach this point when we run on a configuration with at least one -# non empty help table. -# 2007-08 MySQL 5.0 row count of the help tables -# help_category help_keyword help_relation help_topic -# 36 395 809 466 -# Let's assume for all help tables that their content never dramatic -# shrinks and do some plausibility checks. -let $limit_help_category = 30; -let $limit_help_keyword = 320; -let $limit_help_relation = 640; -let $limit_help_topic = 380; -if (`SELECT $c_help_category < $limit_help_category - OR $c_help_keyword < $limit_help_keyword - OR $c_help_relation < $limit_help_relation - OR $c_help_topic < $limit_help_topic`) -{ - --echo # The row count within the help tables is unexepected small. - SELECT COUNT(*), 'exepected: >= $limit_help_category' FROM mysql.help_category; - SELECT COUNT(*), 'exepected: >= $limit_help_keyword' FROM mysql.help_keyword; - SELECT COUNT(*), 'exepected: >= $limit_help_relation' FROM mysql.help_relation; - SELECT COUNT(*), 'exepected: >= $limit_help_topic' FROM mysql.help_topic; - --echo # Either the current help table content (build problem? or - --echo # the expected minimum row count within this script is wrong. - --echo # Abort - exit; -} - -# Enforce a static number of rows within the help tables. -let $limit= `SELECT $c_help_category - $limit_help_category`; ---replace_result $limit <number> -eval DELETE FROM mysql.help_category LIMIT $limit; -# -let $limit= `SELECT $c_help_keyword - $limit_help_keyword`; ---replace_result $limit <number> -eval DELETE FROM mysql.help_keyword LIMIT $limit; -# -let $limit= `SELECT $c_help_relation - $limit_help_relation`; ---replace_result $limit <number> -eval DELETE FROM mysql.help_relation LIMIT $limit; -# -let $limit= `SELECT $c_help_topic - $limit_help_topic`; ---replace_result $limit <number> -eval DELETE FROM mysql.help_topic LIMIT $limit; - - ---replace_column 9 "#ARL#" 10 "#DL#" 11 "#MDL#" 12 "#IL#" 13 "#DF#" 15 "YYYY-MM-DD hh:mm:ss" 16 "YYYY-MM-DD hh:mm:ss" 17 "YYYY-MM-DD hh:mm:ss" -SELECT * FROM INFORMATION_SCHEMA.TABLES -WHERE TABLE_SCHEMA = 'mysql' AND TABLE_NAME LIKE 'help_%'; -SELECT * FROM INFORMATION_SCHEMA.STATISTICS -WHERE TABLE_SCHEMA = 'mysql' AND TABLE_NAME LIKE 'help_%'; diff --git a/mysql-test/suite/funcs_1/t/datadict_help_tables_dev.test b/mysql-test/suite/funcs_1/t/datadict_help_tables_dev.test deleted file mode 100644 index 3342fbca4be..00000000000 --- a/mysql-test/suite/funcs_1/t/datadict_help_tables_dev.test +++ /dev/null @@ -1,27 +0,0 @@ -###### suite/funcs_1/t/datadict_help_tables_dev.test ##### -# -# Check the information about the help tables within -# INFORMATION_SCHEMA.TABLES/INFORMATION_SCHEMA.STATISTICS -# -# Variant for use during development (empty help tables) -# -# Creation: -# 2007-08-25 mleich Add this test as compensation for the -# checks removed within datadict_master.inc. -# - -let $c_help_category= `SELECT COUNT(*) FROM mysql.help_category`; -let $c_help_keyword= `SELECT COUNT(*) FROM mysql.help_keyword`; -let $c_help_relation= `SELECT COUNT(*) FROM mysql.help_relation`; -let $c_help_topic= `SELECT COUNT(*) FROM mysql.help_topic`; -if (`SELECT $c_help_category + $c_help_keyword + $c_help_relation - + $c_help_topic > 0`) -{ - --skip # Test requires empty help tables = Development test configuration -} - ---replace_column 9 "#ARL#" 10 "#DL#" 11 "#MDL#" 12 "#IL#" 13 "#DF#" 15 "YYYY-MM-DD hh:mm:ss" 16 "YYYY-MM-DD hh:mm:ss" 17 "YYYY-MM-DD hh:mm:ss" -SELECT * FROM INFORMATION_SCHEMA.TABLES -WHERE TABLE_SCHEMA = 'mysql' AND TABLE_NAME LIKE 'help_%'; -SELECT * FROM INFORMATION_SCHEMA.STATISTICS -WHERE TABLE_SCHEMA = 'mysql' AND TABLE_NAME LIKE 'help_%'; diff --git a/mysql-test/suite/funcs_1/t/innodb__datadict.test b/mysql-test/suite/funcs_1/t/innodb__datadict.test deleted file mode 100644 index 587ab1d6588..00000000000 --- a/mysql-test/suite/funcs_1/t/innodb__datadict.test +++ /dev/null @@ -1,11 +0,0 @@ -#### suite/funcs_1/t/datadict_innodb.test -# ---source include/have_innodb.inc - -let $engine_type= innodb; -# $OTHER_ENGINE_TYPE must be -# - <> $engine_type -# - all time available like MyISAM or MEMORY -let $OTHER_ENGINE_TYPE= MEMORY; - ---source suite/funcs_1/datadict/datadict_master.inc diff --git a/mysql-test/suite/funcs_1/t/innodb__load.test b/mysql-test/suite/funcs_1/t/innodb__load.test deleted file mode 100644 index d03672b31ff..00000000000 --- a/mysql-test/suite/funcs_1/t/innodb__load.test +++ /dev/null @@ -1,47 +0,0 @@ -##### suite/funcs_1/funcs_1/t/innodb__load.test - -# InnoDB tables should be used -# -# 1. Check if InnoDB is available ---source include/have_innodb.inc -# 2. Set $engine_type -let $engine_type= innodb; - -# Decide, if the objects are to be (re)created -# -# - once at the beginning of a set of testcases ('$NO_REFRESH' <> '' --> TRUE) -# That means all objects have to be (re)created within the current script. -# -# - per every testscript/case ('$NO_REFRESH' = '' --> FALSE) -# That means the current script must not (re)create any object and every -# testscript/case (re)creates only the objects it needs. - -eval SET @NO_REFRESH = IF( '$NO_REFRESH' = '', 0, 1); - -# FIXME Replace the following, when "if" for mysqltest is available -let $run= `SELECT @NO_REFRESH`; -while ($run) -{ - - # Create some objects needed in many testcases - USE test; - --source suite/funcs_1/include/innodb_tb1.inc - --source suite/funcs_1/include/innodb_tb2.inc - --source suite/funcs_1/include/innodb_tb3.inc - --source suite/funcs_1/include/innodb_tb4.inc - - # The database test1 is needed for the VIEW testcases - --disable_warnings - DROP DATABASE IF EXISTS test1; - --enable_warnings - CREATE DATABASE test1; - USE test1; - --source suite/funcs_1/include/innodb_tb2.inc - USE test; - - # These tables are needed for the stored procedure testscases - --source suite/funcs_1/include/sp_tb.inc - - let $run= 0; -} - diff --git a/mysql-test/suite/funcs_1/t/innodb_bitdata.test b/mysql-test/suite/funcs_1/t/innodb_bitdata.test index 24d5f077d96..b178aac598c 100644 --- a/mysql-test/suite/funcs_1/t/innodb_bitdata.test +++ b/mysql-test/suite/funcs_1/t/innodb_bitdata.test @@ -7,28 +7,11 @@ # 2. Set $engine_type let $engine_type= innodb; -# Decide, if the objects are to be (re)created -# -# - once at the beginning of a set of testcases ('$NO_REFRESH' <> '' --> TRUE) -# That means the current script must not (re)create any object. -# It can expect, that the objects already exist. -# -# - per every testscript/case ('$NO_REFRESH' = '' --> FALSE) -# That means all objects have to be (re)created within the current script. - -eval SET @NO_REFRESH = IF( '$NO_REFRESH' = '', 0, 1); +let $message= NOT YET IMPLEMENTED: bitdata tests; +--source include/show_msg80.inc +exit; -# FIXME Replace the following, when "if" for mysqltest is available -let $run= `SELECT @NO_REFRESH = 0`; -while ($run) -{ - - # Create some objects needed in many testcases - USE test; - --source suite/funcs_1/include/innodb_tb4.inc - - let $run= 0; -} - +# Create some objects needed in many testcases +USE test; +--source suite/funcs_1/include/innodb_tb4.inc --source suite/funcs_1/bitdata/bitdata_master.test - diff --git a/mysql-test/suite/funcs_1/t/innodb_cursors.test b/mysql-test/suite/funcs_1/t/innodb_cursors.test index 8d77045f2e7..a75e5cbba05 100644 --- a/mysql-test/suite/funcs_1/t/innodb_cursors.test +++ b/mysql-test/suite/funcs_1/t/innodb_cursors.test @@ -7,28 +7,13 @@ # 2. Set $engine_type let $engine_type= innodb; -# Decide, if the objects are to be (re)created -# -# - once at the beginning of a set of testcases ('$NO_REFRESH' <> '' --> TRUE) -# That means the current script must not (re)create any object. -# It can expect, that the objects already exist. -# -# - per every testscript/case ('$NO_REFRESH' = '' --> FALSE) -# That means all objects have to be (re)created within the current script. - -eval SET @NO_REFRESH = IF( '$NO_REFRESH' = '', 0, 1); - -# FIXME Replace the following, when "if" for mysqltest is available -let $run= `SELECT @NO_REFRESH = 0`; -while ($run) -{ - - # Create some objects needed in many testcases - USE test; - --source suite/funcs_1/include/innodb_tb1.inc +let $message= NOT YET IMPLEMENTED: cursor tests; +--source include/show_msg80.inc +exit; - let $run= 0; -} +# Create some objects needed in many testcases +USE test; +--source suite/funcs_1/include/innodb_tb1.inc --source suite/funcs_1/cursors/cursors_master.test diff --git a/mysql-test/suite/funcs_1/t/innodb_storedproc_02.test b/mysql-test/suite/funcs_1/t/innodb_storedproc_02.test index ace4d0cdc37..ace4d0cdc37 100755..100644 --- a/mysql-test/suite/funcs_1/t/innodb_storedproc_02.test +++ b/mysql-test/suite/funcs_1/t/innodb_storedproc_02.test diff --git a/mysql-test/suite/funcs_1/t/innodb_storedproc_03.test b/mysql-test/suite/funcs_1/t/innodb_storedproc_03.test index 3d1d6134b6f..3d1d6134b6f 100755..100644 --- a/mysql-test/suite/funcs_1/t/innodb_storedproc_03.test +++ b/mysql-test/suite/funcs_1/t/innodb_storedproc_03.test diff --git a/mysql-test/suite/funcs_1/t/innodb_storedproc_06.test b/mysql-test/suite/funcs_1/t/innodb_storedproc_06.test index ce061da2299..ce061da2299 100755..100644 --- a/mysql-test/suite/funcs_1/t/innodb_storedproc_06.test +++ b/mysql-test/suite/funcs_1/t/innodb_storedproc_06.test diff --git a/mysql-test/suite/funcs_1/t/innodb_storedproc_07.test b/mysql-test/suite/funcs_1/t/innodb_storedproc_07.test index dd1396e982e..dd1396e982e 100755..100644 --- a/mysql-test/suite/funcs_1/t/innodb_storedproc_07.test +++ b/mysql-test/suite/funcs_1/t/innodb_storedproc_07.test diff --git a/mysql-test/suite/funcs_1/t/innodb_storedproc_08.test b/mysql-test/suite/funcs_1/t/innodb_storedproc_08.test index c8c289c5f49..c8c289c5f49 100755..100644 --- a/mysql-test/suite/funcs_1/t/innodb_storedproc_08.test +++ b/mysql-test/suite/funcs_1/t/innodb_storedproc_08.test diff --git a/mysql-test/suite/funcs_1/t/innodb_storedproc_10.test b/mysql-test/suite/funcs_1/t/innodb_storedproc_10.test index 88a44a263d7..88a44a263d7 100755..100644 --- a/mysql-test/suite/funcs_1/t/innodb_storedproc_10.test +++ b/mysql-test/suite/funcs_1/t/innodb_storedproc_10.test diff --git a/mysql-test/suite/funcs_1/t/innodb_trig_0102.test b/mysql-test/suite/funcs_1/t/innodb_trig_0102.test index edd706b9e5d..c1da8f5448e 100644 --- a/mysql-test/suite/funcs_1/t/innodb_trig_0102.test +++ b/mysql-test/suite/funcs_1/t/innodb_trig_0102.test @@ -7,28 +7,10 @@ # 2. Set $engine_type let $engine_type= innodb; -# Decide, if the objects are to be (re)created -# -# - once at the beginning of a set of testcases ('$NO_REFRESH' <> '' --> TRUE) -# That means the current script must not (re)create any object. -# It can expect, that the objects already exist. -# -# - per every testscript/case ('$NO_REFRESH' = '' --> FALSE) -# That means all objects have to be (re)created within the current script. - -eval SET @NO_REFRESH = IF( '$NO_REFRESH' = '', 0, 1); - -# FIXME Replace the following, when "if" for mysqltest is available -let $run= `SELECT @NO_REFRESH = 0`; -while ($run) -{ - - # Create some objects needed in many testcases - USE test; - --source suite/funcs_1/include/innodb_tb3.inc +# Create some objects needed in many testcases +USE test; +--source suite/funcs_1/include/innodb_tb3.inc - let $run= 0; -} - --source suite/funcs_1/triggers/triggers_0102.inc +DROP TABLE test.tb3; diff --git a/mysql-test/suite/funcs_1/t/innodb_trig_03.test b/mysql-test/suite/funcs_1/t/innodb_trig_03.test index 5f931e1be47..374bcf59a37 100644 --- a/mysql-test/suite/funcs_1/t/innodb_trig_03.test +++ b/mysql-test/suite/funcs_1/t/innodb_trig_03.test @@ -7,28 +7,10 @@ # 2. Set $engine_type let $engine_type= innodb; -# Decide, if the objects are to be (re)created -# -# - once at the beginning of a set of testcases ('$NO_REFRESH' <> '' --> TRUE) -# That means the current script must not (re)create any object. -# It can expect, that the objects already exist. -# -# - per every testscript/case ('$NO_REFRESH' = '' --> FALSE) -# That means all objects have to be (re)created within the current script. - -eval SET @NO_REFRESH = IF( '$NO_REFRESH' = '', 0, 1); - -# FIXME Replace the following, when "if" for mysqltest is available -let $run= `SELECT @NO_REFRESH = 0`; -while ($run) -{ - - # Create some objects needed in many testcases - USE test; - --source suite/funcs_1/include/innodb_tb3.inc +# Create some objects needed in many testcases +USE test; +--source suite/funcs_1/include/innodb_tb3.inc - let $run= 0; -} - --source suite/funcs_1/triggers/triggers_03.inc +DROP TABLE test.tb3; diff --git a/mysql-test/suite/funcs_1/t/innodb_trig_0407.test b/mysql-test/suite/funcs_1/t/innodb_trig_0407.test index da8a074bab6..d6b7d4a9942 100644 --- a/mysql-test/suite/funcs_1/t/innodb_trig_0407.test +++ b/mysql-test/suite/funcs_1/t/innodb_trig_0407.test @@ -7,28 +7,10 @@ # 2. Set $engine_type let $engine_type= innodb; -# Decide, if the objects are to be (re)created -# -# - once at the beginning of a set of testcases ('$NO_REFRESH' <> '' --> TRUE) -# That means the current script must not (re)create any object. -# It can expect, that the objects already exist. -# -# - per every testscript/case ('$NO_REFRESH' = '' --> FALSE) -# That means all objects have to be (re)created within the current script. - -eval SET @NO_REFRESH = IF( '$NO_REFRESH' = '', 0, 1); - -# FIXME Replace the following, when "if" for mysqltest is available -let $run= `SELECT @NO_REFRESH = 0`; -while ($run) -{ - - # Create some objects needed in many testcases - USE test; - --source suite/funcs_1/include/innodb_tb3.inc - - let $run= 0; -} +# Create some objects needed in many testcases +USE test; +--source suite/funcs_1/include/innodb_tb3.inc --source suite/funcs_1/triggers/triggers_0407.inc +DROP TABLE test.tb3; diff --git a/mysql-test/suite/funcs_1/t/innodb_trig_08.test b/mysql-test/suite/funcs_1/t/innodb_trig_08.test index 05aabe8b0f5..a4ac2db0955 100644 --- a/mysql-test/suite/funcs_1/t/innodb_trig_08.test +++ b/mysql-test/suite/funcs_1/t/innodb_trig_08.test @@ -7,28 +7,10 @@ # 2. Set $engine_type let $engine_type= innodb; -# Decide, if the objects are to be (re)created -# -# - once at the beginning of a set of testcases ('$NO_REFRESH' <> '' --> TRUE) -# That means the current script must not (re)create any object. -# It can expect, that the objects already exist. -# -# - per every testscript/case ('$NO_REFRESH' = '' --> FALSE) -# That means all objects have to be (re)created within the current script. - -eval SET @NO_REFRESH = IF( '$NO_REFRESH' = '', 0, 1); - -# FIXME Replace the following, when "if" for mysqltest is available -let $run= `SELECT @NO_REFRESH = 0`; -while ($run) -{ - - # Create some objects needed in many testcases - USE test; - --source suite/funcs_1/include/innodb_tb3.inc +# Create some objects needed in many testcases +USE test; +--source suite/funcs_1/include/innodb_tb3.inc - let $run= 0; -} - --source suite/funcs_1/triggers/triggers_08.inc +DROP TABLE test.tb3; diff --git a/mysql-test/suite/funcs_1/t/innodb_trig_09.test b/mysql-test/suite/funcs_1/t/innodb_trig_09.test index ac21142779e..40a0f145ef0 100644 --- a/mysql-test/suite/funcs_1/t/innodb_trig_09.test +++ b/mysql-test/suite/funcs_1/t/innodb_trig_09.test @@ -7,28 +7,10 @@ # 2. Set $engine_type let $engine_type= innodb; -# Decide, if the objects are to be (re)created -# -# - once at the beginning of a set of testcases ('$NO_REFRESH' <> '' --> TRUE) -# That means the current script must not (re)create any object. -# It can expect, that the objects already exist. -# -# - per every testscript/case ('$NO_REFRESH' = '' --> FALSE) -# That means all objects have to be (re)created within the current script. - -eval SET @NO_REFRESH = IF( '$NO_REFRESH' = '', 0, 1); - -# FIXME Replace the following, when "if" for mysqltest is available -let $run= `SELECT @NO_REFRESH = 0`; -while ($run) -{ - - # Create some objects needed in many testcases - USE test; - --source suite/funcs_1/include/innodb_tb3.inc +# Create some objects needed in many testcases +USE test; +--source suite/funcs_1/include/innodb_tb3.inc - let $run= 0; -} - --source suite/funcs_1/triggers/triggers_09.inc +DROP TABLE test.tb3; diff --git a/mysql-test/suite/funcs_1/t/innodb_trig_1011ext.test b/mysql-test/suite/funcs_1/t/innodb_trig_1011ext.test index a507a488ca9..f778b097a1b 100644 --- a/mysql-test/suite/funcs_1/t/innodb_trig_1011ext.test +++ b/mysql-test/suite/funcs_1/t/innodb_trig_1011ext.test @@ -7,28 +7,10 @@ # 2. Set $engine_type let $engine_type= innodb; -# Decide, if the objects are to be (re)created -# -# - once at the beginning of a set of testcases ('$NO_REFRESH' <> '' --> TRUE) -# That means the current script must not (re)create any object. -# It can expect, that the objects already exist. -# -# - per every testscript/case ('$NO_REFRESH' = '' --> FALSE) -# That means all objects have to be (re)created within the current script. - -eval SET @NO_REFRESH = IF( '$NO_REFRESH' = '', 0, 1); - -# FIXME Replace the following, when "if" for mysqltest is available -let $run= `SELECT @NO_REFRESH = 0`; -while ($run) -{ - - # Create some objects needed in many testcases - USE test; - --source suite/funcs_1/include/innodb_tb3.inc +# Create some objects needed in many testcases +USE test; +--source suite/funcs_1/include/innodb_tb3.inc - let $run= 0; -} - --source suite/funcs_1/triggers/triggers_1011ext.inc +DROP TABLE test.tb3; diff --git a/mysql-test/suite/funcs_1/t/innodb_trig_frkey.test b/mysql-test/suite/funcs_1/t/innodb_trig_frkey.test index e99273672cd..57298fd9056 100644 --- a/mysql-test/suite/funcs_1/t/innodb_trig_frkey.test +++ b/mysql-test/suite/funcs_1/t/innodb_trig_frkey.test @@ -7,28 +7,10 @@ # 2. Set $engine_type let $engine_type= innodb; -# Decide, if the objects are to be (re)created -# -# - once at the beginning of a set of testcases ('$NO_REFRESH' <> '' --> TRUE) -# That means the current script must not (re)create any object. -# It can expect, that the objects already exist. -# -# - per every testscript/case ('$NO_REFRESH' = '' --> FALSE) -# That means all objects have to be (re)created within the current script. - -eval SET @NO_REFRESH = IF( '$NO_REFRESH' = '', 0, 1); - -# FIXME Replace the following, when "if" for mysqltest is available -let $run= `SELECT @NO_REFRESH = 0`; -while ($run) -{ - - # Create some objects needed in many testcases - USE test; - --source suite/funcs_1/include/innodb_tb3.inc +# Create some objects needed in many testcases +USE test; +--source suite/funcs_1/include/innodb_tb3.inc - let $run= 0; -} - --source suite/funcs_1/triggers/trig_frkey.inc +DROP TABLE test.tb3; diff --git a/mysql-test/suite/funcs_1/t/innodb_views.test b/mysql-test/suite/funcs_1/t/innodb_views.test index 1a835779762..dcab8ec305b 100644 --- a/mysql-test/suite/funcs_1/t/innodb_views.test +++ b/mysql-test/suite/funcs_1/t/innodb_views.test @@ -7,40 +7,18 @@ # 2. Set $engine_type let $engine_type= innodb; -# Decide, if the objects are to be (re)created -# -# - once at the beginning of a set of testcases ('$NO_REFRESH' <> '' --> TRUE) -# That means the current script must not (re)create any object. -# It can expect, that the objects already exist. -# -# - per every testscript/case ('$NO_REFRESH' = '' --> FALSE) -# That means all objects have to be (re)created within the current script. - -eval SET @NO_REFRESH = IF( '$NO_REFRESH' = '', 0, 1); +# Create some objects needed in many testcases +USE test; +--source suite/funcs_1/include/innodb_tb2.inc +--disable_warnings +DROP DATABASE IF EXISTS test1; +--enable_warnings +CREATE DATABASE test1; +USE test1; +--source suite/funcs_1/include/innodb_tb2.inc +USE test; -let $run= `SELECT @NO_REFRESH = 0`; -if ($run) -{ - - # Create some objects needed in many testcases - USE test; - --source suite/funcs_1/include/innodb_tb2.inc - --disable_warnings - DROP DATABASE IF EXISTS test1; - --enable_warnings - CREATE DATABASE test1; - USE test1; - --source suite/funcs_1/include/innodb_tb2.inc - USE test; - -} - --source suite/funcs_1/views/views_master.inc - -# If we created the database in the above loop we now need to drop it -let $run= `SELECT @NO_REFRESH = 0`; -if ($run) -{ - DROP DATABASE IF EXISTS test1; -} +DROP DATABASE test1; +DROP TABLE test.tb2; diff --git a/mysql-test/suite/funcs_1/t/is_basics_mixed.test b/mysql-test/suite/funcs_1/t/is_basics_mixed.test new file mode 100644 index 00000000000..8097c3ab3b1 --- /dev/null +++ b/mysql-test/suite/funcs_1/t/is_basics_mixed.test @@ -0,0 +1,503 @@ +# suite/funcs_1/t/is_basics_mixed.test +# +# Checks of some basic properties of the INFORMATION_SCHEMA which are not +# related to a certain INFORMATION_SCHEMA table. +# +# This test should not check properties related to storage engines. +# +# 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. +# + +--source suite/funcs_1/datadict/datadict.pre + +# $engine_type must point to storage engine which is all time available. +# The fastest engine should be preferred. +let $engine_type = MEMORY; + + +# The INFORMATION_SCHEMA database must exist. +SHOW DATABASES LIKE 'information_schema'; + + +--echo ####################################################################### +--echo # Testcase 3.2.1.20: USE INFORMATION_SCHEMA is supported +--echo ####################################################################### +# Ensure that USE INFORMATION_SCHEMA allows the user to switch to the +# INFORMATION_SCHEMA database, for query purposes only. +# +# Note: The "for query purposes only" is checked in other tests. +# High privileged user (root) +--echo # Switch to connection default +connection default; +USE test; +SELECT DATABASE(); +USE information_schema; +SELECT DATABASE(); +# +--error 0,ER_CANNOT_USER +DROP USER 'testuser1'@'localhost'; +CREATE USER 'testuser1'@'localhost'; +# Low privileged user +--echo # Establish connection testuser1 (user=testuser1) +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +connect (testuser1, localhost, testuser1, , test); +SELECT DATABASE(); +USE information_schema; +SELECT DATABASE(); +# +--echo # Switch to connection default and close connection testuser1 +connection default; +disconnect testuser1; +DROP USER 'testuser1'@'localhost'; + + +--echo ####################################################################### +--echo # Testcase TBD1: The INFORMATION_SCHEMA cannot be dropped. +--echo ####################################################################### +--error ER_DBACCESS_DENIED_ERROR +DROP DATABASE information_schema; + + +--echo ####################################################################### +--echo # Testcase TBD2: There cannot be a second database INFORMATION_SCHEMA. +--echo ####################################################################### +--error ER_DBACCESS_DENIED_ERROR +CREATE DATABASE information_schema; + + +--echo ################################################################################## +--echo # Testcase 3.2.1.6+3.2.1.7: No user may create an INFORMATION_SCHEMA table or view +--echo ################################################################################## +# 3.2.1.6 Ensure that no user may create an INFORMATION_SCHEMA base table. +# 3.2.1.7 Ensure that no user may create an INFORMATION_SCHEMA view +# + +# 1. High privileged user (root) +--echo # Switch to connection default (user=root) +connection default; +--source suite/funcs_1/datadict/basics_mixed1.inc + +# 2. High privileged user (testuser1) +--error 0,ER_CANNOT_USER +DROP USER 'testuser1'@'localhost'; +CREATE USER 'testuser1'@'localhost'; +GRANT ALL ON *.* TO testuser1@localhost; +SHOW GRANTS FOR testuser1@localhost; +--echo # Establish connection testuser1 (user=testuser1) +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +connect (testuser1, localhost, testuser1, , test); +--source suite/funcs_1/datadict/basics_mixed1.inc + +--echo # Switch to connection default (user=root) and close connection testuser1 +connection default; +disconnect testuser1; +DROP USER 'testuser1'@'localhost'; + +--echo ############################################################################### +--echo # Testcase 3.2.1.1+3.2.1.2: INFORMATION_SCHEMA tables can be queried via SELECT +--echo ############################################################################### +# 3.2.1.1 Ensure that every INFORMATION_SCHEMA table can be queried with +# a SELECT statement, just as if it were an ordinary user-defined table. +# 3.2.1.2 Ensure that queries on an INFORMATION_SCHEMA table can accept all +# SELECT statement options and are always correctly evaluated. +# +# Some notes(mleich): +# - Currently here only a subset of select statement options is checked, it's +# still not possible to check here all possible options +# - The content of many INFORMATION_SCHEMA tables is checked in other tests. +# - We work here only with a subset of the columns of information_schema.tables +# because we want have a stable base (all time existing table, stable layout). +--disable_warnings +DROP DATABASE IF EXISTS db_datadict; +--enable_warnings +CREATE DATABASE db_datadict; +--replace_result $engine_type <some_engine> +eval +CREATE TABLE db_datadict.t1_first (f1 BIGINT UNIQUE, f2 BIGINT) +ENGINE = $engine_type; +--replace_result $engine_type <some_engine> +eval +CREATE TABLE db_datadict.t1_second (f1 BIGINT UNIQUE, f2 BIGINT) +ENGINE = $engine_type; + +# SELECT * +--echo # Attention: The protocolling of the next result set is disabled. +--disable_result_log +SELECT * FROM information_schema.tables; +--enable_result_log +# +# SELECT <some columns> + WHERE +--sorted_result +SELECT table_name FROM information_schema.tables +WHERE table_schema = 'db_datadict'; +# +# SELECT string_function(<some column>) + ORDER BY +SELECT LENGTH(table_name) FROM information_schema.tables +WHERE table_schema = 'db_datadict' ORDER BY table_name; +# +# SELECT aggregate_function(<some column>) + WHERE with LIKE +SELECT count(table_name) FROM information_schema.tables +WHERE table_schema LIKE 'db_datadic%'; +# +# SELECT with addition in column list +--sorted_result +SELECT CAST((LENGTH(table_schema) + LENGTH(table_name)) AS DECIMAL(15,1)) +FROM information_schema.tables +WHERE table_schema = 'db_datadict'; +# +# WHERE with IN + LIMIT +SELECT table_name FROM information_schema.tables +WHERE table_name IN ('t1_first','t1_second') ORDER BY table_name LIMIT 1; +SELECT table_name FROM information_schema.tables +WHERE table_name IN ('t1_first','t1_second') ORDER BY table_name LIMIT 1,1; +# +# WHERE with AND +SELECT table_name,table_schema AS my_col FROM information_schema.tables +WHERE table_name = 't1_first' AND table_schema = 'db_datadict'; +# +# SELECT HIGH_PRIORITY + WHERE with OR +--sorted_result +SELECT HIGH_PRIORITY table_name AS my_col FROM information_schema.tables +WHERE table_name = 't1_first' OR table_name = 't1_second'; +# +# Empty result set +SELECT 1 AS my_col FROM information_schema.tables +WHERE table_name = 't1_third'; +# +# SELECT INTO USER VARIABLE +SELECT table_name,table_schema INTO @table_name,@table_schema +FROM information_schema.tables +WHERE table_schema = 'db_datadict' ORDER BY table_name LIMIT 1; +SELECT @table_name,@table_schema; +# +# SELECT INTO OUTFILE +let $OUTFILE = $MYSQL_TMP_DIR/datadict.out; +--error 0,1 +remove_file $OUTFILE; +--replace_result $OUTFILE <OUTFILE> +eval SELECT table_name,table_schema +INTO OUTFILE '$OUTFILE' +FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' +LINES TERMINATED BY '\n' +FROM information_schema.tables +WHERE table_schema = 'db_datadict' ORDER BY table_name; +cat_file $OUTFILE; +remove_file $OUTFILE; +# +# UNION +--sorted_result +SELECT table_name FROM information_schema.tables +WHERE table_name = 't1_first' +UNION ALL +SELECT table_name FROM information_schema.tables +WHERE table_name = 't1_second'; +# +# DISTINCT + SUBQUERY +SELECT DISTINCT table_schema FROM information_schema.tables +WHERE table_name IN (SELECT table_name FROM information_schema.tables + WHERE table_schema = 'db_datadict') +ORDER BY table_name; +# +# JOIN +SELECT table_name FROM information_schema.tables t1 +LEFT JOIN information_schema.tables t2 USING(table_name,table_schema) +WHERE t2.table_schema = 'db_datadict' +ORDER BY table_name; +# +# No schema assigned in SELECT + we are in SCHEMA test +# --> The table tables does not exist +USE test; +--error ER_NO_SUCH_TABLE +SELECT * FROM tables; + + +--echo ######################################################################### +--echo # Testcase 3.2.1.17+3.2.1.18 +--echo ######################################################################### +# 3.2.1.17: Ensure that the SELECT privilege is granted TO PUBLIC WITH GRANT +# OPTION on every INFORMATION_SCHEMA table. +# +# 3.2.1.18: Ensure that the CREATE VIEW privilege on an INFORMATION_SCHEMA table +# may be granted to any user. +# +# Note (mleich): The requirements are to some extend outdated. +# Every user is allowed to SELECT on the INFORMATION_SCHEMA. +# But the result sets depend on the privileges of the user. +# +--disable_warnings +DROP DATABASE IF EXISTS db_datadict; +--enable_warnings +CREATE DATABASE db_datadict; +--replace_result $engine_type <some_engine> +eval +CREATE TABLE db_datadict.t1 (f1 BIGINT UNIQUE, f2 BIGINT) +ENGINE = $engine_type; +SELECT * FROM db_datadict.t1; + +--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'; +GRANT CREATE VIEW,SELECT ON db_datadict.* TO testuser1@localhost +WITH GRANT OPTION; +GRANT USAGE ON db_datadict.* TO testuser2@localhost; +FLUSH PRIVILEGES; + +# Check 0: Reveal that GRANT <some privilege> ON INFORMATION_SCHEMA is no +# longer allowed. +--error ER_DBACCESS_DENIED_ERROR +GRANT SELECT on information_schema.* TO testuser1@localhost; +--error ER_DBACCESS_DENIED_ERROR +GRANT CREATE VIEW ON information_schema.* TO 'u_6_401018'@'localhost'; + +# Check 1: Show that a "simple" user (<> root) has the permission to SELECT +# on some INFORMATION_SCHEMA table. +--echo # Establish connection testuser1 (user=testuser1) +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +connect (testuser1, localhost, testuser1, , db_datadict); +SELECT table_schema,table_name FROM information_schema.tables +WHERE table_schema = 'information_schema' AND table_name = 'tables'; + +# Check 2: Show the privileges of the user on some INFORMATION_SCHEMA tables. +SELECT * FROM information_schema.table_privileges +WHERE table_schema = 'information_schema'; +SELECT * FROM information_schema.schema_privileges +WHERE table_schema = 'information_schema'; + +# Check 3: Show the following +# 1. If a simple user (testuser1) has the privilege to create a VIEW +# than this VIEW could use a SELECT on an INFORMATION_SCHEMA table. +# 2. This user (testuser1) is also able to GRANT the SELECT privilege +# on this VIEW to another user (testuser2). +# 3. The other user (testuser2) must be able to SELECT on this VIEW +# but gets a different result set than testuser1. +CREATE VIEW db_datadict.v2 AS +SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE +FROM information_schema.tables WHERE table_schema = 'db_datadict'; +SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE +FROM db_datadict.v2; +SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE +FROM information_schema.tables WHERE table_schema = 'db_datadict'; +GRANT SELECT ON db_datadict.v2 to testuser2@localhost; +# +--echo # Establish connection testuser2 (user=testuser2) +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +connect (testuser2, localhost, testuser2, , db_datadict); +SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE +FROM db_datadict.v2; +SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE +FROM information_schema.tables WHERE table_schema = 'db_datadict'; + +# Cleanup +--echo # Switch to connection default and close connections testuser1 and testuser2 +connection default; +disconnect testuser1; +disconnect testuser2; +DROP USER 'testuser1'@'localhost'; +DROP USER 'testuser2'@'localhost'; +DROP DATABASE db_datadict; + + +--echo ######################################################################### +--echo # Testcase 3.2.1.19 +--echo ######################################################################### +# Ensure that no other privilege on an INFORMATION_SCHEMA table is granted, or +# may be granted, to any user. +# +--error 0,ER_CANNOT_USER +DROP USER 'testuser1'@'localhost'; +CREATE USER 'testuser1'@'localhost'; + +# Initial privileges on the INFORMATION_SCHEMA tables (empty result sets) +let $my_select1 = SELECT 'empty result set was expected' AS my_col +FROM information_schema.schema_privileges +WHERE table_schema = 'information_schema'; +let $my_select2 = SELECT 'empty result set was expected' AS my_col +FROM information_schema.table_privileges +WHERE table_schema = 'information_schema'; +let $my_select3 = SELECT 'empty result set was expected' AS my_col +FROM information_schema.column_privileges +WHERE table_schema = 'information_schema'; +eval $my_select1; +eval $my_select2; +eval $my_select3; + +#FIXME: check GRANT on IS +--error ER_DBACCESS_DENIED_ERROR +GRANT ALTER ON information_schema.* +TO 'testuser1'@'localhost'; + +#FIXME: check GRANT on IS +--error ER_DBACCESS_DENIED_ERROR +GRANT ALTER ROUTINE ON information_schema.* +TO 'testuser1'@'localhost'; + +#FIXME: check GRANT on IS +--error ER_DBACCESS_DENIED_ERROR +GRANT CREATE ON information_schema.* +TO 'testuser1'@'localhost'; + +#FIXME: check GRANT on IS +--error ER_DBACCESS_DENIED_ERROR +GRANT CREATE ROUTINE ON information_schema.* +TO 'testuser1'@'localhost'; + +#FIXME: check GRANT on IS +--error ER_DBACCESS_DENIED_ERROR +GRANT CREATE TEMPORARY TABLES ON information_schema.* +TO 'testuser1'@'localhost'; + +#FIXME: check GRANT on IS +--error ER_DBACCESS_DENIED_ERROR +GRANT DELETE ON information_schema.* +TO 'testuser1'@'localhost'; + +#FIXME: check GRANT on IS +--error ER_DBACCESS_DENIED_ERROR +GRANT DROP ON information_schema.* +TO 'testuser1'@'localhost'; + +#FIXME: check GRANT on IS +--error ER_DBACCESS_DENIED_ERROR +GRANT EXECUTE ON information_schema.* +TO 'testuser1'@'localhost'; + +#FIXME: check GRANT on IS +--error ER_DBACCESS_DENIED_ERROR +GRANT INDEX ON information_schema.* +TO 'testuser1'@'localhost'; + +#FIXME: check GRANT on IS +--error ER_DBACCESS_DENIED_ERROR +GRANT INSERT ON information_schema.* +TO 'testuser1'@'localhost'; + +#FIXME: check GRANT on IS +--error ER_DBACCESS_DENIED_ERROR +GRANT LOCK TABLES ON information_schema.* +TO 'testuser1'@'localhost'; + +#FIXME: check GRANT on IS +--error ER_DBACCESS_DENIED_ERROR +GRANT UPDATE ON information_schema.* +TO 'testuser1'@'localhost'; + +# Has something accidently changed? +eval $my_select1; +eval $my_select2; +eval $my_select3; + +# Cleanup +DROP USER 'testuser1'@'localhost'; + + +--echo ######################################################################### +--echo # Testcase 3.2.1.16 +--echo ######################################################################### +# Ensure that no user may use any INFORMATION_SCHEMA table to determine any +# information on a database and/or its structure unless authorized to get that +# information. +# Note: The plan is to create a new database and objects within it so that +# any INFORMATION_SCHEMA table gets additional rows if possible. +# A user having no rights on the new database and no rights on objects +# must nowhere see tha name of the new database. +--source suite/funcs_1/datadict/basics_mixed3.inc + +--disable_warnings +DROP DATABASE IF EXISTS db_datadict; +--enable_warnings +CREATE DATABASE db_datadict; +--replace_result $engine_type <some_engine> +eval +CREATE TABLE db_datadict.t1 (f1 BIGINT, f2 BIGINT NOT NULL, f3 BIGINT, +PRIMARY KEY(f1)) +ENGINE = $engine_type; +CREATE UNIQUE INDEX UIDX ON db_datadict.t1(f3); +CREATE PROCEDURE db_datadict.sproc1() SELECT 'db_datadict'; +CREATE FUNCTION db_datadict.func1() RETURNS INT RETURN 0; +CREATE TRIGGER db_datadict.trig1 BEFORE INSERT ON db_datadict.t1 +FOR EACH ROW SET @aux = 1; +CREATE VIEW db_datadict.v1 AS SELECT * FROM db_datadict.t1; +CREATE VIEW db_datadict.v2 AS SELECT * FROM information_schema.tables; + +--source suite/funcs_1/datadict/basics_mixed3.inc + +--error 0,ER_CANNOT_USER +DROP USER 'testuser1'@'localhost'; +CREATE USER 'testuser1'@'localhost'; +GRANT ALL ON test.* TO 'testuser1'@'localhost'; + +--echo # Establish connection testuser1 (user=testuser1) +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +connect (testuser1, localhost, testuser1, , test); +--source suite/funcs_1/datadict/basics_mixed3.inc + +# Cleanup +--echo # Switch to connection default and close connections testuser1 and testuser2 +connection default; +disconnect testuser1; +DROP USER 'testuser1'@'localhost'; +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 tables are not supported +--echo ######################################################################## +# Thorough tests checking the requirements above per every INFORMATION_SCHEMA +# table are within other scripts. +# We check here only that the requirement is fulfilled even when using a +# STORED PROCEDURE. +--disable_warnings +DROP PROCEDURE IF EXISTS test.p1; +--enable_warnings +CREATE PROCEDURE test.p1() +INSERT INTO information_schema.tables +SELECT * FROM information_schema.tables LIMIT 1; +--error ER_DBACCESS_DENIED_ERROR +CALL test.p1(); + +DROP PROCEDURE test.p1; +CREATE PROCEDURE test.p1() +UPDATE information_schema.columns SET table_schema = 'garbage'; +--error ER_DBACCESS_DENIED_ERROR +CALL test.p1(); + +DROP PROCEDURE test.p1; +CREATE PROCEDURE test.p1() +DELETE FROM information_schema.schemata; +--error ER_DBACCESS_DENIED_ERROR +CALL test.p1(); + +DROP PROCEDURE test.p1; + + +--echo ######################################################################### +--echo # Testcase 3.2.17.1+3.2.17.2: To be implemented outside of this script +--echo ######################################################################### +# 3.2.17.1 Ensure that every INFORMATION_SCHEMA table shows all the correct +# information, and no incorrect information, for a database to which +# 100 different users, each of which has a randomly issued set of +# privileges and access to a randomly chosen set of database objects, +# have access. +# The database should contain a mixture of all types of database +# objects (i.e. tables, views, stored procedures, triggers). +# 3.2.17.2 Ensure that every INFORMATION_SCHEMA table shows all the correct +# information, and no incorrect information, for 10 different +# databases to which 50 different users, each of which has a randomly +# issued set of privileges and access to a randomly chosen set of +# database objects in two or more of the databases, have access. +# The databases should each contain a mixture of all types of database +# objects (i.e. tables, views, stored procedures, triggers). +# +# Note(mleich): These requirements are kept here so that they do not get lost. +# The tests are not yet implemented. +# If they are ever developed than they should be stored in other +# scripts. They will have most probably a long runtime because +# the current INFORMATION_SCHEMA implementation has some performance +# issues if a lot of users, privileges and objects are involved. +# diff --git a/mysql-test/suite/funcs_1/t/is_character_sets.test b/mysql-test/suite/funcs_1/t/is_character_sets.test new file mode 100644 index 00000000000..dbb35587eab --- /dev/null +++ b/mysql-test/suite/funcs_1/t/is_character_sets.test @@ -0,0 +1,107 @@ +# suite/funcs_1/t/is_character_sets.test +# +# Check the layout of information_schema.character_sets and run some +# functionality related 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. +# + +let $is_table = CHARACTER_SETS; + +# The table INFORMATION_SCHEMA.CHARACTER_SETS 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.2.1: INFORMATION_SCHEMA.CHARACTER_SETS layout +--echo ######################################################################### +# Ensure that the INFORMATION_SCHEMA.CHARACTER_SETS table has the following +# columns, in the following order: +# +# CHARACTER_SET_NAME (shows a character set name), +# DEFAULT_COLLATE_NAME (shows the name of the default collation for that +# character set), +# DESCRIPTION (shows a descriptive name for that character set), +# MAXLEN (shows the number of bytes used to store each character supported by +# that character set). +# +eval DESCRIBE information_schema.$is_table; +eval SHOW CREATE TABLE information_schema.$is_table; +eval SHOW COLUMNS FROM information_schema.$is_table; + +# Note: Retrieval of information within information_schema.columns about +# information_schema.character_sets is in is_columns_is.test. +# Retrieval of information_schema.character_sets content is in +# charset_collation.inc (sourced by charset_collation_*.test). + + +echo # Testcases 3.2.2.2 and 3.2.2.3 are checked in suite/funcs_1/t/charset_collation*.test; + +--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; + +--error ER_DBACCESS_DENIED_ERROR +INSERT INTO information_schema.character_sets +SELECT * FROM information_schema.character_sets; + +--error ER_DBACCESS_DENIED_ERROR +UPDATE information_schema.character_sets SET description = 'just updated'; + +--error ER_DBACCESS_DENIED_ERROR +DELETE FROM information_schema.character_sets WHERE table_name = 't1'; +--error ER_DBACCESS_DENIED_ERROR +TRUNCATE information_schema.character_sets; + +--error ER_DBACCESS_DENIED_ERROR +CREATE INDEX my_idx ON information_schema.character_sets(character_set_name); + +--error ER_DBACCESS_DENIED_ERROR +ALTER TABLE information_schema.character_sets DROP PRIMARY KEY; +--error ER_DBACCESS_DENIED_ERROR +ALTER TABLE information_schema.character_sets ADD f1 INT; + +--error ER_DBACCESS_DENIED_ERROR +DROP TABLE information_schema.character_sets; + +--error ER_DBACCESS_DENIED_ERROR +ALTER TABLE information_schema.character_sets RENAME db_datadict.character_sets; +--error ER_DBACCESS_DENIED_ERROR +ALTER TABLE information_schema.character_sets +RENAME information_schema.xcharacter_sets; + +# Cleanup +DROP DATABASE db_datadict; + diff --git a/mysql-test/suite/funcs_1/t/is_collation_character_set_applicability.test b/mysql-test/suite/funcs_1/t/is_collation_character_set_applicability.test new file mode 100644 index 00000000000..6572d8e5d55 --- /dev/null +++ b/mysql-test/suite/funcs_1/t/is_collation_character_set_applicability.test @@ -0,0 +1,108 @@ +# suite/funcs_1/t/is_collation_character_set_applicability.test +# +# Check the layout of information_schema.collation_character_set_applicability +# and some functionality realted 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. +# + +let $is_table = COLLATION_CHARACTER_SET_APPLICABILITY; + +# The table INFORMATION_SCHEMA.CHARACTER_SET_APPLICABILITY 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.4.1: INFORMATION_SCHEMA.CHARACTER_SET_APPLICABILITY layout +--echo ######################################################################### +# Ensure that the INFORMATION_SCHEMA.CHARACTER_SET_APPLICABILITY table has the +# following columns, in the following order: +# +# COLLATION_NAME (shows the name of a collation), +# CHARACTER_SET_NAME (shows the name of a character set to which that +# collation applies). +# +eval DESCRIBE information_schema.$is_table; +eval SHOW CREATE TABLE information_schema.$is_table; +eval SHOW COLUMNS FROM information_schema.$is_table; + +# Note: Retrieval of information within information_schema.columns about +# information_schema.collation_character_set_applicability is in +# is_columns_is.test. +# Retrieval of information_schema.collation_character_set_applicability +# content is in charset_collation.inc (sourced by charset_collation_*.test). + +echo # Testcases 3.2.4.2 and 3.2.4.3 are checked in suite/funcs_1/t/charset_collation*.test; + +--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; + +--error ER_DBACCESS_DENIED_ERROR +INSERT INTO information_schema.collation_character_set_applicability +SELECT * FROM information_schema.collation_character_set_applicability; + +--error ER_DBACCESS_DENIED_ERROR +UPDATE information_schema.collation_character_set_applicability +SET collation_name = 'big6_chinese_ci' WHERE character_set_name = 'big6'; +--error ER_DBACCESS_DENIED_ERROR +UPDATE information_schema.collation_character_set_applicability +SET character_set_name = 't_4711'; + +--error ER_DBACCESS_DENIED_ERROR +DELETE FROM information_schema.collation_character_set_applicability; +--error ER_DBACCESS_DENIED_ERROR +TRUNCATE information_schema.collation_character_set_applicability; + +--error ER_DBACCESS_DENIED_ERROR +CREATE INDEX my_idx +ON information_schema.collation_character_set_applicability(collation_name); + +--error ER_DBACCESS_DENIED_ERROR +ALTER TABLE information_schema.collation_character_set_applicability ADD f1 INT; + +--error ER_DBACCESS_DENIED_ERROR +DROP TABLE information_schema.collation_character_set_applicability; + +--error ER_DBACCESS_DENIED_ERROR +ALTER TABLE information_schema.collation_character_set_applicability +RENAME db_datadict.collation_character_set_applicability; +--error ER_DBACCESS_DENIED_ERROR +ALTER TABLE information_schema.collation_character_set_applicability +RENAME information_schema.xcollation_character_set_applicability; + +# Cleanup +DROP DATABASE db_datadict; + diff --git a/mysql-test/suite/funcs_1/t/is_collations.test b/mysql-test/suite/funcs_1/t/is_collations.test new file mode 100644 index 00000000000..e807b3cb028 --- /dev/null +++ b/mysql-test/suite/funcs_1/t/is_collations.test @@ -0,0 +1,114 @@ +# suite/funcs_1/t/is_collations.test +# +# Check the layout of information_schema.collations and some +# functionality related 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. +# + +let $is_table = COLLATIONS; + +# The table INFORMATION_SCHEMA.COLLATIONS 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.3.1: INFORMATION_SCHEMA.COLLATIONS layout +--echo ######################################################################### +# Ensure that the INFORMATION_SCHEMA.COLLATIONS table has the following +# columns, in the following order: +# +# COLLATION_NAME (shows a collation name), +# CHARACTER_SET_NAME (shows the name of the character set to which the +# collation applies), +# ID (shows a numeric identifier for that collation/character set combination), +# IS_DEFAULT (shows whether the collation is the default collation for the +# character set shown), +# IS_COMPILED (indicates whether the collation is compiled into the MySQL server), +# SORTLEN (shows a value related to the amount of memory required to sort +# strings using this collation/character set combination). +# +eval DESCRIBE information_schema.$is_table; +eval SHOW CREATE TABLE information_schema.$is_table; +eval SHOW COLUMNS FROM information_schema.$is_table; + +# Note: Retrieval of information within information_schema.columns about +# information_schema.collations is in is_columns_is.test. +# Retrieval of information_schema.collations content is in +# charset_collation.inc (sourced by charset_collation_*.test). + +echo # Testcases 3.2.3.2 and 3.2.3.3 are checked in suite/funcs_1/t/charset_collation*.test; + +--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; + +--error ER_DBACCESS_DENIED_ERROR +INSERT INTO information_schema.collations +SELECT * FROM information_schema.collations; +--error ER_DBACCESS_DENIED_ERROR +INSERT INTO information_schema.collations + (collation_name,character_set_name,id,is_default,is_compiled,sortlen) +VALUES ( 'cp1251_bin', 'cp1251',50, '', '',0); + +--error ER_DBACCESS_DENIED_ERROR +UPDATE information_schema.collations SET description = 'just updated'; + +--error ER_DBACCESS_DENIED_ERROR +DELETE FROM information_schema.collations WHERE table_name = 't1'; +--error ER_DBACCESS_DENIED_ERROR +TRUNCATE information_schema.collations; + +--error ER_DBACCESS_DENIED_ERROR +CREATE INDEX my_idx ON information_schema.collations(character_set_name); + +--error ER_DBACCESS_DENIED_ERROR +ALTER TABLE information_schema.collations DROP PRIMARY KEY; +--error ER_DBACCESS_DENIED_ERROR +ALTER TABLE information_schema.collations ADD f1 INT; +--error ER_DBACCESS_DENIED_ERROR +ALTER TABLE information_schema.collations ENABLE KEYS; + +--error ER_DBACCESS_DENIED_ERROR +DROP TABLE information_schema.collations; + +--error ER_DBACCESS_DENIED_ERROR +ALTER TABLE information_schema.collations RENAME db_datadict.collations; +--error ER_DBACCESS_DENIED_ERROR +ALTER TABLE information_schema.collations +RENAME information_schema.xcollations; + +# Cleanup +DROP DATABASE db_datadict; + diff --git a/mysql-test/suite/funcs_1/t/is_column_privileges.test b/mysql-test/suite/funcs_1/t/is_column_privileges.test new file mode 100644 index 00000000000..8f125051060 --- /dev/null +++ b/mysql-test/suite/funcs_1/t/is_column_privileges.test @@ -0,0 +1,351 @@ +# suite/funcs_1/t/is_column_privileges.test +# +# Check the layout of information_schema.column_privileges 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. +# +# 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. +# + +# --source suite/funcs_1/datadict/datadict.pre + +let $engine_type = MEMORY; +let $other_engine_type = MyISAM; + +let $is_table = COLUMN_PRIVILEGES; + +# The table INFORMATION_SCHEMA.COLUMN_PRIVILEGES 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.5.1: INFORMATION_SCHEMA.COLUMN_PRIVILEGES layout +--echo ######################################################################### +# Ensure that the INFORMATION_SCHEMA.COLUMN_PRIVILEGES table has the following +# columns, in the following order: +# +# GRANTEE (shows the name of a user who has either granted, +# or been granted a column privilege), +# TABLE_CATALOG (always shows NULL), +# TABLE_SCHEMA (shows the name of the schema, or database, in which the table +# for which a column privilege has been granted resides), +# TABLE_NAME (shows the name of the table), +# COLUMN_NAME (shows the name of the column on which a column privilege has +# been granted), +# PRIVILEGE_TYPE (shows the type of privilege that was granted; must be either +# SELECT, INSERT, UPDATE, or REFERENCES), +# IS_GRANTABLE (shows whether that privilege was granted WITH GRANT OPTION). +# +--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.column_privileges is in is_columns_is.test. + +# Show that TABLE_CATALOG is always NULL. +SELECT table_catalog, table_schema, table_name, column_name, privilege_type +FROM information_schema.column_privileges WHERE table_catalog IS NOT NULL; + + +--echo ###################################################################### +--echo # Testcase 3.2.5.2+3.2.5.3+3.2.5.4: +--echo # INFORMATION_SCHEMA.COLUMN_PRIVILEGES accessible information +--echo ###################################################################### +# 3.2.5.2: Ensure that the table shows the relevant information on every +# column privilege which has been granted to the current user or +# PUBLIC, or which was granted by the current user. +# 3.2.5.3: Ensure that the table does not show any information on any column +# privilege which was granted to any user other than the current user +# or PUBLIC, or which was granted by any user other than +# the current user. +# 3.2.5.4: Ensure that the table does not show any information on any +# privileges that are not column privileges for the current user. +# +# Note: Check of content within information_schema.column_privileges about the +# databases information_schema, mysql and test is in +# is_column_privileges_is_mysql_test.test +# +--disable_warnings +DROP DATABASE IF EXISTS db_datadict; +--enable_warnings +CREATE DATABASE db_datadict; +--replace_result $other_engine_type <other_engine_type> +eval +CREATE TABLE db_datadict.t1 (f1 INT, f2 DECIMAL, f3 TEXT) +ENGINE = $other_engine_type; + +USE 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'; + +GRANT SELECT(f1, f3) ON db_datadict.t1 TO 'testuser1'@'localhost'; +GRANT INSERT(f1) ON db_datadict.t1 TO 'testuser1'@'localhost'; +GRANT UPDATE(f2) ON db_datadict.t1 TO 'testuser1'@'localhost'; +GRANT SELECT(f2) ON db_datadict.t1 TO 'testuser2'@'localhost'; +GRANT INSERT, SELECT ON db_datadict.t1 TO 'testuser3'@'localhost'; +GRANT SELECT(f3) ON db_datadict.t1 TO 'testuser3'@'localhost'; + +GRANT INSERT, SELECT ON db_datadict.t1 TO 'testuser3'@'localhost' +WITH GRANT OPTION; +GRANT ALL ON db_datadict.* TO 'testuser3'@'localhost'; + +let $select= SELECT * FROM information_schema.column_privileges +WHERE grantee LIKE '''testuser%''' +ORDER BY grantee, table_schema,table_name,column_name,privilege_type; +eval $select; + +# Note: WITH GRANT OPTION applies to all privileges on this table +# and not to the columns mentioned only. +GRANT UPDATE(f3) ON db_datadict.t1 TO 'testuser1'@'localhost' +WITH GRANT OPTION; + +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; + +--echo # Establish connection testuser3 (user=testuser3) +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +connect (testuser3, localhost, testuser3, , db_datadict); + +--echo # FIXME: Is it correct that granted TABLES do not occur in COLUMN_PRIVILEGES? +SELECT * FROM information_schema.table_privileges +WHERE grantee LIKE '''testuser%''' +ORDER BY grantee,table_schema,table_name,privilege_type; +SELECT * FROM information_schema.schema_privileges +WHERE grantee LIKE '''testuser%''' +ORDER BY grantee,table_schema,privilege_type; +eval $select; +GRANT SELECT(f1, f3) ON db_datadict.t1 TO 'testuser2'@'localhost'; + +--echo # FIXME: Is it intended that *my* grants to others are *NOT* shown here? +eval $select; + +--echo # Switch to connection testuser2 (user=testuser2) +connection testuser2; +eval $select; + +# Cleanup +--echo # Switch to connection default and close connections testuser1,testuser2,testuser3 +connection default; +disconnect testuser1; +disconnect testuser2; +disconnect testuser3; +DROP DATABASE db_datadict; +DROP USER 'testuser1'@'localhost'; +DROP USER 'testuser2'@'localhost'; +DROP USER 'testuser3'@'localhost'; + + +--echo ################################################################################ +--echo # 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.COLUMN_PRIVILEGES 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. +# +# Note (mleich): +# The MySQL privilege system allows to GRANT objects before they exist. +# (Exception: Grant privileges for columns of not existing tables/views.) +# There is also no migration of privileges if objects (tables, views, columns) +# are moved to other databases (tables only), renamed or dropped. +# +--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.my_table (f1 BIGINT, f2 CHAR(10), f3 DATE) +ENGINE = $engine_type; + +--error 0,ER_CANNOT_USER +DROP USER 'testuser1'@'localhost'; +CREATE USER 'testuser1'@'localhost'; +GRANT ALL ON test.* TO 'testuser1'@'localhost'; + +let $my_select = SELECT * FROM information_schema.column_privileges +WHERE table_name = 'my_table' +ORDER BY grantee, table_schema,table_name,column_name,privilege_type; +let $my_show = SHOW GRANTS FOR 'testuser1'@'localhost'; +eval $my_select; +eval $my_show; + +--echo # Establish connection testuser1 (user=testuser1) +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +connect (testuser1, localhost, testuser1, , test); +eval $my_select; +eval $my_show; + +--echo # Switch to connection default +connection default; +GRANT SELECT (f1,f3) ON db_datadict.my_table TO 'testuser1'@'localhost'; +eval $my_select; +eval $my_show; + +--echo # Switch to connection testuser1 +connection testuser1; +eval $my_select; +eval $my_show; + +--echo # Switch to connection default +connection default; +ALTER TABLE db_datadict.my_table DROP COLUMN f3; +GRANT UPDATE (f1) ON db_datadict.my_table TO 'testuser1'@'localhost'; +eval $my_select; +eval $my_show; + +--echo # Switch to connection testuser1 +connection testuser1; +eval $my_select; +eval $my_show; +--error ER_BAD_FIELD_ERROR +SELECT f1, f3 FROM db_datadict.my_table; + +--echo # Switch to connection default +connection default; +ALTER TABLE db_datadict.my_table CHANGE COLUMN f1 my_col BIGINT; +eval $my_select; +eval $my_show; + +--echo # Switch to connection testuser1 +connection testuser1; +eval $my_select; +eval $my_show; + +--echo # Switch to connection default +connection default; +DROP TABLE db_datadict.my_table; +eval $my_select; +eval $my_show; + +--echo # Switch to connection testuser1 +connection testuser1; +eval $my_select; +eval $my_show; + +--echo # Switch to connection default +connection default; +REVOKE ALL ON db_datadict.my_table FROM 'testuser1'@'localhost'; +eval $my_select; +eval $my_show; + +--echo # Switch to connection testuser1 +connection testuser1; +eval $my_select; +eval $my_show; + +--echo # Switch to connection default and close connection testuser1 +connection default; +disconnect testuser1; +DROP USER 'testuser1'@'localhost'; +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; +--replace_result $engine_type <engine_type> +eval +CREATE TABLE db_datadict.t1 (f1 BIGINT, f2 BIGINT) +ENGINE = $engine_type; +--error 0,ER_CANNOT_USER +DROP USER 'testuser1'@'localhost'; +CREATE USER 'testuser1'@'localhost'; +GRANT SELECT (f1) ON db_datadict.t1 TO 'testuser1'@'localhost'; + +--error ER_DBACCESS_DENIED_ERROR +INSERT INTO information_schema.column_privileges +SELECT * FROM information_schema.column_privileges; + +--error ER_DBACCESS_DENIED_ERROR +UPDATE information_schema.column_privileges SET table_schema = 'test' +WHERE table_name = 't1'; + +--error ER_DBACCESS_DENIED_ERROR +DELETE FROM information_schema.column_privileges WHERE table_name = 't1'; +--error ER_DBACCESS_DENIED_ERROR +TRUNCATE information_schema.column_privileges; + +--error ER_DBACCESS_DENIED_ERROR +CREATE INDEX my_idx_on_tables +ON information_schema.column_privileges(table_schema); +--error ER_DBACCESS_DENIED_ERROR +ALTER TABLE information_schema.column_privileges ADD f1 INT; + +--error ER_DBACCESS_DENIED_ERROR +DROP TABLE information_schema.column_privileges; + +--error ER_DBACCESS_DENIED_ERROR +ALTER TABLE information_schema.column_privileges +RENAME db_datadict.column_privileges; +--error ER_DBACCESS_DENIED_ERROR +ALTER TABLE information_schema.column_privileges +RENAME information_schema.xcolumn_privileges; + +# Cleanup +DROP DATABASE db_datadict; +DROP USER 'testuser1'@'localhost'; + diff --git a/mysql-test/suite/funcs_1/t/is_column_privileges_is_mysql_test.test b/mysql-test/suite/funcs_1/t/is_column_privileges_is_mysql_test.test new file mode 100644 index 00000000000..3ed8597e309 --- /dev/null +++ b/mysql-test/suite/funcs_1/t/is_column_privileges_is_mysql_test.test @@ -0,0 +1,58 @@ +# suite/funcs_1/t/is_column_privileges_is_mysql_test.test +# +# Check the content of information_schema.column_privileges about the databases +# information_schema and mysql visible to high and low privileged users. +# +# 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. +# +# + +--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. +# +--disable_warnings +DROP DATABASE IF EXISTS db_datadict; +--enable_warnings +CREATE DATABASE db_datadict; + +# 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 db_datadict.* TO 'testuser1'@'localhost'; + +let $my_select = SELECT * FROM information_schema.column_privileges +WHERE table_schema IN ('information_schema','mysql','test') +ORDER BY table_schema, table_name, column_name; +let $my_show1 = SHOW DATABASES LIKE 'information_schema'; +let $my_show2 = SHOW DATABASES LIKE 'mysql'; +let $my_show3 = SHOW DATABASES LIKE 'test'; +eval $my_select; +eval $my_show1; +eval $my_show2; +eval $my_show3; + +--echo # Establish connection testuser1 (user=testuser1) +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +connect (testuser1, localhost, testuser1, , db_datadict); +eval $my_select; +eval $my_show1; +eval $my_show2; +eval $my_show3; + +# Cleanup +--echo # Switch to connection default and close connection testuser1 +connection default; +DROP USER 'testuser1'@'localhost'; +DROP DATABASE db_datadict; + diff --git a/mysql-test/suite/funcs_1/t/is_columns.test b/mysql-test/suite/funcs_1/t/is_columns.test new file mode 100644 index 00000000000..385de733ac3 --- /dev/null +++ b/mysql-test/suite/funcs_1/t/is_columns.test @@ -0,0 +1,444 @@ +# suite/funcs_1/t/is_columns.test +# +# Check the layout of information_schema.columns and the impact of +# CREATE/ALTER/DROP TABLE/VIEW/SCHEMA/COLUMN ... on its content. +# +# 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. +# +# 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. +# + +# --source suite/funcs_1/datadict/datadict.pre + +let $engine_type = MEMORY; +let $other_engine_type = MyISAM; + +let $is_table = COLUMNS; + +# The table INFORMATION_SCHEMA.COLUMNS 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.6.1: INFORMATION_SCHEMA.COLUMNS layout +--echo ######################################################################### +# Ensure that the INFORMATION_SCHEMA.COLUMNS 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 an accessible table), +# COLUMN_NAME (shows the name of a column within that table), +# ORDINAL_POSITION (shows the ordinal position of that column in that table), +# COLUMN_DEFAULT (shows the column's default value), +# IS_NULLABLE (shows whether the column may accept NULL values), +# DATA_TYPE (shows the column's defined data type; keyword only), +# CHARACTER_MAXIMUM_LENGTH (shows, for a string column, the column's defined +# maximum length in characters; otherwise NULL), +# CHARACTER_OCTET_LENGTH (shows, for a string column, the column's defined +# maximum length in octets; otherwise NULL), +# NUMERIC_PRECISION (shows, for a numeric column, the column's or data type's +# defined precision; otherwise NULL), +# NUMERIC_SCALE (shows, for a numeric column, the column's or data type's +# defined scale; otherwise NULL), +# CHARACTER_SET_NAME (shows, for a character string column, the column's default +# character set; otherwise NULL), +# COLLATION_NAME (shows, for a character string column, the column's default +# collation; otherwise NULL), +# COLUMN_TYPE (shows the column's complete, defined data type), +# COLUMN_KEY (shows whether the column is indexed; possible values are PRI if +# the column is part of a PRIMARY KEY, UNI if the column is part of a +# UNIQUE key, MUL if the column is part of an index key that allows +# duplicates), +# EXTRA (shows any additional column definition information, e.g. whether the +# column was defined with the AUTO_INCREMENT attribute), +# PRIVILEGES (shows the privileges available to the user on the column), +# COLUMN_COMMENT (shows the comment, if any, defined for the comment; +# 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.columns is in is_columns_is.test. + +# Show that TABLE_CATALOG is always NULL. +SELECT table_catalog, table_schema, table_name, column_name +FROM information_schema.columns WHERE table_catalog IS NOT NULL; + + +--echo ############################################################################### +--echo # Testcase 3.2.6.2 + 3.2.6.3: INFORMATION_SCHEMA.COLUMNS accessible information +--echo ############################################################################### +# 3.2.6.2: Ensure that the table shows the relevant information on the columns +# of every table that is accessible to the current user or to PUBLIC. +# 3.2.6.3: Ensure that the table does not show any information on the columns +# of any table which is not accessible to the current user or PUBLIC. +# +# Note: Check of content within information_schema.columns about +# databases is in +# mysql is_columns_mysql.test +# information_schema is_columns_is.test +# test% is_columns_<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'; +--error 0,ER_CANNOT_USER +DROP USER 'testuser2'@'localhost'; +CREATE USER 'testuser2'@'localhost'; + +--replace_result $other_engine_type <other_engine_type> +eval +CREATE TABLE db_datadict.t1 + (f1 CHAR(10), f2 TEXT, f3 DATE, f4 INT AUTO_INCREMENT, + UNIQUE INDEX MUL_IDX(f1,f3), PRIMARY KEY (f4)) +ENGINE = $other_engine_type; +CREATE VIEW db_datadict.v1 AS SELECT 1 AS f1, 1 AS f2; +GRANT SELECT(f1, f2) ON db_datadict.t1 TO 'testuser1'@'localhost'; +GRANT SELECT(f2) ON db_datadict.v1 TO 'testuser1'@'localhost'; + +--replace_result $other_engine_type <other_engine_type> +eval +CREATE TABLE db_datadict.t2 +(f1 CHAR(10), f2 TEXT, f3 DATE, f4 INT, PRIMARY KEY (f1,f4)) +ENGINE = $other_engine_type; +GRANT INSERT(f1, f2) ON db_datadict.t2 TO 'testuser2'@'localhost'; + +let $my_select= SELECT * FROM information_schema.columns + WHERE table_schema = 'db_datadict' +ORDER BY table_schema, table_name, ordinal_position; +let $my_show1 = SHOW COLUMNS FROM db_datadict.t1; +let $my_show2 = SHOW COLUMNS FROM db_datadict.t2; +let $my_show3 = SHOW COLUMNS FROM db_datadict.v1; + +# Point of view of user root. +--source suite/funcs_1/datadict/datadict_bug_12777.inc +eval $my_select; +eval $my_show1; +eval $my_show2; +eval $my_show3; + +--echo # Establish connection testuser1 (user=testuser1) +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +connect (testuser1, localhost, testuser1, , db_datadict); +--source suite/funcs_1/datadict/datadict_bug_12777.inc +eval $my_select; +eval $my_show1; +--error ER_TABLEACCESS_DENIED_ERROR +eval $my_show2; +eval $my_show3; + +--echo # Establish connection testuser2 (user=testuser2) +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +connect (testuser2, localhost, testuser2, , db_datadict); +--source suite/funcs_1/datadict/datadict_bug_12777.inc +eval $my_select; +--error ER_TABLEACCESS_DENIED_ERROR +eval $my_show1; +eval $my_show2; +--error ER_TABLEACCESS_DENIED_ERROR +eval $my_show3; + +--echo # Switch to connection default and close connections testuser1, testuser2 +connection default; +disconnect testuser1; +disconnect testuser2; + +# Cleanup +DROP USER 'testuser1'@'localhost'; +DROP USER 'testuser2'@'localhost'; +DROP DATABASE IF EXISTS db_datadict; + + +--echo ############################################################################### +--echo # Testcase 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.COLUMNS 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.columns +WHERE table_name LIKE 't1_my_table%'; +--replace_result $engine_type <engine_type> +eval +CREATE TABLE test.t1_my_table (f1 CHAR(12)) +DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci +ENGINE = $engine_type; +# Settings used in CREATE TABLE must be visible in information_schema.columns. +--vertical_results +SELECT * FROM information_schema.columns +WHERE table_name = 't1_my_table'; +--horizontal_results +# +# Check modification of TABLE_NAME +SELECT table_name FROM information_schema.columns +WHERE table_name LIKE 't1_my_table%'; +RENAME TABLE test.t1_my_table TO test.t1_my_tablex; +SELECT table_name FROM information_schema.columns +WHERE table_name LIKE 't1_my_table%'; +# +# Check modification of TABLE_SCHEMA +SELECT table_schema,table_name FROM information_schema.columns +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.columns +WHERE table_name = 't1_my_tablex'; +# +# Check modification of COLUMN_NAME +SELECT table_name, column_name FROM information_schema.columns +WHERE table_name = 't1_my_tablex'; +ALTER TABLE db_datadict.t1_my_tablex CHANGE COLUMN f1 first_col CHAR(12); +SELECT table_name, column_name FROM information_schema.columns +WHERE table_name = 't1_my_tablex'; +# +# Check modification of COLUMN size +SELECT table_name, column_name, character_maximum_length, + character_octet_length, column_type +FROM information_schema.columns +WHERE table_name = 't1_my_tablex'; +ALTER TABLE db_datadict.t1_my_tablex +MODIFY COLUMN first_col CHAR(20); +SELECT table_name, column_name, character_maximum_length, + character_octet_length, column_type +FROM information_schema.columns +WHERE table_name = 't1_my_tablex'; +# +# Check modification of COLUMN type +SELECT table_name, column_name, character_maximum_length, + character_octet_length, column_type +FROM information_schema.columns +WHERE table_name = 't1_my_tablex'; +ALTER TABLE db_datadict.t1_my_tablex +MODIFY COLUMN first_col VARCHAR(20); +SELECT table_name, column_name, character_maximum_length, + character_octet_length, column_type +FROM information_schema.columns +WHERE table_name = 't1_my_tablex'; +# +# Check modify COLUMN DEFAULT +SELECT table_name, column_name, column_default +FROM information_schema.columns +WHERE table_name = 't1_my_tablex'; +ALTER TABLE db_datadict.t1_my_tablex +MODIFY COLUMN first_col CHAR(10) DEFAULT 'hello'; +SELECT table_name, column_name, column_default +FROM information_schema.columns +WHERE table_name = 't1_my_tablex'; +# +# Check modify IS_NULLABLE +SELECT table_name, column_name, is_nullable +FROM information_schema.columns +WHERE table_name = 't1_my_tablex'; +ALTER TABLE db_datadict.t1_my_tablex +MODIFY COLUMN first_col CHAR(10) NOT NULL; +SELECT table_name, column_name, is_nullable +FROM information_schema.columns +WHERE table_name = 't1_my_tablex'; +# +# Check modify COLLATION +SELECT table_name, column_name, collation_name +FROM information_schema.columns +WHERE table_name = 't1_my_tablex'; +ALTER TABLE db_datadict.t1_my_tablex +MODIFY COLUMN first_col CHAR(10) COLLATE 'latin1_general_cs'; +SELECT table_name, column_name, collation_name +FROM information_schema.columns +WHERE table_name = 't1_my_tablex'; +# +# Check modify CHARACTER SET +SELECT table_name, column_name, character_maximum_length, + character_octet_length, character_set_name +FROM information_schema.columns +WHERE table_name = 't1_my_tablex'; +ALTER TABLE db_datadict.t1_my_tablex +MODIFY COLUMN first_col CHAR(10) CHARACTER SET utf8; +SELECT table_name, column_name, character_maximum_length, + character_octet_length, character_set_name +FROM information_schema.columns +WHERE table_name = 't1_my_tablex'; +# +# Check modify COLUMN_COMMENT +SELECT table_name, column_name, column_comment +FROM information_schema.columns +WHERE table_name = 't1_my_tablex'; +ALTER TABLE db_datadict.t1_my_tablex +MODIFY COLUMN first_col CHAR(10) COMMENT 'Hello'; +SELECT table_name, column_name, column_comment +FROM information_schema.columns +WHERE table_name = 't1_my_tablex'; +# +# Check ADD COLUMN +SELECT table_name, column_name +FROM information_schema.columns +WHERE table_name = 't1_my_tablex'; +ALTER TABLE db_datadict.t1_my_tablex +ADD COLUMN second_col CHAR(10); +SELECT table_name, column_name +FROM information_schema.columns +WHERE table_name = 't1_my_tablex'; +# +# Check switch ordinal position of column +SELECT table_name, column_name, ordinal_position +FROM information_schema.columns +WHERE table_name = 't1_my_tablex' +ORDER BY table_name, column_name; +ALTER TABLE db_datadict.t1_my_tablex +MODIFY COLUMN second_col CHAR(10) FIRST; +SELECT table_name, column_name, ordinal_position +FROM information_schema.columns +WHERE table_name = 't1_my_tablex' +ORDER BY table_name, column_name; +# +# Check DROP COLUMN +SELECT table_name, column_name +FROM information_schema.columns +WHERE table_name = 't1_my_tablex'; +ALTER TABLE db_datadict.t1_my_tablex +DROP COLUMN first_col; +SELECT table_name, column_name +FROM information_schema.columns +WHERE table_name = 't1_my_tablex'; +# +# Check set COLUMN UNIQUE +SELECT table_name, column_name, column_key +FROM information_schema.columns +WHERE table_name = 't1_my_tablex'; +ALTER TABLE db_datadict.t1_my_tablex +ADD UNIQUE INDEX IDX(second_col); +SELECT table_name, column_name, column_key +FROM information_schema.columns +WHERE table_name = 't1_my_tablex'; +# +# Check impact of DROP TABLE +SELECT table_name, column_name +FROM information_schema.columns +WHERE table_name = 't1_my_tablex'; +DROP TABLE db_datadict.t1_my_tablex; +SELECT table_name, column_name +FROM information_schema.columns +WHERE table_name = 't1_my_tablex'; +# +# Check a VIEW +CREATE VIEW test.t1_my_tablex +AS SELECT 1 AS "col1", 'A' collate latin1_german1_ci AS "col2"; +--vertical_results +SELECT * FROM information_schema.columns +WHERE table_name = 't1_my_tablex' +ORDER BY table_name, column_name; +--horizontal_results +DROP VIEW test.t1_my_tablex; +SELECT table_name FROM information_schema.columns +WHERE table_name = '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.columns +WHERE table_name = 't1_my_tablex'; +DROP DATABASE db_datadict; +SELECT table_name FROM information_schema.columns +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 test.t1; +--enable_warnings +CREATE DATABASE db_datadict; +CREATE TABLE test.t1 (f1 BIGINT); + +--error ER_DBACCESS_DENIED_ERROR +INSERT INTO information_schema.columns (table_schema,table_name,column_name) +VALUES('test','t1', 'f2'); +--error ER_DBACCESS_DENIED_ERROR +INSERT INTO information_schema.columns (table_schema,table_name,column_name) +VALUES('test','t2', 'f1'); + +--error ER_DBACCESS_DENIED_ERROR +UPDATE information_schema.columns SET table_name = 't4' WHERE table_name = 't1'; + +--error ER_DBACCESS_DENIED_ERROR +DELETE FROM information_schema.columns WHERE table_name = 't1'; +--error ER_DBACCESS_DENIED_ERROR +TRUNCATE information_schema.columns; + +--error ER_DBACCESS_DENIED_ERROR +CREATE INDEX i3 ON information_schema.columns(table_name); + +--error ER_DBACCESS_DENIED_ERROR +ALTER TABLE information_schema.columns ADD f1 INT; + +--error ER_DBACCESS_DENIED_ERROR +DROP TABLE information_schema.columns; + +--error ER_DBACCESS_DENIED_ERROR +ALTER TABLE information_schema.columns RENAME db_datadict.columns; +--error ER_DBACCESS_DENIED_ERROR +ALTER TABLE information_schema.columns RENAME information_schema.xcolumns; + +# Cleanup +DROP TABLE test.t1; +DROP DATABASE db_datadict; + diff --git a/mysql-test/suite/funcs_1/t/is_columns_innodb.test b/mysql-test/suite/funcs_1/t/is_columns_innodb.test new file mode 100644 index 00000000000..04b9f8354e4 --- /dev/null +++ b/mysql-test/suite/funcs_1/t/is_columns_innodb.test @@ -0,0 +1,21 @@ +# suite/funcs_1/t/is_columns_innodb.test +# +# Check the content of information_schema.columns about tables within +# the databases created by the user. +# Variant for storage engine InnoDB +# +# 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. +# + +--source include/have_innodb.inc +let $engine_type= InnoDB; +--source suite/funcs_1/datadict/datadict_load.inc + +# We look only for the tables created by datadict_load.inc. +let $my_where = WHERE table_schema LIKE 'test%'; +--source suite/funcs_1/datadict/columns.inc + +--source suite/funcs_1/include/cleanup.inc diff --git a/mysql-test/suite/funcs_1/t/is_columns_is.test b/mysql-test/suite/funcs_1/t/is_columns_is.test new file mode 100644 index 00000000000..41a7d180be8 --- /dev/null +++ b/mysql-test/suite/funcs_1/t/is_columns_is.test @@ -0,0 +1,19 @@ +# suite/funcs_1/t/is_columns_is.test +# +# Check the content of information_schema.columns about tables within +# the database information_schema. +# +# Note: The INFORMATION_SCHEMA table PROFILING is optional (exists in MySQL +# Community version only) and therefore we exclude it from retrieval. +# +# 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. +# + +let $my_where = WHERE table_schema = 'information_schema' +AND table_name <> 'profiling'; +# --source suite/funcs_1/datadict/datadict.pre +--source suite/funcs_1/datadict/columns.inc + diff --git a/mysql-test/suite/funcs_1/t/is_columns_memory.test b/mysql-test/suite/funcs_1/t/is_columns_memory.test new file mode 100644 index 00000000000..6cbf3b298b4 --- /dev/null +++ b/mysql-test/suite/funcs_1/t/is_columns_memory.test @@ -0,0 +1,21 @@ +# suite/funcs_1/t/is_columns_memory.test +# +# Check the content of information_schema.columns about tables within +# the databases created by the user. +# Variant for storage engine MEMORY +# +# 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. +# + +let $engine_type= MEMORY; +SET @@session.sql_mode = 'NO_ENGINE_SUBSTITUTION'; +--source suite/funcs_1/datadict/datadict_load.inc + +# We look only for the tables created by datadict_load.inc. +let $my_where = WHERE table_schema LIKE 'test%'; +--source suite/funcs_1/datadict/columns.inc + +--source suite/funcs_1/include/cleanup.inc diff --git a/mysql-test/suite/funcs_1/t/is_columns_myisam.test b/mysql-test/suite/funcs_1/t/is_columns_myisam.test new file mode 100644 index 00000000000..d98cd0347c6 --- /dev/null +++ b/mysql-test/suite/funcs_1/t/is_columns_myisam.test @@ -0,0 +1,21 @@ +# suite/funcs_1/t/is_columns_myisam.test +# +# Check the content of information_schema.columns about tables within +# the databases created by the user. +# Variant for storage engine MyISAM +# +# 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. +# + +let $engine_type= MyISAM; +SET @@session.sql_mode = 'NO_ENGINE_SUBSTITUTION'; +--source suite/funcs_1/datadict/datadict_load.inc + +# We look only for the tables created by datadict_load.inc. +let $my_where = WHERE table_schema LIKE 'test%'; +--source suite/funcs_1/datadict/columns.inc + +--source suite/funcs_1/include/cleanup.inc diff --git a/mysql-test/suite/funcs_1/t/is_columns_mysql.test b/mysql-test/suite/funcs_1/t/is_columns_mysql.test new file mode 100644 index 00000000000..26539d0c8e0 --- /dev/null +++ b/mysql-test/suite/funcs_1/t/is_columns_mysql.test @@ -0,0 +1,13 @@ +# suite/funcs_1/t/is_columns_mysql.test +# +# Check the content of information_schema.columns about tables within +# the database mysql. +# +# 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. +# + +let $my_where = WHERE table_schema = 'mysql'; +--source suite/funcs_1/datadict/columns.inc diff --git a/mysql-test/suite/funcs_1/t/is_columns_ndb.test b/mysql-test/suite/funcs_1/t/is_columns_ndb.test new file mode 100644 index 00000000000..960e5f079bb --- /dev/null +++ b/mysql-test/suite/funcs_1/t/is_columns_ndb.test @@ -0,0 +1,33 @@ +# suite/funcs_1/t/is_columns_ndb.test +# +# Check the content of information_schema.columns about tables within +# the databases created by the user. +# Variant for storage engine ndb +# +# 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. +# + +--source include/have_ndb.inc +let $engine_type= ndb; +--source suite/funcs_1/datadict/datadict_load.inc + +# We look only for the tables created by datadict_load.inc. +let $my_where = WHERE table_schema LIKE 'test%'; +--source suite/funcs_1/datadict/columns.inc + +# This test runs with a different set of tables. +# --source suite/funcs_1/include/cleanup.inc +DROP DATABASE test1; +DROP DATABASE test4; +DROP TABLE test.t1; +DROP TABLE test.t2; +DROP TABLE test.t3; +DROP TABLE test.t4; +DROP TABLE test.t7; +DROP TABLE test.t8; +DROP TABLE test.t9; +DROP TABLE test.t10; +DROP TABLE test.t11; diff --git a/mysql-test/suite/funcs_1/t/is_key_column_usage.test b/mysql-test/suite/funcs_1/t/is_key_column_usage.test new file mode 100644 index 00000000000..219277c8645 --- /dev/null +++ b/mysql-test/suite/funcs_1/t/is_key_column_usage.test @@ -0,0 +1,344 @@ +# suite/funcs_1/t/is_key_column_usage.test +# +# 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 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. +# + +# --source suite/funcs_1/datadict/datadict.pre + +let $engine_type = MEMORY; +# let $other_engine_type = MyISAM; + +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; + +--error ER_DBACCESS_DENIED_ERROR +INSERT INTO information_schema.key_column_usage + (constraint_schema, constraint_name, table_name) +VALUES ( 'mysql', 'primary', 'db'); +--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; + +# Cleanup +DROP TABLE db_datadict.t1; +DROP DATABASE db_datadict; + diff --git a/mysql-test/suite/funcs_1/t/is_routines.test b/mysql-test/suite/funcs_1/t/is_routines.test new file mode 100644 index 00000000000..5f015b8624b --- /dev/null +++ b/mysql-test/suite/funcs_1/t/is_routines.test @@ -0,0 +1,471 @@ +# suite/funcs_1/t/is_routines.test +# +# 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. +# + +# --source suite/funcs_1/datadict/datadict.pre + +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). +# +--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; + +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; + +--error ER_DBACCESS_DENIED_ERROR +INSERT INTO information_schema.routines (routine_name, routine_type ) +VALUES ('p2', 'procedure'); + +--error ER_DBACCESS_DENIED_ERROR +UPDATE information_schema.routines SET routine_name = 'p2' +WHERE routine_body = 'sql'; + +--error ER_DBACCESS_DENIED_ERROR +DELETE FROM information_schema.routines ; +# +--error ER_DBACCESS_DENIED_ERROR +TRUNCATE information_schema.routines ; + +--error ER_DBACCESS_DENIED_ERROR +CREATE INDEX i7 ON information_schema.routines (routine_name); + +--error ER_DBACCESS_DENIED_ERROR +ALTER TABLE information_schema.routines ADD f1 INT; +# +--error ER_DBACCESS_DENIED_ERROR +ALTER TABLE information_schema.routines DISCARD TABLESPACE; + +--error ER_DBACCESS_DENIED_ERROR +DROP TABLE information_schema.routines ; + +--error ER_DBACCESS_DENIED_ERROR +ALTER TABLE information_schema.routines RENAME db_datadict.routines; +# +--error ER_DBACCESS_DENIED_ERROR +ALTER TABLE information_schema.routines RENAME information_schema.xroutines; + +# Cleanup +DROP DATABASE db_datadict; + diff --git a/mysql-test/suite/funcs_1/t/is_schema_privileges.test b/mysql-test/suite/funcs_1/t/is_schema_privileges.test new file mode 100644 index 00000000000..4eb4a273362 --- /dev/null +++ b/mysql-test/suite/funcs_1/t/is_schema_privileges.test @@ -0,0 +1,336 @@ +# suite/funcs_1/t/is_schema_privileges.test +# +# Check the layout of information_schema.schema_privileges 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 schemas +# 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. +# + +# --source suite/funcs_1/datadict/datadict.pre + +let $engine_type = MEMORY; +let $other_engine_type = MyISAM; + +let $is_table = SCHEMA_PRIVILEGES; + +# The table INFORMATION_SCHEMA.SCHEMA_PRIVILEGES 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.15.1: INFORMATION_SCHEMA.SCHEMA_PRIVILEGES layout +--echo ######################################################################### +# Ensure that the INFORMATION_SCHEMA.SCHEMA_PRIVILEGES table has the following +# columns, in the following order: +# +# GRANTEE (shows a user to whom a schema privilege has been granted), +# TABLE_CATALOG (always shows NULL), +# TABLE_SCHEMA (shows the name of the database, or schema, on which the +# privilege has been granted), +# PRIVILEGE_TYPE (shows the granted privilege), +# IS_GRANTABLE (shows whether the privilege was granted WITH GRANT OPTION) +# +--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.schema_privileges is in is_columns_is.test. + +# Show that TABLE_CATALOG is always NULL. +SELECT GRANTEE, TABLE_CATALOG, TABLE_SCHEMA, PRIVILEGE_TYPE +FROM information_schema.schema_privileges WHERE table_catalog IS NOT NULL; + + +--echo ############################################################################### +--echo # Testcase 3.2.15.2-3.2.15.4 INFORMATION_SCHEMA.SCHEMA_PRIVILEGES accessibility +--echo ############################################################################### +# 3.2.15.2 Ensure that the table shows the relevant information on every +# schema-level privilege which has been granted to the current user +# or to PUBLIC, or has been granted by the current user. +# FIXME: Why is "or has been granted by the current user" invisible? +# 3.2.15.3 Ensure that the table does not show any information on any +# schema-level privileges which have been granted to users +# other than the current user or to PUBLIC, or that have been +# granted by any user other than the current user. +# 3.2.15.4 Ensure that the table does not show any information on any +# privileges that are not schema-level privileges for the +# current user. +# +# Note: Check of content within information_schema.schema_privileges about the +# databases information_schema, mysql and test is in +# is_schema_privileges_is_mysql_test. +# +--disable_warnings +DROP DATABASE IF EXISTS db_datadict_1; +DROP DATABASE IF EXISTS db_datadict_2; +DROP DATABASE IF EXISTS db_datadict_3; +--enable_warnings +CREATE DATABASE db_datadict_1; +CREATE DATABASE db_datadict_2; +CREATE DATABASE db_datadict_3; +eval +CREATE TABLE db_datadict_2.t1(f1 INT, f2 INT, f3 INT) +ENGINE = $engine_type; + +--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'; + +GRANT INSERT ON db_datadict_1.* TO 'testuser1'@'localhost'; +GRANT INSERT ON db_datadict_2.t1 TO 'testuser1'@'localhost'; +GRANT SELECT ON db_datadict_4.* TO 'testuser1'@'localhost' WITH GRANT OPTION; +GRANT SELECT ON db_datadict_3.* TO 'testuser2'@'localhost'; +GRANT SELECT ON db_datadict_1.* TO 'testuser2'@'localhost'; + +let $my_select = SELECT * FROM information_schema.schema_privileges +WHERE table_schema LIKE 'db_datadict%' +ORDER BY grantee,table_schema,privilege_type; +let $show_testuser1 = SHOW GRANTS FOR 'testuser1'@'localhost'; +let $show_testuser2 = SHOW GRANTS FOR 'testuser2'@'localhost'; + +--echo # Establish connection testuser1 (user=testuser1) +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +connect (testuser1, localhost, testuser1, , test); +GRANT SELECT ON db_datadict_4.* TO 'testuser2'@'localhost'; +--echo # Root granted INSERT db_datadict_1 to me -> visible +--echo # Root granted SELECT db_datadict_1 to testuser2 -> invisible +--echo # Root granted INSERT db_datadict_2.t1 (no schema-level priv!) +--echo # but not db_datadict_2 to me -> invisible +--echo # Root granted SELECT db_datadict_3. to testuser2 but not to me -> invisible +--echo # Root granted SELECT db_datadict_4. to me -> visible +--echo # I granted SELECT db_datadict_4. to testuser2 -> invisible (reality), visible(requirement) +--echo # FIXME +eval $my_select; +eval $show_testuser1; +--error ER_DBACCESS_DENIED_ERROR +eval $show_testuser2; + +--echo # Establish connection testuser2 (user=testuser2) +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +connect (testuser2, localhost, testuser2, , test); +--echo # Root granted SELECT db_datadict_1 to me -> visible +--echo # Root granted INSERT db_datadict_1 to testuser1 -> invisible +--echo # Root granted INSERT db_datadict_2.t1 but not db_datadict_1 to testuser1 -> invisible +--echo # Root granted SELECT db_datadict_3. to me -> visible +--echo # testuser1 granted SELECT db_datadict_4. to me -> visible +eval $my_select; +--error ER_DBACCESS_DENIED_ERROR +eval $show_testuser1; +eval $show_testuser2; + +--echo # Switch to connection default and close connections testuser1 and testuser2 +connection default; +disconnect testuser1; +disconnect testuser2; +eval $my_select; +eval $show_testuser1; +eval $show_testuser2; + +# Cleanup +DROP USER 'testuser1'@'localhost'; +DROP USER 'testuser2'@'localhost'; +DROP DATABASE db_datadict_1; +DROP DATABASE db_datadict_2; +DROP DATABASE db_datadict_3; + + +--echo ################################################################################ +--echo # 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.SCHEMA_PRIVILEGES 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. +# +# Note (mleich): +# The MySQL privilege system allows to GRANT objects before they exist. +# (Exception: Grant privileges for columns of not existing tables/views.) +# There is also no migration of privileges if objects (tables, views, columns) +# are moved to other databases (tables only), renamed or dropped. +# +--disable_warnings +DROP DATABASE IF EXISTS db_datadict; +--enable_warnings +CREATE DATABASE db_datadict; +--error 0,ER_CANNOT_USER +DROP USER 'the_user'@'localhost'; +--error 0,ER_CANNOT_USER +DROP USER 'testuser1'@'localhost'; +CREATE USER 'testuser1'@'localhost'; +GRANT SELECT ON test.* TO 'testuser1'@'localhost'; + +let $my_select = SELECT * FROM information_schema.schema_privileges +WHERE table_schema = 'db_datadict' +ORDER BY grantee,table_schema,privilege_type; + +############ Check grant SCHEMA +eval $my_select; +--echo # Establish connection testuser1 (user=testuser1) +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +connect (testuser1, localhost, testuser1, , test); +eval $my_select; +--echo # Switch to connection default +connection default; +GRANT UPDATE ON db_datadict.* TO 'testuser1'@'localhost'; +eval $my_select; +--echo # Switch to connection testuser1 +eval $my_select; +############ Check RENAME SCHEMA +# Implement this if RENAME SCHEMA is again available. +# Note(mleich): I expect that RENAME has no impact on the result sets, because +# the schema_name is not migrated. +# --echo # Switch to connection default +# connection default; +# RENAME SCHEMA db_datadict TO db_datadictx; +# eval $my_select; +# --echo # Switch to connection testuser1 +# eval $my_select; +# RENAME SCHEMA db_datadictx TO db_datadict; +############ Check extend PRIVILEGES (affects PRIVILEGE_TYPE) on SCHEMA +--echo # Switch to connection default +connection default; +GRANT SELECT ON db_datadict.* TO 'testuser1'@'localhost'; +eval $my_select; +--echo # Switch to connection testuser1 +eval $my_select; +############ Check extend PRIVILEGES (affects IS_GRANTABLE) on SCHEMA +--echo # Switch to connection default +connection default; +GRANT SELECT ON db_datadict.* TO 'testuser1'@'localhost' WITH GRANT OPTION; +eval $my_select; +--echo # Switch to connection testuser1 +eval $my_select; +############ Check DROP SCHEMA +# No impact, because there is no "maintenance" of privileges. +--echo # Switch to connection default +connection default; +DROP SCHEMA db_datadict; +eval $my_select; +--echo # Switch to connection testuser1 +eval $my_select; +############ Check REVOKE PRIVILEGE +--echo # Switch to connection default +connection default; +REVOKE UPDATE ON db_datadict.* FROM 'testuser1'@'localhost'; +eval $my_select; +--echo # Switch to connection testuser1 +eval $my_select; +############ Check RENAME USER +--echo # Switch to connection default +connection default; +RENAME USER 'testuser1'@'localhost' TO 'the_user'@'localhost'; +eval $my_select; +--echo # Switch to connection testuser1 +eval $my_select; +--echo # Close connection testuser1 +disconnect testuser1; +--echo # Establish connection the_user (user=the_user) +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +connect (the_user, localhost, the_user, , test); +eval $my_select; +--echo # Close connection the_user +disconnect the_user; +############ Check DROP USER +--echo # Switch to connection default +connection default; +eval $my_select; +DROP USER 'the_user'@'localhost'; +eval $my_select; + + +--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; +--replace_result $engine_type <engine_type> +eval +CREATE TABLE db_datadict.t1 (f1 BIGINT, f2 BIGINT) +ENGINE = $engine_type; +--error 0,ER_CANNOT_USER +DROP USER 'testuser1'@'localhost'; +CREATE USER 'testuser1'@'localhost'; +GRANT SELECT ON db_datadict.* TO 'testuser1'@'localhost'; + +--error ER_DBACCESS_DENIED_ERROR +INSERT INTO information_schema.schema_privileges +SELECT * FROM information_schema.schema_privileges; + +--error ER_DBACCESS_DENIED_ERROR +UPDATE information_schema.schema_privileges SET table_schema = 'test' +WHERE table_name = 't1'; + +--error ER_DBACCESS_DENIED_ERROR +DELETE FROM information_schema.schema_privileges +WHERE table_schema = 'db_datadict'; +--error ER_DBACCESS_DENIED_ERROR +TRUNCATE information_schema.schema_privileges; + +--error ER_DBACCESS_DENIED_ERROR +CREATE INDEX my_idx_on_tables +ON information_schema.schema_privileges(table_schema); +--error ER_DBACCESS_DENIED_ERROR +ALTER TABLE information_schema.schema_privileges ADD f1 INT; + +--error ER_DBACCESS_DENIED_ERROR +DROP TABLE information_schema.schema_privileges; + +--error ER_DBACCESS_DENIED_ERROR +ALTER TABLE information_schema.schema_privileges +RENAME db_datadict.schema_privileges; +--error ER_DBACCESS_DENIED_ERROR +ALTER TABLE information_schema.schema_privileges +RENAME information_schema.xschema_privileges; + +# Cleanup +DROP DATABASE db_datadict; +DROP USER 'testuser1'@'localhost'; + diff --git a/mysql-test/suite/funcs_1/t/is_schema_privileges_is_mysql_test.test b/mysql-test/suite/funcs_1/t/is_schema_privileges_is_mysql_test.test new file mode 100644 index 00000000000..cb34d1bad2d --- /dev/null +++ b/mysql-test/suite/funcs_1/t/is_schema_privileges_is_mysql_test.test @@ -0,0 +1,58 @@ +# suite/funcs_1/t/is_schema_privileges_is_mysql_test.test +# +# Check the content of information_schema.schema_privileges about the databases +# information_schema, mysql and test visible to high and low privileged users. +# +# 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. +# +# + +--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. +# +--disable_warnings +DROP DATABASE IF EXISTS db_datadict; +--enable_warnings +CREATE DATABASE db_datadict; + +# 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 db_datadict.* TO 'testuser1'@'localhost'; + +let $my_select = SELECT * FROM information_schema.schema_privileges +WHERE table_schema IN ('information_schema','mysql','test') +ORDER BY grantee, table_schema, privilege_type; +let $my_show1 = SHOW DATABASES LIKE 'information_schema'; +let $my_show2 = SHOW DATABASES LIKE 'mysql'; +let $my_show3 = SHOW DATABASES LIKE 'test'; +eval $my_select; +eval $my_show1; +eval $my_show2; +eval $my_show3; + +--echo # Establish connection testuser1 (user=testuser1) +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +connect (testuser1, localhost, testuser1, , db_datadict); +eval $my_select; +eval $my_show1; +eval $my_show2; +eval $my_show3; + +# Cleanup +--echo # Switch to connection default and close connection testuser1 +connection default; +DROP USER 'testuser1'@'localhost'; +DROP DATABASE db_datadict; + diff --git a/mysql-test/suite/funcs_1/t/is_schemata.test b/mysql-test/suite/funcs_1/t/is_schemata.test new file mode 100644 index 00000000000..7eb08dba463 --- /dev/null +++ b/mysql-test/suite/funcs_1/t/is_schemata.test @@ -0,0 +1,246 @@ +# suite/funcs_1/t/is_schemata.test +# +# 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. +# + +# --source suite/funcs_1/datadict/datadict.pre + +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'; + +--error ER_DBACCESS_DENIED_ERROR +INSERT INTO information_schema.schemata + (catalog_name, schema_name, default_character_set_name, sql_path) +VALUES (NULL, 'db1', 'latin1', NULL); +--error ER_DBACCESS_DENIED_ERROR +INSERT INTO information_schema.schemata +SELECT * FROM information_schema.schemata; + +--error ER_DBACCESS_DENIED_ERROR +UPDATE information_schema.schemata +SET default_character_set_name = 'utf8' +WHERE schema_name = 'db_datadict'; +--error ER_DBACCESS_DENIED_ERROR +UPDATE information_schema.schemata SET catalog_name = 't_4711'; + +--error ER_DBACCESS_DENIED_ERROR +DELETE FROM information_schema.schemata WHERE schema_name = 'db_datadict'; +--error ER_DBACCESS_DENIED_ERROR +TRUNCATE information_schema.schemata; + +--error ER_DBACCESS_DENIED_ERROR +CREATE INDEX i1 ON information_schema.schemata(schema_name); + +--error ER_DBACCESS_DENIED_ERROR +ALTER TABLE information_schema.schemata ADD f1 INT; + +--error ER_DBACCESS_DENIED_ERROR +DROP TABLE information_schema.schemata; + +--error ER_DBACCESS_DENIED_ERROR +ALTER TABLE information_schema.schemata RENAME db_datadict.schemata; +--error ER_DBACCESS_DENIED_ERROR +ALTER TABLE information_schema.schemata RENAME information_schema.xschemata; + +# Cleanup +DROP DATABASE db_datadict; + diff --git a/mysql-test/suite/funcs_1/t/is_schemata_is_mysql_test.test b/mysql-test/suite/funcs_1/t/is_schemata_is_mysql_test.test new file mode 100644 index 00000000000..cabcfa640d7 --- /dev/null +++ b/mysql-test/suite/funcs_1/t/is_schemata_is_mysql_test.test @@ -0,0 +1,58 @@ +# suite/funcs_1/t/is_schemata_is_mysql_test.test +# +# Check the content of information_schema.schemata about the databases +# information_schema and mysql visible to high and low privileged users. +# +# 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. +# +# + +--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. +# +--disable_warnings +DROP DATABASE IF EXISTS db_datadict; +--enable_warnings +CREATE DATABASE db_datadict; + +# 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 db_datadict.* TO 'testuser1'@'localhost'; + +let $my_select = SELECT * FROM information_schema.schemata +WHERE schema_name IN ('information_schema','mysql','test') +ORDER BY schema_name; +let $my_show1 = SHOW DATABASES LIKE 'information_schema'; +let $my_show2 = SHOW DATABASES LIKE 'mysql'; +let $my_show3 = SHOW DATABASES LIKE 'test'; +eval $my_select; +eval $my_show1; +eval $my_show2; +eval $my_show3; + +--echo # Establish connection testuser1 (user=testuser1) +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +connect (testuser1, localhost, testuser1, , db_datadict); +eval $my_select; +eval $my_show1; +eval $my_show2; +eval $my_show3; + +# Cleanup +--echo # Switch to connection default and close connection testuser1 +connection default; +DROP USER 'testuser1'@'localhost'; +DROP DATABASE db_datadict; + diff --git a/mysql-test/suite/funcs_1/t/is_statistics.test b/mysql-test/suite/funcs_1/t/is_statistics.test new file mode 100644 index 00000000000..5b17b711fff --- /dev/null +++ b/mysql-test/suite/funcs_1/t/is_statistics.test @@ -0,0 +1,379 @@ +# suite/funcs_1/t/is_statistics.test +# +# Check the layout of information_schema.statistics and the impact of +# CREATE/ALTER/DROP TABLE/VIEW/SCHEMA ... on its content. +# +# 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. +# +# 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. +# + +# --source suite/funcs_1/datadict/datadict.pre + +let $engine_type = MEMORY; +let $other_engine_type = MyISAM; + +let $is_table = STATISTICS; + +# The table INFORMATION_SCHEMA.STATISTICS 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.14.1: INFORMATION_SCHEMA.STATISTICS layout +--echo ######################################################################### +# Ensure that the INFORMATION_SCHEMA.STATISTICS table has the following columns, +# in the following order: +# +# TABLE_CATALOG (always shows NULL), +# TABLE_SCHEMA (shows the database, or schema, in which a table indexed by +# an accessible index resides), +# TABLE_NAME (shows the name of the indexed table), +# NON_UNIQUE (shows whether the index may contain duplicate values; +# 0 if it cannot, 1 if it can), +# INDEX_SCHEMA (shows the database, or schema, in which an accessible +# index resides), +# INDEX_NAME (shows the name of an index which the current user may access), +# SEQ_IN_INDEX (shows the ordinal position of an indexed column within +# the index), +# COLUMN_NAME (shows the name of a column that comprises some, or all, of an +# index key), +# COLLATION (shows how the column is sorted in the index; either A for ascending +# or NULL for unsorted columns), +# CARDINALITY (shows the number of unique values in the index), +# SUB_PART (shows the number of indexed characters if the index is +# a prefix index), +# PACKED (shows how the index key is packed), +# NULLABLE (shows whether the index column may contain NULL values), +# INDEX_TYPE (shows the index type; either BTREE, FULLTEXT, HASH or RTREE), +# COMMENT (shows a comment on the index, if any). +# +--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.statistics is in is_columns_is.test. + +# Show that TABLE_CATALOG is always NULL. +SELECT table_catalog, table_schema, table_name, index_schema, index_name +FROM information_schema.statistics WHERE table_catalog IS NOT NULL; + + +--echo #################################################################################### +--echo # Testcase 3.2.14.2 + 3.2.14.3: INFORMATION_SCHEMA.STATISTICS accessible information +--echo #################################################################################### +# 3.2.14.2 Ensure that the table shows the relevant information on every index +# which the current user or PUBLIC may access (usually because +# privileges on the indexed table have been granted). +# 3.2.14.3 Ensure that the table does not show any information on any indexes +# which the current user and PUBLIC may not access. +# +# Note: Check of content within information_schema.statistics about +# database is in +# mysql is_statistics_mysql.test +# information_schema is_statistics_is.test +# +--disable_warnings +DROP DATABASE IF EXISTS db_datadict; +DROP DATABASE IF EXISTS db_datadict_2; +--enable_warnings +CREATE DATABASE db_datadict; +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'; + +--replace_result $engine_type <engine_type> +eval +CREATE TABLE db_datadict.t1 + (f1 INT NOT NULL, PRIMARY KEY(f1), f2 INT, INDEX f2_ind(f2)) +ENGINE = $engine_type; +--replace_result $engine_type <engine_type> +eval +CREATE TABLE db_datadict.t2 + (f1 INT NOT NULL, PRIMARY KEY(f1), f2 INT, INDEX f2_ind(f2)) +ENGINE = $engine_type; + +eval +CREATE TABLE db_datadict_2.t3 + (f1 INT NOT NULL, f2 INT, f5 DATE, + PRIMARY KEY(f1), INDEX f2f1_ind(f2,f1), UNIQUE(f5)) +ENGINE = $engine_type; +eval +CREATE TABLE db_datadict_2.t4 + (f1 INT NOT NULL, PRIMARY KEY(f1), f2 INT, INDEX f2_ind(f2)) +ENGINE = $engine_type; + +let $my_select = SELECT * FROM information_schema.statistics +WHERE table_schema LIKE 'db_datadict%' +ORDER BY table_schema,table_name,index_name,seq_in_index,column_name; +let $my_show1 = SHOW GRANTS FOR 'testuser1'@'localhost'; +let $my_show2 = SHOW GRANTS FOR 'testuser2'@'localhost'; +eval $my_select; +eval $my_show1; +eval $my_show2; + +--echo # Establish connection testuser1 (user=testuser1) +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +connect (testuser1, localhost, testuser1, , test); +# nothing visible for testuser1 +eval $my_select; +eval $my_show1; +--error ER_DBACCESS_DENIED_ERROR +eval $my_show2; + +--echo # Establish connection testuser2 (user=testuser2) +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +connect (testuser2, localhost, testuser2, , test); +# nothing visible for testuser2 +eval $my_select; +--error ER_DBACCESS_DENIED_ERROR +eval $my_show1; +eval $my_show2; + +--echo # Switch to connection default +connection default; +GRANT SELECT ON db_datadict.t1 TO 'testuser1'@'localhost' WITH GRANT OPTION; +GRANT SELECT(f1,f5) ON db_datadict_2.t3 TO 'testuser1'@'localhost'; +eval $my_select; +eval $my_show1; +eval $my_show2; + +--echo # Switch to connection testuser1 +connection testuser1; +eval $my_select; +eval $my_show1; +--error ER_DBACCESS_DENIED_ERROR +eval $my_show2; + +--echo # Switch to connection testuser2 +connection testuser2; +eval $my_select; +--error ER_DBACCESS_DENIED_ERROR +eval $my_show1; +eval $my_show2; + +--echo # Switch to connection default +connection default; +REVOKE SELECT,GRANT OPTION ON db_datadict.t1 FROM 'testuser1'@'localhost'; +eval $my_show1; + +--echo # Switch to connection testuser1 +# nothing visible for testuser1 +connection testuser1; +eval $my_select; +eval $my_show1; + +# 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 DATABASE db_datadict; +DROP DATABASE db_datadict_2; + + +--echo ######################################################################### +--echo # 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.STATISTICS 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 $other_engine_type <other_engine_type> +eval +CREATE TABLE test.t1_1 (f1 BIGINT, + f2 TEXT, f2x TEXT, f3 CHAR(10), f3x CHAR(10), f4 BIGINT, f4x BIGINT, + f5 POINT, f5x POINT NOT NULL) +DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci +ENGINE = $other_engine_type; +--replace_result $engine_type <engine_type> +eval +CREATE TABLE test.t1_2 (f1 BIGINT, f2 BIGINT) +ENGINE = $engine_type; + +# Tables without primary key or index do not show up +# in information_schema.statistics. +SELECT table_name FROM information_schema.statistics +WHERE table_name LIKE 't1_%'; +# Check ADD PRIMARY KEY (two columns) +ALTER TABLE test.t1_1 ADD PRIMARY KEY (f1,f3); +SELECT * FROM information_schema.statistics +WHERE table_name LIKE 't1_%' +ORDER BY table_schema,table_name,index_name,seq_in_index,column_name; +# Check DROP PRIMARY KEY +ALTER TABLE test.t1_1 DROP PRIMARY KEY; +SELECT table_name FROM information_schema.statistics +WHERE table_name LIKE 't1_%'; +# Check ADD PRIMARY KEY (one column) +ALTER TABLE test.t1_1 ADD PRIMARY KEY (f1); +SELECT * FROM information_schema.statistics +WHERE table_name LIKE 't1_%'; +# Some variations on index definitions +# 1. No name assigned, one column +ALTER TABLE test.t1_1 ADD INDEX (f4); +# 2. Name assigned, two columns +CREATE INDEX f3_f1 ON test.t1_1 (f3,f1); +# 3. Unique index +CREATE UNIQUE INDEX f4x_uni ON test.t1_1 (f4x); +# 4. Index using HASH +CREATE INDEX f2_hash USING HASH ON test.t1_2 (f2); +# 5. Index with comment (feature introduced in 5.2) +--error ER_PARSE_ERROR +CREATE INDEX f1_idx ON test.t1_2 (f1) COMMENT = 'COMMENT'; +# 6. NOT NULL +CREATE INDEX not_null ON test.t1_1 (f3x); +# 7. Prefix index +CREATE INDEX f2_prefix ON test.t1_1 (f2(20)); +# +SELECT * FROM information_schema.statistics +WHERE table_name LIKE 't1_%' AND index_name <> 'PRIMARY' +ORDER BY table_schema,table_name,index_name,seq_in_index,column_name; +--horizontal_results +DROP TABLE test.t1_2; +# +# Check modification of TABLE_NAME +SELECT DISTINCT table_name FROM information_schema.statistics +WHERE table_name = 't1_1'; +RENAME TABLE test.t1_1 TO test.t1_1x; +SELECT DISTINCT table_name FROM information_schema.statistics +WHERE table_name = 't1_1x'; +# +# Check modification of TABLE_SCHEMA +SELECT DISTINCT table_schema,table_name FROM information_schema.statistics +WHERE table_name LIKE 't1_1%'; +RENAME TABLE test.t1_1x TO db_datadict.t1_1x; +SELECT DISTINCT table_schema,table_name FROM information_schema.statistics +WHERE table_name LIKE 't1_1%'; +# +# Check impact of DROP TABLE +SELECT DISTINCT table_name FROM information_schema.statistics +WHERE table_name = 't1_1x'; +DROP TABLE db_datadict.t1_1x; +SELECT DISTINCT table_name FROM information_schema.statistics +WHERE table_name = 't1_1x'; +# +# Check a temporary table (not visible) +--replace_result $engine_type <engine_type> +eval +CREATE TEMPORARY TABLE test.t1_1x (PRIMARY KEY(f1,f2)) +ENGINE = $engine_type + AS SELECT 1 AS f1, 2 AS f2; +--vertical_results +SELECT * FROM information_schema.statistics +WHERE table_name = 't1_1x'; +--horizontal_results +DROP TEMPORARY TABLE test.t1_1x; +# +# Check impact of DROP SCHEMA +--replace_result $engine_type <engine_type> +eval +CREATE TABLE db_datadict.t1_1x (PRIMARY KEY(f1)) +ENGINE = $engine_type + AS SELECT 1 AS f1, 2 AS f2; +SELECT table_name FROM information_schema.statistics +WHERE table_name = 't1_1x'; +DROP DATABASE db_datadict; +SELECT table_name FROM information_schema.statistics +WHERE table_name = 't1_1x'; + + +--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.statistics +SELECT * FROM information_schema.statistics; + +--error ER_DBACCESS_DENIED_ERROR +UPDATE information_schema.statistics SET table_schema = 'test' +WHERE table_name = 't1'; + +--error ER_DBACCESS_DENIED_ERROR +DELETE FROM information_schema.statistics WHERE table_name = 't1'; +--error ER_DBACCESS_DENIED_ERROR +TRUNCATE information_schema.statistics; + +--error ER_DBACCESS_DENIED_ERROR +CREATE INDEX my_idx_on_statistics +ON information_schema.statistics(table_schema); + +--error ER_DBACCESS_DENIED_ERROR +ALTER TABLE information_schema.statistics DROP PRIMARY KEY; +--error ER_DBACCESS_DENIED_ERROR +ALTER TABLE information_schema.statistics ADD f1 INT; + +--error ER_DBACCESS_DENIED_ERROR +DROP TABLE information_schema.statistics; + +--error ER_DBACCESS_DENIED_ERROR +ALTER TABLE information_schema.statistics RENAME db_datadict.statistics; +--error ER_DBACCESS_DENIED_ERROR +ALTER TABLE information_schema.statistics RENAME information_schema.xstatistics; + +# Cleanup +DROP DATABASE db_datadict; + diff --git a/mysql-test/suite/funcs_1/t/is_statistics_is.test b/mysql-test/suite/funcs_1/t/is_statistics_is.test new file mode 100644 index 00000000000..0cf5df64955 --- /dev/null +++ b/mysql-test/suite/funcs_1/t/is_statistics_is.test @@ -0,0 +1,14 @@ +# suite/funcs_1/t/is_statistics_is.test +# +# Check the content of information_schema.statistics about tables within +# the database information_schema for a high and a low privileged user. +# +# 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. +# + +let $my_where = WHERE table_schema = 'information_schema'; +--source suite/funcs_1/datadict/statistics.inc + diff --git a/mysql-test/suite/funcs_1/t/is_statistics_mysql.test b/mysql-test/suite/funcs_1/t/is_statistics_mysql.test new file mode 100644 index 00000000000..a75cc922777 --- /dev/null +++ b/mysql-test/suite/funcs_1/t/is_statistics_mysql.test @@ -0,0 +1,15 @@ +# suite/funcs_1/t/is_statistics_mysql.test +# +# Check the content of information_schema.statistics about tables within +# the database mysql for a high and a low privileged user. +# +# 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. +# + +let $database = mysql; +let $my_where = WHERE table_schema = 'mysql'; +--source suite/funcs_1/datadict/statistics.inc + diff --git a/mysql-test/suite/funcs_1/t/is_table_constraints.test b/mysql-test/suite/funcs_1/t/is_table_constraints.test new file mode 100644 index 00000000000..60d5ebce703 --- /dev/null +++ b/mysql-test/suite/funcs_1/t/is_table_constraints.test @@ -0,0 +1,331 @@ +# suite/funcs_1/t/is_table_constraints.test +# +# Check the layout of information_schema.table_constraints 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_typee. +# +# 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. +# + +# --source suite/funcs_1/datadict/datadict.pre + +let $engine_type = MyISAM; + +let $is_table = TABLE_CONSTRAINTS; + +# The table INFORMATION_SCHEMA.TABLE_CONSTRAINTS 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.10.1: INFORMATION_SCHEMA.TABLE_CONSTRAINTS layout +--echo ######################################################################### +# Ensure that the INFORMATION_SCHEMA.TABLE_CONSTRAINTS table has the following +# columns, in the following order: +# +# CONSTRAINT_CATALOG (always shows NULL), +# CONSTRAINT_SCHEMA (shows the database, or schema, in which +# a constraint an accessible table resides), +# CONSTRAINT_NAME (shows the name of a constraint defined on +# an accessible table), +# TABLE_SCHEMA (shows the database, or schema, in which the +# table resides), +# TABLE_NAME (shows the name of the table), +# CONSTRAINT_TYPE (shows the type of the constraint; either +# 'primary key', 'foreign key', 'unique', 'check'). +# +--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.table_constraints is in is_columns_is.test. + +# Show that CONSTRAINT_CATALOG IS NULL +SELECT constraint_catalog, constraint_schema, constraint_name, + table_schema, table_name +FROM information_schema.table_constraints +WHERE constraint_catalog IS NOT NULL; + + +--echo ######################################################################################### +--echo # Testcase 3.2.7.2 + 3.2.7.3: INFORMATION_SCHEMA.TABLE_CONSTRAINTS 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. +# +# Note: Check of content within table_constraints about tables within +# database is checked in +# mysql is_table_constraints_mysql +# information_schema is_table_constraints_is +# +--disable_warnings +DROP DATABASE IF EXISTS db_datadict; +--enable_warnings +CREATE DATABASE db_datadict; +--replace_result $engine_type <some_engine_type> +eval +CREATE TABLE db_datadict.t1 (f1 BIGINT, f2 BIGINT, f3 BIGINT, f4 BIGINT, + f5 BIGINT, f6 BIGINT, PRIMARY KEY (f1,f2)) +ENGINE = $engine_type; +CREATE UNIQUE INDEX my_idx1 ON db_datadict.t1(f6,f1); +CREATE UNIQUE INDEX my_idx2 ON db_datadict.t1(f3); +--replace_result $engine_type <some_engine_type> +eval +CREATE TABLE db_datadict.t2 (f1 BIGINT, f2 BIGINT, f3 BIGINT, f4 BIGINT, + f5 BIGINT, f6 BIGINT, PRIMARY KEY (f1,f2)) +ENGINE = $engine_type; + +--error 0,ER_CANNOT_USER +DROP USER 'testuser1'@'localhost'; +CREATE USER 'testuser1'@'localhost'; +GRANT SELECT(f5) ON db_datadict.t1 TO 'testuser1'@'localhost'; +SHOW GRANTS FOR 'testuser1'@'localhost'; + +let $my_select = SELECT * FROM information_schema.table_constraints +WHERE table_schema = 'db_datadict' +ORDER BY table_schema,table_name, constraint_name; +let $my_show1 = SHOW INDEXES FROM db_datadict.t1; +let $my_show2 = SHOW INDEXES FROM db_datadict.t2; + +eval $my_select; +# 1 Table Note: We intentinally suppress the protocolling of all +# 2 Non_unique output being not of interest or depending on +# 3 Key_name storage engine used for the table. +# 4 Seq_in_index +# 5 Column_name +# 6 Collation +# 7 Cardinality +# 8 Sub_part +# 9 Packed +# 10 Null +# 11 Index_type +# 12 Comment +--replace_column 6 ### 7 ### 8 ### 9 ### 10 ### 11 ### 12 ### +eval $my_show1; +--replace_column 6 ### 7 ### 8 ### 9 ### 10 ### 11 ### 12 ### +eval $my_show2; + +--echo # Establish connection testuser1 (user=testuser1) +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +connect (testuser1, localhost, testuser1, , db_datadict); +SHOW GRANTS FOR 'testuser1'@'localhost'; +eval $my_select; +--replace_column 6 ### 7 ### 8 ### 9 ### 10 ### 11 ### 12 ### +eval $my_show1; +--error ER_TABLEACCESS_DENIED_ERROR +eval $my_show2; + +# Cleanup +--echo # Switch to connection default and close connection testuser1 +connection default; +disconnect testuser1; +DROP USER 'testuser1'@'localhost'; +DROP DATABASE db_datadict; + + +--echo ######################################################################################### +--echo # Testcase 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.TABLE_CONSTRAINTS 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.table_constraints +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.table_constraints. +SELECT constraint_name, table_schema, table_name, constraint_type +FROM information_schema.table_constraints +WHERE table_name = 't1_my_table'; +# +# Check modification of TABLE_NAME +SELECT table_name FROM information_schema.table_constraints +WHERE table_name LIKE 't1_my_table%'; +RENAME TABLE test.t1_my_table TO test.t1_my_tablex; +SELECT table_name FROM information_schema.table_constraints +WHERE table_name LIKE 't1_my_table%'; +# +# Check modification of TABLE_SCHEMA +SELECT table_schema,table_name FROM information_schema.table_constraints +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.table_constraints +WHERE table_name = 't1_my_tablex'; +# +let $my_select = SELECT constraint_schema, constraint_name, table_schema, +table_name, constraint_type +FROM information_schema.table_constraints +WHERE table_name = 't1_my_tablex' +ORDER BY table_schema,table_name, constraint_name; +# +# Check ADD INDEX being not UNIQUE (does not show up in table_constraints) +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 (f4,f1); +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 (f2); +eval $my_select; +# +# Check DROP COLUMN (removing an UNIQUE INDEX) +eval $my_select; +ALTER TABLE db_datadict.t1_my_tablex +DROP COLUMN f2; +eval $my_select; +# +# Check impact of DROP TABLE +SELECT table_name +FROM information_schema.table_constraints +WHERE table_name = 't1_my_tablex'; +DROP TABLE db_datadict.t1_my_tablex; +SELECT table_name +FROM information_schema.table_constraints +WHERE table_name = 't1_my_tablex'; +# +# No UNIQUE CONSTRAINT -> no entry in key_column_usage +SELECT table_name FROM information_schema.table_constraints +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.table_constraints +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.table_constraints +WHERE table_name = 't1_my_tablex'; +# +# Check impact of DROP SCHEMA +SELECT table_name FROM information_schema.table_constraints +WHERE table_name = 't1_my_tablex'; +DROP DATABASE db_datadict; +SELECT table_name FROM information_schema.table_constraints +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; +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, UNIQUE(f1)) +ENGINE = $engine_type; + +--error ER_DBACCESS_DENIED_ERROR + +--error ER_DBACCESS_DENIED_ERROR +INSERT INTO information_schema.table_constraints + (constraint_schema, constraint_name, table_name) +VALUES ( 'mysql', 'primary', 'db'); +--error ER_DBACCESS_DENIED_ERROR +INSERT INTO information_schema.table_constraints +SELECT * FROM information_schema.table_constraints; + +--error ER_DBACCESS_DENIED_ERROR +UPDATE information_schema.table_constraints +SET table_name = 'db1' WHERE constraint_name = 'primary'; + +--error ER_DBACCESS_DENIED_ERROR +DELETE FROM information_schema.table_constraints WHERE table_name = 't1'; +--error ER_DBACCESS_DENIED_ERROR +TRUNCATE information_schema.table_constraints; + +--error ER_DBACCESS_DENIED_ERROR +CREATE INDEX i3 ON information_schema.table_constraints(table_name); + +--error ER_DBACCESS_DENIED_ERROR +ALTER TABLE information_schema.table_constraints ADD f1 INT; + +--error ER_DBACCESS_DENIED_ERROR +DROP TABLE information_schema.table_constraints; + +--error ER_DBACCESS_DENIED_ERROR +ALTER TABLE information_schema.table_constraints +RENAME db_datadict.table_constraints; +--error ER_DBACCESS_DENIED_ERROR +ALTER TABLE information_schema.table_constraints +RENAME information_schema.xtable_constraints; + +# Cleanup +DROP TABLE db_datadict.t1; +DROP DATABASE db_datadict; + diff --git a/mysql-test/suite/funcs_1/t/is_table_constraints_is.test b/mysql-test/suite/funcs_1/t/is_table_constraints_is.test new file mode 100644 index 00000000000..6105126386a --- /dev/null +++ b/mysql-test/suite/funcs_1/t/is_table_constraints_is.test @@ -0,0 +1,14 @@ +# suite/funcs_1/t/is_table_constraints_is.test +# +# Check the content of information_schema.table_constraints about tables within +# the database information_schema with different privileged users. +# +# 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. +# + +let $table_schema = information_schema; +--source suite/funcs_1/datadict/table_constraints.inc + diff --git a/mysql-test/suite/funcs_1/t/is_table_constraints_mysql.test b/mysql-test/suite/funcs_1/t/is_table_constraints_mysql.test new file mode 100644 index 00000000000..5ef1561ccdf --- /dev/null +++ b/mysql-test/suite/funcs_1/t/is_table_constraints_mysql.test @@ -0,0 +1,14 @@ +# suite/funcs_1/t/is_table_constraints_mysql.test +# +# Check the content of information_schema.table_constraints about tables within +# the database mysql (= the system tables) with different privileged users. +# +# 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. +# + +let $table_schema = mysql; +--source suite/funcs_1/datadict/table_constraints.inc + diff --git a/mysql-test/suite/funcs_1/t/is_table_privileges.test b/mysql-test/suite/funcs_1/t/is_table_privileges.test new file mode 100644 index 00000000000..b095a5ddad6 --- /dev/null +++ b/mysql-test/suite/funcs_1/t/is_table_privileges.test @@ -0,0 +1,349 @@ +# suite/funcs_1/t/is_table_privileges.test +# +# Check the layout of information_schema.table_privileges 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. +# +# 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. +# + +# --source suite/funcs_1/datadict/datadict.pre + +let $engine_type = MEMORY; +let $other_engine_type = MyISAM; + +let $is_table = TABLE_PRIVILEGES; + +# The table INFORMATION_SCHEMA.TABLE_PRIVILEGES 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.11.1: INFORMATION_SCHEMA.TABLE_PRIVILEGES layout +--echo ######################################################################### +# Ensure that the INFORMATION_SCHEMA.TABLE_PRIVILEGES table has the following +# columns, in the following order: +# +# GRANTEE (shows the name of a user who has either granted, or been granted a +# table privilege), +# TABLE_CATALOG (always shows NULL), +# TABLE_SCHEMA (shows the name of the schema, or database, in which the table +# for which a privilege has been granted resides), +# TABLE_NAME (shows the name of the table), +# PRIVILEGE_TYPE (shows the type of privilege that was granted; must be either +# SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER, INDEX, DROP or +# CREATE VIEW), +# IS_GRANTABLE (shows whether that privilege was granted WITH GRANT OPTION). +# +--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.table_privileges is in is_columns_is.test. + +# Show that TABLE_CATALOG is always NULL. +SELECT table_catalog, table_schema, table_name, privilege_type +FROM information_schema.table_privileges WHERE table_catalog IS NOT NULL; + +--echo ###################################################################### +--echo # Testcase 3.2.11.2+3.2.11.3+3.2.11.4: +--echo # INFORMATION_SCHEMA.TABLE_PRIVILEGES accessible information +--echo ###################################################################### +# 3.2.11.2: Ensure that the table shows the relevant information on every +# table privilege which has been granted to the current user or +# PUBLIC, or which was granted by the current user. +# 3.2.11.3: Ensure that the table does not show any information on any table +# privilege which was granted to any user other than the current +# user or PUBLIC, or which was granted by any user other than the +# current user. +# 3.2.11.4: Ensure that the table does not show any information on any +# privileges that are not table privileges for the current user. +# +# To be implemented: +# Check of content within information_schema.table_privileges about +# databases like 'information_schema' or 'mysql'. +# 2008-02-15 Neither root nor a just created low privileged user has table +# privileges within these schemas. +# +--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.tb1(f1 INT, f2 INT, f3 INT) +ENGINE = $engine_type; + +--error 0,ER_CANNOT_USER +DROP USER 'testuser1'@'localhost'; +CREATE USER 'testuser1'@'localhost'; +GRANT CREATE, SELECT ON db_datadict.* +TO 'testuser1'@'localhost' WITH GRANT OPTION; +GRANT SELECT ON db_datadict.tb1 TO 'testuser1'@'localhost'; +--error 0,ER_CANNOT_USER +DROP USER 'testuser2'@'localhost'; +CREATE USER 'testuser2'@'localhost'; +GRANT ALL ON db_datadict.tb1 TO 'testuser2'@'localhost' WITH GRANT OPTION; +--error 0,ER_CANNOT_USER +DROP USER 'testuser3'@'localhost'; +CREATE USER 'testuser3'@'localhost'; + +let $my_select = SELECT * FROM information_schema.table_privileges +WHERE table_name LIKE 'tb%' +ORDER BY grantee,table_schema,table_name,privilege_type; + +--echo # Establish connection testuser1 (user=testuser1) +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +connect (testuser1, localhost, testuser1, , db_datadict); +--replace_result $other_engine_type <other_engine_type> +eval +CREATE TABLE tb3 (f1 TEXT) +ENGINE = $other_engine_type; +GRANT SELECT ON db_datadict.tb3 TO 'testuser3'@'localhost'; +eval $my_select; +SHOW GRANTS FOR 'testuser1'@'localhost'; + +--echo # Establish connection testuser2 (user=testuser3) +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +connect (testuser2, localhost, testuser2, , db_datadict); +# we see only table privileges for this user, and not any other privileges +eval $my_select; +SHOW GRANTS FOR 'testuser2'@'localhost'; + +--echo # Establish connection testuser3 (user=testuser3) +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +connect (testuser3, localhost, testuser3, , db_datadict); +# we see only table privileges for this user, and not any other privileges +eval $my_select; +SHOW GRANTS FOR 'testuser3'@'localhost'; + +--echo # Switch to connection default and close the other connections +connection default; +disconnect testuser1; +disconnect testuser2; +disconnect testuser3; + +# we see only 'public' table privileges +eval $my_select; +SHOW GRANTS FOR 'testuser1'@'localhost'; +SHOW GRANTS FOR 'testuser2'@'localhost'; +SHOW GRANTS FOR 'testuser3'@'localhost'; + +# Cleanup +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.TABLE_PRIVILEGES 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. +# +# Note (mleich): +# The MySQL privilege system allows to GRANT objects before they exist. +# (Exception: Grant privileges for columns of not existing tables/views.) +# There is also no migration of privileges if objects (tables,views,columns) +# are moved to other databases (tables only), renamed or dropped. +# +--disable_warnings +DROP TABLE IF EXISTS test.t1_table; +DROP VIEW IF EXISTS test.t1_view; +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) +DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci +COMMENT = 'Initial Comment' ENGINE = $engine_type; +CREATE VIEW test.t1_view AS SELECT 1; + +--error 0,ER_CANNOT_USER +DROP USER 'testuser1'@'localhost'; +CREATE USER 'testuser1'@'localhost'; +--error 0,ER_CANNOT_USER +DROP USER 'the_user'@'localhost'; +# +# Check granted TABLE and VIEW +SELECT table_name FROM information_schema.table_privileges +WHERE table_name LIKE 't1_%'; +GRANT ALL ON test.t1_table TO 'testuser1'@'localhost'; +GRANT ALL ON test.t1_view TO 'testuser1'@'localhost'; +SELECT * FROM information_schema.table_privileges +WHERE table_name LIKE 't1_%' +ORDER BY grantee, table_schema, table_name, privilege_type; +# +# Check modification of GRANTEE (migration of permissions) +SELECT DISTINCT grantee, table_name FROM information_schema.table_privileges +WHERE table_name LIKE 't1_%' +ORDER BY grantee, table_name; +RENAME USER 'testuser1'@'localhost' TO 'the_user'@'localhost'; +# FIXME: mleich Workaround for bug to be reported +# It looks like an immediate reloading of the system tables is missing in case +# of RENAME USER. +FLUSH PRIVILEGES; +SELECT DISTINCT grantee, table_name FROM information_schema.table_privileges +WHERE table_name LIKE 't1_%' +ORDER BY grantee, table_name; +--error ER_NONEXISTING_GRANT +SHOW GRANTS FOR 'testuser1'@'localhost'; +SHOW GRANTS FOR 'the_user'@'localhost'; +# +# Check modification of TABLE_SCHEMA (no migration of permissions) +SELECT DISTINCT table_schema,table_name FROM information_schema.table_privileges +WHERE table_name LIKE 't1_%' +ORDER BY table_schema,table_name; +RENAME TABLE test.t1_table TO db_datadict.t1_table; +--error ER_FORBID_SCHEMA_CHANGE +RENAME TABLE test.t1_view TO db_datadict.t1_view; +SELECT DISTINCT table_schema,table_name FROM information_schema.table_privileges +WHERE table_name LIKE 't1_%' +ORDER BY table_schema,table_name; +SHOW GRANTS FOR 'the_user'@'localhost'; +REVOKE ALL PRIVILEGES ON test.t1_table FROM 'the_user'@'localhost'; +REVOKE ALL PRIVILEGES ON test.t1_view FROM 'the_user'@'localhost'; +DROP VIEW test.t1_view; +CREATE VIEW db_datadict.t1_view AS SELECT 1; +GRANT ALL ON db_datadict.t1_table TO 'the_user'@'localhost'; +GRANT ALL ON db_datadict.t1_view TO 'the_user'@'localhost'; +# +# Check modification of TABLE_NAME (no migration of permissions) +SELECT DISTINCT table_name FROM information_schema.table_privileges +WHERE table_name LIKE 't1_%' +ORDER BY table_name; +RENAME TABLE db_datadict.t1_table TO db_datadict.t1_tablex; +RENAME TABLE db_datadict.t1_view TO db_datadict.t1_viewx; +SELECT DISTINCT table_name FROM information_schema.table_privileges +WHERE table_name LIKE 't1_%' +ORDER BY table_name; +RENAME TABLE db_datadict.t1_tablex TO db_datadict.t1_table; +RENAME TABLE db_datadict.t1_viewx TO db_datadict.t1_view; +# +# Check impact of DROP TABLE/VIEW (no removal of permissions) +SELECT DISTINCT table_name FROM information_schema.table_privileges +WHERE table_name LIKE 't1_%' +ORDER BY table_name; +DROP TABLE db_datadict.t1_table; +DROP VIEW db_datadict.t1_view; +SELECT DISTINCT table_name FROM information_schema.table_privileges +WHERE table_name LIKE 't1_%' +ORDER BY table_name; +# +# Check impact of DROP SCHEMA (no removal of permissions) +--replace_result $engine_type <engine_type> +eval +CREATE TABLE db_datadict.t1_table +ENGINE = $engine_type AS +SELECT 1; +CREATE VIEW db_datadict.t1_view AS SELECT 1; +GRANT ALL ON db_datadict.t1_table TO 'the_user'@'localhost'; +GRANT ALL ON db_datadict.t1_view TO 'the_user'@'localhost'; +SELECT DISTINCT table_name FROM information_schema.table_privileges +WHERE table_name LIKE 't1_%' +ORDER BY table_name; +DROP DATABASE db_datadict; +SELECT DISTINCT table_name FROM information_schema.table_privileges +WHERE table_name LIKE 't1_%' +ORDER BY table_name; + +# Cleanup +DROP USER 'the_user'@'localhost'; + + +--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; +--replace_result $engine_type <engine_type> +eval +CREATE TABLE db_datadict.t1 (f1 BIGINT, f2 BIGINT) +ENGINE = $engine_type; +--error 0,ER_CANNOT_USER +DROP USER 'testuser1'@'localhost'; +CREATE USER 'testuser1'@'localhost'; +GRANT SELECT (f1) ON db_datadict.t1 TO 'testuser1'@'localhost'; + +--error ER_DBACCESS_DENIED_ERROR +INSERT INTO information_schema.table_privileges +SELECT * FROM information_schema.table_privileges; + +--error ER_DBACCESS_DENIED_ERROR +UPDATE information_schema.table_privileges SET table_schema = 'test' +WHERE table_name = 't1'; + +--error ER_DBACCESS_DENIED_ERROR +DELETE FROM information_schema.table_privileges WHERE table_name = 't1'; +--error ER_DBACCESS_DENIED_ERROR +TRUNCATE information_schema.table_privileges; + +--error ER_DBACCESS_DENIED_ERROR +CREATE INDEX my_idx_on_tables +ON information_schema.table_privileges(table_schema); +--error ER_DBACCESS_DENIED_ERROR +ALTER TABLE information_schema.table_privileges ADD f1 INT; + +--error ER_DBACCESS_DENIED_ERROR +DROP TABLE information_schema.table_privileges; + +--error ER_DBACCESS_DENIED_ERROR +ALTER TABLE information_schema.table_privileges +RENAME db_datadict.table_privileges; +--error ER_DBACCESS_DENIED_ERROR +ALTER TABLE information_schema.table_privileges +RENAME information_schema.xtable_privileges; + +# Cleanup +DROP DATABASE db_datadict; +DROP USER 'testuser1'@'localhost'; + diff --git a/mysql-test/suite/funcs_1/t/is_tables.test b/mysql-test/suite/funcs_1/t/is_tables.test new file mode 100644 index 00000000000..35b6b7ef007 --- /dev/null +++ b/mysql-test/suite/funcs_1/t/is_tables.test @@ -0,0 +1,474 @@ +# suite/funcs_1/t/tables.test +# +# 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. +# + +# --source suite/funcs_1/datadict/datadict.pre + +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/t/is_tables_innodb.test b/mysql-test/suite/funcs_1/t/is_tables_innodb.test new file mode 100644 index 00000000000..31a3900c7c3 --- /dev/null +++ b/mysql-test/suite/funcs_1/t/is_tables_innodb.test @@ -0,0 +1,22 @@ +# suite/funcs_1/t/is_tables_innodb.test +# +# Check the content of information_schema.tables about tables within +# the databases created by the user. +# Variant for storage engine InnoDB +# +# 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. +# + +--source include/have_innodb.inc +let $engine_type= InnoDB; +--source suite/funcs_1/datadict/datadict_load.inc +--enable_abort_on_error + +# We look only for the tables created by datadict_load.inc. +let $my_where = WHERE table_schema LIKE 'test%' AND table_type = 'BASE TABLE'; +--source suite/funcs_1/datadict/tables1.inc + +--source suite/funcs_1/include/cleanup.inc diff --git a/mysql-test/suite/funcs_1/t/is_tables_is.test b/mysql-test/suite/funcs_1/t/is_tables_is.test new file mode 100644 index 00000000000..66ad94f774b --- /dev/null +++ b/mysql-test/suite/funcs_1/t/is_tables_is.test @@ -0,0 +1,18 @@ +# suite/funcs_1/t/is_tables_is.test +# +# Check the content of information_schema.tables about tables within +# the database information_schema. +# +# Note: The INFORMATION_SCHEMA table PROFILING is optional (exists in MySQL +# Community version only) and therefore we exclude it from retrieval. +# +# 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. +# + +let $my_where = WHERE table_schema = 'information_schema' +AND table_name <> 'profiling'; +--source suite/funcs_1/datadict/tables1.inc + diff --git a/mysql-test/suite/funcs_1/t/is_tables_memory.test b/mysql-test/suite/funcs_1/t/is_tables_memory.test new file mode 100644 index 00000000000..8b5da595462 --- /dev/null +++ b/mysql-test/suite/funcs_1/t/is_tables_memory.test @@ -0,0 +1,22 @@ +# suite/funcs_1/t/is_tables_memory.test +# +# Check the content of information_schema.tables about tables within +# the databases created by the user. +# Variant for storage engine MEMORY +# +# 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. +# + +let $engine_type= MEMORY; +SET @@session.sql_mode = 'NO_ENGINE_SUBSTITUTION'; +--source suite/funcs_1/datadict/datadict_load.inc +--enable_abort_on_error + +# We look only for the tables created by datadict_load.inc. +let $my_where = WHERE table_schema LIKE 'test%' AND table_type = 'BASE TABLE'; +--source suite/funcs_1/datadict/tables1.inc + +--source suite/funcs_1/include/cleanup.inc diff --git a/mysql-test/suite/funcs_1/t/is_tables_myisam.test b/mysql-test/suite/funcs_1/t/is_tables_myisam.test new file mode 100644 index 00000000000..4fc88364d87 --- /dev/null +++ b/mysql-test/suite/funcs_1/t/is_tables_myisam.test @@ -0,0 +1,22 @@ +# suite/funcs_1/t/is_tables_myisam.test +# +# Check the content of information_schema.tables about tables within +# the databases created by the user. +# Variant for storage engine MyISAM +# +# 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. +# + +let $engine_type= MyISAM; +SET @@session.sql_mode = 'NO_ENGINE_SUBSTITUTION'; +--source suite/funcs_1/datadict/datadict_load.inc +--enable_abort_on_error + +# We look only for the tables created by datadict_load.inc. +let $my_where = WHERE table_schema LIKE 'test%' AND table_type = 'BASE TABLE'; +--source suite/funcs_1/datadict/tables1.inc + +--source suite/funcs_1/include/cleanup.inc diff --git a/mysql-test/suite/funcs_1/t/is_tables_mysql.test b/mysql-test/suite/funcs_1/t/is_tables_mysql.test new file mode 100644 index 00000000000..a6c3a72a1cf --- /dev/null +++ b/mysql-test/suite/funcs_1/t/is_tables_mysql.test @@ -0,0 +1,14 @@ +# suite/funcs_1/t/is_tables_mysql.test +# +# Check the content of information_schema.tables about tables within +# the database mysql (= the system tables). +# +# 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. +# + +let $my_where = WHERE table_schema = 'mysql'; +--source suite/funcs_1/datadict/tables1.inc + diff --git a/mysql-test/suite/funcs_1/t/is_tables_ndb.test b/mysql-test/suite/funcs_1/t/is_tables_ndb.test new file mode 100644 index 00000000000..49ce60b08bd --- /dev/null +++ b/mysql-test/suite/funcs_1/t/is_tables_ndb.test @@ -0,0 +1,34 @@ +# suite/funcs_1/t/is_tables_ndb.test +# +# Check the content of information_schema.tables about tables within +# the databases created by the user. +# Variant for storage engine NDB +# +# 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. +# + +--source include/have_ndb.inc +let $engine_type= NDB; +--source suite/funcs_1/datadict/datadict_load.inc +--enable_abort_on_error + +# We look only for the tables created by datadict_load.inc. +let $my_where = WHERE table_schema LIKE 'test%' AND table_type = 'BASE TABLE'; +--source suite/funcs_1/datadict/tables1.inc + +# This test runs with a different set of tables. +# --source suite/funcs_1/include/cleanup.inc +DROP DATABASE test1; +DROP DATABASE test4; +DROP TABLE test.t1; +DROP TABLE test.t2; +DROP TABLE test.t3; +DROP TABLE test.t4; +DROP TABLE test.t7; +DROP TABLE test.t8; +DROP TABLE test.t9; +DROP TABLE test.t10; +DROP TABLE test.t11; diff --git a/mysql-test/suite/funcs_1/t/is_triggers.test b/mysql-test/suite/funcs_1/t/is_triggers.test new file mode 100644 index 00000000000..261f082fb50 --- /dev/null +++ b/mysql-test/suite/funcs_1/t/is_triggers.test @@ -0,0 +1,253 @@ +# suite/funcs_1/t/is_triggers.test +# +# 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. +# + +# --source suite/funcs_1/datadict/datadict.pre + +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 +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 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 SUPER ON *.* TO 'testuser1'@'localhost'; +GRANT SUPER ON *.* TO 'testuser3'@'localhost'; +GRANT SUPER 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'; +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 SUPER 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 # SUPER 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 # SUPER Privilege + no SELECT Privilege on t1 --> no result for query +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/t/is_user_privileges.test b/mysql-test/suite/funcs_1/t/is_user_privileges.test new file mode 100644 index 00000000000..dc39ad6fe4f --- /dev/null +++ b/mysql-test/suite/funcs_1/t/is_user_privileges.test @@ -0,0 +1,347 @@ +# suite/funcs_1/t/is_user_privileges.test +# +# Check the layout of information_schema.user_privileges, 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 tables +# 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. +# + +# --source suite/funcs_1/datadict/datadict.pre + +let $engine_type = MEMORY; +let $other_engine_type = MyISAM; + +let $is_table = USER_PRIVILEGES; + +# The table INFORMATION_SCHEMA.USER_PRIVILEGES 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.16.1: INFORMATION_SCHEMA.USER_PRIVILEGES layout +--echo ######################################################################### +# Ensure that the INFORMATION_SCHEMA.USER_PRIVILEGES table has the following columns, +# in the following order: +# +# GRANTEE (shows a user to whom a user privilege has been granted), +# TABLE_CATALOG (always shows NULL), +# PRIVILEGE_TYPE (shows the granted privilege), +# IS_GRANTABLE (shows whether the privilege was granted WITH GRANT OPTION). +# +--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.user_privileges is in is_columns_is.test. + +# Show that TABLE_CATALOG is always NULL. +SELECT grantee, table_catalog, privilege_type +FROM information_schema.user_privileges +WHERE table_catalog IS NOT NULL; + + +--echo ########################################################################## +--echo # Testcases 3.2.16.2+3.2.16.3+3.2.16.4: INFORMATION_SCHEMA.USER_PRIVILEGES +--echo # accessible information +--echo ########################################################################## +# 3.2.16.2: Ensure that the table shows the relevant information on every user +# privilege which has been granted to the current user or to PUBLIC, +# or has been granted by the current user. +# 3.2.16.3: Ensure that the table does not show any information on any user +# privileges which have been granted to users other than the current +# user or have been granted by any user other than the current user. +# 3.2.16.4: Ensure that the table does not show any information on any +# privileges that are not user privileges for the current user. +# +--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'; + +GRANT SELECT ON db_datadict.* TO 'testuser1'@'localhost'; +GRANT SELECT ON mysql.user TO 'testuser1'@'localhost'; + +GRANT INSERT ON *.* TO 'testuser2'@'localhost'; +GRANT UPDATE ON *.* TO 'testuser2'@'localhost'; + +let $my_select1= SELECT * FROM information_schema.user_privileges +WHERE grantee LIKE '''testuser%''' +ORDER BY grantee, table_catalog, privilege_type; +let $my_select2= SELECT * FROM mysql.user +WHERE user LIKE 'testuser%' ORDER BY host, user; +let $my_show= SHOW GRANTS; +eval $my_select1; +eval $my_select2; + +--echo # +--echo # Add GRANT OPTION db_datadict.* to testuser1; +GRANT UPDATE ON db_datadict.* TO 'testuser1'@'localhost' WITH GRANT OPTION; +eval $my_select1; +eval $my_select2; + +--echo # Establish connection testuser1 (user=testuser1) +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +connect (testuser1, localhost, testuser1, , db_datadict); +eval $my_select1; +eval $my_select2; +eval $my_show; + +--echo +--echo # Now add SELECT on *.* to testuser1; + +--echo # Switch to connection default +connection default; +GRANT SELECT ON *.* TO 'testuser1'@'localhost'; +--echo # +--echo # Here <SELECT NO> is shown correctly for testuser1; +eval $my_select1; +eval $my_select2; + +GRANT SELECT ON *.* TO 'testuser1'@'localhost' WITH GRANT OPTION; +--echo # +--echo # Here <SELECT YES> is shown correctly for testuser1; +eval $my_select1; +eval $my_select2; + +--echo # Switch to connection testuser1 +# check that this appears +connection testuser1; +eval $my_select1; +eval $my_select2; +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); +eval $my_select1; +--error ER_TABLEACCESS_DENIED_ERROR +eval $my_select2; +eval $my_show; + +--echo # Establish connection testuser3 (user=testuser3) +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +connect (testuser3, localhost, testuser3, , test); +eval $my_select1; +--error ER_TABLEACCESS_DENIED_ERROR +eval $my_select2; +eval $my_show; + +--echo +--echo # Revoke privileges from testuser1; +--echo # Switch to connection default +connection default; +REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'testuser1'@'localhost'; +eval $my_select1; +eval $my_select2; + +--echo # Switch to connection testuser1 +# check for changes +connection testuser1; +eval $my_select1; +--error ER_TABLEACCESS_DENIED_ERROR +eval $my_select2; +eval $my_show; + +# OK, testuser1 has no privs here +--error ER_TABLEACCESS_DENIED_ERROR +CREATE TABLE db_datadict.tb_55 ( c1 TEXT ); +eval $my_select1; +--error ER_TABLEACCESS_DENIED_ERROR +eval $my_select2; +eval $my_show; +# OK, testuser1 has no privs here +--error ER_TABLEACCESS_DENIED_ERROR +CREATE TABLE db_datadict.tb_66 ( c1 TEXT ); + +--echo +--echo # Add ALL on db_datadict.* (and select on mysql.user) to testuser1; +--echo # Switch to connection default +connection default; +GRANT ALL ON db_datadict.* TO 'testuser1'@'localhost' WITH GRANT OPTION; +GRANT SELECT ON mysql.user TO 'testuser1'@'localhost'; +eval $my_select1; +eval $my_select2; + +--echo # Switch to connection testuser1 +connection testuser1; +eval $my_select1; +eval $my_select2; +eval $my_show; + +# OK, testuser1 has no privs here +--error ER_TABLEACCESS_DENIED_ERROR +CREATE TABLE db_datadict.tb_56 ( c1 TEXT ); + +# using 'USE' lets the server read the privileges new, so now the CREATE works +USE db_datadict; +eval $my_select1; +eval $my_select2; +eval $my_show; +--replace_result $other_engine_type <other_engine_type> +eval +CREATE TABLE tb_57 ( c1 TEXT ) +ENGINE = $other_engine_type; + +--echo +--echo # Revoke privileges from testuser1; +--echo # Switch to connection default +connection default; +REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'testuser1'@'localhost'; +eval $my_select1; +eval $my_select2; + +--echo # Switch to connection testuser1 +# check for changes +connection testuser1; +eval $my_select1; +--error ER_TABLEACCESS_DENIED_ERROR +eval $my_select2; +eval $my_show; +# WORKS, as the existing old privileges are used! +--replace_result $other_engine_type <other_engine_type> +eval +CREATE TABLE db_datadict.tb_58 ( c1 TEXT ) +ENGINE = $other_engine_type; +# existing privileges are "read" new when USE is called, user has no privileges +--error ER_DBACCESS_DENIED_ERROR +USE db_datadict; +#FIXME 3.2.16: check that it is correct that this now 'works': --error ER_TABLEACCESS_DENIED_ERROR +--replace_result $other_engine_type <other_engine_type> +eval +CREATE TABLE db_datadict.tb_59 ( c1 TEXT ) +ENGINE = $other_engine_type; + +# 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 IF EXISTS db_datadict; + + +--echo ######################################################################################## +--echo # Testcases 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.USER_PRIVILEGES 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. +# +let $my_select = SELECT * FROM information_schema.user_privileges +WHERE grantee = '''testuser1''@''localhost'''; +let $my_show = SHOW GRANTS FOR 'testuser1'@'localhost'; +eval $my_select; +--error ER_NONEXISTING_GRANT +eval $my_show; +--error 0,ER_CANNOT_USER +DROP USER 'testuser1'@'localhost'; +CREATE USER 'testuser1'@'localhost'; +eval $my_select; +eval $my_show; +GRANT SELECT, FILE ON *.* TO 'testuser1'@'localhost'; +eval $my_select; +eval $my_show; +DROP USER 'testuser1'@'localhost'; +eval $my_select; +--error ER_NONEXISTING_GRANT +eval $my_show; + + +--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. +# +--error 0,ER_CANNOT_USER +DROP USER 'testuser1'@'localhost'; +CREATE USER 'testuser1'@'localhost'; + +--error ER_DBACCESS_DENIED_ERROR +INSERT INTO information_schema.user_privileges +SELECT * FROM information_schema.user_privileges; + +--error ER_DBACCESS_DENIED_ERROR +UPDATE information_schema.user_privileges +SET PRIVILEGE_TYPE = 'gaming'; + +--error ER_DBACCESS_DENIED_ERROR +DELETE FROM information_schema.user_privileges +WHERE grantee = '''testuser1''@''localhost'''; +--error ER_DBACCESS_DENIED_ERROR +TRUNCATE information_schema.user_privileges; + +--error ER_DBACCESS_DENIED_ERROR +CREATE INDEX i1 ON information_schema.user_privileges(grantee); + +--error ER_DBACCESS_DENIED_ERROR +ALTER TABLE information_schema.user_privileges ADD f1 INT; + +--error ER_DBACCESS_DENIED_ERROR +DROP TABLE information_schema.user_privileges; + +--error ER_DBACCESS_DENIED_ERROR +ALTER TABLE information_schema.user_privileges +RENAME db_datadict.user_privileges; +--error ER_DBACCESS_DENIED_ERROR +ALTER TABLE information_schema.user_privileges +RENAME information_schema.xuser_privileges; + +# Cleanup +DROP USER 'testuser1'@'localhost'; + diff --git a/mysql-test/suite/funcs_1/t/is_views.test b/mysql-test/suite/funcs_1/t/is_views.test new file mode 100644 index 00000000000..9819fe1ffe0 --- /dev/null +++ b/mysql-test/suite/funcs_1/t/is_views.test @@ -0,0 +1,305 @@ +# suite/funcs_1/t/is_views.test +# +# 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. +# + +# --source suite/funcs_1/datadict/datadict.pre + +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 +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/t/memory__datadict.test b/mysql-test/suite/funcs_1/t/memory__datadict.test deleted file mode 100644 index b78a2511f3c..00000000000 --- a/mysql-test/suite/funcs_1/t/memory__datadict.test +++ /dev/null @@ -1,9 +0,0 @@ -#### suite/funcs_1/t/datadict_memory.test -# -let $engine_type= memory; -# $OTHER_ENGINE_TYPE must be -# - <> $engine_type -# - all time available like MyISAM or MEMORY -let $OTHER_ENGINE_TYPE= MyISAM; - ---source suite/funcs_1/datadict/datadict_master.inc diff --git a/mysql-test/suite/funcs_1/t/memory__load.test b/mysql-test/suite/funcs_1/t/memory__load.test deleted file mode 100644 index 44bd054f420..00000000000 --- a/mysql-test/suite/funcs_1/t/memory__load.test +++ /dev/null @@ -1,45 +0,0 @@ -##### suite/funcs_1/funcs_1/t/memory__load.test - -# Memory tables should be used -# -# Set $engine_type -let $engine_type= memory; - -# Decide, if the objects are to be (re)created -# -# - once at the beginning of a set of testcases ('$NO_REFRESH' <> '' --> TRUE) -# That means all objects have to be (re)created within the current script. -# -# - per every testscript/case ('$NO_REFRESH' = '' --> FALSE) -# That means the current script must not (re)create any object and every -# testscript/case (re)creates only the objects it needs. - -eval SET @NO_REFRESH = IF( '$NO_REFRESH' = '', 0, 1); - -# FIXME Replace the following, when "if" for mysqltest is available -let $run= `SELECT @NO_REFRESH`; -while ($run) -{ - - # Create some objects needed in many testcases - USE test; - --source suite/funcs_1/include/memory_tb1.inc - --source suite/funcs_1/include/memory_tb2.inc - --source suite/funcs_1/include/memory_tb3.inc - --source suite/funcs_1/include/memory_tb4.inc - - # The database test1 is needed for the VIEW testcases - --disable_warnings - DROP DATABASE IF EXISTS test1; - --enable_warnings - CREATE DATABASE test1; - USE test1; - --source suite/funcs_1/include/memory_tb2.inc - USE test; - - # These tables are needed for the stored procedure testscases - --source suite/funcs_1/include/sp_tb.inc - - let $run= 0; -} - diff --git a/mysql-test/suite/funcs_1/t/memory_bitdata.test b/mysql-test/suite/funcs_1/t/memory_bitdata.test index 3aa30c2d835..af5d0a9c53b 100644 --- a/mysql-test/suite/funcs_1/t/memory_bitdata.test +++ b/mysql-test/suite/funcs_1/t/memory_bitdata.test @@ -5,28 +5,12 @@ # Set $engine_type let $engine_type= memory; -# Decide, if the objects are to be (re)created -# -# - once at the beginning of a set of testcases ('$NO_REFRESH' <> '' --> TRUE) -# That means the current script must not (re)create any object. -# It can expect, that the objects already exist. -# -# - per every testscript/case ('$NO_REFRESH' = '' --> FALSE) -# That means all objects have to be (re)created within the current script. - -eval SET @NO_REFRESH = IF( '$NO_REFRESH' = '', 0, 1); +let $message= NOT YET IMPLEMENTED: bitdata tests; +--source include/show_msg80.inc +exit; -# FIXME Replace the following, when "if" for mysqltest is available -let $run= `SELECT @NO_REFRESH = 0`; -while ($run) -{ - - # Create some objects needed in many testcases - USE test; - --source suite/funcs_1/include/memory_tb4.inc - - let $run= 0; -} +# Create some objects needed in many testcases +USE test; +--source suite/funcs_1/include/memory_tb4.inc --source suite/funcs_1/bitdata/bitdata_master.test - diff --git a/mysql-test/suite/funcs_1/t/memory_cursors.test b/mysql-test/suite/funcs_1/t/memory_cursors.test index 1361c83ecb0..dc38a3dd0a3 100644 --- a/mysql-test/suite/funcs_1/t/memory_cursors.test +++ b/mysql-test/suite/funcs_1/t/memory_cursors.test @@ -5,28 +5,13 @@ # Set $engine_type let $engine_type= memory; -# Decide, if the objects are to be (re)created -# -# - once at the beginning of a set of testcases ('$NO_REFRESH' <> '' --> TRUE) -# That means the current script must not (re)create any object. -# It can expect, that the objects already exist. -# -# - per every testscript/case ('$NO_REFRESH' = '' --> FALSE) -# That means all objects have to be (re)created within the current script. - -eval SET @NO_REFRESH = IF( '$NO_REFRESH' = '', 0, 1); - -# FIXME Replace the following, when "if" for mysqltest is available -let $run= `SELECT @NO_REFRESH = 0`; -while ($run) -{ - - # Create some objects needed in many testcases - USE test; - --source suite/funcs_1/include/memory_tb1.inc +let $message= NOT YET IMPLEMENTED: cursor tests; +--source include/show_msg80.inc +exit; - let $run= 0; -} +# Create some objects needed in many testcases +USE test; +--source suite/funcs_1/include/memory_tb1.inc --source suite/funcs_1/cursors/cursors_master.test diff --git a/mysql-test/suite/funcs_1/t/memory_storedproc_02.test b/mysql-test/suite/funcs_1/t/memory_storedproc_02.test index f92657ee665..f92657ee665 100755..100644 --- a/mysql-test/suite/funcs_1/t/memory_storedproc_02.test +++ b/mysql-test/suite/funcs_1/t/memory_storedproc_02.test diff --git a/mysql-test/suite/funcs_1/t/memory_storedproc_03.test b/mysql-test/suite/funcs_1/t/memory_storedproc_03.test index 8a839b255e1..8a839b255e1 100755..100644 --- a/mysql-test/suite/funcs_1/t/memory_storedproc_03.test +++ b/mysql-test/suite/funcs_1/t/memory_storedproc_03.test diff --git a/mysql-test/suite/funcs_1/t/memory_storedproc_06.test b/mysql-test/suite/funcs_1/t/memory_storedproc_06.test index 059528590b9..059528590b9 100755..100644 --- a/mysql-test/suite/funcs_1/t/memory_storedproc_06.test +++ b/mysql-test/suite/funcs_1/t/memory_storedproc_06.test diff --git a/mysql-test/suite/funcs_1/t/memory_storedproc_07.test b/mysql-test/suite/funcs_1/t/memory_storedproc_07.test index 1d7cee3dbd6..1d7cee3dbd6 100755..100644 --- a/mysql-test/suite/funcs_1/t/memory_storedproc_07.test +++ b/mysql-test/suite/funcs_1/t/memory_storedproc_07.test diff --git a/mysql-test/suite/funcs_1/t/memory_storedproc_08.test b/mysql-test/suite/funcs_1/t/memory_storedproc_08.test index 304be8c477a..304be8c477a 100755..100644 --- a/mysql-test/suite/funcs_1/t/memory_storedproc_08.test +++ b/mysql-test/suite/funcs_1/t/memory_storedproc_08.test diff --git a/mysql-test/suite/funcs_1/t/memory_storedproc_10.test b/mysql-test/suite/funcs_1/t/memory_storedproc_10.test index 13fbe99fabf..13fbe99fabf 100755..100644 --- a/mysql-test/suite/funcs_1/t/memory_storedproc_10.test +++ b/mysql-test/suite/funcs_1/t/memory_storedproc_10.test diff --git a/mysql-test/suite/funcs_1/t/memory_trig_0102.test b/mysql-test/suite/funcs_1/t/memory_trig_0102.test index efa739cfb14..c7b07cedfad 100644 --- a/mysql-test/suite/funcs_1/t/memory_trig_0102.test +++ b/mysql-test/suite/funcs_1/t/memory_trig_0102.test @@ -5,28 +5,10 @@ # Set $engine_type let $engine_type= memory; -# Decide, if the objects are to be (re)created -# -# - once at the beginning of a set of testcases ('$NO_REFRESH' <> '' --> TRUE) -# That means the current script must not (re)create any object. -# It can expect, that the objects already exist. -# -# - per every testscript/case ('$NO_REFRESH' = '' --> FALSE) -# That means all objects have to be (re)created within the current script. - -eval SET @NO_REFRESH = IF( '$NO_REFRESH' = '', 0, 1); - -# FIXME Replace the following, when "if" for mysqltest is available -let $run= `SELECT @NO_REFRESH = 0`; -while ($run) -{ - - # Create some objects needed in many testcases - USE test; - --source suite/funcs_1/include/memory_tb3.inc - - let $run= 0; -} +# Create some objects needed in many testcases +USE test; +--source suite/funcs_1/include/memory_tb3.inc --source suite/funcs_1/triggers/triggers_0102.inc +DROP TABLE test.tb3; diff --git a/mysql-test/suite/funcs_1/t/memory_trig_03.test b/mysql-test/suite/funcs_1/t/memory_trig_03.test index 76980b6b1b0..b7205ce15d2 100644 --- a/mysql-test/suite/funcs_1/t/memory_trig_03.test +++ b/mysql-test/suite/funcs_1/t/memory_trig_03.test @@ -5,28 +5,10 @@ # Set $engine_type let $engine_type= memory; -# Decide, if the objects are to be (re)created -# -# - once at the beginning of a set of testcases ('$NO_REFRESH' <> '' --> TRUE) -# That means the current script must not (re)create any object. -# It can expect, that the objects already exist. -# -# - per every testscript/case ('$NO_REFRESH' = '' --> FALSE) -# That means all objects have to be (re)created within the current script. - -eval SET @NO_REFRESH = IF( '$NO_REFRESH' = '', 0, 1); - -# FIXME Replace the following, when "if" for mysqltest is available -let $run= `SELECT @NO_REFRESH = 0`; -while ($run) -{ - - # Create some objects needed in many testcases - USE test; - --source suite/funcs_1/include/memory_tb3.inc - - let $run= 0; -} +# Create some objects needed in many testcases +USE test; +--source suite/funcs_1/include/memory_tb3.inc --source suite/funcs_1/triggers/triggers_03.inc +DROP TABLE test.tb3; diff --git a/mysql-test/suite/funcs_1/t/memory_trig_0407.test b/mysql-test/suite/funcs_1/t/memory_trig_0407.test index 05217a7f97f..01b4bc39159 100644 --- a/mysql-test/suite/funcs_1/t/memory_trig_0407.test +++ b/mysql-test/suite/funcs_1/t/memory_trig_0407.test @@ -5,28 +5,10 @@ # Set $engine_type let $engine_type= memory; -# Decide, if the objects are to be (re)created -# -# - once at the beginning of a set of testcases ('$NO_REFRESH' <> '' --> TRUE) -# That means the current script must not (re)create any object. -# It can expect, that the objects already exist. -# -# - per every testscript/case ('$NO_REFRESH' = '' --> FALSE) -# That means all objects have to be (re)created within the current script. - -eval SET @NO_REFRESH = IF( '$NO_REFRESH' = '', 0, 1); - -# FIXME Replace the following, when "if" for mysqltest is available -let $run= `SELECT @NO_REFRESH = 0`; -while ($run) -{ - - # Create some objects needed in many testcases - USE test; - --source suite/funcs_1/include/memory_tb3.inc - - let $run= 0; -} +# Create some objects needed in many testcases +USE test; +--source suite/funcs_1/include/memory_tb3.inc --source suite/funcs_1/triggers/triggers_0407.inc +DROP TABLE test.tb3; diff --git a/mysql-test/suite/funcs_1/t/memory_trig_08.test b/mysql-test/suite/funcs_1/t/memory_trig_08.test index 8caae1ec45f..bc09d4f3943 100644 --- a/mysql-test/suite/funcs_1/t/memory_trig_08.test +++ b/mysql-test/suite/funcs_1/t/memory_trig_08.test @@ -5,28 +5,10 @@ # Set $engine_type let $engine_type= memory; -# Decide, if the objects are to be (re)created -# -# - once at the beginning of a set of testcases ('$NO_REFRESH' <> '' --> TRUE) -# That means the current script must not (re)create any object. -# It can expect, that the objects already exist. -# -# - per every testscript/case ('$NO_REFRESH' = '' --> FALSE) -# That means all objects have to be (re)created within the current script. - -eval SET @NO_REFRESH = IF( '$NO_REFRESH' = '', 0, 1); - -# FIXME Replace the following, when "if" for mysqltest is available -let $run= `SELECT @NO_REFRESH = 0`; -while ($run) -{ - - # Create some objects needed in many testcases - USE test; - --source suite/funcs_1/include/memory_tb3.inc - - let $run= 0; -} +# Create some objects needed in many testcases +USE test; +--source suite/funcs_1/include/memory_tb3.inc --source suite/funcs_1/triggers/triggers_08.inc +DROP TABLE test.tb3; diff --git a/mysql-test/suite/funcs_1/t/memory_trig_09.test b/mysql-test/suite/funcs_1/t/memory_trig_09.test index 95a7fefbe90..682e07f9fad 100644 --- a/mysql-test/suite/funcs_1/t/memory_trig_09.test +++ b/mysql-test/suite/funcs_1/t/memory_trig_09.test @@ -5,28 +5,10 @@ # Set $engine_type let $engine_type= memory; -# Decide, if the objects are to be (re)created -# -# - once at the beginning of a set of testcases ('$NO_REFRESH' <> '' --> TRUE) -# That means the current script must not (re)create any object. -# It can expect, that the objects already exist. -# -# - per every testscript/case ('$NO_REFRESH' = '' --> FALSE) -# That means all objects have to be (re)created within the current script. - -eval SET @NO_REFRESH = IF( '$NO_REFRESH' = '', 0, 1); - -# FIXME Replace the following, when "if" for mysqltest is available -let $run= `SELECT @NO_REFRESH = 0`; -while ($run) -{ - - # Create some objects needed in many testcases - USE test; - --source suite/funcs_1/include/memory_tb3.inc - - let $run= 0; -} +# Create some objects needed in many testcases +USE test; +--source suite/funcs_1/include/memory_tb3.inc --source suite/funcs_1/triggers/triggers_09.inc +DROP TABLE test.tb3; diff --git a/mysql-test/suite/funcs_1/t/memory_trig_1011ext.test b/mysql-test/suite/funcs_1/t/memory_trig_1011ext.test index 726b878854e..c6dfe54b64d 100644 --- a/mysql-test/suite/funcs_1/t/memory_trig_1011ext.test +++ b/mysql-test/suite/funcs_1/t/memory_trig_1011ext.test @@ -5,28 +5,10 @@ # Set $engine_type let $engine_type= memory; -# Decide, if the objects are to be (re)created -# -# - once at the beginning of a set of testcases ('$NO_REFRESH' <> '' --> TRUE) -# That means the current script must not (re)create any object. -# It can expect, that the objects already exist. -# -# - per every testscript/case ('$NO_REFRESH' = '' --> FALSE) -# That means all objects have to be (re)created within the current script. - -eval SET @NO_REFRESH = IF( '$NO_REFRESH' = '', 0, 1); - -# FIXME Replace the following, when "if" for mysqltest is available -let $run= `SELECT @NO_REFRESH = 0`; -while ($run) -{ - - # Create some objects needed in many testcases - USE test; - --source suite/funcs_1/include/memory_tb3.inc - - let $run= 0; -} +# Create some objects needed in many testcases +USE test; +--source suite/funcs_1/include/memory_tb3.inc --source suite/funcs_1/triggers/triggers_1011ext.inc +DROP TABLE test.tb3; diff --git a/mysql-test/suite/funcs_1/t/memory_views.test b/mysql-test/suite/funcs_1/t/memory_views.test index 303d0bb2ac1..b506f08cf31 100644 --- a/mysql-test/suite/funcs_1/t/memory_views.test +++ b/mysql-test/suite/funcs_1/t/memory_views.test @@ -5,40 +5,18 @@ # Set $engine_type let $engine_type= memory; -# Decide, if the objects are to be (re)created -# -# - once at the beginning of a set of testcases ('$NO_REFRESH' <> '' --> TRUE) -# That means the current script must not (re)create any object. -# It can expect, that the objects already exist. -# -# - per every testscript/case ('$NO_REFRESH' = '' --> FALSE) -# That means all objects have to be (re)created within the current script. - -eval SET @NO_REFRESH = IF( '$NO_REFRESH' = '', 0, 1); - -let $run= `SELECT @NO_REFRESH = 0`; -if ($run) -{ - - # Create some objects needed in many testcases - USE test; - --source suite/funcs_1/include/memory_tb2.inc - --disable_warnings - DROP DATABASE IF EXISTS test1; - --enable_warnings - CREATE DATABASE test1; - USE test1; - --source suite/funcs_1/include/memory_tb2.inc - USE test; - -} +# Create some objects needed in many testcases +USE test; +--source suite/funcs_1/include/memory_tb2.inc +--disable_warnings +DROP DATABASE IF EXISTS test1; +--enable_warnings +CREATE DATABASE test1; +USE test1; +--source suite/funcs_1/include/memory_tb2.inc +USE test; --source suite/funcs_1/views/views_master.inc - -# If we created the database in the above loop we now need to drop it -let $run= `SELECT @NO_REFRESH = 0`; -if ($run) -{ - DROP DATABASE IF EXISTS test1; -} +DROP DATABASE test1; +DROP TABLE test.tb2; diff --git a/mysql-test/suite/funcs_1/t/myisam__datadict.test b/mysql-test/suite/funcs_1/t/myisam__datadict.test deleted file mode 100644 index e4baba1de3a..00000000000 --- a/mysql-test/suite/funcs_1/t/myisam__datadict.test +++ /dev/null @@ -1,10 +0,0 @@ -#### suite/funcs_1/t/datadict_myisam.test -# - -let $engine_type= myisam; -# $OTHER_ENGINE_TYPE must be -# - <> $engine_type -# - all time available like MyISAM or MEMORY -let $OTHER_ENGINE_TYPE= MEMORY; - ---source suite/funcs_1/datadict/datadict_master.inc diff --git a/mysql-test/suite/funcs_1/t/myisam__load.test b/mysql-test/suite/funcs_1/t/myisam__load.test deleted file mode 100644 index b63044f128c..00000000000 --- a/mysql-test/suite/funcs_1/t/myisam__load.test +++ /dev/null @@ -1,45 +0,0 @@ -##### suite/funcs_1/funcs_1/t/myisam__load.test - -# MyISAM tables should be used -# -# Set $engine_type -let $engine_type= myisam; - -# Decide, if the objects are to be (re)created -# -# - once at the beginning of a set of testcases ('$NO_REFRESH' <> '' --> TRUE) -# That means all objects have to be (re)created within the current script. -# -# - per every testscript/case ('$NO_REFRESH' = '' --> FALSE) -# That means the current script must not (re)create any object and every -# testscript/case (re)creates only the objects it needs. - -eval SET @NO_REFRESH = IF( '$NO_REFRESH' = '', 0, 1); - -# FIXME Replace the following, when "if" for mysqltest is available -let $run= `SELECT @NO_REFRESH`; -while ($run) -{ - - # Create some objects needed in many testcases - USE test; - --source suite/funcs_1/include/myisam_tb1.inc - --source suite/funcs_1/include/myisam_tb2.inc - --source suite/funcs_1/include/myisam_tb3.inc - --source suite/funcs_1/include/myisam_tb4.inc - - # The database test1 is needed for the VIEW testcases - --disable_warnings - DROP DATABASE IF EXISTS test1; - --enable_warnings - CREATE DATABASE test1; - USE test1; - --source suite/funcs_1/include/myisam_tb2.inc - USE test; - - # These tables are needed for the stored procedure testscases - --source suite/funcs_1/include/sp_tb.inc - - let $run= 0; -} - diff --git a/mysql-test/suite/funcs_1/t/myisam_bitdata.test b/mysql-test/suite/funcs_1/t/myisam_bitdata.test index 7ee15e02ea0..fc00cf478c7 100644 --- a/mysql-test/suite/funcs_1/t/myisam_bitdata.test +++ b/mysql-test/suite/funcs_1/t/myisam_bitdata.test @@ -5,28 +5,12 @@ # Set $engine_type let $engine_type= myisam; -# Decide, if the objects are to be (re)created -# -# - once at the beginning of a set of testcases ('$NO_REFRESH' <> '' --> TRUE) -# That means the current script must not (re)create any object. -# It can expect, that the objects already exist. -# -# - per every testscript/case ('$NO_REFRESH' = '' --> FALSE) -# That means all objects have to be (re)created within the current script. - -eval SET @NO_REFRESH = IF( '$NO_REFRESH' = '', 0, 1); +let $message= NOT YET IMPLEMENTED: bitdata tests; +--source include/show_msg80.inc +exit; -# FIXME Replace the following, when "if" for mysqltest is available -let $run= `SELECT @NO_REFRESH = 0`; -while ($run) -{ - - # Create some objects needed in many testcases - USE test; - --source suite/funcs_1/include/myisam_tb4.inc - - let $run= 0; -} +# Create some objects needed in many testcases +USE test; +--source suite/funcs_1/include/myisam_tb4.inc --source suite/funcs_1/bitdata/bitdata_master.test - diff --git a/mysql-test/suite/funcs_1/t/myisam_cursors.test b/mysql-test/suite/funcs_1/t/myisam_cursors.test index 841903148cd..390d6cc1896 100644 --- a/mysql-test/suite/funcs_1/t/myisam_cursors.test +++ b/mysql-test/suite/funcs_1/t/myisam_cursors.test @@ -5,28 +5,13 @@ # Set $engine_type let $engine_type= myisam; -# Decide, if the objects are to be (re)created -# -# - once at the beginning of a set of testcases ('$NO_REFRESH' <> '' --> TRUE) -# That means the current script must not (re)create any object. -# It can expect, that the objects already exist. -# -# - per every testscript/case ('$NO_REFRESH' = '' --> FALSE) -# That means all objects have to be (re)created within the current script. - -eval SET @NO_REFRESH = IF( '$NO_REFRESH' = '', 0, 1); - -# FIXME Replace the following, when "if" for mysqltest is available -let $run= `SELECT @NO_REFRESH = 0`; -while ($run) -{ - - # Create some objects needed in many testcases - USE test; - --source suite/funcs_1/include/myisam_tb1.inc +let $message= NOT YET IMPLEMENTED: cursor tests; +--source include/show_msg80.inc +exit; - let $run= 0; -} +# Create some objects needed in many testcases +USE test; +--source suite/funcs_1/include/myisam_tb1.inc --source suite/funcs_1/cursors/cursors_master.test diff --git a/mysql-test/suite/funcs_1/t/myisam_storedproc_02.test b/mysql-test/suite/funcs_1/t/myisam_storedproc_02.test index 108b0fe5611..108b0fe5611 100755..100644 --- a/mysql-test/suite/funcs_1/t/myisam_storedproc_02.test +++ b/mysql-test/suite/funcs_1/t/myisam_storedproc_02.test diff --git a/mysql-test/suite/funcs_1/t/myisam_storedproc_03.test b/mysql-test/suite/funcs_1/t/myisam_storedproc_03.test index b181e3ce7ab..b181e3ce7ab 100755..100644 --- a/mysql-test/suite/funcs_1/t/myisam_storedproc_03.test +++ b/mysql-test/suite/funcs_1/t/myisam_storedproc_03.test diff --git a/mysql-test/suite/funcs_1/t/myisam_storedproc_06.test b/mysql-test/suite/funcs_1/t/myisam_storedproc_06.test index 81d3d24a01f..81d3d24a01f 100755..100644 --- a/mysql-test/suite/funcs_1/t/myisam_storedproc_06.test +++ b/mysql-test/suite/funcs_1/t/myisam_storedproc_06.test diff --git a/mysql-test/suite/funcs_1/t/myisam_storedproc_07.test b/mysql-test/suite/funcs_1/t/myisam_storedproc_07.test index a02f2f544ee..a02f2f544ee 100755..100644 --- a/mysql-test/suite/funcs_1/t/myisam_storedproc_07.test +++ b/mysql-test/suite/funcs_1/t/myisam_storedproc_07.test diff --git a/mysql-test/suite/funcs_1/t/myisam_storedproc_08.test b/mysql-test/suite/funcs_1/t/myisam_storedproc_08.test index 24e574fa9e2..24e574fa9e2 100755..100644 --- a/mysql-test/suite/funcs_1/t/myisam_storedproc_08.test +++ b/mysql-test/suite/funcs_1/t/myisam_storedproc_08.test diff --git a/mysql-test/suite/funcs_1/t/myisam_storedproc_10.test b/mysql-test/suite/funcs_1/t/myisam_storedproc_10.test index 6b4f6c21b62..6b4f6c21b62 100755..100644 --- a/mysql-test/suite/funcs_1/t/myisam_storedproc_10.test +++ b/mysql-test/suite/funcs_1/t/myisam_storedproc_10.test diff --git a/mysql-test/suite/funcs_1/t/myisam_trig_0102.test b/mysql-test/suite/funcs_1/t/myisam_trig_0102.test index 77bde5f99ef..fd326b2f061 100644 --- a/mysql-test/suite/funcs_1/t/myisam_trig_0102.test +++ b/mysql-test/suite/funcs_1/t/myisam_trig_0102.test @@ -5,28 +5,10 @@ # Set $engine_type let $engine_type= myisam; -# Decide, if the objects are to be (re)created -# -# - once at the beginning of a set of testcases ('$NO_REFRESH' <> '' --> TRUE) -# That means the current script must not (re)create any object. -# It can expect, that the objects already exist. -# -# - per every testscript/case ('$NO_REFRESH' = '' --> FALSE) -# That means all objects have to be (re)created within the current script. - -eval SET @NO_REFRESH = IF( '$NO_REFRESH' = '', 0, 1); - -# FIXME Replace the following, when "if" for mysqltest is available -let $run= `SELECT @NO_REFRESH = 0`; -while ($run) -{ - - # Create some objects needed in many testcases - USE test; - --source suite/funcs_1/include/myisam_tb3.inc - - let $run= 0; -} +# Create some objects needed in many testcases +USE test; +--source suite/funcs_1/include/myisam_tb3.inc --source suite/funcs_1/triggers/triggers_0102.inc +DROP TABLE test.tb3; diff --git a/mysql-test/suite/funcs_1/t/myisam_trig_03.test b/mysql-test/suite/funcs_1/t/myisam_trig_03.test index 6edaaf7d14c..77b2d16a2a6 100644 --- a/mysql-test/suite/funcs_1/t/myisam_trig_03.test +++ b/mysql-test/suite/funcs_1/t/myisam_trig_03.test @@ -5,28 +5,10 @@ # Set $engine_type let $engine_type= myisam; -# Decide, if the objects are to be (re)created -# -# - once at the beginning of a set of testcases ('$NO_REFRESH' <> '' --> TRUE) -# That means the current script must not (re)create any object. -# It can expect, that the objects already exist. -# -# - per every testscript/case ('$NO_REFRESH' = '' --> FALSE) -# That means all objects have to be (re)created within the current script. - -eval SET @NO_REFRESH = IF( '$NO_REFRESH' = '', 0, 1); - -# FIXME Replace the following, when "if" for mysqltest is available -let $run= `SELECT @NO_REFRESH = 0`; -while ($run) -{ - - # Create some objects needed in many testcases - USE test; - --source suite/funcs_1/include/myisam_tb3.inc - - let $run= 0; -} +# Create some objects needed in many testcases +USE test; +--source suite/funcs_1/include/myisam_tb3.inc --source suite/funcs_1/triggers/triggers_03.inc +DROP TABLE test.tb3; diff --git a/mysql-test/suite/funcs_1/t/myisam_trig_0407.test b/mysql-test/suite/funcs_1/t/myisam_trig_0407.test index a28959b407e..e1e9b6fd230 100644 --- a/mysql-test/suite/funcs_1/t/myisam_trig_0407.test +++ b/mysql-test/suite/funcs_1/t/myisam_trig_0407.test @@ -5,28 +5,10 @@ # Set $engine_type let $engine_type= myisam; -# Decide, if the objects are to be (re)created -# -# - once at the beginning of a set of testcases ('$NO_REFRESH' <> '' --> TRUE) -# That means the current script must not (re)create any object. -# It can expect, that the objects already exist. -# -# - per every testscript/case ('$NO_REFRESH' = '' --> FALSE) -# That means all objects have to be (re)created within the current script. - -eval SET @NO_REFRESH = IF( '$NO_REFRESH' = '', 0, 1); - -# FIXME Replace the following, when "if" for mysqltest is available -let $run= `SELECT @NO_REFRESH = 0`; -while ($run) -{ - - # Create some objects needed in many testcases - USE test; - --source suite/funcs_1/include/myisam_tb3.inc - - let $run= 0; -} +# Create some objects needed in many testcases +USE test; +--source suite/funcs_1/include/myisam_tb3.inc --source suite/funcs_1/triggers/triggers_0407.inc +DROP TABLE test.tb3; diff --git a/mysql-test/suite/funcs_1/t/myisam_trig_08.test b/mysql-test/suite/funcs_1/t/myisam_trig_08.test index dda01314052..225d994a732 100644 --- a/mysql-test/suite/funcs_1/t/myisam_trig_08.test +++ b/mysql-test/suite/funcs_1/t/myisam_trig_08.test @@ -5,28 +5,10 @@ # Set $engine_type let $engine_type= myisam; -# Decide, if the objects are to be (re)created -# -# - once at the beginning of a set of testcases ('$NO_REFRESH' <> '' --> TRUE) -# That means the current script must not (re)create any object. -# It can expect, that the objects already exist. -# -# - per every testscript/case ('$NO_REFRESH' = '' --> FALSE) -# That means all objects have to be (re)created within the current script. - -eval SET @NO_REFRESH = IF( '$NO_REFRESH' = '', 0, 1); - -# FIXME Replace the following, when "if" for mysqltest is available -let $run= `SELECT @NO_REFRESH = 0`; -while ($run) -{ - - # Create some objects needed in many testcases - USE test; - --source suite/funcs_1/include/myisam_tb3.inc - - let $run= 0; -} +# Create some objects needed in many testcases +USE test; +--source suite/funcs_1/include/myisam_tb3.inc --source suite/funcs_1/triggers/triggers_08.inc +DROP TABLE test.tb3; diff --git a/mysql-test/suite/funcs_1/t/myisam_trig_09.test b/mysql-test/suite/funcs_1/t/myisam_trig_09.test index 9f2c932e608..f5c53f48adb 100644 --- a/mysql-test/suite/funcs_1/t/myisam_trig_09.test +++ b/mysql-test/suite/funcs_1/t/myisam_trig_09.test @@ -5,28 +5,10 @@ # Set $engine_type let $engine_type= myisam; -# Decide, if the objects are to be (re)created -# -# - once at the beginning of a set of testcases ('$NO_REFRESH' <> '' --> TRUE) -# That means the current script must not (re)create any object. -# It can expect, that the objects already exist. -# -# - per every testscript/case ('$NO_REFRESH' = '' --> FALSE) -# That means all objects have to be (re)created within the current script. - -eval SET @NO_REFRESH = IF( '$NO_REFRESH' = '', 0, 1); - -# FIXME Replace the following, when "if" for mysqltest is available -let $run= `SELECT @NO_REFRESH = 0`; -while ($run) -{ - - # Create some objects needed in many testcases - USE test; - --source suite/funcs_1/include/myisam_tb3.inc - - let $run= 0; -} +# Create some objects needed in many testcases +USE test; +--source suite/funcs_1/include/myisam_tb3.inc --source suite/funcs_1/triggers/triggers_09.inc +DROP TABLE test.tb3; diff --git a/mysql-test/suite/funcs_1/t/myisam_trig_1011ext.test b/mysql-test/suite/funcs_1/t/myisam_trig_1011ext.test index b4d29476aa5..bbf226cdea6 100644 --- a/mysql-test/suite/funcs_1/t/myisam_trig_1011ext.test +++ b/mysql-test/suite/funcs_1/t/myisam_trig_1011ext.test @@ -14,19 +14,10 @@ let $engine_type= myisam; # - per every testscript/case ('$NO_REFRESH' = '' --> FALSE) # That means all objects have to be (re)created within the current script. -eval SET @NO_REFRESH = IF( '$NO_REFRESH' = '', 0, 1); - -# FIXME Replace the following, when "if" for mysqltest is available -let $run= `SELECT @NO_REFRESH = 0`; -while ($run) -{ - - # Create some objects needed in many testcases - USE test; - --source suite/funcs_1/include/myisam_tb3.inc - - let $run= 0; -} +# Create some objects needed in many testcases +USE test; +--source suite/funcs_1/include/myisam_tb3.inc --source suite/funcs_1/triggers/triggers_1011ext.inc +DROP TABLE test.tb3; diff --git a/mysql-test/suite/funcs_1/t/myisam_views.test b/mysql-test/suite/funcs_1/t/myisam_views.test index 3fa50a3a2a0..7fc1b991dc6 100644 --- a/mysql-test/suite/funcs_1/t/myisam_views.test +++ b/mysql-test/suite/funcs_1/t/myisam_views.test @@ -5,33 +5,16 @@ # Set $engine_type let $engine_type= myisam; -# Decide, if the objects are to be (re)created -# -# - once at the beginning of a set of testcases ('$NO_REFRESH' <> '' --> TRUE) -# That means the current script must not (re)create any object. -# It can expect, that the objects already exist. -# -# - per every testscript/case ('$NO_REFRESH' = '' --> FALSE) -# That means all objects have to be (re)created within the current script. - -eval SET @NO_REFRESH = IF( '$NO_REFRESH' = '', 0, 1); - -let $run= `SELECT @NO_REFRESH = 0`; -if ($run) -{ - - # Create some objects needed in many testcases - USE test; - --source suite/funcs_1/include/myisam_tb2.inc - --disable_warnings - DROP DATABASE IF EXISTS test1; - --enable_warnings - CREATE DATABASE test1; - USE test1; - --source suite/funcs_1/include/myisam_tb2.inc - USE test; - -} +# Create some objects needed in many testcases +USE test; +--source suite/funcs_1/include/myisam_tb2.inc +--disable_warnings +DROP DATABASE IF EXISTS test1; +--enable_warnings +CREATE DATABASE test1; +USE test1; +--source suite/funcs_1/include/myisam_tb2.inc +USE test; let $message= Attention: The nesting level @max_level in Testcase 3.3.1.A6 (Complicated nested VIEWs) has to be limited to 20 because of @@ -40,10 +23,6 @@ let $message= Attention: The nesting level @max_level in Testcase 3.3.1.A6 SET @limit1 = 20; --source suite/funcs_1/views/views_master.inc -# If we created the database in the above loop we now need to drop it -let $run= `SELECT @NO_REFRESH = 0`; -if ($run) -{ - DROP DATABASE IF EXISTS test1; -} +DROP DATABASE test1; +DROP TABLE test.tb2; |