6

Today at work I did an update to production. The update involved replacing old items from two tables with new data. I of course did a back up of the tables before that.

Well, now 6 hours after getting home I realized at shower that there was a foreign key reference on cascade delete for many tables and when I replaced the older data it also removed lots of other data -- I'm not even sure I know all of the data that got removed...

The thing is, there are about 270 tables and I've been to the company for 3 months. I was supposed to do this update today and the CTO was away doing remote work. I tried to contact via Skype and email, but he's away. I figured not to call him at this time of day (late evening here).

What should I be doing here? It's the beginning of weekend and I feel bad leaving the system in such a condition for two days... and especially because I screwed up things.

The client is doing billion dollar business and its the biggest client the company has so this was a major drawback. If the hosting provider does not have nightly backups I will probably get fired. Damn I hate automatic magic like FKs. :(

What would you do in my position?

rFactor
  • 163
  • 4
  • I feel for you, but why didn't you test the updates on a test database and checked the state of it after the updates, before you did so on the production database? – Oded Oct 14 '11 at 19:25
  • 3
    I learned the hard way that you should always make a backup, and simulate your updates on a copy of the production database before applying the changes to the live database. I will file your experience for future reference; I never use cascading deletes, and now I have more evidence to support my position. – Robert Harvey Oct 14 '11 at 19:25
  • Also, if your query was an UPDATE query, you might be OK; only deleting and re-adding the records (something I don't think you would have done, since it changes all of the primary keys) would have triggered the cascade deletes. Think very carefully about your strategy, and remain calm, before you take any additional action. – Robert Harvey Oct 14 '11 at 19:32
  • 2
    cry, then follow Justin Cave's answer? – Peter Ajtai Oct 14 '11 at 20:08
  • 2
    I don't like closing this question yet... rFactor has a real problem and is asking for advice. I don't see how it's too localized... we've almost all experienced an issue like this, and can provide insight about how we handled it, or should have handled it. At the very least, we need a way to mark questions like this to close in a week or something...but not immediately so that rFactor can get help. – CaffGeek Oct 14 '11 at 21:21
  • 1
    @Chad, I agree it's a valid question, but Database Administrators is a better place for it. – Cyclops Oct 14 '11 at 22:34
  • 1
    As a DBA mod, I agree that the OP should goto [dba.se] right away and ask for assistance. There may yet be something that can be done. But he has to act fast. – jcolebrand Oct 15 '11 at 02:24
  • Is anyone else curious how the weekend unfolded? Were you able to get this issue sorted out? – CaffGeek Oct 17 '11 at 13:31
  • Yes the CTO told the hosting company to restore backup MySQL files to user readable directory. Then I downloaded and put the database to work on a local installation and made SQL dumps for the tables that got erased. We managed to fully recover from this and the client did not even notice. The CTO was actually pretty calm and told me to not stress and that he was the one to tell me to update the production. He did say that it was good I noticed this early. So all good now. :) – rFactor Oct 17 '11 at 15:24

3 Answers3

18

Let someone know that there is a problem as quickly as possible.

First off, depending on the database vendor and configuration, the DBAs may have the ability to revert the change. But the options for doing so get harder the more time has elapsed. Oracle's flashback technology, for example, gives the DBA far more options if the error is noticed quickly than if the error is noticed days from now.

Second, if there is a need to restore from a backup, having the weekend to restore the backup and recover whatever data is needed is much preferable to trying to do the restore and recovery during the week. It probably takes a few hours at least to get the backup tapes back so, again, time is precious.

Third, from a purely political standpoint, people are going to be far less upset with you about the mistake if you're the one that is sounding the alarm than if someone else discovers the error later. Everyone makes mistakes. Not everyone owns up to their mistakes. I'd much rather have an employee that made a costly mistake, learned a valuable lesson, and had the strength of character to admit the mistake and work to resolve it proactively than to have an employee that made a costly mistake and either tried to hide it or avoid drawing attention to it.

Justin Cave
  • 12,711
  • 2
    Fess up, work all weekend if you have to in order to fix it. Sometimes **** happens. However, if you're only been there 3 months, and they don't have nightly backups happening that are known to exist and be good some of the blame goes on them. I stress the good backups, because just having a file isn't enough, after every backup, it should be verified to be good, not corrupt, etc. – CaffGeek Oct 14 '11 at 20:19
  • I called the hosting company and they told me they have "real time" backups, whatever that means, but they can restore it or put a dump on disk. I tried calling the CTO and he is not answerring. The CEO has been away past days and I doubt she's going to answer since it's 11.30pm and she's not in Skype (where she always is). I then sent an SMS and again, no response. I offered in SMS to pay the tax. However, it's going to cost $330/hour to do this, and I forwarded the hosting company's email about the cost of restoring the backup at weekend/night. Is there something I should now do? – rFactor Oct 14 '11 at 20:25
  • If you sent an SMS they will get it first thing, so just try and sleep lightly :\ – jcolebrand Oct 15 '11 at 02:30
6

Call your CTO and (calmly) explain what happened. You should only call someone after hours in case of an emergency, and it definitely seems that this is an emergency.

CamelBlues
  • 1,145
  • 1
  • 6
  • 14
  • Oh! as an addendum, I've been in this situation before, and after I wiped out a DB (or two) I started wrapping my update scripts in a BEGIN TRAN, so I could always ROLLBACK if something went wrong – CamelBlues Oct 14 '11 at 20:12
  • He didn't really have anything "go wrong", he merely remembered he overlooked something. – jcolebrand Oct 15 '11 at 02:28
2

Here is what I would suggest:

First take a backup of the database right now to at least preserved what you have at the moment. This will also be helpful if any of the attempts to recover the information that may be lost fails. At least the situation doesn't get worse.

Next get into contact as quickly as you can with the guys running the backups. Then restore that data into a separate database so you can determine what is missing from the live database. This may not be easy but at least it worth trying and it is a start to get everything back on track.

My final recommendation is that you make sure you communicate throughout the process to all stakeholders. Start with your boss as chances are that he will support you or may be able to give you political cover behind the scenes.

Once everybody knows what has happened you will experience a lot of heat. Then apologize and take the initiative to work out a plan to contain and minimize the impact. In my experience, even if stakeholders (including challenging customers) are very angry at you, they still need to you as you are likely to be the best person to resolve the issues. And always remember: It is impossible to over-communicate.

Good luck!

Manfred
  • 1,405