diff options
17 files changed, 73 insertions, 938 deletions
diff --git a/mysql-test/suite/funcs_1/r/innodb_storedproc_08.result b/mysql-test/suite/funcs_1/r/innodb_storedproc_08.result index 7bffd77d9c6..2e504af6ed4 100644 --- a/mysql-test/suite/funcs_1/r/innodb_storedproc_08.result +++ b/mysql-test/suite/funcs_1/r/innodb_storedproc_08.result @@ -103,7 +103,7 @@ END// ... now check what is stored: ----------------------------- -SELECT * FROM information_schema.routines; +SELECT * FROM information_schema.routines where routine_schema = 'db_storedproc'; SPECIFIC_NAME fn_1 ROUTINE_CATALOG NULL ROUTINE_SCHEMA db_storedproc @@ -208,75 +208,6 @@ DEFINER root@localhost CHARACTER_SET_CLIENT latin1 COLLATION_CONNECTION latin1_swedish_ci DATABASE_COLLATION latin1_swedish_ci -SPECIFIC_NAME add_suppression -ROUTINE_CATALOG NULL -ROUTINE_SCHEMA mtr -ROUTINE_NAME add_suppression -ROUTINE_TYPE PROCEDURE -DTD_IDENTIFIER NULL -ROUTINE_BODY SQL -ROUTINE_DEFINITION BEGIN INSERT INTO test_suppressions (pattern) VALUES (pattern); END -EXTERNAL_NAME NULL -EXTERNAL_LANGUAGE NULL -PARAMETER_STYLE SQL -IS_DETERMINISTIC NO -SQL_DATA_ACCESS CONTAINS SQL -SQL_PATH NULL -SECURITY_TYPE DEFINER -CREATED <modified> -LAST_ALTERED <created> -SQL_MODE -ROUTINE_COMMENT -DEFINER root@localhost -CHARACTER_SET_CLIENT latin1 -COLLATION_CONNECTION latin1_swedish_ci -DATABASE_COLLATION latin1_swedish_ci -SPECIFIC_NAME check_testcase -ROUTINE_CATALOG NULL -ROUTINE_SCHEMA mtr -ROUTINE_NAME check_testcase -ROUTINE_TYPE PROCEDURE -DTD_IDENTIFIER NULL -ROUTINE_BODY SQL -ROUTINE_DEFINITION BEGIN SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE variable_name != 'timestamp' ORDER BY VARIABLE_NAME; SELECT * FROM INFORMATION_SCHEMA.SCHEMATA; SELECT table_name AS tables_in_test FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='test'; SELECT CONCAT(table_schema, '.', table_name) AS tables_in_mysql FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='mysql' AND table_name != 'ndb_apply_status' ORDER BY tables_in_mysql; SELECT CONCAT(table_schema, '.', table_name) AS columns_in_mysql, column_name, ordinal_position, column_default, is_nullable, data_type, character_maximum_length, character_octet_length, numeric_precision, numeric_scale, character_set_name, collation_name, column_type, column_key, extra, column_comment FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema='mysql' AND table_name != 'ndb_apply_status' ORDER BY columns_in_mysql; checksum table mysql.columns_priv, mysql.db, mysql.func, mysql.help_category, mysql.help_keyword, mysql.help_relation, mysql.host, mysql.proc, mysql.procs_priv, mysql.tables_priv, mysql.time_zone, mysql.time_zone_leap_second, mysql.time_zone_name, mysql.time_zone_transition, mysql.time_zone_transition_type, mysql.user; END -EXTERNAL_NAME NULL -EXTERNAL_LANGUAGE NULL -PARAMETER_STYLE SQL -IS_DETERMINISTIC NO -SQL_DATA_ACCESS CONTAINS SQL -SQL_PATH NULL -SECURITY_TYPE DEFINER -CREATED <modified> -LAST_ALTERED <created> -SQL_MODE -ROUTINE_COMMENT -DEFINER root@localhost -CHARACTER_SET_CLIENT latin1 -COLLATION_CONNECTION latin1_swedish_ci -DATABASE_COLLATION latin1_swedish_ci -SPECIFIC_NAME check_warnings -ROUTINE_CATALOG NULL -ROUTINE_SCHEMA mtr -ROUTINE_NAME check_warnings -ROUTINE_TYPE PROCEDURE -DTD_IDENTIFIER NULL -ROUTINE_BODY SQL -ROUTINE_DEFINITION BEGIN DECLARE `pos` bigint unsigned; SET SQL_LOG_BIN=0; UPDATE error_log el, global_suppressions gs SET suspicious=0 WHERE el.suspicious=1 AND el.line REGEXP gs.pattern; UPDATE error_log el, test_suppressions ts SET suspicious=0 WHERE el.suspicious=1 AND el.line REGEXP ts.pattern; SELECT COUNT(*) INTO @num_warnings FROM error_log WHERE suspicious=1; IF @num_warnings > 0 THEN SELECT file_name, line FROM error_log WHERE suspicious=1; SELECT 2 INTO result; ELSE SELECT 0 INTO RESULT; END IF; TRUNCATE test_suppressions; DROP TABLE error_log; END -EXTERNAL_NAME NULL -EXTERNAL_LANGUAGE NULL -PARAMETER_STYLE SQL -IS_DETERMINISTIC NO -SQL_DATA_ACCESS CONTAINS SQL -SQL_PATH NULL -SECURITY_TYPE DEFINER -CREATED <modified> -LAST_ALTERED <created> -SQL_MODE -ROUTINE_COMMENT -DEFINER root@localhost -CHARACTER_SET_CLIENT latin1 -COLLATION_CONNECTION latin1_swedish_ci -DATABASE_COLLATION latin1_swedish_ci SHOW CREATE FUNCTION fn_1; Function fn_1 sql_mode @@ -390,7 +321,7 @@ ALTER FUNCTION fn_2 MODIFIES SQL DATA; ... now check what is stored: ----------------------------- -SELECT * FROM information_schema.routines; +SELECT * FROM information_schema.routines where routine_schema = 'db_storedproc'; SPECIFIC_NAME fn_1 ROUTINE_CATALOG NULL ROUTINE_SCHEMA db_storedproc @@ -495,75 +426,6 @@ DEFINER root@localhost CHARACTER_SET_CLIENT latin1 COLLATION_CONNECTION latin1_swedish_ci DATABASE_COLLATION latin1_swedish_ci -SPECIFIC_NAME add_suppression -ROUTINE_CATALOG NULL -ROUTINE_SCHEMA mtr -ROUTINE_NAME add_suppression -ROUTINE_TYPE PROCEDURE -DTD_IDENTIFIER NULL -ROUTINE_BODY SQL -ROUTINE_DEFINITION BEGIN INSERT INTO test_suppressions (pattern) VALUES (pattern); END -EXTERNAL_NAME NULL -EXTERNAL_LANGUAGE NULL -PARAMETER_STYLE SQL -IS_DETERMINISTIC NO -SQL_DATA_ACCESS CONTAINS SQL -SQL_PATH NULL -SECURITY_TYPE DEFINER -CREATED <modified> -LAST_ALTERED <created> -SQL_MODE -ROUTINE_COMMENT -DEFINER root@localhost -CHARACTER_SET_CLIENT latin1 -COLLATION_CONNECTION latin1_swedish_ci -DATABASE_COLLATION latin1_swedish_ci -SPECIFIC_NAME check_testcase -ROUTINE_CATALOG NULL -ROUTINE_SCHEMA mtr -ROUTINE_NAME check_testcase -ROUTINE_TYPE PROCEDURE -DTD_IDENTIFIER NULL -ROUTINE_BODY SQL -ROUTINE_DEFINITION BEGIN SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE variable_name != 'timestamp' ORDER BY VARIABLE_NAME; SELECT * FROM INFORMATION_SCHEMA.SCHEMATA; SELECT table_name AS tables_in_test FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='test'; SELECT CONCAT(table_schema, '.', table_name) AS tables_in_mysql FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='mysql' AND table_name != 'ndb_apply_status' ORDER BY tables_in_mysql; SELECT CONCAT(table_schema, '.', table_name) AS columns_in_mysql, column_name, ordinal_position, column_default, is_nullable, data_type, character_maximum_length, character_octet_length, numeric_precision, numeric_scale, character_set_name, collation_name, column_type, column_key, extra, column_comment FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema='mysql' AND table_name != 'ndb_apply_status' ORDER BY columns_in_mysql; checksum table mysql.columns_priv, mysql.db, mysql.func, mysql.help_category, mysql.help_keyword, mysql.help_relation, mysql.host, mysql.proc, mysql.procs_priv, mysql.tables_priv, mysql.time_zone, mysql.time_zone_leap_second, mysql.time_zone_name, mysql.time_zone_transition, mysql.time_zone_transition_type, mysql.user; END -EXTERNAL_NAME NULL -EXTERNAL_LANGUAGE NULL -PARAMETER_STYLE SQL -IS_DETERMINISTIC NO -SQL_DATA_ACCESS CONTAINS SQL -SQL_PATH NULL -SECURITY_TYPE DEFINER -CREATED <modified> -LAST_ALTERED <created> -SQL_MODE -ROUTINE_COMMENT -DEFINER root@localhost -CHARACTER_SET_CLIENT latin1 -COLLATION_CONNECTION latin1_swedish_ci -DATABASE_COLLATION latin1_swedish_ci -SPECIFIC_NAME check_warnings -ROUTINE_CATALOG NULL -ROUTINE_SCHEMA mtr -ROUTINE_NAME check_warnings -ROUTINE_TYPE PROCEDURE -DTD_IDENTIFIER NULL -ROUTINE_BODY SQL -ROUTINE_DEFINITION BEGIN DECLARE `pos` bigint unsigned; SET SQL_LOG_BIN=0; UPDATE error_log el, global_suppressions gs SET suspicious=0 WHERE el.suspicious=1 AND el.line REGEXP gs.pattern; UPDATE error_log el, test_suppressions ts SET suspicious=0 WHERE el.suspicious=1 AND el.line REGEXP ts.pattern; SELECT COUNT(*) INTO @num_warnings FROM error_log WHERE suspicious=1; IF @num_warnings > 0 THEN SELECT file_name, line FROM error_log WHERE suspicious=1; SELECT 2 INTO result; ELSE SELECT 0 INTO RESULT; END IF; TRUNCATE test_suppressions; DROP TABLE error_log; END -EXTERNAL_NAME NULL -EXTERNAL_LANGUAGE NULL -PARAMETER_STYLE SQL -IS_DETERMINISTIC NO -SQL_DATA_ACCESS CONTAINS SQL -SQL_PATH NULL -SECURITY_TYPE DEFINER -CREATED <modified> -LAST_ALTERED <created> -SQL_MODE -ROUTINE_COMMENT -DEFINER root@localhost -CHARACTER_SET_CLIENT latin1 -COLLATION_CONNECTION latin1_swedish_ci -DATABASE_COLLATION latin1_swedish_ci SHOW CREATE FUNCTION fn_1; Function fn_1 sql_mode @@ -670,7 +532,7 @@ ALTER FUNCTION fn_2 CONTAINS SQL; ... now check what is stored: ----------------------------- -SELECT * FROM information_schema.routines; +SELECT * FROM information_schema.routines where routine_schema = 'db_storedproc'; SPECIFIC_NAME fn_1 ROUTINE_CATALOG NULL ROUTINE_SCHEMA db_storedproc @@ -775,75 +637,6 @@ DEFINER root@localhost CHARACTER_SET_CLIENT latin1 COLLATION_CONNECTION latin1_swedish_ci DATABASE_COLLATION latin1_swedish_ci -SPECIFIC_NAME add_suppression -ROUTINE_CATALOG NULL -ROUTINE_SCHEMA mtr -ROUTINE_NAME add_suppression -ROUTINE_TYPE PROCEDURE -DTD_IDENTIFIER NULL -ROUTINE_BODY SQL -ROUTINE_DEFINITION BEGIN INSERT INTO test_suppressions (pattern) VALUES (pattern); END -EXTERNAL_NAME NULL -EXTERNAL_LANGUAGE NULL -PARAMETER_STYLE SQL -IS_DETERMINISTIC NO -SQL_DATA_ACCESS CONTAINS SQL -SQL_PATH NULL -SECURITY_TYPE DEFINER -CREATED <modified> -LAST_ALTERED <created> -SQL_MODE -ROUTINE_COMMENT -DEFINER root@localhost -CHARACTER_SET_CLIENT latin1 -COLLATION_CONNECTION latin1_swedish_ci -DATABASE_COLLATION latin1_swedish_ci -SPECIFIC_NAME check_testcase -ROUTINE_CATALOG NULL -ROUTINE_SCHEMA mtr -ROUTINE_NAME check_testcase -ROUTINE_TYPE PROCEDURE -DTD_IDENTIFIER NULL -ROUTINE_BODY SQL -ROUTINE_DEFINITION BEGIN SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE variable_name != 'timestamp' ORDER BY VARIABLE_NAME; SELECT * FROM INFORMATION_SCHEMA.SCHEMATA; SELECT table_name AS tables_in_test FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='test'; SELECT CONCAT(table_schema, '.', table_name) AS tables_in_mysql FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='mysql' AND table_name != 'ndb_apply_status' ORDER BY tables_in_mysql; SELECT CONCAT(table_schema, '.', table_name) AS columns_in_mysql, column_name, ordinal_position, column_default, is_nullable, data_type, character_maximum_length, character_octet_length, numeric_precision, numeric_scale, character_set_name, collation_name, column_type, column_key, extra, column_comment FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema='mysql' AND table_name != 'ndb_apply_status' ORDER BY columns_in_mysql; checksum table mysql.columns_priv, mysql.db, mysql.func, mysql.help_category, mysql.help_keyword, mysql.help_relation, mysql.host, mysql.proc, mysql.procs_priv, mysql.tables_priv, mysql.time_zone, mysql.time_zone_leap_second, mysql.time_zone_name, mysql.time_zone_transition, mysql.time_zone_transition_type, mysql.user; END -EXTERNAL_NAME NULL -EXTERNAL_LANGUAGE NULL -PARAMETER_STYLE SQL -IS_DETERMINISTIC NO -SQL_DATA_ACCESS CONTAINS SQL -SQL_PATH NULL -SECURITY_TYPE DEFINER -CREATED <modified> -LAST_ALTERED <created> -SQL_MODE -ROUTINE_COMMENT -DEFINER root@localhost -CHARACTER_SET_CLIENT latin1 -COLLATION_CONNECTION latin1_swedish_ci -DATABASE_COLLATION latin1_swedish_ci -SPECIFIC_NAME check_warnings -ROUTINE_CATALOG NULL -ROUTINE_SCHEMA mtr -ROUTINE_NAME check_warnings -ROUTINE_TYPE PROCEDURE -DTD_IDENTIFIER NULL -ROUTINE_BODY SQL -ROUTINE_DEFINITION BEGIN DECLARE `pos` bigint unsigned; SET SQL_LOG_BIN=0; UPDATE error_log el, global_suppressions gs SET suspicious=0 WHERE el.suspicious=1 AND el.line REGEXP gs.pattern; UPDATE error_log el, test_suppressions ts SET suspicious=0 WHERE el.suspicious=1 AND el.line REGEXP ts.pattern; SELECT COUNT(*) INTO @num_warnings FROM error_log WHERE suspicious=1; IF @num_warnings > 0 THEN SELECT file_name, line FROM error_log WHERE suspicious=1; SELECT 2 INTO result; ELSE SELECT 0 INTO RESULT; END IF; TRUNCATE test_suppressions; DROP TABLE error_log; END -EXTERNAL_NAME NULL -EXTERNAL_LANGUAGE NULL -PARAMETER_STYLE SQL -IS_DETERMINISTIC NO -SQL_DATA_ACCESS CONTAINS SQL -SQL_PATH NULL -SECURITY_TYPE DEFINER -CREATED <modified> -LAST_ALTERED <created> -SQL_MODE -ROUTINE_COMMENT -DEFINER root@localhost -CHARACTER_SET_CLIENT latin1 -COLLATION_CONNECTION latin1_swedish_ci -DATABASE_COLLATION latin1_swedish_ci SHOW CREATE FUNCTION fn_1; Function fn_1 sql_mode diff --git a/mysql-test/suite/funcs_1/r/innodb_trig_0102.result b/mysql-test/suite/funcs_1/r/innodb_trig_0102.result index 86c2d2521ac..5ca24acf0bf 100644 --- a/mysql-test/suite/funcs_1/r/innodb_trig_0102.result +++ b/mysql-test/suite/funcs_1/r/innodb_trig_0102.result @@ -348,13 +348,13 @@ for each row set @test_var2='trig1_a'; create trigger trig_db2.trig2 before insert on trig_db2.t1 for each row set @test_var3='trig2'; select trigger_schema, trigger_name, event_object_table -from information_schema.triggers order by trigger_name; +from information_schema.triggers +where trigger_schema like 'trig_db%' + order by trigger_name; trigger_schema trigger_name event_object_table -mtr gs_insert global_suppressions trig_db1 trig1_a t1 trig_db1 trig1_b t1 trig_db2 trig2 t1 -mtr ts_insert test_suppressions set @test_var1= '', @test_var2= '', @test_var3= ''; insert into t1 (f1,f2) values ('insert to db1 t1 from db1',352); insert into trig_db2.t1 (f1,f2) values ('insert to db2 t1 from db1',352); diff --git a/mysql-test/suite/funcs_1/r/innodb_trig_0407.result b/mysql-test/suite/funcs_1/r/innodb_trig_0407.result index 62c8e0d06db..33e58f50ec1 100644 --- a/mysql-test/suite/funcs_1/r/innodb_trig_0407.result +++ b/mysql-test/suite/funcs_1/r/innodb_trig_0407.result @@ -90,10 +90,10 @@ f1 Trigger 3.5.4.1 drop trigger trg1; select trigger_schema, trigger_name, event_object_table -from information_schema.triggers order by trigger_name; +from information_schema.triggers +where trigger_schema = 'db_drop' + order by trigger_name; trigger_schema trigger_name event_object_table -mtr gs_insert global_suppressions -mtr ts_insert test_suppressions Insert into t1 values ('Insert no trigger 3.5.4.1'); Select * from t1 order by f1; f1 @@ -151,12 +151,8 @@ Select * from t1; f1 Trigger 3.5.4.4 Drop database db_drop4; -Show databases; -Database -information_schema -mtr -mysql -test +Show databases like 'db_drop4'; +Database (db_drop4) select trigger_schema, trigger_name, event_object_table from information_schema.triggers where information_schema.triggers.trigger_name='trg4'; diff --git a/mysql-test/suite/funcs_1/r/memory_storedproc_08.result b/mysql-test/suite/funcs_1/r/memory_storedproc_08.result index 2740a2cafa8..7f08a77ef09 100644 --- a/mysql-test/suite/funcs_1/r/memory_storedproc_08.result +++ b/mysql-test/suite/funcs_1/r/memory_storedproc_08.result @@ -104,7 +104,7 @@ END// ... now check what is stored: ----------------------------- -SELECT * FROM information_schema.routines; +SELECT * FROM information_schema.routines where routine_schema = 'db_storedproc'; SPECIFIC_NAME fn_1 ROUTINE_CATALOG NULL ROUTINE_SCHEMA db_storedproc @@ -209,75 +209,6 @@ DEFINER root@localhost CHARACTER_SET_CLIENT latin1 COLLATION_CONNECTION latin1_swedish_ci DATABASE_COLLATION latin1_swedish_ci -SPECIFIC_NAME add_suppression -ROUTINE_CATALOG NULL -ROUTINE_SCHEMA mtr -ROUTINE_NAME add_suppression -ROUTINE_TYPE PROCEDURE -DTD_IDENTIFIER NULL -ROUTINE_BODY SQL -ROUTINE_DEFINITION BEGIN INSERT INTO test_suppressions (pattern) VALUES (pattern); END -EXTERNAL_NAME NULL -EXTERNAL_LANGUAGE NULL -PARAMETER_STYLE SQL -IS_DETERMINISTIC NO -SQL_DATA_ACCESS CONTAINS SQL -SQL_PATH NULL -SECURITY_TYPE DEFINER -CREATED <modified> -LAST_ALTERED <created> -SQL_MODE -ROUTINE_COMMENT -DEFINER root@localhost -CHARACTER_SET_CLIENT latin1 -COLLATION_CONNECTION latin1_swedish_ci -DATABASE_COLLATION latin1_swedish_ci -SPECIFIC_NAME check_testcase -ROUTINE_CATALOG NULL -ROUTINE_SCHEMA mtr -ROUTINE_NAME check_testcase -ROUTINE_TYPE PROCEDURE -DTD_IDENTIFIER NULL -ROUTINE_BODY SQL -ROUTINE_DEFINITION BEGIN SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE variable_name != 'timestamp' ORDER BY VARIABLE_NAME; SELECT * FROM INFORMATION_SCHEMA.SCHEMATA; SELECT table_name AS tables_in_test FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='test'; SELECT CONCAT(table_schema, '.', table_name) AS tables_in_mysql FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='mysql' AND table_name != 'ndb_apply_status' ORDER BY tables_in_mysql; SELECT CONCAT(table_schema, '.', table_name) AS columns_in_mysql, column_name, ordinal_position, column_default, is_nullable, data_type, character_maximum_length, character_octet_length, numeric_precision, numeric_scale, character_set_name, collation_name, column_type, column_key, extra, column_comment FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema='mysql' AND table_name != 'ndb_apply_status' ORDER BY columns_in_mysql; checksum table mysql.columns_priv, mysql.db, mysql.func, mysql.help_category, mysql.help_keyword, mysql.help_relation, mysql.host, mysql.proc, mysql.procs_priv, mysql.tables_priv, mysql.time_zone, mysql.time_zone_leap_second, mysql.time_zone_name, mysql.time_zone_transition, mysql.time_zone_transition_type, mysql.user; END -EXTERNAL_NAME NULL -EXTERNAL_LANGUAGE NULL -PARAMETER_STYLE SQL -IS_DETERMINISTIC NO -SQL_DATA_ACCESS CONTAINS SQL -SQL_PATH NULL -SECURITY_TYPE DEFINER -CREATED <modified> -LAST_ALTERED <created> -SQL_MODE -ROUTINE_COMMENT -DEFINER root@localhost -CHARACTER_SET_CLIENT latin1 -COLLATION_CONNECTION latin1_swedish_ci -DATABASE_COLLATION latin1_swedish_ci -SPECIFIC_NAME check_warnings -ROUTINE_CATALOG NULL -ROUTINE_SCHEMA mtr -ROUTINE_NAME check_warnings -ROUTINE_TYPE PROCEDURE -DTD_IDENTIFIER NULL -ROUTINE_BODY SQL -ROUTINE_DEFINITION BEGIN DECLARE `pos` bigint unsigned; SET SQL_LOG_BIN=0; UPDATE error_log el, global_suppressions gs SET suspicious=0 WHERE el.suspicious=1 AND el.line REGEXP gs.pattern; UPDATE error_log el, test_suppressions ts SET suspicious=0 WHERE el.suspicious=1 AND el.line REGEXP ts.pattern; SELECT COUNT(*) INTO @num_warnings FROM error_log WHERE suspicious=1; IF @num_warnings > 0 THEN SELECT file_name, line FROM error_log WHERE suspicious=1; SELECT 2 INTO result; ELSE SELECT 0 INTO RESULT; END IF; TRUNCATE test_suppressions; DROP TABLE error_log; END -EXTERNAL_NAME NULL -EXTERNAL_LANGUAGE NULL -PARAMETER_STYLE SQL -IS_DETERMINISTIC NO -SQL_DATA_ACCESS CONTAINS SQL -SQL_PATH NULL -SECURITY_TYPE DEFINER -CREATED <modified> -LAST_ALTERED <created> -SQL_MODE -ROUTINE_COMMENT -DEFINER root@localhost -CHARACTER_SET_CLIENT latin1 -COLLATION_CONNECTION latin1_swedish_ci -DATABASE_COLLATION latin1_swedish_ci SHOW CREATE FUNCTION fn_1; Function fn_1 sql_mode NO_ENGINE_SUBSTITUTION @@ -391,7 +322,7 @@ ALTER FUNCTION fn_2 MODIFIES SQL DATA; ... now check what is stored: ----------------------------- -SELECT * FROM information_schema.routines; +SELECT * FROM information_schema.routines where routine_schema = 'db_storedproc'; SPECIFIC_NAME fn_1 ROUTINE_CATALOG NULL ROUTINE_SCHEMA db_storedproc @@ -496,75 +427,6 @@ DEFINER root@localhost CHARACTER_SET_CLIENT latin1 COLLATION_CONNECTION latin1_swedish_ci DATABASE_COLLATION latin1_swedish_ci -SPECIFIC_NAME add_suppression -ROUTINE_CATALOG NULL -ROUTINE_SCHEMA mtr -ROUTINE_NAME add_suppression -ROUTINE_TYPE PROCEDURE -DTD_IDENTIFIER NULL -ROUTINE_BODY SQL -ROUTINE_DEFINITION BEGIN INSERT INTO test_suppressions (pattern) VALUES (pattern); END -EXTERNAL_NAME NULL -EXTERNAL_LANGUAGE NULL -PARAMETER_STYLE SQL -IS_DETERMINISTIC NO -SQL_DATA_ACCESS CONTAINS SQL -SQL_PATH NULL -SECURITY_TYPE DEFINER -CREATED <modified> -LAST_ALTERED <created> -SQL_MODE -ROUTINE_COMMENT -DEFINER root@localhost -CHARACTER_SET_CLIENT latin1 -COLLATION_CONNECTION latin1_swedish_ci -DATABASE_COLLATION latin1_swedish_ci -SPECIFIC_NAME check_testcase -ROUTINE_CATALOG NULL -ROUTINE_SCHEMA mtr -ROUTINE_NAME check_testcase -ROUTINE_TYPE PROCEDURE -DTD_IDENTIFIER NULL -ROUTINE_BODY SQL -ROUTINE_DEFINITION BEGIN SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE variable_name != 'timestamp' ORDER BY VARIABLE_NAME; SELECT * FROM INFORMATION_SCHEMA.SCHEMATA; SELECT table_name AS tables_in_test FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='test'; SELECT CONCAT(table_schema, '.', table_name) AS tables_in_mysql FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='mysql' AND table_name != 'ndb_apply_status' ORDER BY tables_in_mysql; SELECT CONCAT(table_schema, '.', table_name) AS columns_in_mysql, column_name, ordinal_position, column_default, is_nullable, data_type, character_maximum_length, character_octet_length, numeric_precision, numeric_scale, character_set_name, collation_name, column_type, column_key, extra, column_comment FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema='mysql' AND table_name != 'ndb_apply_status' ORDER BY columns_in_mysql; checksum table mysql.columns_priv, mysql.db, mysql.func, mysql.help_category, mysql.help_keyword, mysql.help_relation, mysql.host, mysql.proc, mysql.procs_priv, mysql.tables_priv, mysql.time_zone, mysql.time_zone_leap_second, mysql.time_zone_name, mysql.time_zone_transition, mysql.time_zone_transition_type, mysql.user; END -EXTERNAL_NAME NULL -EXTERNAL_LANGUAGE NULL -PARAMETER_STYLE SQL -IS_DETERMINISTIC NO -SQL_DATA_ACCESS CONTAINS SQL -SQL_PATH NULL -SECURITY_TYPE DEFINER -CREATED <modified> -LAST_ALTERED <created> -SQL_MODE -ROUTINE_COMMENT -DEFINER root@localhost -CHARACTER_SET_CLIENT latin1 -COLLATION_CONNECTION latin1_swedish_ci -DATABASE_COLLATION latin1_swedish_ci -SPECIFIC_NAME check_warnings -ROUTINE_CATALOG NULL -ROUTINE_SCHEMA mtr -ROUTINE_NAME check_warnings -ROUTINE_TYPE PROCEDURE -DTD_IDENTIFIER NULL -ROUTINE_BODY SQL -ROUTINE_DEFINITION BEGIN DECLARE `pos` bigint unsigned; SET SQL_LOG_BIN=0; UPDATE error_log el, global_suppressions gs SET suspicious=0 WHERE el.suspicious=1 AND el.line REGEXP gs.pattern; UPDATE error_log el, test_suppressions ts SET suspicious=0 WHERE el.suspicious=1 AND el.line REGEXP ts.pattern; SELECT COUNT(*) INTO @num_warnings FROM error_log WHERE suspicious=1; IF @num_warnings > 0 THEN SELECT file_name, line FROM error_log WHERE suspicious=1; SELECT 2 INTO result; ELSE SELECT 0 INTO RESULT; END IF; TRUNCATE test_suppressions; DROP TABLE error_log; END -EXTERNAL_NAME NULL -EXTERNAL_LANGUAGE NULL -PARAMETER_STYLE SQL -IS_DETERMINISTIC NO -SQL_DATA_ACCESS CONTAINS SQL -SQL_PATH NULL -SECURITY_TYPE DEFINER -CREATED <modified> -LAST_ALTERED <created> -SQL_MODE -ROUTINE_COMMENT -DEFINER root@localhost -CHARACTER_SET_CLIENT latin1 -COLLATION_CONNECTION latin1_swedish_ci -DATABASE_COLLATION latin1_swedish_ci SHOW CREATE FUNCTION fn_1; Function fn_1 sql_mode NO_ENGINE_SUBSTITUTION @@ -671,7 +533,7 @@ ALTER FUNCTION fn_2 CONTAINS SQL; ... now check what is stored: ----------------------------- -SELECT * FROM information_schema.routines; +SELECT * FROM information_schema.routines where routine_schema = 'db_storedproc'; SPECIFIC_NAME fn_1 ROUTINE_CATALOG NULL ROUTINE_SCHEMA db_storedproc @@ -776,75 +638,6 @@ DEFINER root@localhost CHARACTER_SET_CLIENT latin1 COLLATION_CONNECTION latin1_swedish_ci DATABASE_COLLATION latin1_swedish_ci -SPECIFIC_NAME add_suppression -ROUTINE_CATALOG NULL -ROUTINE_SCHEMA mtr -ROUTINE_NAME add_suppression -ROUTINE_TYPE PROCEDURE -DTD_IDENTIFIER NULL -ROUTINE_BODY SQL -ROUTINE_DEFINITION BEGIN INSERT INTO test_suppressions (pattern) VALUES (pattern); END -EXTERNAL_NAME NULL -EXTERNAL_LANGUAGE NULL -PARAMETER_STYLE SQL -IS_DETERMINISTIC NO -SQL_DATA_ACCESS CONTAINS SQL -SQL_PATH NULL -SECURITY_TYPE DEFINER -CREATED <modified> -LAST_ALTERED <created> -SQL_MODE -ROUTINE_COMMENT -DEFINER root@localhost -CHARACTER_SET_CLIENT latin1 -COLLATION_CONNECTION latin1_swedish_ci -DATABASE_COLLATION latin1_swedish_ci -SPECIFIC_NAME check_testcase -ROUTINE_CATALOG NULL -ROUTINE_SCHEMA mtr -ROUTINE_NAME check_testcase -ROUTINE_TYPE PROCEDURE -DTD_IDENTIFIER NULL -ROUTINE_BODY SQL -ROUTINE_DEFINITION BEGIN SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE variable_name != 'timestamp' ORDER BY VARIABLE_NAME; SELECT * FROM INFORMATION_SCHEMA.SCHEMATA; SELECT table_name AS tables_in_test FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='test'; SELECT CONCAT(table_schema, '.', table_name) AS tables_in_mysql FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='mysql' AND table_name != 'ndb_apply_status' ORDER BY tables_in_mysql; SELECT CONCAT(table_schema, '.', table_name) AS columns_in_mysql, column_name, ordinal_position, column_default, is_nullable, data_type, character_maximum_length, character_octet_length, numeric_precision, numeric_scale, character_set_name, collation_name, column_type, column_key, extra, column_comment FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema='mysql' AND table_name != 'ndb_apply_status' ORDER BY columns_in_mysql; checksum table mysql.columns_priv, mysql.db, mysql.func, mysql.help_category, mysql.help_keyword, mysql.help_relation, mysql.host, mysql.proc, mysql.procs_priv, mysql.tables_priv, mysql.time_zone, mysql.time_zone_leap_second, mysql.time_zone_name, mysql.time_zone_transition, mysql.time_zone_transition_type, mysql.user; END -EXTERNAL_NAME NULL -EXTERNAL_LANGUAGE NULL -PARAMETER_STYLE SQL -IS_DETERMINISTIC NO -SQL_DATA_ACCESS CONTAINS SQL -SQL_PATH NULL -SECURITY_TYPE DEFINER -CREATED <modified> -LAST_ALTERED <created> -SQL_MODE -ROUTINE_COMMENT -DEFINER root@localhost -CHARACTER_SET_CLIENT latin1 -COLLATION_CONNECTION latin1_swedish_ci -DATABASE_COLLATION latin1_swedish_ci -SPECIFIC_NAME check_warnings -ROUTINE_CATALOG NULL -ROUTINE_SCHEMA mtr -ROUTINE_NAME check_warnings -ROUTINE_TYPE PROCEDURE -DTD_IDENTIFIER NULL -ROUTINE_BODY SQL -ROUTINE_DEFINITION BEGIN DECLARE `pos` bigint unsigned; SET SQL_LOG_BIN=0; UPDATE error_log el, global_suppressions gs SET suspicious=0 WHERE el.suspicious=1 AND el.line REGEXP gs.pattern; UPDATE error_log el, test_suppressions ts SET suspicious=0 WHERE el.suspicious=1 AND el.line REGEXP ts.pattern; SELECT COUNT(*) INTO @num_warnings FROM error_log WHERE suspicious=1; IF @num_warnings > 0 THEN SELECT file_name, line FROM error_log WHERE suspicious=1; SELECT 2 INTO result; ELSE SELECT 0 INTO RESULT; END IF; TRUNCATE test_suppressions; DROP TABLE error_log; END -EXTERNAL_NAME NULL -EXTERNAL_LANGUAGE NULL -PARAMETER_STYLE SQL -IS_DETERMINISTIC NO -SQL_DATA_ACCESS CONTAINS SQL -SQL_PATH NULL -SECURITY_TYPE DEFINER -CREATED <modified> -LAST_ALTERED <created> -SQL_MODE -ROUTINE_COMMENT -DEFINER root@localhost -CHARACTER_SET_CLIENT latin1 -COLLATION_CONNECTION latin1_swedish_ci -DATABASE_COLLATION latin1_swedish_ci SHOW CREATE FUNCTION fn_1; Function fn_1 sql_mode NO_ENGINE_SUBSTITUTION diff --git a/mysql-test/suite/funcs_1/r/memory_trig_0102.result b/mysql-test/suite/funcs_1/r/memory_trig_0102.result index a95702debcd..c39370dde69 100644 --- a/mysql-test/suite/funcs_1/r/memory_trig_0102.result +++ b/mysql-test/suite/funcs_1/r/memory_trig_0102.result @@ -349,13 +349,13 @@ for each row set @test_var2='trig1_a'; create trigger trig_db2.trig2 before insert on trig_db2.t1 for each row set @test_var3='trig2'; select trigger_schema, trigger_name, event_object_table -from information_schema.triggers order by trigger_name; +from information_schema.triggers +where trigger_schema like 'trig_db%' + order by trigger_name; trigger_schema trigger_name event_object_table -mtr gs_insert global_suppressions trig_db1 trig1_a t1 trig_db1 trig1_b t1 trig_db2 trig2 t1 -mtr ts_insert test_suppressions set @test_var1= '', @test_var2= '', @test_var3= ''; insert into t1 (f1,f2) values ('insert to db1 t1 from db1',352); insert into trig_db2.t1 (f1,f2) values ('insert to db2 t1 from db1',352); diff --git a/mysql-test/suite/funcs_1/r/memory_trig_0407.result b/mysql-test/suite/funcs_1/r/memory_trig_0407.result index feb0017b86b..2f76f5544b9 100644 --- a/mysql-test/suite/funcs_1/r/memory_trig_0407.result +++ b/mysql-test/suite/funcs_1/r/memory_trig_0407.result @@ -91,10 +91,10 @@ f1 Trigger 3.5.4.1 drop trigger trg1; select trigger_schema, trigger_name, event_object_table -from information_schema.triggers order by trigger_name; +from information_schema.triggers +where trigger_schema = 'db_drop' + order by trigger_name; trigger_schema trigger_name event_object_table -mtr gs_insert global_suppressions -mtr ts_insert test_suppressions Insert into t1 values ('Insert no trigger 3.5.4.1'); Select * from t1 order by f1; f1 @@ -152,12 +152,8 @@ Select * from t1; f1 Trigger 3.5.4.4 Drop database db_drop4; -Show databases; -Database -information_schema -mtr -mysql -test +Show databases like 'db_drop4'; +Database (db_drop4) select trigger_schema, trigger_name, event_object_table from information_schema.triggers where information_schema.triggers.trigger_name='trg4'; diff --git a/mysql-test/suite/funcs_1/r/myisam_storedproc_08.result b/mysql-test/suite/funcs_1/r/myisam_storedproc_08.result index 2740a2cafa8..7f08a77ef09 100644 --- a/mysql-test/suite/funcs_1/r/myisam_storedproc_08.result +++ b/mysql-test/suite/funcs_1/r/myisam_storedproc_08.result @@ -104,7 +104,7 @@ END// ... now check what is stored: ----------------------------- -SELECT * FROM information_schema.routines; +SELECT * FROM information_schema.routines where routine_schema = 'db_storedproc'; SPECIFIC_NAME fn_1 ROUTINE_CATALOG NULL ROUTINE_SCHEMA db_storedproc @@ -209,75 +209,6 @@ DEFINER root@localhost CHARACTER_SET_CLIENT latin1 COLLATION_CONNECTION latin1_swedish_ci DATABASE_COLLATION latin1_swedish_ci -SPECIFIC_NAME add_suppression -ROUTINE_CATALOG NULL -ROUTINE_SCHEMA mtr -ROUTINE_NAME add_suppression -ROUTINE_TYPE PROCEDURE -DTD_IDENTIFIER NULL -ROUTINE_BODY SQL -ROUTINE_DEFINITION BEGIN INSERT INTO test_suppressions (pattern) VALUES (pattern); END -EXTERNAL_NAME NULL -EXTERNAL_LANGUAGE NULL -PARAMETER_STYLE SQL -IS_DETERMINISTIC NO -SQL_DATA_ACCESS CONTAINS SQL -SQL_PATH NULL -SECURITY_TYPE DEFINER -CREATED <modified> -LAST_ALTERED <created> -SQL_MODE -ROUTINE_COMMENT -DEFINER root@localhost -CHARACTER_SET_CLIENT latin1 -COLLATION_CONNECTION latin1_swedish_ci -DATABASE_COLLATION latin1_swedish_ci -SPECIFIC_NAME check_testcase -ROUTINE_CATALOG NULL -ROUTINE_SCHEMA mtr -ROUTINE_NAME check_testcase -ROUTINE_TYPE PROCEDURE -DTD_IDENTIFIER NULL -ROUTINE_BODY SQL -ROUTINE_DEFINITION BEGIN SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE variable_name != 'timestamp' ORDER BY VARIABLE_NAME; SELECT * FROM INFORMATION_SCHEMA.SCHEMATA; SELECT table_name AS tables_in_test FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='test'; SELECT CONCAT(table_schema, '.', table_name) AS tables_in_mysql FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='mysql' AND table_name != 'ndb_apply_status' ORDER BY tables_in_mysql; SELECT CONCAT(table_schema, '.', table_name) AS columns_in_mysql, column_name, ordinal_position, column_default, is_nullable, data_type, character_maximum_length, character_octet_length, numeric_precision, numeric_scale, character_set_name, collation_name, column_type, column_key, extra, column_comment FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema='mysql' AND table_name != 'ndb_apply_status' ORDER BY columns_in_mysql; checksum table mysql.columns_priv, mysql.db, mysql.func, mysql.help_category, mysql.help_keyword, mysql.help_relation, mysql.host, mysql.proc, mysql.procs_priv, mysql.tables_priv, mysql.time_zone, mysql.time_zone_leap_second, mysql.time_zone_name, mysql.time_zone_transition, mysql.time_zone_transition_type, mysql.user; END -EXTERNAL_NAME NULL -EXTERNAL_LANGUAGE NULL -PARAMETER_STYLE SQL -IS_DETERMINISTIC NO -SQL_DATA_ACCESS CONTAINS SQL -SQL_PATH NULL -SECURITY_TYPE DEFINER -CREATED <modified> -LAST_ALTERED <created> -SQL_MODE -ROUTINE_COMMENT -DEFINER root@localhost -CHARACTER_SET_CLIENT latin1 -COLLATION_CONNECTION latin1_swedish_ci -DATABASE_COLLATION latin1_swedish_ci -SPECIFIC_NAME check_warnings -ROUTINE_CATALOG NULL -ROUTINE_SCHEMA mtr -ROUTINE_NAME check_warnings -ROUTINE_TYPE PROCEDURE -DTD_IDENTIFIER NULL -ROUTINE_BODY SQL -ROUTINE_DEFINITION BEGIN DECLARE `pos` bigint unsigned; SET SQL_LOG_BIN=0; UPDATE error_log el, global_suppressions gs SET suspicious=0 WHERE el.suspicious=1 AND el.line REGEXP gs.pattern; UPDATE error_log el, test_suppressions ts SET suspicious=0 WHERE el.suspicious=1 AND el.line REGEXP ts.pattern; SELECT COUNT(*) INTO @num_warnings FROM error_log WHERE suspicious=1; IF @num_warnings > 0 THEN SELECT file_name, line FROM error_log WHERE suspicious=1; SELECT 2 INTO result; ELSE SELECT 0 INTO RESULT; END IF; TRUNCATE test_suppressions; DROP TABLE error_log; END -EXTERNAL_NAME NULL -EXTERNAL_LANGUAGE NULL -PARAMETER_STYLE SQL -IS_DETERMINISTIC NO -SQL_DATA_ACCESS CONTAINS SQL -SQL_PATH NULL -SECURITY_TYPE DEFINER -CREATED <modified> -LAST_ALTERED <created> -SQL_MODE -ROUTINE_COMMENT -DEFINER root@localhost -CHARACTER_SET_CLIENT latin1 -COLLATION_CONNECTION latin1_swedish_ci -DATABASE_COLLATION latin1_swedish_ci SHOW CREATE FUNCTION fn_1; Function fn_1 sql_mode NO_ENGINE_SUBSTITUTION @@ -391,7 +322,7 @@ ALTER FUNCTION fn_2 MODIFIES SQL DATA; ... now check what is stored: ----------------------------- -SELECT * FROM information_schema.routines; +SELECT * FROM information_schema.routines where routine_schema = 'db_storedproc'; SPECIFIC_NAME fn_1 ROUTINE_CATALOG NULL ROUTINE_SCHEMA db_storedproc @@ -496,75 +427,6 @@ DEFINER root@localhost CHARACTER_SET_CLIENT latin1 COLLATION_CONNECTION latin1_swedish_ci DATABASE_COLLATION latin1_swedish_ci -SPECIFIC_NAME add_suppression -ROUTINE_CATALOG NULL -ROUTINE_SCHEMA mtr -ROUTINE_NAME add_suppression -ROUTINE_TYPE PROCEDURE -DTD_IDENTIFIER NULL -ROUTINE_BODY SQL -ROUTINE_DEFINITION BEGIN INSERT INTO test_suppressions (pattern) VALUES (pattern); END -EXTERNAL_NAME NULL -EXTERNAL_LANGUAGE NULL -PARAMETER_STYLE SQL -IS_DETERMINISTIC NO -SQL_DATA_ACCESS CONTAINS SQL -SQL_PATH NULL -SECURITY_TYPE DEFINER -CREATED <modified> -LAST_ALTERED <created> -SQL_MODE -ROUTINE_COMMENT -DEFINER root@localhost -CHARACTER_SET_CLIENT latin1 -COLLATION_CONNECTION latin1_swedish_ci -DATABASE_COLLATION latin1_swedish_ci -SPECIFIC_NAME check_testcase -ROUTINE_CATALOG NULL -ROUTINE_SCHEMA mtr -ROUTINE_NAME check_testcase -ROUTINE_TYPE PROCEDURE -DTD_IDENTIFIER NULL -ROUTINE_BODY SQL -ROUTINE_DEFINITION BEGIN SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE variable_name != 'timestamp' ORDER BY VARIABLE_NAME; SELECT * FROM INFORMATION_SCHEMA.SCHEMATA; SELECT table_name AS tables_in_test FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='test'; SELECT CONCAT(table_schema, '.', table_name) AS tables_in_mysql FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='mysql' AND table_name != 'ndb_apply_status' ORDER BY tables_in_mysql; SELECT CONCAT(table_schema, '.', table_name) AS columns_in_mysql, column_name, ordinal_position, column_default, is_nullable, data_type, character_maximum_length, character_octet_length, numeric_precision, numeric_scale, character_set_name, collation_name, column_type, column_key, extra, column_comment FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema='mysql' AND table_name != 'ndb_apply_status' ORDER BY columns_in_mysql; checksum table mysql.columns_priv, mysql.db, mysql.func, mysql.help_category, mysql.help_keyword, mysql.help_relation, mysql.host, mysql.proc, mysql.procs_priv, mysql.tables_priv, mysql.time_zone, mysql.time_zone_leap_second, mysql.time_zone_name, mysql.time_zone_transition, mysql.time_zone_transition_type, mysql.user; END -EXTERNAL_NAME NULL -EXTERNAL_LANGUAGE NULL -PARAMETER_STYLE SQL -IS_DETERMINISTIC NO -SQL_DATA_ACCESS CONTAINS SQL -SQL_PATH NULL -SECURITY_TYPE DEFINER -CREATED <modified> -LAST_ALTERED <created> -SQL_MODE -ROUTINE_COMMENT -DEFINER root@localhost -CHARACTER_SET_CLIENT latin1 -COLLATION_CONNECTION latin1_swedish_ci -DATABASE_COLLATION latin1_swedish_ci -SPECIFIC_NAME check_warnings -ROUTINE_CATALOG NULL -ROUTINE_SCHEMA mtr -ROUTINE_NAME check_warnings -ROUTINE_TYPE PROCEDURE -DTD_IDENTIFIER NULL -ROUTINE_BODY SQL -ROUTINE_DEFINITION BEGIN DECLARE `pos` bigint unsigned; SET SQL_LOG_BIN=0; UPDATE error_log el, global_suppressions gs SET suspicious=0 WHERE el.suspicious=1 AND el.line REGEXP gs.pattern; UPDATE error_log el, test_suppressions ts SET suspicious=0 WHERE el.suspicious=1 AND el.line REGEXP ts.pattern; SELECT COUNT(*) INTO @num_warnings FROM error_log WHERE suspicious=1; IF @num_warnings > 0 THEN SELECT file_name, line FROM error_log WHERE suspicious=1; SELECT 2 INTO result; ELSE SELECT 0 INTO RESULT; END IF; TRUNCATE test_suppressions; DROP TABLE error_log; END -EXTERNAL_NAME NULL -EXTERNAL_LANGUAGE NULL -PARAMETER_STYLE SQL -IS_DETERMINISTIC NO -SQL_DATA_ACCESS CONTAINS SQL -SQL_PATH NULL -SECURITY_TYPE DEFINER -CREATED <modified> -LAST_ALTERED <created> -SQL_MODE -ROUTINE_COMMENT -DEFINER root@localhost -CHARACTER_SET_CLIENT latin1 -COLLATION_CONNECTION latin1_swedish_ci -DATABASE_COLLATION latin1_swedish_ci SHOW CREATE FUNCTION fn_1; Function fn_1 sql_mode NO_ENGINE_SUBSTITUTION @@ -671,7 +533,7 @@ ALTER FUNCTION fn_2 CONTAINS SQL; ... now check what is stored: ----------------------------- -SELECT * FROM information_schema.routines; +SELECT * FROM information_schema.routines where routine_schema = 'db_storedproc'; SPECIFIC_NAME fn_1 ROUTINE_CATALOG NULL ROUTINE_SCHEMA db_storedproc @@ -776,75 +638,6 @@ DEFINER root@localhost CHARACTER_SET_CLIENT latin1 COLLATION_CONNECTION latin1_swedish_ci DATABASE_COLLATION latin1_swedish_ci -SPECIFIC_NAME add_suppression -ROUTINE_CATALOG NULL -ROUTINE_SCHEMA mtr -ROUTINE_NAME add_suppression -ROUTINE_TYPE PROCEDURE -DTD_IDENTIFIER NULL -ROUTINE_BODY SQL -ROUTINE_DEFINITION BEGIN INSERT INTO test_suppressions (pattern) VALUES (pattern); END -EXTERNAL_NAME NULL -EXTERNAL_LANGUAGE NULL -PARAMETER_STYLE SQL -IS_DETERMINISTIC NO -SQL_DATA_ACCESS CONTAINS SQL -SQL_PATH NULL -SECURITY_TYPE DEFINER -CREATED <modified> -LAST_ALTERED <created> -SQL_MODE -ROUTINE_COMMENT -DEFINER root@localhost -CHARACTER_SET_CLIENT latin1 -COLLATION_CONNECTION latin1_swedish_ci -DATABASE_COLLATION latin1_swedish_ci -SPECIFIC_NAME check_testcase -ROUTINE_CATALOG NULL -ROUTINE_SCHEMA mtr -ROUTINE_NAME check_testcase -ROUTINE_TYPE PROCEDURE -DTD_IDENTIFIER NULL -ROUTINE_BODY SQL -ROUTINE_DEFINITION BEGIN SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE variable_name != 'timestamp' ORDER BY VARIABLE_NAME; SELECT * FROM INFORMATION_SCHEMA.SCHEMATA; SELECT table_name AS tables_in_test FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='test'; SELECT CONCAT(table_schema, '.', table_name) AS tables_in_mysql FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='mysql' AND table_name != 'ndb_apply_status' ORDER BY tables_in_mysql; SELECT CONCAT(table_schema, '.', table_name) AS columns_in_mysql, column_name, ordinal_position, column_default, is_nullable, data_type, character_maximum_length, character_octet_length, numeric_precision, numeric_scale, character_set_name, collation_name, column_type, column_key, extra, column_comment FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema='mysql' AND table_name != 'ndb_apply_status' ORDER BY columns_in_mysql; checksum table mysql.columns_priv, mysql.db, mysql.func, mysql.help_category, mysql.help_keyword, mysql.help_relation, mysql.host, mysql.proc, mysql.procs_priv, mysql.tables_priv, mysql.time_zone, mysql.time_zone_leap_second, mysql.time_zone_name, mysql.time_zone_transition, mysql.time_zone_transition_type, mysql.user; END -EXTERNAL_NAME NULL -EXTERNAL_LANGUAGE NULL -PARAMETER_STYLE SQL -IS_DETERMINISTIC NO -SQL_DATA_ACCESS CONTAINS SQL -SQL_PATH NULL -SECURITY_TYPE DEFINER -CREATED <modified> -LAST_ALTERED <created> -SQL_MODE -ROUTINE_COMMENT -DEFINER root@localhost -CHARACTER_SET_CLIENT latin1 -COLLATION_CONNECTION latin1_swedish_ci -DATABASE_COLLATION latin1_swedish_ci -SPECIFIC_NAME check_warnings -ROUTINE_CATALOG NULL -ROUTINE_SCHEMA mtr -ROUTINE_NAME check_warnings -ROUTINE_TYPE PROCEDURE -DTD_IDENTIFIER NULL -ROUTINE_BODY SQL -ROUTINE_DEFINITION BEGIN DECLARE `pos` bigint unsigned; SET SQL_LOG_BIN=0; UPDATE error_log el, global_suppressions gs SET suspicious=0 WHERE el.suspicious=1 AND el.line REGEXP gs.pattern; UPDATE error_log el, test_suppressions ts SET suspicious=0 WHERE el.suspicious=1 AND el.line REGEXP ts.pattern; SELECT COUNT(*) INTO @num_warnings FROM error_log WHERE suspicious=1; IF @num_warnings > 0 THEN SELECT file_name, line FROM error_log WHERE suspicious=1; SELECT 2 INTO result; ELSE SELECT 0 INTO RESULT; END IF; TRUNCATE test_suppressions; DROP TABLE error_log; END -EXTERNAL_NAME NULL -EXTERNAL_LANGUAGE NULL -PARAMETER_STYLE SQL -IS_DETERMINISTIC NO -SQL_DATA_ACCESS CONTAINS SQL -SQL_PATH NULL -SECURITY_TYPE DEFINER -CREATED <modified> -LAST_ALTERED <created> -SQL_MODE -ROUTINE_COMMENT -DEFINER root@localhost -CHARACTER_SET_CLIENT latin1 -COLLATION_CONNECTION latin1_swedish_ci -DATABASE_COLLATION latin1_swedish_ci SHOW CREATE FUNCTION fn_1; Function fn_1 sql_mode NO_ENGINE_SUBSTITUTION diff --git a/mysql-test/suite/funcs_1/r/myisam_trig_0102.result b/mysql-test/suite/funcs_1/r/myisam_trig_0102.result index a95702debcd..c39370dde69 100644 --- a/mysql-test/suite/funcs_1/r/myisam_trig_0102.result +++ b/mysql-test/suite/funcs_1/r/myisam_trig_0102.result @@ -349,13 +349,13 @@ for each row set @test_var2='trig1_a'; create trigger trig_db2.trig2 before insert on trig_db2.t1 for each row set @test_var3='trig2'; select trigger_schema, trigger_name, event_object_table -from information_schema.triggers order by trigger_name; +from information_schema.triggers +where trigger_schema like 'trig_db%' + order by trigger_name; trigger_schema trigger_name event_object_table -mtr gs_insert global_suppressions trig_db1 trig1_a t1 trig_db1 trig1_b t1 trig_db2 trig2 t1 -mtr ts_insert test_suppressions set @test_var1= '', @test_var2= '', @test_var3= ''; insert into t1 (f1,f2) values ('insert to db1 t1 from db1',352); insert into trig_db2.t1 (f1,f2) values ('insert to db2 t1 from db1',352); diff --git a/mysql-test/suite/funcs_1/r/myisam_trig_0407.result b/mysql-test/suite/funcs_1/r/myisam_trig_0407.result index feb0017b86b..2f76f5544b9 100644 --- a/mysql-test/suite/funcs_1/r/myisam_trig_0407.result +++ b/mysql-test/suite/funcs_1/r/myisam_trig_0407.result @@ -91,10 +91,10 @@ f1 Trigger 3.5.4.1 drop trigger trg1; select trigger_schema, trigger_name, event_object_table -from information_schema.triggers order by trigger_name; +from information_schema.triggers +where trigger_schema = 'db_drop' + order by trigger_name; trigger_schema trigger_name event_object_table -mtr gs_insert global_suppressions -mtr ts_insert test_suppressions Insert into t1 values ('Insert no trigger 3.5.4.1'); Select * from t1 order by f1; f1 @@ -152,12 +152,8 @@ Select * from t1; f1 Trigger 3.5.4.4 Drop database db_drop4; -Show databases; -Database -information_schema -mtr -mysql -test +Show databases like 'db_drop4'; +Database (db_drop4) select trigger_schema, trigger_name, event_object_table from information_schema.triggers where information_schema.triggers.trigger_name='trg4'; diff --git a/mysql-test/suite/funcs_1/r/ndb_storedproc_08.result b/mysql-test/suite/funcs_1/r/ndb_storedproc_08.result index 7bffd77d9c6..2e504af6ed4 100644 --- a/mysql-test/suite/funcs_1/r/ndb_storedproc_08.result +++ b/mysql-test/suite/funcs_1/r/ndb_storedproc_08.result @@ -103,7 +103,7 @@ END// ... now check what is stored: ----------------------------- -SELECT * FROM information_schema.routines; +SELECT * FROM information_schema.routines where routine_schema = 'db_storedproc'; SPECIFIC_NAME fn_1 ROUTINE_CATALOG NULL ROUTINE_SCHEMA db_storedproc @@ -208,75 +208,6 @@ DEFINER root@localhost CHARACTER_SET_CLIENT latin1 COLLATION_CONNECTION latin1_swedish_ci DATABASE_COLLATION latin1_swedish_ci -SPECIFIC_NAME add_suppression -ROUTINE_CATALOG NULL -ROUTINE_SCHEMA mtr -ROUTINE_NAME add_suppression -ROUTINE_TYPE PROCEDURE -DTD_IDENTIFIER NULL -ROUTINE_BODY SQL -ROUTINE_DEFINITION BEGIN INSERT INTO test_suppressions (pattern) VALUES (pattern); END -EXTERNAL_NAME NULL -EXTERNAL_LANGUAGE NULL -PARAMETER_STYLE SQL -IS_DETERMINISTIC NO -SQL_DATA_ACCESS CONTAINS SQL -SQL_PATH NULL -SECURITY_TYPE DEFINER -CREATED <modified> -LAST_ALTERED <created> -SQL_MODE -ROUTINE_COMMENT -DEFINER root@localhost -CHARACTER_SET_CLIENT latin1 -COLLATION_CONNECTION latin1_swedish_ci -DATABASE_COLLATION latin1_swedish_ci -SPECIFIC_NAME check_testcase -ROUTINE_CATALOG NULL -ROUTINE_SCHEMA mtr -ROUTINE_NAME check_testcase -ROUTINE_TYPE PROCEDURE -DTD_IDENTIFIER NULL -ROUTINE_BODY SQL -ROUTINE_DEFINITION BEGIN SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE variable_name != 'timestamp' ORDER BY VARIABLE_NAME; SELECT * FROM INFORMATION_SCHEMA.SCHEMATA; SELECT table_name AS tables_in_test FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='test'; SELECT CONCAT(table_schema, '.', table_name) AS tables_in_mysql FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='mysql' AND table_name != 'ndb_apply_status' ORDER BY tables_in_mysql; SELECT CONCAT(table_schema, '.', table_name) AS columns_in_mysql, column_name, ordinal_position, column_default, is_nullable, data_type, character_maximum_length, character_octet_length, numeric_precision, numeric_scale, character_set_name, collation_name, column_type, column_key, extra, column_comment FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema='mysql' AND table_name != 'ndb_apply_status' ORDER BY columns_in_mysql; checksum table mysql.columns_priv, mysql.db, mysql.func, mysql.help_category, mysql.help_keyword, mysql.help_relation, mysql.host, mysql.proc, mysql.procs_priv, mysql.tables_priv, mysql.time_zone, mysql.time_zone_leap_second, mysql.time_zone_name, mysql.time_zone_transition, mysql.time_zone_transition_type, mysql.user; END -EXTERNAL_NAME NULL -EXTERNAL_LANGUAGE NULL -PARAMETER_STYLE SQL -IS_DETERMINISTIC NO -SQL_DATA_ACCESS CONTAINS SQL -SQL_PATH NULL -SECURITY_TYPE DEFINER -CREATED <modified> -LAST_ALTERED <created> -SQL_MODE -ROUTINE_COMMENT -DEFINER root@localhost -CHARACTER_SET_CLIENT latin1 -COLLATION_CONNECTION latin1_swedish_ci -DATABASE_COLLATION latin1_swedish_ci -SPECIFIC_NAME check_warnings -ROUTINE_CATALOG NULL -ROUTINE_SCHEMA mtr -ROUTINE_NAME check_warnings -ROUTINE_TYPE PROCEDURE -DTD_IDENTIFIER NULL -ROUTINE_BODY SQL -ROUTINE_DEFINITION BEGIN DECLARE `pos` bigint unsigned; SET SQL_LOG_BIN=0; UPDATE error_log el, global_suppressions gs SET suspicious=0 WHERE el.suspicious=1 AND el.line REGEXP gs.pattern; UPDATE error_log el, test_suppressions ts SET suspicious=0 WHERE el.suspicious=1 AND el.line REGEXP ts.pattern; SELECT COUNT(*) INTO @num_warnings FROM error_log WHERE suspicious=1; IF @num_warnings > 0 THEN SELECT file_name, line FROM error_log WHERE suspicious=1; SELECT 2 INTO result; ELSE SELECT 0 INTO RESULT; END IF; TRUNCATE test_suppressions; DROP TABLE error_log; END -EXTERNAL_NAME NULL -EXTERNAL_LANGUAGE NULL -PARAMETER_STYLE SQL -IS_DETERMINISTIC NO -SQL_DATA_ACCESS CONTAINS SQL -SQL_PATH NULL -SECURITY_TYPE DEFINER -CREATED <modified> -LAST_ALTERED <created> -SQL_MODE -ROUTINE_COMMENT -DEFINER root@localhost -CHARACTER_SET_CLIENT latin1 -COLLATION_CONNECTION latin1_swedish_ci -DATABASE_COLLATION latin1_swedish_ci SHOW CREATE FUNCTION fn_1; Function fn_1 sql_mode @@ -390,7 +321,7 @@ ALTER FUNCTION fn_2 MODIFIES SQL DATA; ... now check what is stored: ----------------------------- -SELECT * FROM information_schema.routines; +SELECT * FROM information_schema.routines where routine_schema = 'db_storedproc'; SPECIFIC_NAME fn_1 ROUTINE_CATALOG NULL ROUTINE_SCHEMA db_storedproc @@ -495,75 +426,6 @@ DEFINER root@localhost CHARACTER_SET_CLIENT latin1 COLLATION_CONNECTION latin1_swedish_ci DATABASE_COLLATION latin1_swedish_ci -SPECIFIC_NAME add_suppression -ROUTINE_CATALOG NULL -ROUTINE_SCHEMA mtr -ROUTINE_NAME add_suppression -ROUTINE_TYPE PROCEDURE -DTD_IDENTIFIER NULL -ROUTINE_BODY SQL -ROUTINE_DEFINITION BEGIN INSERT INTO test_suppressions (pattern) VALUES (pattern); END -EXTERNAL_NAME NULL -EXTERNAL_LANGUAGE NULL -PARAMETER_STYLE SQL -IS_DETERMINISTIC NO -SQL_DATA_ACCESS CONTAINS SQL -SQL_PATH NULL -SECURITY_TYPE DEFINER -CREATED <modified> -LAST_ALTERED <created> -SQL_MODE -ROUTINE_COMMENT -DEFINER root@localhost -CHARACTER_SET_CLIENT latin1 -COLLATION_CONNECTION latin1_swedish_ci -DATABASE_COLLATION latin1_swedish_ci -SPECIFIC_NAME check_testcase -ROUTINE_CATALOG NULL -ROUTINE_SCHEMA mtr -ROUTINE_NAME check_testcase -ROUTINE_TYPE PROCEDURE -DTD_IDENTIFIER NULL -ROUTINE_BODY SQL -ROUTINE_DEFINITION BEGIN SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE variable_name != 'timestamp' ORDER BY VARIABLE_NAME; SELECT * FROM INFORMATION_SCHEMA.SCHEMATA; SELECT table_name AS tables_in_test FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='test'; SELECT CONCAT(table_schema, '.', table_name) AS tables_in_mysql FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='mysql' AND table_name != 'ndb_apply_status' ORDER BY tables_in_mysql; SELECT CONCAT(table_schema, '.', table_name) AS columns_in_mysql, column_name, ordinal_position, column_default, is_nullable, data_type, character_maximum_length, character_octet_length, numeric_precision, numeric_scale, character_set_name, collation_name, column_type, column_key, extra, column_comment FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema='mysql' AND table_name != 'ndb_apply_status' ORDER BY columns_in_mysql; checksum table mysql.columns_priv, mysql.db, mysql.func, mysql.help_category, mysql.help_keyword, mysql.help_relation, mysql.host, mysql.proc, mysql.procs_priv, mysql.tables_priv, mysql.time_zone, mysql.time_zone_leap_second, mysql.time_zone_name, mysql.time_zone_transition, mysql.time_zone_transition_type, mysql.user; END -EXTERNAL_NAME NULL -EXTERNAL_LANGUAGE NULL -PARAMETER_STYLE SQL -IS_DETERMINISTIC NO -SQL_DATA_ACCESS CONTAINS SQL -SQL_PATH NULL -SECURITY_TYPE DEFINER -CREATED <modified> -LAST_ALTERED <created> -SQL_MODE -ROUTINE_COMMENT -DEFINER root@localhost -CHARACTER_SET_CLIENT latin1 -COLLATION_CONNECTION latin1_swedish_ci -DATABASE_COLLATION latin1_swedish_ci -SPECIFIC_NAME check_warnings -ROUTINE_CATALOG NULL -ROUTINE_SCHEMA mtr -ROUTINE_NAME check_warnings -ROUTINE_TYPE PROCEDURE -DTD_IDENTIFIER NULL -ROUTINE_BODY SQL -ROUTINE_DEFINITION BEGIN DECLARE `pos` bigint unsigned; SET SQL_LOG_BIN=0; UPDATE error_log el, global_suppressions gs SET suspicious=0 WHERE el.suspicious=1 AND el.line REGEXP gs.pattern; UPDATE error_log el, test_suppressions ts SET suspicious=0 WHERE el.suspicious=1 AND el.line REGEXP ts.pattern; SELECT COUNT(*) INTO @num_warnings FROM error_log WHERE suspicious=1; IF @num_warnings > 0 THEN SELECT file_name, line FROM error_log WHERE suspicious=1; SELECT 2 INTO result; ELSE SELECT 0 INTO RESULT; END IF; TRUNCATE test_suppressions; DROP TABLE error_log; END -EXTERNAL_NAME NULL -EXTERNAL_LANGUAGE NULL -PARAMETER_STYLE SQL -IS_DETERMINISTIC NO -SQL_DATA_ACCESS CONTAINS SQL -SQL_PATH NULL -SECURITY_TYPE DEFINER -CREATED <modified> -LAST_ALTERED <created> -SQL_MODE -ROUTINE_COMMENT -DEFINER root@localhost -CHARACTER_SET_CLIENT latin1 -COLLATION_CONNECTION latin1_swedish_ci -DATABASE_COLLATION latin1_swedish_ci SHOW CREATE FUNCTION fn_1; Function fn_1 sql_mode @@ -670,7 +532,7 @@ ALTER FUNCTION fn_2 CONTAINS SQL; ... now check what is stored: ----------------------------- -SELECT * FROM information_schema.routines; +SELECT * FROM information_schema.routines where routine_schema = 'db_storedproc'; SPECIFIC_NAME fn_1 ROUTINE_CATALOG NULL ROUTINE_SCHEMA db_storedproc @@ -775,75 +637,6 @@ DEFINER root@localhost CHARACTER_SET_CLIENT latin1 COLLATION_CONNECTION latin1_swedish_ci DATABASE_COLLATION latin1_swedish_ci -SPECIFIC_NAME add_suppression -ROUTINE_CATALOG NULL -ROUTINE_SCHEMA mtr -ROUTINE_NAME add_suppression -ROUTINE_TYPE PROCEDURE -DTD_IDENTIFIER NULL -ROUTINE_BODY SQL -ROUTINE_DEFINITION BEGIN INSERT INTO test_suppressions (pattern) VALUES (pattern); END -EXTERNAL_NAME NULL -EXTERNAL_LANGUAGE NULL -PARAMETER_STYLE SQL -IS_DETERMINISTIC NO -SQL_DATA_ACCESS CONTAINS SQL -SQL_PATH NULL -SECURITY_TYPE DEFINER -CREATED <modified> -LAST_ALTERED <created> -SQL_MODE -ROUTINE_COMMENT -DEFINER root@localhost -CHARACTER_SET_CLIENT latin1 -COLLATION_CONNECTION latin1_swedish_ci -DATABASE_COLLATION latin1_swedish_ci -SPECIFIC_NAME check_testcase -ROUTINE_CATALOG NULL -ROUTINE_SCHEMA mtr -ROUTINE_NAME check_testcase -ROUTINE_TYPE PROCEDURE -DTD_IDENTIFIER NULL -ROUTINE_BODY SQL -ROUTINE_DEFINITION BEGIN SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE variable_name != 'timestamp' ORDER BY VARIABLE_NAME; SELECT * FROM INFORMATION_SCHEMA.SCHEMATA; SELECT table_name AS tables_in_test FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='test'; SELECT CONCAT(table_schema, '.', table_name) AS tables_in_mysql FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='mysql' AND table_name != 'ndb_apply_status' ORDER BY tables_in_mysql; SELECT CONCAT(table_schema, '.', table_name) AS columns_in_mysql, column_name, ordinal_position, column_default, is_nullable, data_type, character_maximum_length, character_octet_length, numeric_precision, numeric_scale, character_set_name, collation_name, column_type, column_key, extra, column_comment FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema='mysql' AND table_name != 'ndb_apply_status' ORDER BY columns_in_mysql; checksum table mysql.columns_priv, mysql.db, mysql.func, mysql.help_category, mysql.help_keyword, mysql.help_relation, mysql.host, mysql.proc, mysql.procs_priv, mysql.tables_priv, mysql.time_zone, mysql.time_zone_leap_second, mysql.time_zone_name, mysql.time_zone_transition, mysql.time_zone_transition_type, mysql.user; END -EXTERNAL_NAME NULL -EXTERNAL_LANGUAGE NULL -PARAMETER_STYLE SQL -IS_DETERMINISTIC NO -SQL_DATA_ACCESS CONTAINS SQL -SQL_PATH NULL -SECURITY_TYPE DEFINER -CREATED <modified> -LAST_ALTERED <created> -SQL_MODE -ROUTINE_COMMENT -DEFINER root@localhost -CHARACTER_SET_CLIENT latin1 -COLLATION_CONNECTION latin1_swedish_ci -DATABASE_COLLATION latin1_swedish_ci -SPECIFIC_NAME check_warnings -ROUTINE_CATALOG NULL -ROUTINE_SCHEMA mtr -ROUTINE_NAME check_warnings -ROUTINE_TYPE PROCEDURE -DTD_IDENTIFIER NULL -ROUTINE_BODY SQL -ROUTINE_DEFINITION BEGIN DECLARE `pos` bigint unsigned; SET SQL_LOG_BIN=0; UPDATE error_log el, global_suppressions gs SET suspicious=0 WHERE el.suspicious=1 AND el.line REGEXP gs.pattern; UPDATE error_log el, test_suppressions ts SET suspicious=0 WHERE el.suspicious=1 AND el.line REGEXP ts.pattern; SELECT COUNT(*) INTO @num_warnings FROM error_log WHERE suspicious=1; IF @num_warnings > 0 THEN SELECT file_name, line FROM error_log WHERE suspicious=1; SELECT 2 INTO result; ELSE SELECT 0 INTO RESULT; END IF; TRUNCATE test_suppressions; DROP TABLE error_log; END -EXTERNAL_NAME NULL -EXTERNAL_LANGUAGE NULL -PARAMETER_STYLE SQL -IS_DETERMINISTIC NO -SQL_DATA_ACCESS CONTAINS SQL -SQL_PATH NULL -SECURITY_TYPE DEFINER -CREATED <modified> -LAST_ALTERED <created> -SQL_MODE -ROUTINE_COMMENT -DEFINER root@localhost -CHARACTER_SET_CLIENT latin1 -COLLATION_CONNECTION latin1_swedish_ci -DATABASE_COLLATION latin1_swedish_ci SHOW CREATE FUNCTION fn_1; Function fn_1 sql_mode diff --git a/mysql-test/suite/funcs_1/r/ndb_trig_0102.result b/mysql-test/suite/funcs_1/r/ndb_trig_0102.result index 86c2d2521ac..5ca24acf0bf 100644 --- a/mysql-test/suite/funcs_1/r/ndb_trig_0102.result +++ b/mysql-test/suite/funcs_1/r/ndb_trig_0102.result @@ -348,13 +348,13 @@ for each row set @test_var2='trig1_a'; create trigger trig_db2.trig2 before insert on trig_db2.t1 for each row set @test_var3='trig2'; select trigger_schema, trigger_name, event_object_table -from information_schema.triggers order by trigger_name; +from information_schema.triggers +where trigger_schema like 'trig_db%' + order by trigger_name; trigger_schema trigger_name event_object_table -mtr gs_insert global_suppressions trig_db1 trig1_a t1 trig_db1 trig1_b t1 trig_db2 trig2 t1 -mtr ts_insert test_suppressions set @test_var1= '', @test_var2= '', @test_var3= ''; insert into t1 (f1,f2) values ('insert to db1 t1 from db1',352); insert into trig_db2.t1 (f1,f2) values ('insert to db2 t1 from db1',352); diff --git a/mysql-test/suite/funcs_1/r/ndb_trig_0407.result b/mysql-test/suite/funcs_1/r/ndb_trig_0407.result index 62c8e0d06db..33e58f50ec1 100644 --- a/mysql-test/suite/funcs_1/r/ndb_trig_0407.result +++ b/mysql-test/suite/funcs_1/r/ndb_trig_0407.result @@ -90,10 +90,10 @@ f1 Trigger 3.5.4.1 drop trigger trg1; select trigger_schema, trigger_name, event_object_table -from information_schema.triggers order by trigger_name; +from information_schema.triggers +where trigger_schema = 'db_drop' + order by trigger_name; trigger_schema trigger_name event_object_table -mtr gs_insert global_suppressions -mtr ts_insert test_suppressions Insert into t1 values ('Insert no trigger 3.5.4.1'); Select * from t1 order by f1; f1 @@ -151,12 +151,8 @@ Select * from t1; f1 Trigger 3.5.4.4 Drop database db_drop4; -Show databases; -Database -information_schema -mtr -mysql -test +Show databases like 'db_drop4'; +Database (db_drop4) select trigger_schema, trigger_name, event_object_table from information_schema.triggers where information_schema.triggers.trigger_name='trg4'; diff --git a/mysql-test/suite/funcs_1/r/storedproc.result b/mysql-test/suite/funcs_1/r/storedproc.result index fd5b090e6fb..7e21ddf1544 100644 --- a/mysql-test/suite/funcs_1/r/storedproc.result +++ b/mysql-test/suite/funcs_1/r/storedproc.result @@ -92,11 +92,8 @@ END// ERROR 42000: Identifier name 'sp1_thisisaveryverylongname234872934_thisisaveryverylongnameabcde' is too long CALL sp1_thisisaveryverylongname234872934_thisisaveryverylongnameabcde( 'abc' ); ERROR 42000: Identifier name 'sp1_thisisaveryverylongname234872934_thisisaveryverylongnameabcde' is too long -SHOW PROCEDURE STATUS; +SHOW PROCEDURE STATUS WHERE db = 'db_storedproc'; Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation -mtr add_suppression PROCEDURE root@localhost <modified> <created> DEFINER latin1 latin1_swedish_ci latin1_swedish_ci -mtr check_testcase PROCEDURE root@localhost <modified> <created> DEFINER latin1 latin1_swedish_ci latin1_swedish_ci -mtr check_warnings PROCEDURE root@localhost <modified> <created> DEFINER latin1 latin1_swedish_ci latin1_swedish_ci DROP PROCEDURE IF EXISTS sp1; CREATE PROCEDURE sp1( f1 BINARY ) LANGUAGE SQL DETERMINISTIC SQL SECURITY INVOKER COMMENT 'this is simple' @@ -109,12 +106,9 @@ CALL sp1( 34 ); 3 Warnings: Warning 1265 Data truncated for column 'f1' at row 1 -SHOW PROCEDURE STATUS; +SHOW PROCEDURE STATUS WHERE db = 'db_storedproc'; Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation db_storedproc sp1 PROCEDURE root@localhost <modified> <created> INVOKER this is simple latin1 latin1_swedish_ci latin1_swedish_ci -mtr add_suppression PROCEDURE root@localhost <modified> <created> DEFINER latin1 latin1_swedish_ci latin1_swedish_ci -mtr check_testcase PROCEDURE root@localhost <modified> <created> DEFINER latin1 latin1_swedish_ci latin1_swedish_ci -mtr check_warnings PROCEDURE root@localhost <modified> <created> DEFINER latin1 latin1_swedish_ci latin1_swedish_ci DROP PROCEDURE IF EXISTS sp1; CREATE PROCEDURE sp1( f1 BLOB ) LANGUAGE SQL NOT DETERMINISTIC SQL SECURITY INVOKER COMMENT 'this is simple' @@ -125,12 +119,9 @@ END// CALL sp1( 34 ); @v1 34 -SHOW PROCEDURE STATUS; +SHOW PROCEDURE STATUS WHERE db = 'db_storedproc'; Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation db_storedproc sp1 PROCEDURE root@localhost <modified> <created> INVOKER this is simple latin1 latin1_swedish_ci latin1_swedish_ci -mtr add_suppression PROCEDURE root@localhost <modified> <created> DEFINER latin1 latin1_swedish_ci latin1_swedish_ci -mtr check_testcase PROCEDURE root@localhost <modified> <created> DEFINER latin1 latin1_swedish_ci latin1_swedish_ci -mtr check_warnings PROCEDURE root@localhost <modified> <created> DEFINER latin1 latin1_swedish_ci latin1_swedish_ci DROP PROCEDURE IF EXISTS sp1; CREATE PROCEDURE sp1( f1 INT ) LANGUAGE SQL NOT DETERMINISTIC SQL SECURITY INVOKER COMMENT 'this is simple' @@ -141,12 +132,9 @@ END// CALL sp1( 34 ); @v1 34 -SHOW PROCEDURE STATUS; +SHOW PROCEDURE STATUS WHERE db = 'db_storedproc'; Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation db_storedproc sp1 PROCEDURE root@localhost <modified> <created> INVOKER this is simple latin1 latin1_swedish_ci latin1_swedish_ci -mtr add_suppression PROCEDURE root@localhost <modified> <created> DEFINER latin1 latin1_swedish_ci latin1_swedish_ci -mtr check_testcase PROCEDURE root@localhost <modified> <created> DEFINER latin1 latin1_swedish_ci latin1_swedish_ci -mtr check_warnings PROCEDURE root@localhost <modified> <created> DEFINER latin1 latin1_swedish_ci latin1_swedish_ci DROP PROCEDURE IF EXISTS sp1; CREATE PROCEDURE sp1( f1 DECIMAL(256, 30) ) LANGUAGE SQL NOT DETERMINISTIC SQL SECURITY INVOKER COMMENT 'this is simple' @@ -185,13 +173,10 @@ LANGUAGE SQL NOT DETERMINISTIC SQL SECURITY INVOKER COMMENT 'this is simple' BEGIN RETURN f1; END// -SHOW PROCEDURE STATUS; +SHOW PROCEDURE STATUS WHERE db = 'db_storedproc'; Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation db_storedproc sproc_1 PROCEDURE root@localhost <modified> <created> INVOKER this is simple latin1 latin1_swedish_ci latin1_swedish_ci -mtr add_suppression PROCEDURE root@localhost <modified> <created> DEFINER latin1 latin1_swedish_ci latin1_swedish_ci -mtr check_testcase PROCEDURE root@localhost <modified> <created> DEFINER latin1 latin1_swedish_ci latin1_swedish_ci -mtr check_warnings PROCEDURE root@localhost <modified> <created> DEFINER latin1 latin1_swedish_ci latin1_swedish_ci -SHOW FUNCTION STATUS; +SHOW FUNCTION STATUS WHERE db = 'db_storedproc'; Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation db_storedproc func_1 FUNCTION root@localhost <modified> <created> INVOKER this is simple latin1 latin1_swedish_ci latin1_swedish_ci UPDATE t1_aux SET f1 = NULL; @@ -1431,12 +1416,9 @@ f1 value1 Warnings: Note 1291 Column '' has duplicated value 'value1' in ENUM -SHOW PROCEDURE STATUS; +SHOW PROCEDURE STATUS WHERE db = 'db_storedproc'; Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation db_storedproc sp1 PROCEDURE root@localhost <modified> <created> INVOKER this is simple latin1 latin1_swedish_ci latin1_swedish_ci -mtr add_suppression PROCEDURE root@localhost <modified> <created> DEFINER latin1 latin1_swedish_ci latin1_swedish_ci -mtr check_testcase PROCEDURE root@localhost <modified> <created> DEFINER latin1 latin1_swedish_ci latin1_swedish_ci -mtr check_warnings PROCEDURE root@localhost <modified> <created> DEFINER latin1 latin1_swedish_ci latin1_swedish_ci DROP PROCEDURE IF EXISTS sp1; CREATE PROCEDURE sp1( f1 SET("value1", "value1") ) LANGUAGE SQL NOT DETERMINISTIC SQL SECURITY INVOKER COMMENT 'this is simple' @@ -1451,12 +1433,9 @@ value1 Warnings: Note 1291 Column '' has duplicated value 'value1' in SET Warning 1265 Data truncated for column 'f1' at row 1 -SHOW PROCEDURE STATUS; +SHOW PROCEDURE STATUS WHERE db = 'db_storedproc'; Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation db_storedproc sp1 PROCEDURE root@localhost <modified> <created> INVOKER this is simple latin1 latin1_swedish_ci latin1_swedish_ci -mtr add_suppression PROCEDURE root@localhost <modified> <created> DEFINER latin1 latin1_swedish_ci latin1_swedish_ci -mtr check_testcase PROCEDURE root@localhost <modified> <created> DEFINER latin1 latin1_swedish_ci latin1_swedish_ci -mtr check_warnings PROCEDURE root@localhost <modified> <created> DEFINER latin1 latin1_swedish_ci latin1_swedish_ci DROP PROCEDURE IF EXISTS sp1; CREATE PROCEDURE sp1( f1 ENUM("value1", "value1") ) LANGUAGE SQL NOT DETERMINISTIC SQL SECURITY INVOKER COMMENT 'this is simple' @@ -1470,12 +1449,9 @@ f1 value1 Warnings: Note 1291 Column '' has duplicated value 'value1' in ENUM -SHOW PROCEDURE STATUS; +SHOW PROCEDURE STATUS WHERE db = 'db_storedproc'; Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation db_storedproc sp1 PROCEDURE root@localhost <modified> <created> INVOKER this is simple latin1 latin1_swedish_ci latin1_swedish_ci -mtr add_suppression PROCEDURE root@localhost <modified> <created> DEFINER latin1 latin1_swedish_ci latin1_swedish_ci -mtr check_testcase PROCEDURE root@localhost <modified> <created> DEFINER latin1 latin1_swedish_ci latin1_swedish_ci -mtr check_warnings PROCEDURE root@localhost <modified> <created> DEFINER latin1 latin1_swedish_ci latin1_swedish_ci DROP PROCEDURE IF EXISTS sp1; CREATE PROCEDURE sp1( f1 TEXT ) LANGUAGE SQL SELECT f1; CALL sp1( 'abc' ); diff --git a/mysql-test/suite/funcs_1/storedproc/storedproc_08_show.inc b/mysql-test/suite/funcs_1/storedproc/storedproc_08_show.inc index 962d9242675..d6fd397561c 100644 --- a/mysql-test/suite/funcs_1/storedproc/storedproc_08_show.inc +++ b/mysql-test/suite/funcs_1/storedproc/storedproc_08_show.inc @@ -2,15 +2,14 @@ # # used from .../storedproc_08.inc to show all created / altered routines -let $message= ... now check what is stored:; ---source include/show_msg.inc +--echo +--echo ... now check what is stored: +--echo ----------------------------- --vertical_results -#--replace_column 16 "YYYY-MM-DD hh:mm:ss" 17 "YYYY-MM-DD hh:mm:ss" - --replace_column 16 <modified> 17 <created> -SELECT * FROM information_schema.routines; +SELECT * FROM information_schema.routines where routine_schema = 'db_storedproc'; SHOW CREATE FUNCTION fn_1; diff --git a/mysql-test/suite/funcs_1/t/storedproc.test b/mysql-test/suite/funcs_1/t/storedproc.test index cc45101fbed..6877b751ed2 100644 --- a/mysql-test/suite/funcs_1/t/storedproc.test +++ b/mysql-test/suite/funcs_1/t/storedproc.test @@ -72,7 +72,7 @@ delimiter ;// CALL sp1_thisisaveryverylongname234872934_thisisaveryverylongnameabcde( 'abc' ); --replace_column 5 <modified> 6 <created> -SHOW PROCEDURE STATUS; +SHOW PROCEDURE STATUS WHERE db = 'db_storedproc'; --disable_warnings DROP PROCEDURE IF EXISTS sp1; @@ -90,7 +90,7 @@ delimiter ;// CALL sp1( 34 ); --replace_column 5 <modified> 6 <created> -SHOW PROCEDURE STATUS; +SHOW PROCEDURE STATUS WHERE db = 'db_storedproc'; --disable_warnings DROP PROCEDURE IF EXISTS sp1; @@ -108,7 +108,7 @@ delimiter ;// CALL sp1( 34 ); --replace_column 5 <modified> 6 <created> -SHOW PROCEDURE STATUS; +SHOW PROCEDURE STATUS WHERE db = 'db_storedproc'; --disable_warnings DROP PROCEDURE IF EXISTS sp1; @@ -126,7 +126,7 @@ delimiter ;// CALL sp1( 34 ); --replace_column 5 <modified> 6 <created> -SHOW PROCEDURE STATUS; +SHOW PROCEDURE STATUS WHERE db = 'db_storedproc'; --disable_warnings DROP PROCEDURE IF EXISTS sp1; @@ -191,9 +191,9 @@ BEGIN END// delimiter ;// --replace_column 5 <modified> 6 <created> -SHOW PROCEDURE STATUS; +SHOW PROCEDURE STATUS WHERE db = 'db_storedproc'; --replace_column 5 <modified> 6 <created> -SHOW FUNCTION STATUS; +SHOW FUNCTION STATUS WHERE db = 'db_storedproc'; let $test_value = 1.7976931348623157493578e+308; --source suite/funcs_1/storedproc/param_check.inc @@ -245,7 +245,7 @@ delimiter ;// CALL sp1( "value1" ); --replace_column 5 <modified> 6 <created> -SHOW PROCEDURE STATUS; +SHOW PROCEDURE STATUS WHERE db = 'db_storedproc'; --disable_warnings DROP PROCEDURE IF EXISTS sp1; @@ -262,7 +262,7 @@ delimiter ;// CALL sp1( "value1, value1" ); --replace_column 5 <modified> 6 <created> -SHOW PROCEDURE STATUS; +SHOW PROCEDURE STATUS WHERE db = 'db_storedproc'; --disable_warnings DROP PROCEDURE IF EXISTS sp1; @@ -279,7 +279,7 @@ delimiter ;// CALL sp1( "value1" ); --replace_column 5 <modified> 6 <created> -SHOW PROCEDURE STATUS; +SHOW PROCEDURE STATUS WHERE db = 'db_storedproc'; --disable_warnings DROP PROCEDURE IF EXISTS sp1; diff --git a/mysql-test/suite/funcs_1/triggers/triggers_0102.inc b/mysql-test/suite/funcs_1/triggers/triggers_0102.inc index 3afbc3f7aa9..e49bcead9f1 100644 --- a/mysql-test/suite/funcs_1/triggers/triggers_0102.inc +++ b/mysql-test/suite/funcs_1/triggers/triggers_0102.inc @@ -458,7 +458,9 @@ let $message= Testcase 3.5.2.1/2/3:; create trigger trig_db2.trig2 before insert on trig_db2.t1 for each row set @test_var3='trig2'; select trigger_schema, trigger_name, event_object_table - from information_schema.triggers order by trigger_name; + from information_schema.triggers + where trigger_schema like 'trig_db%' + order by trigger_name; set @test_var1= '', @test_var2= '', @test_var3= ''; insert into t1 (f1,f2) values ('insert to db1 t1 from db1',352); diff --git a/mysql-test/suite/funcs_1/triggers/triggers_0407.inc b/mysql-test/suite/funcs_1/triggers/triggers_0407.inc index a5e3c180a71..af45017ae6a 100644 --- a/mysql-test/suite/funcs_1/triggers/triggers_0407.inc +++ b/mysql-test/suite/funcs_1/triggers/triggers_0407.inc @@ -60,7 +60,9 @@ let $message= Testcase 3.5.4.1:; connection con1_super; drop trigger trg1; select trigger_schema, trigger_name, event_object_table - from information_schema.triggers order by trigger_name; + from information_schema.triggers + where trigger_schema = 'db_drop' + order by trigger_name; connection con1_general; Insert into t1 values ('Insert no trigger 3.5.4.1'); Select * from t1 order by f1; @@ -160,7 +162,7 @@ let $message= Testcase 3.5.4.4:; Select * from t1; connection con1_super; Drop database db_drop4; - Show databases; + Show databases like 'db_drop4'; select trigger_schema, trigger_name, event_object_table from information_schema.triggers where information_schema.triggers.trigger_name='trg4'; |