Posting in the Magento forums has been disabled pending the implementation of a new and improved forum solution which should better serve the community.

For new questions please post at magento.stackexchange.com, the community-run support site for the Magento community. We will be providing updates on the new forum solution soon. For questions or concerns please email community@magento.com.

Magento Forum

After Upgrade from 1.1.8 to 1.2.1 i have 2 SQL Issues
 
kulimuli
Sr. Member
 
Total Posts:  151
Joined:  2008-03-31
Linz, Oberösterreich
 

After Upgrade to Version 1.2.1 i have 2 SQL Issues, could you help me?

At Product Search on Frontend:

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`xa6157_db1/catalogsearch_result`, CONSTRAINT `FK_CATALOGSEARCH_RESULT_QUERY` FOREIGN KEY (`query_id`) REFERENCES `catalogsearch_query` (`query_id`) ON DELETE CASCADE ON UPDATE CASCADE)
Trace:
#0 /home/www/piwa_info/lib/Zend/Db/Statement.php(283): Zend_Db_Statement_Pdo->_execute(Array)
#1 /home/www/piwa_info/lib/Zend/Db/Adapter/Abstract.php(457): Zend_Db_Statement->execute(Array)
#2 /home/www/piwa_info/lib/Zend/Db/Adapter/Pdo/Abstract.php(230): Zend_Db_Adapter_Abstract->query(’REPLACE INTO `c...’, Array)
#3 /home/www/piwa_info/lib/Varien/Db/Adapter/Pdo/Mysql.php(258): Zend_Db_Adapter_Pdo_Abstract->query(’REPLACE INTO `c...’, Array)
#4 /home/www/piwa_info/app/code/core/Mage/CatalogSearch/Model/Mysql4/Fulltext.php(308): Varien_Db_Adapter_Pdo_Mysql->query(’REPLACE INTO `c...’, Array)
#5 /home/www/piwa_info/app/code/core/Mage/CatalogSearch/Model/Fulltext.php(110): Mage_CatalogSearch_Model_Mysql4_Fulltext->prepareResult(Object(Mage_CatalogSearch_Model_Fulltext), ‘test’, Object(Mage_CatalogSearch_Model_Query))
#6 /home/www/piwa_info/app/code/core/Mage/CatalogSearch/Model/Mysql4/Fulltext/Collection.php(49): Mage_CatalogSearch_Model_Fulltext->prepareResult()
#7 /home/www/piwa_info/app/code/core/Mage/CatalogSearch/Model/Layer.php(59): Mage_CatalogSearch_Model_Mysql4_Fulltext_Collection->addSearchFilter(’test’)
#8 /home/www/piwa_info/app/code/core/Mage/CatalogSearch/Model/Layer.php(43): Mage_CatalogSearch_Model_Layer->prepareProductCollection(Object(Mage_CatalogSearch_Model_Mysql4_Fulltext_Collection))
#9 /home/www/piwa_info/app/code/core/Mage/Catalog/Model/Layer.php(209): Mage_CatalogSearch_Model_Layer->getProductCollection()
#10 /home/www/piwa_info/app/code/core/Mage/Catalog/Block/Layer/View.php(88): Mage_Catalog_Model_Layer->getFilterableAttributes()
#11 /home/www/piwa_info/app/code/core/Mage/Catalog/Block/Layer/View.php(52): Mage_Catalog_Block_Layer_View->_getFilterableAttributes()
#12 /home/www/piwa_info/app/code/core/Mage/Core/Block/Abstract.php(199): Mage_Catalog_Block_Layer_View->_prepareLayout()
#13 /home/www/piwa_info/app/code/core/Mage/Core/Model/Layout.php(444): Mage_Core_Block_Abstract->setLayout(Object(Mage_Core_Model_Layout))
#14 /home/www/piwa_info/app/code/core/Mage/Core/Model/Layout.php(229): Mage_Core_Model_Layout->addBlock(’Mage_CatalogSea...’, ‘catalogsearch.l...’)
#15 /home/www/piwa_info/app/code/core/Mage/Core/Model/Layout.php(195): Mage_Core_Model_Layout->_generateBlock(Object(Mage_Core_Model_Layout_Element), Object(Mage_Core_Model_Layout_Element))
#16 /home/www/piwa_info/app/code/core/Mage/Core/Model/Layout.php(200): Mage_Core_Model_Layout->generateBlocks(Object(Mage_Core_Model_Layout_Element))
#17 /home/www/piwa_info/app/code/core/Mage/Core/Controller/Varien/Action.php(284): Mage_Core_Model_Layout->generateBlocks()
#18 /home/www/piwa_info/app/code/core/Mage/Core/Controller/Varien/Action.php(221): Mage_Core_Controller_Varien_Action->generateLayoutBlocks()
#19 /home/www/piwa_info/app/code/core/Mage/CatalogSearch/controllers/ResultController.php(77): Mage_Core_Controller_Varien_Action->loadLayout()
#20 /home/www/piwa_info/app/code/core/Mage/Core/Controller/Varien/Action.php(348): Mage_CatalogSearch_ResultController->indexAction()
#21 /home/www/piwa_info/app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(163): Mage_Core_Controller_Varien_Action->dispatch(’index’)
#22 /home/www/piwa_info/app/code/core/Mage/Core/Controller/Varien/Front.php(174): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
#23 /home/www/piwa_info/app/Mage.php(450): Mage_Core_Controller_Varien_Front->dispatch()
#24 /home/www/piwa_info/index.php(52): Mage::run()
#25 {main}

