Bug #92387 SHOW SESSION VARIABLES stuck in state "Opening tables"
Submitted: 12 Sep 2018 14:10 Modified: 15 Feb 15:49
Reporter: Tobias Grave Email Updates:
Status: Analyzing Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.7 OS:Ubuntu (16.04)
Assigned to: Umesh Shastry CPU Architecture:x86 (x86_64)

[12 Sep 2018 14:10] Tobias Grave
Description:
The popular MySQL database manager phpMyAdmin issues the command "SHOW SESSION VARIABLES LIKE 'FOREIGN_KEY_CHECKS'" after connecting to a database. This command is usually executed as expected, but sometimes, under unknown circumstances, the corresponding MySQL proccess remains stuck in state "Opening tables" forever, and all subsequent queries for the affected database are blocked (state "Waiting for table metadata lock"). The stuck session variable process has to be killed in order to restore regular access to the database.

I usually see this issue a few times a week on a database server hosting about a thousand databases for common web applications like WordPress.

I use the latest 64 bit MySQL 5.7 version for Ubuntu 16.04 (5.7.23-1ubuntu16.04).

How to repeat:
I was unable to reproduce this issue in a testing environment, it seems to be a race condition that is only triggered under specific circumstances.
[12 Sep 2018 17:04] Miguel Solorzano
Thank you for the bug report. We need a repeatable test case so development could fix the issue. Comment here when you will able to provide the case so this bug report will open again. Thanks.
[27 Sep 2018 8:37] Paolo Ciucani
Hi,
I am able to reproduce this issue in my enviroment (MySQL 5.7.23-0ubuntu0.18.04.1, phpmyadmin 4.8.3).
I have this issue while importing an SQL file containing an export from mysqldump command. The same file imported by 'mysql' command works fine.

phpMyAdmin stucks until I restart mysql server and then phpMyAdmin shows a connection error and a Backtrace like this:

-------------------------------------
./libraries/classes/Dbi/DbiMysqli.php#213: mysqli_query(
,
string 'SET FOREIGN_KEY_CHECKS = ON;',
integer 0,
)
./libraries/classes/DatabaseInterface.php#319: PhpMyAdmin\Dbi\DbiMysqli->realQuery(
string 'SET FOREIGN_KEY_CHECKS = ON;',
,
integer 0,
)
./libraries/classes/DatabaseInterface.php#169: PhpMyAdmin\DatabaseInterface->tryQuery(
string 'SET FOREIGN_KEY_CHECKS = ON;',
integer 256,
integer 0,
boolean true,
)
./libraries/classes/DatabaseInterface.php#1390: PhpMyAdmin\DatabaseInterface->query(
string 'SET FOREIGN_KEY_CHECKS = ON;',
integer 256,
)
./libraries/classes/Util.php#2961: PhpMyAdmin\DatabaseInterface->setVariable(
string 'FOREIGN_KEY_CHECKS',
string 'ON',
)
./libraries/classes/Sql.php#2257: PhpMyAdmin\Util::handleDisableFKCheckCleanup(boolean true)
./import.php#736: PhpMyAdmin\Sql->executeQueryAndGetQueryResponse(
array,
boolean false,
string 'sviluppo',
string '',
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
string 'db_import.php',
string './themes/pmahomme/img/',
NULL,
NULL,
NULL,
string '/*!40000 ALTER TABLE `xxxxxxx` DISABLE KEYS */',
NULL,
NULL,
)
Warning in ./libraries/classes/Dbi/DbiMysqli.php#213
mysqli_query(): Error reading result set's header

----------------------------------

so I think the problem is right before "INSERT INTO" row: /*!40000 ALTER TABLE `xxxxxxx` DISABLE KEYS */
the foreign key checks is disabled at the beginning of the dump file: /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */
[17 Dec 2018 14:51] Arkadiusz Miskiewicz
Here is sometimes stuck on 5.7.22, for example one case and show processlist:

