# 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](/docs/release-notes/community-server/old-releases/11.5/what-is-mariadb-115.md), this defaulted to the maximum, BIGINT. From [MariaDB 11.5](/docs/release-notes/community-server/old-releases/11.5/what-is-mariadb-115.md), 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](/docs/server/reference/sql-statements/administrative-sql-statements/show/show-create-sequence.md)
* [Information Schema SEQUENCES Table](/docs/server/reference/system-tables/information-schema/information-schema-tables/information-schema-sequences-table.md)

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

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


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://mariadb.com/docs/server/reference/error-codes/mariadb-error-codes-4000-to-4099/e4084.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.