Fix Catalog URL Rewrites Error in Magento 1.9

databasemagento-1.9reindexurl-rewrite

I have tried everything possiable. I upgraded my Magento from 1.7.X to 1.9.X then just upgraded to Magento 1.9.2. Over the last couple years. Going from 1.7 to 1.9 had no problems. All is well however I am getting an error when I try to reindex my Catalog URL Rewrites. Here is what I tried to do with NO success:

System Specs:
– Windows Server 2008
– Magento Version 1.9.2
– PHP 5.5

What I have Tried:
– I tried Trunicating my core_url_rewrite table
– I cleared out my magento Cache both in backend and on the server.
– I looked for the Var/Locks folder but my Magento didn't create the folder so I couldn't delete it. I thought maybe it would be a permissions issues so I gave the entire folder of Magento "Everyone" Full control permissions and tried reindexing again. Still errors.
– I logged in to my server using SSH and ran "php indexer.php -reindexall" and "php indexer.php -reindex catalog_url" it indexes everything but the core url. It does show a bunch of stuff on the screen which I don't understand when it does try the to reindex.

The Returned Error:

C:\mydomain\shell>php indexer.php -reindex catalog_url

Catalog URL Rewrites index process unknown error:
exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '35270000_1443190673-
0-1' for key 'UNQ_CORE_URL_REWRITE_ID_PATH_IS_SYSTEM_STORE_ID'' in C:\mydomain\lib\Zend\Db\St
atement\Pdo.php:228

Stack trace:

#0 C:\mydomain\lib\Zend\Db\Statement\Pdo.php(228): PDOStatement->execute(Array)
#1 C:\mydomain\lib\Varien\Db\Statement\Pdo\Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#2 C:\mydomain\app\code\core\Zend\Db\Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#3 C:\mydomain\lib\Zend\Db\Adapter\Abstract.php(480): Zend_Db_Statement->execute(Array)
#4 C:\mydomain\lib\Zend\Db\Adapter\Pdo\Abstract.php(238): Zend_Db_Adapter_Abstract->query('INSERT INTO `co...', Array)
#5 C:\mydomain\lib\Varien\Db\Adapter\Pdo\Mysql.php(504): Zend_Db_Adapter_Pdo_Abstract->query('INSERT INTO `co...', Array)
#6 C:\mydomain\lib\Zend\Db\Adapter\Abstract.php(576): Varien_Db_Adapter_Pdo_Mysql->query('INSERT INTO `co...', Array)
#7 C:\mydomain\app\code\core\Mage\Catalog\Model\Resource\Url.php(338): Zend_Db_Adapter_Abstract->insert('core_url_rewrit...', Array)
#8 C:\mydomain\app\code\core\Mage\Catalog\Model\Url.php(963): Mage_Catalog_Model_Resource_Url->saveRewriteHistory(Array)
#9 C:\mydomain\app\code\core\Mage\Catalog\Model\Url.php(369): Mage_Catalog_Model_Url->_saveRewriteHistory(Array, Object(Varien_Object))
#10 C:\mydomain\app\code\core\Mage\Catalog\Model\Url.php(568): Mage_Catalog_Model_Url->_refreshProductRewrite(Object(Varien_Object), Object(Varien_Object))
#11 C:\mydomain\app\code\core\Mage\Catalog\Model\Url.php(255): Mage_Catalog_Model_Url->refreshProductRewrites('1')
#12 C:\mydomain\app\code\core\Mage\Catalog\Model\Url.php(248): Mage_Catalog_Model_Url->refreshRewrites('1')
#13 C:\mydomain\app\code\core\Mage\Catalog\Model\Indexer\Url.php(257): Mage_Catalog_Model_Url->refreshRewrites()
#14 C:\mydomain\app\code\core\Mage\Index\Model\Process.php(212): Mage_Catalog_Model_Indexer_Url->reindexAll()
#15 C:\mydomain\app\code\core\Mage\Index\Model\Process.php(260): Mage_Index_Model_Process->reindexAll()
#16 C:\mydomain\shell\indexer.php(168): Mage_Index_Model_Process->reindexEverything()
#17 C:\mydomain\shell\indexer.php(216): Mage_Shell_Compiler->run()
#18 {main}



