Support » Plugin: Relevanssi - A Better Search » No results found in product search because of error in SQL syntax

  • Resolved pinkivy

    (@pinkivy)


    Hello. We have a dev site that has a search feature in the header wherein the user can search the whole site or just products (managed by Woocommerce). If I select to search only products, there are always zero results. (If I search the whole site, product results are given along with non-product results.) Checking the PHP error logs, I see this SQL error:

    WordPress database error You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘)) AND (
    ( wp_postmeta.meta_key = ‘_visibility’ AND CAST(wp_postmeta.meta_val’ at line 3 for query SELECT COUNT(DISTINCT(relevanssi.doc)) FROM wp_relevanssi AS relevanssi
    INNER JOIN wp_postmeta ON ( relevanssi.doc = wp_postmeta.post_id ) WHERE (relevanssi.term LIKE ‘stain%’
    OR relevanssi.term_reverse LIKE CONCAT(REVERSE(‘stain’), ‘%’)) AND relevanssi.doc NOT IN (SELECT DISTINCT(tr.object_id) FROM wp_term_relationships AS tr WHERE tr.term_taxonomy_id IN ()) AND (
    ( wp_postmeta.meta_key = ‘_visibility’ AND CAST(wp_postmeta.meta_value AS CHAR) IN (‘visible’,’search’) )
    ) AND (
    relevanssi.doc IN (
    SELECT DISTINCT(posts.ID) FROM wp_posts AS posts
    WHERE posts.post_type IN (‘product’)
    )
    ) made by require(‘wp-blog-header.php’), wp, WP->main, WP->query_posts, WP_Query->query, WP_Query->get_posts, apply_filters_ref_array, call_user_func_array, relevanssi_query, relevanssi_do_query, relevanssi_search, relevanssi_search

    It seems to not like the empty parentheses in “WHERE tr.term_taxonomy_id IN ()”

    We are using MariaDB 10.1 for our database, but I also checked the syntax via https://www.piliapp.com/mysql-syntax-check/ to make sure that indeed the syntax is wrong, and not that MariaDB 10.1 is faulty in handling this statement.

    Is there a solution to this SQL error? (I already have the most recent version of Relevanssi.)

Viewing 5 replies - 1 through 5 (of 5 total)
  • Plugin Author Mikko Saari

    (@msaari)

    If you add this and then do a search, what does it print out?

    add_filter( 'relevanssi_modify_wp_query', 'rlv_check_parameters' );
    function rlv_check_parameters( $query ) {
        var_dump( $query->query_vars );
        exit();
    }

    Relevanssi is getting parameters it can’t parse right, which leads to bad MySQL syntax. However, all cases where Relevanssi sets the taxonomy term limitations are behind a check that there actually is some data there – so this should not happen.

    Thanks for helping me troubleshoot. When I add that code, a product search for “stain” returns this:

    array(70) { [“s”]=> string(5) “stain” [“post_type”]=> string(7) “product” [“error”]=> string(0) “” [“m”]=> string(0) “” [“p”]=> int(0) [“post_parent”]=> string(0) “” [“subpost”]=> string(0) “” [“subpost_id”]=> string(0) “” [“attachment”]=> string(0) “” [“attachment_id”]=> int(0) [“name”]=> string(0) “” [“static”]=> string(0) “” [“pagename”]=> string(0) “” [“page_id”]=> int(0) [“second”]=> string(0) “” [“minute”]=> string(0) “” [“hour”]=> string(0) “” [“day”]=> int(0) [“monthnum”]=> int(0) [“year”]=> int(0) [“w”]=> int(0) [“category_name”]=> string(0) “” [“tag”]=> string(0) “” [“cat”]=> string(0) “” [“tag_id”]=> string(0) “” [“author”]=> string(0) “” [“author_name”]=> string(0) “” [“feed”]=> string(0) “” [“tb”]=> string(0) “” [“paged”]=> int(0) [“comments_popup”]=> string(0) “” [“meta_key”]=> string(0) “” [“meta_value”]=> string(0) “” [“preview”]=> string(0) “” [“sentence”]=> string(0) “” [“title”]=> string(0) “” [“fields”]=> string(0) “” [“menu_order”]=> string(0) “” [“category__in”]=> array(0) { } [“category__not_in”]=> array(0) { } [“category__and”]=> array(0) { } [“post__in”]=> array(0) { } [“post__not_in”]=> array(0) { } [“post_name__in”]=> array(0) { } [“tag__in”]=> array(0) { } [“tag__not_in”]=> array(0) { } [“tag__and”]=> array(0) { } [“tag_slug__in”]=> array(0) { } [“tag_slug__and”]=> array(0) { } [“post_parent__in”]=> array(0) { } [“post_parent__not_in”]=> array(0) { } [“author__in”]=> array(0) { } [“author__not_in”]=> array(0) { } [“cache_results”]=> bool(false) [“orderby”]=> string(16) “menu_order title” [“order”]=> string(3) “ASC” [“meta_query”]=> array(1) { [0]=> array(3) { [“key”]=> string(11) “_visibility” [“value”]=> array(2) { [0]=> string(7) “visible” [1]=> string(6) “search” } [“compare”]=> string(2) “IN” } } [“posts_per_page”]=> int(25) [“wc_query”]=> string(13) “product_query” [“tax_query”]=> array(1) { [0]=> array(4) { [“taxonomy”]=> string(11) “product_cat” [“field”]=> string(2) “ID” [“terms”]=> array(4) { [0]=> int(986) [1]=> int(987) [2]=> int(977) [3]=> int(984) } [“operator”]=> string(6) “NOT IN” } } [“ignore_sticky_posts”]=> bool(false) [“suppress_filters”]=> bool(false) [“update_post_term_cache”]=> bool(true) [“update_post_meta_cache”]=> bool(true) [“nopaging”]=> bool(false) [“comments_per_page”]=> string(2) “50” [“no_found_rows”]=> bool(false) [“search_terms_count”]=> int(1) [“search_terms”]=> array(1) { [0]=> string(5) “stain” } [“search_orderby_title”]=> array(1) { [0]=> string(34) “wp_posts.post_title LIKE ‘%stain%'” } }

    By the way, I have set to not index any Taxonomies.

    Plugin Author Mikko Saari

    (@msaari)

    You’re still using WooCommerce version 2.x? (Based on product visibility being filtered by custom fields.)

    There’s the product_cat taxonomy filter, which should generate SQL like this:

    relevanssi.doc NOT IN (SELECT DISTINCT(tr.object_id) FROM wp_term_relationships AS tr WHERE tr.term_taxonomy_id IN ()

    – but with the term taxonomy ID numbers in the parentheses there. For some reason the numbers are not getting there.

    Here’s something you can try. Find this line in Relevanssi’s lib/search.php file:

    if ( 'id' === $row['field'] || 'term_id' === $row['field'] ) {

    It’s line 2025. Change the line to:

    if ( 'id' === strtolower( $row['field'] ) || 'term_id' === $row['field'] ) {

    Does this fix the problem?

    Your code changed fixed the problem! Thanks so much!

    I imagine this tweak will be included in a future update, so we don’t lose it with our future updates?

    As for Woocommerce, we have actually have version 2.5.5. Strangely enough, our installation does not give us the nag to update, so we didn’t notice it was very outdated. Perhaps another developer turned the nag off? Anyway, if we update, we’ll have to get the client to agree to pay for that work first. But Thanks for bringing it to our attention.

    Plugin Author Mikko Saari

    (@msaari)

    Update to WooCommerce 3 is big enough deal that it’s better agree with the client first, indeed.

    But yes, this fix will be included in the next version of Relevanssi, so you can upgrade without worries.

Viewing 5 replies - 1 through 5 (of 5 total)
  • The topic ‘No results found in product search because of error in SQL syntax’ is closed to new replies.