I'm trying to reset my sequences using the ALTER SEQUENCE that says it's supported in MariaDB 10.3+ since they introduced sequence support.
But I'm getting an error trying to restart my sequences using the MAX(id) + 1 value of the table the sequence is for.
According to the help for MariaDB it looks like it should be supported, but I'm getting a syntax error.
ALTER SEQUENCE Help - https://mariadb.com/kb/en/alter-sequence/
Any help is appreciated..
MariaDB [bcfactory]> select * from job_SEQ;
+-----------------------+---------------+---------------------+-------------+-----------+------------+--------------+-------------+
| next_not_cached_value | minimum_value | maximum_value | start_value | increment | cache_size | cycle_option | cycle_count |
+-----------------------+---------------+---------------------+-------------+-----------+------------+--------------+-------------+
| 1 | 1 | 9223372036854775806 | 1 | 50 | 1000 | 0 | 0 |
+-----------------------+---------------+---------------------+-------------+-----------+------------+--------------+-------------+
1 row in set (0.000 sec)
MariaDB [bcfactory]> alter sequence job_SEQ RESTART WITH (select MAX(id) + 1 from job);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(select MAX(id) + 1 from job)' at line 1
MariaDB [bcfactory]> select version();
+---------------------------------------+
| version() |
+---------------------------------------+
| 11.2.2-MariaDB-1:11.2.2+maria~ubu2204 |
+---------------------------------------+
1 row in set (0.000 sec)
I received an answer from MariaDB support and they said:
"Unfortunately, subqueries in that syntax are not supported, restart parameter is expected to be a number."
And suggested this as a workaround:
select MAX(id) + 1 into @a from job
EXECUTE IMMEDIATE CONCAT('ALTER SEQUENCE job_SEQ RESTART WITH ', (@a));