diff options
Diffstat (limited to 'mysql-test/suite/funcs_1/datadict')
| -rw-r--r-- | mysql-test/suite/funcs_1/datadict/datadict_load.inc | 17 | ||||
| -rw-r--r-- | mysql-test/suite/funcs_1/datadict/datadict_master.inc | 470 | ||||
| -rw-r--r-- | mysql-test/suite/funcs_1/datadict/datadict_priv.inc | 107 | ||||
| -rwxr-xr-x[-rw-r--r--] | mysql-test/suite/funcs_1/datadict/datadict_tables_error_1044.inc | 0 | ||||
| -rw-r--r-- | mysql-test/suite/funcs_1/datadict/processlist_priv.inc | 434 | ||||
| -rw-r--r-- | mysql-test/suite/funcs_1/datadict/processlist_val.inc | 333 |
6 files changed, 1147 insertions, 214 deletions
diff --git a/mysql-test/suite/funcs_1/datadict/datadict_load.inc b/mysql-test/suite/funcs_1/datadict/datadict_load.inc index 4a9bdc9356d..34792080ace 100644 --- a/mysql-test/suite/funcs_1/datadict/datadict_load.inc +++ b/mysql-test/suite/funcs_1/datadict/datadict_load.inc @@ -53,7 +53,7 @@ let $SERVER_NAME= `SELECT DISTINCT host FROM mysql.user WHERE host NOT In ("loca # load tables # ----------- # -# this was part of the 3 files $<engine>_datadict.test, but it has been moved +# this was part of the 4 files $<engine>_datadict.test, but it has been moved # here to have only one place where all preparation for the test is done. # ################################################################################ @@ -61,11 +61,15 @@ let $SERVER_NAME= `SELECT DISTINCT host FROM mysql.user WHERE host NOT In ("loca 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); +eval SET @ENGINE_NDB = IF( '$engine_type' = 'ndb', 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`; +let $engine_ndb= `SELECT @ENGINE_NDB = 1`; +# Note: The NDB variant with their own tb1 - tb4 tables is not ready for use. +let $engine_ndb= 0; # Decide, if the objects are to be (re)created # @@ -117,6 +121,17 @@ if ($run) USE test1; --source suite/funcs_1/include/myisam_tb2.inc } + + if ($engine_ndb) + { + --source suite/funcs_1/include/ndb_tb1.inc + --source suite/funcs_1/include/ndb_tb2.inc + --source suite/funcs_1/include/ndb_tb3.inc + --source suite/funcs_1/include/ndb_tb4.inc + USE test1; + --source suite/funcs_1/include/ndb_tb2.inc + } + USE test; --source suite/funcs_1/include/sp_tb.inc } diff --git a/mysql-test/suite/funcs_1/datadict/datadict_master.inc b/mysql-test/suite/funcs_1/datadict/datadict_master.inc index 6088a5c5143..ee94f7cb318 100644 --- a/mysql-test/suite/funcs_1/datadict/datadict_master.inc +++ b/mysql-test/suite/funcs_1/datadict/datadict_master.inc @@ -1,5 +1,23 @@ #### suite/funcs_1/datadict/datadict_master.inc # +# Checks of INFORMATION_SCHEMA table properties and content. +# (mostly only the features introduced with MySQL 5.1) +# +# Please set the variable $OTHER_ENGINE_TYPE before sourcing this script. +# $OTHER_ENGINE_TYPE must be +# - <> $engine_type +# - all time available like MyISAM or MEMORY +# +# Last change: +# 2007-08-24 mleich Fixes for the bugs +# #30438 "{memory,myisam,ndb}__datadict" tests fail: +# Use "InnoDB" without checking +# #30418 "datadict" tests (all engines) fail: +# Dependency on the host name for ordering +# #30420 "datadict" tests (all engines) fail: +# Release build has help tables loaded +# + --disable_abort_on_error let $message= . @@ -13,8 +31,9 @@ let $message= . *__datadict.test are started. This can be a result of showing e.g. maximum . values of the number of rows of tables. . -. This .result file has been checked OK with Linux 5.0.23-bk, -. ChangeSet@1.2211, 2006-06-28 10:11:43-07:00. +. This .result file has been checked OK with Linux 5.0.48, +. build tree ChangeSet@1.2477.6.3, 2007-07-30 +. except that the not fixed Bug#30020 causes a difference. .; --source include/show_msg80.inc @@ -29,9 +48,11 @@ let $message= # and otherwise some extra statements needs to be executed as long as the bug is not # fixed: let $have_bug_11589= 1; + let $have_bug_30689= 1; #seems not to work: --vertical_results eval SELECT $have_bug_11589 AS "have_bug_11589"; + eval SELECT $have_bug_30689 AS "have_bug_30689"; #seems not to work: --horizontal_results # As long as @@ -48,6 +69,15 @@ let $message= Bug#11589: mysqltest, --ps-protocol, strange output, float/double/real with zerofill; --source include/show_msg80.inc } + if ($have_bug_30689) + { + let $message= Selects on INFORMATION_SCHEMA.VIEWS present incomplete + content for the column VIEW_DEFINITION in cases where + the view selects(=is based) on an INFORMATION_SCHEMA table. + ---> VIEWS vu and vu1 + Bug#30689 Wrong content in I_S.VIEWS.VIEW_DEFINITION if VIEW is based on I_S; + --source include/show_msg80.inc + } # ################################################################################ @@ -105,6 +135,7 @@ SELECT DISTINCT u, AS Server_Clean FROM db_datadict.vu1; --replace_result $SERVER_NAME <SERVER_NAME> +--sorted_result SELECT * FROM db_datadict.vu order by u; delimiter //; @@ -156,7 +187,8 @@ SELECT * FROM tables # 17 CHECK_TIME --replace_column 9 "#ARL#" 10 "#DL#" 11 "#MDL#" 12 "#IL#" 13 "#DF#" 15 "YYYY-MM-DD hh:mm:ss" 16 "YYYY-MM-DD hh:mm:ss" 17 "YYYY-MM-DD hh:mm:ss" SELECT * FROM tables - WHERE NOT( table_schema = 'information_schema'); +WHERE NOT( table_schema = 'information_schema') + AND NOT (table_schema = 'mysql' AND table_name LIKE 'help_%'); --horizontal_results --enable_ps_protocol @@ -176,9 +208,11 @@ select collation_name, character_set_name into @x,@y select * from routines; select count(*) from routines; -select * from statistics; +select * from statistics +where not (table_schema = 'mysql' and table_name like 'help_%'); select * from views; --replace_result $SERVER_NAME <SERVER_NAME> +--sorted_result select * from user_privileges order by grantee, privilege_type; select * from schema_privileges; select * from table_privileges; @@ -187,7 +221,8 @@ select * from table_constraints; select * from key_column_usage; select count(*) as max_recs from key_column_usage; -select max(cardinality) from statistics; +select max(cardinality) from statistics +where not (table_schema = 'mysql' and table_name like 'help_%'); select concat("View '", table_name, "' is associated with the database '", table_schema, "'.") @@ -199,6 +234,7 @@ select concat("Table or view '", table_name, from tables; --replace_result $SERVER_NAME <SERVER_NAME> +--sorted_result select grantee as "user's having select privilege", substring( grantee, length(SUBSTRING_INDEX(grantee,_utf8'@',1))+2 ) from user_privileges where privilege_type = 'select' @@ -233,47 +269,47 @@ let $dd_part2= LIMIT 1; # check again, but from different database (will fail due to missing database name) use db_datadict; ---error 1146 +--error ER_NO_SUCH_TABLE select * from schemata; ---error 1146 +--error ER_NO_SUCH_TABLE select * from tables; ---error 1146 +--error ER_NO_SUCH_TABLE select s.catalog_name, s.schema_name, s.default_character_set_name, t.table_type, t.engine from schemata s inner join tables t ORDER BY s.catalog_name, s.schema_name, s.default_character_set_name; ---error 1146 +--error ER_NO_SUCH_TABLE select * from columns limit 0, 5; ---error 1146 +--error ER_NO_SUCH_TABLE select * from character_sets limit 0, 5; ---error 1146 +--error ER_NO_SUCH_TABLE select * from collations limit 0, 5; ---error 1146 +--error ER_NO_SUCH_TABLE select * from collation_character_set_applicability limit 0, 5; ---error 1146 +--error ER_NO_SUCH_TABLE select * from routines limit 0, 5; ---error 1146 +--error ER_NO_SUCH_TABLE select * from statistics limit 0, 5; ---error 1146 +--error ER_NO_SUCH_TABLE select * from views limit 0, 5; ---error 1146 +--error ER_NO_SUCH_TABLE select * from user_privileges limit 0, 5; ---error 1146 +--error ER_NO_SUCH_TABLE select * from schema_privileges limit 0, 5; ---error 1146 +--error ER_NO_SUCH_TABLE select * from table_privileges limit 0, 5; ---error 1146 +--error ER_NO_SUCH_TABLE select * from column_privileges limit 0, 5; ---error 1146 +--error ER_NO_SUCH_TABLE select * from table_constraints limit 0, 5; ---error 1146 +--error ER_NO_SUCH_TABLE select * from key_column_usage limit 0, 5; # Reference Manual 22.1.16 - we will add more ...: -# --error 1146 +# --error ER_NO_SUCH_TABLE # select * from parameters; -# --error 1146 +# --error ER_NO_SUCH_TABLE # select * from referential_constraints; -# --error 1146 +# --error ER_NO_SUCH_TABLE # select * from triggers; let $message= will fail due to missing database name; let $dd_part1= SELECT * FROM; @@ -316,7 +352,8 @@ SELECT * FROM information_schema.tables # 17 CHRCK_TIME --replace_column 9 "#ARL#" 10 "#DL#" 11 "#MDL#" 12 "#IL#" 13 "#DF#" 15 "YYYY-MM-DD hh:mm:ss" 16 "YYYY-MM-DD hh:mm:ss" 17 "YYYY-MM-DD hh:mm:ss" SELECT * FROM information_schema.tables - WHERE NOT( table_schema = 'information_schema'); +WHERE NOT( table_schema = 'information_schema') + AND NOT (table_schema = 'mysql' AND table_name LIKE 'help_%'); --horizontal_results --enable_ps_protocol @@ -383,13 +420,15 @@ select * from collation_character_set_applicability order by character_set_name desc, collation_name limit 0, 5; select routine_definition from routines; -select * from statistics group by index_name asc limit 0, 5; +select * from statistics where table_name not like 'help_%' +group by index_name asc limit 0, 5; select concat(table_schema, ', ', table_name, ', ', view_definition) view_info from views; select concat(table_schema, ', ', table_name) "Table_info" from tables ORDER BY 1; --replace_result $SERVER_NAME <SERVER_NAME> +--sorted_result select distinct grantee from user_privileges order by grantee, privilege_type; select * from schema_privileges where table_catalog is null limit 0, 5; select * from table_privileges where grantee like '%r%' limit 0, 5; @@ -402,8 +441,10 @@ select sum(ordinal_position) from key_column_usage; select * from schemata limit 0,5; select * from schemata limit 0,5; --replace_result $SERVER_NAME <SERVER_NAME> +--sorted_result select distinct grantee from user_privileges; --replace_result $SERVER_NAME <SERVER_NAME> +--sorted_result select all grantee from user_privileges order by grantee, privilege_type; select id , character_set_name from collations order by id asc limit 10; @@ -437,20 +478,24 @@ eval SELECT * # check also with a 'simple' user CREATE USER user_3212@localhost; GRANT ALL ON db_datadict.* TO user_3212@localhost; +# OBN: The following line was added following the fix to bug 28181 +# where queries to information_schema will fail if exporting to +# a file without having the FILE attribute +GRANT FILE ON *.* TO user_3212@localhost; + --replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK connect (u3212,localhost,user_3212,,db_datadict); --source suite/funcs_1/include/show_connection.inc # no db given --> db_datadict.schema does not exist ---error 1045 +--error ER_NO_SUCH_TABLE eval SELECT * INTO OUTFILE '../tmp/out.$ENGINE_TYPE.user.file' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM schemata LIMIT 0, 5; -# FIXME 3.2.1.2: why do we get different error numbers with and without OUTFILE ? ---error 1146 +--error ER_NO_SUCH_TABLE eval SELECT * FROM schemata LIMIT 0, 5; @@ -460,8 +505,6 @@ eval SELECT * LINES TERMINATED BY '\n' FROM information_schema.schemata WHERE schema_name LIKE 'db_%'; -# The above will fail with access error as long as -# BUBG#28181 - a regression introduced in 5.0.42 is not fixed eval SELECT * FROM information_schema.schemata @@ -469,14 +512,11 @@ eval SELECT * USE information_schema; -# no db given --> db_datadict.schema does not exist eval SELECT * INTO OUTFILE '../tmp/out.$ENGINE_TYPE.user_2.file' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM schemata LIMIT 0, 5; -# The above will fail with access error as long as -# BUBG#28181 - a regression introduced in 5.0.42 is not fixed eval SELECT * FROM schemata LIMIT 0, 5; @@ -487,8 +527,6 @@ eval SELECT * LINES TERMINATED BY '\n' FROM information_schema.schemata WHERE schema_name LIKE 'db_%'; -# The above will fail with access error as long as -# BUBG#28181 - a regression introduced in 5.0.42 is not fixed eval SELECT * FROM information_schema.schemata @@ -532,43 +570,43 @@ let $message= Testcase 3.2.1.3:; ################################################################################ #FIXME: in this block we had --error 1288 until Mid Sep05, check the change! ---error 1044 +--error ER_DBACCESS_DENIED_ERROR insert into schemata (catalog_name, schema_name, default_character_set_name, sql_path) values ('null', 'db1', 'latin1', 'null'); ---error 1044 +--error ER_DBACCESS_DENIED_ERROR insert into tables (table_schema, table_name)values('db_datadict', 't1'); ---error 1044 +--error ER_DBACCESS_DENIED_ERROR insert into columns (table_name, column_name)values('t3', 'f2'); ---error 1044 +--error ER_DBACCESS_DENIED_ERROR insert into character_sets (character_set_name, default_collate_name, description, maxlen) values('cp1251', 'cp1251_general_ci', 'windows cyrillic', 1); ---error 1044 +--error ER_DBACCESS_DENIED_ERROR insert into collations ( collation_name, character_set_name, id, is_default, is_compiled, sortlen) values ('cp1251_bin', 'cp1251', 50, '', '', 0); ---error 1044 +--error ER_DBACCESS_DENIED_ERROR insert into collation_character_set_applicability (collation_name, character_set_name) values (' big5_chinese_ci', 'big6'); ---error 1044 +--error ER_DBACCESS_DENIED_ERROR insert into routines(routine_name, routine_type ) values ('p2', 'procedure'); ---error 1044 +--error ER_DBACCESS_DENIED_ERROR insert into statistics(table_schema, table_name, index_name) values ('mysql', 'db', 'primary'); ---error 1044 +--error ER_DBACCESS_DENIED_ERROR insert into views(table_schema, table_name) values ('db2', 'v2'); ---error 1044 +--error ER_DBACCESS_DENIED_ERROR insert into user_privileges (privilege_type, is_grantable) values ('select', 'yes'); ---error 1044 +--error ER_DBACCESS_DENIED_ERROR insert into schema_privileges (table_schema, privilege_type) values('db2', 'insert'); ---error 1044 +--error ER_DBACCESS_DENIED_ERROR insert into table_privileges (able_schema, table_name, privilege_type) values('db2', 'v2', 'insert'); ---error 1044 +--error ER_DBACCESS_DENIED_ERROR insert into column_privileges (table_name, column_name, privilege_type) values ('t3', 'f3', 'insert'); ---error 1044 +--error ER_DBACCESS_DENIED_ERROR insert into table_constraints ( constraint_schema, constraint_name, table_schema) values ('primary', 'mysql', 'user'); ---error 1044 +--error ER_DBACCESS_DENIED_ERROR insert into key_column_usage (constraint_schema, constraint_name, table_name) values ('mysql', 'primary', 'db'); @@ -589,7 +627,7 @@ delimiter ;// SELECT table_schema, privilege_type FROM information_schema.schema_privileges WHERE table_schema LIKE 'db%'; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR call db_datadict.sp_4_1_3(); #FIXME: check for the diffs Win ./. Linux @@ -610,7 +648,7 @@ connect (u413,localhost,user_4_1_3,,test); use information_schema; #FIXME: check later the change from 1288 to 1044 (since Mid Sep05) ---error 1044 +--error ER_DBACCESS_DENIED_ERROR insert into table_constraints ( constraint_schema, constraint_name, table_schema) values ('primary', 'mysql', 'user'); @@ -630,38 +668,38 @@ use information_schema; --source suite/funcs_1/include/show_connection.inc #FIXME: check later the change from 1288 to 1044 (since Mid Sep05) in the whole next block ---error 1044 +--error ER_DBACCESS_DENIED_ERROR update schemata set schema_name = 'db5' where default_character_set_name = 'latin1'; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR update tables set table_schema = 'db_datadict1' where table_name = 't1'; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR update columns set table_name = 't4' where column_name = 'f2'; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR update character_sets set character_set_name = 'cp1252' where maxlen = 1; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR update collations set collation_name = 'cp1253_bin' where character_set_name = 'cp1251'; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR update collation_character_set_applicability set collation_name = 'big6_chinese_ci' where character_set_name = 'big6'; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR update routines set routine_name = p2 where routine_body = 'sql'; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR update statistics set table_schema = 'mysql1' where table_name = 'db'; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR update views set table_schema = 'db3' where table_name = 'v1'; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR update user_privileges set privilege_type = 'insert' where is_grantable = 'yes'; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR update schema_privileges set table_schema = 'db2' where privilege_type = 'select'; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR update table_privileges set table_name = 'v3' where privilege_type = 'select'; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR update column_privileges set table_name = 't4' where column_name = 'f3'; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR update table_constraints set constraint_schema = 'primary' where table_schema = 'proc'; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR update key_column_usage set table_name = 'db1' where constraint_name = 'primary'; # update through a procedure @@ -681,7 +719,7 @@ delimiter ;// --replace_column 16 "YYYY-MM-DD hh:mm:ss" 17 "YYYY-MM-DD hh:mm:ss" select * from information_schema.routines; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR call db_datadict.sp_4_1_4(); #FIXME: check for the diffs Win ./. Linux @@ -699,7 +737,7 @@ use information_schema; --source suite/funcs_1/include/show_connection.inc #FIXME: check later the change from 1288 to 1044 (since Mid Sep05) ---error 1044 +--error ER_DBACCESS_DENIED_ERROR update user_privileges set privilege_type = 'insert' where is_grantable = 'yes'; connection default; @@ -723,35 +761,35 @@ let $dd_part2=; --source suite/funcs_1/datadict/datadict_tables_error_1044.inc #FIXME: check later the change from 1288 to 1044 (since Mid Sep05) in the whole next block ---error 1044 +--error ER_DBACCESS_DENIED_ERROR delete from schemata where schema_name = 'mysql'; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR delete from tables where table_name = 'abc'; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR delete from columns; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR delete from character_sets; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR delete from collations; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR delete from collation_character_set_applicability; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR delete from routines; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR delete from statistics; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR delete from views; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR delete from user_privileges; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR delete from schema_privileges; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR delete from table_privileges; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR delete from column_privileges; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR delete from table_constraints; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR delete from key_column_usage; # delete through a procedure @@ -766,7 +804,7 @@ begin end// delimiter ;// ---error 1044 +--error ER_DBACCESS_DENIED_ERROR call db_datadict.sp_4_1_5(); # cleanup @@ -780,7 +818,7 @@ use information_schema; --source suite/funcs_1/include/show_connection.inc #FIXME: check later the change from 1288 to 1044 (since Mid Sep05) ---error 1044 +--error ER_DBACCESS_DENIED_ERROR delete from tables where table_name = 'abc'; disconnect u413; @@ -807,7 +845,7 @@ let $dd_part1= CREATE TABLE; let $dd_part2= ( c1 INT ); --source suite/funcs_1/datadict/datadict_tables_error_1044.inc #FIXME solved - 3.2.1.6: check for better error message - 42S02: Unknown table '<xxx>' in <db> ? ---error 1109 +--error ER_UNKNOWN_TABLE create table t1 (f1 int, f2 int, f3 int); use db_datadict; @@ -818,7 +856,7 @@ let $dd_part1= CREATE TABLE information_schema.; let $dd_part2= ( c1 INT ); --source suite/funcs_1/datadict/datadict_tables_error_1044.inc #FIXME solved - 3.2.1.6: check for better error message - 42S02: Unknown table '<xxx>' in <db> ? ---error 1109 +--error ER_UNKNOWN_TABLE create table information_schema.t1 (f1 int, f2 int, f3 int); # create a table in information_schema as a limited user with sufficient permissions @@ -841,7 +879,7 @@ let $dd_part1= CREATE TABLE; let $dd_part2= ( c1 INT ); --source suite/funcs_1/datadict/datadict_tables_error_1044.inc #FIXME solved - 3.2.1.6: check for better error message - 42S02: Unknown table '<xxx>' in <db> ? ---error 1109 +--error ER_UNKNOWN_TABLE create table t1 (f1 int, f2 int, f3 int); use test; @@ -852,7 +890,7 @@ let $dd_part1= CREATE TABLE information_schema.; let $dd_part2= ( c1 INT ); --source suite/funcs_1/datadict/datadict_tables_error_1044.inc #FIXME solved - 3.2.1.6: check for better error message - 42S02: Unknown table '<xxx>' in <db> ? ---error 1109 +--error ER_UNKNOWN_TABLE create table information_schema.t1 (f1 int, f2 int, f3 int); #cleanup @@ -877,7 +915,7 @@ let $dd_part2= AS SELECT * FROM mysql.time_zone; #FIXME: check change from error 1 to 1044 --source suite/funcs_1/datadict/datadict_tables_error_1044.inc ---error 1109 +--error ER_UNKNOWN_TABLE CREATE VIEW v1 AS SELECT * FROM information_schema.schemata; USE db_datadict; @@ -898,7 +936,7 @@ CREATE USER user_4_1_7@localhost; GRANT ALL ON db_datadict.* TO user_4_1_7@localhost; #FIXME: check that GRANT ON i_s is no longer allowed ---error 1044 +--error ER_DBACCESS_DENIED_ERROR GRANT ALL ON information_schema.* TO user_4_1_7@localhost; FLUSH PRIVILEGES; @@ -913,7 +951,7 @@ let $dd_part1= CREATE VIEW ; let $dd_part2= AS SELECT * FROM db_datadict.v1; #FIXME: check change from error 1 to 1044 --source suite/funcs_1/datadict/datadict_tables_error_1044.inc ---error 1109 +--error ER_UNKNOWN_TABLE create view v1 as select * from table_privileges; use db_datadict; @@ -943,45 +981,45 @@ let $message= Testcase 3.2.1.8:; use information_schema; #FIXME: check later the change from 1288 to 1044 (since Mid Sep05) ---error 1044 +--error ER_DBACCESS_DENIED_ERROR create index i1 on schemata(schema_name); ---error 1044 +--error ER_DBACCESS_DENIED_ERROR create index i2 on tables(table_schema); ---error 1044 +--error ER_DBACCESS_DENIED_ERROR create index i3 on columns(table_name); ---error 1044 +--error ER_DBACCESS_DENIED_ERROR create index i4 on character_sets(character_set_name); ---error 1044 +--error ER_DBACCESS_DENIED_ERROR create index i5 on collations( collation_name); ---error 1044 +--error ER_DBACCESS_DENIED_ERROR create index i6 on collation_character_set_applicability(collation_name); ---error 1044 +--error ER_DBACCESS_DENIED_ERROR create index i7 on routines(routine_name); ---error 1044 +--error ER_DBACCESS_DENIED_ERROR create index i8 on statistics(table_schema); ---error 1044 +--error ER_DBACCESS_DENIED_ERROR create index i9 on views(table_schema); ---error 1044 +--error ER_DBACCESS_DENIED_ERROR create index i10 on user_privileges(privilege_type); ---error 1044 +--error ER_DBACCESS_DENIED_ERROR create index i11 on schema_privileges(table_schema); ---error 1044 +--error ER_DBACCESS_DENIED_ERROR create index i12 on table_privileges(able_schema); ---error 1044 +--error ER_DBACCESS_DENIED_ERROR create index i13 on column_privileges(table_name); ---error 1044 +--error ER_DBACCESS_DENIED_ERROR create index i14 on table_constraints(constraint_schema); ---error 1044 +--error ER_DBACCESS_DENIED_ERROR create index i15 on key_column_usage(constraint_schema); ---error 1044 +--error ER_DBACCESS_DENIED_ERROR create index i16 on triggers(trigger_name); use db_datadict; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR create index i15 on information_schema.key_column_usage(constraint_schema); use information_schema; @@ -999,45 +1037,45 @@ connect (u4, localhost, user_4_1_8, , test); use information_schema; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR create index i1 on schemata(schema_name); ---error 1044 +--error ER_DBACCESS_DENIED_ERROR create index i2 on tables(table_schema); ---error 1044 +--error ER_DBACCESS_DENIED_ERROR create index i3 on columns(table_name); ---error 1044 +--error ER_DBACCESS_DENIED_ERROR create index i4 on character_sets(character_set_name); ---error 1044 +--error ER_DBACCESS_DENIED_ERROR create index i5 on collations( collation_name); ---error 1044 +--error ER_DBACCESS_DENIED_ERROR create index i6 on collation_character_set_applicability(collation_name); ---error 1044 +--error ER_DBACCESS_DENIED_ERROR create index i7 on routines(routine_name); ---error 1044 +--error ER_DBACCESS_DENIED_ERROR create index i8 on statistics(table_schema); ---error 1044 +--error ER_DBACCESS_DENIED_ERROR create index i9 on views(table_schema); ---error 1044 +--error ER_DBACCESS_DENIED_ERROR create index i10 on user_privileges(privilege_type); ---error 1044 +--error ER_DBACCESS_DENIED_ERROR create index i11 on schema_privileges(table_schema); ---error 1044 +--error ER_DBACCESS_DENIED_ERROR create index i12 on table_privileges(able_schema); ---error 1044 +--error ER_DBACCESS_DENIED_ERROR create index i13 on column_privileges(table_name); ---error 1044 +--error ER_DBACCESS_DENIED_ERROR create index i14 on table_constraints(constraint_schema); ---error 1044 +--error ER_DBACCESS_DENIED_ERROR create index i15 on key_column_usage(constraint_schema); ---error 1044 +--error ER_DBACCESS_DENIED_ERROR create index i16 on triggers(trigger_name); use db_datadict; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR create index i15 on information_schema.key_column_usage(constraint_schema); disconnect u4; @@ -1072,37 +1110,37 @@ let $dd_part2= ADD f1 INT; #FIXME: check change from error 1146 to 1044 --source suite/funcs_1/datadict/datadict_tables_error_1044.inc # now more detailed checks ---error 1044 +--error ER_DBACCESS_DENIED_ERROR alter table schemata add f1 int; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR alter table tables drop primary key; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR alter table columns add f1 int; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR alter table character_sets disable keys; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR alter table collations enable keys; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR alter table collation_character_set_applicability add f1 int; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR alter table routines discard tablespace; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR alter table statistics import tablespace; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR alter table views drop column table_name; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR alter table user_privileges drop index privilege_type; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR alter table schema_privileges drop column is_grantable; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR alter table table_privileges order by constraint_type; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR alter table column_privileges rename to aaxyz; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR alter table table_constraints order by schema_name; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR alter table key_column_usage rename to information_schema.aabxyz; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR alter table triggers rename to information_schema.sql_mode; # Alter an information_schema table as a limited user with sufficient permissions @@ -1207,25 +1245,25 @@ connect (u7,localhost,user_4_1_11, ,test); use information_schema; --source suite/funcs_1/include/show_connection.inc ---error 1044 +--error ER_DBACCESS_DENIED_ERROR drop table routines; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR alter table collations enable keys; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR create index i5 on collations( collation_name ); ---error 1109 +--error ER_UNKNOWN_TABLE create view v1 as select * from schemata; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR delete from columns; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR update columns set table_name = 't4' where column_name = 'f2'; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR insert into collations ( collation_name, character_set_name, id, is_default, is_compiled, sortlen) values ('cp1251_bin', 'cp1251', 50, '', '', 0); @@ -1253,13 +1291,13 @@ CREATE DATABASE db_datadict; CREATE USER 'u_6_401011'@'localhost'; #FIXME: check that GRANT ON i_s is no longer allowed ---error 1044 +--error ER_DBACCESS_DENIED_ERROR GRANT ALL ON information_schema.* TO 'u_6_401011'@'localhost'; GRANT ALL ON db_datadict.* TO 'u_6_401011'@'localhost'; FLUSH PRIVILEGES; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR ALTER TABLE information_schema.schemata RENAME db_datadict.schemata; let $message= root: move table to other DB; @@ -1273,7 +1311,7 @@ connect (u_6_401011, localhost, u_6_401011, , db_datadict); USE information_schema; --source suite/funcs_1/include/show_connection.inc ---error 1044 +--error ER_DBACCESS_DENIED_ERROR ALTER TABLE information_schema.schemata RENAME db_datadict.schemata; let $message= user: move table to other DB; @@ -1309,38 +1347,38 @@ let $dd_part2=; #FIXME: check change from error 1288 to 1044 --source suite/funcs_1/datadict/datadict_tables_error_1044.inc # check UPDATE for all ... ---error 1044 +--error ER_DBACCESS_DENIED_ERROR UPDATE information_schema.tables SET table_name = 't_4711'; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR UPDATE information_schema.columns SET table_name = 't_4711'; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR UPDATE information_schema.statistics SET table_name = 't_4711'; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR UPDATE information_schema.views SET table_name = 't_4711'; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR UPDATE information_schema.table_privileges SET table_name = 't_4711'; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR UPDATE information_schema.column_privileges SET table_name = 't_4711'; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR UPDATE information_schema.table_constraints SET table_name = 't_4711'; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR UPDATE information_schema.key_column_usage SET table_name = 't_4711'; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR UPDATE information_schema.schemata SET catalog_name = 't_4711'; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR UPDATE information_schema.character_sets SET description = 't_4711'; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR UPDATE information_schema.collations SET character_set_name = 't_4711'; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR UPDATE information_schema.collation_character_set_applicability SET character_set_name = 't_4711'; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR UPDATE information_schema.routines SET routine_type = 't_4711'; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR UPDATE information_schema.user_privileges SET grantee = 't_4711'; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR UPDATE information_schema.schema_privileges SET grantee = 't_4711'; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR UPDATE information_schema.triggers SET sql_mode = 't_4711'; CREATE USER 'u_6_401012'@'localhost'; @@ -1350,28 +1388,28 @@ connect (u_6_401012, localhost, u_6_401012, , test); use information_schema; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR insert into information_schema.schemata (catalog_name, schema_name, default_character_set_name, sql_path) values (null, information_schema1, utf16, null); #FIXME: check later the change from 1142 to 1044 (since Mid Sep05) ---error 1044 +--error ER_DBACCESS_DENIED_ERROR alter table information_schema.schemata rename db_datadict1.schemata; #FIXME: check later the change from 1146 to 1044 (since Mid Sep05) ---error 1044 +--error ER_DBACCESS_DENIED_ERROR alter table information_schema.tables drop column checksum; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR alter table information_schema.statistics modify packed int; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR alter table information_schema.routines modify created int not null; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR alter table information_schema.key_column_usage drop column ordinal_position; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR alter table information_schema.table_privileges change privilege_type rights_approved varchar(32); ---error 1044 +--error ER_DBACCESS_DENIED_ERROR update columns set table_name = 't4' where column_name = 'f2'; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR delete from information_schema.collations; disconnect u_6_401012; @@ -1452,7 +1490,7 @@ DROP DATABASE IF EXISTS db_datadict; CREATE DATABASE db_datadict; USE db_datadict; -create table res_t_401014(f1 char(10), f2 text(25), f3 int); +create table res_t_401014(f1 char(10), f2 varchar(25), f3 int); create view res_v_401014 as select * from res_t_401014; create procedure sp_6_401014() select 'db_datadict'; create function fn_6_401014() returns int return 0; @@ -1468,7 +1506,7 @@ let $message= show existing objects >before< changing them ...; use db_datadict; alter table res_t_401014 change f1 ff1 int; -alter table res_t_401014 engine = innodb; +eval alter table res_t_401014 engine = $OTHER_ENGINE_TYPE; alter table res_t_401014 change f3 f3_new bigint; alter view res_v_401014 as select ff1 from res_t_401014; alter procedure sp_6_401014 sql security invoker; @@ -1575,7 +1613,7 @@ connection default; --source suite/funcs_1/include/show_connection.inc #FIXME: check that GRANT ON i_s is no longer allowed ---error 1044 +--error ER_DBACCESS_DENIED_ERROR grant usage on information_schema.* to 'u_6_401016'@'localhost'; FLUSH PRIVILEGES; @@ -1612,7 +1650,7 @@ let $message= Testcase 3.2.1.17:; CREATE USER 'u_6_401017'@'localhost'; #FIXME: check that GRANT ON i_s is no longer allowed ---error 1044 +--error ER_DBACCESS_DENIED_ERROR grant select on information_schema.* to u_6_401017@localhost; FLUSH PRIVILEGES; @@ -1658,7 +1696,7 @@ let $message= Testcase 3.2.1.18:; CREATE USER 'u_6_401018'@'localhost'; #FIXME: check GRANT on IS ---error 1044 +--error ER_DBACCESS_DENIED_ERROR GRANT CREATE VIEW ON information_schema.* TO 'u_6_401018'@'localhost'; GRANT ALL ON db_datadict.* TO 'u_6_401018'@'localhost'; @@ -1699,52 +1737,52 @@ let $message= Testcase 3.2.1.19:; CREATE USER 'u_6_401019'@'localhost'; #FIXME: check GRANT on IS ---error 1044 +--error ER_DBACCESS_DENIED_ERROR grant alter on information_schema.* to 'u_6_401019'@'localhost'; #FIXME: check GRANT on IS ---error 1044 +--error ER_DBACCESS_DENIED_ERROR grant alter routine on information_schema.* to 'u_6_401019'@'localhost'; #FIXME: check GRANT on IS ---error 1044 +--error ER_DBACCESS_DENIED_ERROR grant create on information_schema.* to 'u_6_401019'@'localhost'; #FIXME: check GRANT on IS ---error 1044 +--error ER_DBACCESS_DENIED_ERROR grant create routine on information_schema.* to 'u_6_401019'@'localhost'; #FIXME: check GRANT on IS ---error 1044 +--error ER_DBACCESS_DENIED_ERROR grant create temporary tables on information_schema.* to 'u_6_401019'@'localhost'; #FIXME: check GRANT on IS ---error 1044 +--error ER_DBACCESS_DENIED_ERROR grant delete on information_schema.* to 'u_6_401019'@'localhost'; #FIXME: check GRANT on IS ---error 1044 +--error ER_DBACCESS_DENIED_ERROR grant drop on information_schema.* to 'u_6_401019'@'localhost'; #FIXME: check GRANT on IS ---error 1044 +--error ER_DBACCESS_DENIED_ERROR grant execute on information_schema.* to 'u_6_401019'@'localhost'; #FIXME: check GRANT on IS ---error 1044 +--error ER_DBACCESS_DENIED_ERROR grant index on information_schema.* to 'u_6_401019'@'localhost'; #FIXME: check GRANT on IS ---error 1044 +--error ER_DBACCESS_DENIED_ERROR grant insert on information_schema.* to 'u_6_401019'@'localhost'; #FIXME: check GRANT on IS ---error 1044 +--error ER_DBACCESS_DENIED_ERROR grant lock tables on information_schema.* to 'u_6_401019'@'localhost'; #FIXME: check GRANT on IS ---error 1044 +--error ER_DBACCESS_DENIED_ERROR grant update on information_schema.* to 'u_6_401019'@'localhost'; SELECT * FROM information_schema.table_privileges @@ -1773,18 +1811,18 @@ connect (u_6_401020, localhost, u_6_401020, , test); USE information_schema; SELECT * FROM schemata; ---error 1109 +--error ER_UNKNOWN_TABLE CREATE TABLE tb_not_allowed ( col TEXT ); #FIXME 3.2.1.20: bad message: ERROR 42S02: Unknown table 'tb_not_allowed' in information_schema #FIXME 3.2.1.20: better: ERROR 42000: Access denied for user 'u_6_401020'@'localhost' to database 'information_schema' ---error 1109 +--error ER_UNKNOWN_TABLE create view res_v1 as select * from information_schema.schemata; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR alter table schemata modify catalog_name varchar(255); ---error 1044 +--error ER_DBACCESS_DENIED_ERROR update schemata set catalog_name = 'abc' where schema_name = 'information_schema'; @@ -1797,7 +1835,7 @@ CREATE PROCEDURE sp_3_2_1_20() END// delimiter ;// ---error 1044 +--error ER_DBACCESS_DENIED_ERROR DELETE FROM schemata WHERE schema_name = 'information_schema'; disconnect u_6_401020; @@ -1928,6 +1966,11 @@ let $message= Testcase 3.2.4.2:; # Testcase 3.2.4.2: Ensure that the table shows the relevant information on # every collation/character set combination for which the # current user or PUBLIC have the USAGE privilege. +# Note(2007-08-24 mleich): +# The amount of collations/character sets grows with new +# MySQL releases and is a reason why this test might +# fail with differences. Please check the new entries +# and update with expected results afterwards. ################################################################################ SELECT * FROM collation_character_set_applicability; @@ -3155,7 +3198,7 @@ SELECT * FROM information_schema.tables # 17 CHECK_TIME --replace_column 9 "#ARL#" 10 "#DL#" 11 "#MDL#" 12 "#IL#" 13 "#DF#" 15 "YYYY-MM-DD hh:mm:ss" 16 "YYYY-MM-DD hh:mm:ss" 17 "YYYY-MM-DD hh:mm:ss" SELECT * FROM information_schema.tables - WHERE NOT( table_schema = 'information_schema'); + WHERE NOT( table_schema = 'information_schema') AND NOT (table_schema = 'mysql' AND table_name LIKE 'help_%'); --enable_ps_protocol # clean-up @@ -3355,7 +3398,8 @@ SELECT * FROM information_schema.statistics; connection default; --source suite/funcs_1/include/show_connection.inc REVOKE SELECT ON db_datadict.tb_6_401402_1 FROM 'user_1'@'localhost'; -SELECT * FROM information_schema.statistics; +SELECT * FROM information_schema.statistics +WHERE NOT (table_schema = 'mysql' AND table_name LIKE 'help_%'); # nothing visible for user_1 connection user_14_1; @@ -3657,14 +3701,14 @@ eval $cmd3; --replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK connect (user_16_2, localhost, user_2, , db_datadict); eval $cmd1; ---error 1142 +--error ER_TABLEACCESS_DENIED_ERROR eval $cmd2; eval $cmd3; --replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK connect (user_16_3, localhost, user_3, , test); eval $cmd1; ---error 1142 +--error ER_TABLEACCESS_DENIED_ERROR eval $cmd2; eval $cmd3; @@ -3682,22 +3726,22 @@ eval $cmd3; connection user_16_1; --source suite/funcs_1/include/show_connection.inc eval $cmd1; ---error 1142 +--error ER_TABLEACCESS_DENIED_ERROR eval $cmd2; eval $cmd3; --source suite/funcs_1/include/show_connection.inc # checks entered before bug #12269 was reported # OK, user_1 has no privs here ---error 1142 +--error ER_TABLEACCESS_DENIED_ERROR CREATE TABLE db_datadict.tb_55 ( c1 TEXT ); --source suite/funcs_1/include/show_connection.inc eval $cmd1; ---error 1142 +--error ER_TABLEACCESS_DENIED_ERROR eval $cmd2; eval $cmd3; # OK, user_1 has no privs here ---error 1142 +--error ER_TABLEACCESS_DENIED_ERROR CREATE TABLE db_datadict.tb_66 ( c1 TEXT ); let $message= add ALL on db_datadict.* (and select on mysql.user) to user_1; @@ -3720,7 +3764,7 @@ eval $cmd2; eval $cmd3; # OK, user_1 has no privs here ---error 1142 +--error ER_TABLEACCESS_DENIED_ERROR CREATE TABLE db_datadict.tb_56 ( c1 TEXT ); # using 'USE' lets the server read the privileges new, so now the CREATE works @@ -3746,13 +3790,13 @@ eval $cmd3; connection user_16_1; --source suite/funcs_1/include/show_connection.inc eval $cmd1; ---error 1142 +--error ER_TABLEACCESS_DENIED_ERROR eval $cmd2; eval $cmd3; # WORKS, as the existing old privileges are used! CREATE TABLE db_datadict.tb_58 ( c1 TEXT ); # existing privileges are "read" new when USE is called, user has no priviliges ---error 1044 +--error ER_DBACCESS_DENIED_ERROR USE db_datadict; #FIXME 3.2.16: check that it is correct that this now 'works': --error 1142 CREATE TABLE db_datadict.tb_59 ( c1 TEXT ); @@ -3864,7 +3908,7 @@ let $is_table= parameters; # and don't forget to add the test description to QATestPlanV50func let $message= checking a table that will be implemented later; --source include/show_msg.inc ---error 1109 +--error ER_UNKNOWN_TABLE eval DESC $is_table; #--source suite/funcs_1/datadict/datadict_show_table_design.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 00000000000..36143e1526e --- /dev/null +++ b/mysql-test/suite/funcs_1/datadict/datadict_priv.inc @@ -0,0 +1,107 @@ +############## 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; +# +# 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 * FROM $table WITH CHECK OPTION; + +eval CREATE VIEW test.v_$table ($columns) AS SELECT * FROM $table; + +--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_UNKNOWN_TABLE +eval RENAME TABLE $table TO new_$table; + +--error ER_DBACCESS_DENIED_ERROR +eval RENAME TABLE $table TO files; + +--error ER_UNKNOWN_TABLE +eval CREATE TABLE new_$table AS SELECT * FROM $table; + +#---------------------------------------------------------------------- +# database access + +--error ER_DBACCESS_DENIED_ERROR +DROP DATABASE information_schema; + +--error ER_DBACCESS_DENIED_ERROR +RENAME DATABASE information_schema TO info_schema; diff --git a/mysql-test/suite/funcs_1/datadict/datadict_tables_error_1044.inc b/mysql-test/suite/funcs_1/datadict/datadict_tables_error_1044.inc index a8876ee7db6..a8876ee7db6 100644..100755 --- a/mysql-test/suite/funcs_1/datadict/datadict_tables_error_1044.inc +++ b/mysql-test/suite/funcs_1/datadict/datadict_tables_error_1044.inc 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 00000000000..337ddf5df60 --- /dev/null +++ b/mysql-test/suite/funcs_1/datadict/processlist_priv.inc @@ -0,0 +1,434 @@ +############ 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: # +# 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. # +# That means we cannot get NDB or InnoDB instead. # +# # +# Creation: # +# 2007-08 hhunger Implement this test as part of # +# WL#3982 Test information_schema.processlist # +# # +# Last update: # +# 2007-08-14 mleich Corrections # +# # +######################################################################## + +# 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; +# +# 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 #################################################################################### + +--echo #################################################################################### +--echo 1.1 Create two user +--echo #################################################################################### +# access to info tables as normal user +--disable_abort_on_error +DROP USER ddicttestuser1@'localhost'; +DROP USER ddicttestuser2@'localhost'; +--enable_abort_on_error +CREATE USER ddicttestuser1@'localhost'; +CREATE USER 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; +eval SHOW CREATE TABLE $table; +--replace_column 6 TIME +eval SHOW $table; +--replace_column 6 TIME +eval SELECT * FROM $table $select_where ORDER BY id; +--replace_column 6 TIME +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 #################################################################################### +eval SHOW CREATE TABLE $table; +--replace_column 6 TIME +eval SHOW $table; +--replace_column 6 TIME +eval SELECT * FROM $table $select_where ORDER BY id; +--replace_column 6 TIME +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_column 6 TIME +SHOW processlist; +--replace_column 6 TIME +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_column 6 TIME +SHOW processlist; +--replace_column 6 TIME +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; +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_column 6 TIME +SHOW processlist; +--replace_column 6 TIME +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' IDENTIFIED BY 'ddictpass'; +--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_column 6 TIME +SHOW processlist; +--replace_column 6 TIME +SELECT * FROM information_schema.processlist; +--real_sleep 0.3 + + +--echo #################################################################################### +--echo 7 Revoke PROCESS privilege from anonymous user + disconnect ddicttestuser1 +--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_column 6 TIME +SHOW processlist; +} +--replace_column 6 TIME +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_column 6 TIME +SHOW processlist; +--replace_column 6 TIME +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_column 6 TIME +SHOW processlist; +--replace_column 6 TIME +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_column 6 TIME +SHOW processlist; +--replace_column 6 TIME +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_column 6 TIME +SHOW processlist; +--replace_column 6 TIME +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_column 6 TIME +SHOW processlist; +--replace_column 6 TIME +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_column 6 TIME +SHOW processlist; +--replace_column 6 TIME +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 00000000000..da2c2950095 --- /dev/null +++ b/mysql-test/suite/funcs_1/datadict/processlist_val.inc @@ -0,0 +1,333 @@ +########### 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. # +# Examples: # +# - change the default database # +# - send some time no SQL command to the server # +# - send a long running query # +# # +# Note(mleich): # +# 1. Please inform me if this test fails because of timing problems. # +# I tried to avoid instabilities but the values within the column # +# TIME are very sensible to fluctuations of the machine load. # +# I had to unify some TIME values with "--replace_result" in cases # +# where they are too unstable. # +# 2. Storage engine variants of this test do not make sense. # +# - I_S tables use the MEMORY storage engine whenever possible. # +# - There are some I_S table which need column data types which # +# are not supported by MEMORY. Example: LONGTEXT/BLOB # +# MyISAM will be used for such tables. # +# The column PROCESSLIST.INFO is of data type LONGTEXT # +# ----> MyISAM # +# - There is no impact of the GLOBAL(server) or SESSION default # +# storage engine setting on the engine used for I_S tables. # +# That means we cannot get NDB or InnoDB instead. # +# 3. The SHOW (FULL) PROCESSLIST command are for comparison. # +# The main test target is INFORMATION_SCHEMA.PROCESSLIST ! # +# # +# Creation: # +# 2007-08-09 mleich Implement this test as part of # +# WL#3982 Test information_schema.processlist # +# # +######################################################################## + +# Basic preparations +--disable_abort_on_error +DROP USER ddicttestuser1@'localhost'; +--enable_abort_on_error +CREATE USER ddicttestuser1@'localhost'; +GRANT ALL ON *.* TO ddicttestuser1@'localhost'; +REVOKE PROCESS ON *.* FROM ddicttestuser1@'localhost'; +SET PASSWORD FOR ddicttestuser1@'localhost' = PASSWORD('ddictpass'); +--disable_warnings +DROP TABLE IF EXISTS test.t1; +--enable_warnings +CREATE TABLE test.t1 (f1 BIGINT); + + +# Show the definition of the PROCESSLIST table +#-------------------------------------------------------------------------- +SHOW CREATE TABLE INFORMATION_SCHEMA.PROCESSLIST; + + +# Ensure that the values follow the changing default database and statement +#-------------------------------------------------------------------------- +# - We have now exact one connection. -> One record +SELECT COUNT(*) FROM INFORMATION_SCHEMA.PROCESSLIST; +# - Other expected values +# - USER = 'root' +# - HOST = 'localhost' +# - DB = 'test' +# - Command = 'Query' +# - TIME = 0, I hope the testing machines are all time fast enough +# - State IS NULL +# - INFO must contain the corresponding SHOW/SELECT PROCESSLIST +USE test; +--replace_column 1 <ID> +SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; +--replace_column 1 <ID> +SHOW FULL PROCESSLIST; +# +# Expect to see now DB = 'information_schema' +USE information_schema; +--replace_column 1 <ID> 6 <TIME> +SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; +--replace_column 1 <ID> 6 <TIME> +SHOW FULL PROCESSLIST; +# +# Expect to see now INFO = 'SELECT INFO FROM INFORMATION_SCHEMA.PROCESSLIST;' +SELECT INFO FROM INFORMATION_SCHEMA.PROCESSLIST; + + +# Ensure that the values for an additional connection are correct +#-------------------------------------------------------------------------- +SELECT ID INTO @my_proclist_id FROM INFORMATION_SCHEMA.PROCESSLIST; +--echo +--echo ----- establish connection ddicttestuser1 (user = ddicttestuser1) ----- +connect (ddicttestuser1,localhost,ddicttestuser1,ddictpass,information_schema); +# +--echo +--echo ----- switch to connection default (user = root) ----- +connection default; +# - We have now a second connection. +# - important values in second connection +# - USER = ddicttestuser1 +# - HOST = 'localhost' +# - DB = 'information_schema' +# - Command = 'Sleep' +# - TIME = 0, I hope the testing machines are all time fast enough +# - State IS NULL +# - INFO must be empty +--replace_column 1 <ID> +SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; +# Change the SQL command (affects content of INFO) +--replace_column 1 <ID> +SHOW FULL PROCESSLIST; +# +--echo # Sleep some time +# The value of TIME must increase after some sleeps. +# So poll till TIME changes. +let $run= 10; +while ($run) +{ + dec $run; + if (`SELECT COUNT(*) FROM INFORMATION_SCHEMA.PROCESSLIST + WHERE ID = @my_proclist_id + 1 AND TIME > 0`) + { + let $run= 0; + } + --real_sleep 0.2 +} +# Either we have now reached TIME = 1 or we fail with unexpected result. +# Expect to see now TIME = 1 +--replace_column 1 <ID> +SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; +--replace_column 1 <ID> +SHOW FULL PROCESSLIST; +# The second connection must have an ID = my ID + 1; +SELECT ID = @my_proclist_id + 1 FROM INFORMATION_SCHEMA.PROCESSLIST +WHERE USER = 'ddicttestuser1'; + + +# Ensure that the user ddicttestuser1 sees only connections with his username +# because he has not the PROCESS privilege. +#---------------------------------------------------------------------------- +--echo +--echo ----- switch to connection ddicttestuser1 (user = ddicttestuser1) ----- +connection ddicttestuser1; +--replace_column 1 <ID> 6 <TIME> +SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; +--replace_column 1 <ID> 6 <TIME> +SHOW FULL PROCESSLIST; + + +# Ensure that the user ddicttestuser1 sees all connections with his username. +#---------------------------------------------------------------------------- +--echo +--echo ----- establish connection con2 (user = ddicttestuser1) ------ +connect (con2,localhost,ddicttestuser1,ddictpass,information_schema); +--replace_column 1 <ID> 6 <TIME> +SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; +--replace_column 1 <ID> 6 <TIME> +SHOW FULL PROCESSLIST; +# +--echo +--echo ----- switch to connection default (user = root) ----- +connection default; +--echo ----- close connection con2 ----- +disconnect con2; + + +# Ensure we see correct values if a connection is during work +#---------------------------------------------------------------------------- +--echo +--echo ----- switch to connection ddicttestuser1 (user = ddicttestuser1) ----- +connection ddicttestuser1; +# "Organise" a long running command to be observed by the root user +--echo +--echo +--echo # Send a long enough running statement to the server, but do not +--echo # wait till the result comes back. We will pull this later. +send SELECT sleep(2.5),'Command time'; +# +--echo +--echo ----- switch to connection default (user = root) ----- +connection default; +# Sleep a bit so that we can be nearly sure that we see the SELECT of ddicttestuser1. +# Expect to see within the processlist the other connection just during statement +# execution. +# - USER = ddicttestuser1 +# - HOST = 'localhost' +# - DB = 'information_schema' +# - Command = 'Query' +# - TIME = 1, Attention: check with TIME = 0 is not stable +# - State IS NULL +# - INFO = "SELECT sleep(2.5),'Command time'" +--echo # Sleep some time +# The command must be at some time in work by the server. +# So poll till INFO is no more NULL and TIME > 0. +let $run= 10; +while ($run) +{ + dec $run; + if (`SELECT COUNT(*) FROM INFORMATION_SCHEMA.PROCESSLIST + WHERE INFO IS NOT NULL AND TIME > 0`) + { + let $run= 0; + } + --real_sleep 0.2 +} +# Expect to see TIME = 1 +--replace_column 1 <ID> +SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; +--replace_column 1 <ID> +SHOW FULL PROCESSLIST; +# +--echo # Sleep some time +# The value of TIME must increase after some sleeps therefore +# poll till TIME changes +let $run= 10; +while ($run) +{ + dec $run; + if (`SELECT COUNT(*) FROM INFORMATION_SCHEMA.PROCESSLIST + WHERE ID = @my_proclist_id + 1 AND TIME > 1`) + { + let $run= 0; + } + --real_sleep 0.2 +} +# Either we have now reached TIME = 2 or we fail with unexpected result. +--replace_column 1 <ID> +SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; +--replace_column 1 <ID> +SHOW FULL PROCESSLIST; +# +--echo +--echo ----- switch to connection ddicttestuser1 (user = ddicttestuser1) ----- +connection ddicttestuser1; +--echo # Pull("reap") the result set from the statement executed with "send". +reap; + + +# Ensure that SHOW/SELECT processlist can handle extreme long commands +#---------------------------------------------------------------------------- +--echo +--echo +--echo # Send a long (21 KB code and runtime = 2 seconds) statement to the server, +--echo # but do not wait till the result comes back. We will pull this later. +# Please do not change the next statement. +# The annoying long line is intended. Many short lines would be a different test. +send SELECT sleep(2),'BEGIN this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.this is the representative of a very long statement.END' AS "my_monster_statement"; +# +--echo +--echo ----- switch to connection default (user = root) ----- +connection default; +--echo # Sleep some time +# The command must be at some time in work by the server. +# So poll till INFO is no more NULL. +let $run= 10; +while ($run) +{ + dec $run; + if (`SELECT COUNT(*) FROM INFORMATION_SCHEMA.PROCESSLIST + WHERE INFO IS NOT NULL`) + { + let $run= 0; + } + --real_sleep 0.2 +} +# Expect to see that SELECT/SHOW PROCESSLIST can handle my statement monster. +--replace_column 1 <ID> 6 <TIME> +SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; +--replace_column 1 <ID> 6 <TIME> +SHOW FULL PROCESSLIST; +# SHOW PROCESSLIST truncates INFO after 100 characters. +--replace_column 1 <ID> 6 <TIME> +SHOW PROCESSLIST; +--echo +--echo ----- switch to connection ddicttestuser1 (user = ddicttestuser1) ----- +connection ddicttestuser1; +--echo # Pull("reap") the result set from the monster statement executed with "send". +reap; + + +# Ensure that we see that a connection "hangs" when colliding with a +# WRITE TABLE LOCK +#---------------------------------------------------------------------------- +--echo +--echo ----- switch to connection default (user = root) ----- +connection default; +LOCK TABLE test.t1 WRITE; +# +--echo +--echo ----- switch to connection ddicttestuser1 (user = ddicttestuser1) ----- +connection ddicttestuser1; +--echo # Send a statement to the server, but do not wait till the result +--echo # comes back. We will pull this later. +send SELECT COUNT(*) FROM test.t1; +# +--echo +--echo ----- switch to connection default (user = root) ----- +connection default; +--echo # Sleep some time +# The command must be at some time in work by the server. +# So poll till INFO is no more NULL. +let $run= 10; +while ($run) +{ + dec $run; + if (`SELECT COUNT(*) FROM INFORMATION_SCHEMA.PROCESSLIST + WHERE INFO IS NOT NULL`) + { + let $run= 0; + } + --real_sleep 0.2 +} +# Expect to see the state 'Locked' for the second connection because the SELECT +# collides with the WRITE TABLE LOCK. +--replace_column 1 <ID> 6 <TIME> +SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; +--replace_column 1 <ID> 6 <TIME> +SHOW FULL PROCESSLIST; +UNLOCK TABLES; +# +--echo +--echo ----- switch to connection ddicttestuser1 (user = ddicttestuser1) ----- +connection ddicttestuser1; +--echo # Pull("reap") the result set from the statement executed with "send". +reap; + + +# Cleanup +--echo +--echo ----- switch to connection default (user = root) ----- +connection default; +--echo +--echo ----- close connection ddicttestuser1 ----- +disconnect ddicttestuser1; +DROP USER ddicttestuser1@'localhost'; |