Posting in the Magento forums has been disabled pending the implementation of a new and improved forum solution which should better serve the community.

For new questions please post at magento.stackexchange.com, the community-run support site for the Magento community. We will be providing updates on the new forum solution soon. For questions or concerns please email community@magento.com.

Magento Forum

MySQL error when saving products: Duplicate entry for key ‘UNQ_CATALOGINVENTORY_STOCK_ITEM_PRODUCT_ID_STOCK_ID’
 
A1Ben
Jr. Member
 
Total Posts:  5
Joined:  2012-11-23
 

Hi everyone,

I’m setting up a store using a clean install of Magento ver. 1.7.0.2 - I’ve not used used any import tools, but I have backed up & restored the entire db across dev & live server a few times.  However whenever I now save a product I am getting this MySQL Integrity constraint violation error in the admin console (screenshot attached):

SQLSTATE[23000]Integrity constraint violation1062 Duplicate entry '18-1' for key 'UNQ_CATALOGINVENTORY_STOCK_ITEM_PRODUCT_ID_STOCK_ID'

The product is saved correctly however.

I found that key (twice) in the cataloginventory_stock_item table:

mysqlSHOW INDEXES FROM cataloginventory_stock_item \G;

[...snip...]

*************************** 2. row ***************************
        
Tablecataloginventory_stock_item
   Non_unique
0
     Key_name
UNQ_CATALOGINVENTORY_STOCK_ITEM_PRODUCT_ID_STOCK_ID
 Seq_in_index
1
  Column_name
product_id
    Collation
A
  Cardinality
2
     Sub_part
NULL
       Packed
NULL
         Null

   
Index_typeBTREE
      Comment

Index_comment
*************************** 
3. row ***************************
        
Tablecataloginventory_stock_item
   Non_unique
0
     Key_name
UNQ_CATALOGINVENTORY_STOCK_ITEM_PRODUCT_ID_STOCK_ID
 Seq_in_index
2
  Column_name
stock_id
    Collation
A
  Cardinality
2
     Sub_part
NULL
       Packed
NULL
         Null

   
Index_typeBTREE
      Comment

Index_comment:

However looking at the table it seems the combined values of product_id & stock_id are unique:

mysqlselect item_idproduct_idstock_id from cataloginventory_stock_item;
+---------+------------+----------+
item_id product_id stock_id |
+---------+------------+----------+
|       
|          |        |
|      
10 |          |        |
|      
12 |          |        |
|      
14 |          |        |
|      
16 |          |        |
|      
18 |         10 |        |
|      
20 |         11 |        |
|      
22 |         12 |        |
|      
24 |         13 |        |
|      
27 |         14 |        |
|      
30 |         15 |        |
|      
33 |         16 |        |
|      
34 |         17 |        |
|      
37 |         18 |        |
+---------+------------+----------+

...there is only one value in cataloginventory_stock:

mysqlselect from cataloginventory_stock;
+----------+------------+
stock_id stock_name |
+----------+------------+
|        
| Default    |
+----------+------------+

Based on other similar posts l checked that all my tables are InnoDB and they are. 

Does anyone know what causes this/ how to fix it?  I will be deleting all my test products and starting again shortly anyway - is there a way I can do that which will stop these errors and something I can do to prevent these errors recurring? 

Many thanks

Ben

P.S.  Here’s the Create statement for the table

CREATE TABLE cataloginventory_stock_item
(
    
item_id int unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT,
    
product_id int unsigned DEFAULT 0 NOT NULL,
    
stock_id smallint unsigned DEFAULT 0 NOT NULL,
    
qty decimal(12,4) DEFAULT 0.0000 NOT NULL,
    
min_qty decimal(12,4) DEFAULT 0.0000 NOT NULL,
    
use_config_min_qty smallint unsigned DEFAULT 1 NOT NULL,
    
is_qty_decimal smallint unsigned DEFAULT 0 NOT NULL,
    
backorders smallint unsigned DEFAULT 0 NOT NULL,
    
use_config_backorders smallint unsigned DEFAULT 1 NOT NULL,
    
min_sale_qty decimal(12,4) DEFAULT 1.0000 NOT NULL,
    
use_config_min_sale_qty smallint unsigned DEFAULT 1 NOT NULL,
    
max_sale_qty decimal(12,4) DEFAULT 0.0000 NOT NULL,
    
use_config_max_sale_qty smallint unsigned DEFAULT 1 NOT NULL,
    
is_in_stock smallint unsigned DEFAULT 0 NOT NULL,
    
low_stock_date timestamp,
    
notify_stock_qty decimal(12,4),
    
use_config_notify_stock_qty smallint unsigned DEFAULT 1 NOT NULL,
    
manage_stock smallint unsigned DEFAULT 0 NOT NULL,
    
use_config_manage_stock smallint unsigned DEFAULT 1 NOT NULL,
    
stock_status_changed_auto smallint unsigned DEFAULT 0 NOT NULL,
    
use_config_qty_increments smallint unsigned DEFAULT 1 NOT NULL,
    
qty_increments decimal(12,4) DEFAULT 0.0000 NOT NULL,
    
use_config_enable_qty_inc smallint unsigned DEFAULT 1 NOT NULL,
    
enable_qty_increments smallint unsigned DEFAULT 0 NOT NULL,
    
is_decimal_divided smallint unsigned DEFAULT 0 NOT NULL,
    
FOREIGN KEY product_id REFERENCES catalog_product_entity entity_id ON DELETE CASCADE ON UPDATE CASCADE,
    
FOREIGN KEY stock_id REFERENCES cataloginventory_stock stock_id ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE UNIQUE INDEX UNQ_CATALOGINVENTORY_STOCK_ITEM_PRODUCT_ID_STOCK_ID ON cataloginventory_stock_item product_idstock_id );
CREATE INDEX IDX_CATALOGINVENTORY_STOCK_ITEM_PRODUCT_ID ON cataloginventory_stock_item product_id );
CREATE INDEX IDX_CATALOGINVENTORY_STOCK_ITEM_STOCK_ID ON cataloginventory_stock_item stock_id );

