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:
None 
Category:MySQL Server: Performance Schema Severity:S3 (Non-critical)
Version:5.6.20 OS:Any
Assigned to: Marc Alff CPU Architecture:Any

[22 Oct 2014 23:21] Kolbe Kegel
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.
[24 Oct 2014 11:01] Umesh Shastry
Hello Kolbe,

Thank you for the report.
Confirmed this on 5.6.22/5.7.6.

Thanks,
Umesh
[24 Oct 2014 11:03] Umesh Shastry
//

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] Umesh Shastry
// 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.