#At lp:maria 2754 knielsen@knielsen-hq.org 2009-09-23 Merge Percona microsec_process patch into MariaDB. This adds a TIME_MS column to SHOW FULL PROCESSLIST and INFORMATION_SCHEMA.PROCESSLIST that works like the TIME column, but in units of milliseconds with microsecond precision. Note that this also changes behaviour of the existing TIME column. In the MySQL server, the TIME column changes when a thread sets @TIMESTAMP. This is contrary to documentation and also potentially confusing, so could in any case be considered a bug. With this patch, to ensure consistency between TIME and TIME_MS, setting @TIMESTAMP has no effect on either value. Add a test case for the TIME and TIME_MS columns. Update existing test cases for changed behaviour. Author: Percona Signed-off-by: Kristian Nielsen <knielsen@knielsen-hq.org> modified: mysql-test/r/create.result mysql-test/r/information_schema.result mysql-test/t/information_schema.test mysql-test/t/not_embedded_server.test sql/sql_show.cc per-file messages: mysql-test/r/create.result Result file update. mysql-test/r/information_schema.result Add test case. Result file update (changed behaviour). mysql-test/t/information_schema.test Add test case. Adjust test case for changed behaviour. mysql-test/t/not_embedded_server.test Update (commented-out) test case for newly added column. sql/sql_show.cc Merge Percona microsec_process patch into MariaDB. === modified file 'mysql-test/r/create.result' --- a/mysql-test/r/create.result 2009-09-07 20:50:10 +0000 +++ b/mysql-test/r/create.result 2009-09-23 11:03:47 +0000 @@ -1749,7 +1749,8 @@ t1 CREATE TABLE `t1` ( `COMMAND` varchar(16) NOT NULL DEFAULT '', `TIME` int(7) NOT NULL DEFAULT '0', `STATE` varchar(64) DEFAULT NULL, - `INFO` longtext + `INFO` longtext, + `TIME_MS` decimal(22,3) NOT NULL DEFAULT '0.000' ) DEFAULT CHARSET=utf8 drop table t1; create temporary table t1 like information_schema.processlist; @@ -1763,7 +1764,8 @@ t1 CREATE TEMPORARY TABLE `t1` ( `COMMAND` varchar(16) NOT NULL DEFAULT '', `TIME` int(7) NOT NULL DEFAULT '0', `STATE` varchar(64) DEFAULT NULL, - `INFO` longtext + `INFO` longtext, + `TIME_MS` decimal(22,3) NOT NULL DEFAULT '0.000' ) DEFAULT CHARSET=utf8 drop table t1; create table t1 like information_schema.character_sets; === modified file 'mysql-test/r/information_schema.result' --- a/mysql-test/r/information_schema.result 2009-09-07 20:50:10 +0000 +++ b/mysql-test/r/information_schema.result 2009-09-23 11:03:47 +0000 @@ -1380,6 +1380,17 @@ select user,db from information_schema.p user db user3148 test drop user user3148@localhost; +SELECT 'other connection here' AS who; +who +other connection here +SELECT IF(`time` > 0, 'OK', `time`) AS time_low, +IF(`time` < 1000, 'OK', `time`) AS time_high, +IF(time_ms > 900, 'OK', time_ms) AS time_ms_low, +IF(time_ms < 1000000, 'OK', time_ms) AS time_ms_high +FROM INFORMATION_SCHEMA.PROCESSLIST +WHERE ID=@tid; +time_low time_high time_ms_low time_ms_high +OK OK OK OK DROP TABLE IF EXISTS server_status; DROP EVENT IF EXISTS event_status; SET GLOBAL event_scheduler=1; @@ -1602,8 +1613,7 @@ CREATE_OPTIONS key_block_size=1 DROP TABLE t1; SET TIMESTAMP=@@TIMESTAMP + 10000000; -SELECT 'OK' AS TEST_RESULT FROM INFORMATION_SCHEMA.PROCESSLIST WHERE time < 0; +SELECT 'NOT_OK' AS TEST_RESULT FROM INFORMATION_SCHEMA.PROCESSLIST WHERE time < 0; TEST_RESULT -OK SET TIMESTAMP=DEFAULT; End of 5.1 tests. === modified file 'mysql-test/t/information_schema.test' --- a/mysql-test/t/information_schema.test 2009-09-07 20:50:10 +0000 +++ b/mysql-test/t/information_schema.test 2009-09-23 11:03:47 +0000 @@ -1099,6 +1099,25 @@ disconnect con3148; drop user user3148@localhost; # +# `time` and `time_ms` columns of INFORMATION_SCHEMA.PROCESSLIST. +# +connect (pslistcon,localhost,root,,test); +let $ID= `select connection_id()`; +SELECT 'other connection here' AS who; +connection default; +sleep 1; +--disable_query_log +eval SET @tid=$ID; +--enable_query_log +SELECT IF(`time` > 0, 'OK', `time`) AS time_low, + IF(`time` < 1000, 'OK', `time`) AS time_high, + IF(time_ms > 900, 'OK', time_ms) AS time_ms_low, + IF(time_ms < 1000000, 'OK', time_ms) AS time_ms_high + FROM INFORMATION_SCHEMA.PROCESSLIST + WHERE ID=@tid; +disconnect pslistcon; + +# # Bug#26174 Server Crash: INSERT ... SELECT ... FROM I_S.GLOBAL_STATUS # in Event (see also openssl_1.test) # @@ -1352,9 +1371,16 @@ DROP TABLE t1; # Bug #22047: Time in SHOW PROCESSLIST for SQL thread in replication seems # to become negative # +# Note that at the time of writing, MariaDB differs in behaviour from MySQL on +# the `time` column. In MySQL this changes depending on the setting of +# @TIMESTAMP, which is contrary to the documented (and sensible) behaviour. +# In MariaDB, the `time` column is independent of @TIMESTAMP. +# (The rationale for this is to keep `time` and `time_ms` consistent; +# @TIMESTAMP has no microsecond precision). +# SET TIMESTAMP=@@TIMESTAMP + 10000000; -SELECT 'OK' AS TEST_RESULT FROM INFORMATION_SCHEMA.PROCESSLIST WHERE time < 0; +SELECT 'NOT_OK' AS TEST_RESULT FROM INFORMATION_SCHEMA.PROCESSLIST WHERE time < 0; SET TIMESTAMP=DEFAULT; --echo End of 5.1 tests. === modified file 'mysql-test/t/not_embedded_server.test' --- a/mysql-test/t/not_embedded_server.test 2009-04-30 10:29:19 +0000 +++ b/mysql-test/t/not_embedded_server.test 2009-09-23 11:03:47 +0000 @@ -32,7 +32,7 @@ select 1; ## End of 4.1 tests # #prepare stmt1 from ' SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND!=\'Daemon\' '; -#--replace_column 1 number 6 time 3 localhost +#--replace_column 1 number 6 time 3 localhost 9 time_ms #execute stmt1; #deallocate prepare stmt1; === modified file 'sql/sql_show.cc' --- a/sql/sql_show.cc 2009-09-15 10:46:35 +0000 +++ b/sql/sql_show.cc 2009-09-23 11:03:47 +0000 @@ -1819,7 +1819,7 @@ int fill_schema_processlist(THD* thd, TA TABLE *table= tables->table; CHARSET_INFO *cs= system_charset_info; char *user; - time_t now= my_time(0); + ulonglong unow= my_micro_time(); DBUG_ENTER("fill_process_list"); user= thd->security_ctx->master_access & PROCESS_ACL ? @@ -1877,8 +1877,8 @@ int fill_schema_processlist(THD* thd, TA table->field[4]->store(command_name[tmp->command].str, command_name[tmp->command].length, cs); /* MYSQL_TIME */ - table->field[5]->store((longlong)(tmp->start_time ? - now - tmp->start_time : 0), FALSE); + const ulonglong utime= tmp->start_utime ? unow - tmp->start_utime : 0; + table->field[5]->store(utime / 1000000, TRUE); /* STATE */ #ifndef EMBEDDED_LIBRARY val= (char*) (tmp->locked ? "Locked" : @@ -1912,6 +1912,9 @@ int fill_schema_processlist(THD* thd, TA table->field[7]->set_notnull(); } + /* TIME_MS */ + table->field[8]->store((double)(utime / 1000.0)); + if (schema_table_store_record(thd, table)) { VOID(pthread_mutex_unlock(&LOCK_thread_count)); @@ -5542,7 +5545,7 @@ ST_SCHEMA_TABLE *get_schema_table(enum e into it two numbers, based on modulus of base-10 numbers. In the ones position is the number of decimals. Tens position is unused. In the hundreds and thousands position is a two-digit decimal number representing - length. Encode this value with (decimals*100)+length , where + length. Encode this value with (length*100)+decimals , where 0<decimals<10 and 0<=length<100 . @param @@ -6557,6 +6560,8 @@ ST_FIELD_INFO processlist_fields_info[]= {"STATE", 64, MYSQL_TYPE_STRING, 0, 1, "State", SKIP_OPEN_TABLE}, {"INFO", PROCESS_LIST_INFO_WIDTH, MYSQL_TYPE_STRING, 0, 1, "Info", SKIP_OPEN_TABLE}, + {"TIME_MS", 100 * (MY_INT64_NUM_DECIMAL_DIGITS + 1) + 3, MYSQL_TYPE_DECIMAL, + 0, 0, "Time_ms", SKIP_OPEN_TABLE}, {0, 0, MYSQL_TYPE_STRING, 0, 0, 0, SKIP_OPEN_TABLE} };