MySQL Forums
Forum List  »  Newbie

Issue with AUTO_INCREMENT and INSERT INTO SELECT
Posted by: Maximilian Gugler
Date: February 12, 2024 11:36AM

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 ?

Options: ReplyQuote


Subject
Written By
Posted
Issue with AUTO_INCREMENT and INSERT INTO SELECT
February 12, 2024 11:36AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.