20

I was trying to do an update on the MySQL server and accidentally forgot to add an additional WHERE clause which was supposed to edit one row.

I now have 3500+ rows edited due to my error.

I may have a back up but I did a ton of work since the last backup and I just dont want to waste it all because of 1 bad query.

Please tell me there is something i can do to fix this.

Naveen
  • 687
  • 3
  • 12
  • 24

4 Answers4

12

Just an idea - could you restore your backup to a NEW database and then do a cross database query to update that column based on the data it used to be?

user158017
  • 2,891
  • 30
  • 37
  • 1
    I have never done this before? Any suggestions on how to do it ? – Naveen Jun 06 '12 at 22:47
  • 1
    For restoring - I would have my DBA do it (I'm a developer, not a DBA). For cross database query - I'm not a mySQL person so I don't know the details. But in Oracle, we first grant permission to "select" on the appropriate table in schema1 to schema2. Then we prefix the table with schemaName. So select * from schema1.tablename. – user158017 Jun 07 '12 at 00:03
12

If you committed your transaction, it's time to dust off that backup, sorry. But that's what backups are for. I did something like that once myself... once.

Jeremy Holovacs
  • 22,480
  • 33
  • 117
  • 254
11

Nothing.

Despite this you can be glad that you've got that learning experience under your belt and be proud of how you'll now change your habits to greatly reduce the chance of it happening again. You'll be the 'master' now that can teach the young pups and quote from actual battle-tested experience.

Michael Durrant
  • 93,410
  • 97
  • 333
  • 497
  • I'm glad it happened at this stage and ill be extra cautious hereafter. Although the impact is not that serious, it still affects me. But nevertheless, it was a good learning experience. – Naveen Jun 06 '12 at 22:49
  • Hi @Naveen. Did you manage to solve the problem. Forgot the where clause and my head is hot. My backup is not so recent. – Fokwa Best Oct 24 '16 at 10:15
9

There is only one thing that you can do now is FIX YOUR BAD HABIT, it will help you in future. I know its an old question and OP must have learned the lesson. I am just posting it for others because I also learned the lesson today.

So I was supposed to run a query which was supposed to update some fifty records and then MySQL returned THIS 48500 row(s) affected which gave me a little heart attack due to one silly mistake in WHERE condition :D

So here are the learnings:

  1. Always check your query twice before running it but sometimes it won't help because you can still make that silly mistake.
  2. Since Step 1 is not always helpful its better that you always take backup of your DB before running any query that will affect the data.
  3. If you are lazy to create a backup (as I was when running that silly query) because the DB is large and it will take time then at least use TRANSACTION and I think this is a good effortless way to beat the disaster. From now on this is what I do with every query that affects data:

I start the Transaction, run the Query, and then check the results if they are OK or Not. If the results are OK then I simply COMMIT the changes otherwise to overcome the disaster I simply ROLLBACK

START TRANSACTION;
  UPDATE myTable
  SET name = 'Sam'
  WHERE recordingTime BETWEEN  '2018-04-01 00:00:59' AND '2019-04-12 23:59:59'; 
ROLLBACK;
-- COMMIT; -- I keep it commented so that I dont run it mistakenly and only uncomment when I really want to COMMIT the changes
UsamaAmjad
  • 4,175
  • 3
  • 28
  • 35
  • 1
    The way you keep "COMMIT" in the snippet so you remember it's an important part of the task, but leave it commented out so you don't copy/paste it accidentally, is good evidence of your rigourous and diligent thinking. I applaud you! Keep it up! – Lightness Races in Orbit Apr 12 '19 at 01:24