PowerSearch Error

[ILoveHostasaurus]

Which fatal error are we talking about here? The one related to a missing table should not be caused by an update or any other change in the software, as that's between the module and the table name it created itself (or thought it created). Now, the other error about syntax is typically due to a bug in the PowerSearch module, and one of the many reasons its use should be eliminated. It does not properly quote many variables it uses in queries, and custom field names are often the source of this problem because they're created with characters or words that are reserved words in the underlying database. If that has occurred, the only solution is to remove or rename the custom field which created the issue.

[lesliekirk]

Which fatal error are we talking about here? The one related to a missing table should not be caused by an update or any other change in the software, as that's between the module and the table name it created itself (or thought it created). Now, the other error about syntax is typically due to a bug in the PowerSearch module, and one of the many reasons its use should be eliminated. It does not properly quote many variables it uses in queries, and custom field names are often the source of this problem because they're created with characters or words that are reserved words in the underlying database. If that has occurred, the only solution is to remove or rename the custom field which created the issue.

I'm trying to eliminate coincidences. The store owner emails me telling me the PowerSearch stopped working. They email me about an hour after I run the update to 10.06. At first the only thing I had to go on was the "Application Timed Out". Then the storeowner tell me they tried to deactivate a product and got this Fatal Error

tqMULu.png

This error looks to be the same error that Pysdde got so I checked the custom fields and didn't see any that matched his issue. Now I did notice that the store has a custom field color and a custom field colors. Could that cause an issue?

[lesliekirk]

Hmmmm...how do I find out what the reserved words are?

[ILoveHostasaurus]

No does not appear so; these are the field names you'd need to avoid https://dev.mysql.com/doc/mysqld-ver...words-5-7.html

A table not existing seems very odd unless the module's code drops and recreates the table when it does the 'rebuild', but I'd expect that to cause noticeable fatal errors on every store that has a long rebuild cycle, so this appears to be some other issue with the module where it dropped the table and didn't recreate it for some reason. Perhaps there's another field name that prevented its recreation. SQL logging could be temporarily enabled to try to determine if that is the case.

[Rick Wilson]

lesliekirk can you just remove Power Search? You're asking for trouble and it's not supported.

[lesliekirk]

lesliekirk can you just remove Power Search? You're asking for trouble and it's not supported.

If it were that easy it would have been done a long time ago. Right now the module needs to at least work so that something can be resurrected from it. It not working is virtually killing the store since the customers relied heavily on the custom filtered search.

[lesliekirk]

No does not appear so; these are the field names you'd need to avoid https://dev.mysql.com/doc/mysqld-ver...words-5-7.html

A table not existing seems very odd unless the module's code drops and recreates the table when it does the 'rebuild', but I'd expect that to cause noticeable fatal errors on every store that has a long rebuild cycle, so this appears to be some other issue with the module where it dropped the table and didn't recreate it for some reason. Perhaps there's another field name that prevented its recreation. SQL logging could be temporarily enabled to try to determine if that is the case.

I've had a ticket open for this but haven't heard anything on it. I had requested a backup copy of the store be set up in dev to rule out that something happened when I ran the update to 10.06 this morning. The timing of the Fatal Error seems too coincidental to the update. But the runtime error looks to be almost identical to Psydde's:

Runtime error in mm5/5.00/modules/util/powrsrch.mvc @ [00000029:00000040]: powrsrch.mv: Line 5081: MvQUERY: mysql_stmt_prepare: Table 'delcors_devmiva5.s02_PowerSearchProducts' doesn't exist Runtime error in mm5/5.00/modules/util/powrsrch.mvc @ [00000029:00000470]: powrsrch.mv: Line 5136: MvQUERY: mysql_stmt_prepare: Table 'delcors_devmiva5.s02_PowerSearchProducts' doesn't exist

[lesliekirk]

A table not existing seems very odd unless the module's code drops and recreates the table when it does the 'rebuild', but I'd expect that to cause noticeable fatal errors on every store that has a long rebuild cycle, so this appears to be some other issue with the module where it dropped the table and didn't recreate it for some reason. Perhaps there's another field name that prevented its recreation. SQL logging could be temporarily enabled to try to determine if that is the case.

I forgot to mention, the last rebuild was done a month ago, the search was working after that rebuild.

[ILoveHostasaurus]

The failure is due to a bug in the PowerSearch module. It looks at a store's custom field name definitions, and then tries to store them in a variable with a length of ten characters. This causes every custom field whose code is longer than ten characters to be truncated at ten characters. It doesn't bother to see if this has occurred, or to see if it has any duplicates in its list of field names, it just blindly constructs a SQL query to create a new power search products table with every field name it has found, including as many duplicated fields as it constructed erroneously. The result is a query with duplicate names that fails. The module also drops the power search product table before beginning the process, so it can never recover, and leaves the search broken.

In this store's case, the cause of the duplicate fields is the use of ShipStation, which adds the custom product fields:

shipstation_asin
shipstation_isbn
shipstation_location
shipstation_upc

Those are turned into "shipstatio" by the PowerSearch module when it attempts to recreate its product table. If this store does not use those fields, you could potentially delete them, but I do not know if ShipStation polls those fields and will itself have issues if they go missing. If they're not in use, you could note them down, remove them, rebuild power search and see if it works. If shipstation then fails, you'd have to put them back and know PowerSearch can no longer be rebuilt.

