We're excited to announce support for executing scheduled events on a Dolt sql-server! Scheduled Events is a MySQL feature that lets you define SQL statements that run on a set schedule. You can think of it as the SQL server equivalent of cron jobs on a Unix system – just tell the database the schedule on which you want your event to run and what SQL statements to execute. We previously announced Dolt's support for creating event definitions, and now we are excited to announce the other component of Scheduled Events – the event scheduler that executes those events.

Jennifer gets all the credit for developing this feature, but since she's been busy with an even more important project, I was happy to help wrap it up while she's out on maternity leave. (👶🏼🎉 Congratulations Jennifer!!! 🎉👶🏼)

This was a really fun feature to play with, and there are a TON of neat ways you can use this with Dolt. If you're new to Dolt, you can think of DoltDB as a combination of all the great distributed versioning features of Git, plus all the powerful relational database features from MySQL.

Here's a quick example of using some Dolt specific functionality in a scheduled event. This event will run once a week at 3am and execute the dolt_gc() stored procedure to garbage collect any extra disk space, pretty similar to PostgreSQL's autovacuum background process:

CREATE EVENT autoDoltGcEvent 
ON SCHEDULE EVERY 1 WEEK 
STARTS '2023-09-20 03:00:00'
DO call dolt_gc();

In this post, we'll show a Hello World example for scheduled events and talk about how they work with Dolt's version control features.

Hello Scheduled Events World!

Let's start up a Dolt sql-server so we can explore scheduled events. Note that Dolt scheduled events are only executed while you're running dolt sql-server. If you just start a local SQL shell with dolt sql, you'll be able to create new events, but they won't run until you start a dolt sql-server.

If you don't have Dolt installed on your system yet, head over to the the install instructions and download the dolt binary so you can follow along.

First we'll create a directory called EventsDemo for our new Dolt database. After we initialize the database, we start up a sql-server. I changed my port to 11223 since I have other processes listening on the default 3306 port, but you can use the default, or whatever port you want. I've also cranked up logging to DEBUG, but that's optional, too.

mkdir EventsDemo && cd EventsDemo
dolt init
dolt sql-server -uroot --loglevel DEBUG --port 11223

In a different terminal window, let's open up a SQL shell to our running sql-server, using the mysql client:

mysql -uroot --port 11223 --protocol TCP EventsDemo

Once we're in the SQL shell, let's go ahead and create a table for some test data and create a Dolt commit for it:

create table baby_names(name varchar(200) primary key);
call dolt_commit('-Am', 'Creating a test table on main');

Now let's create an event that will run once, 30s from now and insert some random characters (the first 8 chars of a UUID) into our new table:

CREATE EVENT myFirstEvent
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 30 SECOND
DO INSERT INTO baby_names (name) VALUES (LEFT(UUID(), 8));

If we look in our baby_names table, we'll see that there's nothing in there:

select * from baby_names;
Empty set (0.00 sec)

We can use the show events command to see the event we just created:

mysql> 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 |
+------------+--------------+------------+-----------+----------+---------------------+----------------+----------------+--------+------+---------+------------+----------------------+----------------------+--------------------+
| EventsDemo | myFirstEvent | `root`@`%` | SYSTEM    | ONE TIME | 2023-09-13 17:32:54 |           NULL | NULL           | NULL   | NULL | ENABLED |          0 | utf8mb4              | utf8mb4_0900_bin     | utf8mb4_0900_bin   |
+------------+--------------+------------+-----------+----------+---------------------+----------------+----------------+--------+------+---------+------------+----------------------+----------------------+--------------------+
1 row in set (0.01 sec)

Wait another 20 seconds or so, and our new event should have executed. If we run show events again, we'll see that the event has been executed and was removed from the events list. See the official MySQL docs for all the options and syntax for CREATE EVENT statements.

show events;
Empty set (0.00 sec)

And if we look at the data in our baby_names table, we see that one row was inserted by our event:

select * from baby_names;
+----------+
| name     |
+----------+
| ae99d428 |
+----------+
1 row in set (0.00 sec)

Okay, so it's not a great baby name (unless maybe you're Musk and Grimes I guess 🤷🏻), but at least our event is working correctly!

Let's create a new event, but this time, let's have the event execute every 30s instead of just once:

CREATE EVENT myRecurringEvent
ON SCHEDULE EVERY 30 SECOND
DO INSERT INTO baby_names (name) VALUES (LEFT(UUID(), 8));

