Strange behavior of the search module

[Dolke]

Hi to all,

 

I'm experiencing strange behavior of the search module recently. Im unable to find out where the problem lies... is it search module itself, messed data base tables, cashing system or something else...

If you look at this screenshot http://www.screencast.com/t/TqY82Gaze from my website you'll noticed that  visitor searched "111740" which is actually product reference for that particular spare part. What you will also notice is that there is only one result and that is product with reference no. 112560.

This product 112560, image, and all other data are correct, that is that product, but why would search turn this result when searching for a completely different reference number? I tried searching for possible existence of this sequence of numbers in that product but I found noting.

This is only one example and who knows how many there are. There is also a fear of this similar thing happening when someone is searching for actual words and phrases instead of a reference numbers and getting totally different results.

I want to say that product with the ref. no. 111740 doesn't exist in my DB but if it existed it would be 2nd search result. This first one is great mystery to me and I don't know how to deal with it...

Any thoughts, suggestion and possible solutions?

 

Thank you.
Dean

[stottycabanas]

Hi Dean,

I've had a look at your site and it is certainly a strange result for the search.

The only thing I can suggest is to perform a search for '111740' throughout the whole of your database. I feel it must be there somewhere hidden away in a description or a translation or something like that.

Cheers, Dave

[Dolke]

Hi stottycabanas,

 

Thanks for replaying. 
Well I used phpMyAdmin and I was able to find "111740" around 47 entries but that all together from the whole DB.

I'm not sure what tables do I need to check exactly? What tables are related to search module?

[stottycabanas]

I don't know exactly which tables are involved in the search, but I would expect them to begin with 'ps_product_'.

Are any of the 47 occurrences in the product-related tables? In particular, is there an occurrence which contains the same value of id_product that is used for the item with reference 112560? 

[Dolke]

Hm... How can I chat this last suggestion?

[stottycabanas]

Maybe try searching the database for 111740 and 1384 and select the 'all words' option?

(1384 is the id of the product with ref 112640).

Let me know the results.

[Dolke]

Hi, sorry for a delay.

I tried what you suggested but I got 0 matches.
I searched through all tables of a DB.

Edited September 23, 2013 by Dolke (see edit history)

[stottycabanas]

Hi stottycabanas,

 

Thanks for replaying. 

Well I used phpMyAdmin and I was able to find "111740" around 47 entries but that all together from the whole DB.

I'm not sure what tables do I need to check exactly? What tables are related to search module?

 

Dean, the 47 entries - how many different tables are they in?

Maybe you can export those tables via phpMyadmin, and send to me via PM?  Or even better, a backup of your database if it's not too large? I can PM you my email address if that's easier.

[Dolke]

When I performed a search again today result for 111740 was 65 matches total. Previous was 47 right.
This entries appeared in 7 different tables and non of them was related to ps_product or similar tables.

Most matches was from the table ps_statssearch, 50.

So I guess this changes over time but Im not sure how or even why.
I'm using Store Manager for Prestashop developed by eMagicOne company (http://www.prestashopmanager.com) so I wonder is this application maybe making some problems with my search index?

My search index is somehow messed up and I'm not sure how can I rebuild it properly.

 

[stottycabanas]

The entries in ps_statssearch are to be expected - they confirm that '111740' has been used as a search term on 50 occasions (some by the likes of you & me). As more searches are performed, that number will increase, maybe some from people following this thread.

Of more interest are the other 6 tables. Can you confirm which those are and in which column in each table the '111740' was found?

We won't start blaming Store Manager just yet!

[PascalVG]

Dolke,

did you try to rebuild the index already?

Preferences->Search

scroll down a little and press the link: "Rebuild the entire index"

 

My 2 cents,

pascal

[Dolke]

@stottycabanas

 

Other 6 tables where ""111740" appears are: 

 

Tables.txt

 

 

@PascaVG

 

I did, several times. I also tried to do it from the Store Manager and it was successfully done but result remained the same.

 

 

Thanks

 

Edited September 24, 2013 by Dolke (see edit history)

