Bug #86996 | MySQl 5.5- Issue in auto_increment keyword | ||
---|---|---|---|
Submitted: | 10 Jul 2017 11:27 | Modified: | 11 Jul 2017 5:21 |
Reporter: | Amit Kakkar | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server: mysqldump Command-line Client | Severity: | S3 (Non-critical) |
Version: | MySQL 5.5 | OS: | Windows |
Assigned to: | CPU Architecture: | Any |
[10 Jul 2017 11:27]
Amit Kakkar
[10 Jul 2017 11:30]
Amit Kakkar
Below is a first table: EID is auto increment Primary key select * from employe; +-----+-------+------------+------+---------+ | EID | EName | EAddress | EAge | ESalary | +-----+-------+------------+------+---------+ | 1 | Amit | Chandigarh | 29 | 35000 | | 2 | Bunny | Mohali | 32 | 50000 | | 3 | Sahil | Panchkula | 26 | 20000 | | 4 | Arun | jammu | 21 | 26000 | | 5 | Varun | Doraha | 27 | 46000 | | 6 | Alok | Punjab | 21 | 36090 | +-----+-------+------------+------+---------+ In second table company: EID is foreign key, CID auto-increment primary key
[11 Jul 2017 5:21]
MySQL Verification Team
Hello Amit Kakkar, Thank you for the report. Imho, this is not a bug but expected behavior, quoting from manual - https://dev.mysql.com/doc/refman/5.5/en/innodb-auto-increment-handling.html "In all lock modes (0, 1, and 2), if a transaction that generated auto-increment values rolls back, those auto-increment values are “lost”. Once a value is generated for an auto-increment column, it cannot be rolled back, whether or not the “INSERT-like” statement is completed, and whether or not the containing transaction is rolled back. Such lost values are not reused. Thus, there may be gaps in the values stored in an AUTO_INCREMENT column of a table." Thanks, Umesh