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 (OCA) 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)