[stottycabanas]

Dean, could you also indicate against each table the column name where 111740 appears.

Many thanks, Dave

[Dolke]

Hi Dave,

 

I would certainly do so but how can I see the name of the actual columns when I hit browse button?

 

For example when I hit browse button beside the first 2 matches that were found under ps_connections table i see this >
http://www.screencast.com/t/mnQvo2e1

 

Edited September 25, 2013 by Dolke (see edit history)

[stottycabanas]

Hi Dean, realistically I would need to have access to your database to try & work out what's going on, which you may or may not be willing to do.

The other factor to bear in mind is that I go on holiday after tomorrow for a little over 2 weeks, and may not get much internet access during that time. So although I'm keen to get to the bottom of this, very soon I may not have the capacity to do so.

Cheers, Dave

[Dolke]

Dave, I really appreciate your help on this issue and I completely understand the situation. 

I'm not allowed to give you the access but what I can provide you is the teamviewer session. You can watch me browsing through tables and I give me some guidance of what I need to check.
 

Are you on the Skype? If you are and willing to continue this please PM me with an ID.

 

Thanks 
Dean

 

[stottycabanas]

Hi Dean,

Can you try executing these 3 queries against your DB and let me know what they produce.

 

SELECT i.id_product, i.id_word

FROM ps_search_index i, ps_search_word w

WHERE w.word = '111740'

AND i.id_word = w.id_word

 

SELECT w.id_word, w.word

FROM ps_search_index i, ps_search_word w

WHERE i.id_product = 1384

AND i.id_word = w.id_word

 

SELECT w.word

FROM ps_search_word w

WHERE w.id_word = 111740

 

I may have to pick up your responses on my travels!

[Dolke]

Sure Dave not a problem. Ill try those later on today and post the results.

 

Wish you a pleasant trip.

[stottycabanas]

Hi Dean, I have internet access for the time being, so if you want to run those queries, I can take a look at the results.

Thanks, Dave

[Dolke]

Hi Dave,

 

I'm very sorry for not replaying sooner. I was out off the office for a week as well.

 

Here are screenshots from the phpMyAdmin after running these SQL queries:
 

query1:
http://www.screencast.com/t/7JTpl7vc

query2:
http://www.screencast.com/t/PPSVxkhyzjv2

query3:

http://www.screencast.com/t/cp7YSpqmnd

Thanks 
Dean

[stottycabanas]

Hi Dean, I've got one or two clues from the results, thanks, but I really need to see the database to work things out properly.

I don't need access to your live database, but can you take a copy of it and email it to me or put it on Dropbox or similar depending on size?

Thanks, Dave

[Dolke]

Hi Dave,

 

Here is my DB dump archive (I sent you the PW for it via PM):

https://www.dropbox.com/s/7hkrhkt3766qp30/dekom_prst11.rar

btw, I found some other strange search module behavior and made completely new topic with bunch of information...

Please take a look here: http://www.prestashop.com/forums/topic/280306-native-prestashop-search-module-issuessolved-casesimprovementsother/?do=findComment&comment=1409027

 

[stottycabanas]

Hi Dean, looks like the export of your database timed out part way through. It got as far as the ps_connections table (184,000 rows of it!) before producing an error message: #2006 'MySQL server has gone away'.

You could try exporting using the custom option and exclude the tables ps_connections and ps_connections_source, which should reduce the size considerably. You may also need to change php.ini settings for timeouts.

PS sorry about the result earlier - the scoreline flattered us I think. 

Cheers, Dave

[Dolke]

Hey Dave,

 

I also found strange that it downloaded DB copy so fast.
I will try to increase these settings with my hosting provider and make a new DB dump. I will exclude those tables too.

 

P.S. I didn't watch the game... I just saw the result. 

Edited October 12, 2013 by Dolke (see edit history)

[Dolke]

Hi Dave,

 

I needed to use SSH terminal in order to properly export DB dump.
This is the link to a new and this time full DB. I forgot to exclude those tables but its fine now, its not too large.
 

