DEV Community: Tamas Rev

How to execute SQL?

Tamas Rev — Fri, 15 Dec 2017 10:36:45 +0000

People claim that it’s dangerous to generate and run SQL in the production. We should execute SQL in production carefully.

Wait! Why is it important to run SQL carefully? More importantly, how to run SQL carefully? It’s not about typing SELECT slowly, is it?

Why should we do it carefully?

If we do any change in production system, we should do it carefully. Whether we deploy new code, upgrade the operating system or run any SQL, we should not mess up the system that’s already up and running. Users don’t like downtime. If users can’t access our page that hurts business. So that’s why.

How can we make production changes carefully?

This carefulness falls into the following three categories:

We should make sure that nothing goes wrong during the change.
We should make sure that when something goes wrong, it has a minimal impact.
When something goes wrong, we should have a plan how to fix it

Making sure nothing goes wrong

So, we should test our change before deploying it. In case of SQL we should have an identical database structure that we can play with. It’s even better to have a dataset realistically similar to that of the production database. Then we can run our stuff on that.

These kind of databases usually need a separate environment. Usually the companies are trying to spare those resources. So we might have a handful of multi-purpose databases. We better don’t mess them up, not even during testing. How can we do this? You can see my tricks in the following sections.

Trick 1: Testing the `WHERE` clause

Before running a DELETE or an UPDATE, we should run a SELECT with the same WHERE clause. So we know we’re targeting the desired dataset.

Trick 2: Using a transaction

We can run our stuff in a transaction. In Oracle SQL Developer we can simply turn off auto commit. So if we’re happy with our results, then we can push the commit button. If we aren’t then we push the rollback button.

Microsofts SQL Server Manager Studio doesn’t provide this functionality. But we can simulate it like this:

BEGIN TRANSACTION

-- do stuff

-- run SELECT-s to make sure we like the result

ROLLBACK TRANSACTION

Once we have the desired result, we can run it with a COMMIT instead of a ROLLBACK.

Trick 3: Having a backup of the test database

It’s good to have a backup of the test database. This can be a snapshot of a virtual machine, or a snapshot of the database files. So if everything else fails, you can always go back to the previous state.

Making sure the impact is minimal

There are several strategies to do risky stuff with minimal impact.

We can implement small changes. So the result should be small. Usually it means Continuous Integration, or maybe Continuous Delivery. Lots of automatic tests make it difficult to do stupid things on production. I don’t have much experience with those. As far as I heard, it requires lots of concentrated effort from the management and the technical teams to have Continuous Delivery. They say the problem itself if hard and there is no easy way around it. If you want it, then the whole company must figure out a way to do that in their business.

The Bank Card business are regulated by the PCI security standard. They must comply with a strict change management policy. So they roll out the changes in the off-hours. This is how they can have minimal impact.

Having a plan to fix it

This plan is usually about rollbacks and backups. In case of code change there must be a way to roll it back. In case of a database change, there must be a way to roll it back.

This means that before we go live with a change, we should test the rollback too.

Backups are problematic because the online traffic doesn’t stop for those couple of hours while you’re restoring the backups. Usually we should avoid this.

Wrapping up

Try not to do ad-hoc changes on production databases.
Test the WHERE clause before changing the data.
Test the SQL commands with transactions.
Test the changes on test systems first.
Test the rollback too.
Have a back-up.

What are your tricks?

Originally appeared at tamasrev.

How to debug in production

Tamas Rev — Mon, 04 Dec 2017 17:03:53 +0000

We’ve all seen bugs in production. Sometimes we can reproduce them, sometimes we can’t.

When we can reproduce them, then we fix it quickly. The we do some regression tests to see if we broke anything else. And then we’re done.

When we can’t reproduce it, that’s a totally different story.

How do we know that we can’t reproduce it?

This sounds simple, doesn’t it? We tried to use the system, and the problem hasn’t occurred. It is possible that we just couldn’t reproduce it yet? Is it possible that we’re only one step away from reproducing it?

Let’s move from philosophical questions to pragmatic ones. Is it a waste of time tying to reproduce a NullPointerException? Is it a bigger waste of time blindly adding some code? How do we know it?

Usually we have a process about reproducing. This process is usually implicit, nobody writes it down. My process looks something like this:

Read the bug report
Quickly check the exception trace, hoping that it reveals an obvious problem
Try to reproduce it based on what’s written on the bug report
Analyze the logs and the related source code to figure out what could’ve happened
Try to reproduce it again
In case of multi-threaded programming, tweak the threads and locks to reproduce an odd situation

At every step I might go back to read the bug report and the logs. After a day or two I can either reproduce the bug, or I give up the search.

When I give it up, then I do either or both of the two things:

Reproduce and fix the issue with a unit-test.
Add more logging.

If we reproduce a problem with a unit test, then we’re likely to fix only a symptom. Say for instance, if we add another null-check, then we still won’t know why a certain method receives invalid data. Soon we’ll see other NullPointerException-s.

When we add logging, then we’ll use lots of resources like CPU, Disk, Memory. Operators won’t allow it most of the time. They usually agree that it’s crucial to fix the bug. Yes, they want to help you figure out the root cause. No, sorry, they can’t let you add logging.

