Search code examples
mysqlupdatingrecordsdel

Information schema process list and updating state

Well here is my question, I messed up yesterday and made a delete query on my database. The messing up is not the delete part, but the fact that i didnt realized it was 424 million records. I keeping track on the query with the information schema but i would like to know what the "updating" state stands by. What is it doing right now, deleting or what?

Here is the get

"COMMAND": Query "STATE": "updating" "INFO": "delete from posicion where fechahora between '2012-12-12 00:00:00' and '2014-12-15 00:00:00'"

Solution

  • It looks to me like your delete query is still running.

    You can double check this by issuing this command, from some other client besides the one from which you issued the delete query.

    SHOW FULL PROCESSLIST

    This will show your active processes and what they are doing. Your DELETE query may be among them.

    If you

    1. don't want to delete those rows.
    2. are using InnoDB for your posicion table, and
    3. have not yet finished running the DELETE query

    you can look at the processlist to get the id of your delete operation, then issue the command

    KILL QUERY id

    InnoDB should roll back the in-progress delete and leave your table as it was before you started the DELETE .

    Good luck!