Can I handle AND rethrow exception in mysql?
Mark Smith
transaction and set an out parameter:
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SET success = -1;
END;
I am calling my proc via JDBC, and the only problem with this method,
is that the only thing my java process knows is that the stored proc
failed by inspecting the out parameter, the actual SQLException object
is never thrown so I have no more detail to log.
Without the handler my java process gets a great error message, like
exactly what column name / table couldn't be found, etc... However the
transaction is left open.
I would like to do something like this:
DECLARE EXIT HANDLER FOR SQLEXCEPTION ex
BEGIN
ROLLBACK;
SET success = -1;
THROW ex;
END;
I know the syntax is wrong here, but is this supported in any way?
Thanks
Lennart
From:
http://dev.mysql.com/doc/refman/5.0/en/faqs-stored-procs.html#qandaitem-22-4-1-1
22.4.1: Do stored procedures have a statement for raising application
errors?
Not in MySQL 5.0. The SQL standard SIGNAL and RESIGNAL statements are
implemented in MySQL 5.5.
/Lennart
Mark Smith
>
> 22.4.1: Do stored procedures have a statement for raising application
> errors?
>
> Not in MySQL 5.0. The SQL standard SIGNAL and RESIGNAL statements are
> implemented in MySQL 5.5.
>
> /Lennart
Thanks, but since 5.5 is not yet considered stable, are there any
guidelines on what is considered the best way to tackle this problem
in production code?
Maybe I am trying to solve a problem that does not exist... For
example:
- If I just allowed the stored proc to throw the original error
without rolling back, would the transaction remain open and leave
tables locked indefinitely?
- If so could I have a call to another stored proc to do the rollback
from the error handler in java?
- Other options?
Thanks for any help.
Lennart
Why do you have to do the rollback from within a procedure? Something
like (pseudocode):
conn = getConnection()
try {
conn.execute("call procedure ....")
} catch SqlException {
conn.rollback()
} catch ... {
...
}
conn.commit()
looks pretty normal to me
/Lennart
Mark Smith
My understanding was that it was better to leave data integrity checks
like this to the database. Moving logic like this into any and all
clients opens up much more possibility of getting the database into a
bad state.
Jerry Stuckle
Not necessarily. For instance, what if there is a correctable problem
with your data? You might want to resubmit the query.
That's why you have the option. I normally handle such things at the
application level.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstu...@attglobal.net
==================
Mark Smith
Surely then the application should hold that it in state to allow the
user to correct the data and resubmit to the DB only when it is
correct?
I admit I am a relative newcomer to this, which is why I am posting
here for guidance - but allowing for the possibility of an incomplete
transaction (orphaned data and all the headaches that come with that)
seems counterintuitive for most scenarios.
Jerry Stuckle
It depends on what the problem is. It's never good to have an
inconsistent database. However, there are times when the application
can correct for certain errors.
But since the application initiated the request that caused the error,
only the application can determine if it is a correctable error or not.
And the application should be making that decision, not the database.
Mark Smith
I think the errors that would cause these stored procs to fail are
definitely db issues, bad foreign keys, etc.
Plus, having the app leave a transaction open while the user figures
out what they (or the app) did wrong will leave the tables in a locked
state for other users...
Perhaps if my choice is between better error logging - and a safer db
interface I should opt for the latter?
Lennart
You will get neither by doing commit() or rollback() inside the
procedure. Imagine what will happen if someone calls your procedure as
part of a larger transaction.
/Lennart
Captain Paralytic
> I think the errors that would cause these stored procs to fail are
> definitely db issues, bad foreign keys, etc.
bad foreign key in the first place. I would suggest that the reason
for this would be a problem in the application and so you are
ultimately back with an application error.
Mark Smith
They won't, the coding standards I am following define 2 types of
stored procs:
Controlling procs - that are called directly by the clients, handle
errors, transactions, call smaller procs but never touch the data
themselves.
And worker procs - that are only called by controlling procs, modify
the data but are not concerned about transaction handling.
Peter H. Coffin
Bad foreign keys aren't uncorrectable. In fact, with some proper
planning, it's almost kind of expected to find "bad" foreign keys on a
regular basis.
Here's an idealized example of how this can happen:
You have a database with organizations that have attached (child)
persons with their own attached addresses, phone numbers, transaction
histories, etc. A list of some of these people meeting some criteria is
pulled from the database and sent off. (Maybe these people are being
invited to a party, and the organizer needs a list of who was invited to
manage followup and make guest lists.)
Joe Blow has a duplicate record; he's in the database as Joe Blow and
Jonas Blow. He gets two invitations. When Joe calls the party organizer,
he mentions this and that he prefers "Joe", and the organizer makes a
note of the duplication, crosses off "Jonas", and later calls HQ to get
the DB duplication fixed. The DB admin get the details and knows there's
a "preferred name" column to handle this kind of case, and adds the
Joe name to the proper place in the Jonas record, and moves all the
child records from Joe to Jonas, and deletes Joe. Three weeks later, the
party is a great success. The organizer sends the list back, and the
crossed-off Jonas record in the database gets marked "Did not attend".
There's also a Joe record with "Did attend" marked on it, with a key
field that does not exist in the DB.
What to do next will vary. Sometimes the process might be to run a fuzzy
match/dedupe to find if Joe exists in the database already and add him
if he doesn't. Sometimes the process will be to throw away the record.
Sometimes the process will be to send it to a reconciliation crew to be
addressed manually. A particularly-clever database designer will have an
entity attached to each important parent entity that contains "prior
keys" that can also be searched. But no database can manage having a
foreign key potentially in *two different places*, so it's something
that generally *MUST* be handled at the application layer, even the
"application layer" is actually handled and programmed in stored
procedures. It's still handled outside the formal database design
specification.
--
CS is about lofty design goals and algorithmic optimization. Sysadmining
is about cleaning up the fscking mess that results.
Mark Smith
I agree, but I need to protect against it as I will not have 100%
control over the client applications. And even if I did, I could never
guarantee they would be bug free.
It would be nice however to tell the client exactly what went wrong.
Jerry Stuckle
>
> I think the errors that would cause these stored procs to fail are
> definitely db issues, bad foreign keys, etc.
>
No, the db does not create a bad foreign key. The application does.
> Plus, having the app leave a transaction open while the user figures
> out what they (or the app) did wrong will leave the tables in a locked
> state for other users...
>
I never said leave the transaction open while the user figures out the
problem. The app should correct it, if possible. If not, the app
should perform the rollback.
> Perhaps if my choice is between better error logging - and a safer db
> interface I should opt for the latter?
Opt for the source of the error to get and handle the error. Correct if
possible, rollback if not.
You came here asking for help. The suggestions you have received from
multiple people have been in complete agreement.
Why do you continue to argue with people who have had many years more
experience than you? Haven't you gotten the idea you are wrong in your
thinking?
Mark Smith
> I never said leave the transaction open while the user figures out the
> problem. The app should correct it, if possible. If not, the app
> should perform the rollback.
>
> > Perhaps if my choice is between better error logging - and a safer db
> > interface I should opt for the latter?
>
> Opt for the source of the error to get and handle the error. Correct if
> possible, rollback if not.
>
Consider a proc that does multiple inserts and deletes. How does the
app know where in the transaction it failed? Calling it again after
the transaction is only half baked surely gets us into more trouble.
Is it not easier to maintain that transaction logic if it is enforced
in one place - the DB, rather than spread out in 100s of places over
all the clients?
>
> Why do you continue to argue with people who have had many years more
> experience than you? Haven't you gotten the idea you are wrong in your
> thinking?
>
I accept I probably am wrong, but the best way to learn is by asking
questions to understand why my thinking is wrong. Not blindly
accepting advice from anonymous strangers as gospel...
Jerry Stuckle
> On Feb 24, 1:14 am, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>> I never said leave the transaction open while the user figures out the
>> problem. The app should correct it, if possible. If not, the app
>> should perform the rollback.
>>
>>> Perhaps if my choice is between better error logging - and a safer db
>>> interface I should opt for the latter?
>> Opt for the source of the error to get and handle the error. Correct if
>> possible, rollback if not.
>>
>
> Consider a proc that does multiple inserts and deletes. How does the
> app know where in the transaction it failed? Calling it again after
> the transaction is only half baked surely gets us into more trouble.
>
I don't have procs which do that.
> Is it not easier to maintain that transaction logic if it is enforced
> in one place - the DB, rather than spread out in 100s of places over
> all the clients?
>
My transaction logic is not spread out over 100s of places in my
applications.
>> Why do you continue to argue with people who have had many years more
>> experience than you? Haven't you gotten the idea you are wrong in your
>> thinking?
>>
>
> I accept I probably am wrong, but the best way to learn is by asking
> questions to understand why my thinking is wrong. Not blindly
> accepting advice from anonymous strangers as gospel...
And not arguing with those same people. Or they will soon stop
answering you. As I am doing now.
Mark Smith
OK in worst case scenarios where the app cannot automatically recover,
it should fail gracefully and log whatever detail is possible. Right?
In cases where it can detect and fix the problem, do you think the
transaction should be handled by the clients or the db?
Peter H. Coffin
> On Feb 24, 1:14�am, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>> I never said leave the transaction open while the user figures out the
>> problem. �The app should correct it, if possible. �If not, the app
>> should perform the rollback.
>>
>> > Perhaps if my choice is between better error logging - and a safer db
>> > interface I should opt for the latter?
>>
>> Opt for the source of the error to get and handle the error. �Correct if
>> possible, rollback if not.
>>
>
> Consider a proc that does multiple inserts and deletes. How does the
> app know where in the transaction it failed? Calling it again after
> the transaction is only half baked surely gets us into more trouble.
Are you just making up conditions in which your idea might be a good
plan, or do you actually think this is a good practice or a good design?
> Is it not easier to maintain that transaction logic if it is enforced
> in one place - the DB, rather than spread out in 100s of places over
> all the clients?
Why would hundred of clients be doing exactly the same kind of
transaction? I'm pretty sure I've never, ever seen that in production.
Usually anything that has hundreds of clients will end up talking to a
cluster of service programs that then manages the database interaction.
Even if there are hundreds of clients attaching to a single database,
they're hundreds of *identical clients built from the same source
modules*.
>> Why do you continue to argue with people who have had many years more
>> experience than you? �Haven't you gotten the idea you are wrong in your
>> thinking?
>
> I accept I probably am wrong, but the best way to learn is by asking
> questions to understand why my thinking is wrong. Not blindly
> accepting advice from anonymous strangers as gospel...
But you're not asking about what *we're* telling you. You're asking
about what you're telling us.
--
Cunningham's Second Law:
It's always more complex than you expect, even when you take
Cunningham's Second Law into account.
Mark Smith
> > Is it not easier to maintain that transaction logic if it is enforced
> > in one place - the DB, rather than spread out in 100s of places over
> > all the clients?
>
> My transaction logic is not spread out over 100s of places in my
> applications.
>
I never said anything about *your* transaction logic. If you have
complete control over how and what clients connect to the DB, I see
your point.
In my case I do not, from the DB perspective leaving it up to the
client to enforce transactionality leaves open the window that
different clients will have different transaction logic, or worse
ommit it completely. Or is there a way to have the best of both
worlds?
> >> Why do you continue to argue with people who have had many years more
> >> experience than you? Haven't you gotten the idea you are wrong in your
> >> thinking?
>
> > I accept I probably am wrong, but the best way to learn is by asking
> > questions to understand why my thinking is wrong. Not blindly
> > accepting advice from anonymous strangers as gospel...
>
> And not arguing with those same people. Or they will soon stop
> answering you. As I am doing now.
>
I stated an opinion and asked you why you think it's wrong. If you
think that's an argument then you are very oversensitive and probably
shouldn't respond.
Mark Smith
> On Wed, 24 Feb 2010 01:12:50 -0800 (PST), Mark Smith wrote:
> > On Feb 24, 1:14 am, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> >> I never said leave the transaction open while the user figures out the
> >> problem. The app should correct it, if possible. If not, the app
> >> should perform the rollback.
>
> >> > Perhaps if my choice is between better error logging - and a safer db
> >> > interface I should opt for the latter?
>
> >> Opt for the source of the error to get and handle the error. Correct if
> >> possible, rollback if not.
>
> > Consider a proc that does multiple inserts and deletes. How does the
> > app know where in the transaction it failed? Calling it again after
> > the transaction is only half baked surely gets us into more trouble.
>
> Are you just making up conditions in which your idea might be a good
> plan, or do you actually think this is a good practice or a good design?
>
Neither, this is just the situation I'm in. Perhaps the whole thing
does need redesigned.
> > Is it not easier to maintain that transaction logic if it is enforced
> > in one place - the DB, rather than spread out in 100s of places over
> > all the clients?
>
> Why would hundred of clients be doing exactly the same kind of
> transaction? I'm pretty sure I've never, ever seen that in production.
> Usually anything that has hundreds of clients will end up talking to a
> cluster of service programs that then manages the database interaction.
> Even if there are hundreds of clients attaching to a single database,
> they're hundreds of *identical clients built from the same source
> modules*.
You're right, 100s of different clients was over the top, It's a db,
being used currently by a single web service... However it will be
used by others later on, so I am trying to make life easier for others
that will have to use this after me.
> >> Why do you continue to argue with people who have had many years more
> >> experience than you? Haven't you gotten the idea you are wrong in your
> >> thinking?
>
> > I accept I probably am wrong, but the best way to learn is by asking
> > questions to understand why my thinking is wrong. Not blindly
> > accepting advice from anonymous strangers as gospel...
>
> But you're not asking about what *we're* telling you. You're asking
> about what you're telling us.
I'm just trying to give my question context. Not picking any fights.
Jerry Stuckle
> On Feb 24, 11:56 am, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>>> Is it not easier to maintain that transaction logic if it is enforced
>>> in one place - the DB, rather than spread out in 100s of places over
>>> all the clients?
>> My transaction logic is not spread out over 100s of places in my
>> applications.
>>
>
> I never said anything about *your* transaction logic. If you have
> complete control over how and what clients connect to the DB, I see
> your point.
>
I wasn't going to respond any more, but I will one more time.
No, you didn't. And no, I don't always have complete control over what
clients connect to the db. Especially when you're talking a project
with a couple of hundred programmers. The logic is the same.
> In my case I do not, from the DB perspective leaving it up to the
> client to enforce transactionality leaves open the window that
> different clients will have different transaction logic, or worse
> ommit it completely. Or is there a way to have the best of both
> worlds?
>
No, the database is responsible for maintaining consistency - that's
what foreign keys are for. The transaction, however, is responsible for
handling errors it created.
>>>> Why do you continue to argue with people who have had many years more
>>>> experience than you? Haven't you gotten the idea you are wrong in your
>>>> thinking?
>>> I accept I probably am wrong, but the best way to learn is by asking
>>> questions to understand why my thinking is wrong. Not blindly
>>> accepting advice from anonymous strangers as gospel...
>> And not arguing with those same people. Or they will soon stop
>> answering you. As I am doing now.
>>
>
> I stated an opinion and asked you why you think it's wrong. If you
> think that's an argument then you are very oversensitive and probably
> shouldn't respond.
Are you trying to antagonize everyone in this newsgroup? You're sure
going about it the right way. Keep it up. Soon you won't be able to
get ANY answers in this newsgroup.
Remember - we are all volunteers, and are trying to help YOU. But when
you get to the personal attacks, then
<plonk>
Peter H. Coffin
Yup. But getting back to the original point, it can't do that if it
doesn't get a good, detailed error thrown back to it. (Which is, if you
remember, a condition that depends on having a version of MySQL that
one is not likely to find in current production use.) An intermediate
process intercepting the error and automatically rolling back then
saying "Something bad happened!" doesn't make useful logging easy. All
there is is the input data, and whatever was the problem then may not
be still the case whenever someone gets around to looking at the logs
later. In any serious case, someone's going to at least want to know
what kinds of things were being problems with the input data, by count
and type of problem.
> In cases where it can detect and fix the problem, do you think the
> transaction should be handled by the clients or the db?
That kind of phrasing is too elastic to be useful.
The controlling application level should be doing the transaction
control: starting a unit of work, committing if successful, rolling back
if something fails, because the *what* and *why* of the failure may be
something that the application can account for (and can immediately
reprocess), and only the application level will know whether that will
be the case. Now, rarely, there's good and sufficient reason to put some
of the application into SPs, but they tend to work best as "black box
functions" or like APIs, with static and defined inputs and outputs and
no with no appeal after they do what they do. Essentially, the more you
put into them and outside of your overseeing process, the more like
always having the worst-case scenario you described.
"The db" is just storage. It's really sophisticated storage, but it's
still best to think of it as just someplace to put stuff so that you can
get it later. Don't fall into thinking that tables and SPs are a package
in and of themselves, or should be. If for no other reason than at some
point you might have to retool things to work on DB2 or Postgres.
--
Advocacy leads to disagreement; disagreement leads to Holy Wars; Holy
Wars lead to suffering. Advocacy is the first step on the road to the
kill-file.
-- Joe Zeff in the Monastery
Mark Smith
Thanks, you've given me a lot to think about.
I still find the concept of all-or-nothing style stored procedures
appealing, and if the current stable version of mysql supported
RESIGNAL this would be a no brainer.
But I'm going to take a step back and look at the overal design with
some of the stuff you raised in mind.