Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-33666

MariaDB 11.x - ALTER SEQUENCE RESTART WITH not working?

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Not a Bug
    • 11.2.2
    • N/A
    • Sequences
    • None

    Description

      I'm trying to reset my sequences using the `ALTER SEQUENCE` that says it's supported.

      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)
      		 
      ```

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-20111 (CREATE|ALTER) SEQUENCE / SETVAL: only integer literal accepted as parameter

          • Major - Major loss of function.
          • Closed

          Activity

            alice Alice Sherepa added a comment -

            Unfortunately, subqueries in that syntax are not supported, restart parameter is expected to be a number.
            As a workaround, one could use:

            select MAX(id) + 1  into @a from job
            		 
            EXECUTE IMMEDIATE CONCAT('ALTER SEQUENCE job_SEQ RESTART WITH ', (@a));

            alice Alice Sherepa added a comment - Unfortunately, subqueries in that syntax are not supported, restart parameter is expected to be a number. As a workaround, one could use: select MAX (id) + 1 into @a from job EXECUTE IMMEDIATE CONCAT( 'ALTER SEQUENCE job_SEQ RESTART WITH ' , (@a));

            People

              Unassigned Unassigned
              tmulle Tim
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.