Luckily, there are a few approaches to get debug data from production effectively.

Logging fix 1: Sampling

One approach is sampling. We don’t have to log every user session. We can select every 1000th user session and log all of their data. Alternatively, we can use a random generator to select every 1000th user session and log that.

This is useful only if the bug occurs quite frequently. Just keep in mind, we need to sample user sessions, not log events. So we’ll get detailed information on what went wrong.

You might make the log sampling configurable. So if production operators finds that our logging still uses too many resources during peek time, they might turn it off for a couple of hours.

An alternative of log sampling is A/B testing. If our architecture allows, we might turn on debug logging on a fraction of our servers.

Logging fix 2: Logging a session in memory

Sometimes we can log debug data in memory. If an error occurs in that user session, then we can write it to a file. If not, then we simply release the memory we’ve allocated.

This is useful if the error occurs infrequently. In this case we have to do something to catch the bug as soon as it happens. However, production operation won’t necessarily be happy about it. It depends on the memory needs of our debug logging.

We can combine this kind of logging with log sampling. Say for instance, we log debug data for every 10th user session, and write it to file when an error occurs.

Logging fix 3: In-memory ring buffer, by Kofa.

István Kovács explained a third approach: an in-memory ring buffer. Every log goes into this in-memory ring buffer. This buffer has a configurable size. Also, it’s a ring buffer, so the most recent logs overwrite the oldest lines.

The logs that usually go to a file, they still go to a file. When an error occurs, the whole ring buffer gets written to a file.

This is really cool because the memory overhead is limited. If you set the size of the ring buffer right, then you’ll have all the debug data you need. On the other hand, you might spend some time to find the right size for this ring buffer.

What’s your approach?

Originally appeared at tamasrev.

What can we learn from arrogant bosses?

Tamas Rev — Tue, 28 Nov 2017 21:09:56 +0000

I believe, everybody encountered an arrogant boss through her career. An arrogant boss is somebody who… Well, someone who looks like an arrogant boss to me, he might be fine for you.

Nobody is perfect, not even the managers. There are some kind of personality flaws that I can hardly tolerate while it might be fine for others. Also, there are personality flaws that I tolerate well, while others can’t handle it that well.

The fun fact is that we can even learn from this kind of bad experiences.

So, how do you know you’re working for an arrogant boss?

Arrogant people in general don’t respect your personal boundaries. They judge you. They criticize you. They make you work overtime a lot. They use language that you don’t like. And so on.

So, your boss does something that you can hardly tolerate. He is pushing your buttons a little too much. He makes your life miserable. Too bad that you didn’t foresee it during the interview process. What now?

How to deal with it?

You can try to tolerate it. I’m serious. Tolerance and self-composure are like a muscle. The more you train them, the stronger they become. You can practice how to calm yourself down in stressful situation. It’s going to be useful through your life.

You can look at your boss and try to guess what he needs from you – give it to him if you can. You can learn how to read other peoples body language.

What are the advantages?

In the previous paragraphs you already saw what you can learn in this kind of situation. I.e. you might become stronger and more resilient. But that’s not all!

You might learn how this craziness is possible within the organization. So you can level up your systemic thinking skills.

If you can get hardly anything right, then you get to decide what’s important for you. An arrogant boss is a great opportunity to get more conscious about your values. Some people want to do a good job. Some want to do god for the organization they are working for. Others need stimulating tasks. Some folks are people pleasers, and so on. So, if your boss fails to give clear priorities, then, ironically, you get to decide what’s important.

If you work on monotonous tasks, then you’ll learn how to tolerate monotony. Also, you’ll learn how to deal with that kind of tasks. Say for instance, lots of maintenance work is going to improve your problem-solving skills. Believe or not, there is a book about debugging effectively. It’s a really good one.

Do you have to work on a code base you can’t stand looking at? Come on, there’s a book about that too. Distasteful it might be, a legacy code base is a good place to enhance your analytical skills. Moreover, you’ll learn how to pin down the problem to a couple of classes. You’ll learn what to focus on. It’s an incredibly important skill.

When to stop?

Tolerance and self-composure are like a muscle. You can chronically exhaust them.

Look for signs of burnout and depression. If you’re frequently moody, tired or irritated then it’s probably getting too much.

Again, it’s about knowing your priorities. You need to know how much convenience you want to sacrifice for the sake of learning. However, when it’s getting under your skin then it’s time to move on. You should not trade your mental health for a job.

Anyway, these skills you’ve just learned at your job, they are going to pay off well – in your next position. Don’t stick to a bad job for too long.

Originally appeared at tamasrev.

Why not write clean code?

Tamas Rev — Fri, 13 Oct 2017 15:06:19 +0000

I think you already know what clean code is: this is the famous Uncle Bob book, which provides a very accurate definition of readable code. Ever since that book was published there have been a lot of talking about Clean Code. People talk about craftsmanship, readability. They even go to code retreats. Should you say something publicly against clean code you’ll find yourself on a "never to be hired" list. Talking about Duct-Tape Programmers is not cool anymore. Even ~~Dave~~ Paul Graham agrees that we have to write re-readable code. OK, maybe Paul said that years before Uncle Bob published this book, but you get the point.

