Databases Reference
In-Depth Information
The “dummy” continue handler ensures that the event will release the lock, even if the
stored procedure throws an exception.
Although events are dissociated from connections, they are still associated with threads.
There's a main event scheduler thread, which you must enable in your server's config-
uration file or with a
SET
command:
mysql>
SET GLOBAL event_scheduler := 1;
When enabled, this thread executes events on the schedule specified in the event. You
can watch the server's error log for information about event execution.
Although the event scheduler is single-threaded, events can run concurrently. The
server will create a new process each time an event executes. Within the event's code,
a call to
CONNECTION_ID()
will return a unique value, as usual—even though there is no
“connection” per se. (The return value of
CONNECTION_ID()
is really just the thread ID.)
The process and thread will live only for the duration of the event's execution. You can
see it in
SHOW PROCESSLIST
by looking at the
Command
column, which will appear as
“Connect”.
Although the process necessarily creates a thread to actually execute, the thread is
destroyed at the end of event execution, not placed into the thread cache, and the
Threads_created
status counter is not incremented.
Preserving Comments in Stored Code
Stored procedures, stored functions, triggers, and events can all have significant
amounts of code, and it's useful to add comments. But the comments might not be
stored inside the server, because the command-line client can strip them out. (This
“feature” of the command-line client can be a nuisance, but
c'est la vie
.)
A useful trick for preserving comments in your stored code is to use version-specific
comments, which the server sees as potentially executable code (i.e., code to be exe-
cuted only if the server's version number is that high or higher). The server and client
programs know these aren't ordinary comments, so they won't discard them. To pre-
vent the “code” from being executed, you can just use a very high version number, such
as 99999. Let's add some documentation to our trigger example to demystify what it
does:
CREATE TRIGGER fake_statement_trigger
BEFORE INSERT ON sometable
FOR EACH ROW
BEGIN
DECLARE v_row_count INT DEFAULT ROW_COUNT();
/*!99999 ROW_COUNT() is 1 except for the first row, so this executes
only once per statement. */
IF v_row_count <> 1 THEN
-- Your code here
END IF;
END;
