Database Error Detection and Recovery

CowboyRobot writes "ACM Queue has an interview by Steve Bourne with Bruce Lindsay, responsible for a lot of the SQL and RDBMS we use today, in which they discuss error detection and recovery. My favorite part other than the photos is the definition of Heisenbugs - those problems that disappear only when you explicitly look for them."

Comments

This is why MySQL ignites flamewars

[Anonymous Coward]

A good design principle is: either do what you're told to do or tell us you didn't do it and why, but don't do something completely different.

Exactly. Compare and contrast with MySQL's behaviour.

• NULL inserted into a NOT NULL column silently alters the data to fit.
• VARCHAR values have trailing whitespace silently removed without asking.
• Dividing by zero is not an error.
• Inserting a value into a column that violates its constraints doesn't result in an error; MySQL guesses at the "correct" value instead. For example, limiting an integer column to 4 digits, and attempting to insert 99999 will result in 9999 being inserted without any error.
• If MySQL finds that it can't create certain table types, it simply ignores referential integrity.

That's why there are loads of people who point out that you can't trust MySQL for important data, or that it isn't a "real" database. A real database tells you when it fails, which is something that is necessary for trusting it with data integrity.

The key point here is that if you go to sea with only one clock, you can't tell whether it's telling you the right time.

Ahh... but a man with one clock always knows the time - but a man with two is never quite sure :).

Re:This is why MySQL ignites flamewars

[ajs]

Welcome to the obligatory MySQL flame that must be attached to all database-related postings.

Any second now, the parent is going to be modded down, right? Everything noted in the parent is well documented functionality in MySQL, which takes the approach of not generating application-breaking exceptions, and allowing you to split of data validation as either a pre-processing step or a more macroscopic endevor.

You may not like this approach. If you don't, then don't use MySQL. There are lots of other (very good) choices. Slashdot doesn't need your redundant and off-topic flames.

Mods, please start swinging that clue-bat.

Re:This is why MySQL ignites flamewars

[Anonymous Coward]

Everything noted in the parent is well documented functionality in MySQL

Well documented, perhaps, but nevertheless utterly wrong and often in violation of the SQL specifications.

Slashdot doesn't need your redundant and off-topic flames.

Try reading the article. I was pointing out that MySQL's behaviour goes 100% in opposition to what the article calls a "good design principle". How on earth is that off-topic?

Re:This is why MySQL ignites flamewars

[Anonymous Coward]

It's funny how that idiot isn't smart enough to argue the philosophical differences

Before calling me and everybody who agrees with me names, go back and read what I posted. Most of it's a case of asking MySQL to do something, MySQL being unable to do it, and instead of throwing an error, doing something completely different.

Whether you prefer your data integrity checks in your database or in your application is irrelevent to the point I was making. The person claiming that MySQL's trying not to generate "application breaking" exceptions (how fragile are these applications that they can't handle a database error?) - if they were doings things as you prefer - the errors wouldn't occur in the database layer at all, as the application layer would have already caught them.

Make error message meaningful!

[martyb]

One of the things that is addressed to some extent in the article is the need to make error messages meaningful! There is nothing more frustrating to me than to encounter an error message like "syntax error."

At a minimum, an error message should have a Unique ID of where in the code this message is coming from, what was expected, what was actually found, and the context where it was found.

EXAMPLE:

