SQL error

This topic contains 7 replies, has 2 voices, and was last updated by Ernest Marcinko Ernest Marcinko 4 years, 2 months ago.

Viewing 8 posts - 1 through 8 (of 8 total)
  • Author
    Posts
  • #18605
    SimonDukes
    SimonDukes
    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
    Ernest Marcinko
    Ernest Marcinko
    Keymaster

    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
    SimonDukes
    SimonDukes
    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
    SimonDukes
    SimonDukes
    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
    Ernest Marcinko
    Ernest Marcinko
    Keymaster

    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
    SimonDukes
    SimonDukes
    Participant

    Thanks Ernest.

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

    Many thanks Simon

    #18720
    SimonDukes
    SimonDukes
    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
    Ernest Marcinko
    Ernest Marcinko
    Keymaster

    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.