Junior developer's code worked in tests, destroyed data in production Alas, the weekend is over, but The Register tries to make your entry to the working week a little more enjoyable by bringing you a fresh installment of Who, Me? – the column in which you explain your worst slip-ups. This week, we have two readers for you to meet! The first asked to be Regomized as "Reggie" because he thought …
I was building a meeting scheduler for my department using Microsoft Power Automate (I know) and when someone cancels a meeting, you would like to blank the attendee field and open up the slot again. For some reason, although you can manually set a Person field to null, you cannot using Power Automate, because reasons. So when I tried to set the field to null, what happened was the system searched the database looking for the closest person to null, and now someone with the surname Nulli started getting dozens of meeting emails and calendar invites.
Top job MS. Top job me for not testing it properly.
If you're ever running something like this
DELETE FROM bigtable WHERE secretfield = 1;
in production the way I always do this is to type out the query without the table name ("bigtable") or the condition ("1").
That means if you accidentally hit Enter at an inopportune moment e.g. after just DELETE FROM bigtable (which would delete all rows!) the query will fail because it's syntactically invalid.
I then go back and add in the table name with a sanity check of "is this the table I really mean?" followed by the same with the conditions. Pause. Then press Enter.
Takes about 5 seconds longer and I've never once deleted something inadvertently in production. Oh, and never copy/paste queries for exactly this reason - unless you modify them to remove the same params first.
You're welcome.
or you could just start with SELECT * , and see if the results correspond with what you wanted to delete
then bring the DELETE in once you're happy the filters/ table/ db are all correct
or at the minimum type the WHERE field = 1 first to avoid accidentally hitting enter and doing a wholesale delete with no filter
yeh this - SELECT the things you think you want to delete and see if they are the things then swap SELECT for DELETE when you are super sure you are right!
I guess thats why having a "hidden" column called "reallyDelete" is handy... so you can practice the where with update <wherever>.reallyDelete=$true where .... and check with select * from <wherever> where <wherever>.reallyDelete=$true before you "delete from <wherever> where <wherever>.reallyDelete=$true"
Yeh - what could possibly go wrong there? I definitely haven't ever had to restore tables/databases for DBAs who got it wrong even with a reallyDelete column
/mines the one with a backup tape in the pocket
> or you could just start with SELECT * , and see if the results correspond with what you wanted to delete
Yeah I should have clarified that I meant once you've actually verified what you're going to delete.
There comes a point where you actually have to run the DELETE command.
I've come across numerous people where they've accidentally hit Enter, or even copied/pasted a full query with a line break at the end which in some clients is enough to execute it. The point being that if you supply 95% of the query initially and then fill in the blanks it's a simple way to avoid this specific problem.
Yep I have (thankfully) minimal SQL knowledge/experience but even I was thinking to myself while reading "why not run that query first as a select"? It costs nothing to verify it produces the expected level of output, then you can edit the select command to perform the deletion.
I could understand if that extra step would add an unacceptable amount of load to the database (i.e. doing a highly complex query twice) but that's not true here. Even if the query took a few minutes to execute that wouldn't be a problem, because it isn't like you have to wait on its completion. You could catch up on email while you wait, then return to that window to issue the delete when the select completes with the expected output.
First do the SELECT * FROM bigtable WHERE <condition> bit, then when you have verified that, do a SELECT * INTO bigtable_backup_todaysdate FROM bigtable (without the WHERE clause) before changing it to a DELETE statement. If the database has active transactions going on, you might also want to make sure that either everyone else is out of the database, or you might want to consider using a query locking hint, such as WITH READUNCOMMITTED.
You can always do a DROP TABLE bigtable_backup_todaysdate later, once you've established you didn't screw up.
This does, of course, assume you have enough space in your database for a copy of "bigtable", but to be honest, if you don't have that much headroom (or log space) in your database, you're likely to run into bigger problems.
This assumes SQL Server too, other DB engines are available.
Heres a little snippet I always have handy for such temp backups , I usually only use the top one , which is the same as suggested above
SELECT * INTO mytable_backup --newtable
FROM mytable
----------------
INSERT INTO mytable_backup --existing table
SELECT * FROM mytable
----------------
INSERT INTO dbo.mytable_backup (col1,col2) --existing table named cols
SELECT col1,col2 FROM dbo.mytable
I have found DROP is far more effective and efficient.
If you must "double down" on your screw ups, do it in style. :)
Dropping the "wrong" index while not normally catastrophic can be "interesting" (as in times, not hobbies.)
How's this for a pro tip: always Always ALWAYS surround your PROD statements with begin transaction / rollback transaction FIRST?
begin transaction
delete from bigtable where secretfield=1
rollback transaction
See how many records are impacted. If it's more than you think, carefully query the data and understand why.
Finally, when you are done, replace "rollback" with "commit":
begin transaction
delete from bigtable where secretfield=1
commit transaction
But even before you do this, do a SELECT with the same criteria as the DELETE and actually read some records.
I can appreciate your imagination, but there are just too many steps where things can completely go wrong.
The only solution that works is to wrap things in a transaction. That's what they are there for, after all.
Certainly, checking the number of rows delete is an important sanity check. But not the only thing that might be checked.
This post has been deleted by its author
Always always do the Select Count first, then run at least three queries - select count, delete, select count and select any other useful where to prove the delete or the surviving records - all wrapped in a transaction to give yourself chance to check that you’re getting what you expect. Then go for a coffee, come back and check it again before swapping rollback for commit.
Once had an interesting chat with a co-worker who couldn’t trace why his select count where sanity check gave a different number to the delete where where where was the same in both queries. Never did find out why; I was busy, he sussed it and went home.
This post has been deleted by its author
""All of the records were flagged with a test number – 1 – in an unused field,"
I wouldn't have touched this job until it is was clarified exactly what was meant by " – 1 –". I'd sound like an annoying pedant, but I'd be sure what was required before doing something disastrous.
And then I would have executed a SELECT before doing the DELETE.
So many times I have seen data in databases that were probably sane at the the time there were initially input but completely insane by the time I looked at it. The data type and boundaries were not well defined/if defined because they were built on different input applications that changed over time and scope of the input changed. Lazy, lets make it work, attitudes meant data built on data became increasingly undefined but nobody wanted to change the old values. This instinctively causes me to do changes in a separate table where I can. Basically I keep and old table and new table. Start using the new data to validate that changes are desired.
I once did the the same as Sam/Mike. We had a table with about 97,000 records in it controlling the products on a live trade portal website. I wanted to remove about 800 that had a flag field delflag='Yes'.
To test I had the right records I first ran
select count(unique_field) from table ;
then
select count(unique_field) from table where delflag='Yes'
then
select count(unique_field) from table where delflag<>'Yes'
Finally after I was happy these results all added up to what I was expecting I edited the open select query to make my delete query and ran
delete from table where delflag<>'Yes' - of course that should have been where delflag='Yes'
Luckily this particular table was updated very rarely so I was able to go back to the previous night's backup and just rebuild the table from there. Total downtime on the website was about 45 minutes, and as the mistake was made around 3pm on a Friday afternoon, no-one really noticed.
I did learn not to edit the current query in pgAdmin when going from a select to a delete and actually start the delete from scratch.
Not so long ago we were doing a production database migration and the testing process involved creating a bunch of temporary tables corresponding to the proper database tables (but with a much smaller number of records).
Testing was eventually declared successful. Time to drop all those temporary tables... except for one I accidentally dropped the main database table instead. This was Oracle, on which any DDL operation auto-commits.
A quick panicked email to the DBA who was on-hand for the testing process and within 10 minutes the table was back. (Fortunately - since it was a migration - he just needed to re-import that table. The application was down during the migration process, so no intervening changes to worry about.)
Not deleted; just "repurposed."
Actually was more often /usr especially when the standard (BSD) disk partition conventions were flouted.
I suspect "dd" aka disk destroyer, was responsible for more root deletions. dd from root partition to a new altroot partition after replacing a disk was favourite if you got your of= and if= arse about.
I have never inadvertantly deleted the root partition on a Unix system.
I have, however, inadvertantly deleted all the files in /.
(The error was due to me, working as root, issuing a rm * when I thought the cwd was "/tmp/", rather than "/". Recovery was effected via tar and the previous night's backup tape.)
Back in the DOS days, I had the bad habit of using the root directory of my second partition as a temp folder. It would ordinarilly never have files in it, so clean-up was as simple as deleting any non-folders.
Well, one day Microsoft outsmarted me. Their installer finished adding the program, and then it just blanket deleted everything in that folder including itself, existing folders and the new program it had just installed.
In my defense, the installer shouldn't have assumed it was being run from a directory with nothing valuable in it, and if it was going to clean up files, it should have only deleted ones belonging to it.
I haven't even looked in root in years, so I obviously don't use it as a temp directory any more, but i'm sure I probably continued to do so after this adventure.
Theres a very nasty snafu waiting for the inattentive,
We uploaded a new program to the robot cell, proved it all out, given this was a CAM generated monster(50 000+ lines), but it didnt return to a safe position at the end, so rather than CAM it all again, then have to reload it, and prove it, we thought "Lets just jump to the end and hand edit it"
The control has a handy search function, type in the text you want and press the down arrow on the keyboard... however... if you press 'delete' it deletes everything upto that search text.
We all know where this is going....
All I can say is praise the god of backups ... I wonder what his name is....
> praise the god of backups ... I wonder what his name is....
"His" s/b "his/her".
AI tells us:
AI Overview: In mythology, the Greek daemon (spirit) of safety and deliverance is Soter, who can be considered a figure representing "the god of backups" in a broader sense of preservation and recovery. His female counterpart is Soteria, the goddess or personified spirit of safety, deliverance, and preservation from harm.
Obviously some retcon here.
who hasn't done something careless and dangerous like this?
A young dev of mine recently built an API in GoLang for a project and the API mapped GET methods to POST methods so if you ordered /table/field=foo you actually set all entries' field field to foo. It also returned all fields where field=foo. So running GET /table/field=foo returned 200 OK and a shit-ton of JSON. Like all the JSON. Since they were now all foo.
Worked fine when tested, it returned 200 OK and JSON data...
Back in the day when I was a uni student working a summer job at GEC, I once accidentally deleted C:\Windows on my work PC. I realised my mistake, but the files were gone. I had access to a second PC, so I copied files over the network to reconstitute the basic file structure, but of course the Windows registry was toast so nothing worked. You could get to a DOS prompt, but no Windows.
Solution? Lie through my teeth. I went to our IT guy and basically said "My PC isn't booting Windows", and gave a short account of what happened when you dropped into DOS and then tried running Windows. This being the days of Windows 95, hard disks not always being reliable, and me being generally regarded as someone who knew what they were doing, the IT manager just swore a bit about Windows 95, about the batch of hard drives he'd just got (apparently a couple had genuinely failed), and then replaced the (probably OK) hard drive and re-imaged the PC. Job done, bullet dodged.
Accidentally did just that at my second job when I was still a greenhorn. didn't notice which database I was on and assumed I was on the test database (my first mistake), and executed a mass purge of grades of the university exam database.
Thankfully we had backups, but yeah, my contract was not renewed at the end of the term. It was for the best tho, since my relationship with one of the deans soured after he made me stay back and do unpaid overtime on a weekend when I already had plans a few months later.
Years ago, I had a job in Freight Forwarding. It was my job to help prepare the documentation for my various clients exports to various middle east countries. Sometimes, I had to deliver the documentation personally to the relevant country's embassy in London (this was the one aspect of the job I liked, because I love exploring and I got paid to go to all sorts of areas I wouldn't normally visit).
To ensure that customs were notified of the shipments, we had to use a special computer system to enter the details, and print any relevant documentation. For this, we had an IBM AT, connected to both a dot matrix and daisywheel printer, a modem (it used this to upload details of our shipments to an HMRC computer) and a serial terminal. I can't remember the OS, but it was either Concurrant DOS or Multi User DOS.
We had strict rules on how long we needed to keep documentation on shipments (5 years, IIRC), but due to the time required to upload, we were asked by customs to keep the online records to the last year or two, which meant one of my boss's jobs was, on a friday afternoon, deleting any out of date shipment records. For Audit purposes, we still had the paperwork.
The system asked the user to enter the record number to delete. This is terrible UI design, and asking for trouble already. It did allow you to enter a range, which made things slightly easier. My boss had been happily deleting ranges of records for years, and was comfortable doing so. But he made a mistake. He entered the range backward. The software did not check for this, and couldn't cope with it. It happily started deleting everything. When the deletion (which normaly took a few seconds) was still going over 20 minutes later, he looked and realised what was happening. He immediately turned the computer off (there was no option to stop it or quit).
When we got it back up and running, he tasked me with replacing the missing records. They didn't have a backup, and he didn't want to request access to our records from HMRC, because doing so would have meant admitting to an error.
So, I spent the next 3 months knee deep in shipping paperwork, re-entering the details of all relavant shipments on the computer. The company didn't survive much longer, and ultimately I was rewarded with redundancy a few months later.