Only other quick option would be to restore the entire store from backup, but changes to data since the time of the update would be lost.

I'm going to recommend internally that PowerSearch be put on the upgrade prevention list, as along with all its other failure scenarios, this one is particularly bad given it breaks a store's search. We're at the point this module is no longer safe to be using; it's based on code dating back to version 5.5 12+ years ago.

[lesliekirk]

The failure is due to a bug in the PowerSearch module. It looks at a store's custom field name definitions, and then tries to store them in a variable with a length of ten characters. This causes every custom field whose code is longer than ten characters to be truncated at ten characters. It doesn't bother to see if this has occurred, or to see if it has any duplicates in its list of field names, it just blindly constructs a SQL query to create a new power search products table with every field name it has found, including as many duplicated fields as it constructed erroneously. The result is a query with duplicate names that fails. The module also drops the power search product table before beginning the process, so it can never recover, and leaves the search broken.

In this store's case, the cause of the duplicate fields is the use of ShipStation, which adds the custom product fields:

shipstation_asin
shipstation_isbn
shipstation_location
shipstation_upc

Those are turned into "shipstatio" by the PowerSearch module when it attempts to recreate its product table. If this store does not use those fields, you could potentially delete them, but I do not know if ShipStation polls those fields and will itself have issues if they go missing. If they're not in use, you could note them down, remove them, rebuild power search, and see if it works. If shipstation then fails, you'd have to put them back and know PowerSearch can no longer be rebuilt.

Only other quick option would be to restore the entire store from backup, but changes to data since the time of the update would be lost.

I'm going to recommend internally that PowerSearch be put on the upgrade prevention list, as along with all its other failure scenarios, this one is particularly bad given it breaks a store's search. We're at the point this module is no longer safe to be using; it's based on code dating back to version 5.5 12+ years ago.

Thank you, David. Once (if) I manage to get this site's PowerSearch functioning as it was before the breakdown I will have to ask around to see who can get the Miva Search to function exactly like how PowerSearch works (worked) for this site. This has always been the sticking point with replacing this and many of the "defunct" modules, the functionality can't be exactly duplicated as needed. It's always been understood they need to go. Also, it was pointed out to me

many store owners don't actually know PowerSearch is installed in their store

This also applies to many of the other "defunct" modules. The store is humming along. The store owner is happy so they are not worrying about it.

Perhaps we can set up specific forums (or/and blog posts, knowledge bases, newsletters) to help guide others in how best to replace each of these modules. On a related note, I know there have been other store owners trying to find a suitable replacement for the Miscellaneous Fee module. Also being able to have a Custom Field collect a fee. I suspect a long laundry list could be of needs could be written along with a ton of ideas on how to make this happen in a friendly easy to understand way.

[lesliekirk]

William Davis I have a lot of custom search functionality on one of my sites that was originally created using PowerSearch. Really cool Tag search and Designer search from my product pages and some customized "smart" searches from landing pages / NavBar for things that require multiple search criteria like "masculine jewelry from designer xxx" or "mens clothing with skulls under $100". It's possible that some of this could now be rewritten using native Miva search, but the original project was costly and unfortunately I don't currently have the budget to R&D / re-write the customizations (if it's even possible.)

If I could figure out to make these changes myself I would try it.. my available time to experiment with things like this however is very tight. I would like to be on Miva native functionality wherever possible just to get rid of some of the older 3rd party modules I'm still using - but in many cases Miva's solutions end up missing one or two critical features that I use from the modules that I currently have in use.

I am actually using some of Miva' s search features in tandem with the PowerSearch custom features, so I do have faceted search in use as well.

Hey Psydde

Is your heavily customized Power Search still working for you? The site I've been working on has had its "break" again. I don't know if someone tried to do the "Rebuild Power Search File" (which I was told would break it if ever run again) or if running the "Perform Module Cleanup Tasks" for another issue may have broken it again. Much of the custom work for this particular site was done by Miva's Professional Services and like you, it's not in their budget to have them rebuild it. Have you reached out to any other developers for quotes? I was going to try and get some for the store owner (some of the custom work is very javascript heavy and my javascript foo is not strong).

[ILoveHostasaurus]

Leslie, that module's author died seven years ago, it has known severe performance issues, it has known security concerns, it became unsafe to use in any store seven years ago and only becomes more unsafe as the years go on and Miva Merchant changes. It's highly likely that module will be blacklisted at some point as it will surely cease working (well, it seems to have ceased working in at least some stores) or will ultimately have a complete compatibility failure.

[lesliekirk]

Leslie, that module's author died seven years ago, it has known severe performance issues, it has known security concerns, it became unsafe to use in any store seven years ago and only becomes more unsafe as the years go on and Miva Merchant changes. It's highly likely that module will be blacklisted at some point as it will surely cease working (well, it seems to have ceased working in at least some stores) or will ultimately have a complete compatibility failure.

Hi David, yes, I know. I was at his internment. I am trying to find someone affordable who can reproduce everything that this module is doing for a store that keeps breaking. This store put a lot of money into the custom work (from Professional Services) because (at the time) the out-of-the-box search could not hold a candle to it. They previously had tried SearchSpring but it was more than their budget could afford. I'm not ready to post to the Word for Hire forum if there are other users that might have some conversion tips that might make it easier to convert the AJAX and custom field search (to name a few of the customized things the site has). Or if someone can point me to someone who is both affordable and available. Many thanks! Leslie