# 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](https://github.com/mariadb-corporation/docs-server/blob/test/general-resources/community/sql-structure/sequences/README.md), once the MAXVALUE has been reached, and the [CYCLE](https://github.com/mariadb-corporation/docs-server/blob/test/general-resources/community/sql-structure/sequences/create-sequence.md#cycle-nocycle) 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](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/11.5/what-is-mariadb-115), this defaulted to the maximum, BIGINT. From [MariaDB 11.5](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/11.5/what-is-mariadb-115), 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](https://github.com/mariadb-corporation/docs-server/blob/test/general-resources/community/sql-structure/sequences/create-sequence.md#cache-nocache) 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 |
+------------+
```

## See Also

* [Sequence Overview](https://github.com/mariadb-corporation/docs-server/blob/test/general-resources/community/sql-structure/sequences/sequence-overview.md)
* [CREATE SEQUENCE](https://github.com/mariadb-corporation/docs-server/blob/test/general-resources/community/sql-structure/sequences/create-sequence.md)
* [ALTER SEQUENCE](https://github.com/mariadb-corporation/docs-server/blob/test/general-resources/community/sql-structure/sequences/alter-sequence.md)
* [DROP SEQUENCE](https://github.com/mariadb-corporation/docs-server/blob/test/general-resources/community/sql-structure/sequences/drop-sequence.md)
* [NEXT VALUE FOR](https://github.com/mariadb-corporation/docs-server/blob/test/general-resources/community/sql-structure/sequences/sequence-functions/next-value-for-sequence_name.md)
* [PREVIOUS VALUE FOR](https://github.com/mariadb-corporation/docs-server/blob/test/general-resources/community/sql-structure/sequences/sequence-functions/previous-value-for-sequence_name.md)
* [SETVAL()](https://github.com/mariadb-corporation/docs-server/blob/test/general-resources/community/sql-structure/sequences/sequence-functions/setval.md). Set next value for the sequence.
* [AUTO INCREMENT](https://github.com/mariadb-corporation/docs-server/blob/test/general-resources/community/data-types/auto_increment.md)
* [SHOW CREATE SEQUENCE](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-create-sequence)
* [Information Schema SEQUENCES Table](https://mariadb.com/docs/server/reference/system-tables/information-schema/information-schema-tables/information-schema-sequences-table)

<sub>*This page is licensed: CC BY-SA / Gnu FDL*</sub>

{% @marketo/form formId="4316" %}