|      24 | bmuser    | db05:56092 | mysql         | Query   | 4381 | Waiting for schema metadata lock | DROP DATABASE `abc_2`                       |         0 |             0 |
| 8861721 | abc_2    | dbadmin:43028      | abc_2    | Query   | 6111 | Opening tables                   | SHOW SESSION VARIABLES LIKE 'FOREIGN_KEY_CHECKS' |         0 |             0 |
[17 Dec 2018 15:41] Shane Bester
When this happens, please collect stack traces of the mysqld process, and indicate which version of mysqld they're from:

gdb -ex "set pagination 0" -ex "thread apply all bt" --batch -p $(pidof mysqld)
[9 Jan 12:22] Nikolay Vizovitin
Full gdb output for MySQL w/ hanging process

Attachment: mysql-opening-tables-gdb.txt (text/plain), 20.86 KiB.

[9 Jan 12:23] Nikolay Vizovitin
I have a container VM with Ubuntu 16.04 where this issue is reproduced (with the same use case).

{code}
mysql> show full processlist;
+-----+------------------+-----------+---------+---------+------+----------------+--------------------------------------------------+
| Id  | User             | Host      | db      | Command | Time | State          | Info                                             |
+-----+------------------+-----------+---------+---------+------+----------------+--------------------------------------------------+
| 104 | admin            | localhost | psa     | Sleep   |  374 |                | NULL                                             |
| 105 | admin            | localhost | psa     | Sleep   |  374 |                | NULL                                             |
| 106 | pma_gsnAmu_CqNnY | localhost | NULL    | Sleep   |   89 |                | NULL                                             |
| 107 | ruslan4          | localhost | ruslan4 | Query   |   68 | Opening tables | SHOW SESSION VARIABLES LIKE 'FOREIGN_KEY_CHECKS' |
| 108 | admin            | localhost | psa     | Query   |    0 | starting       | show full processlist                            |
| 109 | admin            | localhost | psa     | Sleep   |  290 |                | NULL                                             |
| 110 | admin            | localhost | psa     | Sleep   |  290 |                | NULL                                             |
+-----+------------------+-----------+---------+---------+------+----------------+--------------------------------------------------+
7 rows in set (0.00 sec)

mysql> STATUS;
--------------
mysql  Ver 14.14 Distrib 5.7.24, for Linux (x86_64) using  EditLine wrapper

Connection id:          108
Current database:       psa
Current user:           admin@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.7.24-0ubuntu0.16.04.1 (Ubuntu)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    utf8
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:            /var/run/mysqld/mysqld.sock
Uptime:                 24 min 37 sec

Threads: 7  Questions: 7081  Slow queries: 0  Opens: 2818  Flush tables: 1  Open tables: 359  Queries per second avg: 4.794
--------------

{code}

(see the attached file mysql-opening-tables-gdb.txt for full gdb output)
{code}
# gdb -ex "set pagination 0" -ex "thread apply all bt" --batch -p $(pidof mysqld)
...

Thread 32 (Thread 0x7f9368703700 (LWP 1154)):
#0  0x0000000000e8ee77 in my_getsystime ()
#1  0x0000000000b9f288 in MDL_context::acquire_lock(MDL_request*, unsigned long) ()
#2  0x0000000000bf4af1 in open_table(THD*, TABLE_LIST*, Open_table_context*) ()
#3  0x0000000000bfb9db in open_tables(THD*, TABLE_LIST**, unsigned int*, unsigned int, Prelocking_strategy*) ()
#4  0x0000000000bfc189 in open_tables_for_query(THD*, TABLE_LIST*, unsigned int) ()
#5  0x0000000000753b35 in ?? ()
#6  0x0000000000c5195b in mysql_execute_command(THD*, bool) ()
#7  0x0000000000c53ead in mysql_parse(THD*, Parser_state*) ()
#8  0x0000000000c54fea in dispatch_command(THD*, COM_DATA const*, enum_server_command) ()
#9  0x0000000000c564a7 in do_command(THD*) ()
#10 0x0000000000d18848 in handle_connection ()
#11 0x0000000000ec9834 in pfs_spawn_thread ()
#12 0x00007f938a2fc6ba in start_thread (arg=0x7f9368703700) at pthread_create.c:333
#13 0x00007f938979141d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109

