Magento – How to resolve Foreign Key constraint ‘SQLSTATE[23000]: Integrity constraint violation: 1452

errorsearch-terms

When I do a search in my magento store, it gives an error on random searchterms. Some searchterms work and some don't. With this error I used the search term 'schroef' (dutch).

enter image description here

When I look in the report with this nummer in /var/report, I get the following error + stacktrace.

a:5:{i:0;s:313:"SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`mydb`.`catalogsearch_result`, CONSTRAINT `FK_CATSRCH_RESULT_PRD_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`product_id`) 
REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCADE ON)";i:1;s:4777:"#0 /var/www/vhosts/www.mystore.nl/docs/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#1 /var/www/vhosts/www.mystore.nl/docs/lib/Zend/Db/Statement.php(300): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#2 /var/www/vhosts/www.mystore.nl/docs/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#3 /var/www/vhosts/www.mystore.nl/docs/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('INSERT INTO `ca...', Array)
#4 /var/www/vhosts/www.mystore.nl/docs/lib/Varien/Db/Adapter/Pdo/Mysql.php(419): Zend_Db_Adapter_Pdo_Abstract->query('INSERT INTO `ca...', Array)
#5 /var/www/vhosts/www.mystore.nl/docs/app/code/local/MyFolder/Sphinx/Model/CatalogSearch/Resource/Fulltext.php(92): Varien_Db_Adapter_Pdo_Mysql->query('INSERT INTO `ca...', Array)
#6 /var/www/vhosts/www.mystore.nl/docs/app/code/core/Mage/CatalogSearch/Model/Fulltext.php(136): MyFolder_Sphinx_Model_CatalogSearch_Resource_Fulltext->prepareResult(Object(Mage_CatalogSearch_Model_Fulltext), 'schroef', Object(Mage_CatalogSearch_Model_Query))
#7 /var/www/vhosts/www.mystore.nl/docs/app/code/core/Mage/CatalogSearch/Model/Resource/Fulltext/Collection.php(55): Mage_CatalogSearch_Model_Fulltext->prepareResult()
#8 /var/www/vhosts/www.mystore.nl/docs/app/code/core/Mage/CatalogSearch/Model/Layer.php(58): Mage_CatalogSearch_Model_Resource_Fulltext_Collection->addSearchFilter('schroef')
#9 /var/www/vhosts/www.mystore.nl/docs/app/code/core/Mage/CatalogSearch/Model/Layer.php(42): Mage_CatalogSearch_Model_Layer->prepareProductCollection(Object(Mage_CatalogSearch_Model_Resource_Fulltext_Collection))
#10 /var/www/vhosts/www.mystore.nl/docs/app/code/core/Mage/Catalog/Model/Layer.php(290): Mage_CatalogSearch_Model_Layer->getProductCollection()
#11 /var/www/vhosts/www.mystore.nl/docs/app/code/core/Mage/Catalog/Model/Layer.php(220): Mage_Catalog_Model_Layer->_getSetIds()
#12 /var/www/vhosts/www.mystore.nl/docs/app/code/core/Mage/Catalog/Block/Layer/View.php(163): Mage_Catalog_Model_Layer->getFilterableAttributes()
#13 /var/www/vhosts/www.mystore.nl/docs/app/code/core/Mage/Catalog/Block/Layer/View.php(122): Mage_Catalog_Block_Layer_View->_getFilterableAttributes()
#14 /var/www/vhosts/www.mystore.nl/docs/app/code/core/Mage/Core/Block/Abstract.php(238): Mage_Catalog_Block_Layer_View->_prepareLayout()
#15 /var/www/vhosts/www.mystore.nl/docs/app/code/core/Mage/Core/Model/Layout.php(456): Mage_Core_Block_Abstract->setLayout(Object(Mage_Core_Model_Layout))
#16 /var/www/vhosts/www.mystore.nl/docs/app/code/core/Mage/Core/Model/Layout.php(472): Mage_Core_Model_Layout->createBlock('catalogsearch/l...', 'catalogsearch.l...')
#17 /var/www/vhosts/www.mystore.nl/docs/app/code/core/Mage/Core/Model/Layout.php(239): Mage_Core_Model_Layout->addBlock('catalogsearch/l...', 'catalogsearch.l...')
#18 /var/www/vhosts/www.mystore.nl/docs/app/code/core/Mage/Core/Model/Layout.php(205): Mage_Core_Model_Layout->_generateBlock(Object(Mage_Core_Model_Layout_Element), Object(Mage_Core_Model_Layout_Element))
#19 /var/www/vhosts/www.mystore.nl/docs/app/code/core/Mage/Core/Model/Layout.php(210): Mage_Core_Model_Layout->generateBlocks(Object(Mage_Core_Model_Layout_Element))
#20 /var/www/vhosts/www.mystore.nl/docs/app/code/core/Mage/Core/Controller/Varien/Action.php(344): Mage_Core_Model_Layout->generateBlocks()
#21 /var/www/vhosts/www.mystore.nl/docs/app/code/core/Mage/Core/Controller/Varien/Action.php(269): Mage_Core_Controller_Varien_Action->generateLayoutBlocks()
#22 /var/www/vhosts/www.mystore.nl/docs/app/code/core/Mage/CatalogSearch/controllers/ResultController.php(77): Mage_Core_Controller_Varien_Action->loadLayout()
#23 /var/www/vhosts/www.mystore.nl/docs/app/code/core/Mage/Core/Controller/Varien/Action.php(419): Mage_CatalogSearch_ResultController->indexAction()
#24 /var/www/vhosts/www.mystore.nl/docs/app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(250): Mage_Core_Controller_Varien_Action->dispatch('index')
#25 /var/www/vhosts/www.mystore.nl/docs/app/code/community/BalkeTechnologies/StoreMaintenance/Controller/Router/Standard.php(91): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
#26 /var/www/vhosts/www.mystore.nl/docs/app/code/core/Mage/Core/Controller/Varien/Front.php(176): BalkeTechnologies_StoreMaintenance_Controller_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
#27 /var/www/vhosts/www.mystore.nl/docs/app/code/core/Mage/Core/Model/App.php(354): Mage_Core_Controller_Varien_Front->dispatch()
#28 /var/www/vhosts/www.mystore.nl/docs/app/Mage.php(683): Mage_Core_Model_App->run(Array)
#29 /var/www/vhosts/www.mystore.nl/docs/index.php(95): Mage::run('', 'store')
#30 {main}";s:3:"url";s:57:"/catalogsearch/result/?order=relevance&dir=desc&q=schroef";s:11:"script_name";s:10:"/index.php";s:4:"skin";s:7:"default";}

