CODEWITHSUNDEEP
mysql
Oren Chapo
Oren Chapo

Reputation: 527

Handle Abort/Kill/Connection Close from Stored Procedure

I'm writing a MySQL stored procedure that does maintenance on large tables, and runs every night. Unfortunately, due to reasons beyond my control, the running SP sometimes stops unexpectedly (due to "kill" by another admins or dropped connections).

Is there a way to "catch" those situations from within my SP and update a table (e.g. an activity log or maintenance audit table) when this happens?

I tried:

DECLARE EXIT HANDLER FOR SQLWARNING, SQLEXCEPTION

...and other specific ERRORs and SQLSTATEs:

DECLARE EXIT HANDLER FOR 1078, 1080, 1152, 1159, 1161, 1184, 1317, 3169, SQLSTATE '08S01'

but non of them seem to catch when aborting or killing.

Upvotes: 1

Views: 260

Answers (1)

Majid Hajibaba
Majid Hajibaba

Reputation: 3270

DECLARE EXIT HANDLER is for times your query terminates normally, something like clicking on shutdown button in windows. But kill process by another admin is something like reset PC by hand or power outage, So there is no time to do anything.

A good way to find these situations is investigating MySQL logs. If you cannot access to logs, try to do that by yourself. For example create a log table (include id, sp_name, start_time,end_time) and insert a record every time SP starts. You can do it by inserting timestamp and SP name at the beginning of SP. At the end of SP, you can update this record by inserting a timestamp for end_time. Every record with a start_time and without end_time means the contained SP name is killed by others after start_time time.

Upvotes: 1

Related Questions