cancel
Showing results for 
Search instead for 
Did you mean: 

ITEM_ID and PRODUCT_ID not in sync - is this an issue?

SOLVED

ITEM_ID and PRODUCT_ID not in sync - is this an issue?

Hi,


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_idproduct_id
24332432
24322431
24302430
24292429
24282428

 


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.

  1. Is it a problem that they are different values? Should they be the same?
  2. What is the purpose of the two different ids (item_id and product_id)?
  3. Any suggestion about what caused them to lose sync?

Many thanks,

 

David

1 ACCEPTED SOLUTION

Accepted Solutions

Re: ITEM_ID and PRODUCT_ID not in sync - is this an issue?

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.

View solution in original post

2 REPLIES 2

Re: ITEM_ID and PRODUCT_ID not in sync - is this an issue?

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.

Re: ITEM_ID and PRODUCT_ID not in sync - is this an issue?

Thanks for the detailed answer, it's been of great help.