I don't know if it's usefull information, but I have a Sphinx server running. I can't seem to find a solution. Any help is greatly appreciated.

EDIT:

Something that I failed to mention before, but can be important. I insert/update/delete large quantities of products (~110000 products) on a regular basis. At least once per 24 hours. This I do through a cronjob that calls an import file.

Best Answer

For some reason, your Magento installation is trying to write/cache products whose IDs don't exist in your current catalog, catalog_product_entity. Catalog search results are saved in catalogsearch_result, each unique query will have a set of corresponding results saved in this table. This is why some of your search queries work and others don't work.

My guess is that Magento is using data from your indexed catalog search fulltext table, which is not up-to-date with your latest catalog. It seems like a full reindex should take care of it, unless you have made customization to the catalog search.

To see if you have indexed products for the catalog search that are not in your current catalog, run the following.

SELECT f.product_id, p.entity_id,p.sku
FROM catalogsearch_fulltext AS f
    LEFT JOIN catalog_product_entity AS p
        ON f.product_id = p.entity_id
WHERE p.entity_id IS NULL;

Any products you get returned from this query are products that are not in your catalog. When you do a catalog search that matches any of the products returned from the abovementioned query, it will cause the error you've posted.

Related Solutions

Magento 1.7 – Fix Currency ‘USD’ Not Found Error

Ok so I am not sure if this is the bug but if you compare the Magento 1.8 and Magento 1.7 versions of the function currency in Mage_Core_Model_Local you will notice a reference to a bug with Zend_Currency

Magento 1.7: Mage_Core_Model_Local->currency

/**
 * Create Zend_Currency object for current locale
 *
 * @param   string $currency
 * @return  Zend_Currency
 */
public function currency($currency)
{
    Varien_Profiler::start('locale/currency');
    if (!isset(self::$_currencyCache[$this->getLocaleCode()][$currency])) {
        try {
            $currencyObject = new Zend_Currency($currency, $this->getLocale());
        } catch (Exception $e) {
            $currencyObject = new Zend_Currency($this->getCurrency(), $this->getLocale());
            $options = array(
                    'name'      => $currency,
                    'currency'  => $currency,
                    'symbol'    => $currency
            );
            $currencyObject->setFormat($options);
        }

        self::$_currencyCache[$this->getLocaleCode()][$currency] = $currencyObject;
    }
    Varien_Profiler::stop('locale/currency');
    return self::$_currencyCache[$this->getLocaleCode()][$currency];
}

Magento 1.8: Mage_Core_Model_Local->currency

/**
 * Create Zend_Currency object for current locale
 *
 * @param   string $currency
 * @return  Zend_Currency
 */
public function currency($currency)
{
    Varien_Profiler::start('locale/currency');
    if (!isset(self::$_currencyCache[$this->getLocaleCode()][$currency])) {
        $options = array();
        try {
            $currencyObject = new Zend_Currency($currency, $this->getLocale());
        } catch (Exception $e) {
            /**
             * catch specific exceptions like "Currency 'USD' not found"
             * - back end falls with specific locals as Malaysia and etc.
             *
             * as we can see from Zend framework ticket
             * http://framework.zend.com/issues/browse/ZF-10038
             * zend team is not going to change it behaviour in the near time
             */
            $currencyObject = new Zend_Currency($currency);
            $options['name'] = $currency;
            $options['currency'] = $currency;
            $options['symbol'] = $currency;
        }

        $options = new Varien_Object($options);
        Mage::dispatchEvent('currency_display_options_forming', array(
            'currency_options' => $options,
            'base_code' => $currency
        ));

        $currencyObject->setFormat($options->toArray());
        self::$_currencyCache[$this->getLocaleCode()][$currency] = $currencyObject;
    }
    Varien_Profiler::stop('locale/currency');
    return self::$_currencyCache[$this->getLocaleCode()][$currency];
}

My suggestion would be to try updating the try catch to match that of Magento 1.8 (I know this is not the best solution and obviously I would not suggest editing the core files but adding a rewrite)

Magento – Invalid URI error after moving Magento but no underscore in URL

Can you please open lib/Zend/Uri/Http.php file and add the following lines:

$a = $this->validateUsername();
$b = $this->validatePassword();
$c = $this->validateHost();
$d = $this->validatePort();
$e = $this->validatePath();
$f = $this->validateQuery();
$g =  $this->validateFragment();
var_dump($a, $b, $c, $d, $e, $f, $g);
exit;

above the

// Validate the URI
if ($this->valid() === false) {

And post the output here. This way we can check what part of the url can't pass validation.

Also, could you please tell what the values are for "web/unsecure/base_url" and "web/secure/base_url" in your core_config_data table?

Best Answer

Related Solutions

Magento 1.7 – Fix Currency ‘USD’ Not Found Error

Magento – Invalid URI error after moving Magento but no underscore in URL

Related Topic