Description:
When a connection executes a stored procedure, the sys.processlist and sys.session views include two (or more) rows for the connection. For example:
mysql> SELECT thd_id, conn_id, current_statement
FROM sys.session
WHERE thd_id = 55;
+--------+---------+--------------------+
| thd_id | conn_id | current_statement |
+--------+---------+--------------------+
| 55 | 18 | DO SLEEP(in_sleep) |
| 55 | 18 | DO SLEEP(in_sleep) |
+--------+---------+--------------------+
2 rows in set (0.1183 sec)
How to repeat:
1. Create a stored procedure
mysql> CREATE PROCEDURE testproc(in_sleep INT UNSIGNED)
DO SLEEP(in_sleep);
Query OK, 0 rows affected (0.11 sec)
2. Get the thread id for the connection:
mysql> SELECT sys.ps_thread_id(NULL);
+------------------------+
| sys.ps_thread_id(NULL) |
+------------------------+
| 55 |
+------------------------+
1 row in set (0.00 sec)
3. Invoke the procedure:
mysql> CALL testproc(10);
4. While the procedure is executing, query the sys.processlist or sys.session view - and the performance_schema.events_statements_current table:
mysql> SELECT thd_id, conn_id, current_statement
FROM sys.session
WHERE thd_id = 55;
+--------+---------+--------------------+
| thd_id | conn_id | current_statement |
+--------+---------+--------------------+
| 55 | 18 | DO SLEEP(in_sleep) |
| 55 | 18 | DO SLEEP(in_sleep) |
+--------+---------+--------------------+
2 rows in set (0.1183 sec)
mysql> SELECT THREAD_ID, EVENT_ID, SQL_TEXT, NESTING_EVENT_ID,
NESTING_EVENT_TYPE, NESTING_EVENT_LEVEL
FROM performance_schema.events_statements_current
WHERE THREAD_ID = 55\G
*************************** 1. row ***************************
THREAD_ID: 55
EVENT_ID: 130
SQL_TEXT: CALL testproc(10)
NESTING_EVENT_ID: NULL
NESTING_EVENT_TYPE: NULL
NESTING_EVENT_LEVEL: 0
*************************** 2. row ***************************
THREAD_ID: 55
EVENT_ID: 131
SQL_TEXT: DO SLEEP(in_sleep)
NESTING_EVENT_ID: 130
NESTING_EVENT_TYPE: STATEMENT
NESTING_EVENT_LEVEL: 1
2 rows in set (0.0014 sec)
Suggested fix:
Either:
* Show the two different queries in progress for the thread (see the events_statements_current output)
* Only show the active query
Description: When a connection executes a stored procedure, the sys.processlist and sys.session views include two (or more) rows for the connection. For example: mysql> SELECT thd_id, conn_id, current_statement FROM sys.session WHERE thd_id = 55; +--------+---------+--------------------+ | thd_id | conn_id | current_statement | +--------+---------+--------------------+ | 55 | 18 | DO SLEEP(in_sleep) | | 55 | 18 | DO SLEEP(in_sleep) | +--------+---------+--------------------+ 2 rows in set (0.1183 sec) How to repeat: 1. Create a stored procedure mysql> CREATE PROCEDURE testproc(in_sleep INT UNSIGNED) DO SLEEP(in_sleep); Query OK, 0 rows affected (0.11 sec) 2. Get the thread id for the connection: mysql> SELECT sys.ps_thread_id(NULL); +------------------------+ | sys.ps_thread_id(NULL) | +------------------------+ | 55 | +------------------------+ 1 row in set (0.00 sec) 3. Invoke the procedure: mysql> CALL testproc(10); 4. While the procedure is executing, query the sys.processlist or sys.session view - and the performance_schema.events_statements_current table: mysql> SELECT thd_id, conn_id, current_statement FROM sys.session WHERE thd_id = 55; +--------+---------+--------------------+ | thd_id | conn_id | current_statement | +--------+---------+--------------------+ | 55 | 18 | DO SLEEP(in_sleep) | | 55 | 18 | DO SLEEP(in_sleep) | +--------+---------+--------------------+ 2 rows in set (0.1183 sec) mysql> SELECT THREAD_ID, EVENT_ID, SQL_TEXT, NESTING_EVENT_ID, NESTING_EVENT_TYPE, NESTING_EVENT_LEVEL FROM performance_schema.events_statements_current WHERE THREAD_ID = 55\G *************************** 1. row *************************** THREAD_ID: 55 EVENT_ID: 130 SQL_TEXT: CALL testproc(10) NESTING_EVENT_ID: NULL NESTING_EVENT_TYPE: NULL NESTING_EVENT_LEVEL: 0 *************************** 2. row *************************** THREAD_ID: 55 EVENT_ID: 131 SQL_TEXT: DO SLEEP(in_sleep) NESTING_EVENT_ID: 130 NESTING_EVENT_TYPE: STATEMENT NESTING_EVENT_LEVEL: 1 2 rows in set (0.0014 sec) Suggested fix: Either: * Show the two different queries in progress for the thread (see the events_statements_current output) * Only show the active query