While trying to diagnose a possible bug in an extension, I've noticed that last week the values for item_id and product_id (also called entity_id) in the mage_cateloginventory_stock_item table got out of sync. The item_id jumped ahead by 1 as shown below...
item_id | product_id |
2433 | 2432 |
2432 | 2431 |
2430 | 2430 |
2429 | 2429 |
2428 | 2428 |
There is nothing special about this product or the way it was created to suggest why this happened. I'm worried that this may become a problem. If anyone can answer these questions I'd be very grateful.
Many thanks,
David
Solved! Go to Solution.
IDs are unique numbers for relations between database tables or in the case of EAV, between entities. The only time you are going to have an issue is when you end up with duplicate IDs which is prohibited by foreign key constraints.
In the instance shown, something attempted to do a database commit somewhere along the line, failed and so the increment on one of the IDs skipped a number. This doesn't break the relation so it's likely a non-issue. There may be an orphaned record out there for item_id 2431, but if it isn't referenced anywhere, it's just an entry in a table that will never be used, much line the rest of Magento where deleted attribute entries can remain cluttering tables in perpetuity due to lack of housecleaning removal but not otherwise hurting things because they're no longer referenced.
One of Magento's biggest failures has been the exposure of record IDs and the seeming giving of significance to them, something that an experienced database programmer does not do as IDs belong behind the scenes to tie data relationships together. Product numbers should never be IDs and should be just like SKUs, another field changeable independent of the record ID number. Varien's decision to make the product_id number matter has plagued us ever since, causing issues with import collisions where SKU were actual numbers, etc.
The Varien developers had a passing knowledge of Inventory Control, but implemented it as e-commerce web developers who knew it existed, not actual hands-on inventory control users.
IDs are unique numbers for relations between database tables or in the case of EAV, between entities. The only time you are going to have an issue is when you end up with duplicate IDs which is prohibited by foreign key constraints.
In the instance shown, something attempted to do a database commit somewhere along the line, failed and so the increment on one of the IDs skipped a number. This doesn't break the relation so it's likely a non-issue. There may be an orphaned record out there for item_id 2431, but if it isn't referenced anywhere, it's just an entry in a table that will never be used, much line the rest of Magento where deleted attribute entries can remain cluttering tables in perpetuity due to lack of housecleaning removal but not otherwise hurting things because they're no longer referenced.
One of Magento's biggest failures has been the exposure of record IDs and the seeming giving of significance to them, something that an experienced database programmer does not do as IDs belong behind the scenes to tie data relationships together. Product numbers should never be IDs and should be just like SKUs, another field changeable independent of the record ID number. Varien's decision to make the product_id number matter has plagued us ever since, causing issues with import collisions where SKU were actual numbers, etc.
The Varien developers had a passing knowledge of Inventory Control, but implemented it as e-commerce web developers who knew it existed, not actual hands-on inventory control users.
Thanks for the detailed answer, it's been of great help.