Article pages sometimes throw 404 error

A weird problem which I have encountered in two different magento installations (version 1.9.x.x): sometimes it happens that product pages are no longer available, instead a 404 error page is shown. In the log (var/log/exception.log) there is the following error:

Next exception 'Zend_Db_Statement_Exception' with message 'SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'xxxxx-xxx' for key 'UNQ_REPORT_VIEWED_PRODUCT_INDEX_CUSTOMER_ID_PRODUCT_ID'' in /path/to/shop/lib/Zend/Db/Statement/Pdo.php:234

This bugreport looks like exactly the same problem, seems to be a known issue with magento < 2. The link explains how to fix the bug as well, I recommend the solution by andrr at the bottom, which is to edit the magento core itself in the app/code/core/Mage/Reports/Model/Resource/Product/Index/Abstract.php file, editing the save() method like this:

    //start fix 
    //end fix

    $matchFields = array('product_id', 'store_id');

And then add updateCustomerFromVisitorByProductId to the class itself:

public function updateCustomerFromVisitorByProductId(Mage_Reports_Model_Product_Index_Abstract $object)
         * Do nothing if customer not logged in
        if (!$object->getCustomerId() || !$object->getVisitorId() || !$object->getProductId()) {
            return $this;

        $adapter = $this->_getWriteAdapter();
        $select  = $adapter->select()
            ->where('visitor_id = ?', $object->getVisitorId())
            ->where('product_id = ?', $object->getProductId());

        $rowSet = $select->query()->fetchAll();
        foreach ($rowSet as $row) {

            $select = $adapter->select()
                ->where('customer_id = ?', $object->getCustomerId())
                ->where('product_id = ?', $row['product_id']);

            $idx = $adapter->fetchRow($select);
            if ($idx) {
                /* If we are here it means that we have two rows: one with known customer, but second just visitor is set
                 * One row should be updated with customer_id, second should be deleted
                $adapter->delete($this->getMainTable(), array('index_id = ?' => $row['index_id']));
                $where = array('index_id = ?' => $idx['index_id']);
                $data  = array(
                    'visitor_id'    => $object->getVisitorId(),
                    'store_id'      => $object->getStoreId(),
                    'added_at'      => Varien_Date::now(),
            } else {
                $where = array('index_id = ?' => $row['index_id']);
                $data  = array(
                    'customer_id'   => $object->getCustomerId(),
                    'store_id'      => $object->getStoreId(),
                    'added_at'      => Varien_Date::now()

            $adapter->update($this->getMainTable(), $data, $where);

        return $this;

And that worked for me.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.