Integrity constraint violations when inserting data

Question

I have a basic table that I've added through my module. This table simply holds a product ID and a JSON string.

I am running an observer method via CRON. This method loops through the product collection and inserts a new row for each product that meets some basic conditions.

When I run the cron process, I can watch the table start to populate with data. After a couple dozen inserts, the table will truncate and start over trying to insert data. It never completely inserts all the rows. I will get the typical error:

exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1444' for key 'PRIMARY'' in ...

The entry ID that shows as duplicate changes each iteration I run. I have logged the product IDs in this method (without doing inserts) and there are no duplicated values.

Of note is that my method does truncate the table before it inserts data, but this happens at the very beginning and not inside the loop.

1. Why am I getting duplicate entry violations when there are no duplicates?

2. When the cron process returns the error, is it attempting to re-run the same method again, which triggers my table truncate?

---

Sample Code:

public function updateTable() {
    $resource = Mage::getSingleton('core/resource');
    $table = $resource->getTableName('my_table');

    $write = $resource->getConnection('core_write');
    $write->truncate($table);

    $products = Mage::getModel('catalog/product')->getCollection();
    $products->addAttributeToFilter('status', array('eq' => Mage_Catalog_Model_Product_Status::STATUS_ENABLED));

    foreach ($products as $product) {
        $sizesIn = array();
        $options = $product->getProductOptionsCollection();
        foreach ($options as $option) {
            $sizesIn[$optionTitle] = array();
            $sizes = array();
            foreach ($option->getValues() as $value) {
                if ($value->getCustomoptionsQty() > 0) {
                    $sizes[$value->getTitle()] = $value->getCustomoptionsQty();
                }
            }
        }
        $json = json_encode($sizesIn);
        // product_id = INT(10)
        // option_json = TEXT
        $data = array(
            'product_id' => $product->getId(),
            'option_json' => $json
        );
        $write->insert($table, $data);
    }

}

---

Table setup

<?php

$installer = $this;
$installer->startSetup();

$table = $this->getConnection()
    ->newTable('my_table')
    ->addColumn(
        'product_id',
        Varien_Db_Ddl_Table::TYPE_INTEGER,
        12,
        array(
            'unsigned' => true,
            'nullable' => false,
            'primary'  => true
        ),
        'Product ID'
    )
    ->addColumn(
        'option_json',
        Varien_Db_Ddl_Table::TYPE_TEXT,
        array(
            'nullable' => true,
            'default'  => null
        ),
        'Option JSON'
    );


$installer->getConnection()->createTable($table);
$installer->endSetup();

Answer 1

The problem her is that you use the product_id as the primary key. Looking at your code, you fetch the entire collection of products in your Magento installation and filter the ones that are enabled. Then you loop over all products and get the options.

All good so far, but at the end you do a direct write action on your database, So you add every product that's enabled in your webshop into this table.

The error you get means that a row with that product_id already exists. So what happens here is that for some reason, there is already an entry with product_id 1444. The easiest way to fix this, is to make sure that you make sure that every product_id that you post is unique. I slightly rewritten your code, so it should work.

public function updateTable() 
{
    $resource = Mage::getSingleton('core/resource');
    $table = $resource->getTableName('my_table');

    $write = $resource->getConnection('core_write');
    $write->truncate($table);

    $insertData = array();
    $products = Mage::getModel('catalog/product')->getCollection()
        ->addAttributeToFilter('status', array('eq' => Mage_Catalog_Model_Product_Status::STATUS_ENABLED));

    foreach ($products as $product) {
        $sizesIn = array();
        $options = $product->getProductOptionsCollection();

        foreach ($options as $option) {
            $sizesIn[$optionTitle] = array();
            $sizes = array();

            foreach ($option->getValues() as $value) {
                if ($value->getCustomoptionsQty() > 0) {
                    $sizes[$value->getTitle()] = $value->getCustomoptionsQty();
                }
            }
        }

        $insertData[$product->getId()] = array(
            'product_id'  => $product->getId(),
            'option_json' => json_encode($sizesIn)
        );
    }

    $write->insertMultiple('my_table', $insertData);
}

Because the product ID is used as the key of the $insertData value, you know for sure that every entry in that array is unique. Using insertMultiple makes it possible to insert multiple items in one go.

I have to say that I didn't test this myself, so it might need some tweaks here and there, but this should at least solve the issue you're facing.