Delete rows on event after time

Question

I am trying to make an event that will delete my articles where article_type_id is 2, but only if count of articles where article_type_id is 2 is greater than 100 (i want to filter out my old articles so that there are always 100 articles of type 2 in database)

I tried to write some query, but whatever I try doesn't seem to work.

USE travemdb;

CREATE EVENT delete_old_rows
ON SCHEDULE EVERY 1 MINUTE
DO
  BEGIN
    DECLARE row_count INT;
    SELECT COUNT(*) INTO row_count FROM article WHERE article_type_id = 2;
    IF row_count >= 100 THEN
      DELETE FROM article WHERE article_type_id = 2 ORDER BY id LIMIT row_count - 100;
    END IF;
  END;

I want it to trigger every 1 month, this is just for testing, but it doesn't work.

The message I get is currently

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 5

And also some of my keywords are highlighted:

I am not used to writing vanilla SQL because I only use ORM on backend.

Answer 1

The documentation states:

Within stored programs, LIMIT parameters can be specified using integer-valued routine parameters or local variables.

So you can't use calculated LIMIT parameters. You need to precalculate it.

CREATE EVENT delete_old_rows
ON SCHEDULE EVERY 1 MINUTE
DO
  BEGIN
    DECLARE row_count INT;
    SELECT COUNT(*) - 100
      INTO row_count
      FROM article
      WHERE article_type_id = 2;
    IF row_count > 0 THEN
      DELETE FROM article
        WHERE article_type_id = 2
        ORDER BY id
        LIMIT row_count;
    END IF;
  END;

But the SELECT and IF is unnecessary. LIMIT accepts an offset (as long as it's in a SELECT), so you can just offset it by 100 and rejoin it back by id.

CREATE EVENT delete_old_rows
ON SCHEDULE EVERY 1 MINUTE
DO
  BEGIN
    DELETE FROM article
      WHERE article_type_id = 2
        AND id IN (
          SELECT a.id
          FROM article a
          ORDER BY a.id
          LIMIT 100, 1000000000
        );
  END;

Note that offsetting by 100 is slightly different, as it will ignore the first 100 rather than the last. If that's an issue then sort DESC rather than ASC.