Sadly, nobody asks why not write clean code? I have many independent reasons for that:

1. Any fool can point out mistakes in clean code.

Suppose you spent some time and effort on making your code clean. You removed duplications, you used short and consistent names, your methods are easy to understand. At a certain point you decided that this much polishing is enough – shipment is a feature anyway.

Some time later a colleague will look at your code and point out actual problems. The more names you use the more you can misspell. The shorter methods you use the more easy it is to find duplication. So your coworker walks over and tells you how to improve it. Shame, isn’t it?

None of this could have happened if you wrote messy code. In that case the best your coworker could say is “it’s dirty”. There is a good reply for that: “So what? It works! The louder you say that the more convincing it is. Nobody would find bugs in your code just by looking at it.

2. You like to be important

Suppose you go to a vacation for 2-3 weeks. When you come back your colleagues tell you that a user reported a bug in your code – and they fixed it. They’ll tell you that they factored out a few things without introducing new bugs , thanks to your thorough unit tests. After that refactor, they would say, they could easily fix the original bug and release the new version.

Something doesn’t feel right, does it? It sounds like you are replaceable – and now everybody knows it. It should never happen. Wouldn’t it be better if they called you in the middle of your vacation to fix that thing ASAP? It’s a little inconvenient at first, but who cares? Your family will know that you are a super-important world-class hacker. Your coworkers will know too that if you leave, they can rewrite your module from scratch.

So what to do when they call you with an urgent problem? You have many options:

You can kindly explain that it’s a very easy problem and you can tell them how to fix it. If that method didn’t work out well you can still blame them for misinterpreting what you said
Quite often you’ll get your hands dirty and fix the problem yourself
Sometimes you can be a little harsh, e.g. “Can you guys get done anything without me?”
Bottom line: everybody will treat you with utter respect.

3. You don’t like OO principles

There are these acronyms like DRY or DIP. If you google them you’ll find long explanations about, basically, nothing. All they are saying that for some abstract reasons like flexibility or modularity you’ll have to write more complex code. Once you start paying attention to these principles your code will be full of Design Patterns.

Come on! Do you let these principles diminish your effort to patternless code? You know you can do better! You can say that all those patterns add unnecessary complexity to your code. Should anyone question why you have 7 almost identical variations of a code block across 3 methods you can always tell them: “this way you can easily see the flow: one step after another”. The more often you say this the more convincing it is.

4. You want to create jobs for others

I know you are a true altruist deep down inside. Once you write that service you don’t want the company founders to take all the money. You want them to hire more testers and software engineers to understand and maintain the thing you created. What can be more noble than creating jobs and puzzles for other people?

Are there any real reasons for not writing clean code?

OK, let’s put the jokes aside. Are there any reasons for not writing clean code? Well, one could ask if are there any reasons for writing bad code. That’s a big no-no. However, the cleanliness of code is rather a 0-1 scale than a 0/1 boolean. I believe there are a few cases when you don’t need to write extra-clean code:

1. You don’t know how to write clean code

Indeed this is a real one. Say you want to learn how to write clean code. You are really enthusiastic, however, you tend to take everything literally. (I am also guilty of this.) Well, then, you shouldn’t write clean code right away. If you try to apply all the techniques in that book then you’ll end up with the worst code you ever wrote. I suggest to learn and practice them one by one. Once you can apply a technique almost automatically then you can go on to the next. Sooner than you think you’ll write amazing code.

2. You are in crunch mode

This is real too. Sometimes you are in crunch mode. Maybe there is a bug in your service and your company is loosing hundred thousand dollars each day until it’s fixed.

I’m hesitating to mention the case when you work for a startup, having to release something before your competition does. I’m hesitating because you can save most of the time on choosing RAD tools. You can save some time (and gain a lot of bugs) for not using TDD (more on this here). Although it is clear that startups cannot spend time on extra-clean code, writing bad code won’t help either.

For whatever reason you are in crunch mode: Go ahead, fix and ship for the greater good. Just don’t forget to clean up the mess later.

Originally appeared at tamasrev blog.

DEV Community: Tamas Rev

How to execute SQL?

Why should we do it carefully?

How can we make production changes carefully?

Making sure nothing goes wrong

Trick 1: Testing the WHERE clause

Trick 2: Using a transaction

Trick 3: Having a backup of the test database

Making sure the impact is minimal

Having a plan to fix it

Wrapping up

How to debug in production

How do we know that we can’t reproduce it?

Logging fix 1: Sampling

Logging fix 2: Logging a session in memory

Logging fix 3: In-memory ring buffer, by Kofa.

What can we learn from arrogant bosses?

So, how do you know you’re working for an arrogant boss?

How to deal with it?

What are the advantages?

When to stop?

Why not write clean code?

1. Any fool can point out mistakes in clean code.

2. You like to be important

3. You don’t like OO principles

4. You want to create jobs for others

Are there any real reasons for not writing clean code?

1. You don’t know how to write clean code

2. You are in crunch mode

Trick 1: Testing the `WHERE` clause