Bug #44933 STARTUP_TIME in I_S.PROCESSLIST
Submitted: 18 May 2009 13:27 Modified: 19 May 2009 6:26
Reporter: Jørgen Austvik Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Information schema Severity:S4 (Feature request)
Version:6.0 OS:Any
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D5 (Feature request)

[18 May 2009 13:27] Jørgen Austvik
Description:
Today it is impossible to know how long a connection/session/process has been open from SQL. This makes queries a la "show average wait time for all processes sort desc" impossible.

I believe the wish for getting access to this information will increase when we get performance_schema.

I can not find I_S.PROCESSLIST in SQL:2003, so I believe the information can be added there.

How to repeat:
n/a - RFE

Suggested fix:
=== modified file 'sql/sql_show.cc'
--- sql/sql_show.cc     2009-04-13 16:15:22 +0000
+++ sql/sql_show.cc     2009-05-18 13:04:15 +0000
@@ -1839,6 +1839,7 @@ int fill_schema_processlist(THD* thd, TA
 {
   TABLE *table= tables->table;
   CHARSET_INFO *cs= system_charset_info;
+  MYSQL_TIME time;
   char *user;
   time_t now= my_time(0);
   DBUG_ENTER("fill_process_list");
@@ -1934,6 +1935,14 @@ int fill_schema_processlist(THD* thd, TA
         table->field[7]->set_notnull();
       }
 
+      /* STARTUP TIME (Connection established) */
+      tmp->variables.time_zone->gmt_sec_to_TIME(&time,
+                                                (my_time_t) tmp->connect_utime / 1000000);
+      table->field[8]->store_time(&time, MYSQL_TIMESTAMP_DATETIME);
+      table->field[8]->set_notnull();
+
       if (schema_table_store_record(thd, table))
       {
         MYSQL_MUTEX_UNLOCK(&LOCK_thread_count);
@@ -7016,6 +7025,7 @@ 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},
+  {"STARTUP_TIME", 7, MYSQL_TYPE_DATETIME, 0, 0, "StartupTime", SKIP_OPEN_TABLE},
   {0, 0, MYSQL_TYPE_STRING, 0, 0, 0, SKIP_OPEN_TABLE}
 };