Issue with AUTO_INCREMENT and INSERT INTO SELECT
Hey,
i have an issue with an auto increment column in one of my tables.
The table looks like this:
CREATE TABLE `warehouse_snapshots` (
`id` int(11) PRIMARY KEY AUTO_INCREMENT,
`snapshot_timestamp` current_timestamp() NOT NULL,
`product` int(11) NOT NULL,
`amount` decimal(11,2) NOT NULL,
`purchase_price` decimal(11,2) NOT NULL,
`warehouse_id` tinyint(4) NOT NULL
)
And i also have a bit of PHP code that populates the table each day:
try {
$this->db->beginTransaction();
$queryWarehouse1= "
INSERT INTO warehouse_snapshots (snapshot_timestamp, product, pieces, purchase_price, warehouse_id)
SELECT CURRENT_TIMESTAMP(), w.products, w.pieces, p.productUnitPrice, 1
FROM warehouse1 w
JOIN products p ON w.product = p.productId
";
$statement1 = $this->db->prepare($queryWarehouse1);
$statement1->execute();
$queryWarehouse2 = "
INSERT INTO warehouse_snapshots (snapshot_timestamp, product, pieces, purchase_price, warehouse_id)
SELECT CURRENT_TIMESTAMP(), w.product, w.pieces, p.productUnitPrice, 2
FROM warehouse2 w
JOIN products p ON w.product = p.productId
";
$statement2 = $this->db->prepare($queryWarehouse2);
$statement2->execute();
$queryWarehouse3 = "
INSERT INTO warehouse_snapshots (snapshot_timestamp, product, pieces, purchase_price, warehouse_id)
SELECT CURRENT_TIMESTAMP(), w.product, w.pieces, p.productUnitPrice, 3
FROM warehouse3 w
JOIN products p ON w.product = p.productId
";
$statement3 = $this->db->prepare($queryWarehouse3);
$statement3->execute();
$this->db->commit();
}...
A bit of background information:
- The warehouse1 table has 146 rows in it
- The warehouse2 and warehouse3 tables are completely empty
- Engine is InnoDB
- innodb_autoinc_lock_mode = 1
When this code is executed, everything works as expected and 146 rows are inserted into the warehouse_snapshot table each time the code is executed. However the id column (AUTO_INCREMENT column) jumps to 256 instead of 147 after the transaction is done.
I am aware that in case the AUTO INCREMENT counter is incremented with each INSERT statement, regardless if it is successful or not. However, i do not get any errors while the transaction executes so i assume there are no errors.
I could reproduce this behavior both locally with the MySQL Workbench and MySQL and PHPmyadmin online.
Do you have any explaination for the gap in the AUTO_INCREMENT id column ?