My employee updated all records in a database table...

[Chunkee]

at least make incrementals; snapshots throughout the day.

 

[AlienCraft]

Fail List...
1. Back Up Procedure
2. Internal Company User Access / Permissions / Update Protocol
3. Company Policy Re: Unauthorized Use vs n00b moment / human error
4. Back Up Procedure


All of your "problems" stem from a faulty B/U routine.
< When doing B/U of music files, I always made a "master" first that I kept for my own records "just in case". This saved my butt more than once.
As much as I love analog Tape recording, I can do without tape in a digital format.

 

[nakedfrog]

Let me guess, he forgot the WHERE clause?

 

[D1gger]

Originally posted by: biggestmuff
I'm not quite sure what you position is over him. If you're his shift supervisor or section lead/head, I'd do the following:

-Pull him aside and tell him how bad he fucked up, only more eloquently
-Have a shift/team meeting telling everyone what happened, but DO NOT single him out; this will provide a 'lessons learned' to your group.


- And, of course, your backup system needs to be re-evaluated.

This is the correct course of action to take. It will raise the issue without causing too large an issue for the employee who made the mistake. Just make sure it is clear that you are covering his ass this time only.

 

[manlymatt83]

Originally posted by: KingGheedora
What would you do in this situation? I know enough about the sql inner workings to figure out who ran the query that caused the problem, and when, and i even have the text of the query that updated all the records.

Anyways, one of the main tables we use as part of our operation is not part of our production environment, and we aren't losing any money (this is exactly why we don't allow developers access to our production db) as a result of this mistake. I can even recover >99% of the three columns that were updated because luckily i created a backup of the table last night for some unrelated tests I was running. Our true nightly backup already overwrote the previous night's backup with the fucked up version of the table so that backup is useless. The data in this table is very usefuland we report off of it all the time.

I'm mainly pissed that this happened and the employee didn't fucking tell me he did this. If he had and this had been any other day, I wouldn't have the personal backup, but I would have been able to recover from the nightly backup. His not telling me would have fucked us over and made all the historical data basically useless.

Should I just fix it and send a message directly to him, what would I say in that case? "I noticed that all the records in the table were updated because you ran an update statement last night without a proper where condition. Please be more careful with this in the future."

Should i send a message to the team and not really single him out at all?

Probably either of the above two options would also include an office-politically-correct reprise of paragraph #3 of this post. I plan to let my boss know this happened in a private message.

Until you get to the point where you can go back more than one night in backups, you don't deserve to say anything.

I'm a professional DBA and I've worked on some pretty large scale systems. I've had quite a few mess ups in my day too, but they are easily rebuilt from delayed slave/binary logs/backups/etc.

 

[kranky]

To me, the big issue is that he didn't come forward after making the mistake.

 

[drum]

Originally posted by: kranky
To me, the big issue is that he didn't come forward after making the mistake.

again, assuming he realized he made a mistake.
we use EMC Avamar tapeless backup and it is marvelous

 

[Delita]

Fire his bitch ass

 

[Kntx]

It seems to me you have a process problem.

Why is the guy running ad hoc SQL on a production database? If this is business as usual, things like this are going to happen once and a while.

You don't have a good backup system? Who's responsibility is that?

I don't think you can really blame the guy. If he realized he mucked up the data and didn't say anything, maybe that speaks to a culture problem?

In the end giving the guy a hard time about it won't solve anything. You clearly have bigger issues to deal with. Use this as an opportunity to kick of a project with the these goals

1) maintain data integrity
2) reaffirming database access rights
3) develop a backup and recovery strategy

 

[alkemyst]

Originally posted by: KingGheedora
I know enough about the sql inner workings to figure out who ran the query that caused the problem, and when, and i even have the text of the query that updated all the records.

Whoever is in charge should handle this. Especially that you have just defined yourself as a non-SQL expert.

 

[AkumaX]

backup and recover

 

[oiprocs]

Originally posted by: Delita
Fire his bitch ass

Finally, some real justice.

:laugh:

 

[SoulAssassin]

Originally posted by: drum

Originally posted by: kranky
To me, the big issue is that he didn't come forward after making the mistake.

again, assuming he realized he made a mistake.
we use EMC Avamar tapeless backup and it is marvelous

Avamar sucks ass, get a real dedupe solution.

 

[randay]

hoof to the head.

 

[Howard]

Originally posted by: Delita
Fire his bitch ass

SPOILERS









You die in the end.

 

[torpid]

You should point out to the developer what happened but only in the context of it being a mistake that does happen. It is almost certain that he did not notice his mistake. The big issues caused by this are NOT the developer's fault because mistakes happen. However, they should be made aware of the mistake and there should be policies in place to help prevent it. If the SQL had been faulty in a much more difficult manner to detect, it would actually be a lot worse for you right now, because you'd have no idea that a defect existed.

