diff options
Diffstat (limited to 'mysql-test')
32 files changed, 897 insertions, 181 deletions
diff --git a/mysql-test/r/grant.result b/mysql-test/r/grant.result index 25fe976b7ed..6c769e60780 100644 --- a/mysql-test/r/grant.result +++ b/mysql-test/r/grant.result @@ -58,6 +58,7 @@ authentication_string password_expired N is_role N default_role +max_statement_time 0.000000 show grants for mysqltest_1@localhost; Grants for mysqltest_1@localhost GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' REQUIRE CIPHER 'EDH-RSA-DES-CBC3-SHA' @@ -85,7 +86,7 @@ delete from mysql.user where user='mysqltest_1'; flush privileges; delete from mysql.user where user='mysqltest_1'; flush privileges; -grant usage on *.* to mysqltest_1@localhost with max_queries_per_hour 10; +grant usage on *.* to mysqltest_1@localhost with max_queries_per_hour 10 max_statement_time 60; select * from mysql.user where user="mysqltest_1"; Host localhost User mysqltest_1 @@ -132,10 +133,11 @@ authentication_string password_expired N is_role N default_role +max_statement_time 60.000000 show grants for mysqltest_1@localhost; Grants for mysqltest_1@localhost -GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' WITH MAX_QUERIES_PER_HOUR 10 -grant usage on *.* to mysqltest_1@localhost with max_updates_per_hour 20 max_connections_per_hour 30; +GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' WITH MAX_QUERIES_PER_HOUR 10 MAX_STATEMENT_TIME 60.000000 +grant usage on *.* to mysqltest_1@localhost with max_updates_per_hour 20 max_connections_per_hour 30 max_statement_time 0; select * from mysql.user where user="mysqltest_1"; Host localhost User mysqltest_1 @@ -182,6 +184,7 @@ authentication_string password_expired N is_role N default_role +max_statement_time 0.000000 show grants for mysqltest_1@localhost; Grants for mysqltest_1@localhost GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' WITH MAX_QUERIES_PER_HOUR 10 MAX_UPDATES_PER_HOUR 20 MAX_CONNECTIONS_PER_HOUR 30 diff --git a/mysql-test/r/max_statement_time.result b/mysql-test/r/max_statement_time.result new file mode 100644 index 00000000000..2681575daea --- /dev/null +++ b/mysql-test/r/max_statement_time.result @@ -0,0 +1,147 @@ + +# Test the MAX_STATEMENT_TIME option. + +SET @@MAX_STATEMENT_TIME=2; +select @@max_statement_time; +@@max_statement_time +2.000000 +SELECT SLEEP(1); +SLEEP(1) +0 +SELECT SLEEP(3); +SLEEP(3) +1 +SET @@MAX_STATEMENT_TIME=0; +SELECT SLEEP(1); +SLEEP(1) +0 +SHOW STATUS LIKE "max_statement_time_exceeded"; +Variable_name Value +Max_statement_time_exceeded 1 +CREATE TABLE t1 (a INT, b VARCHAR(300)) engine=myisam; +INSERT INTO t1 VALUES (1, 'string'); +SELECT 0; +0 +0 + +# Test the MAX_STATEMENT_TIME option with SF (should have no effect). + +CREATE PROCEDURE p1() +BEGIN +declare tmp int; +SET @@MAX_STATEMENT_TIME=0.0001; +SELECT COUNT(*) INTO tmp FROM t1 WHERE b LIKE '%z%'; +SET @@MAX_STATEMENT_TIME=0; +END| +CREATE PROCEDURE p2() +BEGIN +SET @@MAX_STATEMENT_TIME=5; +END| +SELECT @@MAX_STATEMENT_TIME; +@@MAX_STATEMENT_TIME +0.000000 +CALL p1(); +CALL p2(); +SELECT @@MAX_STATEMENT_TIME; +@@MAX_STATEMENT_TIME +5.000000 +SET @@MAX_STATEMENT_TIME=0; +DROP PROCEDURE p1; +DROP PROCEDURE p2; +DROP TABLE t1; + +# MAX_STATEMENT_TIME account resource + +GRANT USAGE ON *.* TO user1@localhost WITH MAX_STATEMENT_TIME 1.005; +# con1 +SELECT @@max_statement_time; +@@max_statement_time +1.005000 +# restart and reconnect +set @global.userstat=1; +SELECT @@global.max_statement_time,@@session.max_statement_time; +@@global.max_statement_time @@session.max_statement_time +0.000000 1.005000 +select sleep(100); +sleep(100) +1 +SHOW STATUS LIKE "max_statement_time_exceeded"; +Variable_name Value +Max_statement_time_exceeded 1 +show grants for user1@localhost; +Grants for user1@localhost +GRANT USAGE ON *.* TO 'user1'@'localhost' WITH MAX_STATEMENT_TIME 1.005000 +set @global.userstat=0; +DROP USER user1@localhost; + +# MAX_STATEMENT_TIME status variables. + +flush status; +SET @@max_statement_time=0; +SELECT CONVERT(VARIABLE_VALUE, UNSIGNED) INTO @time_exceeded +FROM INFORMATION_SCHEMA.GLOBAL_STATUS +WHERE VARIABLE_NAME = 'max_statement_time_exceeded'; +SET @@max_statement_time=0.5; +SELECT SLEEP(2); +SLEEP(2) +1 +SHOW STATUS LIKE '%timeout%'; +Variable_name Value +Ssl_default_timeout 0 +Ssl_session_cache_timeouts 0 +SET @@max_statement_time=0; +# Ensure that the counters for: +# - statements that exceeded their maximum execution time +# are incremented. +SELECT 1 AS STATUS FROM INFORMATION_SCHEMA.GLOBAL_STATUS +WHERE VARIABLE_NAME = 'max_statement_time_exceeded' + AND CONVERT(VARIABLE_VALUE, UNSIGNED) > @time_exceeded; +STATUS +1 + +# Check that the appropriate error status is set. + +CREATE TABLE t1 (a INT) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1); +START TRANSACTION; +SELECT * FROM t1 FOR UPDATE; +a +1 +SET @@SESSION.max_statement_time = 0.5; +UPDATE t1 SET a = 2; +ERROR 70100: Query execution was interrupted (max_statement_time exceeded) +SHOW WARNINGS; +Level Code Message +Error 1967 Query execution was interrupted (max_statement_time exceeded) +ROLLBACK; +DROP TABLE t1; + +# Test interaction with lock waits. + +CREATE TABLE t1 (a INT) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1); +SET @@SESSION.max_statement_time= 0.5; +LOCK TABLES t1 WRITE; +SELECT @@SESSION.max_statement_time; +@@SESSION.max_statement_time +0.500000 +LOCK TABLES t1 READ; +ERROR 70100: Query execution was interrupted (max_statement_time exceeded) +UNLOCK TABLES; +BEGIN; +SELECT * FROM t1; +a +1 +ALTER TABLE t1 ADD COLUMN b INT; +ERROR 70100: Query execution was interrupted (max_statement_time exceeded) +ROLLBACK; +SELECT GET_LOCK('lock', 1); +GET_LOCK('lock', 1) +1 +SELECT GET_LOCK('lock', 1); +GET_LOCK('lock', 1) +NULL +SELECT RELEASE_LOCK('lock'); +RELEASE_LOCK('lock') +1 +DROP TABLE t1; diff --git a/mysql-test/r/mysqld--help.result b/mysql-test/r/mysqld--help.result index 5a2142c402c..80ba45be72a 100644 --- a/mysql-test/r/mysqld--help.result +++ b/mysql-test/r/mysqld--help.result @@ -422,6 +422,11 @@ The following options may be given as the first argument: value are used; the rest are ignored) --max-sp-recursion-depth[=#] Maximum stored procedure recursion depth + --max-statement-time=# + A SELECT query that have taken more than + max_statement_time seconds will be aborted. The argument + will be treated as a decimal value with microsecond + precision. A value of 0 (default) means no timeout --max-tmp-tables=# Maximum number of temporary tables a client can keep open at a time --max-user-connections=# @@ -1196,6 +1201,7 @@ max-relay-log-size 1073741824 max-seeks-for-key 18446744073709551615 max-sort-length 1024 max-sp-recursion-depth 0 +max-statement-time 0 max-tmp-tables 32 max-user-connections 0 max-write-lock-count 18446744073709551615 diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result index 7db51eadbe6..e30fa9a1966 100644 --- a/mysql-test/r/ps.result +++ b/mysql-test/r/ps.result @@ -1202,13 +1202,13 @@ SET @aux= "SELECT COUNT(*) prepare my_stmt from @aux; execute my_stmt; COUNT(*) -45 +46 execute my_stmt; COUNT(*) -45 +46 execute my_stmt; COUNT(*) -45 +46 deallocate prepare my_stmt; drop procedure if exists p1| drop table if exists t1| diff --git a/mysql-test/r/status_user.result b/mysql-test/r/status_user.result index 829c8abb634..766a00f6f78 100644 --- a/mysql-test/r/status_user.result +++ b/mysql-test/r/status_user.result @@ -25,6 +25,7 @@ DENIED_CONNECTIONS bigint(21) NO 0 LOST_CONNECTIONS bigint(21) NO 0 ACCESS_DENIED bigint(21) NO 0 EMPTY_QUERIES bigint(21) NO 0 +MAX_STATEMENT_TIME_EXCEEDED bigint(21) NO 0 show columns from information_schema.user_statistics; Field Type Null Key Default Extra USER varchar(128) NO @@ -50,6 +51,7 @@ DENIED_CONNECTIONS bigint(21) NO 0 LOST_CONNECTIONS bigint(21) NO 0 ACCESS_DENIED bigint(21) NO 0 EMPTY_QUERIES bigint(21) NO 0 +MAX_STATEMENT_TIME_EXCEEDED bigint(21) NO 0 show columns from information_schema.index_statistics; Field Type Null Key Default Extra TABLE_SCHEMA varchar(192) NO diff --git a/mysql-test/r/system_mysql_db.result b/mysql-test/r/system_mysql_db.result index 0204354d9e3..f195b0a607e 100644 --- a/mysql-test/r/system_mysql_db.result +++ b/mysql-test/r/system_mysql_db.result @@ -131,6 +131,7 @@ user CREATE TABLE `user` ( `password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `is_role` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `default_role` char(80) COLLATE utf8_bin NOT NULL DEFAULT '', + `max_statement_time` decimal(12,6) NOT NULL DEFAULT '0.000000', PRIMARY KEY (`Host`,`User`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges' show create table func; diff --git a/mysql-test/r/system_mysql_db_fix40123.result b/mysql-test/r/system_mysql_db_fix40123.result index 0204354d9e3..f195b0a607e 100644 --- a/mysql-test/r/system_mysql_db_fix40123.result +++ b/mysql-test/r/system_mysql_db_fix40123.result @@ -131,6 +131,7 @@ user CREATE TABLE `user` ( `password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `is_role` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `default_role` char(80) COLLATE utf8_bin NOT NULL DEFAULT '', + `max_statement_time` decimal(12,6) NOT NULL DEFAULT '0.000000', PRIMARY KEY (`Host`,`User`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges' show create table func; diff --git a/mysql-test/r/system_mysql_db_fix50030.result b/mysql-test/r/system_mysql_db_fix50030.result index 0204354d9e3..f195b0a607e 100644 --- a/mysql-test/r/system_mysql_db_fix50030.result +++ b/mysql-test/r/system_mysql_db_fix50030.result @@ -131,6 +131,7 @@ user CREATE TABLE `user` ( `password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `is_role` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `default_role` char(80) COLLATE utf8_bin NOT NULL DEFAULT '', + `max_statement_time` decimal(12,6) NOT NULL DEFAULT '0.000000', PRIMARY KEY (`Host`,`User`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges' show create table func; diff --git a/mysql-test/r/system_mysql_db_fix50117.result b/mysql-test/r/system_mysql_db_fix50117.result index 0204354d9e3..f195b0a607e 100644 --- a/mysql-test/r/system_mysql_db_fix50117.result +++ b/mysql-test/r/system_mysql_db_fix50117.result @@ -131,6 +131,7 @@ user CREATE TABLE `user` ( `password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `is_role` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `default_role` char(80) COLLATE utf8_bin NOT NULL DEFAULT '', + `max_statement_time` decimal(12,6) NOT NULL DEFAULT '0.000000', PRIMARY KEY (`Host`,`User`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges' show create table func; diff --git a/mysql-test/suite/funcs_1/r/is_columns_is.result b/mysql-test/suite/funcs_1/r/is_columns_is.result index 8f2f81a4616..c73eaa874c7 100644 --- a/mysql-test/suite/funcs_1/r/is_columns_is.result +++ b/mysql-test/suite/funcs_1/r/is_columns_is.result @@ -36,6 +36,7 @@ def information_schema CLIENT_STATISTICS CPU_TIME 6 0 NO double NULL NULL 21 NUL def information_schema CLIENT_STATISTICS DENIED_CONNECTIONS 20 0 NO bigint NULL NULL 19 0 NULL NULL NULL bigint(21) select def information_schema CLIENT_STATISTICS EMPTY_QUERIES 23 0 NO bigint NULL NULL 19 0 NULL NULL NULL bigint(21) select def information_schema CLIENT_STATISTICS LOST_CONNECTIONS 21 0 NO bigint NULL NULL 19 0 NULL NULL NULL bigint(21) select +def information_schema CLIENT_STATISTICS MAX_STATEMENT_TIME_EXCEEDED 24 0 NO bigint NULL NULL 19 0 NULL NULL NULL bigint(21) select def information_schema CLIENT_STATISTICS OTHER_COMMANDS 17 0 NO bigint NULL NULL 19 0 NULL NULL NULL bigint(21) select def information_schema CLIENT_STATISTICS ROLLBACK_TRANSACTIONS 19 0 NO bigint NULL NULL 19 0 NULL NULL NULL bigint(21) select def information_schema CLIENT_STATISTICS ROWS_DELETED 12 0 NO bigint NULL NULL 19 0 NULL NULL NULL bigint(21) select @@ -408,6 +409,7 @@ def information_schema USER_STATISTICS CPU_TIME 6 0 NO double NULL NULL 21 NULL def information_schema USER_STATISTICS DENIED_CONNECTIONS 20 0 NO bigint NULL NULL 19 0 NULL NULL NULL bigint(21) select def information_schema USER_STATISTICS EMPTY_QUERIES 23 0 NO bigint NULL NULL 19 0 NULL NULL NULL bigint(21) select def information_schema USER_STATISTICS LOST_CONNECTIONS 21 0 NO bigint NULL NULL 19 0 NULL NULL NULL bigint(21) select +def information_schema USER_STATISTICS MAX_STATEMENT_TIME_EXCEEDED 24 0 NO bigint NULL NULL 19 0 NULL NULL NULL bigint(21) select def information_schema USER_STATISTICS OTHER_COMMANDS 17 0 NO bigint NULL NULL 19 0 NULL NULL NULL bigint(21) select def information_schema USER_STATISTICS ROLLBACK_TRANSACTIONS 19 0 NO bigint NULL NULL 19 0 NULL NULL NULL bigint(21) select def information_schema USER_STATISTICS ROWS_DELETED 12 0 NO bigint NULL NULL 19 0 NULL NULL NULL bigint(21) select @@ -533,6 +535,7 @@ NULL information_schema CLIENT_STATISTICS DENIED_CONNECTIONS bigint NULL NULL NU NULL information_schema CLIENT_STATISTICS LOST_CONNECTIONS bigint NULL NULL NULL NULL bigint(21) NULL information_schema CLIENT_STATISTICS ACCESS_DENIED bigint NULL NULL NULL NULL bigint(21) NULL information_schema CLIENT_STATISTICS EMPTY_QUERIES bigint NULL NULL NULL NULL bigint(21) +NULL information_schema CLIENT_STATISTICS MAX_STATEMENT_TIME_EXCEEDED bigint NULL NULL NULL NULL bigint(21) 3.0000 information_schema COLLATIONS COLLATION_NAME varchar 32 96 utf8 utf8_general_ci varchar(32) 3.0000 information_schema COLLATIONS CHARACTER_SET_NAME varchar 32 96 utf8 utf8_general_ci varchar(32) NULL information_schema COLLATIONS ID bigint NULL NULL NULL NULL bigint(11) @@ -906,6 +909,7 @@ NULL information_schema USER_STATISTICS DENIED_CONNECTIONS bigint NULL NULL NULL NULL information_schema USER_STATISTICS LOST_CONNECTIONS bigint NULL NULL NULL NULL bigint(21) NULL information_schema USER_STATISTICS ACCESS_DENIED bigint NULL NULL NULL NULL bigint(21) NULL information_schema USER_STATISTICS EMPTY_QUERIES bigint NULL NULL NULL NULL bigint(21) +NULL information_schema USER_STATISTICS MAX_STATEMENT_TIME_EXCEEDED bigint NULL NULL NULL NULL bigint(21) 3.0000 information_schema VIEWS TABLE_CATALOG varchar 512 1536 utf8 utf8_general_ci varchar(512) 3.0000 information_schema VIEWS TABLE_SCHEMA varchar 64 192 utf8 utf8_general_ci varchar(64) 3.0000 information_schema VIEWS TABLE_NAME varchar 64 192 utf8 utf8_general_ci varchar(64) diff --git a/mysql-test/suite/funcs_1/r/is_columns_mysql.result b/mysql-test/suite/funcs_1/r/is_columns_mysql.result index d05c120bfa8..09adebfab1c 100644 --- a/mysql-test/suite/funcs_1/r/is_columns_mysql.result +++ b/mysql-test/suite/funcs_1/r/is_columns_mysql.result @@ -227,6 +227,7 @@ def mysql user is_role 44 N NO enum 1 3 NULL NULL NULL utf8 utf8_general_ci enum def mysql user Lock_tables_priv 21 N NO enum 1 3 NULL NULL NULL utf8 utf8_general_ci enum('N','Y') select,insert,update,references def mysql user max_connections 39 0 NO int NULL NULL 10 0 NULL NULL NULL int(11) unsigned select,insert,update,references def mysql user max_questions 37 0 NO int NULL NULL 10 0 NULL NULL NULL int(11) unsigned select,insert,update,references +def mysql user max_statement_time 46 0.000000 NO decimal NULL NULL 12 6 NULL NULL NULL decimal(12,6) select,insert,update,references def mysql user max_updates 38 0 NO int NULL NULL 10 0 NULL NULL NULL int(11) unsigned select,insert,update,references def mysql user max_user_connections 40 0 NO int NULL NULL 10 0 NULL NULL NULL int(11) select,insert,update,references def mysql user Password 3 NO char 41 41 NULL NULL NULL latin1 latin1_bin char(41) select,insert,update,references @@ -568,3 +569,4 @@ NULL mysql user max_user_connections int NULL NULL NULL NULL int(11) 3.0000 mysql user password_expired enum 1 3 utf8 utf8_general_ci enum('N','Y') 3.0000 mysql user is_role enum 1 3 utf8 utf8_general_ci enum('N','Y') 3.0000 mysql user default_role char 80 240 utf8 utf8_bin char(80) +NULL mysql user max_statement_time decimal NULL NULL NULL NULL decimal(12,6) diff --git a/mysql-test/suite/funcs_1/r/is_user_privileges.result b/mysql-test/suite/funcs_1/r/is_user_privileges.result index 5296a37c98d..dfbe50ad862 100644 --- a/mysql-test/suite/funcs_1/r/is_user_privileges.result +++ b/mysql-test/suite/funcs_1/r/is_user_privileges.result @@ -132,6 +132,7 @@ authentication_string password_expired N is_role N default_role +max_statement_time 0.000000 Host localhost User testuser2 Password @@ -177,6 +178,7 @@ authentication_string password_expired N is_role N default_role +max_statement_time 0.000000 Host localhost User testuser3 Password @@ -222,6 +224,7 @@ authentication_string password_expired N is_role N default_role +max_statement_time 0.000000 # # Add GRANT OPTION db_datadict.* to testuser1; GRANT UPDATE ON db_datadict.* TO 'testuser1'@'localhost' WITH GRANT OPTION; @@ -291,6 +294,7 @@ authentication_string password_expired N is_role N default_role +max_statement_time 0.000000 Host localhost User testuser2 Password @@ -336,6 +340,7 @@ authentication_string password_expired N is_role N default_role +max_statement_time 0.000000 Host localhost User testuser3 Password @@ -381,6 +386,7 @@ authentication_string password_expired N is_role N default_role +max_statement_time 0.000000 # Establish connection testuser1 (user=testuser1) SELECT * FROM information_schema.user_privileges WHERE grantee LIKE '''testuser%''' @@ -436,6 +442,7 @@ authentication_string password_expired N is_role N default_role +max_statement_time 0.000000 Host localhost User testuser2 Password @@ -481,6 +488,7 @@ authentication_string password_expired N is_role N default_role +max_statement_time 0.000000 Host localhost User testuser3 Password @@ -526,6 +534,7 @@ authentication_string password_expired N is_role N default_role +max_statement_time 0.000000 SHOW GRANTS; Grants for testuser1@localhost GRANT USAGE ON *.* TO 'testuser1'@'localhost' @@ -603,6 +612,7 @@ authentication_string password_expired N is_role N default_role +max_statement_time 0.000000 Host localhost User testuser2 Password @@ -648,6 +658,7 @@ authentication_string password_expired N is_role N default_role +max_statement_time 0.000000 Host localhost User testuser3 Password @@ -693,6 +704,7 @@ authentication_string password_expired N is_role N default_role +max_statement_time 0.000000 GRANT SELECT ON *.* TO 'testuser1'@'localhost' WITH GRANT OPTION; # # Here <SELECT YES> is shown correctly for testuser1; @@ -762,6 +774,7 @@ authentication_string password_expired N is_role N default_role +max_statement_time 0.000000 Host localhost User testuser2 Password @@ -807,6 +820,7 @@ authentication_string password_expired N is_role N default_role +max_statement_time 0.000000 Host localhost User testuser3 Password @@ -852,6 +866,7 @@ authentication_string password_expired N is_role N default_role +max_statement_time 0.000000 # Switch to connection testuser1 SELECT * FROM information_schema.user_privileges WHERE grantee LIKE '''testuser%''' @@ -907,6 +922,7 @@ authentication_string password_expired N is_role N default_role +max_statement_time 0.000000 Host localhost User testuser2 Password @@ -952,6 +968,7 @@ authentication_string password_expired N is_role N default_role +max_statement_time 0.000000 Host localhost User testuser3 Password @@ -997,6 +1014,7 @@ authentication_string password_expired N is_role N default_role +max_statement_time 0.000000 SHOW GRANTS; Grants for testuser1@localhost GRANT SELECT ON *.* TO 'testuser1'@'localhost' WITH GRANT OPTION @@ -1104,6 +1122,7 @@ authentication_string password_expired N is_role N default_role +max_statement_time 0.000000 Host localhost User testuser2 Password @@ -1149,6 +1168,7 @@ authentication_string password_expired N is_role N default_role +max_statement_time 0.000000 Host localhost User testuser3 Password @@ -1194,6 +1214,7 @@ authentication_string password_expired N is_role N default_role +max_statement_time 0.000000 # Switch to connection testuser1 SELECT * FROM information_schema.user_privileges WHERE grantee LIKE '''testuser%''' @@ -1296,6 +1317,7 @@ authentication_string password_expired N is_role N default_role +max_statement_time 0.000000 Host localhost User testuser2 Password @@ -1341,6 +1363,7 @@ authentication_string password_expired N is_role N default_role +max_statement_time 0.000000 Host localhost User testuser3 Password @@ -1386,6 +1409,7 @@ authentication_string password_expired N is_role N default_role +max_statement_time 0.000000 # Switch to connection testuser1 SELECT * FROM information_schema.user_privileges WHERE grantee LIKE '''testuser%''' @@ -1441,6 +1465,7 @@ authentication_string password_expired N is_role N default_role +max_statement_time 0.000000 Host localhost User testuser2 Password @@ -1486,6 +1511,7 @@ authentication_string password_expired N is_role N default_role +max_statement_time 0.000000 Host localhost User testuser3 Password @@ -1531,6 +1557,7 @@ authentication_string password_expired N is_role N default_role +max_statement_time 0.000000 SHOW GRANTS; Grants for testuser1@localhost GRANT USAGE ON *.* TO 'testuser1'@'localhost' @@ -1593,6 +1620,7 @@ authentication_string password_expired N is_role N default_role +max_statement_time 0.000000 Host localhost User testuser2 Password @@ -1638,6 +1666,7 @@ authentication_string password_expired N is_role N default_role +max_statement_time 0.000000 Host localhost User testuser3 Password @@ -1683,6 +1712,7 @@ authentication_string password_expired N is_role N default_role +max_statement_time 0.000000 SHOW GRANTS; Grants for testuser1@localhost GRANT USAGE ON *.* TO 'testuser1'@'localhost' @@ -1760,6 +1790,7 @@ authentication_string password_expired N is_role N default_role +max_statement_time 0.000000 Host localhost User testuser2 Password @@ -1805,6 +1836,7 @@ authentication_string password_expired N is_role N default_role +max_statement_time 0.000000 Host localhost User testuser3 Password @@ -1850,6 +1882,7 @@ authentication_string password_expired N is_role N default_role +max_statement_time 0.000000 # Switch to connection testuser1 SELECT * FROM information_schema.user_privileges WHERE grantee LIKE '''testuser%''' diff --git a/mysql-test/suite/perfschema/r/digest_table_full.result b/mysql-test/suite/perfschema/r/digest_table_full.result index 9c0efb7b1ca..4c7de82abc2 100644 --- a/mysql-test/suite/perfschema/r/digest_table_full.result +++ b/mysql-test/suite/perfschema/r/digest_table_full.result @@ -109,11 +109,11 @@ DROP TRIGGER trg; #################################### # QUERYING PS STATEMENT DIGEST #################################### -SELECT SCHEMA_NAME, DIGEST, DIGEST_TEXT, COUNT_STAR, SUM_ROWS_AFFECTED, SUM_WARNINGS, +SELECT SCHEMA_NAME, DIGEST_TEXT, COUNT_STAR, SUM_ROWS_AFFECTED, SUM_WARNINGS, SUM_ERRORS FROM performance_schema.events_statements_summary_by_digest; -SCHEMA_NAME DIGEST DIGEST_TEXT COUNT_STAR SUM_ROWS_AFFECTED SUM_WARNINGS SUM_ERRORS -NULL NULL NULL 55 32 1 2 -statements_digest 0e98ee6a98e296530ec59c12dbc08dfe TRUNCATE TABLE performance_schema . events_statements_summary_by_digest 1 0 0 0 +SCHEMA_NAME DIGEST_TEXT COUNT_STAR SUM_ROWS_AFFECTED SUM_WARNINGS SUM_ERRORS +NULL NULL 55 32 1 2 +statements_digest TRUNCATE TABLE performance_schema . events_statements_summary_by_digest 1 0 0 0 SHOW VARIABLES LIKE "performance_schema_digests_size"; Variable_name Value performance_schema_digests_size 2 diff --git a/mysql-test/suite/perfschema/r/rpl_gtid_func.result b/mysql-test/suite/perfschema/r/rpl_gtid_func.result index 98342bc4869..86c8347addd 100644 --- a/mysql-test/suite/perfschema/r/rpl_gtid_func.result +++ b/mysql-test/suite/perfschema/r/rpl_gtid_func.result @@ -33,11 +33,11 @@ TABLE information_schema % NO NO TABLE master foo YES YES TABLE mysql % NO NO TABLE performance_schema % NO NO -select digest, digest_text, count_star +select digest_text, count_star from performance_schema.events_statements_summary_by_digest where digest_text like "%in_%_digest%"; -digest digest_text count_star -e315485f9cbc06befb4e59970905a034 SELECT ? AS in_master_digest 1 +digest_text count_star +SELECT ? AS in_master_digest 1 insert into test.marker values (2); **** On Slave **** select * from test.marker; @@ -60,11 +60,11 @@ TABLE information_schema % NO NO TABLE mysql % NO NO TABLE performance_schema % NO NO TABLE slave foo YES YES -select digest, digest_text, count_star +select digest_text, count_star from performance_schema.events_statements_summary_by_digest where digest_text like "%in_%_digest%"; -digest digest_text count_star -bd2f53b41efcd037df41a3dd8bf3312a SELECT ? AS in_slave_digest 1 +digest_text count_star +SELECT ? AS in_slave_digest 1 **** On Master **** delete from performance_schema.setup_objects where object_schema='master'; diff --git a/mysql-test/suite/perfschema/r/start_server_no_digests.result b/mysql-test/suite/perfschema/r/start_server_no_digests.result index 4f6fa9bc5da..e4b389716a9 100644 --- a/mysql-test/suite/perfschema/r/start_server_no_digests.result +++ b/mysql-test/suite/perfschema/r/start_server_no_digests.result @@ -109,9 +109,9 @@ DROP TRIGGER trg; #################################### # QUERYING PS STATEMENT DIGEST #################################### -SELECT SCHEMA_NAME, DIGEST, DIGEST_TEXT, COUNT_STAR, SUM_ROWS_AFFECTED, SUM_WARNINGS, +SELECT SCHEMA_NAME, DIGEST_TEXT, COUNT_STAR, SUM_ROWS_AFFECTED, SUM_WARNINGS, SUM_ERRORS FROM performance_schema.events_statements_summary_by_digest; -SCHEMA_NAME DIGEST DIGEST_TEXT COUNT_STAR SUM_ROWS_AFFECTED SUM_WARNINGS SUM_ERRORS +SCHEMA_NAME DIGEST_TEXT COUNT_STAR SUM_ROWS_AFFECTED SUM_WARNINGS SUM_ERRORS SHOW VARIABLES LIKE "performance_schema_digests_size"; Variable_name Value performance_schema_digests_size 0 diff --git a/mysql-test/suite/perfschema/r/statement_digest.result b/mysql-test/suite/perfschema/r/statement_digest.result index 41cba435cb6..a7983e5baaa 100644 --- a/mysql-test/suite/perfschema/r/statement_digest.result +++ b/mysql-test/suite/perfschema/r/statement_digest.result @@ -109,46 +109,46 @@ DROP TRIGGER trg; #################################### # QUERYING PS STATEMENT DIGEST #################################### -SELECT SCHEMA_NAME, DIGEST, DIGEST_TEXT, COUNT_STAR, SUM_ROWS_AFFECTED, SUM_WARNINGS, +SELECT SCHEMA_NAME, DIGEST_TEXT, COUNT_STAR, SUM_ROWS_AFFECTED, SUM_WARNINGS, SUM_ERRORS FROM performance_schema.events_statements_summary_by_digest; -SCHEMA_NAME DIGEST DIGEST_TEXT COUNT_STAR SUM_ROWS_AFFECTED SUM_WARNINGS SUM_ERRORS -statements_digest 0e98ee6a98e296530ec59c12dbc08dfe TRUNCATE TABLE performance_schema . events_statements_summary_by_digest 1 0 0 0 -statements_digest 954f43425c3234acc8e194afd97e8a0a SELECT ? FROM t1 1 0 0 0 -statements_digest fc365a54bc19d746bd24c27aba46b990 SELECT ? FROM `t1` 1 0 0 0 -statements_digest 27ba28f6252e4ae0e9b14b36da536fbe SELECT ?, ... FROM t1 2 0 0 0 -statements_digest 81d03922612900032ec4b81934ab4841 SELECT ? FROM t2 1 0 0 0 -statements_digest adce8aec12b6b5046cd4bf55951014c7 SELECT ?, ... FROM t2 2 0 0 0 -statements_digest 59a1bd93c424b10802fe66bb6dcd94d2 INSERT INTO t1 VALUES (?) 1 1 0 0 -statements_digest 91b2da58b0eb49c35a38fbc49f5e491d INSERT INTO t2 VALUES (?) 1 1 0 0 -statements_digest 967114adbf91d8a4a99ec5e49e909ff4 INSERT INTO t3 VALUES (...) 4 4 0 0 -statements_digest 8f25e7a48487e0aa7377e816816bb658 INSERT INTO t4 VALUES (...) 1 1 0 0 -statements_digest 4e51253af793867fba66166de1f314f7 INSERT INTO t5 VALUES (...) 1 1 0 0 -statements_digest fa47b3109e117216cd10209690d28596 INSERT INTO t1 VALUES (?) /* , ... */ 2 7 0 0 -statements_digest 72409f84bc236e6fe9f2f7b4d727f2d3 INSERT INTO t3 VALUES (...) /* , ... */ 1 3 0 0 -statements_digest d40aaddb41ed794d65dd8273f0c75700 INSERT INTO t5 VALUES (...) /* , ... */ 1 3 0 0 -statements_digest 57a82b28388e52e99fc64339dd30edde INSERT INTO t1 VALUES ( NULL ) 1 1 0 0 -statements_digest 6a56b694106442474cb0e5fb7575c8b9 INSERT INTO t6 VALUES (...) 5 5 0 0 -statements_digest c9abf55e296c4317dbaf2d14ef907ad7 SELECT ? + ? 3 0 0 0 -statements_digest 156304a0851a3e3626b39fb3da841a82 SELECT ? 1 0 0 0 -statements_digest 3b085ab0d2063dfca1a39212e3ea1831 CREATE SCHEMA statements_digest_temp 2 2 0 0 -statements_digest 09f9fabef2feb9a54ba01455e5ae83b9 DROP SCHEMA statements_digest_temp 2 0 0 0 -statements_digest 7910a63ffd31cbcb742e15270c8958c8 SELECT ? FROM no_such_table 1 0 0 1 -statements_digest fa34540a438bc672478b1162505ee28c CREATE TABLE dup_table ( c CHARACTER (?) ) 2 0 0 1 -statements_digest 2c720f176bb7c8510ff8aca8921b9945 DROP TABLE dup_table 1 0 0 0 -statements_digest 0c7d9fd8c27ab067511da41ca3bcdff3 INSERT INTO t11 VALUES (?) 1 1 1 0 -statements_digest 81681ff345065ed72bcd1e9407ab85e4 SHOW WARNINGS 1 0 0 0 -statements_digest d766f5823ae5d8e4cf4602b8e7a3fb80 PREPARE stmt FROM ? 1 0 0 0 -statements_digest 3ab1e87eabd9688edf919754cce6348b EXECUTE stmt 2 0 0 0 -statements_digest 470094469d250b9f45cab45bf610efe8 DEALLOCATE PREPARE stmt 1 0 0 0 -statements_digest 1b4d25358e08b35ad54e49dfe5eaf3e4 CREATE PROCEDURE p1 ( ) BEGIN SELECT * FROM t12 ; END 1 0 0 0 -statements_digest 84554971243e91106214dcb8f4eaa89b CALL p1 ( ) 2 0 0 0 -statements_digest 77206e4bf30979c56752a7ed9150213a DROP PROCEDURE p1 1 0 0 0 -statements_digest 03b91dcdba6b0e29f7fb240ae4bcd97f CREATE FUNCTION `func` ( a INTEGER , b INTEGER ) RETURNS INTEGER (?) RETURN a + b 1 0 0 0 -statements_digest 72bc532f308f2dca62f5291df8c50e6f SELECT func (...) 2 0 0 0 -statements_digest 0b5a5297689c5036def6af8e8a8ce113 DROP FUNCTION func 1 0 0 0 -statements_digest d08331e42c67555ece50e46eef0f2b47 CREATE TRIGGER trg BEFORE INSERT ON t12 FOR EACH ROW SET @ ? := ? 1 0 0 0 -statements_digest 754a49a4de995c5a729e9ab52f135f59 INSERT INTO t12 VALUES (?) 2 2 0 0 -statements_digest 68df17752bca7c2c8ee2a6a19a0674e7 DROP TRIGGER trg 1 0 0 0 +SCHEMA_NAME DIGEST_TEXT COUNT_STAR SUM_ROWS_AFFECTED SUM_WARNINGS SUM_ERRORS +statements_digest TRUNCATE TABLE performance_schema . events_statements_summary_by_digest 1 0 0 0 +statements_digest SELECT ? FROM t1 1 0 0 0 +statements_digest SELECT ? FROM `t1` 1 0 0 0 +statements_digest SELECT ?, ... FROM t1 2 0 0 0 +statements_digest SELECT ? FROM t2 1 0 0 0 +statements_digest SELECT ?, ... FROM t2 2 0 0 0 +statements_digest INSERT INTO t1 VALUES (?) 1 1 0 0 +statements_digest INSERT INTO t2 VALUES (?) 1 1 0 0 +statements_digest INSERT INTO t3 VALUES (...) 4 4 0 0 +statements_digest INSERT INTO t4 VALUES (...) 1 1 0 0 +statements_digest INSERT INTO t5 VALUES (...) 1 1 0 0 +statements_digest INSERT INTO t1 VALUES (?) /* , ... */ 2 7 0 0 +statements_digest INSERT INTO t3 VALUES (...) /* , ... */ 1 3 0 0 +statements_digest INSERT INTO t5 VALUES (...) /* , ... */ 1 3 0 0 +statements_digest INSERT INTO t1 VALUES ( NULL ) 1 1 0 0 +statements_digest INSERT INTO t6 VALUES (...) 5 5 0 0 +statements_digest SELECT ? + ? 3 0 0 0 +statements_digest SELECT ? 1 0 0 0 +statements_digest CREATE SCHEMA statements_digest_temp 2 2 0 0 +statements_digest DROP SCHEMA statements_digest_temp 2 0 0 0 +statements_digest SELECT ? FROM no_such_table 1 0 0 1 +statements_digest CREATE TABLE dup_table ( c CHARACTER (?) ) 2 0 0 1 +statements_digest DROP TABLE dup_table 1 0 0 0 +statements_digest INSERT INTO t11 VALUES (?) 1 1 1 0 +statements_digest SHOW WARNINGS 1 0 0 0 +statements_digest PREPARE stmt FROM ? 1 0 0 0 +statements_digest EXECUTE stmt 2 0 0 0 +statements_digest DEALLOCATE PREPARE stmt 1 0 0 0 +statements_digest CREATE PROCEDURE p1 ( ) BEGIN SELECT * FROM t12 ; END 1 0 0 0 +statements_digest CALL p1 ( ) 2 0 0 0 +statements_digest DROP PROCEDURE p1 1 0 0 0 +statements_digest CREATE FUNCTION `func` ( a INTEGER , b INTEGER ) RETURNS INTEGER (?) RETURN a + b 1 0 0 0 +statements_digest SELECT func (...) 2 0 0 0 +statements_digest DROP FUNCTION func 1 0 0 0 +statements_digest CREATE TRIGGER trg BEFORE INSERT ON t12 FOR EACH ROW SET @ ? := ? 1 0 0 0 +statements_digest INSERT INTO t12 VALUES (?) 2 2 0 0 +statements_digest DROP TRIGGER trg 1 0 0 0 #################################### # CLEANUP #################################### diff --git a/mysql-test/suite/perfschema/r/statement_digest_consumers.result b/mysql-test/suite/perfschema/r/statement_digest_consumers.result index 21e62e13b19..8c5696bb139 100644 --- a/mysql-test/suite/perfschema/r/statement_digest_consumers.result +++ b/mysql-test/suite/perfschema/r/statement_digest_consumers.result @@ -123,47 +123,47 @@ DROP TRIGGER trg; #################################### # QUERYING PS STATEMENT DIGEST #################################### -SELECT schema_name, digest, digest_text, count_star FROM performance_schema.events_statements_summary_by_digest; -schema_name digest digest_text count_star -statements_digest 0e98ee6a98e296530ec59c12dbc08dfe TRUNCATE TABLE performance_schema . events_statements_summary_by_digest 1 -statements_digest 954f43425c3234acc8e194afd97e8a0a SELECT ? FROM t1 1 -statements_digest fc365a54bc19d746bd24c27aba46b990 SELECT ? FROM `t1` 1 -statements_digest 27ba28f6252e4ae0e9b14b36da536fbe SELECT ?, ... FROM t1 2 -statements_digest 81d03922612900032ec4b81934ab4841 SELECT ? FROM t2 1 -statements_digest adce8aec12b6b5046cd4bf55951014c7 SELECT ?, ... FROM t2 2 -statements_digest 59a1bd93c424b10802fe66bb6dcd94d2 INSERT INTO t1 VALUES (?) 1 -statements_digest 91b2da58b0eb49c35a38fbc49f5e491d INSERT INTO t2 VALUES (?) 1 -statements_digest 967114adbf91d8a4a99ec5e49e909ff4 INSERT INTO t3 VALUES (...) 4 -statements_digest 8f25e7a48487e0aa7377e816816bb658 INSERT INTO t4 VALUES (...) 1 -statements_digest 4e51253af793867fba66166de1f314f7 INSERT INTO t5 VALUES (...) 1 -statements_digest fa47b3109e117216cd10209690d28596 INSERT INTO t1 VALUES (?) /* , ... */ 2 -statements_digest 72409f84bc236e6fe9f2f7b4d727f2d3 INSERT INTO t3 VALUES (...) /* , ... */ 1 -statements_digest d40aaddb41ed794d65dd8273f0c75700 INSERT INTO t5 VALUES (...) /* , ... */ 1 -statements_digest 57a82b28388e52e99fc64339dd30edde INSERT INTO t1 VALUES ( NULL ) 1 -statements_digest 6a56b694106442474cb0e5fb7575c8b9 INSERT INTO t6 VALUES (...) 5 -statements_digest c9abf55e296c4317dbaf2d14ef907ad7 SELECT ? + ? 3 -statements_digest 156304a0851a3e3626b39fb3da841a82 SELECT ? 1 -statements_digest 3b085ab0d2063dfca1a39212e3ea1831 CREATE SCHEMA statements_digest_temp 2 -statements_digest 09f9fabef2feb9a54ba01455e5ae83b9 DROP SCHEMA statements_digest_temp 2 -statements_digest 7910a63ffd31cbcb742e15270c8958c8 SELECT ? FROM no_such_table 1 -statements_digest fa34540a438bc672478b1162505ee28c CREATE TABLE dup_table ( c CHARACTER (?) ) 2 -statements_digest 2c720f176bb7c8510ff8aca8921b9945 DROP TABLE dup_table 1 -statements_digest 0c7d9fd8c27ab067511da41ca3bcdff3 INSERT INTO t11 VALUES (?) 1 -statements_digest 81681ff345065ed72bcd1e9407ab85e4 SHOW WARNINGS 1 -statements_digest d766f5823ae5d8e4cf4602b8e7a3fb80 PREPARE stmt FROM ? 1 -statements_digest 3ab1e87eabd9688edf919754cce6348b EXECUTE stmt 2 -statements_digest 470094469d250b9f45cab45bf610efe8 DEALLOCATE PREPARE stmt 1 -statements_digest 1b4d25358e08b35ad54e49dfe5eaf3e4 CREATE PROCEDURE p1 ( ) BEGIN SELECT * FROM t12 ; END 1 -statements_digest 84554971243e91106214dcb8f4eaa89b CALL p1 ( ) 2 -statements_digest 77206e4bf30979c56752a7ed9150213a DROP PROCEDURE p1 1 -statements_digest 03b91dcdba6b0e29f7fb240ae4bcd97f CREATE FUNCTION `func` ( a INTEGER , b INTEGER ) RETURNS INTEGER (?) RETURN a + b 1 -statements_digest 72bc532f308f2dca62f5291df8c50e6f SELECT func (...) 2 -statements_digest 0b5a5297689c5036def6af8e8a8ce113 DROP FUNCTION func 1 -statements_digest d08331e42c67555ece50e46eef0f2b47 CREATE TRIGGER trg BEFORE INSERT ON t12 FOR EACH ROW SET @ ? := ? 1 -statements_digest 754a49a4de995c5a729e9ab52f135f59 INSERT INTO t12 VALUES (?) 2 -statements_digest 68df17752bca7c2c8ee2a6a19a0674e7 DROP TRIGGER trg 1 -SELECT digest, digest_text FROM performance_schema.events_statements_current; -digest digest_text +SELECT schema_name,digest_text, count_star FROM performance_schema.events_statements_summary_by_digest; +schema_name digest_text count_star +statements_digest TRUNCATE TABLE performance_schema . events_statements_summary_by_digest 1 +statements_digest SELECT ? FROM t1 1 +statements_digest SELECT ? FROM `t1` 1 +statements_digest SELECT ?, ... FROM t1 2 +statements_digest SELECT ? FROM t2 1 +statements_digest SELECT ?, ... FROM t2 2 +statements_digest INSERT INTO t1 VALUES (?) 1 +statements_digest INSERT INTO t2 VALUES (?) 1 +statements_digest INSERT INTO t3 VALUES (...) 4 +statements_digest INSERT INTO t4 VALUES (...) 1 +statements_digest INSERT INTO t5 VALUES (...) 1 +statements_digest INSERT INTO t1 VALUES (?) /* , ... */ 2 +statements_digest INSERT INTO t3 VALUES (...) /* , ... */ 1 +statements_digest INSERT INTO t5 VALUES (...) /* , ... */ 1 +statements_digest INSERT INTO t1 VALUES ( NULL ) 1 +statements_digest INSERT INTO t6 VALUES (...) 5 +statements_digest SELECT ? + ? 3 +statements_digest SELECT ? 1 +statements_digest CREATE SCHEMA statements_digest_temp 2 +statements_digest DROP SCHEMA statements_digest_temp 2 +statements_digest SELECT ? FROM no_such_table 1 +statements_digest CREATE TABLE dup_table ( c CHARACTER (?) ) 2 +statements_digest DROP TABLE dup_table 1 +statements_digest INSERT INTO t11 VALUES (?) 1 +statements_digest SHOW WARNINGS 1 +statements_digest PREPARE stmt FROM ? 1 +statements_digest EXECUTE stmt 2 +statements_digest DEALLOCATE PREPARE stmt 1 +statements_digest CREATE PROCEDURE p1 ( ) BEGIN SELECT * FROM t12 ; END 1 +statements_digest CALL p1 ( ) 2 +statements_digest DROP PROCEDURE p1 1 +statements_digest CREATE FUNCTION `func` ( a INTEGER , b INTEGER ) RETURNS INTEGER (?) RETURN a + b 1 +statements_digest SELECT func (...) 2 +statements_digest DROP FUNCTION func 1 +statements_digest CREATE TRIGGER trg BEFORE INSERT ON t12 FOR EACH ROW SET @ ? := ? 1 +statements_digest INSERT INTO t12 VALUES (?) 2 +statements_digest DROP TRIGGER trg 1 +SELECT digest_text FROM performance_schema.events_statements_current; +digest_text #################################### # CLEANUP #################################### diff --git a/mysql-test/suite/perfschema/r/statement_digest_long_query.result b/mysql-test/suite/perfschema/r/statement_digest_long_query.result index bb355304537..8bc7a877bda 100644 --- a/mysql-test/suite/perfschema/r/statement_digest_long_query.result +++ b/mysql-test/suite/perfschema/r/statement_digest_long_query.result @@ -6,7 +6,7 @@ SELECT 1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1 #################################### # QUERYING PS STATEMENT DIGEST #################################### -SELECT schema_name, digest, digest_text, count_star FROM events_statements_summary_by_digest; -schema_name digest digest_text count_star -performance_schema 9d35ff74210c6b30efa4559d627ed0f7 TRUNCATE TABLE events_statements_summary_by_digest 1 -performance_schema d78a04c1c42765b8552e0483c50ae9ff SELECT ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ... 1 +SELECT schema_name, digest_text, count_star FROM events_statements_summary_by_digest; +schema_name digest_text count_star +performance_schema TRUNCATE TABLE events_statements_summary_by_digest 1 +performance_schema SELECT ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ... 1 diff --git a/mysql-test/suite/perfschema/t/digest_table_full.test b/mysql-test/suite/perfschema/t/digest_table_full.test index cb9d7ea4ea8..dbbce662256 100644 --- a/mysql-test/suite/perfschema/t/digest_table_full.test +++ b/mysql-test/suite/perfschema/t/digest_table_full.test @@ -19,7 +19,7 @@ TRUNCATE TABLE performance_schema.events_statements_summary_by_digest; --echo #################################### --echo # QUERYING PS STATEMENT DIGEST --echo #################################### -SELECT SCHEMA_NAME, DIGEST, DIGEST_TEXT, COUNT_STAR, SUM_ROWS_AFFECTED, SUM_WARNINGS, +SELECT SCHEMA_NAME, DIGEST_TEXT, COUNT_STAR, SUM_ROWS_AFFECTED, SUM_WARNINGS, SUM_ERRORS FROM performance_schema.events_statements_summary_by_digest; diff --git a/mysql-test/suite/perfschema/t/rpl_gtid_func.test b/mysql-test/suite/perfschema/t/rpl_gtid_func.test index 27837572bff..f337f95bf84 100644 --- a/mysql-test/suite/perfschema/t/rpl_gtid_func.test +++ b/mysql-test/suite/perfschema/t/rpl_gtid_func.test @@ -45,7 +45,7 @@ insert into performance_schema.setup_objects select * from performance_schema.setup_objects order by object_type, object_schema, object_name; -select digest, digest_text, count_star +select digest_text, count_star from performance_schema.events_statements_summary_by_digest where digest_text like "%in_%_digest%"; @@ -67,7 +67,7 @@ insert into performance_schema.setup_objects select * from performance_schema.setup_objects order by object_type, object_schema, object_name; -select digest, digest_text, count_star +select digest_text, count_star from performance_schema.events_statements_summary_by_digest where digest_text like "%in_%_digest%"; diff --git a/mysql-test/suite/perfschema/t/start_server_no_digests.test b/mysql-test/suite/perfschema/t/start_server_no_digests.test index cb9d7ea4ea8..dbbce662256 100644 --- a/mysql-test/suite/perfschema/t/start_server_no_digests.test +++ b/mysql-test/suite/perfschema/t/start_server_no_digests.test @@ -19,7 +19,7 @@ TRUNCATE TABLE performance_schema.events_statements_summary_by_digest; --echo #################################### --echo # QUERYING PS STATEMENT DIGEST --echo #################################### -SELECT SCHEMA_NAME, DIGEST, DIGEST_TEXT, COUNT_STAR, SUM_ROWS_AFFECTED, SUM_WARNINGS, +SELECT SCHEMA_NAME, DIGEST_TEXT, COUNT_STAR, SUM_ROWS_AFFECTED, SUM_WARNINGS, SUM_ERRORS FROM performance_schema.events_statements_summary_by_digest; diff --git a/mysql-test/suite/perfschema/t/statement_digest.test b/mysql-test/suite/perfschema/t/statement_digest.test index ed1f99e4318..59486ad61d8 100644 --- a/mysql-test/suite/perfschema/t/statement_digest.test +++ b/mysql-test/suite/perfschema/t/statement_digest.test @@ -16,7 +16,7 @@ TRUNCATE TABLE performance_schema.events_statements_summary_by_digest; --echo #################################### --echo # QUERYING PS STATEMENT DIGEST --echo #################################### -SELECT SCHEMA_NAME, DIGEST, DIGEST_TEXT, COUNT_STAR, SUM_ROWS_AFFECTED, SUM_WARNINGS, +SELECT SCHEMA_NAME, DIGEST_TEXT, COUNT_STAR, SUM_ROWS_AFFECTED, SUM_WARNINGS, SUM_ERRORS FROM performance_schema.events_statements_summary_by_digest; # Cleanup for Digest diff --git a/mysql-test/suite/perfschema/t/statement_digest_consumers.test b/mysql-test/suite/perfschema/t/statement_digest_consumers.test index e7510e32049..16fa300b975 100644 --- a/mysql-test/suite/perfschema/t/statement_digest_consumers.test +++ b/mysql-test/suite/perfschema/t/statement_digest_consumers.test @@ -28,8 +28,8 @@ TRUNCATE TABLE performance_schema.events_statements_summary_by_digest; --echo #################################### --echo # QUERYING PS STATEMENT DIGEST --echo #################################### -SELECT schema_name, digest, digest_text, count_star FROM performance_schema.events_statements_summary_by_digest; -SELECT digest, digest_text FROM performance_schema.events_statements_current; +SELECT schema_name,digest_text, count_star FROM performance_schema.events_statements_summary_by_digest; +SELECT digest_text FROM performance_schema.events_statements_current; # Cleanup for Digest --source ../include/digest_cleanup.inc diff --git a/mysql-test/suite/perfschema/t/statement_digest_long_query.test b/mysql-test/suite/perfschema/t/statement_digest_long_query.test index 3969383a6fb..be80917c9af 100644 --- a/mysql-test/suite/perfschema/t/statement_digest_long_query.test +++ b/mysql-test/suite/perfschema/t/statement_digest_long_query.test @@ -20,4 +20,4 @@ SELECT 1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1 --echo #################################### --echo # QUERYING PS STATEMENT DIGEST --echo #################################### -SELECT schema_name, digest, digest_text, count_star FROM events_statements_summary_by_digest; +SELECT schema_name, digest_text, count_star FROM events_statements_summary_by_digest; diff --git a/mysql-test/suite/roles/create_and_drop_role_invalid_user_table.result b/mysql-test/suite/roles/create_and_drop_role_invalid_user_table.result deleted file mode 100644 index df4bc6ce5f6..00000000000 --- a/mysql-test/suite/roles/create_and_drop_role_invalid_user_table.result +++ /dev/null @@ -1,25 +0,0 @@ -use mysql; -alter table user drop column is_role; -alter table user drop column default_role; -flush privileges; -create role test_role; -ERROR HY000: Column count of mysql.user is wrong. Expected 44, found 43. Created with MariaDB MYSQL_VERSION_ID, now running MYSQL_VERSION_ID. Please use mysql_upgrade to fix this error. -drop role test_role; -ERROR HY000: Operation DROP ROLE failed for 'test_role' -alter table user add column is_role enum('N', 'Y') default 'N' not null -COLLATE utf8_general_ci -after password_expired; -create role test_role; -create user test_user@localhost; -grant test_role to test_user@localhost; -set default role test_role for root@localhost; -ERROR HY000: Column count of mysql.user is wrong. Expected 45, found 44. Created with MariaDB MYSQL_VERSION_ID, now running MYSQL_VERSION_ID. Please use mysql_upgrade to fix this error. -drop role test_role; -drop user test_user@localhost; -alter table user add column default_role char(80) binary default '' not null -COLLATE utf8_general_ci -after is_role; -update user set is_role='N'; -flush privileges; -create role test_role; -drop role test_role; diff --git a/mysql-test/suite/roles/create_and_drop_role_invalid_user_table.test b/mysql-test/suite/roles/create_and_drop_role_invalid_user_table.test deleted file mode 100644 index 5122a2fed9a..00000000000 --- a/mysql-test/suite/roles/create_and_drop_role_invalid_user_table.test +++ /dev/null @@ -1,38 +0,0 @@ -source include/not_embedded.inc; - -connect (mysql, localhost, root,,); -use mysql; - -alter table user drop column is_role; -alter table user drop column default_role; - -flush privileges; - ---replace_regex /10\d\d\d\d/MYSQL_VERSION_ID/ ---error ER_COL_COUNT_DOESNT_MATCH_PLEASE_UPDATE -create role test_role; ---error ER_CANNOT_USER -drop role test_role; -alter table user add column is_role enum('N', 'Y') default 'N' not null - COLLATE utf8_general_ci -after password_expired; - -# Test default role column -create role test_role; -create user test_user@localhost; -grant test_role to test_user@localhost; ---replace_regex /10\d\d\d\d/MYSQL_VERSION_ID/ ---error ER_COL_COUNT_DOESNT_MATCH_PLEASE_UPDATE -set default role test_role for root@localhost; -drop role test_role; -drop user test_user@localhost; - -alter table user add column default_role char(80) binary default '' not null - COLLATE utf8_general_ci -after is_role; - -update user set is_role='N'; - -flush privileges; -create role test_role; -drop role test_role; diff --git a/mysql-test/suite/roles/set_role-recursive.result b/mysql-test/suite/roles/set_role-recursive.result index 008de2c3265..9e62558fc14 100644 --- a/mysql-test/suite/roles/set_role-recursive.result +++ b/mysql-test/suite/roles/set_role-recursive.result @@ -16,11 +16,11 @@ Host User Role Admin_option test_role1 test_role2 N grant select on *.* to test_role2; select * from mysql.user where user like 'test_role1'; -Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv Create_tablespace_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections plugin authentication_string password_expired is_role default_role - test_role1 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0 N Y +Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv Create_tablespace_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections plugin authentication_string password_expired is_role default_role max_statement_time + test_role1 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0 N Y 0.000000 select * from mysql.user where user like 'test_role2'; -Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv Create_tablespace_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections plugin authentication_string password_expired is_role default_role - test_role2 Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0 N Y +Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv Create_tablespace_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections plugin authentication_string password_expired is_role default_role max_statement_time + test_role2 Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0 N Y 0.000000 select * from mysql.roles_mapping; ERROR 42000: SELECT command denied to user 'test_user'@'localhost' for table 'roles_mapping' show grants; diff --git a/mysql-test/suite/roles/set_role-simple.result b/mysql-test/suite/roles/set_role-simple.result index f870bf8eb30..3ce6d5c054b 100644 --- a/mysql-test/suite/roles/set_role-simple.result +++ b/mysql-test/suite/roles/set_role-simple.result @@ -11,8 +11,8 @@ localhost root test_role1 Y localhost test_user test_role1 N grant select on *.* to test_role1; select * from mysql.user where user='test_role1'; -Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv Create_tablespace_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections plugin authentication_string password_expired is_role default_role - test_role1 Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0 N Y +Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv Create_tablespace_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections plugin authentication_string password_expired is_role default_role max_statement_time + test_role1 Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0 N Y 0.000000 select * from mysql.roles_mapping; ERROR 42000: SELECT command denied to user 'test_user'@'localhost' for table 'roles_mapping' show grants; diff --git a/mysql-test/suite/sys_vars/r/max_statement_time_basic.result b/mysql-test/suite/sys_vars/r/max_statement_time_basic.result new file mode 100644 index 00000000000..8b384ac6765 --- /dev/null +++ b/mysql-test/suite/sys_vars/r/max_statement_time_basic.result @@ -0,0 +1,172 @@ +SET @start_global_value = @@global.max_statement_time; +SELECT @start_global_value; +@start_global_value +0 +SET @start_session_value = @@session.max_statement_time; +SELECT @start_session_value; +@start_session_value +0 +'#--------------------FN_DYNVARS_068_01-------------------------#' +SET @@global.max_statement_time = 100; +SET @@global.max_statement_time = DEFAULT; +SELECT @@global.max_statement_time; +@@global.max_statement_time +0.000000 +SET @@session.max_statement_time = 200; +SET @@session.max_statement_time = DEFAULT; +SELECT @@session.max_statement_time; +@@session.max_statement_time +0.000000 +'#--------------------FN_DYNVARS_068_02-------------------------#' +SET @@global.max_statement_time = DEFAULT; +SELECT @@global.max_statement_time = 0; +@@global.max_statement_time = 0 +1 +SET @@session.max_statement_time = DEFAULT; +SELECT @@session.max_statement_time = 0; +@@session.max_statement_time = 0 +1 +'#--------------------FN_DYNVARS_068_03-------------------------#' +SET @@global.max_statement_time = 0; +SELECT @@global.max_statement_time; +@@global.max_statement_time +0.000000 +SET @@global.max_statement_time = 0.123456; +SELECT @@global.max_statement_time; +@@global.max_statement_time +0.123456 +SET @@global.max_statement_time = 60020; +SELECT @@global.max_statement_time; +@@global.max_statement_time +60020.000000 +SET @@global.max_statement_time = 31536000; +SELECT @@global.max_statement_time; +@@global.max_statement_time +31536000.000000 +SET @@global.max_statement_time = 65536; +SELECT @@global.max_statement_time; +@@global.max_statement_time +65536.000000 +'#--------------------FN_DYNVARS_068_04-------------------------#' +SET @@session.max_statement_time = 0; +SELECT @@session.max_statement_time; +@@session.max_statement_time +0.000000 +SET @@session.max_statement_time = 1; +SELECT @@session.max_statement_time; +@@session.max_statement_time +1.000000 +SET @@session.max_statement_time = 50050; +SELECT @@session.max_statement_time; +@@session.max_statement_time +50050.000000 +SET @@session.max_statement_time = 31536000; +SELECT @@session.max_statement_time; +@@session.max_statement_time +31536000.000000 +SET @@session.max_statement_time = 65550; +SELECT @@session.max_statement_time; +@@session.max_statement_time +65550.000000 +'#------------------FN_DYNVARS_068_05-----------------------#' +SET @@global.max_statement_time = 100000000000; +Warnings: +Warning 1292 Truncated incorrect max_statement_time value: '100000000000' +SELECT @@global.max_statement_time; +@@global.max_statement_time +31536000.000000 +SET @@global.max_statement_time = -1; +Warnings: +Warning 1292 Truncated incorrect max_statement_time value: '-1' +SELECT @@global.max_statement_time; +@@global.max_statement_time +0.000000 +SET @@global.max_statement_time = 65530.34; +SELECT @@global.max_statement_time; +@@global.max_statement_time +65530.340000 +SET @@global.max_statement_time = test; +ERROR 42000: Incorrect argument type to variable 'max_statement_time' +SELECT @@global.max_statement_time; +@@global.max_statement_time +65530.340000 +SET @@session.max_statement_time = 100000000000; +Warnings: +Warning 1292 Truncated incorrect max_statement_time value: '100000000000' +SELECT @@session.max_statement_time; +@@session.max_statement_time +31536000.000000 +SET @@session.max_statement_time = -2; +Warnings: +Warning 1292 Truncated incorrect max_statement_time value: '-2' +SELECT @@session.max_statement_time; +@@session.max_statement_time +0.000000 +SET @@session.max_statement_time = 65530.34; +SELECT @@session.max_statement_time; +@@session.max_statement_time +65530.340000 +SET @@session.max_statement_time = test; +ERROR 42000: Incorrect argument type to variable 'max_statement_time' +SELECT @@session.max_statement_time; +@@session.max_statement_time +65530.340000 +'#------------------FN_DYNVARS_068_06-----------------------#' +SELECT @@global.max_statement_time = VARIABLE_VALUE +FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES +WHERE VARIABLE_NAME='max_statement_time'; +@@global.max_statement_time = VARIABLE_VALUE +1 +'#------------------FN_DYNVARS_068_07-----------------------#' +SELECT @@session.max_statement_time = VARIABLE_VALUE +FROM INFORMATION_SCHEMA.SESSION_VARIABLES +WHERE VARIABLE_NAME='max_statement_time'; +@@session.max_statement_time = VARIABLE_VALUE +1 +'#------------------FN_DYNVARS_068_08-----------------------#' +SET @@global.max_statement_time = TRUE; +SELECT @@global.max_statement_time; +@@global.max_statement_time +1.000000 +SET @@global.max_statement_time = FALSE; +SELECT @@global.max_statement_time; +@@global.max_statement_time +0.000000 +'#---------------------FN_DYNVARS_001_09----------------------#' +SET @@global.max_statement_time = 10; +SELECT @@max_statement_time = @@global.max_statement_time; +@@max_statement_time = @@global.max_statement_time +0 +'#---------------------FN_DYNVARS_001_10----------------------#' +SET @@max_statement_time = 100; +SELECT @@max_statement_time = @@local.max_statement_time; +@@max_statement_time = @@local.max_statement_time +1 +SELECT @@local.max_statement_time = @@session.max_statement_time; +@@local.max_statement_time = @@session.max_statement_time +1 +'#---------------------FN_DYNVARS_001_11----------------------#' +SET max_statement_time = 1; +SELECT @@max_statement_time; +@@max_statement_time +1.000000 +SELECT local.max_statement_time; +ERROR 42S02: Unknown table 'local' in field list +SELECT session.max_statement_time; +ERROR 42S02: Unknown table 'session' in field list +SELECT max_statement_time = @@session.max_statement_time; +ERROR 42S22: Unknown column 'max_statement_time' in 'field list' +# +# Check that one can use max_statement_time as a field +# +drop table if exists t1; +create table t1 (a int, max_statement_time int); +drop table t1; +SET @@global.max_statement_time = @start_global_value; +SELECT @@global.max_statement_time; +@@global.max_statement_time +0.000000 +SET @@session.max_statement_time = @start_session_value; +SELECT @@session.max_statement_time; +@@session.max_statement_time +0.000000 diff --git a/mysql-test/suite/sys_vars/t/max_statement_time_basic.test b/mysql-test/suite/sys_vars/t/max_statement_time_basic.test new file mode 100644 index 00000000000..186589dcee2 --- /dev/null +++ b/mysql-test/suite/sys_vars/t/max_statement_time_basic.test @@ -0,0 +1,217 @@ +####################### mysql-test\t\max_statement_time_basic.test ############### +# # +# Variable Name: max_statement_time # +# Scope: GLOBAL | SESSION # +# Access Type: Dynamic # +# Data Type: numeric # +# Default Value:10 # +# Min Value: 1 # +# # +# # +# Creation Date: 2012-12-30 # +# Author: Monty # +# # +# Description: Test Cases of Dynamic System Variable max_statement_time # +# that checks the behavior of this variable in the following ways# +# * Default Value # +# * Valid & Invalid values # +# * Scope & Access method # +# * Data Integrity # +# # +# Reference: https://kb.askmonty.org/en/how-to-limittimeout-queries/ # +# server-system-variables.html # +# # +############################################################################### + +--source include/load_sysvars.inc + +############################################################ +# START OF max_statement_time TESTS # +############################################################ + + +############################################################# +# Save initial value # +############################################################# + +SET @start_global_value = @@global.max_statement_time; +SELECT @start_global_value; +SET @start_session_value = @@session.max_statement_time; +SELECT @start_session_value; + + +--echo '#--------------------FN_DYNVARS_068_01-------------------------#' +############################################################### +# Display the DEFAULT value of max_statement_time # +############################################################### + +SET @@global.max_statement_time = 100; +SET @@global.max_statement_time = DEFAULT; +SELECT @@global.max_statement_time; + +SET @@session.max_statement_time = 200; +SET @@session.max_statement_time = DEFAULT; +SELECT @@session.max_statement_time; + + +--echo '#--------------------FN_DYNVARS_068_02-------------------------#' +############################################################### +# Check the DEFAULT value of max_statement_time # +############################################################### + +SET @@global.max_statement_time = DEFAULT; +SELECT @@global.max_statement_time = 0; + +SET @@session.max_statement_time = DEFAULT; +SELECT @@session.max_statement_time = 0; + + +--echo '#--------------------FN_DYNVARS_068_03-------------------------#' +######################################################################### +# Change the value of max_statement_time to a valid value for GLOBAL Scope # +######################################################################### + +SET @@global.max_statement_time = 0; +SELECT @@global.max_statement_time; +SET @@global.max_statement_time = 0.123456; +SELECT @@global.max_statement_time; +SET @@global.max_statement_time = 60020; +SELECT @@global.max_statement_time; +SET @@global.max_statement_time = 31536000; +SELECT @@global.max_statement_time; +SET @@global.max_statement_time = 65536; +SELECT @@global.max_statement_time; + + +--echo '#--------------------FN_DYNVARS_068_04-------------------------#' +########################################################################## +# Change the value of max_statement_time to a valid value for SESSION Scope # +########################################################################## + +SET @@session.max_statement_time = 0; +SELECT @@session.max_statement_time; +SET @@session.max_statement_time = 1; +SELECT @@session.max_statement_time; +SET @@session.max_statement_time = 50050; +SELECT @@session.max_statement_time; +SET @@session.max_statement_time = 31536000; +SELECT @@session.max_statement_time; +SET @@session.max_statement_time = 65550; +SELECT @@session.max_statement_time; + + +--echo '#------------------FN_DYNVARS_068_05-----------------------#' +######################################################## +# Change the value of max_statement_time to an invalid value # +######################################################## + +SET @@global.max_statement_time = 100000000000; +SELECT @@global.max_statement_time; +SET @@global.max_statement_time = -1; +SELECT @@global.max_statement_time; +SET @@global.max_statement_time = 65530.34; +SELECT @@global.max_statement_time; +--Error ER_WRONG_TYPE_FOR_VAR +SET @@global.max_statement_time = test; +SELECT @@global.max_statement_time; + +SET @@session.max_statement_time = 100000000000; +SELECT @@session.max_statement_time; +SET @@session.max_statement_time = -2; +SELECT @@session.max_statement_time; +SET @@session.max_statement_time = 65530.34; +SELECT @@session.max_statement_time; + +--Error ER_WRONG_TYPE_FOR_VAR +SET @@session.max_statement_time = test; +SELECT @@session.max_statement_time; + + +--echo '#------------------FN_DYNVARS_068_06-----------------------#' +#################################################################### +# Check if the value in GLOBAL Table matches value in variable # +#################################################################### + + +SELECT @@global.max_statement_time = VARIABLE_VALUE +FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES +WHERE VARIABLE_NAME='max_statement_time'; + +--echo '#------------------FN_DYNVARS_068_07-----------------------#' +#################################################################### +# Check if the value in SESSION Table matches value in variable # +#################################################################### + +SELECT @@session.max_statement_time = VARIABLE_VALUE +FROM INFORMATION_SCHEMA.SESSION_VARIABLES +WHERE VARIABLE_NAME='max_statement_time'; + + +--echo '#------------------FN_DYNVARS_068_08-----------------------#' +#################################################################### +# Check if TRUE and FALSE values can be used on variable # +#################################################################### + +SET @@global.max_statement_time = TRUE; +SELECT @@global.max_statement_time; +SET @@global.max_statement_time = FALSE; +SELECT @@global.max_statement_time; + + +--echo '#---------------------FN_DYNVARS_001_09----------------------#' +################################################################################# +# Check if accessing variable with and without GLOBAL point to same variable # +################################################################################# + +SET @@global.max_statement_time = 10; +SELECT @@max_statement_time = @@global.max_statement_time; + + +--echo '#---------------------FN_DYNVARS_001_10----------------------#' +######################################################################################################## +# Check if accessing variable with SESSION,LOCAL and without SCOPE points to same session variable # +######################################################################################################## + +SET @@max_statement_time = 100; +SELECT @@max_statement_time = @@local.max_statement_time; +SELECT @@local.max_statement_time = @@session.max_statement_time; + + +--echo '#---------------------FN_DYNVARS_001_11----------------------#' +########################################################################## +# Check if max_statement_time can be accessed with and without @@ sign # +########################################################################## + +SET max_statement_time = 1; +SELECT @@max_statement_time; +--Error ER_UNKNOWN_TABLE +SELECT local.max_statement_time; +--Error ER_UNKNOWN_TABLE +SELECT session.max_statement_time; +--Error ER_BAD_FIELD_ERROR +SELECT max_statement_time = @@session.max_statement_time; + +--echo # +--echo # Check that one can use max_statement_time as a field +--echo # + +--disable_warnings +drop table if exists t1; +--enable_warnings +create table t1 (a int, max_statement_time int); +drop table t1; + +#################################### +# Restore initial value # +#################################### + +SET @@global.max_statement_time = @start_global_value; +SELECT @@global.max_statement_time; +SET @@session.max_statement_time = @start_session_value; +SELECT @@session.max_statement_time; + + +#################################################### +# END OF max_statement_time TESTS # +#################################################### + diff --git a/mysql-test/t/grant.test b/mysql-test/t/grant.test index ed7271521c8..20632038273 100644 --- a/mysql-test/t/grant.test +++ b/mysql-test/t/grant.test @@ -47,10 +47,10 @@ flush privileges; # delete from mysql.user where user='mysqltest_1'; flush privileges; -grant usage on *.* to mysqltest_1@localhost with max_queries_per_hour 10; +grant usage on *.* to mysqltest_1@localhost with max_queries_per_hour 10 max_statement_time 60; query_vertical select * from mysql.user where user="mysqltest_1"; show grants for mysqltest_1@localhost; -grant usage on *.* to mysqltest_1@localhost with max_updates_per_hour 20 max_connections_per_hour 30; +grant usage on *.* to mysqltest_1@localhost with max_updates_per_hour 20 max_connections_per_hour 30 max_statement_time 0; query_vertical select * from mysql.user where user="mysqltest_1"; show grants for mysqltest_1@localhost; # This is just to double check that one won't ignore results of selects diff --git a/mysql-test/t/max_statement_time.test b/mysql-test/t/max_statement_time.test new file mode 100644 index 00000000000..0356d3caa49 --- /dev/null +++ b/mysql-test/t/max_statement_time.test @@ -0,0 +1,189 @@ +# +# Test behavior of MAX_STATEMENT_TIME. +# + +--source include/not_embedded.inc +--source include/have_innodb.inc + +--echo +--echo # Test the MAX_STATEMENT_TIME option. +--echo + +SET @@MAX_STATEMENT_TIME=2; +select @@max_statement_time; +SELECT SLEEP(1); +SELECT SLEEP(3); +SET @@MAX_STATEMENT_TIME=0; +SELECT SLEEP(1); +SHOW STATUS LIKE "max_statement_time_exceeded"; + +CREATE TABLE t1 (a INT, b VARCHAR(300)) engine=myisam; + +INSERT INTO t1 VALUES (1, 'string'); + +--disable_result_log +--disable_query_log + +SET @@MAX_STATEMENT_TIME=2; + +SET @@MAX_STATEMENT_TIME=0.1; +WHILE (! $mysql_errno) +{ + SET @@MAX_STATEMENT_TIME=0; + INSERT INTO t1 SELECT * FROM t1; + SET @@MAX_STATEMENT_TIME=0.1; + --error 0,ER_STATEMENT_TIMEOUT + SELECT COUNT(*) FROM t1 WHERE b LIKE '%z%'; +} +SET @@MAX_STATEMENT_TIME=0; + +--enable_query_log +--enable_result_log + +eval SELECT $mysql_errno; + +--echo +--echo # Test the MAX_STATEMENT_TIME option with SF (should have no effect). +--echo + +DELIMITER |; + +CREATE PROCEDURE p1() +BEGIN + declare tmp int; + SET @@MAX_STATEMENT_TIME=0.0001; + SELECT COUNT(*) INTO tmp FROM t1 WHERE b LIKE '%z%'; + SET @@MAX_STATEMENT_TIME=0; +END| + +CREATE PROCEDURE p2() +BEGIN + SET @@MAX_STATEMENT_TIME=5; +END| + +DELIMITER ;| + +SELECT @@MAX_STATEMENT_TIME; +CALL p1(); +CALL p2(); +SELECT @@MAX_STATEMENT_TIME; +SET @@MAX_STATEMENT_TIME=0; + +DROP PROCEDURE p1; +DROP PROCEDURE p2; +DROP TABLE t1; + +--echo +--echo # MAX_STATEMENT_TIME account resource +--echo + +GRANT USAGE ON *.* TO user1@localhost WITH MAX_STATEMENT_TIME 1.005; + +--echo # con1 +connect(con1,localhost,user1,,test,,); +SELECT @@max_statement_time; +disconnect con1; + +--echo # restart and reconnect +connection default; +source include/restart_mysqld.inc; + +set @global.userstat=1; +connect(con1,localhost,user1,,test,,); +SELECT @@global.max_statement_time,@@session.max_statement_time; +select sleep(100); +SHOW STATUS LIKE "max_statement_time_exceeded"; +disconnect con1; + +connection default; +show grants for user1@localhost; +--disable_parsing +select max_user_timeouts from information_schema.user_statistics where user="user1"; +--enable_parsing + +set @global.userstat=0; +DROP USER user1@localhost; + +--echo +--echo # MAX_STATEMENT_TIME status variables. +--echo + +flush status; + +SET @@max_statement_time=0; +SELECT CONVERT(VARIABLE_VALUE, UNSIGNED) INTO @time_exceeded + FROM INFORMATION_SCHEMA.GLOBAL_STATUS + WHERE VARIABLE_NAME = 'max_statement_time_exceeded'; + +SET @@max_statement_time=0.5; +SELECT SLEEP(2); +SHOW STATUS LIKE '%timeout%'; +SET @@max_statement_time=0; + +--echo # Ensure that the counters for: +--echo # - statements that exceeded their maximum execution time +--echo # are incremented. + +SELECT 1 AS STATUS FROM INFORMATION_SCHEMA.GLOBAL_STATUS + WHERE VARIABLE_NAME = 'max_statement_time_exceeded' + AND CONVERT(VARIABLE_VALUE, UNSIGNED) > @time_exceeded; + +--echo +--echo # Check that the appropriate error status is set. +--echo + +CREATE TABLE t1 (a INT) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1); + +START TRANSACTION; +SELECT * FROM t1 FOR UPDATE; + +connect (con1,localhost,root,,test,,); +SET @@SESSION.max_statement_time = 0.5; +--error ER_STATEMENT_TIMEOUT +UPDATE t1 SET a = 2; +SHOW WARNINGS; +disconnect con1; + +connection default; +ROLLBACK; +DROP TABLE t1; + +--echo +--echo # Test interaction with lock waits. +--echo + +CREATE TABLE t1 (a INT) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1); + +connect (con1,localhost,root,,test,,); +SET @@SESSION.max_statement_time= 0.5; + +connection default; +LOCK TABLES t1 WRITE; + +connection con1; +SELECT @@SESSION.max_statement_time; +--error ER_STATEMENT_TIMEOUT +LOCK TABLES t1 READ; + +connection default; +UNLOCK TABLES; +BEGIN; +SELECT * FROM t1; + +connection con1; +--error ER_STATEMENT_TIMEOUT +ALTER TABLE t1 ADD COLUMN b INT; + +connection default; +ROLLBACK; +SELECT GET_LOCK('lock', 1); + +connection con1; +SELECT GET_LOCK('lock', 1); + +disconnect con1; +connection default; +SELECT RELEASE_LOCK('lock'); +DROP TABLE t1; |