SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction
Sometimes deadlock issues can be caused on the database.
This might happen if you are running a lot of custom scripts and killing the scripts before the database connection gets chance to close
If you login to MySQL from CLI and run the command
SHOW PROCESSLIST;
you will get the following output
+———+—————–+——————-+—————–+———+——+——-+——————+———–+—————+———–+
| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined | Rows_read |
+———+—————–+——————-+—————–+———+——+——-+——————+———–+—————+———–+
| 6794372 | db_user| 111.11.0.65:21532 | db_name| Sleep | 3800 | | NULL | 0 | 0 | 0 |
| 6794475 | db_user| 111.11.0.65:27488 | db_name| Sleep | 3757 | | NULL | 0 | 0 | 0 |
| 6794550 | db_user| 111.11.0.65:32670 | db_name| Sleep | 3731 | | NULL | 0 | 0 | 0 |
| 6794797 | db_user| 111.11.0.65:47424 | db_name | Sleep | 3639 | | NULL | 0 | 0 | 0 |
| 6794909 | db_user| 111.11.0.65:56029 | db_name| Sleep | 3591 | | NULL | 0 | 0 | 0 |
| 6794981 | db_user| 111.11.0.65:59201 | db_name| Sleep | 3567 | | NULL | 0 | 0 | 0 |
| 6795096 | db_user| 111.11.0.65:2390 | db_name| Sleep | 3529 | | NULL | 0 | 0 | 0 |
| 6795270 | db_user| 111.11.0.65:10125 | db_name | Sleep | 3473 | | NULL | 0 | 0 | 0 |
| 6795402 | db_user| 111.11.0.65:18407 | db_name| Sleep | 3424 | | NULL | 0 | 0 | 0 |
| 6795701 | db_user| 111.11.0.65:35679 | db_name| Sleep | 3330 | | NULL | 0 | 0 | 0 |
| 6800436 | db_user| 111.11.0.65:57815 | db_name| Sleep | 1860 | | NULL | 0 | 0 | 0 |
| 6806227 | db_user| 111.11.0.67:20650 | db_name| Sleep | 188 | | NULL | 1 | 0 | 0 |
| 6806589 | db_user| 111.11.0.65:36618 | db_name| Query | 0 | NULL | SHOW PROCESSLIST | 0 | 0 | 0 |
| 6806742 | db_user| 111.11.0.75:38717 | db_name| Sleep | 0 | | NULL | 0 | 0 | 0 |
| 6806744 | db_user| 111.11.0.75:38819 | db_name| Sleep | 0 | | NULL | 61 | 61 | 61 |
+———+—————–+——————-+—————–+———+——+——-+——————+———–+—————+———–+
15 rows in set (9990.00 sec)
You can see as an example
6794372 the command is sleep and time is 3800. This is preventing other operations
These processes should be killed 1 by 1 using the command.
KILL 6794372;
Once you have killed all the sleeping connections, things should start working as normal again