Bug #73999 SET TIMESTAMP on non-slave can be made to show erronous Time in SHOW PROCESSLIST

Submitted: 21 Sep 2014 22:51 Modified: 22 Sep 2014 6:07
Reporter: Roel Van de Paar Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Connection Handling Severity:S2 (Serious)
Version:5.6 OS:Any
Assigned to: CPU Architecture:Any

[21 Sep 2014 22:51] Roel Van de Paar
Description:
mysql> SET TIMESTAMP=1;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW FULL PROCESSLIST\G
*************************** 1. row ***************************
     Id: 1
   User: root
   Host: localhost
     db: mysql
Command: Query
   Time: 1411375647
  State: init
   Info: SHOW FULL PROCESSLIST
1 row in set (0.00 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST\G
*************************** 1. row ***************************
     ID: 1
   USER: root
   HOST: localhost
     DB: mysql
COMMAND: Query
   TIME: 1411375668
  STATE: executing
   INFO: SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST
1 row in set (0.00 sec)

How to repeat:
SET TIMESTAMP=1;
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;

Suggested fix:
"Time": The time in seconds that the thread has been in its current state. For a slave SQL thread, the value is the number of seconds between the timestamp of the last replicated event and the real time of the slave machine. 
http://dev.mysql.com/doc/refman/5.6/en/show-processlist.html

Don't allow anything but the slave SQL thread to use SET TIMESTAMP, or (likely better) fix SHOW PROCESSLIST (show the correct duration/Time) for threads that are not SQL slave threads.
[21 Sep 2014 22:54] Roel Van de Paar
.
[22 Sep 2014 6:07] Shane Bester
I agree with this somewhat (as an S3/S4 at best).

At least from my perspective it leads to long query killer incorrectly terminating threads that play around with timestamp variable. 

Also, another effect is the uptime status variable lies:

mysql> select * from information_schema.processlist where id=connection_id()\G
*************************** 1. row ***************************
     ID: 13
   USER: root
   HOST:
     DB: NULL
COMMAND: Query
   TIME: 0
  STATE: executing
   INFO: select * from information_schema.processlist where id=connection_id()
1 row in set (0.00 sec)

mysql> show status like '%uptime%';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| Uptime                    | 30405 |
| Uptime_since_flush_status | 30405 |
+---------------------------+-------+
2 rows in set (0.00 sec)

mysql> set timestamp=1;
Query OK, 0 rows affected (0.00 sec)

mysql> show status like '%uptime%';
+---------------------------+----------------------+
| Variable_name             | Value                |
+---------------------------+----------------------+
| Uptime                    | 18446744072298216299 |
| Uptime_since_flush_status | 18446744072298216299 |
+---------------------------+----------------------+
2 rows in set (0.00 sec)

mysql> select * from information_schema.processlist where id=connection_id()\G
*************************** 1. row ***************************
     ID: 13
   USER: root
   HOST:
     DB: NULL
COMMAND: Query
   TIME: 1411365733
  STATE: executing
   INFO: select * from information_schema.processlist where id=connection_id()
1 row in set (0.00 sec)

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.039 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.