I’ve also attached a visualization of the relevant table structure.

Image Attachments
db-diagram.pngScreen Shot 2012-12-21 at 13.27.49.png
 
Magento Community Magento Community
Magento Community
Magento Community
 
A1Ben
Jr. Member
 
Total Posts:  5
Joined:  2012-11-23
 

Ok, so I just realised the reason for this is that I have created 2 custom modules with Observers that amend certain properties and update the product like this:

class A1web_CustomModule_Model_Observer
{
    
/**
     * Flag to stop observer executing more than once
     *
     * @var static bool
     */
    
static protected $_singletonFlag false;

    public function 
saveProductData(Varien_Event_Observer $observer)
    
{
        
if (!self::$_singletonFlag{
            self
::$_singletonFlag true;

            
$product $observer->getEvent()->getProduct();
             
//do stuff to the $product object
            
$product->save();  // commenting out this line prevents the error
    
}
}

So can I now please change my question completely smile and ask how do I update the product at the point it is saved from the admin interface without causing the violation errors?

Many thanks
Ben

 
Magento Community Magento Community
Magento Community
Magento Community
 
Tim MST
Jr. Member
 
Avatar
Total Posts:  9
Joined:  2011-10-26
 
A1Ben - 21 December 2012 04:51 AM

Ok, so I just realised the reason for this is that I have created 2 custom modules with Observers that amend certain properties and update the product like this:

class A1web_CustomModule_Model_Observer
{
    
/**
     * Flag to stop observer executing more than once
     *
     * @var static bool
     */
    
static protected $_singletonFlag false;

    public function 
saveProductData(Varien_Event_Observer $observer)
    
{
        
if (!self::$_singletonFlag{
            self
::$_singletonFlag true;

            
$product $observer->getEvent()->getProduct();
             
//do stuff to the $product object
            
$product->save();  // commenting out this line prevents the error
    
}
}

So can I now please change my question completely smile and ask how do I update the product at the point it is saved from the admin interface without causing the violation errors?

Many thanks
Ben

It’s work. Thank you Ben

 
Magento Community Magento Community
Magento Community
Magento Community
 
A1Ben
Jr. Member
 
Total Posts:  5
Joined:  2012-11-23
 

Just got the answer to my problem on stackoverflow:

This line

$product->save();
should be
$product->getResource()->save($product);
to avoid the MySQL integrity contraint violation when first saving the product.
 
Magento Community Magento Community
Magento Community
Magento Community
 
Anand_Girish
Jr. Member
 
Avatar
Total Posts:  4
Joined:  2012-03-30
Bangalore
 

The Perfect Solution for this error is
1) Open Database in Phpmyadmin
2) Now just write a query to select

SELECT * FROM `cataloginventory_stock_status` ORDER BY `product_id` DESC LIMIT 1

3) Above query will give you one record where you will get last product_id (just Note it down somewhere)

4) Now again write a query in sql to select

SELECT * FROM `cataloginventory_stock_item` ORDER BY item_id LIMIT 1

5) Note the product_id Now obviously this will give some higher product id

6) Just delete the rows that are greater then the product id we just have fetched

7) FIX is just delete the records by selecting it like below

SELECT * FROM cataloginventory_stock_item WHERE product_id > 7896 ;

Whatever records you get just delete them and you are done

Replace the 7896 id with the one that you have got from the first query from table cataloginventory_stock_item

Let me know in case of issue
This will work 100 %

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top