The developer should be made aware:
1. That he forgot a WHERE clause
2. That he should be verifying data before committing
3. That he should check the row count of data updated as a major clue

The process involved in all other regards is unacceptable. If developers can do ad-hoc sqls, they should have a form similar to:

A. Select data you are going to update
B. Update data
C. Select data you updated

A-C should have the same WHERE Clause always. Run the SQL, check the before and after before committing.

Backup system sounds like a mess. Why would you retain only a SINGLE day's worth of backup?

Allowing developers to run amok in live data without anyone being aware is a problem.

 

[KingGheedora]

Originally posted by: alkemyst

Originally posted by: KingGheedora
I know enough about the sql inner workings to figure out who ran the query that caused the problem, and when, and i even have the text of the query that updated all the records.

Whoever is in charge should handle this. Especially that you have just defined yourself as a non-SQL expert.

Uh, i don't know where you got that. I'm a developer, not a dba, and the average developer wouldn't even know how to do what I described above.

Our dba team created the backup process, and yes it was flawed, our whole process is flawed. As i stated in the original post, this is NOT A PRODUCTION DB, and we aren't losing any money with this data being lost. It's operational data that is useful to track but we can start from scratch if need be, without any of it, at any time.

 

[KingGheedora]

I sent email to my team with my boss Cc'ed. highlighting the fact that mistakes happen but the important thing is to fess up about it ASAP. I'm singling out the developer who did this later today and letting him know I traced it back to him, be more careful, and let me know next time.

Also, we're updating the backup process for this db so this doesn't happen again.

 

[Crusty]

Originally posted by: KingGheedora
What would you do in this situation? I know enough about the sql inner workings to figure out who ran the query that caused the problem, and when, and i even have the text of the query that updated all the records.

Anyways, one of the main tables we use as part of our operation is not part of our production environment, and we aren't losing any money (this is exactly why we don't allow developers access to our production db) as a result of this mistake. I can even recover >99% of the three columns that were updated because luckily i created a backup of the table last night for some unrelated tests I was running. Our true nightly backup already overwrote the previous night's backup with the fucked up version of the table so that backup is useless. The data in this table is very usefuland we report off of it all the time.

I'm mainly pissed that this happened and the employee didn't fucking tell me he did this. If he had and this had been any other day, I wouldn't have the personal backup, but I would have been able to recover from the nightly backup. His not telling me would have fucked us over and made all the historical data basically useless.

Should I just fix it and send a message directly to him, what would I say in that case? "I noticed that all the records in the table were updated because you ran an update statement last night without a proper where condition. Please be more careful with this in the future."

Should i send a message to the team and not really single him out at all?

Probably either of the above two options would also include an office-politically-correct reprise of paragraph #3 of this post. I plan to let my boss know this happened in a private message.

A couple things I noticed, your backup plan is bad. You only retain a single backup for 24 hours? That's horrible.

Secondly, you SHOULD be managing permissions on the databases, especially production machines.

 

[Atheus]

I've done exactly this when working late on a site launch. Something like "UPDATE x SET y=z" with no WHERE...

Luckily it was only a big list of filenames and directories and I was able to rebuild it by writing a program to go through the file system looking for the right files. Took me ages.

 

[torpid]

Originally posted by: KingGheedora
As i stated in the original post, this is NOT A PRODUCTION DB, and we aren't losing any money with this data being lost. It's operational data that is useful to track but we can start from scratch if need be, without any of it, at any time.

If you are reporting off it then isn't it production data? It might not be customer data but it sounds like production data to me...??

 

[KingGheedora]

Originally posted by: torpid

Originally posted by: KingGheedora
As i stated in the original post, this is NOT A PRODUCTION DB, and we aren't losing any money with this data being lost. It's operational data that is useful to track but we can start from scratch if need be, without any of it, at any time.

If you are reporting off it then isn't it production data? It might not be customer data but it sounds like production data to me...??

In a way yes. But it is technically a development server where people started writing rogue apps and tools, and db's. Anything that is mission critical is put on the actual production servers, where we have robust backup procedures, and permissions are locked down, etc.

 

[nakedfrog]

Originally posted by: Atheus
I've done exactly this when working late on a site launch. Something like "UPDATE x SET y=z" with no WHERE...

Luckily it was only a big list of filenames and directories and I was able to rebuild it by writing a program to go through the file system looking for the right files. Took me ages.

That's a mistake you only ever make once, eh?

 

[KLin]

So what if someone needs to update 1001 records? Will they have to call a dba to temporarily disable the trigger that's throwing the error?

 

[Atheus]

Originally posted by: nakedfrog

Originally posted by: Atheus
I've done exactly this when working late on a site launch. Something like "UPDATE x SET y=z" with no WHERE...

Luckily it was only a big list of filenames and directories and I was able to rebuild it by writing a program to go through the file system looking for the right files. Took me ages.

That's a mistake you only ever make once, eh?

yyyup