SQL error

This topic contains 7 replies, has 2 voices, and was last updated by

 
Keymaster
3 years, 6 months ago.

Viewing 8 posts - 1 through 8 (of 8 total)
  • Author
    Posts
  • #18605

    Participant

    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]

    #18617

    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 :)


    #18623

    Participant

    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

    #18674

    Participant

    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

    #18676

    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 :)


    #18687

    Participant

    Thanks Ernest.

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

    Many thanks Simon

    #18720

    Participant

    Hi Ernest,

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

    Thanks again

    Simon

    #18731

    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 :)


Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic.