I am having a some what similar problem to this that doesn’t seem to be solved by any of the obvious fixes like clearing cache, reindexing, truncating, repair db through phpmyadmin, repair db through magento tool.
Magento Version : 1.6.1 CE
Dedicated server, 2 x 2.1GHz AMD Opteron 12 Core Processor, 32GB ram, Ubuntu server (14 i think)
Error message is :
SQLSTATE: Integrity constraint violation: 1062 Duplicate entry ‘XXXXX-1’ for key ‘UNQ_CATALOGINVENTORY_STOCK_ITEM_PRODUCT_ID_STOCK_ID’
The problem arises when creating a new product. You hit save and you are presented with this error. XXXXX is equal to the current product id that you are creating (foreign key to the catalog_product_entity table) and 1 is an entry in the cataloginventry_stock table.
The product is correctly created and everything seems in tact from a user POV once this error is removed - the product looks to be correct throughout the db including the table where the constraint error is thrown. Logic leads me to believe this means that the product models create function must be running the insert script for that portion of the product twice (at least? maybe more?).
The server was in test mode and not seeing any issues. After it moved to production there will still no issues for a while (3 months approx). This problem all of a sudden manifested (several users on the system no one can tell that they did anything to cause this error and I can not find any logs that imply anything serious happened). Since product there have been no updates to the product model, and since this is vanilla magento installation for the product side of things (nothing like simple configurable products which i have read can be an issue) this is leading me to think that some data (perhaps cause by a transaction failing and not rolling back?) is stuck in a table, this is in turn causing the code to call two (or more?) inserts on the CATALOGINVENTORY_STOCK_ITEM table.
My concern is that bad data is rapidly building up as new products are failing this process and this is subsequently effecting performance in other places (reindexing now takes what is officially considered an age).
Perhaps someone has an idea of something else I can try with this issue? Or can point me to the data that the product create methods are looking at to generate the insert statement for that table?
Any idea great appreciated.