Learn More
Resend Email
6
Bad data loss story?
Week 98 Group Rant
grayfox
7y

This one was thanks to the beloved MariaDB.

I needed to update a record with id = 12345
I copied the id to the clipboard.
Then proceded to type:

UPDATE table SET field = NULL WHERE Ctrl+v

So it ended up
UPDATE table SET field = NULL WHERE 12345

I forgot to type "id = " after the "WHERE".

MariaDB says "OK, after the WHERE any number means TRUE".

Simple update taking longer than 0.000001 seconds means bad news. And if you add that I was making the stupid update using phpMyAdmin, I couldn't cancel it faster. I had to log into terminal and kill it from there. Some hundred of thousands of records updated to null, thank you.

It was a testing database, and we had a backup so I had to take my good 30 minutes to restore it but it was not cool.

devrant

mysql

mariadb

wk98
Favorite
Ranter
Comments
  • 3
    7y
    As a precaution, I always start writing my queries backwards, I.e start with the WHERE & LIMIT condition, then write the rest of the query
  • 3
    7y
    @encore do the same when sending emails; first type the text, then the recipient, otherwise you could, for some reason, accidentally send the mail before it's complete!
  • 3
    7y
    As a precaution, write the select statement, then after verifying, change to update. Or if you can - do it within a transaction
  • 1
    7y
    @AndSoWeCode That's my way to do it. Before -

    for big "important" updates; Now - for every single update.
  • 1
    7y
    I tend to write everything as a select first then once I physically see the data is correct, convert that select to an UPDATE.
Related Rants
devRant © 2021 Hexical Labs LLC
Privacy Policy  |  Terms of Service
Add Comment