Error 4084: Sequence has run out

Error Code

SQLSTATE

Error

Description

4084

ER_SEQUENCE_RUN_OUT

Sequence '%-.64s.%-.64s' has run out

Possible Causes and Solutions

Sequence has Completed Normally

In a sequence, once the MAXVALUE has been reached, and the CYCLE argument has not been used, further attempts to set the next value will fail:

CREATE OR REPLACE SEQUENCE s START WITH 1 INCREMENT BY 1 MAXVALUE=2;

SELECT NEXTVAL(s);
+------------+
| NEXTVAL(s) |
+------------+
|          1 |
+------------+

SELECT NEXTVAL(s);
+------------+
| NEXTVAL(s) |
+------------+
|          2 |
+------------+

SELECT NEXTVAL(s);
ERROR 4084 (HY000): Sequence 'test.s' has run out

This is usually intended behaviour, but there are a number of ways to prevent this:

Choose a larger MAXVALUE

Prior to MariaDB 11.5, this defaulted to the maximum, BIGINT. From MariaDB 11.5, it can be BIGINT UNSIGNED

CREATE OR REPLACE SEQUENCE s AS BIGINT UNSIGNED START WITH 1 INCREMENT BY 1;

SELECT SEQUENCE_NAME,MAXIMUM_VALUE FROM INFORMATION_SCHEMA.SEQUENCES\G
*************************** 1. row ***************************
SEQUENCE_NAME: s
MAXIMUM_VALUE: 18446744073709551614

Cycling

If re-use sequence values is not a problem, you can cycle back to the start once the maximum value has been reached:

CREATE OR REPLACE SEQUENCE s START WITH 1 INCREMENT BY 1 MAXVALUE=2 CYCLE;

SELECT NEXTVAL(s);
+------------+
| NEXTVAL(s) |
+------------+
|          1 |
+------------+

SELECT NEXTVAL(s);
+------------+
| NEXTVAL(s) |
+------------+
|          2 |
+------------+

SELECT NEXTVAL(s);
+------------+
| NEXTVAL(s) |
+------------+
|          1 |
+------------+

Sequence has Completed Due to Cache

The underlying table is only updated with a new sequence value the first time the value is created, and then each time the cache expires. By default the cache is set to 1000. For example:

CREATE OR REPLACE SEQUENCE s START WITH 1 INCREMENT BY 1 MAXVALUE=10 CACHE=5;

SELECT NEXTVAL(s);
+------------+
| NEXTVAL(s) |
+------------+
|          1 |
+------------+

SELECT NEXTVAL(s);
+------------+
| NEXTVAL(s) |
+------------+
|          2 |
+------------+

FLUSH TABLES s;

SELECT NEXTVAL(s);
+------------+
| NEXTVAL(s) |
+------------+
|          6 |
+------------+

FLUSH TABLES s;

SELECT NEXTVAL(s);
ERROR 4084 (HY000): Sequence 'test.s' has run out

This behaviour can be prevented by setting the cache to zero, so that the value stored in the underlying table is written each time:

CREATE OR REPLACE SEQUENCE s START WITH 1 INCREMENT BY 1 MAXVALUE=10 CACHE=0;

SELECT NEXTVAL(s);
+------------+
| NEXTVAL(s) |
+------------+
|          1 |
+------------+

SELECT NEXTVAL(s);
+------------+
| NEXTVAL(s) |
+------------+
|          2 |
+------------+

FLUSH TABLES s;

SELECT NEXTVAL(s);
+------------+
| NEXTVAL(s) |
+------------+
|          3 |
+------------+

Good afternoon