Bug #82663 mysqld hangs when accessing Performance Schema tables
Submitted: 20 Aug 2016 5:12 Modified: 26 Aug 2016 2:24
Reporter: Roel Van de Paar (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Performance Schema Severity:S1 (Critical)
Version:5.7.13, 5.7.14 OS:Any
Assigned to: Marc Alff CPU Architecture:Any

[20 Aug 2016 5:12] Roel Van de Paar
Description:
mysql> show processlist;
+----+------+-----------+------+---------+------+----------------+------------------------------------------------------------+-----------+---------------+
| Id | User | Host      | db   | Command | Time | State          | Info                                                       | Rows_sent | Rows_examined |
+----+------+-----------+------+---------+------+----------------+------------------------------------------------------------+-----------+---------------+
|  2 | root | localhost | test | Query   |  101 | Opening tables | SELECT * FROM performance_schema.replication_group_members |         0 |             0 |
|  3 | root | localhost | test | Query   |    0 | starting       | show processlist                                           |         0 |             0 |
+----+------+-----------+------+---------+------+----------------+------------------------------------------------------------+-----------+---------------+
2 rows in set (0.00 sec)

How to repeat:
DROP DATABASE transforms;CREATE DATABASE transforms;DROP DATABASE test;CREATE DATABASE test;USE test;
CREATE TEMPORARY TABLE IF NOT EXISTS t3 (c1 INT AUTO_INCREMENT PRIMARY KEY,c2 CHAR(254) BINARY CHARACTER SET "Binary" COLLATE "Binary",c3 VARCHAR(1025)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS t3 (c1 CHAR(1) PRIMARY KEY,c2 TIMESTAMP(1),c3 NUMERIC(10,10) UNSIGNED) ENGINE=MRG_MyISAM;
DROP TABLE t3;
LOCK TABLES t3 AS a3 LOW_PRIORITY WRITE, t3 AS a1 READ LOCAL;
TRUNCATE t3;
SELECT * FROM performance_schema.replication_group_members;     # <-------- mysqld will hang here (5.7.13) in 'opening tables'
SELECT * FROM performance_schema.events_stages_history WHERE EVENT_NAME LIKE '%wsrep%';
SELECT * FROM performance_schema.replication_group_member_stats;
SELECT * FROM performance_schema.events_stages_history_long;
SHOW CHARACTER SET LIKE '%e';
[20 Aug 2016 8:20] Shane Bester
Verified on 5.7.  The query can be killed fortunately.
Call stack during hang:

00 mysqld_debug!DoTrace [.\dbug\dbug.c @ 1913]
01 mysqld_debug!_db_return_ [.\dbug\dbug.c @ 1296]
02 mysqld_debug!release_table_share [.\sql\sql_base.cc @ 967]
03 mysqld_debug!open_table [.\sql\sql_base.cc @ 3500]
04 mysqld_debug!open_and_process_table [.\sql\sql_base.cc @ 5107]
05 mysqld_debug!open_tables [.\sql\sql_base.cc @ 5716]
06 mysqld_debug!open_tables_for_query [.\sql\sql_base.cc @ 6493]
07 mysqld_debug!execute_sqlcom_select [.\sql\sql_parse.cc @ 5114]
08 mysqld_debug!mysql_execute_command [.\sql\sql_parse.cc @ 2756]
09 mysqld_debug!mysql_parse [.\sql\sql_parse.cc @ 5559]
0a mysqld_debug!dispatch_command [.\sql\sql_parse.cc @ 1429]
0b mysqld_debug!do_command [.\sql\sql_parse.cc @ 995]
0c mysqld_debug!handle_connection [.\connection_handler_per_thread.cc @ 300]
0d mysqld_debug!pfs_spawn_thread [.\storage\perfschema\pfs.cc @ 2190]
0e mysqld_debug!win_thread_start [.\mysys\my_thread.c @ 37]
[20 Aug 2016 8:22] Shane Bester
Current trunk and 5.6 are NOT affected.
[22 Aug 2016 7:21] Roel Van de Paar
Thanks Shane. How did you get the hang stacktrace?
[22 Aug 2016 7:24] Shane Bester
On windows I use this to get gdb's thread apply all bt output:

windbg.exe -c "~*kn 50 ;  .detach ; q" -Q -logo stacks.txt -pn mysqld-debug.exe
[22 Aug 2016 7:26] Shane Bester
fwiw I always manually edit my outputs so they fit into the small width without wrapping and becoming unreadable at a glance.
[22 Aug 2016 7:34] Roel Van de Paar
Very nice! Thank you!
[25 Aug 2016 8:46] Roel Van de Paar
There's other hangs too

mysql> show processlist;
+----+------+-----------+------+---------+------+----------------+-----------------------------------------------------------+
| Id | User | Host      | db   | Command | Time | State          | Info                                                      |
+----+------+-----------+------+---------+------+----------------+-----------------------------------------------------------+
|  4 | root | localhost | test | Query   |  124 | Opening tables | SELECT * FROM performance_schema.user_variables_by_thread |
|  5 | root | localhost | test | Query   |    0 | starting       | show processlist                                          |
+----+------+-----------+------+---------+------+----------------+-----------------------------------------------------------+
2 rows in set (0.00 sec)
[25 Aug 2016 8:51] Roel Van de Paar
mysql> show full processlist;
+----+------+-----------+------+---------+------+----------------+-----------------------------------------------------------------+
| Id | User | Host      | db   | Command | Time | State          | Info                                                            |
+----+------+-----------+------+---------+------+----------------+-----------------------------------------------------------------+
|  2 | root | localhost | test | Query   |    3 | Opening tables | SELECT * FROM performance_schema.objects_summary_global_by_type |
|  4 | root | localhost | test | Query   |    0 | starting       | show full processlist                                           |
+----+------+-----------+------+---------+------+----------------+-----------------------------------------------------------------+
2 rows in set (0.00 sec)
[25 Aug 2016 8:52] Roel Van de Paar
mysql> show full processlist;
+----+------+-----------+------+---------+------+----------------+-----------------------------------------------------------------------+
| Id | User | Host      | db   | Command | Time | State          | Info                                                                  |
+----+------+-----------+------+---------+------+----------------+-----------------------------------------------------------------------+
|  2 | root | localhost | test | Query   |    2 | Opening tables | SELECT * FROM performance_schema.events_statements_summary_by_program |
|  4 | root | localhost | test | Query   |    0 | starting       | show full processlist                                                 |
+----+------+-----------+------+---------+------+----------------+-----------------------------------------------------------------------+
2 rows in set (0.01 sec)
[25 Aug 2016 8:55] Roel Van de Paar
As a secondary (and unreduced) testcase (produces all examples above, go through them with CTRL+C) you can use this hang.sql

Attachment: hang.sql (application/octet-stream, text), 287.28 KiB.

[25 Aug 2016 8:57] Roel Van de Paar
If reduction for second case is helpful, you can use reducer.sh (in https://github.com/Percona-QA/percona-qa). If info on how to do that is needed, let me know.
[26 Aug 2016 2:24] Roel Van de Paar
Bug is seen in 5.7.14 as well. Stops QA runs.
[27 Aug 2016 7:04] Shane Bester
fwiw, this reminds me of my old bug (internal):
Bug 23044286 - MDL HANGUP & CPU HOG WHEN SELECTING FROM P_S AFTER FAILED DDL