MDEV-30645: Generalized triggers

[andremralves]

trafficstars

• [x] The Jira issue number for this PR is: MDEV-30645

• This project was developed during GSoC 2024
• Development discussion on Zulip chat
• My final submission blog post

Description

TL;DR

This PR implements some Generalized Triggers. It contains:

• CREATE TRIGGER as a synonym for CREATE EVENT.
• CREATE TRIGGER .. AFTER STARTUP DO ..
• CREATE TRIGGER .. BEFORE SHUTDOWN DO ..

Making CREATE TRIGGER a synonym for CREATE EVENT

CREATE TRIGGER myevent
    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
    DO
      UPDATE myschema.mytable SET mycol = mycol + 1;

Implementation:

I changed the rule trigger_tail to be either a trigger_tail or a event_tail.

I had to change some tests since the syntax now accepts the prefix create trigger [trigger_name] on.

Implementing CREATE TRIGGER .. AFTER STARTUP and CREATE TRIGGER .. BEFORE SHUTDOWN.

To run those triggers, I took advantage of the event scheduler infrastructure, and for storing them, I used the mysql.event table.

Startup trigger example:

CREATE TABLE test.t1 (inc INT);
CREATE TRIGGER startup_trg AFTER STARTUP DO UPDATE test.t1 SET inc = inc + 1;

Will run right after the server startup starts.

Shutdown trigger example:

CREATE TABLE test.t1 (inc INT);
CREATE TRIGGER shutdown_trg BEFORE SHUTDOWN DO UPDATE test.t1 SET inc = inc + 1;

Will run right before the server shutdown starts.

Last GSoC commit: Fixup: some rpl and embedded test errors and other minor issues

Release Notes

TODO: What should the release notes say about this change? Include any changed system variables, status variables or behaviour. Optionally list any https://mariadb.com/kb/ pages that need changing.

How can this PR be tested?

You can use the automated test:

./mtr main.create_trigger_on_schedule
./mtr main.create_drop_startup_trigger
./mtr main.create_drop_shutdown_trigger

or manually try to create an event using CREATE TRIGGER and then check if there is any corner case.

Basing the PR against the correct MariaDB version

• [x] This is a new feature and the PR is based against the latest MariaDB development branch.
• [ ] This is a bug fix and the PR is based against the earliest maintained branch in which the bug can be reproduced.

PR quality check

• [x] I checked the CODING_STANDARDS.md file and my PR conforms to this where appropriate.
• [x] For any trivial modifications to the PR, I am ok with the reviewer making the changes themselves.

[vuvova]

Looks good, but please add a test for a syntax error, like

CREATE OR REPLACE TRIGGER IF NOT EXISTS ev1 IN SCHEDULE EVERY 1 SECOND DO DROP DATABASE db1;

[andremralves]

@vuvova I added some more tests like the one you suggested.

[vuvova]

Note, this won't be pushed into 11.6 right now, it would just be seen as a strange and redundant alias of an existing feature.

We'll push everything together, then it'll be a logical part of the "generalized triggers"

[andremralves]

Looks good, thanks.

It's a bit inconsistent that error messages still say "event", like in

CREATE TRIGGER ev1 ON SCHEDULE EVERY 1 SECOND DO INSERT INTO t1 VALUES (11);
ERROR HY000: Event 'ev1' already exists

but let's not spend time on it now. You can get back to it after the main GSoC project is done.

That is something I was thinking about. After we use CREATE TRIGGER .. ON, should the outcome be an event or a trigger? The name we use is trigger, but all the characteristics are from an event. Also, when we use SHOW EVENTS, should the ones created with CREATE TRIGGER appear as well? With this implementation, CREATE TRIGGER .. ON will just be another way to create an event. Would it be better to differentiate them?

[vuvova]

I think it'll be the same object, so no need to differentiate. EVENT is very overloaded concept, e.g. very often people speak of "binlog events", which are completely different. What you're doing creates a notion of a "generalized trigger", like in some other databases. The syntax is CREATE TRIGGER <name> <condition> ... and the condition can be "AFTER INSERT" or "BEFORE SHUTDOWN" or whatever, may be "ON LOGIN" or "ON SLAVE CONNECT", etc. In this scheme "ON SCHEDULE" is just another condition for a trigger. And CREATE EVENT is a historical syntax to create a scheduled generalized trigger.

But it should be done in steps.

[andremralves]

Thanks for the explanation. I thought that since they will be the same object (event), maybe the error message saying that is an event is good, but, on the other hand, it could confuse the user.