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

Count sequence nextval after FLUSH TABLES

Details

    Description

      Let's try to simulate behavior

      MariaDB [d]> CREATE SEQUENCE a1 START WITH 5000 INCREMENT BY 1 MINVALUE=5000 MAXVALUE=5003;
      		 
      Query OK, 0 rows affected (0.026 sec)
      		 
       
      		 
      MariaDB [d]> SELECT LASTVAL(a1), NEXTVAL(a1);
      		 
      +-------------+-------------+
      		 
      | LASTVAL(a1) | NEXTVAL(a1) |
      		 
      +-------------+-------------+
      		 
      |        NULL |        5000 |
      		 
      +-------------+-------------+
      		 
      1 row in set (0.001 sec)
      		 
       
      		 
      MariaDB [d]> SELECT LASTVAL(a1), NEXTVAL(a1);
      		 
      +-------------+-------------+
      		 
      | LASTVAL(a1) | NEXTVAL(a1) |
      		 
      +-------------+-------------+
      		 
      |        5000 |        5001 |
      		 
      +-------------+-------------+
      		 
      1 row in set (0.000 sec)
      		 
       
      		 
      MariaDB [d]> FLUSH TABLES;
      		 
      Query OK, 0 rows affected (0.000 sec)
      		 
       
      		 
      MariaDB [d]> SELECT LASTVAL(a1); # FLUSH TABLES shouldn't affect LASTVAL() - OK
      		 
      +-------------+
      		 
      | LASTVAL(a1) |
      		 
      +-------------+
      		 
      |        5001 |
      		 
      +-------------+
      		 
      1 row in set (0.001 sec)
      		 
       
      		 
      MariaDB [d]> SELECT NEXTVAL(a1);  # Problematic behavior
      		 
      ERROR 4084 (HY000): Sequence 'd.a1' has run out
      		 
      MariaDB [d]> SELECT LASTVAL(a1); # NEXTVAL() affects LASTVAL(), should it?
      		 
      +-------------+
      		 
      | LASTVAL(a1) |
      		 
      +-------------+
      		 
      |        NULL |
      		 
      +-------------+
      		 
      1 row in set (0.000 sec)

      The same as above happens if we start the new session or restart the server.

      So here 2 questions should be addressed:
      1. How to open/count table/value of closed sequence, after FLUSH TABLES is invoked? Documentation nextval sequence should be updated.
      Also, the error message ERROR 4084 (HY000): Sequence 'd.a1' has run out is the same for "empty" sequence (no cached value) as showed above as well as for NON-CYCLE sequence crossing MAXVALUE. Distinction between those should be done.
      2. LASTVAL in the current situation (crossing MAXVALUE) is correct? If yes it should be documented saying that crossing the value for NEXTVAL is setting the function to NULL. If not, let's change it.

      Attachments

        Activity

          Transition Time In Source Status Execution Times
          Ian Gilfillan made transition -
          Open Closed
          		1332d 1h 4m 1

          People

            greenman Ian Gilfillan
            anel Anel Husakovic
            Votes:
            0 Vote for this issue
            Watchers:
            5 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.