https://www.dropbox.com/s/mv38ug2gfgfyzna/dbexport.rar

Password for the archive is the same.

 

Thanks
Dean

[stottycabanas]

Dean, I managed to get the DB imported (one or two issues with the size of it!), and whilst I can see that a link exists between the search term entered (111740) and the result returned (the product with id=1384), at this stage I can't find anything to indicate why that link should have been made.

The investigation continues!

I believe your PS version is 1.5.4.1 - please correct me if it's not.

I'm also assuming that none of your PS files related to the search function have been modified. Advanced parameters > Configuration Information > List of changed files will tell you which files have been amended.

Thanks, Dave

[Dolke]

Hi Dave,

 

Thanks for the input.

 

1st I want to give you the link to the topic I opened to all search specific issues that I came across.

http://www.prestashop.com/forums/topic/280306-native-prestashop-search-module-issuessolved-casesimprovementsother/?do=findComment&comment=1409027

Yes, my prestashop version is 1.5.4.1

 

Well DB is big as I have around 4000 product on the website.

 

Although I heard list of changed files feature is not working so good here is the list that it reports:
 

 

 

Nedostajući fajlovi (7)

• modules/blocknewsletter/mails/en/index.php
• modules/blockstore/store.jpg
• modules/blockwishlist/mails/en/index.php
• modules/followup/mails/en/index.php
• modules/mailalerts/mails/en/index.php
• modules/referralprogram/mails/en/index.php
• modules/sendtoafriend/mails/en/index.php

Updated files (45)

• admin-1366091183/ajaxfilemanager/inc/data.php
• admin-1366091183/autoupgrade/index.php
• classes/Dispatcher.php
• classes/Search.php
• modules/blocknewsletter/mails/index.php
• modules/blockreinsurance/img/reinsurance-1-1.jpg
• modules/blockreinsurance/img/reinsurance-2-1.jpg
• modules/blockreinsurance/img/reinsurance-3-1.jpg
• modules/blockreinsurance/img/reinsurance-4-1.jpg
• modules/blockreinsurance/img/reinsurance-5-1.jpg
• modules/blocksearch/blocksearch-instantsearch.tpl
• modules/blocksearch/blocksearch-top.tpl
• modules/blocktopmenu/css/superfish-modified.css
• modules/blockwishlist/mails/index.php
• modules/cashondelivery/cashondelivery.php
• modules/cashondelivery/index.php
• modules/cashondelivery/logo.png
• modules/followup/mails/index.php
• modules/homefeatured/homefeatured.css
• modules/mailalerts/mails/index.php
• modules/productscategory/productscategory.tpl
• modules/referralprogram/mails/index.php
• modules/sendtoafriend/mails/index.php
• modules/watermark/index.php
• modules/watermark/watermark.php
• override/index.php
• override/classes/index.php
• override/classes/cache/index.php
• override/classes/controller/index.php
• override/classes/db/index.php
• override/classes/exception/index.php
• override/classes/helper/index.php
• override/classes/log/index.php
• override/classes/module/index.php
• override/classes/order/index.php
• override/classes/pdf/index.php
• override/classes/range/index.php
• override/classes/shop/index.php
• override/classes/stock/index.php
• override/classes/tax/index.php
• override/classes/webservice/index.php
• override/controllers/index.php
• override/controllers/admin/index.php
• override/controllers/admin/templates/index.php
• override/controllers/front/index.php

 

Also I do see some sort of error here. Take a look at > http://www.screencast.com/t/3SIqIxhERgN
I don't know what it means but I think it is not related to search in anyway.

Thanks
Dean

[stottycabanas]

 

• classes/Search.php
• modules/blocksearch/blocksearch-instantsearch.tpl
• modules/blocksearch/blocksearch-top.tpl
•  

Also I do see some sort of error here. Take a look at > http://www.screencast.com/t/3SIqIxhERgN

I don't know what it means but I think it is not related to search in anyway.

 

Dean, could you zip up the 3 files I've selected from your list and post them here or on Dropbox.

I don't think the magicquotes message is related either, but from other threads it seems advisable to edit your php.ini to turn magicquotes off.

