Mariadb large deletion with join and limit

Question

I'm trying to remove a lot of rows

My query is pretty simple but there is a JOIN and LIMIT is not allowed with that. I don't really understand why since it's not a LEFT JOIN.

I would like to wrap it in an event scheduler within a batch. I'm currently stuck with the LIMIT part.

# working
DELETE invitations from invitations JOIN events on events.id = invitations.event_id WHERE (...)

# not working
DELETE invitations from invitations JOIN events on events.id = invitations.event_id WHERE (...) LIMIT 10

I had something like that in mind

SET GLOBAL event_scheduler = ON;

CREATE EVENT invitation_cleaner_event
ON SCHEDULE EVERY 1 DAY
STARTS '2023-05-26 04:00:00'
DO
BEGIN
    REPEAT
        DO SLEEP(1);  
        # my query to delete with join and limit
    UNTIL ROW_COUNT() = 0 END REPEAT;
END

Answer 1

Here is the solution, deeply inspired by Michal's (thanks again). I still have a down time issue (duration depends on the number of records to remove) so it might be upgraded with knowledge I don't have. 🙂

SET GLOBAL event_scheduler = ON;

CREATE EVENT invitation_cleaner_event
ON SCHEDULE EVERY 1 DAY
STARTS '2023-05-27 02:00:00'
DO
BEGIN
    DECLARE rows_deleted INT;
    SET rows_deleted = 1;
    WHILE rows_deleted > 0 DO
      DELETE invitations
      FROM invitations
      JOIN (
        SELECT invitations.id
        FROM invitations
        JOIN events ON events.id=invitations.event_id WHERE ...
        LIMIT 1000
      ) AS subquery ON subquery.id = invitations.id;

      SET rows_deleted = ROW_COUNT();
      DO SLEEP(1);
    END WHILE;
END
show events;

EDIT

Very important to read this answer where I actually learnt how to enable scheduler permanently (requires a restart of the db)

# /etc/mysql/mysql.cnf
[mysqld]
event_scheduler = ON