MySQL delete hanging-CodePudding

I am running a basic delete from mysql. There are no foreign keys, nothing is accessing the table other than the delete I am running.

delete from database.table 
where filename='roger.doc' 
and aisle='whatever' 
and filetime='2022-07-09 18:37:13.645050';

MYSQL is just hanging, how can I get past this or force a delete?

Error I am getting is

Lock wait timeout exceeded; try restarting transaction`.

I think the lock wait is 3 minutes. However there are no locks using show engine innodb status\G the latest deadlock was 10 days ago on a completely different db and table.

I have added the primary key which is the filetime and an additional key which is aisle and the delete still hangs. I have to manually delete the rows before then the rows after then delete the problem row and that has worked. But the delete script just hangs.

Using explain gives me the following.

1 | DELETE      | data_management | NULL       | range | PRIMARY,aisle_index,date_index | PRIMARY | 7       | const |    1 |   100.00 | Using where |

CodePudding user response：

This seems to be related to replication. One of the nodes is refusing to accept the transactions from lead node. So it is just halting the system.

CodePudding user response：

Some other thread on the system is holding a lock on the table. This can be caused by a number of issues. You should run SHOW FULL PROCESSLIST; to find the thread that has a lock on the table. Once you identify it (it maybe a thread started by another user - either directly or as part of a stored program with a definer security profile), you will need to kill that thread.