Do not display "Statement interrupted by user" event exceptions when database is shutting down

This doesn't happen very often, but when shutting down a very busy 11.0.1.2276 database which has several events running, the "Statement interrupted by user" exception is recorded in the console log.

Since one of these events has an unconditional EXCEPTION handler which does not RESIGNAL any exception, it looks like EXCEPTION handlers are being bypassed during the shutdown process... that makes sense.

However, it does not make sense (to me) to display these messages in the console log... in other words, "yeah, sure, the statement was interrupted... the steenking database is being shut down!" :)

I. 10/03 13:58:41. SQL Anywhere Network Server Version 11.0.1.2276 I. 10/03 13:58:41. Workgroup edition
...
I. 10/03 14:12:33. Database server shutdown requested by DBSTOP
E. 10/03 14:12:38. Handler for event 'event1' caused SQLSTATE '57014'
E. 10/03 14:12:38. Statement interrupted by user
E. 10/03 14:12:38. Handler for event 'event1' caused SQLSTATE '57014'
E. 10/03 14:12:38. Statement interrupted by user
E. 10/03 14:12:38. Handler for event 'event1' caused SQLSTATE '57014'
E. 10/03 14:12:38. Statement interrupted by user
E. 10/03 14:12:38. Handler for event 'event1' caused SQLSTATE '57014'
E. 10/03 14:12:38. Statement interrupted by user
E. 10/03 14:12:38. Handler for event 'event1' caused SQLSTATE '57014'
E. 10/03 14:12:38. Statement interrupted by user
E. 10/03 14:12:38. Handler for event 'event1' caused SQLSTATE '57014'
E. 10/03 14:12:38. Statement interrupted by user
E. 10/03 14:12:38. Handler for event 'event1' caused SQLSTATE '57014'
E. 10/03 14:12:38. Statement interrupted by user
E. 10/03 14:12:38. Handler for event 'event1' caused SQLSTATE '57014'
E. 10/03 14:12:38. Statement interrupted by user
E. 10/03 14:12:38. Handler for event 'event1' caused SQLSTATE '57014'
E. 10/03 14:12:38. Statement interrupted by user
E. 10/03 14:12:38. Handler for event 'event1' caused SQLSTATE '57014'
E. 10/03 14:12:38. Statement interrupted by user
E. 10/03 14:12:38. Handler for event 'event1' caused SQLSTATE '57014'
E. 10/03 14:12:38. Statement interrupted by user
E. 10/03 14:12:38. Handler for event 'event1' caused SQLSTATE '57014'
E. 10/03 14:12:38. Statement interrupted by user
E. 10/03 14:12:38. Handler for event 'event1' caused SQLSTATE '57014'
E. 10/03 14:12:38. Statement interrupted by user
E. 10/03 14:12:38. Handler for event 'event1' caused SQLSTATE '57014'
E. 10/03 14:12:38. Statement interrupted by user
E. 10/03 14:12:38. Handler for event 'event1' caused SQLSTATE '57014'
E. 10/03 14:12:38. Statement interrupted by user
E. 10/03 14:12:38. Handler for event 'event1' caused SQLSTATE '57014'
E. 10/03 14:12:38. Statement interrupted by user
E. 10/03 14:12:38. Handler for event 'event1' caused SQLSTATE '57014'
E. 10/03 14:12:38. Statement interrupted by user
E. 10/03 14:12:38. Handler for event 'event1' caused SQLSTATE '57014'
E. 10/03 14:12:38. Statement interrupted by user
E. 10/03 14:12:38. Handler for event 'event1' caused SQLSTATE '57014'
E. 10/03 14:12:38. Statement interrupted by user
E. 10/03 14:12:38. Handler for event 'event1' caused SQLSTATE '57014'
E. 10/03 14:12:38. Statement interrupted by user
E. 10/03 14:12:38. Handler for event 'event2' caused SQLSTATE '57014'
E. 10/03 14:12:38. Statement interrupted by user
I. 10/03 14:12:38. Starting checkpoint of "d" (d.db) at Sun Oct 03 2010 14:12
I. 10/03 14:12:38. Finished checkpoint of "d" (d.db) at Sun Oct 03 2010 14:12
I. 10/03 14:12:40. Database server stopped at Sun Oct 03 2010 14:12

CREATE EVENT event1
HANDLER BEGIN

...

EXCEPTION

WHEN OTHERS THEN

SELECT SQLCODE, SQLSTATE, ERRORMSG() 
        INTO @sqlcode, @sqlstate, @errormsg;

IF ( @sqlcode = 0    AND @sqlstate = '00000' AND TRIM ( @errormsg ) = '' )
      OR ( @sqlcode = -299 AND @sqlstate = '57014' AND TRIM ( @errormsg ) = 'Statement interrupted by user' ) THEN

-- Note: This is not an "exception" worth recording via CALL record_exception.

ELSE

CALL record_exception ( STRING ( 
            ' SQLCODE = ', @sqlcode,  
            ', SQLSTATE = ', @sqlstate,  
            ', ERRORMSG() = ', @errormsg ) );

END IF;

END; -- event1

product-suggestion debugging

asked 04 Oct '10, 08:59

Breck Carter
32.5k●541●726●1050
accept rate: 20%

flat view

One Answer:

active answers oldest answers newest answers popular answers

Breck, this is an interesting problem.

What happens when the server (or database) is shutdown is that all currently executing requests (for the server/database) are canceled, and as a result the exception handler in the event is ignored (as you presumed). The issue then arises because when an event terminates with an error the server reports the error to the console because there is no other place to report it to. (I figure you knew all that, but I stated it here in case anyone else reading this didn't).

I'm not sure that the correct action in all cases would be to not report the event error to the console. E.g. There is almost certainly some cases when the event is doing a really important task and the DBA would really want to know if it were to not finish, even in the case that the server was being shutdown.

So I think we need to think about the bigger issue of what should the server do when an event terminates with an error and come up with a way of allowing the developer to define what should be done. There are two situations:

DBA does not care to know if the event fails with an error and therefore do report the error to the console.
DBA does care to know if the event fails with an error. (This is the current presumed scenario).

I don't think either answer is the "right" answer in all cases.

We (SA engineering) will consider this issue further to determine if a solution can be implemented in a future release.

permanent link

answered 06 Oct '10, 00:40

Mark Culp
24.9k●10●141●297
accept rate: 41%

Note that this message does NOT appear in all cases. In my case, there is always at least one event running when the database is shutdown, sometimes 100 or more... this message never appears when there are 10 or fewer events running, only when there are a lot more running. I don't know the break point, only that "no messages for 10 or fewer", "two dozen messages or so when 100 events are running".

(06 Oct '10, 06:47) Breck Carter

If you are opening "what happens at shutdown" for discussion, I'm all for that... I would like a DatabaseShutdown event. That's quite the can of worms, doncha think? :)

(06 Oct '10, 06:50) Breck Carter

flat view

Your answer

toggle preview

community wiki:

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

*italic* or _italic_
**bold** or __bold__
link:[text](http://url.com/ "title")
image?![alt text](/path/img.jpg "title")
numbered list: 1. Foo 2. Bar
to add a line break simply add two spaces to where you would like the new line to be.
basic HTML tags are also supported

learn more about Markdown

Question tags:

product-suggestion ×113
debugging ×106

question asked: 04 Oct '10, 08:59

question was seen: 3,939 times

last updated: 06 Oct '10, 00:40

SAP SQL Anywhere

Do not display "Statement interrupted by user" event exceptions when database is shutting down

Follow this question

Related questions