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

Search fails when Catalog Search Index is processing after updating from 1.6.1.0 to 1.7.0.0
 
Ryan Grow
Jr. Member
 
Total Posts:  21
Joined:  2009-12-04
 

I noticed after I updated to 1.7 that if the Catalog Search Index is processing then searches on the front end fail and give the following error :

Error: SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction

I looked at the thread in the database and it is just sitting there on Updating....

Has any one else seen the issue after updating?

 
Magento Community Magento Community
Magento Community
Magento Community
 
MagenX
Enthusiast
 
Total Posts:  791
Joined:  2008-05-26
Dublin
 

see it here

 
Magento Community Magento Community
Magento Community
Magento Community
 
Ryan Grow
Jr. Member
 
Total Posts:  21
Joined:  2009-12-04
 

Ok I have done a little digging…

This is what happens, when Catalog Search Index is being reindexed or when a product is being saved, the catalogsearch_query, catalogsearch_fulltext and catalogsearch_results tables get updated.

Now when someone performs a search on the front end magento tries to update the catalogsearch_query table, (but for some reason in the new code in 1.7) the thread just sits waiting for the re-index to be finished before it will return a result. (1.6.1 did not have this issue ,it would return the results even if a reindex was running)

So, just changing the innodb_lock_wait_timeout to a higher limit will not solve the issue since the search results are waiting until after the reindex finishes (which can take hours), but on the front end the search page is just hanging waiting for mysql to return a result.

I think that this is a major bug, but I’m not sure if it’s just my install or if it’s happening to others as well, but as usual this forum is no help, so I guess I will just create a fresh install on a test box and load it up with 10,000 or so products and see if I can reproduce this issue…

I’ll post an update later if anyone cares.

 
Magento Community Magento Community
Magento Community
Magento Community
 
dbabaev
Sr. Member
 
Total Posts:  83
Joined:  2008-03-29
 

not sure if it’s related but here is what happens to me:

Ever since I upgraded to 1.7.0.0, some products take forever (up to 5 min) to save in Magento. It only happens to simple products that belong to 2 or more bundle products. Some bundle products also do that. During the save process two things happen:

1. MySQL process takes up 100% of CPU for about 5 minutes.
2. During that time, I get multiple “SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded. Mostly search related.

For example, there are 4 MySQL queries that run when I save a certain simple product (which I’m guessing is because this product is associated with 4 different bundled products). Each of these queries takes about 90 seconds to run (and takes up about 3 pages of normal typed document), and because I have the index management set to “Update on Save” for the Product Prices index, every time you save a product, it has to re-index the product prices for each updated bundled product.

I’ve disabled the “Update on Save” feature of the Index Management, and saving the product takes about 10 seconds now, the only downside is that I’ll have to manually refresh the Product Prices index, but updating that manually only takes about 10 seconds as well.

This is only happening in 1.7.0.0.

 
Magento Community Magento Community
Magento Community
Magento Community
 
Ryan Grow
Jr. Member
 
Total Posts:  21
Joined:  2009-12-04
 

I think the issues may be related since it looks as if they changed how indexes are saving and rebuilding.

But I was able to reproduce the search breaking when processing the catalog search index on a clean install.

So, I did some code digging…
/app/code/core/Mage/CatalogSearch/Model/Indexer/Fulltext.php

In 1.7 they have changed the reindexAll function:

public function reindexAll()
{
$resourceModel = $this->_getIndexer()->getResource();
$resourceModel->beginTransaction();
try {
$this->_getIndexer()->rebuildIndex();
$resourceModel->commit();
} catch (Exception $e) {
$resourceModel->rollBack();
throw $e;
}
}

When I changed the function back to the 1.6.1.0 version it worked It worked I can now run the reindex process on catalog search and still have searches work on the front end.

public function reindexAll()
{
$this->_getIndexer()->rebuildIndex();
}

I have not completely fixed it though, the front end search will still stop working when someone saves a product, when the product search index is set to save on updates, so I had to turn that off. Unfortunately, it takes hours for me to run that reindex.

 
Magento Community Magento Community
Magento Community
Magento Community
 
elfling
Enthusiast
 
Avatar
Total Posts:  901
Joined:  2008-10-21
 

This is an irritating factoid. With every product save, the front end search gets killed.

Manual update is just as much as a pain, because you then a) have to run it manually, new products cannot be searched for and c) running the index will once again kill the front search.

Solutions yet? Actual solutions that is.

Anyone who says they haven’t noticed this before, set your store debug information to email you each time it fails and you’ll start to see you e-mail account fill up over time.

 
Magento Community Magento Community
Magento Community
Magento Community
 
elfling
Enthusiast
 
Avatar
Total Posts:  901
Joined:  2008-10-21
 

This problem has been addressed and fixed in Enterprise 1.13

The question is, will this fix be brought to CE and at what point in time.

 
Magento Community Magento Community
Magento Community
Magento Community
 
ali1neil
Jr. Member
 
Total Posts:  2
Joined:  2011-05-18
 

can confirm this problem exists in 1.8.0.0

While running reindex on search index, via webGUI/admin login , or through /shell it locks the search for all front end users.

 
Magento Community Magento Community
Magento Community
Magento Community
 
tsnyder
Jr. Member
 
Avatar
Total Posts:  1
Joined:  2013-09-26
St. Paul
 

I can confirm this on EE 1.13.0.1

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