Next exception 'Zend_Db_Statement_Exception' with message 'SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '
35270000_1443190673-0-1' for key 'UNQ_CORE_URL_REWRITE_ID_PATH_IS_SYSTEM_STORE_ID', query was: INSERT INTO `core_url_rewrite` (`st
ore_id`, `category_id`, `product_id`, `id_path`, `request_path`, `target_path`, `is_system`, `options`) VALUES (?, ?, ?, ?, ?, ?,?, ?)' in C:\mydomain\lib\Zend\Db\Statement\Pdo.php:235 
Stack trace:
#0 C:\mydomain\lib\Varien\Db\Statement\Pdo\Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#1 C:\mydomain\app\code\core\Zend\Db\Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#2 C:\mydomain\lib\Zend\Db\Adapter\Abstract.php(480): Zend_Db_Statement->execute(Array)
#3 C:\mydomain\lib\Zend\Db\Adapter\Pdo\Abstract.php(238): Zend_Db_Adapter_Abstract->query('INSERT INTO `co...', Array)
#4 C:\mydomain\lib\Varien\Db\Adapter\Pdo\Mysql.php(504): Zend_Db_Adapter_Pdo_Abstract->query('INSERT INTO `co...', Array)
#5 C:\mydomain\lib\Zend\Db\Adapter\Abstract.php(576): Varien_Db_Adapter_Pdo_Mysql->query('INSERT INTO `co...', Array)
#6 C:\mydomain\app\code\core\Mage\Catalog\Model\Resource\Url.php(338): Zend_Db_Adapter_Abstract->insert('core_url_rewrit...', Array)
#7 C:\mydomain\app\code\core\Mage\Catalog\Model\Url.php(963): Mage_Catalog_Model_Resource_Url->saveRewriteHistory(Array)
#8 C:\mydomain\app\code\core\Mage\Catalog\Model\Url.php(369): Mage_Catalog_Model_Url->_saveRewriteHistory(Array, Object(Varien_Object))
#9 C:\mydomain\app\code\core\Mage\Catalog\Model\Url.php(568): Mage_Catalog_Model_Url->_refreshProductRewrite(Object(Varien_Object), Object(Varien_Object))
#10 C:\mydomain\app\code\core\Mage\Catalog\Model\Url.php(255): Mage_Catalog_Model_Url->refreshProductRewrites('1')
#11 C:\mydomain\app\code\core\Mage\Catalog\Model\Url.php(248): Mage_Catalog_Model_Url->refreshRewrites('1')
#12 C:\mydomain\app\code\core\Mage\Catalog\Model\Indexer\Url.php(257): Mage_Catalog_Model_Url->refreshRewrites()
#13 C:\mydomain\app\code\core\Mage\Index\Model\Process.php(212): Mage_Catalog_Model_Indexer_Url->reindexAll()
#14 C:\mydomain\app\code\core\Mage\Index\Model\Process.php(260): Mage_Index_Model_Process->reindexAll()
#15 C:\mydomain\shell\indexer.php(168): Mage_Index_Model_Process->reindexEverything()
#16 C:\mydomain\shell\indexer.php(216): Mage_Shell_Compiler->run()
#17 {main}



C:\mydomain\shell>

I don't know what else to try or do can someone please help me? Am I open to any suggestions? Please let me know if you have any Questions?

Best Answer

My hunch is that this is a result of truncating your core_url_rewrite table, which contains a mix of URLs--those generated by indexing, and those preserved for historical purposes.

The historical URLs likely are the culplrit. To resolve this it's likely best to restore that table from a backup, then delete (not truncate) only the indexed URLs via:

DELETE FROM core_url_rewrite WHERE is_system = 1;

At this point you should be able to rebuild the deleted URLs by re-indexing.

Related Solutions

Magento – Magento 1.8 reindex all

The catalog_product_index_eav table has a foreign key contraint named

FK_CAT_PRD_IDX_EAV_ENTT_ID_CAT_PRD_ENTT_ENTT_ID

Looking at this table's definition