for (i=1;i<=10;i++) {printf "%d\n', i}

Which would you prefer:

1. Syntax error in line 1.
2. ERROR [ID=WXY1234] found "'" where expected """ in statement: "{printf "%d\n', i}" on line: 1.

In my experience, meaningful error messages save more debugging time than it takes to put them in.

Re:Make error message meaningful!

[Anonymous Coward]

I agree with your sentiment, but I think that this particular example can be used to describe why error messages are exactly the way they are. The compiler has no way of knowing that you DIDN'T want a single quote, comma, space, letter i, and closed curly brace to be part of your string literal -- they're all valid characters for inside a string literal, after all. It has no idea that you typed a single quote in error instead of a double quote -- Maybe you want the output to look like:

1
',i}2
',i}3
...

and your only mistake was forgetting a final ",i); It is only when it gets to something that is clearly wrong (like the NEXT double quote in the file, which is followed by something that doesn't look like the proper token -- or maybe the end of the file itself) that it knows something was wrong. It would take a lot of guessing to scan through the mess that looks like a string literal and arbitrarily decide that it must have been the single quote (also used as an apostrophe) where you went wrong. I think the best thing it could have told you was that it found an error immediately after parsing the string literal that starts on line 1, show you the line, and point to the character that starts the literal. Anything beyond that is mere speculation.

I hand crafted a (simple) C compiler when an undergrad, and figuring out where the stream of good tokens turns to mush is very hard. Often by the time you realize there's a problem, you already missed the real problem.

I agree you should be as explicit and precise as you can in telling the user, but there are so many ways to screw things up, and they look so much like unusual-but-legal syntax that it's probably better to tell the user / developer what you actually do know, rather than guess about what might have been wrong.

Now, on the other hand, if your statement was

int y = ;

the compiler should probably be able to tell that the equals operator needs an operand of some kind on the right, and there was none. It ought to tell you immediately that the problem was a missing right hand operand for the equals operator, and it should be able to tell you the exact position of the equals that is missing the operand. Just spitting out "syntax error" in a case like that is a little weak.

Re:Too much slashdot.

[Anonymous Coward]

And /. needs better thread handling too. When there's to much posts, the thread is splited in 72 pages. Pages 1, 2, and 3 are the same post reappearing over and over, then page 4 skipped a couple messages. You have to manually change the startat= parameter in the URL to see thoses missing posts. This is nuts. I mean, how difficult is it to code message threading? It's not rocket science. This makes /. look like ass when they are complaining about Microsoft bugs.

Java does exactly what Bruce wants

[chaves]

"That's one of the real problems in today's programming language architecture for exception handling. Each component should list the exceptions that were"That's one of the real problems in today's programming language architecture for exception handling. Each component should list the exceptions that were raised: typically if I call you and you say that you can raise A, B, and C, but you can call Joe who can raise D, E, and F, and you ignore D, E, and F, then I'm suddenly faced with D, E, and F at my level and there's nothing in your interface that said D, E, and F errors were things you caused. That seems to be ubiquitous in the programming and the language facilities. You are never required to say these are all the errors that might escape from a call to me. And that's because you're allowed to ignore errors."

I bet he didn't look into Java. Java (at least) allows and enforces that. A method will only throw an exception if declares to do so. A caller is forced to provide appropriate handlers or to declare it throws the exceptions not handled at its level. If a method can throw A, B or C but gets D during its execution, it has to in some way map D to either A, B or C (or not throw an exception at all).

Of course, I am talking here about checked exceptions. Unchecked exceptions are supposed to represent *bugs*, and nobody should be trying to capture those.

The sad thing is that even seasoned Java programmers do not understand how to write code w.r.t. exception handling. And beginners are usually turned off by the verbosity required by exception handling, so it is usual to see code where people capture (because they are forced by the language) and ignore exceptions (because they are too lazy and/or stupid to understand the consequences).

Re:Java does exactly what Bruce wants

[koreth]

it is usual to see code where people capture (because they are forced by the language) and ignore exceptions (because they are too lazy and/or stupid to understand the consequences).

Ignoring exceptions completely is almost always a bad idea (though what do you do to handle, say, the InterruptedException that can be thrown by Thread.sleep(), or a CloneNotSupportedException from one of your own classes that you know is cloneable?) But there is some legitimate difference of opinion about whether Java's checked exceptions were a good idea or not.

In my Java code I'm pretty paranoid about catching exceptions and handling them in as intelligent a way as I can, and even so I've run into plenty of situations where there's really no good way to recover from an underlying error and I end up just repackaging the exception into a higher-semantic-level one and tossing it upstream, where the upstream code does the same thing, all the way back out to the UI code, which displays an error message. At which point all I've achieved is cluttering up the intermediate layers of code with useless exception handlers when I could have gotten exactly the same effect by just catching a superclass exception in the UI code and displaying the same error message. (In addition to catching any specific exceptions that would cause a different result, of course.)

Most likely anyone who's written a Java app of any appreciable size has run into exactly the same thing. In theory, and in small sample snippets of code, checked exceptions seem great. In practice, even some experienced Java gurus find them more hassle than they're worth. I'm quite certain that over the years I've spent far more time writing code to handle checked exceptions than they've saved me in debugging or diagnosis time. That to me is not the sign of a helpful language feature.

Re:Rite of Passage

[Anonymous Coward]

Of course, another senior programmer saw the statemnent and removed it just before it was shipped to the customer...

As well he should have. If you need something like that to stay, you need a comment explaining its purpose. "The following statement is never executed but necessary to work around a compiler bug" would be helpful. You could even describe the bug so they could check if it's still necessary once the next version of the compiler is released.