[MDEV-17030] Reset Sequence doesn't reset the value the second time. Created: 2018-08-22  Updated: 2020-08-25  Resolved: 2018-08-22

Status: Closed
Project: MariaDB Server
Component/s: Sequences
Affects Version/s: 10.2.14
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: Pramod Mahto Assignee: Ian Gilfillan
Resolution: Won't Fix Votes: 0
Labels: None


 Description   

Reset Sequence doesn't reset the value the second time. 

 
 
Test case:-
 
 
 
MariaDB [test1]> set sql_mode=ORACLE;
 
Query OK, 0 rows affected (0.001 sec)
 
 
 
MariaDB [test1]> CREATE or replace SEQUENCE "entkaisgc_sg" start with 1 minvalue 1 maxvalue 9999999999999999 increment by 1 cache 20 cycle ;
 
Query OK, 0 rows affected (0.022 sec)
 
 
 
MariaDB [test1]> select entkaisgc_sg.nextval;
 
+----------------------+
 
| entkaisgc_sg.nextval |
 
+----------------------+
 
| 1 |
 
+----------------------+
 
1 row in set (0.012 sec)
 
 
 
MariaDB [test1]> select entkaisgc_sg.nextval;
 
+----------------------+
 
| entkaisgc_sg.nextval |
 
+----------------------+
 
| 2 |
 
+----------------------+
 
1 row in set (0.001 sec)
 
 
 
MariaDB [test1]> SELECT setval(entkaisgc_sg,9999999999999999);
 
+---------------------------------------+
 
| setval(entkaisgc_sg,9999999999999999) |
 
+---------------------------------------+
 
| 9999999999999999 |
 
+---------------------------------------+
 
1 row in set (0.001 sec)
 
 
 
MariaDB [test1]> select entkaisgc_sg.nextval;
 
+----------------------+
 
| entkaisgc_sg.nextval |
 
+----------------------+
 
| 1 |
 
+----------------------+
 
1 row in set (0.001 sec)
 
 
 
MariaDB [test1]> select entkaisgc_sg.nextval;
 
+----------------------+
 
| entkaisgc_sg.nextval |
 
+----------------------+
 
| 2 |
 
+----------------------+
 
1 row in set (0.000 sec)
 
 
 
MariaDB [test1]> SELECT setval(entkaisgc_sg,9999999999999999);
 
+---------------------------------------+
 
| setval(entkaisgc_sg,9999999999999999) |
 
+---------------------------------------+
 
| NULL |
 
+---------------------------------------+
 
1 row in set (0.000 sec)
 
 
 
MariaDB [test1]> select entkaisgc_sg.nextval;
 
+----------------------+
 
| entkaisgc_sg.nextval |
 
+----------------------+
 
| 3 |
 
+----------------------+
 
1 row in set (0.001 sec)
 
 



 Comments   
Comment by Alice Sherepa [ 2018-08-22 ]

from documentation https://mariadb.com/kb/en/library/setval/
"For SEQUENCE tables defined with CYCLE one should use both next_value and round to define the next value. In this case the current sequence value is defined to be round, next_value.

The result returned by SETVAL() is next_value or NULL if the given next_value and round is smaller than the current value.

SETVAL() will not set the SEQUENCE value to a something that is less than its current value. This is needed to ensure that SETVAL() is replication safe. If you want to set the SEQUENCE to a smaller number use ALTER SEQUENCE."

I guess it is not very clear documented about round option and no examples there, but to return the correct results, round option should be used:

MariaDB [test]> CREATE or replace SEQUENCE "s" start with 1 minvalue 1 maxvalue 99  increment by 1 cache 20 cycle;
 
Query OK, 0 rows affected (0.030 sec)
 
 
 
MariaDB [test]> select setval(s,99,true); 
+-------------------+
 
| setval(s,99,true) |
 
+-------------------+
 
|                99 |
 
+-------------------+
 
1 row in set (0.001 sec)
 
 
 
MariaDB [test]> select * from s; 
+-----------------------+---------------+---------------+-------------+-----------+------------+--------------+-------------+
 