Cheers, Dave

[Dolke]

Hi Dave,

 

Here are the files you asked for: https://www.dropbox.com/s/ptwif3wmctsgbwm/files.rar

Can you post me the link to those threads to read about it? 
What does this magicquotes represent actually?

 

Thanks

[stottycabanas]

Thanks Dean. I can't access Dropbox from work, so I'll take a look later.

Magicquotes is a now-deprecated php 'security' function: http://en.wikipedia.org/wiki/Magic_quotes

Some associated threads:

http://www.prestashop.com/forums/topic/274543-blank-page-when-adding-new-cart-rule

http://www.prestashop.com/forums/topic/267630-categories-fatal-error-smarty-error-syntax-error-unrecognized-tag-nodelinkescapehtmlutf

http://www.prestashop.com/forums/topic/268380-errors

[stottycabanas]

Hi Dean,

I've spent quite a bit of time going through your database, but I have not found the answer to this. I cannot explain why

a) '111740' is held in your database as a eligible search word and 

a link gets created between that word and product 1384.

Indeed there are links to other products if other languages were enabled, but as Serbian (id_lang=7) is the only enabled language, product 1384 is the only result returned.

The only thing I can suggest is that there is some legacy information which is still being retained in the search tables. There are products with references very similar to 111740 - have you ever had a product with this reference in your store and then deleted it?

One thing you could try in a test environment is to use phpMyAdmin to take backup copies of the tables ps_search_index and ps_search_word (select Export while browsing each table), then Empty them both. Then in back office go to Preferences > Search, select Re-build the entire index, then try the search for 111740 again.

If that doesn't work, I'm pretty much out of ideas, unless it's something to do with Store Manager (which I know nothing about).

Cheers, Dave

[Dolke]

Dave thank you very much for taking so much effort and time on my issue.

I'm currently investigating it with Store Manager support agents because I think it started to appear once I started using the application.

They also think this is quite strange issue.

Answer to your question...

The only thing I can suggest is that there is some legacy information which is still being retained in the search tables. There are products with references very similar to 111740 - have you ever had a product with this reference in your store and then deleted it?

is that I probably have used it before, yes.

Regarding your suggestion... it sounds like a good one but risky too   
What if I try this on my test website? 

I have complete clone of the live website at: test.dekom.co.rs

Edited October 24, 2013 by Dolke (see edit history)

[stottycabanas]

Hi Dean, I enjoy a challenge!

My suggestion for emptying the search tables was to do it in a test environment. As you rightly point out, it's risky to carry out testing directly on the live site.

It may not tell us anything, but it's worth a try.

Cheers, Dave

[Dolke]

Dave, 

I found people like you and me rare on the forums. Its much easier to help people here who come once or twice, ask how to change the logo or to change some color scheme of their website layouts and generally use 10% of prestashop features and power.

But as I'm asking all kind of quite complex questions nobody, even most active community users wont get involved in solving and answering these questions. Its even worse when Prestashop developers and people who are directly connected to the whole PS project pass my topics sometimes not writing a word.

I know, one might say I'm the one asking all these questions, finding strange behaviors, bugs etc, how come others dont find them?
Well answer is quite simple and honest I guess:

A. They are not looking and testing enough
B. They use few % of Prestashop features and abilities and never across such problems like I did.

For those Prestashop is working perfect but not for me.

And I'm not saying that because I want to leave the PS but because I want to make it better for me and then ofc because of other users.

All this being said, I must admit to you that I fell very frustrated because this topic: http://www.prestashop.com/forums/topic/280306-native-prestashop-search-module-issuessolved-casesimprovementsother/?do=findComment&comment=1424533 

is passing unnoticed by other users, mods, admins and finally Presta Team.

Would you help me make it more alive?

 

Thank you in advance
Dean

[stottycabanas]

Thank you Dean.

I guess that's the way it works with forums such as this. Some will contribute more than others, people will find some topics of interest and relevance, but not others, etc. It will be driven by the nature of people's businesses and the extent to which they want to use Prestashop to support them, technical know-how and a number of other factors.