CREATE TABLE `catalog_product_index_eav` (
  `entity_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Entity ID',
  `attribute_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Attribute ID',
  `store_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Store ID',
  `value` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Value',
  PRIMARY KEY (`entity_id`,`attribute_id`,`store_id`,`value`),
  KEY `IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID` (`entity_id`),
  KEY `IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID` (`attribute_id`),
  KEY `IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID` (`store_id`),
  KEY `IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE` (`value`),
  CONSTRAINT `FK_CATALOG_PRODUCT_INDEX_EAV_STORE_ID_CORE_STORE_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `core_store` (`store_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_CAT_PRD_IDX_EAV_ATTR_ID_EAV_ATTR_ATTR_ID` FOREIGN KEY (`attribute_id`) REFERENCES `eav_attribute` (`attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_CAT_PRD_IDX_EAV_ENTT_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`entity_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Catalog Product EAV Index Table';

we can see the foreign key definition is

CONSTRAINT `FK_CAT_PRD_IDX_EAV_ENTT_ID_CAT_PRD_ENTT_ENTT_ID` 
FOREIGN KEY (`entity_id`) 
REFERENCES `catalog_product_entity` (`entity_id`) 
ON DELETE CASCADE ON UPDATE CASCADE

This means for every entity_id row in catalog_product_index_eav, there needs to be an identical, corresponding entity_id value in catalog_product_entity.

The root of your problem is for some reason (either a rouge extension, errors caused by randomly typing in SQL from the internet, or performing data updates with disabled indexes), Magento's indexing attempts to update data in catalog_product_index_eav that violates this rule. The next step is identifying what Magento's doing so you can fix the data.

If we look at your call stack, this looks like a good place to start debugging

#10 app/code/core/Mage/Catalog/Model/Resource/Product/Indexer/Eav/Abstract.php(54): 
Mage_Index_Model_Resource_Abstract->syncData()

Jumping to that source file, we see the following bit of code

public function syncData()
{
    $this->beginTransaction();
    try {
        /**
         * Can't use truncate because of transaction
         */
        $this->_getWriteAdapter()->delete($this->getMainTable());
        $this->insertFromTable($this->getIdxTable(), $this->getMainTable(), false);
        $this->commit();
    } catch (Exception $e) {
        $this->rollBack();
        throw $e;
    }
    return $this;
}

As part of its indexing process, Magento tries to sync data from an "index table" (getIdxTable), to a "source" (getMainTable) table.

public function insertFromTable($sourceTable, $destTable, $readToIndex = true)
{
    //...
}

For this particular index, the index table is catalog_product_index_eav_idx, and the source table is catalog_product_index_eav.

Note: Be careful with your terminology around here, things are confusingly named. The "Source" table is the table we're copying to. (I believe it's called the source table because it's the "source" a normal Magento system will query from when it needs information)

So, Magento is trying to sync a row from catalog_product_index_eav_idx to the table catalog_product_index_eav. However, this causes the previously mentioned foreign key error. This leads us to two possible conclusions

The catalog_product_index_eav_idx has entity_id rows that do not exist in catalog_product_entity.
The catalog_product_index_eav table has (through previous manipulation with index checks turned off) entity_id rows that do not exist in catalog_product_entity.

So, your mission here is to figure out which entity_id rows in catalog_product_index_eav and catalog_product_index_eav_idx don't exist in catalog_product_entity, and manually delete said rows (from catalog_product_index_eav and catalog_product_index_eav_idx).

If it were me, and my catalog_product_entity table wasn't too large, I'd start with the following queries (these are untested, as I don't have any Magento tables with the above invalid data states)

SELECT * 
FROM catalog_product_index_eav_idx 
WHERE NOT (entity_id IN (SELECT entity_id from catalog_product_entity));

SELECT * FROM catalog_product_index_eav 
WHERE NOT (entity_id IN (SELECT entity_id from catalog_product_entity));

Good luck!

Magento – Product Attributes could not be reindexed

How to interpret the error message

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '2828-148-1-471' 
for key 'PRIMARY

In the case of the product attribute indexer, this means there's a constraint error while saving a record to the catalog_product_index_eav_idx table.

2828-148-1-471 refers to:

product entity id: 2828
attribute id: 148
store id: 1
value: 471

Possible origin of the problem

I'm assuming that the attribute that's causing problems is of type multiselect. A value which is linked multiple times to a product, will cause this type of error.

You can check this by executing the following query:

SELECT * FROM `catalog_product_entity_varchar` 
where entity_id = 2828 and attribute_id = 148 and store_id = 1;

I'm guessing that the id 471 will be listed multiple times in the column value. Every id should only be listed once in that column.

Are you using an importer to add product data to Magento?

Best Answer

Related Solutions

Magento – Magento 1.8 reindex all

Magento – Product Attributes could not be reindexed

Related Topic