Database Reference
In-Depth Information
At this point, you should wait a few minutes and then select the contents of the
mark_log
table to verify that new rows are being written on schedule. However, if this is the first
event that you've set up, you might find that the table remains empty no matter how
long you wait:
mysql>
SELECT * FROM mark_log;
Empty set (0.00 sec)
If that's the case, very likely the event scheduler isn't running (which is its default state
until you enable it). Check the scheduler status by examining the value of the
event_scheduler
system variable:
mysql>
SHOW VARIABLES LIKE 'event_scheduler';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | OFF |
+-----------------+-------+
To enable the scheduler interactively if it's not running, execute the following statement
(which requires the
SUPER
privilege):
SET
GLOBAL
event_scheduler
=
1
;
That statement enables the scheduler, but only until the server shuts down. To start the
scheduler each time the server starts, enable the system variable in your
my.cnf
option
file:
[mysqld]
event_scheduler=1
When the event scheduler is enabled, the
mark_insert
event eventually creates many
rows in the table. There are several ways that you can affect event execution to prevent
the table from growing forever:
• Drop the event:
DROP
EVENT
mark_insert
;
This is the simplest way to stop an event from occurring. But if you want it to resume
later, you must re-create it.
• Disable event execution:
ALTER
EVENT
mark_insert
DISABLE
;
That leaves the event in place but causes it not to run until you reactivate it:
ALTER
EVENT
mark_insert
ENABLE
;
• Let the event continue to run, but set up another event that “expires” old
mark_log
rows. This second event need not run so frequently (perhaps once a day). Its body
should remove rows older than a given threshold. The following definition creates
an event that deletes rows that are more than two days old:
