Mariadb: SEQUENCE err - a huge increase in next start value

I added new records to a table, which used a sequence to increment the primary key, and noticed that they were auto-incremented from 2000 rather than around 450. Oh no!

After finding the bug as described below and staring at the mariadb ref - https://mariadb.com/kb/en/create-sequence/, and phind hint - https://www.phind.com/search?cache=pu5i52862ibn08h10ric0hj9.

I used the sample code from https://jira.mariadb.org/browse/MDEV-23831 to test the sequence.

1) Resetting the sequence

DROP SEQUENCE IF EXISTS a1;

2) I added a cache size of zero which is to say, 'do not use the cache'

CREATE SEQUENCE IF NOT EXISTS a1 START WITH 5000 INCREMENT BY 1 MINVALUE=5000 MAXVALUE=5003 CACHE=1000;

3) Test nextval

SELECT * FROM a1; SELECT LASTVAL(a1), NEXTVAL(a1);

4) login as an admin, on the mariadb cli, to flush cache and try select

sudo mariadb -u db_sql_username -p

Repeat step 3 to see how the increment changes

5) Reset the database server cache

FLUSH TABLES;

Repeat step 3 to see how the increment changes

Repeat step 1 with:

CREATE SEQUENCE IF NOT EXISTS a1 START WITH 5000 INCREMENT BY 1 MINVALUE=5000 MAXVALUE=5003 CACHE=0;

Setting the cache to 0 seems to keep the correct nextval even after a table flush. It might slow the creation of sequence numbers.