Introducing Dolt Events
Dolt is a version-controlled relational database that supports MySQL features. Today, we are excited to announce that we are adding support for MySQL Events. These are tasks that run on a specific schedule, such as executing a query statement at a particular time, either once or on a recurring basis. We are implementing this feature with version-control, which allows users human review and view the history of event changes. Moreover, events can be used to automate some version control workflows like committing, running garbage collection, or backing up your database on a schedule. These operations can also be specified to run on one or more branches.
We have divided this project into multiple steps. The first part is support for parsing all event statement syntax. MySQL standard statements, such as CREATE EVENT, ALTER EVENT, and DROP EVENT, are now fully supported in Dolt.
$ dolt sql
# Welcome to the DoltSQL shell.
# Statements must be terminated with ';'.
# "exit" or "quit" (or Ctrl-D) to exit.
dolt> CREATE EVENT daily_commit ON SCHEDULE EVERY 1 DAY STARTS '2023-05-30 20:00:00' DO CALL DOLT_COMMIT('--allow-empty', '-m', 'committing current working set for the day');
dolt> SHOW EVENTS;
+----------+--------------+--------------------+-----------+-----------+------------+----------------+----------------+---------------------+---------------------+---------+------------+----------------------+----------------------+--------------------+
| Db | Name | Definer | Time zone | Type | Execute At | Interval Value | Interval Field | Starts | Ends | Status | Originator | character_set_client | collation_connection | Database Collation |
+----------+--------------+--------------------+-----------+-----------+------------+----------------+----------------+---------------------+---------------------+---------+------------+----------------------+----------------------+--------------------+
| thursday | daily_commit | `root`@`localhost` | SYSTEM | RECURRING | NULL | 1 | DAY | 2023-05-30 20:00:00 | 2023-06-30 20:00:00 | ENABLED | 0 | utf8mb4 | utf8mb4_0900_bin | utf8mb4_0900_bin |
+----------+--------------+--------------------+-----------+-----------+------------+----------------+----------------+---------------------+---------------------+---------+------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)
dolt> ALTER EVENT daily_commit ON SCHEDULE EVERY 1 DAY STARTS '2023-05-30 20:00:00' ENDS '2023-06-30 20:00:00';
dolt> SHOW EVENTS;
+----------+--------------+--------------------+-----------+-----------+------------+----------------+----------------+---------------------+---------------------+---------+------------+----------------------+----------------------+--------------------+
| Db | Name | Definer | Time zone | Type | Execute At | Interval Value | Interval Field | Starts | Ends | Status | Originator | character_set_client | collation_connection | Database Collation |
+----------+--------------+--------------------+-----------+-----------+------------+----------------+----------------+---------------------+---------------------+---------+------------+----------------------+----------------------+--------------------+
| thursday | daily_commit | `root`@`localhost` | SYSTEM | RECURRING | NULL | 1 | DAY | 2023-05-30 20:00:00 | 2023-06-30 20:00:00 | ENABLED | 0 | utf8mb4 | utf8mb4_0900_bin | utf8mb4_0900_bin |
+----------+--------------+--------------------+-----------+-----------+------------+----------------+----------------+---------------------+---------------------+---------+------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)
dolt> DROP EVENT daily_commit;
dolt> SHOW EVENTS;
Empty set (0.00 sec)
The next part is to implement event functionality, which is further divided into two steps: (1) support event metadata storage and (2) event execution. As of the latest Dolt version, v1.2.1, event storage is supported, which means that you can try out our versioned events feature to see diffs on events between Dolt commits or branches. We are currently working on the second step.
Let's see how events are versioned
With the current version of Dolt, we can experiment with events to see how versioned events work. Here is a simple example of creating an event that inserts data into a table every day (Notice, the use of vertical output format).
> describe mytable;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| c1 | timestamp(6) | YES | | NULL | |
+-------+--------------+------+-----+---------+----------------+
> CREATE EVENT myevent ON SCHEDULE EVERY 1 DAY DO INSERT INTO mytable (c1) VALUES (NOW());
> SHOW EVENTS\G
*************************** 1. row ***************************
Db: thursday
Name: myevent
Definer: `root`@`localhost`
Time zone: SYSTEM
Type: RECURRING
Execute At: NULL
Interval Value: 1
Interval Field: DAY
Starts: 2023-05-30 08:20:39
Ends: NULL
Status: ENABLED
Originator: 0
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_bin
Database Collation: utf8mb4_0900_bin
1 row in set (0.00 sec)
We store events in the dolt_schemas system table using the CREATE EVENT definition, similar to how VIEWS and TRIGGERS. The dolt_schemas table is utilized for storing and retrieving diffs when there are modifications to EVENTS, VIEWS, and TRIGGERS between commits or branches. You can find more information about the dolt_schemas table in the Dolt system tables documentation page.
> SELECT * FROM dolt_schemas\G
*************************** 1. row ***************************
type: event
name: myevent
fragment: CREATE DEFINER = `root`@`localhost` EVENT `myevent` ON SCHEDULE EVERY 1 DAY STARTS '2023-05-30 08:20:39' ON COMPLETION NOT PRESERVE ENABLE DO INSERT INTO mytable (c1) VALUES (NOW())
extra: {"CreatedAt": 1685460039}
1 row in set (0.00 sec)After modifying an existing event, the event entry in the dolt_schemas system table will be updated, and it can be used to display a diff for this event using the dolt diff command.
> ALTER EVENT myevent RENAME TO mytable_event DISABLE;
> SELECT * FROM dolt_schemas\G
*************************** 1. row ***************************
type: event
name: mytable_event
fragment: CREATE DEFINER = `root`@`localhost` EVENT `mytable_event` ON SCHEDULE EVERY 1 DAY STARTS '2023-05-30 08:20:39' ON COMPLETION NOT PRESERVE DISABLE DO INSERT INTO mytable (c1) VALUES (NOW())
extra: {"CreatedAt": 1685460039}
1 row in set (0.00 sec)$ dolt diff
-CREATE DEFINER = `root`@`localhost` EVENT `myevent` ON SCHEDULE EVERY 1 DAY STARTS '2023-05-30 08:20:39' ON COMPLETION NOT PRESERVE ENABLE DO INSERT INTO mytable (c1) VALUES (NOW());
+CREATE DEFINER = `root`@`localhost` EVENT `mytable_event` ON SCHEDULE EVERY 1 DAY STARTS '2023-05-30 08:20:39' ON COMPLETION NOT PRESERVE DISABLE DO INSERT INTO mytable (c1) VALUES (NOW());Progress on the implementing execution of the events step
The execution of events in Dolt works in the same way as MySQL, where the event definition is run at the specified execution time. However, Dolt also introduces additional behavior introducing version-controlled events. The initial version of this project includes executing events on a specific branch where the event is intended to be executed. Users can also specify running events on multiple branches. For example, users can ENABLE the event on the branch where they want it to be executed. This approach helps prevent events from running without the user's awareness.
One of the challenges we are currently addressing is evaluating and storing the event time values of the schedule, which include timestamp values for the AT, EVERY ... STARTS, and EVERY ... ENDS components. Currently, in Dolt, we allow and parse various formats for time inputs, including some that are invalid in MySQL. MySQL not only accepts MySQL-valid datetime and timestamp formats but also parses what it can and truncates the rest that doesn't match the accepted MySQL formats. This collides with our time input parsing where Dolt allows formats that MySQL truncates some part or fails on.
Furthermore, MySQL has specific behavior regarding handling timezone definitions in these time inputs. All time inputs are evaluated and stored using the @@session.time_zone, which can be set to any timezone during server runtime. However, the event scheduler checks the event's execution time in @@system_time_zone, which cannot be changed. Consequently, if the session timezone differs from the system timezone, the event may expire and be dropped, be scheduled in the past, or be set to run in the future.
Cool use cases of Dolt Events
After the execution of the events part is supported, events enhance Dolt version control functionality beyond what is achievable in Git. Users can:
- Commit their working directory every night as a precaution against any technical issues.
- Create a scheduled task for database backup at a specific time.
- Schedule a task to run garbage collection on a weekly basis.
- Create a nightly release using dolt tag
- Create a branch running data quality queries on it
- Attempt to merging a main branch into feature branches nightly
Here is a simple example of Dolt command used with MySQL Events:
CREATE EVENT weekly_gc ON SCHEDULE EVERY 1 WEEK DO CALL DOLT_GC();Conclusion
The project is being implemented in multiple steps, with support for parsing event statements and events storage already available in the current Dolt version. The addition of MySQL Events support in Dolt opens up exciting possibilities for version-controlled relational databases. By incorporating versioned events, Dolt enables users to track the history of event changes and automate operations such as committing or backing up the database on a schedule.
Join our Discord channel. We would love to hear about your use case for Dolt Events and your desired functionality for future Dolt Events.