-
May 22nd, 2019, 11:39 AM
#1
Thread Starter
PowerPoster
[RESOLVED] Will you do a post mortum with me?
I want to know how I could've avoided this bug.
We have a function that goes: SELECT @Result = sum(MRSaleAmount) FROM Jobs WHERE (CustNo = @cust_no) AND (MRSaleDate BETWEEN dateadd(yy,-1,GETDATE()) AND GETDATE())
You see that it is selecting MRSaleAmount FROM Jobs.
Over a year and a half ago, I "moved" MRSaleAmount to its own table, xtblFONumbers, so that we could have a 1:many relationship between Jobs and xtblFONumbers. At that time, the function should've been updated to
Code:
select @Result = sum(fo.MRSaleAmount)
from xtblFONumbers fo
join jobs j on j.Control = fo.JobControl
where CustNo = @cust_no
AND (fo.MRSaleDate BETWEEN dateadd(yy,-1,GETDATE()) AND GETDATE())
Realize when I say "moved" that Jobs.MRSaleAmount was still a valid column. I updated xtblFONumbers from Jobs in a bulk UPDATE query, but the column remained in both tables. Maybe that was my first mistake.
If I run a dependencies list on Jobs, I see this function listed along with 20 other dependencies. Was I supposed to look at all of them to see if I was breaking any?
So my question is, when you make a change like this - discontinuing use of a column and putting it in a different table - what is the best way to find out what you're breaking? It'd be nice if you could do a dependency list of a column instead of just a table.
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
May 22nd, 2019, 12:24 PM
#2
Re: Will you do a post mortum with me?
Usually, depending on the rules I have to play by, if I no longer need a column, I drop it. There's no point in keeping it around. Doing so then leads me to all the places (with adequate testing by both myself, QA, and inevitably the users) where said column is used and needs to be altered. If I can't permanently drop the column, then I'll at least take a backup of my local db, drop it from there and run what tests I need to to make sure I've gotten all my bases covered. Even if the field never gets dropped from Production, dropping it from my dev copy and running it will usually break enough things that I can find where it was used and needs to be altered.
-tg
-
May 22nd, 2019, 12:26 PM
#3
Thread Starter
PowerPoster
Re: Will you do a post mortum with me?
Thanks, tg. Good advice for next time!
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
May 23rd, 2019, 02:12 AM
#4
Re: Will you do a post mortum with me?
the column remained in both tables. Maybe that was my first mistake.
Yeah, that's where you fell. It's a fundamental principle of database theory that any piece of information exists in one place and one place only. You don't even store it if it can be calculated from other places. As soon as you allow it to exist in two places you allow it to be inconsistent between those two places.
(N.b. we actually sometimes do allow information to exist in two places for performance reasons. This is called "denormalization" and is kinda like knowingly cheating the system. By default you shouldn't do it but once you have a certain level of expertise you might decide to.)
Anyway, the question really is, why didn't you drop the extra column at the time? Sorry if that sounds like a challenge, it's not meant to. There are all sorts of reasons why you might decide it's risky to drop a column, particularly in a production system (e.g. not wanting to break existing queries etc.), and I'm wondering if any of those reasons were in play for you. If they were then there's probably some tricks and tips we can offer that would have let you drop it more safely. On the other hand, if you just didn't realise that dropping the column would be sensible then, no worries, lesson learned, you'll know better next time.
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
-
May 23rd, 2019, 05:05 AM
#5
Re: Will you do a post mortum with me?
All arguments pro and con droping the column not withstanding:
To avoid breaking any SELECT-Queries (for reports and what not) you could define a Trigger on your new table, checking for your xtblFONumbers column (INSERT, UPDATE, DELETE), which then updates your Jobs column, so (in theory) it should enforce consistency between both columns.
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
May 23rd, 2019, 05:16 AM
#6
Thread Starter
PowerPoster
Re: Will you do a post mortum with me?
Zvoni, interesting idea, thanks. FD - not sure why I didn't think to drop the columns. But I could've/should've and it's pretty chill here so even if I caused errors in the live system, users would've reported them to me and that would've been that.
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
May 23rd, 2019, 08:01 AM
#7
Re: Will you do a post mortum with me?
Fair enough. Take it as a lesson learned and drop it next time.
The sorts of measures I was going to put out include things like the trigger Zvoni suggested (although be a little cautious with that your code only ever updates the new location) or using views to mimic the table as your want them to be. These sort of measure buy you some time to go through your code and debug anything that's referencing the old fields.
Of course, the right thing to do is drop the column in dev, update your code and unit tests, run it through your continuous integration process, roll out to your QA department, have a thorough systems text, put it in beta for acceptance testing... but most of us don't get to live in that world
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
-
May 23rd, 2019, 08:07 AM
#8
Thread Starter
PowerPoster
Re: Will you do a post mortum with me?
We're way too chill for even a QA department, LOL. My boss usually just tells me, if worse comes to worst the users can just use paper for a while.
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
May 23rd, 2019, 08:47 AM
#9
Re: Will you do a post mortum with me?
Originally Posted by MMock
We're way too chill for even a QA department, LOL. My boss usually just tells me, if worse comes to worst the users can just use paper for a while.
I think i could like your boss
I have more often than i'd like people complaining about "IT this" and "IT that" and
"why does this not work? --> because in 95% of cases the problem is in front of the keyboard.... --> > You're a Meanie!"
Gah, just letting my colleagues go back to being "paperpushers" just for 1 day..... i'd pay good money to see that
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
May 23rd, 2019, 03:34 PM
#10
Re: Will you do a post mortum with me?
I usually do something like this - renaming the column to add an "x" to the end of the name, for instance.
Exec SP_Rename 'Table.ColName','NewColName,'COLUMN'
-
May 24th, 2019, 06:49 AM
#11
Thread Starter
PowerPoster
Re: Will you do a post mortum with me?
Originally Posted by zvoni
because in 95% of cases the problem is in front of the keyboard....
lol!
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
May 24th, 2019, 06:50 AM
#12
Thread Starter
PowerPoster
Re: Will you do a post mortum with me?
Originally Posted by szlamany
I usually do something like this - renaming the column to add an "x" to the end of the name, for instance.
Exec SP_Rename 'Table.ColName','NewColName,'COLUMN'
Also a great tip, thanks!
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|