Symptoms
-
Any operation on a database loads for a long time in Plesk at Domains > example.com > Databases and then fails with:
PLESK_INFO: 504 Gateway Timeout error
-
The website that is using this database is not accessible with the following error message in web-browser:
PLESK_INFO: 504 Gateway Timeout error
-
The following error message may appear at Domains > example.com > Logs:
upstream timed out (110: Connection timed out) while reading response header from upstream
-
phpMyAdmin loads for a long time and fails when accessing via Plesk > Domains > example.com > Databases >Â phpMyAdmin under database “database_for_examplecom“;
-
When creating a manual database dump or switching to this database in MySQL, the operation hangs;
-
There are database processes listed in the process list of MySQL with the status “Waiting for table metadata lock” or “Waiting for table level lock”:
# plesk db “show processlist”
| Id | User | Host | db | Command | Time | State | Info |
| 7048 | example | localhost | example_db | Query | 42822 | Opening tables | SHOW SESSION VARIABLES LIKE ‘FOREIGN_KEY_CHECKS’
| 10741 | example | localhost | example | Query | 19415 | Waiting for table metadata lock | SELECT COUNT(*) FROM `example_db`.`example_table`OR
| Id | User | Host | db | Command | Time | State | Info |
| 7048 | example | localhost | example_db | Query | 42822 | Opening tables | SHOW SESSION VARIABLES LIKE 'FOREIGN_KEY_CHECKS'
| 10741 | example | localhost | example_db | Query | 19415 | Waiting for table level lock | SELECT COUNT(*) FROM `example_db`.`example_table` - The same processes are shown at Tools & Settings > MySQL Process List (Beta);
-
A number of stuck requests present in MySQL list of process for this database, for example 50:<7p>
# plesk db “SELECT count(*) FROM INFORMATION_SCHEMA.PROCESSLIST where db=’database_for_examplecom’;”
+———-+
| count(*) |
+———-+
|Â Â Â Â 50 |
+———-+
Cause
The database has been locked and the process is unable to finish.
Resolution
Resolution via Plesk
-
Warning: Pay attention to query type: it is safe to kill SELECT/SHOW queries while killing ALTER/UPDATE might cause huge collateral damage.
Kill all ‘stuck’ processes at Tools & Settings > MySQL Process List (Beta)
-
If the previous step did not help, restart MySQL using the SSH method.
Resolution via SSH
-
Connect to the server via SSH.
-
Restart the MySQL server:
# service mysql restart
Note: Contact the website developer in order to improve website code.