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:
None 
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
Description:
Issue with auto_increment feature if we get error while inserting values in a table

How to repeat:
mysql> select * from company;
+-----+------+--------------------+-------------------+
| CID | EID  | DepName            | Position          |
+-----+------+--------------------+-------------------+
|   1 |    1 | Software Testing   | Test Analyst      |
|   2 |    2 | Software Developer | Software Engineer |
|   3 |    4 | Accounts           | Acountant         |
|   4 |    5 | UX                 | Designer          |
|   6 |    3 | Customer Support   | support           |
+-----+------+--------------------+-------------------+
5 rows in set (0.00 sec)

mysql> insert into company (eid, depname, position) values (8, "Customer Support", "support");
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`prac_database`.`company`, CONSTRAINT `company_ibfk_1` FOREIGN KEY (`EID`) REFERENCES `employe` (`EID`))

mysql> insert into company (eid, depname, position) values (6, "Products", "service");
Query OK, 1 row affected (0.07 sec)

mysql> select * from company;
+-----+------+--------------------+-------------------+
| CID | EID  | DepName            | Position          |
+-----+------+--------------------+-------------------+
|   1 |    1 | Software Testing   | Test Analyst      |
|   2 |    2 | Software Developer | Software Engineer |
|   3 |    4 | Accounts           | Acountant         |
|   4 |    5 | UX                 | Designer          |
|   6 |    3 | Customer Support   | support           |
|   8 |    6 | Products           | service           |//issue
+-----+------+--------------------+-------------------+
6 rows in set (0.00 sec)

Actual Result: Above newly added record added at CID (auto increment value)8 instead of 7.

Suggested fix:
The increment value should be 7 instead of 8.
[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