| next_not_cached_value | minimum_value | maximum_value | start_value | increment | cache_size | cycle_option | cycle_count |
 
+-----------------------+---------------+---------------+-------------+-----------+------------+--------------+-------------+
 
|                   100 |             1 |            99 |           1 |         1 |         20 |            1 |           0 |
 
+-----------------------+---------------+---------------+-------------+-----------+------------+--------------+-------------+
 
1 row in set (0.000 sec)
 
 
 
MariaDB [test]> select s.nextval;
 
+-----------+
 
| s.nextval |
 
+-----------+
 
|         1 |
 
+-----------+
 
1 row in set (0.000 sec)
 
 
 
MariaDB [test]> select * from s; ##cycle_count=1
 
+-----------------------+---------------+---------------+-------------+-----------+------------+--------------+-------------+
 
| next_not_cached_value | minimum_value | maximum_value | start_value | increment | cache_size | cycle_option | cycle_count |
 
+-----------------------+---------------+---------------+-------------+-----------+------------+--------------+-------------+
 
|                    21 |             1 |            99 |           1 |         1 |         20 |            1 |           1 |
 
+-----------------------+---------------+---------------+-------------+-----------+------------+--------------+-------------+
 
1 row in set (0.000 sec)
 
 
 
MariaDB [test]> select setval(s,99,true,1); 
+---------------------+
 
| setval(s,99,true,1) |
 
+---------------------+
 
|                  99 |
 
+---------------------+
 
1 row in set (0.000 sec)
 
 
 
MariaDB [test]> select * from s;
 
+-----------------------+---------------+---------------+-------------+-----------+------------+--------------+-------------+
 
| next_not_cached_value | minimum_value | maximum_value | start_value | increment | cache_size | cycle_option | cycle_count |
 
+-----------------------+---------------+---------------+-------------+-----------+------------+--------------+-------------+
 
|                   100 |             1 |            99 |           1 |         1 |         20 |            1 |           1 |
 
+-----------------------+---------------+---------------+-------------+-----------+------------+--------------+-------------+
 
1 row in set (0.000 sec)
 
 
 
MariaDB [test]> select s.nextval;
 
+-----------+
 
| s.nextval |
 
+-----------+
 
|         1 |
 
+-----------+
 
1 row in set (0.000 sec)
 
 
 
MariaDB [test]> select * from s;
 
+-----------------------+---------------+---------------+-------------+-----------+------------+--------------+-------------+
 
| next_not_cached_value | minimum_value | maximum_value | start_value | increment | cache_size | cycle_option | cycle_count |
 
+-----------------------+---------------+---------------+-------------+-----------+------------+--------------+-------------+
 
|                    21 |             1 |            99 |           1 |         1 |         20 |            1 |           2 |
 
+-----------------------+---------------+---------------+-------------+-----------+------------+--------------+-------------+
 
1 row in set (0.000 sec)
 
 
 
MariaDB [test]> select setval(s,99,true,1); 
+---------------------+
 
| setval(s,99,true,1) |
 
+---------------------+
 
|                NULL |
 
+---------------------+
 
1 row in set (0.000 sec)
 
 
 
MariaDB [test]> select setval(s,99,true,2); 
+---------------------+
 
| setval(s,99,true,2) |
 
+---------------------+
 
|                  99 |
 
+---------------------+
 
1 row in set (0.000 sec)

Comment by Michael Widenius [ 2018-08-22 ]

This is as expected and documented:

"The result returned by SETVAL() is next_value or NULL if the given next_value and round is smaller than the current value."

The problem here is that the second setval() command in the example is same as:
SELECT setval(entkaisgc_sg,9999999999999999,1,0);
In other words, it will only update nextvalue if round is 0 (or lower)

As the sequence has already done one cycle, it's internal cycle_count (ie round) is 1 and this is what setval() fails.

Another option to change the sequence that 'always' work independent of the current sequence value is to use ALTER SEQUENCE.

Comment by Ian Gilfillan [ 2018-08-28 ]

The documentation needed expanding here, as there were no examples with round. I've added an example and clarification to make this clearer.

Generated at Thu Feb 08 08:33:23 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.