SQL error

[SimonDukesParticipant]

Hi, I working on a client site and in the error logs I’m getting an SQL error from Ajax Search Pro.
Just updated to 4.13.1 but still getting the error.

Below is the error log entry. If you look toward the bottom the NOT IN elements are not quoted.

[05-Jul-2018 23:31:07 UTC] WordPress database error Unknown column ‘machine’ in ‘where clause’ for query
SELECT

wp_terms.name as title,
wp_terms.term_id as id,
1 as blogid,
wp_term_taxonomy.description as content,
” as date,
” as author,
wp_term_taxonomy.taxonomy as taxonomy,
‘term’ as content_type,
‘terms’ as g_content_type,
((case when
(wp_terms.name LIKE ‘%4400%’)
then 10 else 0 end) + (case when
(wp_terms.name LIKE ‘4400%’)
then 20 else 0 end) + (case when
(wp_terms.name LIKE ‘%4400%’)
then 10 else 0 end) + (case when
(wp_term_taxonomy.description LIKE ‘%4400%’)
then 3 else 0 end)) as relevance
FROM
wp_terms
LEFT JOIN wp_term_taxonomy ON wp_terms.term_id = wp_term_taxonomy.term_id

WHERE
( wp_term_taxonomy.taxonomy IN (‘post_tag’,’product_cat’,’product_type’,’pa_options’) )
AND (( wp_terms.name LIKE ‘%4400%’ ) OR ( wp_term_taxonomy.description LIKE ‘%4400%’ ))
AND (wp_terms.term_id NOT IN (machine-packages, uncategorized))
AND (1)

GROUP BY wp_terms.term_id
ORDER BY relevance DESC, date DESC, wp_terms.name ASC
LIMIT 30 made by do_action(‘wp_ajax_nopriv_ajaxsearchpro_search’), WP_Hook->do_action, WP_Hook->apply_filters, WD_ASP_Search_Handler->handle, ASP_Query->__construct, ASP_Query->get_posts, ASP_Search->search, ASP_Search_TERMS->do_search

Let me know if you would like more details or to test anything.

Regards Simon
[email protected]

[Ernest MarcinkoKeymaster]

Hi Simon,

I believe, that on the General Options -> Sources 2 panel on the search instance options you have some values entered into the Exclude terms by ID field, here: https://i.imgur.com/4AIiYYr.png
The problem is, that you probably entered term/category names, but that input field expects IDs, thus causing the query to fail.

To get the a term ID, simply open it in the WordPress category editor screen, and on the browser URL bar, you should be able to see it, like so: https://i.imgur.com/BbAs0tg.png

Once you enter the category IDs, instead of the names, the error will go away.

Best,
Ernest Marcinko
If you like my products, don't forget to rate them on codecanyon :)

[SimonDukesParticipant]

Hi Ernesto, Thanks for the pointer.
All seems to be working, I’ll let you know if I see an more errors in the logs.

Many thanks
Simon

[SimonDukesParticipant]

Hi Ernesto, getting a different SQL error now!

[16-Jul-2018 23:25:38 UTC] WordPress database error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘) OR ())
AND (wp_terms.term_id NOT IN (192,103))
‘ at line 25 for query
SELECT

wp_terms.name as title,
wp_terms.term_id as id,
1 as blogid,
wp_term_taxonomy.description as content,
” as date,
” as author,
wp_term_taxonomy.taxonomy as taxonomy,
‘term’ as content_type,
‘terms’ as g_content_type,
((case when
(wp_terms.name LIKE ‘D%’)
then 20 else 0 end) + (case when
(wp_terms.name LIKE ‘%D%’)
then 10 else 0 end) + (case when
(wp_term_taxonomy.description LIKE ‘%D%’)
then 3 else 0 end)) as relevance
FROM
wp_terms
LEFT JOIN wp_term_taxonomy ON wp_terms.term_id = wp_term_taxonomy.term_id

WHERE
( wp_term_taxonomy.taxonomy IN (‘post_tag’,’product_cat’,’product_type’,’pa_options’) )
AND (() OR ())
AND (wp_terms.term_id NOT IN (192,103))
AND (1)

GROUP BY wp_terms.term_id
ORDER BY relevance DESC, date DESC, wp_terms.name ASC
LIMIT 0 made by do_action(‘wp_ajax_nopriv_ajaxsearchpro_search’), WP_Hook->do_action, WP_Hook->apply_filters, WD_ASP_Search_Handler->handle, ASP_Query->__construct, ASP_Query->get_posts, ASP_Search->search, ASP_Search_TERMS->do_search

The AND (() OR ()) is the problem.

Seen the error in the logs about 50 times in the last week but I’ve not been able to create the error myself so I don’t know why the term and description details seem to be missing.

Any pointer would be gratefully received.

Many thanks Simon

[Ernest MarcinkoKeymaster]

Hi!

Thank you very much for letting me know, I believe this is related to one of the latest features, where the word length does not exceed a certain length.

Can you please try changing this option to 0: https://i.imgur.com/7y845bk.png

That should resolve it immediately. I will make sure to include a permanent fix in the upcoming release.

Best,
Ernest Marcinko
If you like my products, don't forget to rate them on codecanyon :)

[SimonDukesParticipant]

Thanks Ernest.

I’ll watch the logs and let you know if anything shows up.

Many thanks Simon

[SimonDukesParticipant]

Hi Ernest,

Change the option as you noted yesterday and I’ve not got any errors in the last 24hrs.

Thanks again

Simon

[Ernest MarcinkoKeymaster]

Thank you Simon, I will apply the fix to the next release then.

Best,
Ernest Marcinko
If you like my products, don't forget to rate them on codecanyon :)

[Author]

Posts