| Bug #74517 | thread/sql/main doesn't change state/info after startup | ||
|---|---|---|---|
| Submitted: | 22 Oct 2014 23:21 | Modified: | 30 Apr 2015 12:57 |
| Reporter: | Kolbe Kegel | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Performance Schema | Severity: | S3 (Non-critical) |
| Version: | 5.6.20 | OS: | Any |
| Assigned to: | Marc ALFF | CPU Architecture: | Any |
[24 Oct 2014 11:01]
MySQL Verification Team
Hello Kolbe, Thank you for the report. Confirmed this on 5.6.22/5.7.6. Thanks, Umesh
[24 Oct 2014 11:03]
MySQL Verification Team
//
mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name | Value |
+-------------------------+---------------------------------------------------------+
| innodb_version | 5.6.22 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.6.22-enterprise-commercial-advanced |
| version_comment | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+-------------------------+---------------------------------------------------------+
7 rows in set (0.00 sec)
mysql> select * from performance_schema.threads where thread_id=1\G
*************************** 1. row ***************************
THREAD_ID: 1
NAME: thread/sql/main
TYPE: BACKGROUND
PROCESSLIST_ID: NULL
PROCESSLIST_USER: NULL
PROCESSLIST_HOST: NULL
PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: NULL
PROCESSLIST_TIME: 4
PROCESSLIST_STATE: System lock
PROCESSLIST_INFO: INTERNAL DDL LOG RECOVER IN PROGRESS
PARENT_THREAD_ID: NULL
ROLE: NULL
INSTRUMENTED: YES
1 row in set (0.00 sec)
..
.
mysql> select * from performance_schema.threads where thread_id=1\G
*************************** 1. row ***************************
THREAD_ID: 1
NAME: thread/sql/main
TYPE: BACKGROUND
PROCESSLIST_ID: NULL
PROCESSLIST_USER: NULL
PROCESSLIST_HOST: NULL
PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: NULL
PROCESSLIST_TIME: 1905
PROCESSLIST_STATE: System lock
PROCESSLIST_INFO: INTERNAL DDL LOG RECOVER IN PROGRESS
PARENT_THREAD_ID: NULL
ROLE: NULL
INSTRUMENTED: YES
1 row in set (0.00 sec)
[24 Oct 2014 11:03]
MySQL Verification Team
// 5.7.6
mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name | Value |
+-------------------------+---------------------------------------------------------+
| innodb_version | 5.7.6 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.7.6-m16-enterprise-commercial-advanced |
| version_comment | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+-------------------------+---------------------------------------------------------+
7 rows in set (0.00 sec)
mysql> select * from performance_schema.threads where thread_id=1\G
*************************** 1. row ***************************
THREAD_ID: 1
NAME: thread/sql/main
TYPE: BACKGROUND
PROCESSLIST_ID: NULL
PROCESSLIST_USER: NULL
PROCESSLIST_HOST: NULL
PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: NULL
PROCESSLIST_TIME: 280
PROCESSLIST_STATE: System lock
PROCESSLIST_INFO: INTERNAL DDL LOG RECOVER IN PROGRESS
PARENT_THREAD_ID: NULL
ROLE: NULL
INSTRUMENTED: YES
1 row in set (0.00 sec)
.
mysql> select * from performance_schema.threads where thread_id=1\G
*************************** 1. row ***************************
THREAD_ID: 1
NAME: thread/sql/main
TYPE: BACKGROUND
PROCESSLIST_ID: NULL
PROCESSLIST_USER: NULL
PROCESSLIST_HOST: NULL
PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: NULL
PROCESSLIST_TIME: 3027
PROCESSLIST_STATE: System lock
PROCESSLIST_INFO: INTERNAL DDL LOG RECOVER IN PROGRESS
PARENT_THREAD_ID: NULL
ROLE: NULL
INSTRUMENTED: YES
1 row in set (0.00 sec)
[30 Apr 2015 12:57]
Paul DuBois
Noted in 5.6.25, 5.7.8, 5.8.0 changelogs. In the Performance Schema threads table, the PROCESSLIST_STATE and PROCESSLIST_INFO values did not change for the thread/sql/main main thread instrument as the thread state changed.
[24 Jun 2015 4:55]
Laurynas Biveinis
commit 9ef936eeae9c6d44fb1f0e421b8861509af71ebd
Author: Mayank Prasad <mayank.prasad@oracle.com>
Date: Wed Apr 29 10:55:09 2015 +0530
Bug #19887143 : THREAD/SQL/MAIN DOESN'T CHANGE STATE/INFO AFTER STARTUP
Issue:
State and Info of main thread was not changing. It was because of
not resetting its state/info accordingly while doing some startup
operation when mysql server starts.
Fix:
Reset the state/info for main thread so that it shows correct state.
Description: Thread #1, thread/sql/main, as shown in performance_schema.threads, appears to have its state permanently set as "System lock" and its "info" as "INTERNAL DDL LOG RECOVER IN PROGRESS". mysql 5.6.20-log (root) [test] 1> select * from performance_schema.threads where thread_id=1\G *************************** 1. row *************************** THREAD_ID: 1 NAME: thread/sql/main TYPE: BACKGROUND PROCESSLIST_ID: NULL PROCESSLIST_USER: NULL PROCESSLIST_HOST: NULL PROCESSLIST_DB: NULL PROCESSLIST_COMMAND: NULL PROCESSLIST_TIME: 809 PROCESSLIST_STATE: System lock PROCESSLIST_INFO: INTERNAL DDL LOG RECOVER IN PROGRESS PARENT_THREAD_ID: NULL ROLE: NULL INSTRUMENTED: YES 1 row in set (0.00 sec) How to repeat: As far as I can tell, this is what shows up when you start the server and it seems to be what shows up indefinitely as the server runs. Suggested fix: On startup, when this thread is initialized, its state and info should be set to empty/null/blank if the thread is indeed not doing any work.