If we give it a minute or two, we'll see more rows are being inserted:

select * from baby_names;
+----------+
| name     |
+----------+
| ae99d428 |
| fd3b7ed9 |
| fd3b7ed9 |
+----------+
3 rows in set (0.00 sec)

It's important to understand that your event definitions live on each branch of your database, just like your table data lives on your branches. If we change branches, show events will show us the events for THAT branch. Let's try that now...

call dolt_checkout('-b', 'dev');
+--------+--------------------------+
| status | message                  |
+--------+--------------------------+
|      0 | Switched to branch 'dev' |
+--------+--------------------------+
1 row in set (0.00 sec)

show events
Empty set (0.00 sec)

And if we look at our baby_names table, we see that it's empty, which makes sense, because our event is only running on our main branch and inserting into that table. Our dev branch is isolated from that and was created right after we Dolt committed the empty table, so we shouldn't see any of the updates on main (until we create a Dolt commit and merge changes from main over of course).

After a couple of minutes, let's jump back to the main branch and confirm that our events were still running while we had another branch checked out:

call dolt_checkout('main');
+--------+---------------------------+
| status | message                   |
+--------+---------------------------+
|      0 | Switched to branch 'main' |
+--------+---------------------------+
1 row in set (0.00 sec)

select * from baby_names;
+----------+
| name     |
+----------+
| ae99d428 |
| fd3b7ed9 |
| fd3b7ed9 |
| ffc44568 |
| ffe0248f |
| fffd1f6b |
+----------+
6 rows in set (0.00 sec)

So, even though our individual session had checked out the dev branch, our scheduled event was still happily running on the main branch.

To summarize, we created a recurring event on the main branch that inserts rows into our baby_names table every thirty seconds. After each insert, a SQL commit is performed, which means those changes to our table baby_names will be visible to other SQL sessions. We haven't performed a Dolt commit yet though! That means, the event definition as well as the baby_names table updates are all currently living in the working set. We can see this by taking a look at that dolt_status system table:

select * from dolt_status;
+--------------+--------+-----------+
| table_name   | staged | status    |
+--------------+--------+-----------+
| dolt_schemas |      0 | new table |
| baby_names   |      0 | modified  |
+--------------+--------+-----------+
2 rows in set (0.00 sec)

That status output show us that the data in our baby_names table has been modified and is not currently staged for commit, and it also reports a new table dolt_schemas that was created. dolt_schemas is a special Dolt table that shows schema fragments for view definitions, event definitions, and triggers. If we want our new event to be included in the Dolt commit graph and to be versioned along side all our table data, then we just need to commit that table.

call dolt_commit('-Am', 'creating a recurring event to update baby_names every 30s');

Note that the -A flag to Dolt commit will stage all new tables so that we don't have to explicitly run call dolt_add('dolt_schemas');

To reiterate a couple important details about how scheduled events run:

• You must be running a dolt sql-server for scheduled events to be executed. If no sql-server is running, and you only open a local SQL shell with dolt sql, then events can be created and committed, but the scheduler will not execute them.
• Only events on the main branch will be executed by the event scheduler. Events on other branches can still be created and committed, but they will not run until they are merged to the main branch and enabled. The intent of this is so that the same event doesn't run from multiple branches. This is something that we'll likely extend in the future so that customers can specify which branch(es) to run events from.
• In this first release of Scheduled Events, there is a 30s limit in place around how frequently events will execute. So, if you schedule an event to execute every second, you'll actually see it execute every 30s. We figure the vast majority of scheduled event use cases won't need to execute events faster than that, and we want to be cautious about the event scheduler not adding extra overhead. If you do need faster event execution, just reach out and let us know – it's a limit we can easily adjust for you.

Conclusion

We're excited that scheduled events will open up new ways for customers to use Dolt and we're happy to be yet another step closer to full MySQL compatibility! There are a lot of neat things you can do with scheduled events and we're looking forward to seeing what customers use them for. If you have specific feature requests or hit any issues with scheduled events, please send us a GitHub Issue so we can take a look.

If you have any questions about Dolt, whether they're related to scheduled events or not, please come by the DoltHub Discord and let us know! We'd be thrilled to answer any questions you have, no matter how big or small, and to demo product features like scheduled events, or anything else you're curious about.