| 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]
MySQL Verification Team
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)
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.