4

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.

  • have you imported DB from other server? – urfusion Apr 21 '15 at 12:40
  • Yeah I have, it was a while ago though. I've moved my magento shop from a different server. However I failed to mention some information wich could be important. I have editted my question with additional information. – Tom Apr 21 '15 at 12:57
  • Do you have a "clean" copy of your original database? Perhaps the Magento Database Repair Tool can help? magentocommerce.com/wiki/1_-_installation_and_configuration/… . Also, there's obviously something wrong with your import. Are you using the standard import or a dataflow profile? – gtr1971 Apr 21 '15 at 12:59
  • try to export DB again and this time select "Disable foreign key checks" checkbox – urfusion Apr 21 '15 at 13:08
  • Did you try to rebiuld your indexes and clear you cache? If you mass important products the indizes can easily get become dangerously outdated. – user3154108 Apr 21 '15 at 13:37
2

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.

  • Sorry for the late response. First of all thank you for your answer. I've tried your query right after an import. If my import deletes products, this query returns the same amount of records that of the products i've deleted. However, if I do a reindex of the indexes this query is returning an empty set. So the catalogsearch_fulltext gets updated after a reindex. The error when searching a product in the shop still persists though.. – Tom Apr 23 '15 at 6:29
1

Was facing the same issue and get rid of it by re-indexing all through ssh. Here is the command that I used php shell/indexer.php reindexall

0

What version of MySQL are you running?

There's a problem that exists with later versions of MySQL (>5.6) where the foreign key constraint is processed immediately instead of being deferred.

The only workaround I've found is by modifying the Varien_Db_Adapter_Pdo_Mysql::insertOnDuplicate() function to set foreign key checks to 0. (after line 2035)

NOTE: For MySQL 5.7 non-deferred foreign key checks support; we must turn this off for the insert to happen

REF: http://dev.mysql.com/doc/refman/5.7/en/ansi-diff-foreign-keys.html

$insertSql = sprintf('SET FOREIGN_KEY_CHECKS=0;%s;SET FOREIGN_KEY_CHECKS=1;',$insertSql);

This solves the problem until deferred FKC's are added to InnoDB.

0

I had this issue when a setup script had failed to run (due to me accidentally naming it incorrectly) so it had not created two attributes which I tried to mass update on a product.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.