How Scheduled Events Deal with sql

When we create a scheduled event in MySQL, the current sql_mode is stored with the event. That causes the event to use the sql_mode that was in effect at the time the event was created. Same with altering an event.

Therefore, if we change the sql_mode after the event was created or altered, it won’t have any impact on the event. In other words, we can safely change our system’s sql_mode without worrying about whether it’s going to mess up any existing scheduled events.

Example

Suppose we set our sql_mode as follows:

SET SESSION sql_mode = 'STRICT_TRANS_TABLES,ALLOW_INVALID_DATES';
SELECT @@SESSION.sql_mode;

Result:

+-----------------------------------------+
| @@SESSION.sql_mode                      |
+-----------------------------------------+
| STRICT_TRANS_TABLES,ALLOW_INVALID_DATES |
+-----------------------------------------+

Here I included ALLOW_INVALID_DATES in my session’s sql_mode. This means that MySQL will not perform full checking of dates. It will check only that the month is in the range from 1 to 12 and the day is in the range from 1 to 31.

Now let’s create an event:

CREATE EVENT Event1
    ON SCHEDULE
        EVERY 1 HOUR
    DO
        INSERT INTO EventTest ( EventTimestamp, InsertedBy ) VALUES ('2024-02-31 09:05:00', 'Event1');

This event inserts an invalid date into a table. It’s invalid because it’s the 31st of February – February doesn’t have 31 days.

Inserting an invalid date may or may not be possible, depending on our sql_mode. In our case we’ve already added ALLOW_INVALID_DATES to our sql_mode, and so this should be possible.

We can check the table to see if the invalid date was inserted:

SELECT * FROM EventTest;

Result:

+---------------------+------------+
| EventTimestamp      | InsertedBy |
+---------------------+------------+
| 2024-02-31 09:05:00 | Event1     |
+---------------------+------------+

It was inserted as specified.

As mentioned, when we create an event, the current sql_mode is stored with the event. We can check the information schema to see this:

SELECT sql_mode 
FROM information_schema.events
WHERE event_schema = 'test'
AND event_name = 'Event1';

Result:

+-----------------------------------------+
| SQL_MODE                                |
+-----------------------------------------+
| STRICT_TRANS_TABLES,ALLOW_INVALID_DATES |
+-----------------------------------------+

This shows us that the sql_mode for this event is exactly as we specified earlier.

To demonstrate that the sql_mode will remain with the event, even if we change our session’s sql_mode, let’s change the sql_mode of the session:

SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

SELECT @@sql_mode;

Result:

+-----------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+

Now let’s check the event again:

SELECT sql_mode 
FROM information_schema.events
WHERE event_schema = 'test'
AND event_name = 'Event1';

Result:

+-----------------------------------------+
| SQL_MODE                                |
+-----------------------------------------+
| STRICT_TRANS_TABLES,ALLOW_INVALID_DATES |
+-----------------------------------------+

As expected, the event still has the sql_mode that it was created with. It hasn’t changed just because we changed our session’s sql_mode.

This allows us to make changes to the sql_mode without worrying about it having an adverse impact on existing events.

Altering the Event

Altering an event will cause the sql_mode to be updated to the current one.

Let’s alter the event:

ALTER EVENT Event1
    ON SCHEDULE
        EVERY 1 HOUR
    DO
        INSERT INTO EventTest ( EventTimestamp, InsertedBy ) VALUES ('2024-02-28 09:05:00', 'Event1');

Now let’s check the information schema again:

SELECT sql_mode 
FROM information_schema.events
WHERE event_schema = 'test'
AND event_name = 'Event1';

Result:

+-----------------------------------------------------------------------------------------------------------------------+
| SQL_MODE                                                                                                              |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+

We can see that the sql_mode has changed to the one that was in effect when we altered the event.

Database.Guide

How Scheduled Events Deal with sql_mode in MySQL

Example

Altering the Event