Monthly Archives: April 2022

Woody Woodpecker

Posted on by
Reply

There’s an old saying in the software industry that “If houses were built like software was, the first woodpecker that came along would destroy civilization.”

I was reminded of that last week in both ways. First I was sitting at my desk when I heard what sounded like someone banging on a tin drum of some sort. I went up to the porch outside my office window and saw a bird fly off. Seeing that only solved part of the question in my mind and created a new one. The new one was what the heck was a woodpecker doing on the corner post. It’s pressure-treated and there’s no signs of any sort of infestation. But even more so, why was it so friggen’ loud and metal sounding? About 20 minutes later it was back. I chased it off again.

This cycle has become a daily event with it coming back 3-4 times a day. But I finally got a partial answer. I had forgotten that on the far side of the post was the copper downspout for the gutters. As far as I can tell, it’s hitting it directly (though I can’t imagine why) or close enough that the entire downspout is acting like a drum. Annoys the heck out of me, but the woodpecker doesn’t seem to care. And the good news is, despite all this, my house is still standing.

The other reminder was an error message I had received from an ETL process I have written for a customer. It takes data from a vendor and loads it into my client’s Salesforce instance. The error was one I hadn’t seen before (but had thankfully planned for). So I went into the package and started to investigate. It seemed all the new records had simply failed to load. I looked closely and could not see an issue, so I attempted a manual load and it instantly failed. This was unusual and troubling. My first thought was that the vendor had somehow changed the data format and that was causing a problem, but nothing really indicated that (and had that happened it most likely would have actually generated an error earlier in the process).

After some digging I realized that it wasn’t the vendors fault. As part of the ETL process, one of the steps that happens is a lookup into Salesforce for a Vendor ID to make sure the data coming in is tagged with the right ID. This step is somewhat important because the ID, being an internally generated one in Salesforce, is different between the UAT instance and the Prod instance, so rather than hardcoding it, it’s a lookup. And while the ID itself in an instance can never change, the keyword associated with it in theory can. In practice it shouldn’t. Normal users don’t have permission and admin users should know that changing the keyword should never be done, or if it is, needs an entire change process.

Sure enough, my lookup, which was basically doing “Where Vendor_ID=’Adventureworks'” was now returning nothing. So the insert, which needs that Vendor_ID was failing. A little digging showed that an admin had changed it and not to something like ‘Adventure Works’ or something that might have been close and thought to be a trivial example, but had changed it to something like ‘Bob and Mary’. It wasn’t even close.

Needless to say, better controls have been put into place, but apparently a woodpecker came along and broke my ETL.

Fortunately, that one isn’t coming back.

Code strong, code robustly, and have good error checking.

An Ode to my Mom

Posted on by
Reply

Three-quarters of a century. That time frame seems both like forever and a blink of an eye. They say one doesn’t speak of the age of a lady, but I think in this case, I’ll make an exception. This past weekend, besides celebrating Easter a day early, my family and I celebrated my mother’s 75th birthday.

Now when one thinks of an Easter celebration, one might think of a ham or lamb dinner with all the trimmings, or at the very least some sort of formal sit-down meal. And yes, for much of my life that’s what we had, especially when we would celebrate at my grandmother’s.

But no, this year my mom, now the matriarch of at least this little corner of the family decided she’d rather do take-out sushi and loads of Thai food from a local restaurant. And a great repast it was.

That’s not to say we eschewed all tradition. Ever since my kids, her only grandchildren have been old enough, she and her partner Jimmy have hidden plastic Easter eggs around their house for the kids to find. Even at 22 and just about 19, they still enjoy the tradition and while being quite competitive about it, end up sharing the rewards equally. And inevitably there’s one plastic egg that seems to go missing.

This year, that tradition was changed a bit to accommodate my cousin’s children who are much younger. For them, the eggs were multi-colored and placed on the floor. And yet, they still managed to find a few of the ones higher up. I suppose a child’s reach should exceed their grasp.

And of course, as always in our family there was the tradition of cracking colored, boiled eggs.

Easter Eggs, ripe for cracking

But as I mentioned it wasn’t just an Easter celebration, it was a celebration of 75 years of my mother’s life travelling around the Sun. Of course I wasn’t around for some of those years, but I know she’s always been the unconventional one in the family. She’s the one that went to art school, the first to get married, the first to get divorced. She was the one that opened a store for interior design in a small town in Northwest Connecticut. She later managed one of the first antique centers in the area. While she may not have had the formal education of some of her peers, but she often could out smart them anyway.

During college, I’d often stop by after a hiking trip with friends in tow, and no matter how many, she somehow always had food in the house to feed them all, no matter how muddy or stinky we might be. I even managed to get her into a couple of caves one time.

Other times I’d come home from college for vacation or break and we’d end up in deep philosophical discussions or discussions about politics. We still do from time to time, though now, more often on the phone.

She’s taught me much of my appreciation for music, and I will never think of Joni Mitchell or Billie Holiday without her. And my kids will always have memories of sitting on the floor eating sushi with her. You might start to notice a theme here that involves food. I will add “wavy pizza” without any context.

I think I should add at this point that my mom is, in my opinion an awesome cook and I developed my love of cooking and baking from her.

All these memories and many more have been running through my head over the past few days. 75 seems like a magical number and I think it is. And the truth is, given the events of Covid over the past few years, this honestly was perhaps the best Easter and birthday I recall in awhile. I’m glad I was able to celebrate it with her and look forward to many more.

And while as she admit, her body is showing her age a bit, her spirit hasn’t slowed down one wit and I love her for that.

