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: | |
| Category: | MySQL Server: Connection Handling | Severity: | S2 (Serious) |
| Version: | 5.6 | OS: | Any |
| Assigned to: | CPU Architecture: | Any |
[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.
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.