4084
ER_SEQUENCE_RUN_OUT
Sequence '%-.64s.%-.64s' has run out
In a , once the MAXVALUE has been reached, and the argument has not been used, further attempts to set the next value will fail:
This is usually intended behaviour, but there are a number of ways to prevent this:
Prior to , this defaulted to the maximum, BIGINT. From , it can be BIGINT UNSIGNED
If re-use sequence values is not a problem, you can cycle back to the start once the maximum value has been reached:
The underlying table is only updated with a new sequence value the first time the value is created, and then each time the expires. By default the cache is set to 1000. For example:
This behaviour can be prevented by setting the cache to zero, so that the value stored in the underlying table is written each time:
SETVAL(). Set next value for the sequence.
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 outCREATE 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: 18446744073709551614CREATE 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 |
+------------+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 outCREATE 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 |
+------------+This page is licensed: CC BY-SA / Gnu FDL