Circular tables in MariaDB

The round table, Great Hall, Winchester Castle
The most famous Round Table
(Great Hall, Winchester Castle)

Several articles have been written over the years about how to implement circular tables in MySQL. The idea is that sometimes we want new rows to overwrite the oldest ones. The general advice is to do it with partitions, and it works. But since MariaDB 10.3, we can use a cleaner method to do this in a precise way.

In this article, we will see how to create a circular table always consisting of an exact number of rows. To do so we will use sequences and the CHECK clause, implemented respectively in MariaDB 10.3 and 10.2. Currently MySQL doesn’t support sequences.

The table and the sequence

Suppose we want our table to have exactly five rows. We will start to count them from zero.

CREATE OR REPLACE SEQUENCE q0
    MINVALUE 0
    MAXVALUE 4
    START WITH 0
    CYCLE
    COMMENT 'Index for capped_table'
;
CREATE OR REPLACE TABLE capped_table
(
    id TINYINT UNSIGNED NOT NULL
        DEFAULT NEXT VALUE FOR q0,
    label VARCHAR(20) NOT NULL,
    PRIMARY KEY (id),
    CHECK (id < 5)
)
    ENGINE InnoDB,
    COMMENT 'This table should never have more than 5 rows'
;

The sequence just acts as a counter the next row that will be inserted. The key here is that it must be a circular sequence (CYCLE), which is not the default. After generating value 4, the sequence will restart and generate value 0. Non-circular sequences return an error instead.

The table (MongoDB users might easily guess where its name comes from) has an id column, whose default value is the next value of the sequence. But this does not prevent a user to manually insert a new row with an arbitrary id; so we use a CHECK constraint to prevent that an out of range value is inserted.

Let's test

No idea is a good idea until a test succeeds. So, let's try. Remember that this table is circular: we want old rows to be overwritten, so we must use REPLACE instead of INSERT.

REPLACE INTO capped_table (label) VALUE
      ('William Hartnell')
    , ('Patrick Troughton')
    , ('Jon Pertwee')
    , ('Tom Baker')
;
MariaDB [test]> SELECT * FROM capped_table;
+----+-------------------+
| id | label             |
+----+-------------------+
|  0 | William Hartnell  |
|  1 | Patrick Troughton |
|  2 | Jon Pertwee       |
|  3 | Tom Baker         |
+----+-------------------+

So far so good, but we didn't reach the table limit yet. Let's add some more rows, and let's see if old rows are overwritten. Let's use letters this time, so it will be easy to distinguish new values from the old ones.

REPLACE INTO capped_table (label) VALUE
      ('A')
    , ('B')
    , ('C')
;
MariaDB [test]> SELECT * FROM capped_table;
+----+-------------+
| id | label       |
+----+-------------+
|  0 | B           |
|  1 | C           |
|  2 | Jon Pertwee |
|  3 | Tom Baker   |
|  4 | A           |
+----+-------------+

Brilliant! The oldest two rows were overwritten by the second and third rows we just inserted.

Chronological order

The id doesn't represent a chronological order. Or better said, it does if we know which id is the newest one. But we don't, because SELECT PREVIOUS VALUE FOR q0 will return the last number generated by the current session - not the global one.

To workaround this and be able to read rows in a chronological order, we can add a TIMESTAMP(9) column.

Forbidding UPDATEs and DELETEs

If our circular table is meant to be a queue, the only possible write operation must be append. Thus, it is desirable to do forbid UPDATEs and DELETEs. We can do that by using triggers - this is my favourite solution because it works for all users, including root. Further details on this will be in a future article.

Toodle pip,
Federico

Photo Credit

Comments (5)

  1. Why use triggers for restricting the operations on the table? Wouldn’t the simplest (and best?) solution be GRANTs?

    1. Hi Karl. Because triggers will work for all users, including root. But of course you *can* use GRANT if you prefer.

      1. I personally would use a dedicated user account for something like this, and only use the root account to administer the database. Anyway, this was a super interesting article as usual, and I look forward to your next piece.

        1. Thank you!
          Just to be clear: I didn’t mean that root should do ordinary work on the db. I agree it must only administer. But still, I would prefer if no one is able to do certain operations on a table like this. Just my point of view, anyway: using permissions is also ok.

Leave a Reply

Your email address will not be published. Required fields are marked *