And Mom, I look forward to celebrating number 76 and many more with you. In the meantime, don’t trip over the robo-vac and enjoy all the flowers and so much more in your life.

Up a Creek…

Posted on by
Reply

Actually more like an upstream problem. Or two.

Or, another week in the life of a DBA and other duties as assigned

So a few weeks ago a developer at a client of mine reported that some recently deployed code wasn’t working. I tried it and of course it worked for me. That isn’t unusual since I have sysadmin rights on that box. I tried execute as using her ID and it failed. Not uncommon, sometimes in production permissions don’t get promoted the way they should. So I checked her permissions and the permissions of the users actively trying to call the stored procedure. Strangely, they all had the proper permissions, at least as far as I could tell. Then I had that lightbulb insight and realized I had been misinterpreting the error message execute as was giving me. “The server principle “DOMAIN\USerXYZ” is not able to access the database “ImportantDB” under the current security context.”

I realized I had been troubleshooting the wrong problem. It wasn’t that the person didn’t have permissions to execute, it was that no one other than sysadmins had the right to CONNECT to the database. A simple:

GRANT CONNECT SQL

And all was good to go! Or was it? It took us some digging to figure out why this had happened on a production database. Apparently when the database was designed in Dev, the developers had the rights they need to connect, so never thought about who else might need to connect. Apparently they had created it with very limited Grant Connect rights. When the database was moved to production, in this case, with a backup and restore the same lack of rights moved upstream with it.

Now, in the opposite direction, a vendor wanted to send a file to my client in their UAT environment. I fired up the PowerShell script to retrieve the file and decrypt it. The decryption failed. It took me awhile to figure out the problem. The client has a rule that every 2 years we must upgrade our RSA Public Key with them. Ironically, I had just completed the most recent update last month and moved it into production. Apparently though, their rule doesn’t apply to their UAT environment. Which came as sort of a shock to me, since they’re always so insistent on us following their security requirements. Of course beyond the irony of them not following their own rules, the file they had asked we download, wasn’t there.

The PM contacted them and they assured him the file would be there on Friday. Well here it is Tuesday and we’ll see if this time the file is there.

In any case, this time, the problem wasn’t promoting a change from UAT to PROD, but the client’s failure to move a change from PROD to UAT.

Such is life.

So sometimes I’m the creek without the paddle and sometimes I’m down the creek…

Unlocking the Secrets of the Universe, or at least Databases and Muscles

Posted on by
Reply

I think I speak for many DBAs when they say they’ve all had a programmer tell them, “The database is too slow, we need faster hardware.” Then as a DBA, you go in, look at their query and realize they’re running a table scan across a huge 100GB plus table without any indices. You analyze the data and overall usage further and decide to put in an index and suddenly their query is running in sub-second times instead of minutes.

Or, in a case I was working with with a client years ago, they had a stored procedure that sometimes would suddenly get slow. Their solution was to occasionally issue a WITH RECOMPILE command when calling it and things would get better. It worked, but was hardly ideal. After analyzing the stored procedure and usage I realized that it was suffering from a form of parameter sniffing and with a bit of work I was able to recommend a rewrite that solved their problem in a better way.

What is critical here is understanding HOW exactly data is stored and how the query engine works and how we can take advantage of how the database engine works to improve things.

And honestly, for me, I’ve always loved understanding “how things work.” I’ve marveled at seeing Bob Ward open up the debugger, live on stage, and show how a particular piece of code in the SQL engine works.

And now, I’m starting to undergo the same journey in the human body.

Let me caveat things by saying I am only half-way through my Anatomy & Physiology I class (and same with my Bio I class) and I greatly recognize how much more I have yet to learn (and that’s only out of what we know. We’re still learning more!)

However, I continue to be blown away with “how things work.” Mother Nature has had millions and millions of years to make things work. What strikes me as interesting at times is how both conservative and ingenious things can be. We all start from a single cell that divides and forms a sphere of cells. This soon forms a neural tube and then starts to specialize further. Somehow from that single cell we end up with our feet at one end and our skull at the other, completely different, but from the same source.

But, even then when you dig deeper you realize that at some level hands are really feet, but with an opposable joint and far more flexibility and dexterity. The internal structures are very similar. Then you move up to the arms and legs and realize even the limbs start out fairly similar: the upper limb has a single main bone and the lower limb has two bones. Of course the joints start to differ, a knee joint is VERY different from an elbow joint. Nature is conservative.

This week we started to study skeletal muscles and how they work. This builds on earlier lessons in both the parts of a cell and histology. A generic animal cell has sort of a skeleton made up of fibers the cell produces. These fibers can come in one of several forms. In most cases they’re simply there to give a certain structure to the cell, or perhaps via the use of other proteins, connect to other cells. They often may look haphazard in design and function, but they get the job done.

Until you look at skeletal muscle. Then you see two of the fibers are laid out a bit differently. One, the thin “actin” fibers are laid out radially around larger fibers, the myosin. You may see these fibers in other cells, but in muscle cells their layout is different. And this makes all the difference. The myosin fibers have “heads” on them that can, in the right circumstances both attach to the actin fiber, but also essentially pull on it by flipping its head from a “forward” to a “backwards” position. The head can then release, flop back to its original position, grab the actin again, and again flip back to the tail facing position. I’ve left out a lot of details, but if you think of yourself using both hands to pull upon a rope, you get the same basic idea. Or, for the caving readers among me, it’s a pair of handled ascenders and it’s basically using a Texas system!

To me, this is really an ingenious re-use of existing structures inside the cell. The more I learn about the human body (or biology in general) the more I’m amazed.

I’m loving “seeing under the covers” and this is part of it!