MySQL Processlist -- View the SQL status of session execution

For MySQL version 5.7, you can use the sys.session view to view the last SQL executed by the session:

SELECT * 
FROM sys.session 
WHERE CONN_ID = 20036 \G

The sys.session view is associated with sys.processlist, and sys.processlist uses `performance_schema'. `events_statements_current'.

If you want to query the SQL situation that a session has been executed since it was opened, you need to use the following tables:

performance_schema.events_statements_current
performance_schema.events_statements_history
performance_schema.events_transactions_history_long

The three tables above record the recent execution of SQL by MySQL server, but the fields representing time in the three tables above are expressed in picoseconds and can not be converted directly using FROM_UNIXTIME (instead of starting from 1970-01-00:00 or 0000-01-01:00:00, the starting baseline of each MySQL instance is different).

Using the following script, you can see the execution of SQL on a particular session:

SELECT @dt_ts:=UNIX_TIMESTAMP(NOW());
SELECT 
@dt_timer:=MAX(SH.TIMER_START)
FROM performance_schema.threads AS T1
INNER JOIN performance_schema.events_statements_history AS SH
ON T1.`THREAD_ID`=SH.`THREAD_ID`
WHERE T1.PROCESSLIST_ID=CONNECTION_ID();

SELECT 
SH.`SQL_TEXT`,
FROM_UNIXTIME(@dt_ts-CAST((@dt_timer-SH.TIMER_START)/1000000000000 AS SIGNED)) AS start_time,
FROM_UNIXTIME(@dt_ts-CAST((@dt_timer+SH.TIMER_END)/1000000000000 AS SIGNED)) AS end_time,
(SH.TIMER_END-SH.TIMER_START)/1000000000000 AS used_seconds,
SH.ROWS_AFFECTED AS affected_rows,
SH.ROWS_SENT AS send_rows
FROM performance_schema.threads AS T1
INNER JOIN performance_schema.events_statements_history AS SH
ON T1.`THREAD_ID`=SH.`THREAD_ID`
WHERE T1.PROCESSLIST_ID=946364
AND SH.TIMER_START<@dt_timer
ORDER BY SH.TIMER_START ASC;

The output effect is as follows:

+------------------------------------+---------------------+---------------------+--------------+---------------+-----------+
| SQL_TEXT                           | start_time          | end_time            | used_seconds | affected_rows | send_rows |
+------------------------------------+---------------------+---------------------+--------------+---------------+-----------+
| select sleep(5)                    | 2019-07-29 19:59:55 | 2019-07-29 20:00:00 |       5.0003 |             0 |         1 |
| insert into tb001(c1,c2)select 2,4 | 2019-07-29 20:01:26 | 2019-07-29 20:01:26 |       0.0002 |             1 |         0 |
| insert into tb001(c1,c2)select 2,4 | 2019-07-29 20:04:34 | 2019-07-29 20:04:34 |       0.0001 |             1 |         0 |
+------------------------------------+---------------------+---------------------+--------------+---------------+-----------+

 

Expansion: MySQL Transaction -- View the SQL for uncommitted transaction execution

Keywords: PHP Session SQL MySQL

Added by titeroy on Wed, 31 Jul 2019 03:35:29 +0300