Alice Sherepa added a comment - 2018-08-22 08:34 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)

Michael Widenius added a comment - 2018-08-22 12:08 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.

Ian Gilfillan added a comment - 2018-08-28 20:43 The documentation needed expanding here, as there were no examples with round. I've added an example and clarification to make this clearer.