MySQL Forums
Forum List  »  InnoDB

DELETE from table does not delete rows
Posted by: Alexandru Badulescu
Date: April 23, 2019 11:23AM


I ran into a very strange situation: a cron job runs every night and is supposed to delete form old data from a table. It turns out that for a few weeks now the query runs, takes a lot of time (as the table kept growing), reports the correct number of affected rows, but it does not actually delete the rows.

Here is the slow-query log for such a query:
# Time: 2019-04-17T03:13:19.714146Z
# User@Host: root[root] @ [] Id: 11005891
# Schema: live_indicators Last_errno: 0 Killed: 0
# Query_time: 11552.742740 Lock_time: 0.000092 Rows_sent: 0 Rows_examined: 16026170 Rows_affected: 10231900
# Bytes_sent: 14
SET timestamp=1555470799;
DELETE FROM `reports_hourly` WHERE (start_date < '2019-03-03 00:00:00');

If I connect run the query from the client (I only ran it with limit, as this is a master and it would make replication lag) and it works ok.

Any idea what might be causing this or where to dig further?

Thank you,

Options: ReplyQuote

Written By
DELETE from table does not delete rows
April 23, 2019 11:23AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.