[MDEV-23430] AUTO_INCREMENT column gets unexpected value after INSERT -2 Created: 2020-08-07  Updated: 2020-08-08

Status: Open
Project: MariaDB Server
Component/s: Data types
Affects Version/s: 10.4.13, 10.5.4
Fix Version/s: None

Type: Bug Priority: Minor
Reporter: FLAESCH Sebastien Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Linux Debian 10 x86_64


Attachments: Text File config.txt     File serial.sql    

 Description   

After inserting negative -2 in an auto-incremented column, the new generated serial jumps from 1 to 3, when it should be 2, according to the documentation:

https://mariadb.com/kb/en/auto_increment/#setting-explicit-values

The doc shows examples with positive values, but there is a clear sentence:

If the new value is lower than the current maximum value, the AUTO_INCREMENT value remains unchanged.

Which should include negative explicit values ...



 Comments   
Comment by FLAESCH Sebastien [ 2020-08-07 ]

To reproduce with serial.sql :

sf@toro:/tmp$ mariadb test1 --host=toro --port=3309 --protocol=tcp -u mdbuser -p
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.5.4-MariaDB MariaDB Server
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
MariaDB [test1]> source serial.sql
Query OK, 0 rows affected (0.009 sec)
 
Query OK, 0 rows affected (0.014 sec)
 
Query OK, 1 row affected (0.002 sec)
 
Query OK, 1 row affected (0.002 sec)
 
Query OK, 1 row affected (0.002 sec)
 
Query OK, 1 row affected (0.002 sec)
 
Query OK, 1 row affected (0.002 sec)
 
Query OK, 1 row affected (0.002 sec)
 
Query OK, 1 row affected (0.002 sec)
 
Query OK, 1 row affected (0.002 sec)
 
Query OK, 1 row affected (0.002 sec)
 
Query OK, 1 row affected (0.002 sec)
 
+------+--------------+
| pkey | name         |
+------+--------------+
|   -1 | insert -1    |
|   -2 | insert -2    |
|   -3 | insert -3    |
|   -4 | insert -4    |
|   -5 | insert -5    |
|    1 | insert 0 (1) |
|    3 | insert 0 (2) |       <-- expecting 2 here
|    4 | insert 0 (3) |
|    5 | insert 0 (4) |
|    6 | insert 0 (5) |
+------+--------------+
10 rows in set (0.001 sec)

Comment by FLAESCH Sebastien [ 2020-08-08 ]

In fact this is not dramatic: Obviously, values generated by auto-incremented columns may not be an exact numeric sequence, especially in a multi-user environment.

I have reported this to alert you about the behavior change, to make sure there is not a more important defect, and to indicated that there seems to be exceptions to what the documentation describes.

I wonder about the fact that there is only a gap between 1 et 3 (2 is missing), but then for next serials, there is no missing number (4,5,6)... something weird to me.

This issue has been detected in one of our non-regression test programs (FOURJS Genero BDL), where only the test program inserts rows into the table.
We can adapt this test program, to check that the new generated numbers are always greater as the previous one (when using zero).
I just need a confirmation that the new current behavior is expected, before I change the test program...

Generated at Thu Feb 08 09:22:20 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.