The mods seem to be a pretty dedicated bunch, but I suppose given the volume of topics raised, they're not going to be able to resolve every one. As for Presta themselves, they're unlikely to provide much direct detailed support as it would go against their business model.

I will be responding to your other thread on search issues, will do some testing around it first.

Enjoy your weekend.

Cheers, Dave

[PascalVG]

Hi Dean, Dave,

I followed the thread and saw your efforts to find a solution to the problem raised.

 

The problem raised here has the additional difficulty that the CAUSE is unknown and, most important, non reproducible. So it's very hard to say where it comes from. This makes it hard to say anything useful about it. I think both of you went the extra miles (and another, and another) to solve this specific problem, but without a reproducible case, it's immense hard to really get to the source of it.

 

Just back from holiday. If there's still anything you'd like us to try, let us know. I'll keep it in mind whenever I see other, similar questions.

 

Pascal

[Dolke]

Dave I agree with most of what you have said. Thats how the things work and I completely understand that.

Major problem is not Presta Team giving us support here... as you said that would be against their business model, but not fixing things they already gave to the users.

Great example of what I wrote above is the native Prestashop Google sitemap generator module.

This feature is really great and I think we users are lucky to have it. But, there has been issues with the module and many users were reporting them, me being one as well.

I really put a lot of efforts trying to give the guys as much as information as I can in order for them to fix this module asap and give us back possibility to create simple and clean sitemaps for Google. Although Mr. Benjamin said they are admitting a bug and they will address this issue urgently months has passed and that topic is still open.

OK I dont blame them, it is some hardcore issue with the module, no more time to be spent on trying to fix it... cancel it and don't offer it so we can search for alternatives or pay someone to make one right.

@PascalVG
Thank you for replaying and taking interest. 
Yes, you are right when you say this problem is hard to reproduce but I guess you don't need to reproduce it as I gave so much information on it and did a bunch of tests myself and with the Daves help ofc.

Pascal, all my issues are quite complex. I'm not using 10% of Prestashop abilites. You could better say I use 110%, this 10% being some features that I would need to add and implement.

Also, this is just one of the issues I have with the native search module. 
I put quite effort in making this cumulative report of few issues with the search module but as I said, community and Presta team is not interested or so it seems...

http://www.prestashop.com/forums/topic/280306-native-prestashop-search-module-issuessolved-casesimprovementsother/?do=findComment&comment=1424533

Thanks
Dean
 

[PascalVG]

Hi Dean,

You're right that not many people reacted on this (and other) problems, as, as you said, you have complex questions, and it's many times over their head. The community at large are just PrestaShop users, without much inside knowledge of the System. The PrestaTeam does try to solve problems when reported, but the problem first needs to be reproducible, so they can see WHERE it goes wrong, not only what goes wrong. They, understandably, won't focus on some individual's database content. The few knowledged people have their own work, and do this as their 'hobby', but, as Dave said, there are sometimes just too many questions asked to fully go for it 100%. They then have to choose between helping one person, or 10 others with simpler questions. I myself try to find some middle way, helping some intermediate questions quickly, and sometimes writing long epistels with examples, sample code, or even some module for someone. In this case, I noticed that Dave gave you quite some attention, and 'left it for him', focusing myself on a few other cases I had.

I admire Dave's determination and efforts. He stayed with you all the way!

 

Coming back to the topic, I think the suggestion of Dave to clean your search tables, as there might be some legacy stuff in there sounds like a good idea. (Of course, on a test copy) From there on, try to think about what might have caused the problem, deleting items and and adding other items, changing names and descriptions, and try some of the ideas out. See if this create the problem again. Focus on the old case's text. If I remember well, this was a product reference number, so maybe add, modify, delete this text, add the text and delete the whole item, etc. Did you ever do an import of CSV files with this reference number in it? Did you import with reference as 'ID' of a product, etc.

 

My few more cents,

pascal

[ZikmaSolutions]

Thank you thank you thank you thank you  PASCAL!