From dc40a188a9f49ac52f123ec9363c15653e25d9c7 Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 7 Mar 2008 17:33:07 +0100 Subject: WL#4203 Reorganize and fix the data dictionary tests of testsuite funcs_1 1. Fix the following bugs Bug#30440 "datadict" tests (all engines) fail: Character sets depend on configuration Solution: Test variants charset_collation_* adjusted to different builds Bug#32603 "datadict" tests (all engines) fail in "community" tree: "PROFILING" table Solution: Excluding "PROFILING" table from queries Bug#33654 "slow log" is missing a line Solution: Unify the content of the fields TABLES.TABLE_ROWS and STATISTICS.CARDINALITY within result sets Bug#34532 Some funcs_1 tests do not clean up at end of testing Solution: DROP objects/reset global server variables modified during testing + let tests missing implementation end before loading of tables Bug#31421 funcs_1: ndb__datadict fails, discrepancy between scripts and expected results Solution: Cut __datadict tests into smaller tests + generate new results. Bug#33599 INFORMATION_SCHEMA.STATISTICS got a new column INDEX_COMMENT: tests fail (2) Generation of new results during post merge fix Bug#33600 CHARACTER_OCTET_LENGTH is now CHARACTER_MAXIMUM_LENGTH * 4 Generation of new results during post merge fix Bug#33631 Platform-specific replace of CHARACTER_MAXIMUM_LENGTH broken by 4-byte encoding Generation of new results during post merge fix + removal of platform-specific replace routine (no more needed) 2. Restructure the tests - Test not more than one INFORMATION_SCHEMA view per testscript - Separate tests of I_S view layout+functionality from content related to the all time existing databases "information_schema", "mysql" and "test" - Avoid storage engine related variants of tests which are not sensible to storage engines at all. 3. Reimplement or add some subtests + cleanup There is a some probability that even the reviewed changeset - does not fix all bugs from above or - contains new bugs which show up on some platforms <> Linux or on one of the various build types 4. The changeset contains fixes according to - one code review - minor bugs within testing code found after code review (accepted by reviewer) - problems found during tests with 5.0.56 in build environment mysql-test/suite/funcs_1/t/innodb_storedproc_02.test: Change mode to -rw-rw-r-- mysql-test/suite/funcs_1/t/innodb_storedproc_03.test: Change mode to -rw-rw-r-- mysql-test/suite/funcs_1/t/innodb_storedproc_06.test: Change mode to -rw-rw-r-- mysql-test/suite/funcs_1/t/innodb_storedproc_07.test: Change mode to -rw-rw-r-- mysql-test/suite/funcs_1/t/innodb_storedproc_08.test: Change mode to -rw-rw-r-- mysql-test/suite/funcs_1/t/innodb_storedproc_10.test: Change mode to -rw-rw-r-- mysql-test/suite/funcs_1/t/memory_storedproc_02.test: Change mode to -rw-rw-r-- mysql-test/suite/funcs_1/t/memory_storedproc_03.test: Change mode to -rw-rw-r-- mysql-test/suite/funcs_1/t/memory_storedproc_06.test: Change mode to -rw-rw-r-- mysql-test/suite/funcs_1/t/memory_storedproc_07.test: Change mode to -rw-rw-r-- mysql-test/suite/funcs_1/t/memory_storedproc_08.test: Change mode to -rw-rw-r-- mysql-test/suite/funcs_1/t/memory_storedproc_10.test: Change mode to -rw-rw-r-- mysql-test/suite/funcs_1/t/myisam_storedproc_02.test: Change mode to -rw-rw-r-- mysql-test/suite/funcs_1/t/myisam_storedproc_03.test: Change mode to -rw-rw-r-- mysql-test/suite/funcs_1/t/myisam_storedproc_06.test: Change mode to -rw-rw-r-- mysql-test/suite/funcs_1/t/myisam_storedproc_07.test: Change mode to -rw-rw-r-- mysql-test/suite/funcs_1/t/myisam_storedproc_08.test: Change mode to -rw-rw-r-- mysql-test/suite/funcs_1/t/myisam_storedproc_10.test: Change mode to -rw-rw-r-- mysql-test/suite/funcs_1/storedproc/storedproc_02.inc: Change mode to -rw-rw-r-- mysql-test/suite/funcs_1/storedproc/storedproc_03.inc: Change mode to -rw-rw-r-- mysql-test/suite/funcs_1/storedproc/storedproc_06.inc: Change mode to -rw-rw-r-- mysql-test/suite/funcs_1/storedproc/storedproc_07.inc: Change mode to -rw-rw-r-- mysql-test/suite/funcs_1/storedproc/storedproc_08.inc: Change mode to -rw-rw-r-- mysql-test/suite/funcs_1/storedproc/storedproc_08_show.inc: Change mode to -rw-rw-r-- mysql-test/suite/funcs_1/storedproc/storedproc_10.inc: Change mode to -rw-rw-r-- BitKeeper/deleted/.del-innodb__load.test: Delete: mysql-test/suite/funcs_1/t/innodb__load.test BitKeeper/deleted/.del-innodb__load.result: Delete: mysql-test/suite/funcs_1/r/innodb__load.result BitKeeper/deleted/.del-myisam__load.test: Delete: mysql-test/suite/funcs_1/t/myisam__load.test BitKeeper/deleted/.del-memory__load.test: Delete: mysql-test/suite/funcs_1/t/memory__load.test BitKeeper/deleted/.del-memory__load.result: Delete: mysql-test/suite/funcs_1/r/memory__load.result BitKeeper/deleted/.del-myisam__load.result: Delete: mysql-test/suite/funcs_1/r/myisam__load.result BitKeeper/deleted/.del-create_database.inc: Delete: mysql-test/suite/funcs_1/include/create_database.inc BitKeeper/deleted/.del-create_user_lowest_priv.inc: Delete: mysql-test/suite/funcs_1/include/create_user_lowest_priv.inc BitKeeper/deleted/.del-create_user_no_super.inc: Delete: mysql-test/suite/funcs_1/include/create_user_no_super.inc BitKeeper/deleted/.del-datadict_help_tables_build.result: Delete: mysql-test/suite/funcs_1/r/datadict_help_tables_build.result BitKeeper/deleted/.del-datadict_help_tables_build.test: Delete: mysql-test/suite/funcs_1/t/datadict_help_tables_build.test BitKeeper/deleted/.del-datadict_help_tables_dev.result: Delete: mysql-test/suite/funcs_1/r/datadict_help_tables_dev.result BitKeeper/deleted/.del-datadict_help_tables_dev.test: Delete: mysql-test/suite/funcs_1/t/datadict_help_tables_dev.test BitKeeper/deleted/.del-datadict_master.inc: Delete: mysql-test/suite/funcs_1/datadict/datadict_master.inc BitKeeper/deleted/.del-datadict_show_schema.inc: Delete: mysql-test/suite/funcs_1/datadict/datadict_show_schema.inc BitKeeper/deleted/.del-datadict_show_table_design.inc: Delete: mysql-test/suite/funcs_1/datadict/datadict_show_table_design.inc BitKeeper/deleted/.del-datadict_tables.inc: Delete: mysql-test/suite/funcs_1/datadict/datadict_tables.inc BitKeeper/deleted/.del-datadict_tables_error.inc: Delete: mysql-test/suite/funcs_1/datadict/datadict_tables_error.inc BitKeeper/deleted/.del-datadict_tables_error_1.inc: Delete: mysql-test/suite/funcs_1/datadict/datadict_tables_error_1.inc BitKeeper/deleted/.del-datadict_tables_error_1044.inc: Delete: mysql-test/suite/funcs_1/datadict/datadict_tables_error_1044.inc BitKeeper/deleted/.del-datadict_tables_error_1049.inc: Delete: mysql-test/suite/funcs_1/datadict/datadict_tables_error_1049.inc BitKeeper/deleted/.del-datadict_tables_error_1051.inc: Delete: mysql-test/suite/funcs_1/datadict/datadict_tables_error_1051.inc BitKeeper/deleted/.del-datadict_tables_error_1146.inc: Delete: mysql-test/suite/funcs_1/datadict/datadict_tables_error_1146.inc BitKeeper/deleted/.del-datadict_tables_error_1288.inc: Delete: mysql-test/suite/funcs_1/datadict/datadict_tables_error_1288.inc BitKeeper/deleted/.del-innodb__datadict.result: Delete: mysql-test/suite/funcs_1/r/innodb__datadict.result BitKeeper/deleted/.del-innodb__datadict.test: Delete: mysql-test/suite/funcs_1/t/innodb__datadict.test BitKeeper/deleted/.del-memory__datadict.test: Delete: mysql-test/suite/funcs_1/t/memory__datadict.test BitKeeper/deleted/.del-myisam__datadict.result: Delete: mysql-test/suite/funcs_1/r/myisam__datadict.result BitKeeper/deleted/.del-myisam__datadict.test: Delete: mysql-test/suite/funcs_1/t/myisam__datadict.test BitKeeper/deleted/.del-memory__datadict.result: Delete: mysql-test/suite/funcs_1/r/memory__datadict.result mysql-test/suite/funcs_1/README.txt: Cleanup in description mysql-test/suite/funcs_1/include/memory_tb1.inc: Move setting of variables into the caller scripts mysql-test/suite/funcs_1/include/memory_tb2.inc: Move setting of variables into the caller scripts mysql-test/suite/funcs_1/include/memory_tb3.inc: Move setting of variables into the caller scripts mysql-test/suite/funcs_1/include/memory_tb4.inc: Move setting of variables into the caller scripts mysql-test/suite/funcs_1/include/sp_tb.inc: Remove outdated comment mysql-test/suite/funcs_1/r/innodb_bitdata.result: Updated results mysql-test/suite/funcs_1/r/innodb_cursors.result: Updated results mysql-test/suite/funcs_1/r/innodb_storedproc_02.result: Updated results mysql-test/suite/funcs_1/r/innodb_storedproc_03.result: Updated results mysql-test/suite/funcs_1/r/innodb_storedproc_06.result: Updated results mysql-test/suite/funcs_1/r/innodb_storedproc_07.result: Updated results mysql-test/suite/funcs_1/r/innodb_storedproc_08.result: Updated results mysql-test/suite/funcs_1/r/innodb_storedproc_10.result: Updated results mysql-test/suite/funcs_1/r/innodb_trig_0102.result: Updated results mysql-test/suite/funcs_1/r/innodb_trig_03.result: Updated results mysql-test/suite/funcs_1/r/innodb_trig_0407.result: Updated results mysql-test/suite/funcs_1/r/innodb_trig_08.result: Updated results mysql-test/suite/funcs_1/r/innodb_trig_09.result: Updated results mysql-test/suite/funcs_1/r/innodb_trig_1011ext.result: Updated results mysql-test/suite/funcs_1/r/innodb_trig_frkey.result: Updated results mysql-test/suite/funcs_1/r/innodb_views.result: Updated results mysql-test/suite/funcs_1/r/memory_bitdata.result: Updated results mysql-test/suite/funcs_1/r/memory_cursors.result: Updated results mysql-test/suite/funcs_1/r/memory_storedproc_02.result: Updated results mysql-test/suite/funcs_1/r/memory_storedproc_03.result: Updated results mysql-test/suite/funcs_1/r/memory_storedproc_06.result: Updated results mysql-test/suite/funcs_1/r/memory_storedproc_07.result: Updated results mysql-test/suite/funcs_1/r/memory_storedproc_08.result: Updated results mysql-test/suite/funcs_1/r/memory_storedproc_10.result: Updated results mysql-test/suite/funcs_1/r/memory_trig_0102.result: Updated results mysql-test/suite/funcs_1/r/memory_trig_03.result: Updated results mysql-test/suite/funcs_1/r/memory_trig_0407.result: Updated results mysql-test/suite/funcs_1/r/memory_trig_08.result: Updated results mysql-test/suite/funcs_1/r/memory_trig_09.result: Updated results mysql-test/suite/funcs_1/r/memory_trig_1011ext.result: Updated results mysql-test/suite/funcs_1/r/memory_views.result: Updated results mysql-test/suite/funcs_1/r/myisam_bitdata.result: Updated results mysql-test/suite/funcs_1/r/myisam_cursors.result: Updated results mysql-test/suite/funcs_1/r/myisam_storedproc_02.result: Updated results mysql-test/suite/funcs_1/r/myisam_storedproc_03.result: Updated results mysql-test/suite/funcs_1/r/myisam_storedproc_06.result: Updated results mysql-test/suite/funcs_1/r/myisam_storedproc_07.result: Updated results mysql-test/suite/funcs_1/r/myisam_storedproc_08.result: Updated results mysql-test/suite/funcs_1/r/myisam_storedproc_10.result: Updated results mysql-test/suite/funcs_1/r/myisam_trig_0102.result: Updated results mysql-test/suite/funcs_1/r/myisam_trig_03.result: Updated results mysql-test/suite/funcs_1/r/myisam_trig_0407.result: Updated results mysql-test/suite/funcs_1/r/myisam_trig_08.result: Updated results mysql-test/suite/funcs_1/r/myisam_trig_09.result: Updated results mysql-test/suite/funcs_1/r/myisam_trig_1011ext.result: Updated results mysql-test/suite/funcs_1/r/myisam_views.result: Updated results mysql-test/suite/funcs_1/storedproc/load_sp_tb.inc: Move setting of variables into the caller scripts mysql-test/suite/funcs_1/t/innodb_bitdata.test: Cleanup mysql-test/suite/funcs_1/t/innodb_cursors.test: Cleanup mysql-test/suite/funcs_1/t/innodb_trig_0102.test: Cleanup mysql-test/suite/funcs_1/t/innodb_trig_03.test: Cleanup mysql-test/suite/funcs_1/t/innodb_trig_0407.test: Cleanup mysql-test/suite/funcs_1/t/innodb_trig_08.test: Cleanup mysql-test/suite/funcs_1/t/innodb_trig_09.test: Cleanup mysql-test/suite/funcs_1/t/innodb_trig_1011ext.test: Cleanup mysql-test/suite/funcs_1/t/innodb_trig_frkey.test: Cleanup mysql-test/suite/funcs_1/t/innodb_views.test: Cleanup mysql-test/suite/funcs_1/t/memory_bitdata.test: Cleanup mysql-test/suite/funcs_1/t/memory_cursors.test: Cleanup mysql-test/suite/funcs_1/t/memory_trig_0102.test: Cleanup mysql-test/suite/funcs_1/t/memory_trig_03.test: Cleanup mysql-test/suite/funcs_1/t/memory_trig_0407.test: Cleanup mysql-test/suite/funcs_1/t/memory_trig_08.test: Cleanup mysql-test/suite/funcs_1/t/memory_trig_09.test: Cleanup mysql-test/suite/funcs_1/t/memory_trig_1011ext.test: Cleanup mysql-test/suite/funcs_1/t/memory_views.test: Cleanup mysql-test/suite/funcs_1/t/myisam_bitdata.test: Cleanup mysql-test/suite/funcs_1/t/myisam_cursors.test: Cleanup mysql-test/suite/funcs_1/t/myisam_trig_0102.test: Cleanup mysql-test/suite/funcs_1/t/myisam_trig_03.test: Cleanup mysql-test/suite/funcs_1/t/myisam_trig_0407.test: Cleanup mysql-test/suite/funcs_1/t/myisam_trig_08.test: Cleanup mysql-test/suite/funcs_1/t/myisam_trig_09.test: Cleanup mysql-test/suite/funcs_1/t/myisam_trig_1011ext.test: Cleanup mysql-test/suite/funcs_1/t/myisam_views.test: Cleanup mysql-test/suite/funcs_1/t/charset_collation_1.test: Check content of information_schema views character_sets, collations, collation_character_set_applicability Build variant 1 mysql-test/suite/funcs_1/t/charset_collation_2.test: Check content of information_schema views character_sets, collations, collation_character_set_applicability Build variant 2 mysql-test/suite/funcs_1/t/charset_collation_3.test: Check content of information_schema views character_sets, collations, collation_character_set_applicability Build variant 3 mysql-test/suite/funcs_1/t/is_basics_mixed.test: Test some basic properties which are independend of the storage engine used for the creation of tables. mysql-test/suite/funcs_1/t/is_character_sets.test: Tests related to functionality of information_schema.character_sets mysql-test/suite/funcs_1/t/is_collation_character_set_applicability.test: Tests related to functionality of information_schema.collation_character_set_applicability mysql-test/suite/funcs_1/t/is_collations.test: Tests related to functionality of information_schema.collations mysql-test/suite/funcs_1/t/is_column_privileges.test: Tests related to functionality of information_schema.column_privileges mysql-test/suite/funcs_1/t/is_column_privileges_is_mysql_test.test: Check content of information_schema.column_privileges about databases mysql and test mysql-test/suite/funcs_1/t/is_columns.test: Tests related to functionality of information_schema.columns mysql-test/suite/funcs_1/t/is_columns_innodb.test: Check content of information_schema.columns when some tables are preloaded Variant for InnoDB mysql-test/suite/funcs_1/t/is_columns_is.test: Check content of information_schema.columns about schema information_schema mysql-test/suite/funcs_1/t/is_columns_memory.test: Check content of information_schema.columns when some tables are preloaded Variant for MEMORY mysql-test/suite/funcs_1/t/is_columns_myisam.test: Check content of information_schema.columns when some tables are preloaded Variant for MyISAM mysql-test/suite/funcs_1/t/is_columns_mysql.test: Check content of information_schema.columns about schema mysql mysql-test/suite/funcs_1/t/is_columns_ndb.test: Check content of information_schema.columns when some tables are preloaded Variant for NDB mysql-test/suite/funcs_1/t/is_key_column_usage.test: Tests related to functionality of information_schema.key_column_usage mysql-test/suite/funcs_1/t/is_routines.test: Tests related to functionality of information_schema.routines mysql-test/suite/funcs_1/t/is_schema_privileges.test: Tests related to functionality of information_schema.schema_privileges mysql-test/suite/funcs_1/t/is_schema_privileges_is_mysql_test.test: Check content of information_schema.schema_privileges about schemas information_schema, mysql and test mysql-test/suite/funcs_1/t/is_schemata.test: Tests related to functionality of information_schema.schemata mysql-test/suite/funcs_1/t/is_schemata_is_mysql_test.test: Check content of information_schema.schemata about schemas information_schema, mysql and test mysql-test/suite/funcs_1/t/is_statistics.test: Tests related to functionality of information_schema.statistics mysql-test/suite/funcs_1/t/is_statistics_is.test: Check content of information_schema.statistics about schema information_schema mysql-test/suite/funcs_1/t/is_statistics_mysql.test: Check content of information_schema.statistics about schema mysql mysql-test/suite/funcs_1/t/is_table_constraints.test: Tests related to functionality of information_schema.table_constraints mysql-test/suite/funcs_1/t/is_table_constraints_is.test: Check content of information_schema.table_constraints about schema information_schema mysql-test/suite/funcs_1/t/is_table_constraints_mysql.test: Check content of information_schema.table_constraints about schema mysql mysql-test/suite/funcs_1/t/is_table_privileges.test: Tests related to functionality of information_schema.table_privileges mysql-test/suite/funcs_1/datadict/basics_mixed1.inc: Auxiliary script mysql-test/suite/funcs_1/datadict/basics_mixed2.inc: Auxiliary script mysql-test/suite/funcs_1/include/cleanup.inc: Auxiliary script mysql-test/suite/funcs_1/r/charset_collation_1.result: Expected result for new test mysql-test/suite/funcs_1/r/charset_collation_2.result: Expected result for new test mysql-test/suite/funcs_1/r/charset_collation_3.result: Expected result for new test mysql-test/suite/funcs_1/r/is_basics_mixed.result: Expected result for new test mysql-test/suite/funcs_1/r/is_character_sets.result: Expected result for new test mysql-test/suite/funcs_1/r/is_collation_character_set_applicability.result: Expected result for new test mysql-test/suite/funcs_1/r/is_collations.result: Expected result for new test mysql-test/suite/funcs_1/r/is_column_privileges.result: Expected result for new test mysql-test/suite/funcs_1/r/is_column_privileges_is_mysql_test.result: Expected result for new test mysql-test/suite/funcs_1/r/is_columns.result: Expected result for new test mysql-test/suite/funcs_1/r/is_columns_innodb.result: Expected result for new test mysql-test/suite/funcs_1/r/is_columns_is.result: Expected result for new test mysql-test/suite/funcs_1/r/is_columns_memory.result: Expected result for new test mysql-test/suite/funcs_1/r/is_columns_myisam.result: Expected result for new test mysql-test/suite/funcs_1/r/is_columns_mysql.result: Expected result for new test mysql-test/suite/funcs_1/r/is_columns_ndb.result: Expected result for new test mysql-test/suite/funcs_1/r/is_key_column_usage.result: Expected result for new test mysql-test/suite/funcs_1/r/is_routines.result: Expected result for new test mysql-test/suite/funcs_1/r/is_schema_privileges.result: Expected result for new test mysql-test/suite/funcs_1/r/is_schema_privileges_is_mysql_test.result: Expected result for new test mysql-test/suite/funcs_1/r/is_schemata.result: Expected result for new test mysql-test/suite/funcs_1/r/is_schemata_is_mysql_test.result: Expected result for new test mysql-test/suite/funcs_1/r/is_statistics.result: Expected result for new test mysql-test/suite/funcs_1/r/is_statistics_is.result: Expected result for new test mysql-test/suite/funcs_1/r/is_statistics_mysql.result: Expected result for new test mysql-test/suite/funcs_1/r/is_table_constraints.result: Expected result for new test mysql-test/suite/funcs_1/r/is_table_constraints_is.result: Expected result for new test mysql-test/suite/funcs_1/r/is_table_constraints_mysql.result: Expected result for new test mysql-test/suite/funcs_1/r/is_table_privileges.result: Expected result for new test mysql-test/suite/funcs_1/r/is_tables.result: Expected result for new test mysql-test/suite/funcs_1/r/is_tables_innodb.result: Expected result for new test mysql-test/suite/funcs_1/r/is_tables_is.result: Expected result for new test mysql-test/suite/funcs_1/r/is_tables_memory.result: Expected result for new test mysql-test/suite/funcs_1/r/is_tables_myisam.result: Expected result for new test mysql-test/suite/funcs_1/r/is_tables_mysql.result: Expected result for new test mysql-test/suite/funcs_1/r/is_tables_ndb.result: Expected result for new test mysql-test/suite/funcs_1/r/is_triggers.result: Expected result for new test mysql-test/suite/funcs_1/r/is_user_privileges.result: Expected result for new test mysql-test/suite/funcs_1/r/is_views.result: Expected result for new test mysql-test/suite/funcs_1/t/is_tables.test: Tests related to functionality of information_schema.tables mysql-test/suite/funcs_1/t/is_tables_innodb.test: Check content of information_schema.tables when some tables are preloaded Variant for InnoDB mysql-test/suite/funcs_1/t/is_tables_is.test: Check content of information_schema.tables about schema information_schema mysql-test/suite/funcs_1/t/is_tables_memory.test: Check content of information_schema.tables when some tables are preloaded Variant for MEMORY mysql-test/suite/funcs_1/t/is_tables_myisam.test: Check content of information_schema.tables when some tables are preloaded Variant for MyISAM mysql-test/suite/funcs_1/t/is_tables_mysql.test: Check content of information_schema.tables about schema mysql mysql-test/suite/funcs_1/t/is_tables_ndb.test: Check content of information_schema.tables when some tables are preloaded Variant for NDB mysql-test/suite/funcs_1/t/is_triggers.test: Tests related to functionality of information_schema.triggers mysql-test/suite/funcs_1/t/is_user_privileges.test: Tests related to functionality of information_schema.user_privileges mysql-test/suite/funcs_1/t/is_views.test: Tests related to functionality of information_schema.views mysql-test/suite/funcs_1/datadict/basics_mixed3.inc: Auxiliary script mysql-test/suite/funcs_1/datadict/charset_collation.inc: Auxiliary script mysql-test/suite/funcs_1/datadict/columns.inc: Auxiliary script mysql-test/suite/funcs_1/datadict/datadict.pre: Auxiliary script mysql-test/suite/funcs_1/datadict/is_table_query.inc: Auxiliary script mysql-test/suite/funcs_1/datadict/statistics.inc: Auxiliary script mysql-test/suite/funcs_1/datadict/table_constraints.inc: Auxiliary script mysql-test/suite/funcs_1/datadict/tables1.inc: Auxiliary script mysql-test/suite/funcs_1/datadict/tables2.inc: Auxiliary script --- mysql-test/suite/funcs_1/t/is_basics_mixed.test | 503 ++++++++++++++++++++++++ 1 file changed, 503 insertions(+) create mode 100644 mysql-test/suite/funcs_1/t/is_basics_mixed.test (limited to 'mysql-test/suite/funcs_1/t/is_basics_mixed.test') 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 +eval +CREATE TABLE db_datadict.t1_first (f1 BIGINT UNIQUE, f2 BIGINT) +ENGINE = $engine_type; +--replace_result $engine_type +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 + WHERE +--sorted_result +SELECT table_name FROM information_schema.tables +WHERE table_schema = 'db_datadict'; +# +# SELECT string_function() + ORDER BY +SELECT LENGTH(table_name) FROM information_schema.tables +WHERE table_schema = 'db_datadict' ORDER BY table_name; +# +# SELECT aggregate_function() + 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 +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 +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 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 +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. +# -- cgit v1.2.1