Updating in production, like a boss
- Reference: 1624260605
- News link: https://www.theregister.co.uk/2021/06/21/who_me/
- Source link:
Our tale, from a reader Regomised as Nicolás, takes us back through the decades and to the glory days of Microsoft SQL Server 7.
SQL Server 7, which turned up in 1998, was a major rewrite of the old Sybase engine that had underpinned previous versions of Microsoft's database. Not so fragrant and fresh, however, were the other tools Nicolás used in his Madrid day job: Visual Basic 6 and that [2]terror of programmers everywhere at the time , Crystal Reports.
[3]
At this point we'd normally go off on a historical tangent regarding the reporting tool, which came bundled with the likes of Visual Studio and Borland Delphi, but the trauma of having to change data sources programmatically still haunts this hack decades after the event. However, it was not Crystal Reports, nor was it Visual Basic 6 that lay at the heart of this week's metaphorical locking of the brakes in front of a looming truck. It was SQL Server.
[4]
[5]
Nicolás and his manager had been tasked with fixing problems with some brand spanking new in-house accounting software. “To say that the previous coding was utter crap is an understatement,” he muttered. Inexplicably, the duo were denied a development and test environment, or even space for backups.
[6]Do you come from a land Down Under? Where diesel's low and techies blunder
[7]Peter Moore: IT consultant and Iraq hostage – Part One
[8]That thing you were utterly sure would never happen? Yeah, well, guess what …
[9]Thanks, boss. The accidental creation of a lights-out data centre – what a fun surprise
[10]Congestion or a Christmas cock-up? A Register reader throws himself under the bus
“Keep up the good work,” exhorted the management as the pair glumly looked at the task.
They got cracking. “I was the database expert,” Nicolás modestly told us, as well as being the resident VB6 guru. The latter meant that he was elbow-deep dealing with what he described as “seriously crap VB6 code (forms would duplicate and crash)” when his colleague decided to have a crack at some data modifications.
Nicolás, although more experienced, was the junior partner in terms of job titles and so his colleague did not trouble him with the ins and outs of the task. It was just an update. On the production database server. Without backups.
[11]
Looking back, Nicolás told us: “To this day I don’t really know what went through their head, but those modifications were intended to fix some inconsistencies in the database (one [insert expletive here] had added a small amount of fixes to hide the previous software rounding errors!)
“Since we were not having rounding errors, those small adjustments had to go.”
Alas, the adjustments were not the only thing to go.
[12]
“Oh Dios … la he jodido.”
Nicolás looked up from his work. What had been, er, screwed up?
There are two types of database programmer in the world. Those who have missed a critical filter in the WHERE clause of an UPDATE, and those that will do so at some later point in their career.
Nicolás’s colleague had skipped from the latter camp to the former, and done so in production and without backups. Every record in the table had been inadvertently updated.
While the need for anonymity forbids us from revealing the purpose of the database, the cock-up would make for national news if it could not be fixed. Nicolás played the only card available to him: “The database is going down for important performance updates, please do not use the software.”
There is a happy ending to the story: he was able to reconstruct the borked data from the content of other tables during what we imagine was a very sweaty-palmed SQL session and bring the production system back online, the users none the wiser.
“It required so many filters it took hours,” he said, “not to mention carefully checking data consistency to make sure it made sense.”
Job done, he took himself home to soothe his rattled nerves. Perhaps with an adult beverage or two. It could, after all, have been much, much worse – a DELETE might have been issued rather than an update.
Then it would have had to be a "capacity optimisation" instead of the "performance updates."
We all know never to test or develop in production. But sometimes management is reluctant to pay for all those extra environments. Tell us about your moment of expense justification with an email to [13]Who, Me? ®
Get our [14]Tech Resources
[1] https://www.theregister.com/Tag/who-me
[2] https://www.theregister.com/2012/11/27/peter_moore_interview/
[3] https://pubads.g.doubleclick.net/gampad/jump?co=1&iu=/6978/reg_software/front&sz=300x50%7C300x100%7C300x250%7C300x251%7C300x252%7C300x600%7C300x601&tile=2&c=2YNBjRoRHYOoid3NQS59VCQAAAM8&t=ct%3Dns%26unitnum%3D2%26raptor%3Dcondor%26pos%3Dtop%26test%3D0
[4] https://pubads.g.doubleclick.net/gampad/jump?co=1&iu=/6978/reg_software/front&sz=300x50%7C300x100%7C300x250%7C300x251%7C300x252%7C300x600%7C300x601&tile=4&c=44YNBjRoRHYOoid3NQS59VCQAAAM8&t=ct%3Dns%26unitnum%3D4%26raptor%3Dfalcon%26pos%3Dmid%26test%3D0
[5] https://pubads.g.doubleclick.net/gampad/jump?co=1&iu=/6978/reg_software/front&sz=300x50%7C300x100%7C300x250%7C300x251%7C300x252%7C300x600%7C300x601&tile=3&c=33YNBjRoRHYOoid3NQS59VCQAAAM8&t=ct%3Dns%26unitnum%3D3%26raptor%3Deagle%26pos%3Dmid%26test%3D0
[6] https://www.theregister.com/2021/06/14/who_me/
[7] https://www.theregister.com/2012/11/27/peter_moore_interview/
[8] https://www.theregister.com/2021/06/09/who_me/
[9] https://www.theregister.com/2021/06/07/who_me/
[10] https://www.theregister.com/2021/05/31/who_me/
[11] https://pubads.g.doubleclick.net/gampad/jump?co=1&iu=/6978/reg_software/front&sz=300x50%7C300x100%7C300x250%7C300x251%7C300x252%7C300x600%7C300x601&tile=4&c=44YNBjRoRHYOoid3NQS59VCQAAAM8&t=ct%3Dns%26unitnum%3D4%26raptor%3Dfalcon%26pos%3Dmid%26test%3D0
[12] https://pubads.g.doubleclick.net/gampad/jump?co=1&iu=/6978/reg_software/front&sz=300x50%7C300x100%7C300x250%7C300x251%7C300x252%7C300x600%7C300x601&tile=3&c=33YNBjRoRHYOoid3NQS59VCQAAAM8&t=ct%3Dns%26unitnum%3D3%26raptor%3Deagle%26pos%3Dmid%26test%3D0
[13] mailto:whome@theregister.com
[14] https://whitepapers.theregister.com/
It's the kind of scenario where I'd be tempted to wait for half an hour without doing anything affecting the db, then take it offline and ring back and tell them something disastrously bad had happened to it.
Then pause and comment - "At least that's what may happen if we do this live on your production database without any backups. Now do you want to reconsider this scenario...?"
Management seem to like their role playing training exercises, so maybe spring one on them to give them a proper taste of the risks they're taking?
I'd be almost rich if I had £1/$1 for every time I've seen database uses which perfectly demonstrate that the developer didn't have a clue about SQL whatsoever, let alone the specifics of MS-SQL.
From horrors such as an absolute lack of referential integrity (no linked tables whatsoever), to company owners who insisted on browsing the SQL data directory to open up the "tables" directly and so often, the devopers who just do not understand that SQL operations are set based and not procedural.
...and the perpetual bugbear? No underlying fallback to a unique sort order in display results. Want to order by name? Fine, but make the last sort order column a unique record ID to ensure that the search results are consistent.
DING!
Every one of your points there hit home, perfectly describing the mess I walked into 5 years ago. Admittedly, I didn't know a whole lot about SQL Server databases at the time, but even I boggled at the fact that the database in question had nearly 100 tables, none of which were linked. To top it off, the database designer had used an aggressively large varchar for every field, regardless of the actual data type of the contents (including yes / no). The code that went with it was worse.
Excuse me, I need to go somewhere quiet until the PTSD flashbacks go away...
We all know never to test or develop in production. But sometimes management is reluctant to pay for all those extra environments
In that case, ask them to write they refuse to pay for a test environment and will be accountable of any problem related to development made directly into production.
Words are not enough. It has to be written.
And to personally name, date and sign it...
"Because I say so"
In that case, you don't need them to write it. You can do it yourself.
"Per your request, we will be making these changes in production with no rollback mechanism." If you can show you sent that email, that's as good as receiving it.
Back-End Issues
A pretty common experience I suspect. But having a dev & test environments is no sure thing either. On one SQL/CR combo I used to re-up the dev server by droping the tables and then batch import new data, that was fairly routine until of course one day I failed to recognise I was on the Prod box. I think of these moments as a "Sphincter Loosener" when your heart misses several beats, disengages from your chest wall and drops into your stomach and attempts to push all contents out of the "back-end".
This particular incident stress tested my rebuild scripts and took the best part of a day IIRC. I explained to my manager something like that I'd found a bad flaw in the database structure, that the flaw meant all the CR financial reporting was incorrect and that only my prompt intervention saved the day.
Surely anyone faced with doing updates on a live box will run the query first as a Select instead of an update to check that an expected quantity of rows are updated and repeat that a couple of times, and then run the Update inside a transaction with a Select immediately after on the expected updates to confirm which then finishes with a Rollback after the check to undo it? Then do that again, and again to check and then once more to make sure. Then step away, have a coffee and then check again. And get someone else to eyeball the script that you're about to run.
We've all been there, I certainly have. The sense of trepidation as you're about to hit Execute is enough to want to be absolutely sure that you're happy with what you're about to inflict on the database.
"The sense of trepidation"
That's because you have a suitable degree of paranoia, the first requirement of any DBA. Unfortunately it's not part of the certifications that HR check at recruitment.
You absolutely should do all that, yes. But if you're sure of what you're doing, you've already made several changes without a hitch, you're anxious to get home early. .. It's possible to get sloppy.
And even if you don't, all it takes is to miss a line in the part of your query you highlight before pressing F5. Been there, done that.
The random expiry time
A large bank used a messaging product to send information around its systems.
Some of these "what is my bank balance" messages were not important, expired after 30 seconds and were thrown away - the requester could always resend.
Some of these were "transfer $100 Million" messages - and these were not allowed to expire. They were logged to disk.
Unfortunately a teeny-weeny application change put a random expiry time into the data.
The first the bank knew there was a problem when there was a phone call "Where is my $100 Million?"
I worked for the company that provided the messaging system. I got a call 10:00 from a stranger who explained the problem and asked if I could go on site to help.
I said I was happy to, but the customer would have to pay for a plane ticket etc. (That usually puts people off). I strolled over to my boss to give him early warning - but he was away from the office. I got back to my desk and had an email " there is a ticket for you on the 1200 flight to ... if you can make this it would be great"
This is when you think "They are serious". I left a note on my desk, booked a taxi - rushed home, picked up my passport, and change of underwear etc. I got the (business class wow!) flight with minutes to spare.
At the far end I was met and taken to the customer site. I knew the confidential layout of the log records on disk, and between us we came up with some rules - if this value is .. and that value is - then print out this other data. Some people then worked through the nigh and re-entered the expired data.
By 0900 the next morning, it was all fixed.
They then told me the true scale of the problem. They had "recovered" billions of dollars which was good. The banking auditors were due to arrive that day at 1200 for the annual review. If they found there was money missing, the bank would have been closed down.
Re: The random expiry time
"The banking auditors were due to arrive that day at 1200 for the annual review"
That would explain business class flights.
Done it, learned from it
Working on a meteorological database in the field. Database was in a data centre in Reading, I was sat on top of a truck chasing storms in Kansas. We had had some inconsistencies on one sensor. Quick SQL query to take them. Put for the sake of making the public facing graphs look better, eg no jumps from 25C to - 127C. Ran query, expected to loose 20 odd rows...
Lost 45,000, in fact All of the data for that asset in the feild.
Thankfully backups. And a week in the office after writing procedures and documenting stuff so it wouldn't happen again. At least us small businesses tend to learn...
No test environment and no backups - yet important enough to make national news if it went TITSUP? Assuming this story is indeed true, the management involved redefines the meaning of the word "incompetent".