diff options
| author | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-05-04 18:00:34 +0000 |
|---|---|---|
| committer | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-05-04 18:00:34 +0000 |
| commit | 3f619478f796eddbba6e39502fe941b285dd97b1 (patch) | |
| tree | e2c7b5777f728320e5b5542b6213fd3591ba51e2 /mysql-test/suite/funcs_1/datadict | |
| parent | Initial commit. (diff) | |
| download | mariadb-3f619478f796eddbba6e39502fe941b285dd97b1.tar.xz mariadb-3f619478f796eddbba6e39502fe941b285dd97b1.zip | |
Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'mysql-test/suite/funcs_1/datadict')
22 files changed, 3982 insertions, 0 deletions
diff --git a/mysql-test/suite/funcs_1/datadict/basics_mixed1.inc b/mysql-test/suite/funcs_1/datadict/basics_mixed1.inc new file mode 100644 index 00000000..ba51bfce --- /dev/null +++ b/mysql-test/suite/funcs_1/datadict/basics_mixed1.inc @@ -0,0 +1,49 @@ +# suite/funcs_1/datadict/basics_mixed1.inc +# +# Auxiliary script to be sourced by suite/funcs_1/t/is_basics_mixed.test +# +# 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. +# + +# 1 Attempt to create tables and views when residing in information_schema +# 1.1 CREATE TABLE +USE information_schema; +let $message= root: create a table with a name of an IS table directly in IS; +let $dd_part1= CREATE TABLE; +let $dd_part2= ( c1 INT ); +--source suite/funcs_1/datadict/basics_mixed2.inc +--error ER_DBACCESS_DENIED_ERROR +CREATE TABLE t1 (f1 INT, f2 INT, f3 INT); +# +# 1.2 CREATE VIEW +# 1.2.1 Hit on existing INFORMATION_SCHEMA table +--error ER_DBACCESS_DENIED_ERROR +CREATE VIEW tables AS SELECT 'garbage'; +--error ER_DBACCESS_DENIED_ERROR +CREATE VIEW tables AS SELECT * FROM information_schema.tables; +# 1.2.2 New view +--error ER_DBACCESS_DENIED_ERROR +CREATE VIEW v1 AS SELECT 'garbage'; + +# 2 Attempt to create tables and views when residing in information_schema +# 1.1 CREATE TABLE +USE test; +let $message= root: create a table with a name of an IS table from other db; +let $dd_part1= CREATE TABLE information_schema.; +let $dd_part2= ( c1 INT ); +--source suite/funcs_1/datadict/basics_mixed2.inc +--error ER_DBACCESS_DENIED_ERROR +CREATE TABLE information_schema.t1 (f1 INT, f2 INT, f3 INT); +# +# Hit on existing INFORMATION_SCHEMA table +--error ER_DBACCESS_DENIED_ERROR +CREATE VIEW information_schema.tables AS SELECT 'garbage'; +--error ER_DBACCESS_DENIED_ERROR +CREATE VIEW information_schema.tables AS +SELECT * FROM information_schema.tables; +# New table +--error ER_DBACCESS_DENIED_ERROR +CREATE VIEW information_schema.v1 AS SELECT 'garbage'; diff --git a/mysql-test/suite/funcs_1/datadict/basics_mixed2.inc b/mysql-test/suite/funcs_1/datadict/basics_mixed2.inc new file mode 100644 index 00000000..6b1f0779 --- /dev/null +++ b/mysql-test/suite/funcs_1/datadict/basics_mixed2.inc @@ -0,0 +1,55 @@ +#### suite/funcs_1/datadict/basics_mixed2.inc +# +# Auxiliary script to be sourced by suite/funcs_1/datadict/is_basics_mixed1.inc +# +# contains all tables from INFORMATION_SCHEMA +# +# Usage example(snip of script): +# let $dd_part1= CREATE TABLE information_schema.; +# let $dd_part2= ( c1 INT ); +# --source suite/funcs_1/datadict/basics_mixed2.inc +# +# We expect to get +# ERROR 42000: Access denied for user 'root'@'localhost' +# to database 'information_schema' +# for every statement. +# +# 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. +# + +--error ER_DBACCESS_DENIED_ERROR +eval $dd_part1 schemata $dd_part2; +--error ER_DBACCESS_DENIED_ERROR +eval $dd_part1 tables $dd_part2; +--error ER_DBACCESS_DENIED_ERROR +eval $dd_part1 columns $dd_part2; +--error ER_DBACCESS_DENIED_ERROR +eval $dd_part1 character_sets $dd_part2; +--error ER_DBACCESS_DENIED_ERROR +eval $dd_part1 collations $dd_part2; +--error ER_DBACCESS_DENIED_ERROR +eval $dd_part1 collation_character_set_applicability $dd_part2; +--error ER_DBACCESS_DENIED_ERROR +eval $dd_part1 routines $dd_part2; +--error ER_DBACCESS_DENIED_ERROR +eval $dd_part1 statistics $dd_part2; +--error ER_DBACCESS_DENIED_ERROR +eval $dd_part1 views $dd_part2; +--error ER_DBACCESS_DENIED_ERROR +eval $dd_part1 user_privileges $dd_part2; +--error ER_DBACCESS_DENIED_ERROR +eval $dd_part1 schema_privileges $dd_part2; +--error ER_DBACCESS_DENIED_ERROR +eval $dd_part1 table_privileges $dd_part2; +--error ER_DBACCESS_DENIED_ERROR +eval $dd_part1 column_privileges $dd_part2; +--error ER_DBACCESS_DENIED_ERROR +eval $dd_part1 table_constraints $dd_part2; +--error ER_DBACCESS_DENIED_ERROR +eval $dd_part1 key_column_usage $dd_part2; +--error ER_DBACCESS_DENIED_ERROR +eval $dd_part1 triggers $dd_part2; + diff --git a/mysql-test/suite/funcs_1/datadict/basics_mixed3.inc b/mysql-test/suite/funcs_1/datadict/basics_mixed3.inc new file mode 100644 index 00000000..f6a29063 --- /dev/null +++ b/mysql-test/suite/funcs_1/datadict/basics_mixed3.inc @@ -0,0 +1,42 @@ +#### suite/funcs_1/datadict/basics_mixed3.inc +# +# Auxiliary routine to be sourced by suite/funcs_1/t/is_basics_mixed.test +# +# Check if INFORMATION_SCHEMA tables contain a schema_name like 'db_data%'. +# +# 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. +# + +# No column with the name of a database contained in: +# character_sets collations collation_character_set_applicability +# user_privileges +SELECT DISTINCT table_schema FROM information_schema.columns +WHERE table_schema LIKE 'db_data%'; +SELECT DISTINCT table_schema FROM information_schema.column_privileges +WHERE table_schema LIKE 'db_data%'; +SELECT DISTINCT constraint_schema,table_schema +FROM information_schema.key_column_usage +WHERE constraint_schema LIKE 'db_data%' OR table_schema LIKE 'db_data%'; +SELECT DISTINCT routine_schema FROM information_schema.routines +WHERE routine_schema LIKE 'db_data%'; +SELECT DISTINCT schema_name FROM information_schema.schemata +WHERE schema_name LIKE 'db_data%'; +SELECT DISTINCT table_schema FROM information_schema.schema_privileges +WHERE table_schema LIKE 'db_data%'; +SELECT DISTINCT table_schema,index_schema FROM information_schema.statistics +WHERE table_schema LIKE 'db_data%' OR index_schema LIKE 'db_data%'; +SELECT DISTINCT table_schema FROM information_schema.tables +WHERE table_schema LIKE 'db_data%'; +SELECT DISTINCT constraint_schema,table_schema +FROM information_schema.table_constraints +WHERE constraint_schema LIKE 'db_data%' OR table_schema LIKE 'db_data%'; +SELECT DISTINCT table_schema FROM information_schema.table_privileges +WHERE table_schema LIKE 'db_data%'; +SELECT DISTINCT trigger_schema,event_object_schema +FROM information_schema.triggers +WHERE trigger_schema LIKE 'db_data%' OR event_object_schema LIKE 'db_data%'; +SELECT DISTINCT table_schema FROM information_schema.views +WHERE table_schema LIKE 'db_data%'; diff --git a/mysql-test/suite/funcs_1/datadict/columns.inc b/mysql-test/suite/funcs_1/datadict/columns.inc new file mode 100644 index 00000000..a03fef9c --- /dev/null +++ b/mysql-test/suite/funcs_1/datadict/columns.inc @@ -0,0 +1,90 @@ +# suite/funcs_1/datadict/is_columns.inc +# +# Auxiliary script to be sourced by +# is_columns_is +# is_columns_mysql +# is_columns_<engine> +# +# Purpose: +# Check the content of information_schema.columns about tables within certain +# database/s. +# +# Usage: +# The variable $my_where has to +# - be set before sourcing this script. +# - contain the first part of the WHERE qualification +# Example: +# let $my_where = WHERE table_schema = 'information_schema' +# AND table_name <> 'profiling'; +# --source suite/funcs_1/datadict/is_columns.inc +# +# 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_bug_12777.inc + +# The following is needed as embedded server can be compiled with and without +# privlege tables + +eval +SELECT * FROM information_schema.columns +$my_where +ORDER BY table_schema, table_name, column_name; + +--echo ########################################################################## +--echo # Show the quotient of CHARACTER_OCTET_LENGTH and CHARACTER_MAXIMUM_LENGTH +--echo ########################################################################## +eval +SELECT DISTINCT + CHARACTER_OCTET_LENGTH / CHARACTER_MAXIMUM_LENGTH AS COL_CML, + DATA_TYPE, + CHARACTER_SET_NAME, + COLLATION_NAME +FROM information_schema.columns +$my_where +AND CHARACTER_OCTET_LENGTH / CHARACTER_MAXIMUM_LENGTH = 1 +ORDER BY CHARACTER_SET_NAME, COLLATION_NAME, COL_CML; + +#FIXME 3.2.6.2: check the value 2.0079 tinytext ucs2 ucs2_general_ci +eval +SELECT DISTINCT + CHARACTER_OCTET_LENGTH / CHARACTER_MAXIMUM_LENGTH AS COL_CML, + DATA_TYPE, + CHARACTER_SET_NAME, + COLLATION_NAME +FROM information_schema.columns +$my_where +AND CHARACTER_OCTET_LENGTH / CHARACTER_MAXIMUM_LENGTH <> 1 +ORDER BY CHARACTER_SET_NAME, COLLATION_NAME, COL_CML; + +eval +SELECT DISTINCT + CHARACTER_OCTET_LENGTH / CHARACTER_MAXIMUM_LENGTH AS COL_CML, + DATA_TYPE, + CHARACTER_SET_NAME, + COLLATION_NAME +FROM information_schema.columns +$my_where + AND CHARACTER_OCTET_LENGTH / CHARACTER_MAXIMUM_LENGTH IS NULL +ORDER BY CHARACTER_SET_NAME, COLLATION_NAME, COL_CML; + +echo --> CHAR(0) is allowed (see manual), and here both CHARACHTER_* values; +echo --> are 0, which is intended behavior, and the result of 0 / 0 IS NULL; +--source suite/funcs_1/datadict/datadict_bug_12777.inc +eval +SELECT CHARACTER_OCTET_LENGTH / CHARACTER_MAXIMUM_LENGTH AS COL_CML, + TABLE_SCHEMA, + TABLE_NAME, + COLUMN_NAME, + DATA_TYPE, + CHARACTER_MAXIMUM_LENGTH, + CHARACTER_OCTET_LENGTH, + CHARACTER_SET_NAME, + COLLATION_NAME, + COLUMN_TYPE +FROM information_schema.columns +$my_where +ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION; diff --git a/mysql-test/suite/funcs_1/datadict/datadict.pre b/mysql-test/suite/funcs_1/datadict/datadict.pre new file mode 100644 index 00000000..cedc24aa --- /dev/null +++ b/mysql-test/suite/funcs_1/datadict/datadict.pre @@ -0,0 +1,54 @@ +#### suite/funcs_1/datadict/datadict.pre +# +# Auxiliary script which loads prerequisites +# (variables needed for --replace_result ...) +# in datadictionary 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. +# +--disable_query_log + +# Bug#12777 Different size shown for VARCHAR(n) columns (with n> 64) +# in INFORMATION_SCHEMA +# This bug was unfortunately (for testers) declared to be no bug. +# So CHARACTER_MAXIMUM_LENGTH of several <whatever>_CATALOG columns within +# the INFORMATION_SCHEMA depends on PATH_MAX of the operating system. +# Workaround for this problem: +# Get the size of ONE known colum and check the size against some values to +# be able to use the correct --replace_result statement. Using this only the +# one pair of 'wrong' values is replaced and not all occurrencies of all +# possible pairs of values. See bug #12777 for details. +SELECT character_maximum_length INTO @CML +FROM information_schema.columns +WHERE table_schema = 'information_schema' + AND table_name = 'columns' + AND column_name = 'table_catalog'; + +let $bug_12777_0512= `SELECT @CML = 512`; +let $bug_12777_1023= `SELECT @CML = 1023`; +let $bug_12777_1024= `SELECT @CML = 1024`; +let $bug_12777_2048= `SELECT @CML = 2048`; +# 4096 is the value used in the files with expected results. +let $bug_12777_4095= `SELECT @CML = 4095`; +if (0) +{ + # enable this for debugging only, but NOT in a pushed version, as then the + # result changes from OS to OS ... + eval SELECT @CML AS 'CML', + $bug_12777_0512 AS '512', + $bug_12777_1023 AS '1023', + $bug_12777_1024 AS '1024', + $bug_12777_2048 AS '2048', + $bug_12777_4095 AS '4095'; +} + + +# Prepare a variable to be able to suppress machine dependant diffs +# this can be used in: --replace_result $SERVER_NAME <SERVER_NAME> +let $SERVER_NAME= `SELECT DISTINCT host FROM mysql.user + WHERE host NOT In ("localhost", "127.0.0.1", "%")`; +--enable_query_log + diff --git a/mysql-test/suite/funcs_1/datadict/datadict_bug_12777.inc b/mysql-test/suite/funcs_1/datadict/datadict_bug_12777.inc new file mode 100644 index 00000000..5ba969fe --- /dev/null +++ b/mysql-test/suite/funcs_1/datadict/datadict_bug_12777.inc @@ -0,0 +1,60 @@ +#### --source suite/funcs_1/datadict/datadict_bug_12777.inc +# + +# columns in INFORMATION_SCHEMA with VARCHAR(4096) on Linux and Intel or AMD +# processor are shown as VARCHAR(512) on Windows, VARCHAR(1023) on AIX and HPUX, +# VARCHAR(1024) on Solaris10, ... see below and in bug #12777 for details. +# So we need to replace the output for these systems. There may be other still +# not tested / detected systems. +# +# Setting the variables used below has been moved to the beginning of the datadict +# tests to "suite/funcs_1/datadict/datadict_load.inc". +# +# SELECT character_maximum_length INTO @CML +# FROM information_schema.columns +# WHERE table_schema = 'information_schema' +# AND table_name = 'columns' +# AND column_name = 'table_catalog'; + +# this enables the --replace_result only if needed, using this we never replace +# results on 'simple Linux' and so we will see any changes that might be +# suppressed by the - only on some systems used - replacements. + + +# Windows XP 32bit +if ($bug_12777_0512) +{ + # switch next 2 lines on for debugging the correct detection of the operating systems + # let $message= value 512 detected - 1st replace statement activated!; + # --source include/show_msg.inc + # nnnn 3*n + --replace_result 512 4096 1536 12288 +} + +# aix52, aix52-64bit, hp3750, hp3750-64bit, hpux11, hpux11-64bit, +if ($bug_12777_1023) +{ + # nnnn 3*n + --replace_result 1023 4096 3069 12288 +} + +# Solaris10, 32bit +if ($bug_12777_1024) +{ + # nnnn 3*n + --replace_result 1024 4096 3072 12288 +} + +# Linux Suse 9.3 32bit Intel/AMD +if ($bug_12777_2048) +{ + # nnnn 3*n + --replace_result 2048 4096 6144 12288 +} + +# build-5.0-standard +if ($bug_12777_4095) +{ + # nnnn 3*n + --replace_result 4095 4096 12285 12288 +} diff --git a/mysql-test/suite/funcs_1/datadict/datadict_load.inc b/mysql-test/suite/funcs_1/datadict/datadict_load.inc new file mode 100644 index 00000000..9e3b8766 --- /dev/null +++ b/mysql-test/suite/funcs_1/datadict/datadict_load.inc @@ -0,0 +1,107 @@ +#### suite/funcs_1/datadict/datadict_load.inc +# +# The sub testcases are nearly independend. That is the reason why we do not +# want to abort after the first error. +--disable_abort_on_error + +################################################################################ +# +# prepare variables for --replace_result +# +################################################################################ +--disable_query_log + +# ------------------------------------------------------------------------------ +# Get the size of ONE known colum and check the size against some values to +# be able to use the correct --replace_result statement. Using this only the +# one pair of 'wrong' values is replaced and not all occurrencies of all +# possible pairs of values. See bug #12777 for details. +SELECT character_maximum_length INTO @CML + FROM information_schema.columns + WHERE table_schema = 'information_schema' + AND table_name = 'columns' + AND column_name = 'table_catalog'; + +let $bug_12777_0512= `SELECT @CML = 512`; +let $bug_12777_1023= `SELECT @CML = 1023`; +let $bug_12777_1024= `SELECT @CML = 1024`; +let $bug_12777_2048= `SELECT @CML = 2048`; +# 4096 is the value used in the .results +let $bug_12777_4095= `SELECT @CML = 4095`; + +if (0) +{ + # enable this for debugging only, but NOT in a pushed version, as then the + # result changes from OS to OS ... + eval SELECT @CML AS 'CML', + $bug_12777_0512 AS '512', + $bug_12777_1023 AS '1023', + $bug_12777_1024 AS '1024', + $bug_12777_2048 AS '2048', + $bug_12777_4095 AS '4095'; +} + +# ------------------------------------------------------------------------------ +# prepare a variable to be able to suppress machine dependant diffs +# this can be used in: --replace_result $SERVER_NAME <SERVER_NAME> +# let $SERVER_NAME= `SELECT DISTINCT host FROM mysql.user WHERE host LIKE "%\%" AND host NOT In ("localhost", "127.0.0.1", "%")`; +let $SERVER_NAME= `SELECT DISTINCT host FROM mysql.user WHERE host NOT In ("localhost", "127.0.0.1", "%")`; + + +################################################################################ +# +# load tables +# ----------- +# +# this was part of the 4 files $<engine>_datadict.test, but it has been moved +# here to have only one place where all preparation for the test is done. +# +################################################################################ + +eval SET @ENGINE_INNODB = IF( '$engine_type' = 'innodb', 1, 0); +eval SET @ENGINE_MEMORY = IF( '$engine_type' = 'memory', 1, 0); +eval SET @ENGINE_MYISAM = IF( '$engine_type' = 'myisam', 1, 0); +--enable_query_log + +let $engine_myisam= `SELECT @ENGINE_MYISAM = 1`; +let $engine_innodb= `SELECT @ENGINE_INNODB = 1`; +let $engine_memory= `SELECT @ENGINE_MEMORY = 1`; + +--disable_warnings +DROP DATABASE IF EXISTS test1; +--enable_warnings +CREATE DATABASE test1; +USE test; + +if ($engine_innodb) +{ + --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 + USE test1; + --source suite/funcs_1/include/innodb_tb2.inc +} + +if ($engine_memory) +{ + --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 + USE test1; + --source suite/funcs_1/include/memory_tb2.inc +} + +if ($engine_myisam) +{ + --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 + USE test1; + --source suite/funcs_1/include/myisam_tb2.inc +} + +USE test; +--source suite/funcs_1/include/sp_tb.inc diff --git a/mysql-test/suite/funcs_1/datadict/datadict_priv.inc b/mysql-test/suite/funcs_1/datadict/datadict_priv.inc new file mode 100644 index 00000000..c69d7b00 --- /dev/null +++ b/mysql-test/suite/funcs_1/datadict/datadict_priv.inc @@ -0,0 +1,112 @@ +############## suite/funcs_1/datadict/datadict_priv.inc ################ +# # +# DDL and DML operations on information_schema tables # +# # +# Creation: # +# 2007-08 hhunger Implement this test as part of # +# WL#3982 Test information_schema.processlist # +# # +# Last update: # +# 2007-08-14 mleich Some cleanup # +# # +######################################################################## + +# These variables have to be set before sourcing this file. +# +# information_schema table to be tested +# let $table= processlist; +# +# columns of the information_schema table e.g. to use in a select. +# let $columns= ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO, PROGRESS; +# +# Where clause for an update. +# let $update_where= WHERE id=1 ; +# +# Column to be used in the SET of an update. +# let $set_column= user='any_user' ; +# +# Where clause of a delete. +# let $delete_where= WHERE id=1 ; +# +# Column to be dropped. +# let $drop_column= user; +# +# Column to be indexed +# let $index_col= user; + + +# data access + +eval CREATE TEMPORARY TABLE test.t_$table AS SELECT * FROM $table; + +eval UPDATE test.t_$table SET user='horst' $update_where ; + +--error ER_DBACCESS_DENIED_ERROR +eval INSERT INTO $table SELECT * FROM test.t_$table; + +# bug#30208: CREATE TABLE ...LIKE does not accept dbname.tablename:unknown database +eval DROP TABLE test.t_$table; + +--error ER_VIEW_NONUPD_CHECK +eval CREATE VIEW test.v_$table ($columns) AS SELECT $columns FROM $table WITH CHECK OPTION; + +eval CREATE VIEW test.v_$table ($columns) AS SELECT $columns FROM $table; + +# !!! This query returns a wrong error due to a bug in the code of mwl106 +# !!! Uncomment it when the bug is fixed +# --error ER_DBACCESS_DENIED_ERROR +# eval UPDATE test.v_$TABLE SET TIME=NOW() WHERE id = 1; + +eval DROP VIEW test.v_$table; + +--error ER_DBACCESS_DENIED_ERROR +eval UPDATE $table SET $set_column $update_where; + +--error ER_DBACCESS_DENIED_ERROR +eval DELETE FROM $table $delete_where; + +# change privileges + +--error ER_DBACCESS_DENIED_ERROR +eval REVOKE ALL ON $table FROM current_user; + +--error ER_DBACCESS_DENIED_ERROR +eval GRANT INSERT,UPDATE ON $table TO current_user; + +SHOW GRANTS; + +#---------------------------------------------------------------------- +# table access + +--error ER_DBACCESS_DENIED_ERROR +eval CREATE INDEX i_$table ON $table ($index_col); + +--error ER_DBACCESS_DENIED_ERROR +eval DROP TABLE $table; + +--error ER_DBACCESS_DENIED_ERROR +eval ALTER TABLE $table DROP COLUMN $drop_column; + +--error ER_DBACCESS_DENIED_ERROR +eval ALTER TABLE $table ADD COLUMN (my_column INT); + +--error ER_DBACCESS_DENIED_ERROR +eval RENAME TABLE $table TO new_$table; + +--error ER_DBACCESS_DENIED_ERROR +eval RENAME TABLE $table TO files; + +--error ER_DBACCESS_DENIED_ERROR +eval CREATE TABLE new_$table AS SELECT * FROM $table; + +#---------------------------------------------------------------------- +# database access + +--error ER_DBACCESS_DENIED_ERROR +DROP DATABASE information_schema; + +--error ER_PARSE_ERROR +RENAME DATABASE information_schema TO info_schema; + +--error ER_DBACCESS_DENIED_ERROR +ALTER DATABASE information_schema UPGRADE DATA DIRECTORY NAME; diff --git a/mysql-test/suite/funcs_1/datadict/is_key_column_usage.inc b/mysql-test/suite/funcs_1/datadict/is_key_column_usage.inc new file mode 100644 index 00000000..ba390dfb --- /dev/null +++ b/mysql-test/suite/funcs_1/datadict/is_key_column_usage.inc @@ -0,0 +1,354 @@ +# suite/funcs_1/datadict/is_key_column_usage.inc +# +# Check the layout of information_schema.key_column_usage and the impact of +# CREATE/ALTER/DROP TABLE/VIEW/SCHEMA/COLUMN ... on it. +# +# Note: +# This test is not intended +# - to show information about the all time existing tables +# within the databases information_schema and mysql +# - for checking storage engine properties +# Therefore please do not alter $engine_type. +# +# Author: +# 2008-01-23 mleich WL#4203 Reorganize and fix the data dictionary tests of +# testsuite funcs_1 +# Create this script based on older scripts and new code. +# Last Change: +# 2008-06-11 mleich Move t/is_key_column_usage.test to this file and +# create variants for embedded/non embedded server. +# + +let $engine_type = MEMORY; + +let $is_table = KEY_COLUMN_USAGE; + +# The table INFORMATION_SCHEMA.KEY_COLUMN_USAGE must exist +eval SHOW TABLES FROM information_schema LIKE '$is_table'; + +--echo ####################################################################### +--echo # Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT +--echo ####################################################################### +# Ensure that every INFORMATION_SCHEMA table can be queried with a SELECT +# statement, just as if it were an ordinary user-defined table. +# +--source suite/funcs_1/datadict/is_table_query.inc + + +--echo ######################################################################### +--echo # Testcase 3.2.7.1: INFORMATION_SCHEMA.KEY_COLUMN_USAGE layout +--echo ######################################################################### +# Ensure that the INFORMATION_SCHEMA.KEY_COLUMN_USAGE table has the following +# columns, in the following order: +# +# CONSTRAINT_CATALOG (always shows NULL), +# CONSTRAINT_SCHEMA (shows the database, or schema, in which an accessible +# constraint, or index, resides), +# CONSTRAINT_NAME (shows the name of the accessible constraint), +# TABLE_CATALOG (always shows NULL), +# TABLE_SCHEMA (shows the database, or schema, in which the table constrained +# by that constraint resides), +# TABLE_NAME (shows the name of the table constrained by the constraint), +# COLUMN_NAME (shows the name of a column that is the index key, or part of +# the index key), +# ORDINAL_POSITION (shows the ordinal position of the column within the +# constraint index), +# POSITION_IN_UNIQUE_CONSTRAINT (shows, for a foreign key column, the ordinal +# position of the referenced column within the referenced unique index; +# otherwise NULL). +# added with 5.0.6: +# REFERENCED_TABLE_SCHEMA, +# REFERENCED_TABLE_NAME, +# REFERENCED_COLUMN_NAME +# +--source suite/funcs_1/datadict/datadict_bug_12777.inc +eval DESCRIBE information_schema.$is_table; +--source suite/funcs_1/datadict/datadict_bug_12777.inc +eval SHOW CREATE TABLE information_schema.$is_table; +--source suite/funcs_1/datadict/datadict_bug_12777.inc +eval SHOW COLUMNS FROM information_schema.$is_table; + +# Note: Retrieval of information within information_schema.columns about +# information_schema.key_column_usage is in is_columns_is.test. + +# Show that CONSTRAINT_CATALOG and TABLE_CATALOG are always NULL. +SELECT constraint_catalog, constraint_schema, constraint_name, table_catalog, + table_schema, table_name, column_name +FROM information_schema.key_column_usage +WHERE constraint_catalog IS NOT NULL OR table_catalog IS NOT NULL +ORDER BY BINARY table_schema, BINARY table_name, BINARY column_name, BINARY constraint_name; + + +--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; + +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +connect (testuser1, localhost, testuser1, , db_datadict); +eval $select; + +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +connect (testuser2, localhost, testuser2, , db_datadict); +eval $select; + +# Cleanup +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 impact of DROP TABLE +SELECT table_name, column_name +FROM information_schema.key_column_usage +WHERE table_name = 't1_my_tablex' +ORDER BY table_name, column_name; +DROP TABLE db_datadict.t1_my_tablex; +SELECT table_name, column_name +FROM information_schema.key_column_usage +WHERE table_name = 't1_my_tablex'; +# +# No UNIQUE CONSTRAINT -> no entry in key_column_usage +SELECT table_name FROM information_schema.key_column_usage +WHERE table_name = 't1_my_tablex'; +--replace_result $engine_type <engine_type> +eval +CREATE TABLE db_datadict.t1_my_tablex +ENGINE = $engine_type AS +SELECT 1 AS f1; +SELECT table_name FROM information_schema.key_column_usage +WHERE table_name = 't1_my_tablex'; +# UNIQUE CONSTRAINT -> entry in key_column_usage +ALTER TABLE db_datadict.t1_my_tablex ADD PRIMARY KEY(f1); +SELECT table_name FROM information_schema.key_column_usage +WHERE table_name = 't1_my_tablex'; +# +# Check impact of DROP SCHEMA +SELECT table_name FROM information_schema.key_column_usage +WHERE table_name = 't1_my_tablex'; +DROP DATABASE db_datadict; +SELECT table_name FROM information_schema.key_column_usage +WHERE table_name = 't1_my_tablex'; +# + + +--echo ######################################################################## +--echo # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and +--echo # DDL on INFORMATION_SCHEMA table are not supported +--echo ######################################################################## +# 3.2.1.3: Ensure that no user may execute an INSERT statement on any +# INFORMATION_SCHEMA table. +# 3.2.1.4: Ensure that no user may execute an UPDATE statement on any +# INFORMATION_SCHEMA table. +# 3.2.1.5: Ensure that no user may execute a DELETE statement on any +# INFORMATION_SCHEMA table. +# 3.2.1.8: Ensure that no user may create an index on an +# INFORMATION_SCHEMA table. +# 3.2.1.9: Ensure that no user may alter the definition of an +# INFORMATION_SCHEMA table. +# 3.2.1.10: Ensure that no user may drop an INFORMATION_SCHEMA table. +# 3.2.1.11: Ensure that no user may move an INFORMATION_SCHEMA table to any +# other database. +# 3.2.1.12: Ensure that no user may directly add to, alter, or delete any data +# in an INFORMATION_SCHEMA table. +# +--disable_warnings +DROP DATABASE IF EXISTS db_datadict; +DROP TABLE IF EXISTS db_datadict.t1; +--enable_warnings +CREATE DATABASE db_datadict; +--replace_result $engine_type <engine_type> +eval +CREATE TABLE db_datadict.t1 (f1 BIGINT) +ENGINE = $engine_type; + +# Note(mleich): +# 1. We can get here different error messages. +# 2. We do not want to unify the individual messages to the far to unspecific +# 'Got one of the listed errors'. +let $my_error_message = +##### The previous statement must fail ###### +# Server type | expected error name | expected error message +# -------------------------------------------------------------------------------------------------------------------- +# not embedded | ER_DBACCESS_DENIED_ERROR | ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' +# embedded | ER_NON_INSERTABLE_TABLE | ERROR HY000: The target table schemata of the INSERT is not insertable-into +# | or similar | or similar +; + +--disable_abort_on_error +INSERT INTO information_schema.key_column_usage + (constraint_schema, constraint_name, table_name) +VALUES ( 'mysql', 'primary', 'db'); +if (!$mysql_errno) +{ + --echo $my_error_message + exit; +} +--error ER_DBACCESS_DENIED_ERROR +INSERT INTO information_schema.key_column_usage +SELECT * FROM information_schema.key_column_usage; + +--error ER_DBACCESS_DENIED_ERROR,ER_NON_UPDATABLE_TABLE +UPDATE information_schema.key_column_usage +SET table_name = 'db1' WHERE constraint_name = 'primary'; + +--error ER_DBACCESS_DENIED_ERROR +DELETE FROM information_schema.key_column_usage WHERE table_name = 't1'; +--error ER_DBACCESS_DENIED_ERROR +TRUNCATE information_schema.key_column_usage; + +--error ER_DBACCESS_DENIED_ERROR +CREATE INDEX i3 ON information_schema.key_column_usage(table_name); + +--error ER_DBACCESS_DENIED_ERROR +ALTER TABLE information_schema.key_column_usage ADD f1 INT; + +--error ER_DBACCESS_DENIED_ERROR +DROP TABLE information_schema.key_column_usage; + +--error ER_DBACCESS_DENIED_ERROR +ALTER TABLE information_schema.key_column_usage +RENAME db_datadict.key_column_usage; +--error ER_DBACCESS_DENIED_ERROR +ALTER TABLE information_schema.key_column_usage +RENAME information_schema.xkey_column_usage; +--enable_abort_on_error + +# Cleanup +DROP TABLE db_datadict.t1; +DROP DATABASE db_datadict; diff --git a/mysql-test/suite/funcs_1/datadict/is_routines.inc b/mysql-test/suite/funcs_1/datadict/is_routines.inc new file mode 100644 index 00000000..37e77e81 --- /dev/null +++ b/mysql-test/suite/funcs_1/datadict/is_routines.inc @@ -0,0 +1,523 @@ +# suite/funcs_1/datadict/is_routines.inc +# +# Check the layout of information_schema.routines and the impact of +# CREATE/ALTER/DROP PROCEDURE/FUNCTION ... on it. +# +# Note: +# This test is not intended +# - to show information about the all time existing routines (there are no +# in the moment) within the databases information_schema and mysql +# - for checking storage engine properties +# Therefore please do not alter $engine_type and $other_engine_type. +# +# Author: +# 2008-01-23 mleich WL#4203 Reorganize and fix the data dictionary tests of +# testsuite funcs_1 +# Create this script based on older scripts and new code. +# Last Change: +# 2008-06-11 mleich Move t/is_routines.test to this file and +# create variants for embedded/non embedded server. +# + +--source include/default_charset.inc +set sql_mode=""; + +let $engine_type = MEMORY; +let $other_engine_type = MyISAM; + +let $is_table = ROUTINES; + +# The table INFORMATION_SCHEMA.TABLES must exist +eval SHOW TABLES FROM information_schema LIKE '$is_table'; + +--echo ####################################################################### +--echo # Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT +--echo ####################################################################### +# Ensure that every INFORMATION_SCHEMA table can be queried with a SELECT +# statement, just as if it were an ordinary user-defined table. +# +--source suite/funcs_1/datadict/is_table_query.inc + + +--echo ######################################################################### +--echo # Testcase 3.2.8.1: INFORMATION_SCHEMA.ROUTINES layout +--echo ######################################################################### +# Ensure that the INFORMATION_SCHEMA.ROUTINES table has the following columns, +# in the following order: +# +# SPECIFIC_NAME (shows the name of an accessible stored procedure, or routine), +# ROUTINE_CATALOG (always shows NULL), +# ROUTINE_SCHEMA (shows the database, or schema, in which the routine resides), +# ROUTINE_NAME (shows the same stored procedure name), +# ROUTINE_TYPE (shows whether the stored procedure is a procedure or a function), +# DTD_IDENTIFIER (shows, for a function, the complete data type definition of +# the value the function will return; otherwise NULL), +# ROUTINE_BODY (shows the language in which the stored procedure is written; +# currently always SQL), +# ROUTINE_DEFINITION (shows as much of the routine body as is possible in the +# allotted space), +# EXTERNAL_NAME (always shows NULL), +# EXTERNAL_LANGUAGE (always shows NULL), +# PARAMETER_STYLE (shows the routine's parameter style; always SQL), +# IS_DETERMINISTIC (shows whether the routine is deterministic), +# SQL_DATA_ACCESS (shows the routine's defined sql-data-access clause value), +# SQL_PATH (always shows NULL), +# SECURITY_TYPE (shows whether the routine's defined security_type is 'definer' +# or 'invoker'), +# CREATED (shows the timestamp of the time the routine was created), +# LAST_ALTERED (shows the timestamp of the time the routine was last altered), +# SQL_MODE (shows the sql_mode setting at the time the routine was created), +# ROUTINE_COMMENT (shows the comment, if any, defined for the routine; +# otherwise NULL), +# DEFINER (shows the user who created the routine). +# Starting with MySQL 5.1 +# CHARACTER_SET_CLIENT +# COLLATION_CONNECTION +# DATABASE_COLLATION +# +--source suite/funcs_1/datadict/datadict_bug_12777.inc +eval DESCRIBE information_schema.$is_table; +--source suite/funcs_1/datadict/datadict_bug_12777.inc +--replace_result ENGINE=MyISAM "" ENGINE=Aria "" " PAGE_CHECKSUM=1" "" " PAGE_CHECKSUM=0" "" +eval SHOW CREATE TABLE information_schema.$is_table; +--source suite/funcs_1/datadict/datadict_bug_12777.inc +eval SHOW COLUMNS FROM information_schema.$is_table; + +USE test; +--disable_warnings +DROP PROCEDURE IF EXISTS sp_for_routines; +DROP FUNCTION IF EXISTS function_for_routines; +--enable_warnings +CREATE PROCEDURE sp_for_routines() SELECT 'db_datadict'; +CREATE FUNCTION function_for_routines() RETURNS INT RETURN 0; + +# Show that the column values of +# ROUTINE_CATALOG, EXTERNAL_NAME, EXTERNAL_LANGUAGE, SQL_PATH are always NULL +# and +# ROUTINE_BODY, PARAMETER_STYLE are 'SQL' +# and +# SPECIFIC_NAME = ROUTINE_NAME. +SELECT specific_name,routine_catalog,routine_schema,routine_name,routine_type, + routine_body,external_name,external_language,parameter_style,sql_path +FROM information_schema.routines +WHERE routine_schema = 'test' AND + (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; + +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +connect (testuser1, localhost, testuser1, , db_datadict); +--replace_column 24 "YYYY-MM-DD hh:mm:ss" 25 "YYYY-MM-DD hh:mm:ss" +SELECT * FROM information_schema.routines where routine_schema <> 'sys'; + +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +connect (testuser2, localhost, testuser2, , db_datadict); +--replace_column 24 "YYYY-MM-DD hh:mm:ss" 25 "YYYY-MM-DD hh:mm:ss" +SELECT * FROM information_schema.routines where routine_schema <> 'sys'; + +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +connect (testuser3, localhost, testuser3, , "*NO-ONE*"); +--replace_column 24 "YYYY-MM-DD hh:mm:ss" 25 "YYYY-MM-DD hh:mm:ss" +SELECT * FROM information_schema.routines where routine_schema <> 'sys'; + +# Cleanup +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 24 <created> 25 <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 24 <created> 25 <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 24 <created> 25 <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 24 "YYYY-MM-DD hh:mm:ss" 25 "YYYY-MM-DD hh:mm:ss" +SELECT *, LENGTH(routine_definition) FROM information_schema.routines +WHERE routine_schema = 'db_datadict'; +--horizontal_results + +# Cleanup +DROP DATABASE db_datadict; +# ---------------------------------------------------------------------------------------------- + + +--echo ######################################################################## +--echo # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and +--echo # DDL on INFORMATION_SCHEMA table are not supported +--echo ######################################################################## +# 3.2.1.3: Ensure that no user may execute an INSERT statement on any +# INFORMATION_SCHEMA table. +# 3.2.1.4: Ensure that no user may execute an UPDATE statement on any +# INFORMATION_SCHEMA table. +# 3.2.1.5: Ensure that no user may execute a DELETE statement on any +# INFORMATION_SCHEMA table. +# 3.2.1.8: Ensure that no user may create an index on an INFORMATION_SCHEMA table. +# 3.2.1.9: Ensure that no user may alter the definition of an +# INFORMATION_SCHEMA table. +# 3.2.1.10: Ensure that no user may drop an INFORMATION_SCHEMA table. +# 3.2.1.11: Ensure that no user may move an INFORMATION_SCHEMA table to any +# other database. +# 3.2.1.12: Ensure that no user may directly add to, alter, or delete any data +# in an INFORMATION_SCHEMA table. +# +--disable_warnings +DROP DATABASE IF EXISTS db_datadict; +--enable_warnings +CREATE DATABASE db_datadict; +USE db_datadict; +CREATE PROCEDURE sp_for_routines() SELECT 'db_datadict'; +USE test; + +# Note(mleich): +# 1. We can get here different error messages. +# 2. We do not want to unify the individual messages to the far to unspecific +# 'Got one of the listed errors'. +let $my_error_message = +##### The previous statement must fail ###### +# Server type | expected error name | expected error message +# -------------------------------------------------------------------------------------------------------------------- +# not embedded | ER_DBACCESS_DENIED_ERROR | ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' +# embedded | ER_NON_INSERTABLE_TABLE | ERROR HY000: The target table schemata of the INSERT is not insertable-into +# | or similar | or similar +; + +--disable_abort_on_error +INSERT INTO information_schema.routines (routine_name, routine_type ) +VALUES ('p2', 'procedure'); +if (!$mysql_errno) +{ + --echo $my_error_message + exit; +} +UPDATE information_schema.routines SET routine_name = 'p2' +WHERE routine_body = 'sql'; +if (!$mysql_errno) +{ + --echo $my_error_message + exit; +} +DELETE FROM information_schema.routines ; +if (!$mysql_errno) +{ + --echo $my_error_message + exit; +} +TRUNCATE information_schema.routines ; +if (!$mysql_errno) +{ + --echo $my_error_message + exit; +} +CREATE INDEX i7 ON information_schema.routines (routine_name); +if (!$mysql_errno) +{ + --echo $my_error_message + exit; +} +ALTER TABLE information_schema.routines ADD f1 INT; +if (!$mysql_errno) +{ + --echo $my_error_message + exit; +} +ALTER TABLE information_schema.routines DISCARD TABLESPACE; +if (!$mysql_errno) +{ + --echo $my_error_message + exit; +} +DROP TABLE information_schema.routines ; +if (!$mysql_errno) +{ + --echo $my_error_message + exit; +} +ALTER TABLE information_schema.routines RENAME db_datadict.routines; +if (!$mysql_errno) +{ + --echo $my_error_message + exit; +} +ALTER TABLE information_schema.routines RENAME information_schema.xroutines; +if (!$mysql_errno) +{ + --echo $my_error_message + exit; +} +--enable_abort_on_error + +# Cleanup +DROP DATABASE db_datadict; + diff --git a/mysql-test/suite/funcs_1/datadict/is_schemata.inc b/mysql-test/suite/funcs_1/datadict/is_schemata.inc new file mode 100644 index 00000000..6ddd966d --- /dev/null +++ b/mysql-test/suite/funcs_1/datadict/is_schemata.inc @@ -0,0 +1,298 @@ +# suite/funcs_1/datadict/is_schemata.inc +# +# Check the layout of information_schema.schemata, permissions and the impact of +# CREATE/ALTER/DROP SCHEMA on it. +# +# Note: +# This test is not intended +# - to show information about the all time existing databases +# information_schema and mysql +# - for checking storage engine properties +# +# Author: +# 2008-01-23 mleich WL#4203 Reorganize and fix the data dictionary tests of +# testsuite funcs_1 +# Create this script based on older scripts and new code. +# Last Change: +# 2008-06-11 mleich Move t/is_schemata.test to this file and +# create variants for embedded/non embedded server. +# + +--source include/default_charset.inc +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 +ORDER BY schema_name; + + +--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; + +--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; + +--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; + +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +connect (testuser3, localhost, testuser3, ,"*NO-ONE*"); +# Shows neither db_datadict_1 nor db_datadict_2 +eval $my_select; +--sorted_result +eval $my_show; + +# Cleanup +connection default; +disconnect testuser1; +disconnect testuser2; +disconnect testuser3; +DROP USER 'testuser1'@'localhost'; +DROP USER 'testuser2'@'localhost'; +DROP USER 'testuser3'@'localhost'; +DROP DATABASE db_datadict_1; +DROP DATABASE db_datadict_2; + + +--echo ################################################################################# +--echo # Testcases 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.SCHEMATA modifications +--echo ################################################################################# +# 3.2.1.13: Ensure that the creation of any new database object (e.g. table or +# column) automatically inserts all relevant information on that +# object into every appropriate INFORMATION_SCHEMA table. +# 3.2.1.14: Ensure that the alteration of any existing database object +# automatically updates all relevant information on that object in +# every appropriate INFORMATION_SCHEMA table. +# 3.2.1.15: Ensure that the dropping of any existing database object +# automatically deletes all relevant information on that object from +# every appropriate INFORMATION_SCHEMA table. +# +--disable_warnings +DROP DATABASE IF EXISTS db_datadict; +--enable_warnings + +SELECT * FROM information_schema.schemata WHERE schema_name = 'db_datadict'; +CREATE DATABASE db_datadict CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci'; +SELECT * FROM information_schema.schemata WHERE schema_name = 'db_datadict'; + +# Check modify default CHARACTER SET +SELECT schema_name, default_character_set_name +FROM information_schema.schemata WHERE schema_name = 'db_datadict'; +ALTER SCHEMA db_datadict CHARACTER SET 'utf8'; +SELECT schema_name, default_character_set_name +FROM information_schema.schemata WHERE schema_name = 'db_datadict'; +ALTER SCHEMA db_datadict CHARACTER SET 'latin1'; + +# Check modify default COLLATION +SELECT schema_name, default_collation_name FROM information_schema.schemata +WHERE schema_name = 'db_datadict'; +ALTER SCHEMA db_datadict COLLATE 'latin1_general_cs'; +SELECT schema_name, default_collation_name FROM information_schema.schemata +WHERE schema_name = 'db_datadict'; + +# Check DROP DATABASE +SELECT schema_name +FROM information_schema.schemata WHERE schema_name = 'db_datadict'; +DROP DATABASE db_datadict; +SELECT schema_name +FROM information_schema.schemata WHERE schema_name = 'db_datadict'; + + +--echo ######################################################################## +--echo # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and +--echo # DDL on INFORMATION_SCHEMA tables are not supported +--echo ######################################################################## +# 3.2.1.3: Ensure that no user may execute an INSERT statement on any +# INFORMATION_SCHEMA table. +# 3.2.1.4: Ensure that no user may execute an UPDATE statement on any +# INFORMATION_SCHEMA table. +# 3.2.1.5: Ensure that no user may execute a DELETE statement on any +# INFORMATION_SCHEMA table. +# 3.2.1.8: Ensure that no user may create an index on an INFORMATION_SCHEMA table. +# 3.2.1.9: Ensure that no user may alter the definition of an +# INFORMATION_SCHEMA table. +# 3.2.1.10: Ensure that no user may drop an INFORMATION_SCHEMA table. +# 3.2.1.11: Ensure that no user may move an INFORMATION_SCHEMA table to any +# other database. +# 3.2.1.12: Ensure that no user may directly add to, alter, or delete any data +# in an INFORMATION_SCHEMA table. +# +--disable_warnings +DROP DATABASE IF EXISTS db_datadict; +--enable_warnings +CREATE DATABASE db_datadict CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci'; + +# Note(mleich): +# 1. We can get here different error messages. +# 2. We do not want to unify the individual messages to the far to unspecific +# 'Got one of the listed errors'. +let $my_error_message = +##### The previous statement must fail ###### +# Server type | expected error name | expected error message +# -------------------------------------------------------------------------------------------------------------------- +# not embedded | ER_DBACCESS_DENIED_ERROR | ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' +# embedded | ER_NON_INSERTABLE_TABLE | ERROR HY000: The target table schemata of the INSERT is not insertable-into +# | or similar | or similar +; + +--disable_abort_on_error +INSERT INTO information_schema.schemata + (catalog_name, schema_name, default_character_set_name, sql_path) +VALUES (NULL, 'db1', 'latin1', NULL); +if (!$mysql_errno) +{ + --echo $my_error_message + exit; +} +INSERT INTO information_schema.schemata +SELECT * FROM information_schema.schemata; +if (!$mysql_errno) +{ + --echo $my_error_message + exit; +} +UPDATE information_schema.schemata +SET default_character_set_name = 'utf8' +WHERE schema_name = 'db_datadict'; +if (!$mysql_errno) +{ + --echo $my_error_message + exit; +} +UPDATE information_schema.schemata SET catalog_name = 't_4711'; +if (!$mysql_errno) +{ + --echo $my_error_message + exit; +} +DELETE FROM information_schema.schemata WHERE schema_name = 'db_datadict'; +if (!$mysql_errno) +{ + --echo $my_error_message + exit; +} +TRUNCATE information_schema.schemata; +if (!$mysql_errno) +{ + --echo $my_error_message + exit; +} +CREATE INDEX i1 ON information_schema.schemata(schema_name); +if (!$mysql_errno) +{ + --echo $my_error_message + exit; +} +ALTER TABLE information_schema.schemata ADD f1 INT; +if (!$mysql_errno) +{ + --echo $my_error_message + exit; +} +DROP TABLE information_schema.schemata; +if (!$mysql_errno) +{ + --echo $my_error_message + exit; +} +ALTER TABLE information_schema.schemata RENAME db_datadict.schemata; +if (!$mysql_errno) +{ + --echo $my_error_message + exit; +} +ALTER TABLE information_schema.schemata RENAME information_schema.xschemata; +if (!$mysql_errno) +{ + --echo $my_error_message + exit; +} +--enable_abort_on_error + +# Cleanup +DROP DATABASE db_datadict; + diff --git a/mysql-test/suite/funcs_1/datadict/is_table_query.inc b/mysql-test/suite/funcs_1/datadict/is_table_query.inc new file mode 100644 index 00000000..3ed74131 --- /dev/null +++ b/mysql-test/suite/funcs_1/datadict/is_table_query.inc @@ -0,0 +1,42 @@ +# suite/funcs_1/datadict/is_table_query.inc +# +# Check that every INFORMATION_SCHEMA table can be queried with a SELECT +# statement, just as if it were an ordinary user-defined table. +# (Requirement 3.2.1.1) +# +# The variable $is_table must be set before sourcing this script. +# +# 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. +# +--disable_warnings +DROP VIEW IF EXISTS test.v1; +DROP PROCEDURE IF EXISTS test.p1; +DROP FUNCTION IF EXISTS test.f1; +--enable_warnings +eval CREATE VIEW test.v1 AS SELECT * FROM information_schema.$is_table; +eval CREATE PROCEDURE test.p1() SELECT * FROM information_schema.$is_table; +delimiter //; +eval CREATE FUNCTION test.f1() returns BIGINT +BEGIN + DECLARE counter BIGINT DEFAULT NULL; + SELECT COUNT(*) INTO counter FROM information_schema.$is_table; + RETURN counter; +END// +delimiter ;// + + +# We are not interested to check the content here. +--echo # Attention: The printing of the next result sets is disabled. +--disable_result_log +eval SELECT * FROM information_schema.$is_table; +SELECT * FROM test.v1; +CALL test.p1; +SELECT test.f1(); +--enable_result_log + +DROP VIEW test.v1; +DROP PROCEDURE test.p1; +DROP FUNCTION test.f1; diff --git a/mysql-test/suite/funcs_1/datadict/is_tables.inc b/mysql-test/suite/funcs_1/datadict/is_tables.inc new file mode 100644 index 00000000..b3d0ef4c --- /dev/null +++ b/mysql-test/suite/funcs_1/datadict/is_tables.inc @@ -0,0 +1,473 @@ +# suite/funcs_1/datadict/is_tables.inc +# +# Check the layout of information_schema.tables and the impact of +# CREATE/ALTER/DROP TABLE/VIEW/SCHEMA ... on it. +# +# Note: +# This test is not intended +# - to show information about the all time existing tables +# within the databases information_schema and mysql +# - for checking storage engine properties +# Therefore please do not alter $engine_type and $other_engine_type. +# Some results of the subtests depend on the storage engines assigned. +# +# Author: +# 2008-01-23 mleich WL#4203 Reorganize and fix the data dictionary tests of +# testsuite funcs_1 +# Create this script based on older scripts and new code. +# Last Change: +# 2008-06-11 mleich Move t/is_tables.test to this file and +# create variants for embedded/non embedded server. +# + +--source include/default_charset.inc + +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 'def'), +# 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 'def'. +SELECT table_catalog, table_schema, table_name +FROM information_schema.tables WHERE table_catalog IS NULL OR table_catalog <> 'def'; + + +--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; + +--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; + +--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; + +--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; + +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 +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; +FLUSH TABLES; +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,ER_NON_UPDATABLE_TABLE +UPDATE information_schema.tables SET table_schema = 'test' +WHERE table_name = 't1'; + +--error ER_DBACCESS_DENIED_ERROR +DELETE FROM information_schema.tables WHERE table_name = 't1'; +--error ER_DBACCESS_DENIED_ERROR +TRUNCATE information_schema.tables; + +--error ER_DBACCESS_DENIED_ERROR +CREATE INDEX my_idx_on_tables ON information_schema.tables(table_schema); + +--error ER_DBACCESS_DENIED_ERROR +ALTER TABLE information_schema.tables DROP PRIMARY KEY; +--error ER_DBACCESS_DENIED_ERROR +ALTER TABLE information_schema.tables ADD f1 INT; + +--error ER_DBACCESS_DENIED_ERROR +DROP TABLE information_schema.tables; + +--error ER_DBACCESS_DENIED_ERROR +ALTER TABLE information_schema.tables RENAME db_datadict.tables; +--error ER_DBACCESS_DENIED_ERROR +ALTER TABLE information_schema.tables RENAME information_schema.xtables; + +# Cleanup +DROP DATABASE db_datadict; + diff --git a/mysql-test/suite/funcs_1/datadict/is_triggers.inc b/mysql-test/suite/funcs_1/datadict/is_triggers.inc new file mode 100644 index 00000000..5dda419a --- /dev/null +++ b/mysql-test/suite/funcs_1/datadict/is_triggers.inc @@ -0,0 +1,272 @@ +# suite/funcs_1/datadict/is_triggers.inc +# +# Check the layout of information_schema.triggers and the impact of +# CREATE/ALTER/DROP TABLE/VIEW/SCHEMA ... on it. +# +# Note: +# This test is not intended +# - to show information about the all time existing triggers +# (there are no in the moment) within the databases information_schema +# and mysql +# - for checking storage engine properties +# Therefore please do not alter $engine_type and $other_engine_type. +# +# Author: +# 2008-01-23 mleich WL#4203 Reorganize and fix the data dictionary tests of +# testsuite funcs_1 +# Create this script based on older scripts and new code. +# Last Change: +# 2008-06-11 mleich Move t/is_triggers.test to this file and +# create variants for embedded/non embedded server. +# + +--source include/default_charset.inc +set @save_sql_mode=@@global.sql_mode; +set global sql_mode=""; + +let $engine_type = MEMORY; +let $other_engine_type = MyISAM; + +let $is_table = TRIGGERS; + +# The table INFORMATION_SCHEMA.TRIGGERS must exist +eval SHOW TABLES FROM information_schema LIKE '$is_table'; + +--echo ####################################################################### +--echo # Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT +--echo ####################################################################### +# Ensure that every INFORMATION_SCHEMA table can be queried with a SELECT +# statement, just as if it were an ordinary user-defined table. +# +--source suite/funcs_1/datadict/is_table_query.inc + + +--echo ######################################################################### +--echo # Testcase 3.2.12.1: INFORMATION_SCHEMA.TRIGGERS layout +--echo ######################################################################### +# Ensure that the INFORMATION_SCHEMA.TRIGGERS table has the following columns, +# in the following order: +# +# TRIGGER_CATALOG NULL +# TRIGGER_SCHEMA name of the database in which the trigger occurs +# TRIGGER_NAME +# EVENT_MANIPULATION event associated with the trigger +# ('INSERT', 'DELETE', or 'UPDATE') +# EVENT_OBJECT_CATALOG NULL +# EVENT_OBJECT_SCHEMA database in which the table associated with the +# trigger occurs +# EVENT_OBJECT_TABLE name of the table associated with the trigger +# ACTION_ORDER 0 +# ACTION_CONDITION NULL +# ACTION_STATEMENT +# ACTION_ORIENTATION ROW +# ACTION_TIMING 'BEFORE' or 'AFTER' +# ACTION_REFERENCE_OLD_TABLE NULL +# ACTION_REFERENCE_NEW_TABLE NULL +# ACTION_REFERENCE_OLD_ROW OLD +# ACTION_REFERENCE_NEW_ROW NEW +# CREATED NULL (0) +# SQL_MODE server SQL mode that was in effect at the time +# when the trigger was created +# (also used during trigger execution) +# DEFINER who defined the trigger +# +--source suite/funcs_1/datadict/datadict_bug_12777.inc +eval DESCRIBE information_schema.$is_table; +--source suite/funcs_1/datadict/datadict_bug_12777.inc +--replace_result ENGINE=MyISAM "" ENGINE=Aria "" " PAGE_CHECKSUM=1" "" " PAGE_CHECKSUM=0" "" +eval SHOW CREATE TABLE information_schema.$is_table; +--source suite/funcs_1/datadict/datadict_bug_12777.inc +eval SHOW COLUMNS FROM information_schema.$is_table; + + +# Note: Retrieval of information within information_schema.columns about +# information_schema.tables is in is_columns_is.test. + +# Show that several columns are always NULL. +--replace_column 17 # +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 +ORDER BY trigger_schema, trigger_name; + + +--echo ################################################################################## +--echo # Testcase 3.2.18.2 + 3.2.18.3: INFORMATION_SCHEMA.TRIGGERS accessible information +--echo ################################################################################## +# 3.2.18.2: Ensure that the table shows the relevant information on every +# trigger on which the current user or PUBLIC has privileges. +# 3.2.18.3: Ensure that the table does not show any information on any trigger +# on which the current user and public have no privileges. +# The SUPER (before 5.1.22) or TRIGGER (since 5.1.22) privilege is required for +# - creation of triggers +# - retrieval in INFORMATION_SCHEMA.TRIGGERS (affects size of result set) +# +--disable_warnings +DROP DATABASE IF EXISTS db_datadict; +--enable_warnings +CREATE DATABASE db_datadict; +--error 0,ER_CANNOT_USER +DROP USER 'testuser1'@'localhost'; +CREATE USER 'testuser1'@'localhost'; +--error 0,ER_CANNOT_USER +DROP USER 'testuser2'@'localhost'; +CREATE USER 'testuser2'@'localhost'; +--error 0,ER_CANNOT_USER +DROP USER 'testuser3'@'localhost'; +CREATE USER 'testuser3'@'localhost'; +--error 0,ER_CANNOT_USER +DROP USER 'testuser4'@'localhost'; +CREATE USER 'testuser4'@'localhost'; + +GRANT TRIGGER ON *.* TO 'testuser1'@'localhost'; +GRANT TRIGGER ON *.* TO 'testuser3'@'localhost'; +GRANT TRIGGER ON *.* TO 'testuser4'@'localhost'; +GRANT ALL ON db_datadict.* TO 'testuser1'@'localhost' WITH GRANT OPTION; + +let $my_select = SELECT * FROM information_schema.triggers +WHERE trigger_name = 'trg1'; +let $my_show = SHOW TRIGGERS FROM db_datadict; +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +connect (testuser1, localhost, testuser1, , db_datadict); +--replace_result $engine_type <engine_type> +eval +CREATE TABLE db_datadict.t1 (f1 INT, f2 INT, f3 INT) +ENGINE = $engine_type; +CREATE TRIGGER trg1 BEFORE INSERT +ON db_datadict.t1 FOR EACH ROW SET @test_before = 2, new.f1 = @test_before; +GRANT ALL ON db_datadict.t1 TO 'testuser2'@'localhost'; +REVOKE TRIGGER ON db_datadict.t1 FROM 'testuser2'@'localhost'; +GRANT SELECT ON db_datadict.t1 TO 'testuser3'@'localhost'; +--replace_column 17 # +eval $my_select; +--replace_column 6 # +eval $my_show; + +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +connect (testuser2, localhost, testuser2, , db_datadict); +SHOW GRANTS FOR 'testuser2'@'localhost'; +--echo # No TRIGGER Privilege --> no result for query +--replace_column 17 # +eval $my_select; +--replace_column 6 # +eval $my_show; + +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +connect (testuser3, localhost, testuser3, , test); +SHOW GRANTS FOR 'testuser3'@'localhost'; +--echo # TRIGGER Privilege + SELECT Privilege on t1 --> result for query +--replace_column 17 # +eval $my_select; +--replace_column 6 # +eval $my_show; + +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +connect (testuser4, localhost, testuser4, , test); +SHOW GRANTS FOR 'testuser4'@'localhost'; +--echo # TRIGGER Privilege + no SELECT Privilege on t1 --> result for query +--disable_abort_on_error +SELECT * FROM db_datadict.t1; +DESC db_datadict.t1; +--replace_column 17 # +eval $my_select; +--replace_column 6 # +eval $my_show; + +connection default; +disconnect testuser1; +disconnect testuser2; +disconnect testuser3; +disconnect testuser4; +--replace_column 17 # +eval $my_select; +--replace_column 6 # +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,ER_NON_UPDATABLE_TABLE +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; + +set global sql_mode=@save_sql_mode; + diff --git a/mysql-test/suite/funcs_1/datadict/is_views.inc b/mysql-test/suite/funcs_1/datadict/is_views.inc new file mode 100644 index 00000000..acf12ce5 --- /dev/null +++ b/mysql-test/suite/funcs_1/datadict/is_views.inc @@ -0,0 +1,310 @@ +# suite/funcs_1/datadict/is_views.inc +# +# Check the layout of information_schema.views and the impact of +# CREATE/ALTER/DROP TABLE/VIEW/SCHEMA ... on it. +# +# Note: +# - This test should not check storage engine properties. +# - Please do not change the storage engines used within this test +# except you know that the impact is acceptable. +# Some storage engines might not support the modification of +# properties like in the following tests. +# +# Author: +# 2008-01-23 mleich WL#4203 Reorganize and fix the data dictionary tests of +# testsuite funcs_1 +# Create this script based on older scripts and new code. +# Last Change: +# 2008-06-11 mleich Move t/is_views.test to this file and +# create variants for embedded/non embedded server. +# + +let $engine_type = MEMORY; +let $other_engine_type = MyISAM; + +let $is_table = VIEWS; + +# The table INFORMATION_SCHEMA.VIEWS must exist +eval SHOW TABLES FROM information_schema LIKE '$is_table'; + +--echo ####################################################################### +--echo # Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT +--echo ####################################################################### +# Ensure that every INFORMATION_SCHEMA table can be queried with a SELECT +# statement, just as if it were an ordinary user-defined table. +# +--source suite/funcs_1/datadict/is_table_query.inc + + +--echo ######################################################################### +--echo # Testcase 3.2.13.1: INFORMATION_SCHEMA.VIEWS layout +--echo ######################################################################### +# Ensure that the INFORMATION_SCHEMA.VIEWS table has the following columns, +# in the following order: +# +# TABLE_CATALOG (always shows NULL), +# TABLE_SCHEMA (shows the database, or schema, in which an accessible +# view resides), +# TABLE_NAME (shows the name of a view accessible to the current user), +# VIEW_DEFINITION (shows the SELECT statement that makes up the +# view's definition), +# CHECK_OPTION (shows the value of the WITH CHECK OPTION clause used to define +# the view, either NONE, LOCAL or CASCADED), +# IS_UPDATABLE (shows whether the view is an updatable view), +# DEFINER (added with 5.0.14), +# SECURITY_TYPE (added with 5.0.14). +# +--source suite/funcs_1/datadict/datadict_bug_12777.inc +eval DESCRIBE information_schema.$is_table; +--source suite/funcs_1/datadict/datadict_bug_12777.inc +--replace_result ENGINE=MyISAM "" ENGINE=Aria "" " PAGE_CHECKSUM=1" "" " PAGE_CHECKSUM=0" "" +eval SHOW CREATE TABLE information_schema.$is_table; +--source suite/funcs_1/datadict/datadict_bug_12777.inc +eval SHOW COLUMNS FROM information_schema.$is_table; + +# Note: Retrieval of information within information_schema.columns about +# information_schema.views is in is_columns_is.test. + +# Show that TABLE_CATALOG is always 'def'. +SELECT table_catalog, table_schema, table_name +FROM information_schema.views WHERE table_catalog <> 'def'; + + +--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; + +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +connect (testuser1, localhost, testuser1, ,"*NO-ONE*"); +eval $select; + +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +connect (testuser2, localhost, testuser2, ,"*NO-ONE*"); +eval $select; + +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +connect (test_no_views, localhost, test_no_views, ,"*NO-ONE*"); +eval $select; + +# Cleanup +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_%' +ORDER BY table_schema, table_name; +CREATE VIEW test.t1_view AS SELECT DISTINCT f1 FROM test.t1_table; +CREATE ALGORITHM=MERGE VIEW test.t1_view1 AS SELECT f1 FROM test.t1_table; +CREATE ALGORITHM=TEMPTABLE VIEW test.t1_view2 AS SELECT f1 FROM test.t1_table; +SELECT * FROM information_schema.views +WHERE table_name LIKE 't1_%' +ORDER BY table_schema, table_name; +DROP VIEW test.t1_view1; +DROP VIEW test.t1_view2; + +# +# 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,ER_NON_UPDATABLE_TABLE +UPDATE information_schema.views SET table_schema = 'test' +WHERE table_name = 't1'; + +--error ER_DBACCESS_DENIED_ERROR +DELETE FROM information_schema.views WHERE table_name = 't1'; +--error ER_DBACCESS_DENIED_ERROR +TRUNCATE information_schema.views; + +--error ER_DBACCESS_DENIED_ERROR +CREATE INDEX my_idx_on_views ON information_schema.views(table_schema); + +--error ER_DBACCESS_DENIED_ERROR +ALTER TABLE information_schema.views DROP PRIMARY KEY; +--error ER_DBACCESS_DENIED_ERROR +ALTER TABLE information_schema.views ADD f1 INT; + +--error ER_DBACCESS_DENIED_ERROR +DROP TABLE information_schema.views; + +--error ER_DBACCESS_DENIED_ERROR +ALTER TABLE information_schema.views RENAME db_datadict.views; +--error ER_DBACCESS_DENIED_ERROR +ALTER TABLE information_schema.views RENAME information_schema.xviews; + +# Cleanup +DROP DATABASE db_datadict; + diff --git a/mysql-test/suite/funcs_1/datadict/processlist_priv.inc b/mysql-test/suite/funcs_1/datadict/processlist_priv.inc new file mode 100644 index 00000000..90bc19f2 --- /dev/null +++ b/mysql-test/suite/funcs_1/datadict/processlist_priv.inc @@ -0,0 +1,498 @@ +############ suite/funcs_1/datadict/processlist_priv.inc ############### +# # +# Testing of privileges around # +# SELECT ... PROCESSLIST/SHOW PROCESSLIST # +# # +# Note(mleich): # +# There is a significant risk to get an unstable test because of # +# timing issues. # +# Example1: # +# 1. Disconnect connection X # +# 2. Switch to connection Y # +# 3. SHOW PROCESSLIST might present a record like # +# <ID> <user> <host> <db> Quit 0 cleaning up NULL # +# or even a row where connection X is without # +# "Quit" or "cleaning up". # +# That means our SHOW PROCESSLIST can come too early. # +# Solution: # +# Close the connections at the end of the test. # +# Example2 (2008-08-14 again observed): # +# 1. connection X: SHOW PROCESSLIST/GRANT ... etc. # +# 2. Switch to connection Y # +# 3. SHOW PROCESSLIST might present a record like # +# <ID> <user> <host> <db> Query TIME cleaning up <command> # +# <ID> <user> <host> <db> Query TIME writing to net <command> # +# Problems happens more often in case of slow filesystem! # +# First Solution: # +# Insert a dummy SQL command where the cleanup is most probably # +# fast before switching to another connection and running # +# SHOW/SELECT PROCESSLIST. # +# Suppress writing to protocol by assignment to $variable. # +# let $my_var= `SELECT 1`; # +# Even the 'SELECT 1' was in some cases in state # +# "writing to net". # +# Final Solution: # +# --real_sleep 0.3 # +# This value was at least on my box sufficient. # +# Please inform us if this test fails so that we can adjust # +# the sleep time better or switch to poll routines. # +# # +# Storage engine variants of this test do not make sense. # +# - I_S tables use the MEMORY storage engine whenever possible. # +# - There are some I_S table which need column data types which # +# are not supported by MEMORY. Example: LONGTEXT/BLOB # +# MyISAM will be used for such tables. # +# The column PROCESSLIST.INFO is of data type LONGTEXT # +# ----> MyISAM # +# - There is no impact of the GLOBAL(server) or SESSION default # +# storage engine setting on the engine used for I_S tables. # +# # +# Creation: # +# 2007-08 hhunger Implement this test as part of # +# WL#3982 Test information_schema.processlist # +# # +# Last update: # +# 2008-08-14 mleich Bug#38270 Test "processlist_priv_ps" fails on # +# varying "processlist" output # +# - Replace one sleep by a poll routines # +# - Remove or disable superfluous sleeps # +# # +######################################################################## + +# The following variables are used in "datadict_priv.inc" and here. +# +# information_schema table to be tested +let $table= processlist; +# +# columns of the information_schema table e.g. to use in a select. +let $columns= ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO, TIME_MS, STAGE, MAX_STAGE, PROGRESS, MEMORY_USED, EXAMINED_ROWS, QUERY_ID, INFO_BINARY; +# +# Where clause for an update. +let $update_where= WHERE id=1 ; +# +# Column to be used in the SET of an update. +let $set_column= user='any_user' ; +# +# Where clause of a delete. +let $delete_where= WHERE id=1 ; +# +# Column to be dropped. +let $drop_column= user; +# +# Column to be indexed +let $index_col= user; + +USE information_schema; + +--echo #################################################################################### +--echo 1 Prepare test. +--echo connection default (user=root) +--echo #################################################################################### + +# Check that we have only one connection around. +# If there is more, it may be the check() connection that has not yet ended, +# so we wait for it for up to 10 seconds. + +let $counter=100; +while ($counter) +{ + dec $counter; + let $res= `SELECT COUNT(*) <> 1 FROM processlist`; + if (!$res) + { + # Success; Abort while loop + let $counter=0; + } + if ($res) + { + --sleep 0.1 + if (!$counter) + { + --echo This test expects one connection to the server. + --echo Expectation: USER HOST DB COMMAND STATE INFO + --echo Expectation: root localhost information_schema Query executing SELECT USER,HOST,DB,COMMAND,STATE,INFO FROM processlist ORDER BY ID + --echo But we found in the moment: + SELECT USER,HOST,DB,COMMAND,STATE,INFO FROM processlist ORDER BY ID; + --echo Maybe + --echo - the base configuration (no of parallel auxiliary sessions) of the server has changed + --echo - a parallel test intended for another server accidently connected to our current one + --echo We cannot proceed in this situation. Abort + exit; + } + } +} + +--echo #################################################################################### +--echo 1.1 Create two user +--echo #################################################################################### +# access to info tables as normal user +--error 0, ER_CANNOT_USER +DROP USER ddicttestuser1@'localhost'; +--error 0, ER_CANNOT_USER +DROP USER ddicttestuser2@'localhost'; +CREATE USER ddicttestuser1@'localhost'; +CREATE USER ddicttestuser2@'localhost'; +GRANT SELECT,UPDATE,DELETE,INSERT,CREATE,CREATE TEMPORARY TABLES,CREATE VIEW,DROP on test.* to ddicttestuser1@'localhost'; +GRANT SELECT,UPDATE,DELETE,INSERT,CREATE,CREATE TEMPORARY TABLES,CREATE VIEW,DROP on test.* to ddicttestuser2@'localhost'; +SET PASSWORD FOR ddicttestuser1@'localhost' = PASSWORD('ddictpass'); +SET PASSWORD FOR ddicttestuser2@'localhost' = PASSWORD('ddictpass'); + +--echo #################################################################################### +--echo 1.2 Establish connection con100 (user=ddicttestuser1 with no PROCESS privilege): +connect (con100,localhost,ddicttestuser1,ddictpass,information_schema); +--echo #################################################################################### + + +--echo #################################################################################### +--echo 2 connection default(user=root with default privileges): +--echo SHOW/SELECT shows all processes/threads. +--echo #################################################################################### +connection default; +# Avoid Bug#38270 Test "processlist_priv_ps" fails on varying "processlist" output +# This subtest expects that the connection con100 is in state 'Sleep'. +# Poll till the connection con100 is in state COMMAND = 'Sleep'. +let $wait_timeout= 10; +let $wait_condition= +SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST +WHERE DB = 'information_schema' AND COMMAND = 'Sleep' AND USER = 'ddicttestuser1' AND state=''; +--source include/wait_condition.inc +--replace_result ENGINE=MyISAM "" ENGINE=Aria "" " PAGE_CHECKSUM=1" "" " PAGE_CHECKSUM=0" "" +eval SHOW CREATE TABLE $table; +--replace_result Execute Query +--replace_column 1 ID 3 HOST_NAME 6 TIME 9 TIME_MS +eval SHOW $table; +--replace_column 1 ID 3 HOST_NAME 6 TIME 9 TIME_MS 13 MEMORY 14 MAX_MEMORY 15 ROWS 16 QUERY_ID 18 TID +eval SELECT * FROM $table $select_where ORDER BY id; +--replace_column 1 ID 3 HOST_NAME 6 TIME 9 TIME_MS 13 MEMORY 14 ROWS 15 QUERY_ID 17 TID +eval SELECT $columns FROM $table $select_where ORDER BY id; +--source suite/funcs_1/datadict/datadict_priv.inc +--real_sleep 0.3 + + +--echo #################################################################################### +--echo 3 Switch to connection con100 (user=ddicttestuser1 with no PROCESS privilege): +connection con100; +--echo SHOW/SELECT shows only the processes (1) of the user. +--echo #################################################################################### +# No need for poll routine here. +# The current state of the default session might depend on load of testing box +# but "ddicttestuser1" must not see anything of the root session. +--replace_result ENGINE=MyISAM "" ENGINE=Aria "" " PAGE_CHECKSUM=1" "" " PAGE_CHECKSUM=0" "" +eval SHOW CREATE TABLE $table; +--replace_result Execute Query +--replace_column 1 ID 3 HOST_NAME 6 TIME 9 TIME_MS +eval SHOW $table; +--replace_column 1 ID 3 HOST_NAME 6 TIME 9 TIME_MS 13 MEMORY 14 MAX_MEMORY 15 ROWS 16 QUERY_ID 18 TID +eval SELECT * FROM $table $select_where ORDER BY id; +--replace_column 1 ID 3 HOST_NAME 6 TIME 9 TIME_MS 13 MEMORY 14 ROWS 15 QUERY_ID 17 TID +eval SELECT $columns FROM $table $select_where ORDER BY id; +--source suite/funcs_1/datadict/datadict_priv.inc +--real_sleep 0.3 + + +--echo #################################################################################### +--echo 4 Grant PROCESS privilege to ddicttestuser1 +--echo connection default (user=root) +--echo #################################################################################### +connection default; +GRANT PROCESS ON *.* TO ddicttestuser1@'localhost' IDENTIFIED BY 'ddictpass'; +--real_sleep 0.3 + +--echo #################################################################################### +--echo 4.1 Existing connection con100 (ddicttestuser1) +--echo The user ddicttestuser1 has the PROCESS privilege, but the connection was +--echo established before PROCESS was granted. +--echo SHOW/SELECT shows only the processes (1) of the user. +--echo #################################################################################### +connection con100; +SHOW GRANTS; +--replace_result Execute Query +--replace_column 1 ID 3 HOST_NAME 6 TIME 9 TIME_MS +SHOW processlist; +--replace_column 1 ID 3 HOST_NAME 6 TIME 9 TIME_MS 13 MEMORY 14 MAX_MEMORY 15 ROWS 16 QUERY_ID 18 TID +SELECT * FROM information_schema.processlist; +--real_sleep 0.3 + +--echo #################################################################################### +--echo 4.2 New connection con101 (ddicttestuser1 with PROCESS privilege) +--echo SHOW/SELECT shows all processes/threads. +--echo #################################################################################### +connect (con101,localhost,ddicttestuser1,ddictpass,information_schema); +SHOW GRANTS; +--replace_result Execute Query +--replace_column 1 ID 3 HOST_NAME 6 TIME 9 TIME_MS +SHOW processlist; +--replace_column 1 ID 3 HOST_NAME 6 TIME 9 TIME_MS 13 MEMORY 14 MAX_MEMORY 15 ROWS 16 QUERY_ID 18 TID +SELECT * FROM information_schema.processlist; +--real_sleep 0.3 + + +--echo #################################################################################### +--echo 5 Grant PROCESS privilege to anonymous user. +--echo connection default (user=root) +--echo #################################################################################### +connection default; +create user ''@'localhost'; +GRANT PROCESS ON *.* TO ''@'localhost'; +--real_sleep 0.3 + +--echo #################################################################################### +--echo 5.1 Establish connection (anonymous1,localhost,'',,information_schema) +--echo anonymous user with PROCESS privilege +--echo SHOW/SELECT shows all processes/threads. +--echo #################################################################################### +connect (anonymous1,localhost,"''",,information_schema); +SHOW GRANTS; +--replace_result Execute Query +--replace_column 1 ID 3 HOST_NAME 6 TIME 9 TIME_MS +SHOW processlist; +--replace_column 1 ID 3 HOST_NAME 6 TIME 9 TIME_MS 13 MEMORY 14 MAX_MEMORY 15 ROWS 16 QUERY_ID 18 TID +SELECT * FROM information_schema.processlist; +--real_sleep 0.3 + + +--echo #################################################################################### +--echo 6 Revoke PROCESS privilege from ddicttestuser1 +--echo connection default (user=root) +--echo #################################################################################### +connection default; +REVOKE PROCESS ON *.* FROM ddicttestuser1@'localhost'; +--real_sleep 0.3 + +--echo #################################################################################### +--echo 6.1 New connection con102 (ddicttestuser1 has no more PROCESS privilege) +connect (con102,localhost,ddicttestuser1,ddictpass,information_schema); +--echo Again (compared to state before GRANT PROCESS) only the processes of +--echo ddicttestuser1 are visible. +--echo #################################################################################### +SHOW GRANTS; +--replace_result Execute Query +--replace_column 1 ID 3 HOST_NAME 6 TIME 9 TIME_MS +SHOW processlist; +--replace_column 1 ID 3 HOST_NAME 6 TIME 9 TIME_MS 13 MEMORY 14 MAX_MEMORY 15 ROWS 16 QUERY_ID 18 TID +SELECT * FROM information_schema.processlist; +--real_sleep 0.3 + + +--echo #################################################################################### +--echo 7 Revoke PROCESS privilege from anonymous user +--echo connection default (user=root) +--echo #################################################################################### +connection default; +REVOKE PROCESS ON *.* FROM ''@'localhost'; +--real_sleep 0.3 + +--echo #################################################################################### +--echo 7.1 New connection (anonymous2,localhost,'',,information_schema) +connect (anonymous2,localhost,"''",,information_schema); +--echo The anonymous user has no more the PROCESS privilege +--echo Again only the processes of the anonymous user are visible. +--echo #################################################################################### +SHOW GRANTS FOR ''@'localhost'; +if ($fixed_bug_30395) +{ +# Bug#30395 strange results after REVOKE PROCESS ON *.* FROM ... +--replace_result Execute Query +--replace_column 1 ID 3 HOST_NAME 6 TIME 9 TIME_MS +SHOW processlist; +} +--replace_column 1 ID 3 HOST_NAME 6 TIME 9 TIME_MS 13 MEMORY 14 MAX_MEMORY 15 ROWS 16 QUERY_ID 18 TID +SELECT * FROM information_schema.processlist; +--real_sleep 0.3 + + +--echo #################################################################################### +--echo 8 Grant SUPER (does not imply PROCESS) privilege to ddicttestuser1 +--echo connection default (user=root) +--echo #################################################################################### +connection default; +GRANT SUPER ON *.* TO 'ddicttestuser1'@'localhost'; +--real_sleep 0.3 + +--echo #################################################################################### +--echo 8.1 New connection con103 (ddicttestuser1 with SUPER privilege) +connect (con103,localhost,ddicttestuser1,ddictpass,information_schema); +--echo Only the processes of ddicttestuser1 user are visible. +--echo #################################################################################### +SHOW GRANTS FOR 'ddicttestuser1'@'localhost'; +--replace_result Execute Query +--replace_column 1 ID 3 HOST_NAME 6 TIME 9 TIME_MS +SHOW processlist; +--replace_column 1 ID 3 HOST_NAME 6 TIME 9 TIME_MS 13 MEMORY 14 MAX_MEMORY 15 ROWS 16 QUERY_ID 18 TID +SELECT * FROM information_schema.processlist; +--real_sleep 0.3 + + +--echo #################################################################################### +--echo 9 Revoke SUPER privilege from user ddicttestuser1 +--echo connection default (user=root) +--echo #################################################################################### +connection default; +REVOKE SUPER ON *.* FROM 'ddicttestuser1'@'localhost'; +--real_sleep 0.3 + +--echo #################################################################################### +--echo 9.1 New connection con104 (ddicttestuser1 without SUPER privilege) +connect (con104,localhost,ddicttestuser1,ddictpass,information_schema); +--echo ddicttestuser1 has no more the SUPER privilege. +--echo Only the processes of ddicttestuser1 are visible. +--echo #################################################################################### +SHOW GRANTS FOR 'ddicttestuser1'@'localhost'; +--replace_result Execute Query +--replace_column 1 ID 3 HOST_NAME 6 TIME 9 TIME_MS +SHOW processlist; +--replace_column 1 ID 3 HOST_NAME 6 TIME 9 TIME_MS 13 MEMORY 14 MAX_MEMORY 15 ROWS 16 QUERY_ID 18 TID +SELECT * FROM information_schema.processlist; +--real_sleep 0.3 + + +--echo #################################################################################### +--echo 10 Grant SUPER privilege with grant option to user ddicttestuser1. +--echo connection default (user=root) +--echo #################################################################################### +connection default; +GRANT SUPER ON *.* TO 'ddicttestuser1'@'localhost' WITH GRANT OPTION; +--real_sleep 0.3 + +--echo #################################################################################### +--echo 10.1 New connection con105 (ddicttestuser1 with SUPER privilege and GRANT OPTION) +connect (con105,localhost,ddicttestuser1,ddictpass,information_schema); +--echo Try to grant PROCESS privilege to user ddicttestuser2 without having it. +--echo #################################################################################### +SHOW GRANTS FOR 'ddicttestuser1'@'localhost'; +--error ER_ACCESS_DENIED_ERROR +GRANT PROCESS ON *.* TO 'ddicttestuser2'@'localhost'; + +--echo #################################################################################### +--echo 10.2 Grant SUPER and PROCESS privilege with grant option to user ddicttestuser1 +--echo connection default (user=root) +--echo #################################################################################### +connection default; +GRANT SUPER,PROCESS ON *.* TO 'ddicttestuser1'@'localhost' WITH GRANT OPTION; +--real_sleep 0.3 + +--echo #################################################################################### +--echo 10.3 New connection con106 (ddicttestuser1 with SUPER,PROCESS WITH GRANT OPTION) +connect (con106,localhost,ddicttestuser1,ddictpass,information_schema); +--echo Grant PROCESS privilege to user ddicttestuser2 +--echo #################################################################################### +SHOW GRANTS FOR 'ddicttestuser1'@'localhost'; +GRANT PROCESS ON *.* TO 'ddicttestuser2'@'localhost'; +--real_sleep 0.3 + +--echo #################################################################################### +--echo 10.4 New connection con200 (ddicttestuser2 with PROCESS privilege) +connect (con200,localhost,ddicttestuser2,ddictpass,information_schema); +--echo ddicttestuser2 has now the PROCESS privilege and sees all connections +--echo #################################################################################### +SHOW GRANTS FOR 'ddicttestuser2'@'localhost'; +--replace_result Execute Query +--replace_column 1 ID 3 HOST_NAME 6 TIME 9 TIME_MS +SHOW processlist; +--replace_column 1 ID 3 HOST_NAME 6 TIME 9 TIME_MS 13 MEMORY 14 MAX_MEMORY 15 ROWS 16 QUERY_ID 18 TID +SELECT * FROM information_schema.processlist; +--real_sleep 0.3 + + +--echo #################################################################################### +--echo 11 User ddicttestuser1 revokes PROCESS privilege from user ddicttestuser2 +--echo connection ddicttestuser1; +--echo #################################################################################### +connection con106; +REVOKE PROCESS ON *.* FROM 'ddicttestuser2'@'localhost'; +--real_sleep 0.3 + +--echo #################################################################################### +--echo 11.1 New connection con201 (ddicttestuser2) +connect (con201,localhost,ddicttestuser2,ddictpass,information_schema); +--echo ddicttestuser2 has no more the PROCESS privilege and can only see own connects +--echo #################################################################################### +SHOW GRANTS; +--replace_result Execute Query +--replace_column 1 ID 3 HOST_NAME 6 TIME 9 TIME_MS +SHOW processlist; +--replace_column 1 ID 3 HOST_NAME 6 TIME 9 TIME_MS 13 MEMORY 14 MAX_MEMORY 15 ROWS 16 QUERY_ID 18 TID +SELECT * FROM information_schema.processlist; +--real_sleep 0.3 + +--echo #################################################################################### +--echo 11.2 Revoke SUPER,PROCESS,GRANT OPTION privilege from user ddicttestuser1 +--echo connection default (user=root) +--echo #################################################################################### +connection default; +REVOKE SUPER,PROCESS,GRANT OPTION ON *.* FROM 'ddicttestuser1'@'localhost'; +--real_sleep 0.3 + +--echo #################################################################################### +--echo 11.3 New connection con107 (ddicttestuser1) +connect (con107,localhost,ddicttestuser1,ddictpass,information_schema); +--echo ddicttestuser1 has no more the PROCESS privilege and can only see own connects +--echo He is also unable to GRANT the PROCESS privilege to ddicttestuser2 +--echo #################################################################################### +SHOW GRANTS FOR 'ddicttestuser1'@'localhost'; +--error ER_ACCESS_DENIED_ERROR +GRANT PROCESS ON *.* TO 'ddicttestuser2'@'localhost'; +--replace_result Execute Query +--replace_column 1 ID 3 HOST_NAME 6 TIME 9 TIME_MS +SHOW processlist; +--replace_column 1 ID 3 HOST_NAME 6 TIME 9 TIME_MS 13 MEMORY 14 MAX_MEMORY 15 ROWS 16 QUERY_ID 18 TID +SELECT * FROM information_schema.processlist; +--real_sleep 0.3 + + +--echo #################################################################################### +--echo 12 Revoke the SELECT privilege from user ddicttestuser1 +--echo connection default (user=root) +--echo #################################################################################### +connection default; +REVOKE SELECT ON *.* FROM 'ddicttestuser1'@'localhost'; +--real_sleep 0.3 + +--echo #################################################################################### +--echo 12.1 New connection con108 (ddicttestuser1) +connect (con108,localhost,ddicttestuser1,ddictpass,information_schema); +--echo ddicttestuser1 has neither PROCESS nor SELECT privilege +--echo Manual says: Each MySQL user has the right to access these tables, but can see +--echo only the rows ... +--echo Therefore the missing SELECT privilege does not affect SELECTs on PROCESSLIST. +--echo #################################################################################### +SHOW GRANTS FOR 'ddicttestuser1'@'localhost'; +--replace_result Execute Query +--replace_column 1 ID 3 HOST_NAME 6 TIME 9 TIME_MS +SHOW processlist; +--replace_column 1 ID 3 HOST_NAME 6 TIME 9 TIME_MS 13 MEMORY 14 MAX_MEMORY 15 ROWS 16 QUERY_ID 18 TID +SELECT * FROM information_schema.processlist; +--real_sleep 0.3 + +--echo #################################################################################### +--echo 12.2 Revoke only the SELECT privilege on the information_schema from ddicttestuser1. +--echo connection default (user=root) +--echo #################################################################################### +connection default; +--error ER_DBACCESS_DENIED_ERROR +REVOKE SELECT ON information_schema.* FROM 'ddicttestuser3'@'localhost'; +--real_sleep 0.3 + + +--echo #################################################################################### +--echo connection default (user=root) +--echo Cleanup: close connections, DROP USER etc. +--echo #################################################################################### +connection default; +disconnect con100; +disconnect con101; +disconnect con102; +disconnect con103; +disconnect con104; +disconnect con105; +disconnect con106; +disconnect con107; +disconnect con108; +disconnect con200; +disconnect con201; +disconnect anonymous1; +disconnect anonymous2; +DROP USER ddicttestuser1@'localhost'; +DROP USER ddicttestuser2@'localhost'; +REVOKE USAGE ON *.* FROM ''@'localhost'; +DROP USER ''@'localhost'; diff --git a/mysql-test/suite/funcs_1/datadict/processlist_val.inc b/mysql-test/suite/funcs_1/datadict/processlist_val.inc new file mode 100644 index 00000000..fa6ed404 --- /dev/null +++ b/mysql-test/suite/funcs_1/datadict/processlist_val.inc @@ -0,0 +1,412 @@ +########### suite/funcs_1/datadict/processlist_val.inc ######################### +# # +# Testing of values within INFORMATION_SCHEMA.PROCESSLIST # +# # +# Ensure that the values fit to the current state of the connection and # +# especially that they change if a connection does nothing or runs some SQL. # +# runs some SQL. # +# Examples: # +# - change the default database # +# - send some period of time no SQL command to the server # +# - send a long running query # +# # +# Note(mleich): # +# 1. Please inform me if this test fails because of timing problems. # +# 2. Storage engine variants of this test do not make sense. # +# - I_S tables use the MEMORY storage engine whenever possible. # +# - There are some I_S tables which need column data types which are not # +# supported by MEMORY. Example: LONGTEXT/BLOB # +# MyISAM will be used for such tables. # +# The column PROCESSLIST.INFO is of data type LONGTEXT ----> MyISAM # +# - There is no impact of the GLOBAL(server) or SESSION default storage # +# engine setting on the engine used for I_S tables. # +# 3. The SHOW (FULL) PROCESSLIST command are for comparison. # +# The main test target is INFORMATION_SCHEMA.PROCESSLIST ! # +# 4. Attention: # +# The values of the PROCESSLIST columns HOST and TIME tend to cause # +# problems and therefore their printing has to be suppressed. # +# Examples of the exact values: # +# HOST: 'localhost' (UNIX derivates) # +# 'localhost:<varying_port>' (WINDOWS) # +# TIME: In many cases within this test 0 seconds but if the testing box is # +# overloaded we might get up to 2 seconds. # +# Solution: # +# --replace_column 1 <ID> 3 <HOST_NAME> 6 <TIME> # +# 5. How to debug the script? # +# <graphical diff tool> \ # +# suite/funcs_1/datadict/processlist_val.inc \ # +# <Result|Reject|Log file> # +# I tweaked a lot of the script lines around "echo" so that you will get a # +# lot of useful synchronisation. # +# # +# Creation: # +# 2007-08-09 mleich Implement this test as part of # +# WL#3982 Test information_schema.processlist # +# # +# Last Modification: # +# 2008-07-04 mleich Fix for # +# Bug#37853 Test "funcs_1.processlist_val_ps" fails in # +# various ways # +# - issues with printing of port (Win only) # +# - too optimistic assumptions about timing # +# + corrections of logic in poll routines # +# + minor improvements # +################################################################################ + +# Basic preparations +--error 0, ER_CANNOT_USER +DROP USER test_user@'localhost'; +CREATE USER test_user@'localhost'; +GRANT ALL ON *.* TO test_user@'localhost'; +REVOKE PROCESS ON *.* FROM test_user@'localhost'; +SET PASSWORD FOR test_user@'localhost' = PASSWORD('ddictpass'); +--disable_warnings +DROP TABLE IF EXISTS test.t1; +--enable_warnings +CREATE TABLE test.t1 (f1 BIGINT); +USE test; + + +echo +# Show the definition of the PROCESSLIST table +#-------------------------------------------------------------------------- +; +--replace_result ENGINE=MyISAM "" ENGINE=Aria "" " PAGE_CHECKSUM=1" "" " PAGE_CHECKSUM=0" "" +SHOW CREATE TABLE INFORMATION_SCHEMA.PROCESSLIST; + +echo +# Ensure that the information about the own connection is correct. +#-------------------------------------------------------------------------- +; +# Expected values +# - USER = 'root' +# - HOST (printed value is unified), the exact values are often like +# UNIX: 'localhost' +# WIN: 'localhost:<some port>' +# - DB = 'test' +# - Command IN (no protocol -> 'Query', ps-protocol -> 'Execute') +# - TIME (printed value will be unified), the exact values are like +# "normal" load: 0 (seconds) +# "heavy" load: 1 or more (seconds) +# - State 'Filling schema table' +# - INFO must contain the corresponding SHOW/SELECT PROCESSLIST +# +# 1. Just dump what we get +--replace_column 1 <ID> 3 <HOST_NAME> 6 <TIME> 9 <TIME_MS> 13 <MEMORY> 14 <MAX_MEMORY> 15 <ROWS> 16 <QUERY_ID> 18 <TID> +SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; +--replace_result Execute Query +--replace_column 1 <ID> 3 <HOST_NAME> 6 <TIME> 9 <TIME_MS> 13 <MEMORY> 14 <ROWS> +SHOW FULL PROCESSLIST; +# +# Determine the connection id of the current connection (default) +SET @default_id = CONNECTION_ID(); +# +# 2. There must be exact one connection with @default_id; +SELECT COUNT(*) = 1 AS "Expect exact one connection with this id" +FROM INFORMATION_SCHEMA.PROCESSLIST WHERE ID = @default_id; +# +# 3. Check the remaining stuff +SELECT COUNT(*) = 1 AS "Expect 1" +FROM INFORMATION_SCHEMA.PROCESSLIST WHERE ID = @default_id + AND USER = 'root' AND DB = 'test' AND Command IN('Query','Execute') + AND State = 'Filling schema table'; +# +# 4. Change the DB +USE information_schema; +SELECT COUNT(*) = 1 AS "Is the DB correct?" +FROM INFORMATION_SCHEMA.PROCESSLIST +WHERE ID = @default_id AND DB = 'information_schema'; +# +# 5. Change the statement +let $my_statement = +SELECT @my_info := INFO FROM INFORMATION_SCHEMA.PROCESSLIST +WHERE ID = @default_id; +eval $my_statement; +eval +SELECT @my_info = '$my_statement' + AS 'Is the content of PROCESSLIST.INFO correct?'; +# +# 6. TIME must have a reasonable value +SELECT COUNT(*) = 1 AS "Has TIME a reasonable value?" +FROM INFORMATION_SCHEMA.PROCESSLIST +WHERE ID = @default_id AND 0 <= TIME < 10 AND 0 <= TIME_MS < 10000; + + +echo +# Ensure that the information about an inactive connection is correct. +#-------------------------------------------------------------------------- +; +connect (con1,localhost,test_user,ddictpass,information_schema); +# +connection default; +# We have now a second connection. +# First working phase for the new connection is with Command = 'Connect'. +# This is a very short phase and the likelihood to meet it is +# - nearly zero on average boxes with low parallel load +# - around some percent on weak or overloaded boxes +# (Bug#32153 Status output differs - scheduling ?) +# Therefore we do not try to catch this state. +# We poll till we reach the long lasting phase with Command = 'Sleep'. +# - USER = 'test_user' +# - DB = 'information_schema' +# - Command = 'Sleep' +# - State is empty +# - INFO IS NULL +echo +# Poll till the connection con1 is in state COMMAND = 'Sleep'. +; +let $wait_timeout= 10; +let $wait_condition= SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST + WHERE COMMAND = 'Sleep' AND USER = 'test_user'; +--source include/wait_condition.inc +# 1. Just dump what we get +--replace_column 1 <ID> 3 <HOST_NAME> 6 <TIME> 7 <STATE> 9 <TIME_MS> 13 <MEMORY> 14 <MAX_MEMORY> 15 <ROWS> 16 <QUERY_ID> 18 <TID> +SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; +--replace_result Execute Query +--replace_column 1 <ID> 3 <HOST_NAME> 6 <TIME> +SHOW FULL PROCESSLIST; +# +# Pull ID and TIME of the second connection +SELECT ID,TIME INTO @test_user_con1_id,@time FROM INFORMATION_SCHEMA.PROCESSLIST +WHERE COMMAND = 'Sleep' AND USER = 'test_user'; +# +# 2. The second connection must (behaviour at least since 2007) have an +# ID = ID_of_previous_connection + 1 +SELECT @test_user_con1_id = @default_id + 1 + AS "Did we got the next higher PROCESSLIST ID?"; +# +# 3. TIME must have a reasonable value +SELECT 0 <= @time < 10 AS "Has TIME a reasonable value?"; +# +# 4. HOST must be for both connections similar (varying port on Win) +SELECT COUNT(*) = 2 AS "Is HOST LIKE 'localhost%'?" +FROM INFORMATION_SCHEMA.PROCESSLIST +WHERE HOST LIKE 'localhost%'; +# +# 5. Check the remaining stuff +SELECT COUNT(*) = 1 AS "Expect 1" +FROM INFORMATION_SCHEMA.PROCESSLIST WHERE ID = @test_user_con1_id + AND USER = 'test_user' AND DB = 'information_schema' + AND Command = 'Sleep' AND State = '' AND INFO IS NULL; +# +# 6. Check that TIME increases +let $wait_timeout= 10; +let $wait_condition= SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST + WHERE COMMAND = 'Sleep' AND USER = 'test_user' + AND TIME > @time; +--source include/wait_condition.inc + + +echo +# Ensure that the user test_user sees only connections with his username +# because he has not the PROCESS privilege. +#---------------------------------------------------------------------------- +; +connection con1; +--replace_column 1 <ID> 3 <HOST_NAME> 6 <TIME> 9 <TIME_MS> 13 <MEMORY> 14 <MAX_MEMORY> 15 <ROWS> 16 <QUERY_ID> 18 <TID> +SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; +--replace_result Execute Query +--replace_column 1 <ID> 3 <HOST_NAME> 6 <TIME> +SHOW FULL PROCESSLIST; + + +echo +# Ensure that the user test_user sees all connections with his username. +#---------------------------------------------------------------------------- +; +connect (con2,localhost,test_user,ddictpass,information_schema); +connection default; +# If the testing box is under heavy load we might see within some of the +# next queries connection +# con2 with Command = 'Connect' +# con1 with INFO = 'SHOW FULL PROCESSLIST' and STATE = 'Writing to net' +# Both phases are too short to be checked. +echo +# Poll till all connections of 'test_user' are in a state with COMMAND = 'Sleep' +; +let $wait_condition= SELECT COUNT(*) = 2 FROM INFORMATION_SCHEMA.PROCESSLIST + WHERE USER = 'test_user' AND COMMAND = 'Sleep' AND STATE = ''; +--source include/wait_condition.inc +connection con2; +# Just dump what we get +--replace_column 1 <ID> 3 <HOST_NAME> 6 <TIME> 9 <TIME_MS> 13 <MEMORY> 14 <MAX_MEMORY> 15 <ROWS> 16 <QUERY_ID> 18 <TID> +SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; +--replace_result Execute Query +--replace_column 1 <ID> 3 <HOST_NAME> 6 <TIME> +SHOW FULL PROCESSLIST; +# +connection default; +# Pull the ID of con2, we will need it later +SELECT ID INTO @test_user_con2_id FROM INFORMATION_SCHEMA.PROCESSLIST +WHERE ID <> @test_user_con1_id + AND USER = 'test_user' AND DB = 'information_schema'; + + +echo +# Ensure we get correct information about a connection during work +#---------------------------------------------------------------------------- +; +connection con2; +# "Organise" a long running command to be observed by the root user. +echo +# Send a long enough running statement to the server, but do not +# wait till the result comes back. +; +# Worst case scenario (=high I/O load on testing box): +# - My experience: +# Up to 2 seconds runtime per SELECT ... FROM INFORMATION_SCHEMA.PROCESSLIST +# in rare cases. +# - The following sequence contains ~ 4 of such SELECTs +# Therefore we sleep 10 seconds. +let $sleep_command = +SELECT sleep(10), 17; +send; +eval $sleep_command; +# +connection default; +echo +# Poll till connection con2 is in state 'User sleep'. +; +# Expect to see within the processlist the other connection just during +# statement execution. +# - USER = 'test_user' +# - DB = 'information_schema' +# - Command = 'Query'(run without --ps-protocol)/'Execute' (run --ps-protocol) +# - TIME >= 0 +# - State = 'User sleep' +# - INFO = $sleep_command +let $wait_condition= +SELECT COUNT(*) FROM INFORMATION_SCHEMA.PROCESSLIST +WHERE ID = @test_user_con2_id AND Command IN('Query','Execute') + AND State = 'User sleep' AND INFO IS NOT NULL ; +--source include/wait_condition.inc +# 1. Just dump what we get +--replace_column 1 <ID> 3 <HOST_NAME> 6 <TIME> 9 <TIME_MS> 13 <MEMORY> 14 <MAX_MEMORY> 15 <ROWS> 16 <QUERY_ID> 18 <TID> +SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; +--replace_result Execute Query +--replace_column 1 <ID> 3 <HOST_NAME> 6 <TIME> +SHOW FULL PROCESSLIST; +# +# Pull some information about the connection con2 +SELECT STATE, TIME, INFO INTO @state, @time, @info +FROM INFORMATION_SCHEMA.PROCESSLIST +WHERE ID = @test_user_con2_id; +# 2. TIME must have a reasonable value +SELECT 0 <= @time < 10 AS "Has TIME a reasonable value?"; +# 3. STATE must be 'User sleep' +SELECT @state = 'User sleep' AS "Has STATE the expected value?"; +# 4. INFO must fit +eval SELECT @info = '$sleep_command' AS "Has INFO the expected value?"; +# 5. Check that TIME increases +let $wait_timeout= 10; +let $wait_condition= SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST + WHERE ID = @test_user_con2_id AND INFO IS NOT NULL AND TIME > @time; +--source include/wait_condition.inc +connection con2; +echo +# Pull("reap") the result set from the statement executed with "send". +; +reap; +connection default; +echo +# Poll till all connections of 'test_user' are in a state with COMMAND = 'Sleep' +; +let $wait_timeout= 10; +let $wait_condition= SELECT COUNT(*) = 2 FROM INFORMATION_SCHEMA.PROCESSLIST + WHERE COMMAND = 'Sleep' AND USER = 'test_user'; +--source include/wait_condition.inc + + +echo +# Ensure that we see that a connection "hangs" when colliding with a +# WRITE TABLE LOCK +#---------------------------------------------------------------------------- +; +LOCK TABLE test.t1 WRITE; +# +connection con2; +echo +# Send a statement to the server, but do not wait till the result +# comes back. We will pull this later. +; +send +SELECT COUNT(*) FROM test.t1; +connection default; +echo +# Poll till INFO is no more NULL and State = 'Waiting for table metadata lock'. +; +let $wait_condition= SELECT COUNT(*) FROM INFORMATION_SCHEMA.PROCESSLIST + WHERE INFO IS NOT NULL AND STATE = 'Waiting for table metadata lock'; +--source include/wait_condition.inc +# +# Expect to see the state 'Waiting for table metadata lock' for the third +# connection because the SELECT collides with the WRITE TABLE LOCK. +--replace_column 1 <ID> 3 <HOST_NAME> 6 <TIME> 9 <TIME_MS> 13 <MEMORY> 14 <MAX_MEMORY> 15 <ROWS> 16 <QUERY_ID> 18 <TID> +SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; +UNLOCK TABLES; +# +connection con2; +echo +# Pull("reap") the result set from the statement executed with "send". +; +reap; + + +echo +# Ensure that SHOW/SELECT processlist can handle extreme long commands +#---------------------------------------------------------------------------- +; +# We do not want to waste runtime, therefore we run the following test based +# on "Lock collision" and not with some "sleep(10)". +connection default; +LOCK TABLE test.t1 WRITE; +# +connection con2; +echo +# Send a long (~20 KB code) statement to the server, but do not wait +# till the result comes back. We will pull this later. +; +let $string= +`SELECT CONCAT('BEGIN-', + REPEAT('This is the representative of a very long statement.',400), + '-END')`; +let $my_statement = +SELECT count(*),'$string' AS "Long string" FROM test.t1; +send; +eval $my_statement; +connection default; +echo +# Poll till INFO is no more NULL and State = 'Waiting for table metadata lock'. +; +let $wait_condition= SELECT COUNT(*) FROM INFORMATION_SCHEMA.PROCESSLIST + WHERE INFO IS NOT NULL AND STATE = 'Waiting for table metadata lock'; +--source include/wait_condition.inc +echo +# Expect result: +# Statement Content of INFO +# SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST Complete statement +# SHOW FULL PROCESSLIST Complete statement +# SHOW PROCESSLIST statement truncated after 100 char +; +--replace_column 1 <ID> 3 <HOST_NAME> 5 <COMMAND> 6 <TIME> 7 <STATE> 9 <TIME_MS> 13 <MEMORY> 14 <MAX_MEMORY> 15 <ROWS> 16 <QUERY_ID> 18 <TID> +SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; +--replace_result Execute Query +--replace_column 1 <ID> 3 <HOST_NAME> 5 <COMMAND> 6 <TIME> 7 <STATE> +SHOW FULL PROCESSLIST; +--replace_result Execute Query +--replace_column 1 <ID> 3 <HOST_NAME> 5 <COMMAND> 6 <TIME> 7 <STATE> +SHOW PROCESSLIST; +UNLOCK TABLES; +connection con2; +echo +# Pull("reap") the result set from the monster statement executed with "send". +; +reap; + + +# Cleanup +connection default; +disconnect con1; +disconnect con2; +DROP USER test_user@'localhost'; +DROP TABLE test.t1; diff --git a/mysql-test/suite/funcs_1/datadict/statistics.inc b/mysql-test/suite/funcs_1/datadict/statistics.inc new file mode 100644 index 00000000..56a291a5 --- /dev/null +++ b/mysql-test/suite/funcs_1/datadict/statistics.inc @@ -0,0 +1,52 @@ +# suite/funcs_1/datadict/statistics.inc +# +# Auxiliary script to be sourced by +# is_statistics_is +# is_statistics_mysql +# is_statistics_<engine> +# +# Purpose: +# Check the content of information_schema.statistics about tables within the +# database '$database'. +# +# Usage: +# The variable $database has to be set before sourcing this script. +# Example: +# let $database = db_data; +# +# 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 + +--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.statistics +$my_where +ORDER BY table_schema, table_name, index_name, seq_in_index, column_name; +--replace_column 10 #CARD# +eval $my_select; + +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +connect (testuser1,localhost,testuser1,,db_datadict); +--replace_column 10 #CARD# +eval $my_select; + +connection default; +disconnect testuser1; +DROP USER testuser1@localhost; +DROP DATABASE db_datadict; diff --git a/mysql-test/suite/funcs_1/datadict/table_constraints.inc b/mysql-test/suite/funcs_1/datadict/table_constraints.inc new file mode 100644 index 00000000..74e4c038 --- /dev/null +++ b/mysql-test/suite/funcs_1/datadict/table_constraints.inc @@ -0,0 +1,43 @@ +# suite/funcs_1/datadict/table_constraints.inc +# +# Auxiliary script to be sourced by +# suite/funcs_1/t/is_table_constraints_mysql.test +# suite/funcs_1/t/is_table_constraints_is.test +# +# The variable +# $table_schema database to be inspected +# has to be set before sourcing this script. +# +# 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. +# + +--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.table_constraints +WHERE table_schema = '$table_schema' +ORDER BY table_schema,table_name,constraint_name; +eval $my_select; + +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +connect (testuser1,localhost,testuser1,,db_datadict); +eval $my_select; + +connection default; +disconnect testuser1; +DROP USER testuser1@localhost; +DROP DATABASE db_datadict; + diff --git a/mysql-test/suite/funcs_1/datadict/tables.inc b/mysql-test/suite/funcs_1/datadict/tables.inc new file mode 100644 index 00000000..ad9abc3f --- /dev/null +++ b/mysql-test/suite/funcs_1/datadict/tables.inc @@ -0,0 +1,55 @@ +# suite/funcs_1/datadict/tables.inc +# +# Auxiliary script to be sourced by +# is_tables_<engine>.test +# +# The variable $engine_type has to be assigned before sourcing ths script. +# +# Author: +# 2008-06-04 mleich Create this script based on older scripts and new code. +# + +--source include/default_charset.inc + +# Just have some tables within different databases. +--disable_warnings +DROP DATABASE IF EXISTS test1; +DROP DATABASE IF EXISTS test2; +--enable_warnings +CREATE DATABASE test1; +CREATE DATABASE test2; + +--replace_result $engine_type <engine_to_be_used> +eval CREATE TABLE test1.t1 (f1 VARCHAR(20)) ENGINE = $engine_type; +--replace_result $engine_type <engine_to_be_used> +eval CREATE TABLE test1.t2 (f1 VARCHAR(20)) ENGINE = $engine_type; +--replace_result $engine_type <engine_to_be_used> +eval CREATE TABLE test2.t1 (f1 VARCHAR(20)) ENGINE = $engine_type; + +--source suite/funcs_1/datadict/tables2.inc +SHOW TABLES FROM test1; +SHOW TABLES FROM test2; + +# Create a low privileged user. +# Note: The database db_datadict is just a "home" for the low privileged user +# and not in the focus of testing. +--error 0,ER_CANNOT_USER +DROP USER testuser1@localhost; +CREATE USER testuser1@localhost; +GRANT SELECT ON test1.* TO testuser1@localhost; + +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +connect (testuser1,localhost,testuser1,,test1); +--source suite/funcs_1/datadict/tables2.inc +SHOW TABLES FROM test1; +# The lowprivileged user testuser1 will get here an error. +--disable_abort_on_error +SHOW TABLES FROM test2; +--enable_abort_on_error + +connection default; +disconnect testuser1; +DROP USER testuser1@localhost; + +DROP DATABASE test1; +DROP DATABASE test2; diff --git a/mysql-test/suite/funcs_1/datadict/tables1.inc b/mysql-test/suite/funcs_1/datadict/tables1.inc new file mode 100644 index 00000000..36a4ee56 --- /dev/null +++ b/mysql-test/suite/funcs_1/datadict/tables1.inc @@ -0,0 +1,36 @@ +# suite/funcs_1/datadict/tables1.inc +# +# Auxiliary script to be sourced by +# is_tables_mysql.test +# is_tables_mysql_embedded.test +# is_tables_is.test +# +# 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. +# + +--disable_warnings +DROP DATABASE IF EXISTS test1; +--enable_warnings +CREATE DATABASE test1; + +--source suite/funcs_1/datadict/tables2.inc + +# Create a low privileged user. +# Note: The database test1 is just a "home" for the low privileged user +# and not in the focus of testing. +--error 0,ER_CANNOT_USER +DROP USER testuser1@localhost; +CREATE USER testuser1@localhost; +GRANT SELECT ON test1.* TO testuser1@localhost; + +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +connect (testuser1,localhost,testuser1,,test1); +--source suite/funcs_1/datadict/tables2.inc + +connection default; +disconnect testuser1; +DROP USER testuser1@localhost; +DROP DATABASE test1; diff --git a/mysql-test/suite/funcs_1/datadict/tables2.inc b/mysql-test/suite/funcs_1/datadict/tables2.inc new file mode 100644 index 00000000..2d19eb8b --- /dev/null +++ b/mysql-test/suite/funcs_1/datadict/tables2.inc @@ -0,0 +1,45 @@ +# suite/funcs_1/datadict/tables2.inc +# +# Auxiliary script to be sourced by suite/funcs_1/datadict/tables1.inc. +# +# 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. +# +################################################################################ + +# 8 TABLE_ROWS +# 9 AVG_ROW_LENGTH +# 10 DATA_LENGTH +# 11 MAX_DATA_LENGTH +# 12 INDEX_LENGTH +# 13 DATA_FREE +# 15 CREATE_TIME +# 16 UPDATE_TIME +# 17 CHECK_TIME +# 20 CREATE_OPTIONS +# 21 TABLE_COMMENT User defined comment +# + InnoDB +# + InnoDB: "InnoDB free: <number_kB> kB" appended +# <number_kB> depends on tablespace history! +# The LEFT/INSTR/IF/LENGTH stuff should remove these +# storage engine specific part. +let $innodb_pattern = 'InnoDB free'; +--vertical_results +# We do not unify the engine name here, because the rowformat is +# specific to the engine. +--replace_result Dynamic DYNAMIC_OR_PAGE Page DYNAMIC_OR_PAGE MyISAM MYISAM_OR_MARIA Aria MYISAM_OR_MARIA +--replace_column 8 "#TBLR#" 9 "#ARL#" 10 "#DL#" 11 "#MDL#" 12 "#IL#" 13 "#DF#" 15 "#CRT#" 16 "#UT#" 17 "#CT#" 20 "#CO#" 21 "#TC#" 22 "#MIL#" +eval +SELECT *, + LEFT( table_comment, + IF(INSTR(table_comment,$innodb_pattern) = 0, + LENGTH(table_comment), + INSTR(table_comment,$innodb_pattern) - 1)) + AS "user_comment", + '-----------------------------------------------------' AS "Separator" +FROM information_schema.tables +$my_where +ORDER BY table_schema,table_name; +--horizontal_results |