Why using INSERT ON DUPLICATE KEY the AUTO_INCREMENT goes itself ?

Developers Programming
#1

Hello all,

I got a query that insert new record is not present and update the existing ones if available.
I got a AUTO_INCREMENT field that is the number of row.

If I run more than one time the same query that field run always as it would be a new insert also if it’s not.
Another strange thing is that the number jumps 2 numbers (from 10 to 13 instead of 11)
Is there a trick to avoid that behavior ??

$sql_insert_rows="INSERT INTO orders_details (OrderID , CustomerID, SalesmanID , OrderDate , ProductID , UnitPrice , CurrencyID , Quantity)
SELECT OrderID , CustomerID, SalesmanID , OrderDate , ProductID , UnitPrice , CurrencyID , Quantity
FROM orders_details_temp 
ON DUPLICATE KEY UPDATE         
        orders_details.OrderID = orders_details_temp.OrderID ,
        orders_details.CustomerID = orders_details_temp.CustomerID,
        orders_details.SalesmanID = orders_details_temp.SalesmanID,
        orders_details.OrderDate = orders_details_temp.OrderDate,
        orders_details.ProductID = orders_details_temp.ProductID ,
        orders_details.UnitPrice = orders_details_temp.UnitPrice ,
        orders_details.CurrencyID = orders_details_temp.CurrencyID,
        orders_details.Quantity = orders_details_temp.Quantity ";

See image to understand better
Thanks

query.png

#2

Is it safe ;-(( set AUTO_INCREMENT to “” before to run the query and then set it again to AUTO_INCREMENT ??? It seems a problem of mysql…

ALTER TABLE orders_details CHANGE Order_row_ID Order_row_ID INT( 11 ) NOT NULL

then after the query

ALTER TABLE orders_details CHANGE Order_row_ID Order_row_ID INT( 11 ) NOT NULL AUTO_INCREMENT

Ok… I’ve just tried… and the only difference is that lines number start from 1 to 5.

Really misterious !!!

#3

Your ON DUPLICATE … part will never be executed because your primary key is Order_row_ID and you select
the data to be inserted from order_details_temp and there is no Order_row_ID in your statement/order_details_temp.
There is no colliding key and it will always be a regular insert.

The ‘jumping’ auto_increment is NOT a bug or problem of MYSQL. Check your temp table and make
sure there are no empty lines in it, do you run any other command (delete?) on the table, is it part of a transaction?

Also auto_increment guarantees unique numbers not necessarily sequential ones. What you can do is check and set the the auto_increment value
every time before you run the insert statement.

jsb

#4

Hello JSB !!! You got as always the reason.
At the end, I did this modification and now it seems to work properly… (not really sure …:wink: )

$sql_insert_rows="INSERT INTO orders_details (Order_row_ID, OrderID , CustomerID, SalesmanID , OrderDate , ProductID , UnitPrice , CurrencyID , Quantity)
SELECT (SELECT Order_row_ID FROM orders_details WHERE OrderID = t.OrderID AND 
CustomerID = t.CustomerID AND 
SalesmanID = t.SalesmanID AND 
OrderDate = t.OrderDate AND 
ProductID = t.ProductID AND 
UnitPrice = t.UnitPrice AND
CurrencyID = t.CurrencyID) AS Order_row_ID, 
t.OrderID , t.CustomerID, t.SalesmanID , t.OrderDate , t.ProductID , t.UnitPrice , t.CurrencyID , t.Quantity
FROM orders_details_temp t 
ON DUPLICATE KEY UPDATE
#Order_row_ID = t.Order_row_ID;
OrderID = t.OrderID ,
CustomerID = t.CustomerID,
SalesmanID = t.SalesmanID,
OrderDate = t.OrderDate,
ProductID = t.ProductID ,
UnitPrice = t.UnitPrice ,
CurrencyID = t.CurrencyID,
Quantity = t.Quantity ";