...
{code}

Thread 32 seems to correspond to the hanging MySQL process. It is stuck in 
  0x0000000000bfb9db in open_tables(THD*, TABLE_LIST**, unsigned int*, unsigned int, Prelocking_strategy*) ()
(other calls above this function in stack trace tend to change with time - it continually attempts to acquire a table lock and recover from failures).

Please respond if you need any additional information.
[10 Jan 9:04] Nikolay Vizovitin
I've attached a minimized DB dump with only 1 table that could be used to reproduce the issue (visible only to devs).

I've been able to reproduce the issue on the following configuration:
1. Ubuntu 16.04
2. MySQL 5.7.24-0ubuntu0.16.04.1
3. phpMyAdmin 4.8.3 and 4.8.4

# dpkg -l | egrep '\<mysql-|\<libmysql'
ii  libdbd-mysql-perl                  4.033-1ubuntu0.1                        amd64        Perl5 database interface to the MySQL database
ii  libmysqlclient-dev                 5.7.24-0ubuntu0.16.04.1                 amd64        MySQL database development files
ii  libmysqlclient20:amd64             5.7.24-0ubuntu0.16.04.1                 amd64        MySQL database client library
ii  mysql-client-5.7                   5.7.24-0ubuntu0.16.04.1                 amd64        MySQL database client binaries
ii  mysql-client-core-5.7              5.7.24-0ubuntu0.16.04.1                 amd64        MySQL database core client binaries
ii  mysql-common                       5.7.24-0ubuntu0.16.04.1                 all          MySQL database common files, e.g. /etc/mysql/my.cnf
ii  mysql-server                       5.7.24-0ubuntu0.16.04.1                 all          MySQL database server (metapackage depending on the latest version)
ii  mysql-server-5.7                   5.7.24-0ubuntu0.16.04.1                 amd64        MySQL database server binaries and system database setup
ii  mysql-server-core-5.7              5.7.24-0ubuntu0.16.04.1                 amd64        MySQL database server binaries
[31 Jan 15:30] Kyle Pinkley
We have had two reports of customer's experiencing this issue as well. 

The first customer reported this early in December 2018. The server is using MySQL 5.7.24 and phpMyAdmin 4.8.3-2 at the time.

The second customer reported this yesterday (January 30, 2019). The server is using MySQL 5.7.25 and phpMyAdmin 4.8.3-3 at the time.
[12 Feb 16:09] Joerg Kost
MySQL stack trace

Attachment: strace (application/octet-stream, text), 22.46 KiB.

[12 Feb 16:10] Joerg Kost
Saw this too with 5.7.25-0ubuntu0.16.04.2 and phpmyadmin 4.8.5.
[12 Feb 16:26] Miguel Solorzano
Thank you for the feedback I will try with the dump provided.
[12 Feb 16:26] Miguel Solorzano
Thank you for the feedback I will try with the dump provided.
[12 Feb 16:49] Miguel Solorzano
I couldn't repeat with mysql command client and WorkBench, then the issue is with PHPAdmin.
[15 Feb 12:38] Joerg Kost
Did you try out the dumps from  Nikolay?

The workaround to restart the database is indeed a MySQL issue / bug, not a phpmyadmin thing. Maybe it is triggered by phpmyadmin.

https://support.plesk.com/hc/en-us/articles/360004375313-phpMyAdmin-loads-long-time-and-fa...
[15 Feb 15:47] Miguel Solorzano
Yes I tried the dump provided and checked with mysql client and WorkBench, if there are specific circumstances to reproduce it I will appreciate for that info. Thanks.
[15 Feb 15:47] Miguel Solorzano
Yes I tried the dump provided and checked with mysql client and WorkBench, if there are specific circumstances to reproduce it I will appreciate for that info. Thanks.
[8 Mar 20:27] Kyle Pinkley
Had another report of this yesterday with a different customer, using phpMyAdmin 4.8.3-7 and MySQL Ver 14.14 Distrib 5.7.25.