Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.3.1, 10.3.2
-
None
-
Description
Folks,
Checking the SEQUENCE feature while working with a new schema on MariaDB 10.3.1 and 2, I found the following situation what I think is a bug. I created a simple SEQUENCE, selected some times its NEXTVAL and then, I issued the command flush tables. Continuing with the tests, I selected the SEQUENCE's NEXTVAL again and for my surprise, the numeric sequence got broken for some reason. Below, the sequence of the facts:
MariaDB [mydb]> create sequence s1;
|
Query OK, 0 rows affected (0.006 sec) |
|
|
MariaDB [mydb]> select s1.nextval;
|
+------------+
|
| s1.nextval |
|
+------------+
|
| 1 | |
+------------+
|
1 row in set (0.001 sec) |
|
|
MariaDB [mydb]> select s1.nextval;
|
+------------+
|
| s1.nextval |
|
+------------+
|
| 2 | |
+------------+
|
1 row in set (0.000 sec) |
|
|
MariaDB [mydb]> flush tables;
|
Query OK, 0 rows affected (0.000 sec) |
|
|
MariaDB [mydb]> select * from s1;
|
+-----------------------+---------------+---------------------+-------------+-----------+------------+--------------+-------------+
|
| next_not_cached_value | minimum_value | maximum_value | start_value | increment | cache_size | cycle_option | cycle_count |
|
+-----------------------+---------------+---------------------+-------------+-----------+------------+--------------+-------------+
|
| 1001 | 1 | 9223372036854775806 | 1 | 1 | 1000 | 0 | 0 | |
+-----------------------+---------------+---------------------+-------------+-----------+------------+--------------+-------------+
|
1 row in set (0.000 sec) |
|
|
MariaDB [mydb]> select s1.nextval;
|
+------------+
|
| s1.nextval |
|
+------------+
|
| 1001 | |
+------------+
|
1 row in set (0.000 sec) |
Analysizng the results, it's clear that, if it has cached all the values until 1000, as 1001 is the next_not_cached_value, it's not OK to receive 1001 after 2 in case I flush tables; if I'm using on my application the S1.NEXTVAL instead of an AUTO_INCREMENT column, I will have a gap of many positions on my PK.
Getting a new record into a table having the INSERT using another existing SEQUENCE, gave me the below after the FLUSH TABLES:
MariaDB [mydb]> SELECT * FROM TBL_CUSTOMER;
|
+-------------+---------------------+---------------------+
|
| CUST_NUMBER | CUST_NAME | CUST_BORN_DATE |
|
+-------------+---------------------+---------------------+
|
| 2 | KUDDI SACARANDEERMA | 1980-09-09 00:00:00 | |
| 3 | JANNE KESKITALANII | 1983-02-17 00:00:00 | |
| 4 | SUOMMO AKETELEENI | 1981-05-11 00:00:00 | |
| 1000001 | CIKUA ROCONNEQ | 1967-03-27 00:00:00 | |
+-------------+---------------------+---------------------+
|
4 rows in set (0.000 sec) |
Thanks for checking, cheers!