on 2010 Oct 04 9:59 AM
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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".
User | Count |
---|---|
67 | |
10 | |
10 | |
10 | |
10 | |
8 | |
8 | |
6 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.