By Add/Edit a product in Admin:
SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`xa6157_db1/weee_discount`, CONSTRAINT `FK_CATALOG_PRODUCT_ENTITY_WEEE_DISCOUNT_WEBSITE` FOREIGN KEY (`website_id`) REFERENCES `core_website` (`website_id`) ON DELETE CASCADE ON UPDATE CASCADE)

 
Magento Community Magento Community
Magento Community
Magento Community
 
mediagang
Sr. Member
 
Avatar
Total Posts:  111
Joined:  2008-01-16
Leeds UK
 

I had same problem.  Fixed it by running this sql file on my database.  It ensures that all tables are innodb.

File Attachments
innodb-fix.sql  (File Size: 11KB - Downloads: 406)
 
Magento Community Magento Community
Magento Community
Magento Community
 
kulimuli
Sr. Member
 
Total Posts:  151
Joined:  2008-03-31
Linz, Oberösterreich
 

Hi mediagang,

thanks for your reply! I executed the sql statement to my sql Database, the search ist now fixed, but the error after i clicked the save button in “edit products” is still present.
Cache is currently disabled, Browsercache cleared.

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`xa6157_db1/weee_discount`, CONSTRAINT `FK_CATALOG_PRODUCT_ENTITY_WEEE_DISCOUNT_PRODUCT_ENTITY` FOREIGN KEY (`entity_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCADE ON)

another hint?

Greetings from Austria
kulimuli

 
Magento Community Magento Community
Magento Community
Magento Community
 
thE_iNviNciblE
Sr. Member
 
Avatar
Total Posts:  220
Joined:  2008-01-09
Oldenburg
 
mediagang - 12 January 2009 08:45 AM

I had same problem.  Fixed it by running this sql file on my database.  It ensures that all tables are innodb.

thx a lot i’ll try it tomorrow.... grin

 
Magento Community Magento Community
Magento Community
Magento Community
 
markf
Sr. Member
 
Total Posts:  145
Joined:  2007-09-20
 
kulimuli - 12 January 2009 09:10 AM

Hi mediagang,

thanks for your reply! I executed the sql statement to my sql Database, the search ist now fixed, but the error after i clicked the save button in “edit products” is still present.
Cache is currently disabled, Browsercache cleared.


SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`xa6157_db1/weee_discount`, CONSTRAINT `FK_CATALOG_PRODUCT_ENTITY_WEEE_DISCOUNT_PRODUCT_ENTITY` FOREIGN KEY (`entity_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCADE ON)

another hint?

Greetings from Austria
kulimuli

I am having the same problem when i try to save a product, did you ever find a fix to this?

 
Magento Community Magento Community
Magento Community
Magento Community
 
Pro Digital
Sr. Member
 
Avatar
Total Posts:  110
Joined:  2007-12-05
Hungary
 

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`magento/catalogsearch_result`, CONSTRAINT `FK_CATALOGSEARCH_RESULT_QUERY` FOREIGN KEY (`query_id`) REFERENCES `catalogsearch_query_old` (`query_id`) ON DELETE CASCADE ON UPDATE CASCADE)

thats for me after quicksearch....

the sql fix did not help for me,

 
Magento Community Magento Community
Magento Community
Magento Community
 
turbo1
Sr. Member
 
Avatar
Total Posts:  296
Joined:  2008-08-19
Los Angeles, CA
 
mediagang - 12 January 2009 08:45 AM

I had same problem.  Fixed it by running this sql file on my database.  It ensures that all tables are innodb.

Next time give credit where credit is due..  http://www.magentocommerce.com/boards/viewthread/26356/#t86703

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top