• Resolved phinn

    (@phinn)


    Hi,
    I’m getting search results over ajax with relevanssi_do_query. After upgrading to 4.2 I am getting errors like this:

    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 'AND ( 
      ( wp_postmeta.meta_key = 'add_to_resources' AND wp_postmeta.meta_value ' at line 2]

    the apostrophes are coming into the error as an html character code

    I’ve tried re-indexing but that doesn’t help. I also tried adding the debugging filters, but they won’t print.
    Downgrading to 4.1.4 the searching will work again

    • This topic was modified 5 years, 9 months ago by phinn.
    • This topic was modified 5 years, 9 months ago by phinn.
    • This topic was modified 5 years, 9 months ago by phinn.
Viewing 5 replies - 1 through 5 (of 5 total)
  • Plugin Author Mikko Saari

    (@msaari)

    I’d need a bit more to figure out what’s wrong. So this doesn’t work?

    add_filter( 'relevanssi_query_filter', 'rlv_check_mysql' );
    function rlv_check_mysql( $query ) {
        var_dump( $query );
        exit();
    }

    I’d need to see the complete MySQL query to be able to tell what’s going on. Also, can you please show me the query you’re doing?

    Thread Starter phinn

    (@phinn)

    Nope, i think it fails before getting to that filter.

    Here’s the mysql:

    <div id="error"><p class="wpdberror"><strong>WordPress database error:</strong> [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 'AND ( 
      ( wp_postmeta.meta_key = 'add_to_resources' AND wp_postmeta.meta_value ' at line 2]<br /><code>SELECT COUNT(DISTINCT(relevanssi.doc)) FROM wp_relevanssi AS relevanssi
    				 INNER JOIN wp_postmeta ON ( relevanssi.doc = wp_postmeta.post_id )  INNER JOIN wp_postmeta AS mt1 ON ( relevanssi.doc = mt1.post_id ) WHERE  relevanssi.term = 'live'   AND  AND ( 
      ( wp_postmeta.meta_key = 'add_to_resources' AND wp_postmeta.meta_value = 'documents' ) 
      AND 
      ( mt1.meta_key = 'search' AND mt1.meta_value IN ('live') )
    ) AND (
    			relevanssi.doc IN (
    				SELECT DISTINCT(posts.ID) FROM wp_posts AS posts
    				WHERE posts.post_type IN ('attachment')
    			) 
    		) AND ((relevanssi.doc IN (SELECT DISTINCT(posts.ID) FROM wp_posts AS posts
    				WHERE posts.post_status IN ('inherit'))) OR (doc = -1))</code></p></div><div id="error"><p class="wpdberror"><strong>WordPress database error:</strong> [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 'AND ( 
      ( wp_postmeta.meta_key = 'add_to_resources' AND wp_postmeta.meta_value ' at line 3]<br /><code>SELECT COUNT(DISTINCT(relevanssi.doc)) FROM wp_relevanssi AS relevanssi
    					 INNER JOIN wp_postmeta ON ( relevanssi.doc = wp_postmeta.post_id )  INNER JOIN wp_postmeta AS mt1 ON ( relevanssi.doc = mt1.post_id ) WHERE (relevanssi.term LIKE 'live%'
    					OR relevanssi.term_reverse LIKE CONCAT(REVERSE('live'), '%'))  AND  AND ( 
      ( wp_postmeta.meta_key = 'add_to_resources' AND wp_postmeta.meta_value = 'documents' ) 
      AND 
      ( mt1.meta_key = 'search' AND mt1.meta_value IN ('live') )
    ) AND (
    			relevanssi.doc IN (
    				SELECT DISTINCT(posts.ID) FROM wp_posts AS posts
    				WHERE posts.post_type IN ('attachment')
    			) 
    		) AND ((relevanssi.doc IN (SELECT DISTINCT(posts.ID) FROM wp_posts AS posts
    				WHERE posts.post_status IN ('inherit'))) OR (doc = -1))</code></p></div>string(1006) "SELECT DISTINCT(relevanssi.doc), relevanssi.*, relevanssi.title * 10 +
    				relevanssi.content * 2 + relevanssi.comment * 0.75 +
    				relevanssi.tag * 0.75 + relevanssi.link * 0 +
    				relevanssi.author + relevanssi.category * 0.75 + relevanssi.excerpt +
    				relevanssi.taxonomy + relevanssi.customfield + relevanssi.mysqlcolumn AS tf
    				FROM wp_relevanssi AS relevanssi  INNER JOIN wp_postmeta ON ( relevanssi.doc = wp_postmeta.post_id )  INNER JOIN wp_postmeta AS mt1 ON ( relevanssi.doc = mt1.post_id ) WHERE  relevanssi.term = 'live'   AND  AND ( 
      ( wp_postmeta.meta_key = 'add_to_resources' AND wp_postmeta.meta_value = 'documents' ) 
      AND 
      ( mt1.meta_key = 'search' AND mt1.meta_value IN ('live') )
    ) AND (
    			relevanssi.doc IN (
    				SELECT DISTINCT(posts.ID) FROM wp_posts AS posts
    				WHERE posts.post_type IN ('attachment')
    			) 
    		) AND ((relevanssi.doc IN (SELECT DISTINCT(posts.ID) FROM wp_posts AS posts
    				WHERE posts.post_status IN ('inherit'))) OR (doc = -1)) ORDER BY tf DESC LIMIT 500"
    

    here’s a query

    ["query"]=>
      array(9) {
        ["post_type"]=>
        string(10) "attachment"
        ["posts_per_page"]=>
        int(20)
        ["paged"]=>
        int(1)
        ["tax_query"]=>
        array(3) {
          ["relation"]=>
          string(3) "AND"
          [0]=>
          array(1) {
            ["relation"]=>
            string(3) "AND"
          }
          [1]=>
          array(1) {
            ["relation"]=>
            string(2) "OR"
          }
        }
        ["meta_query"]=>
        array(3) {
          ["relation"]=>
          string(3) "AND"
          [0]=>
          array(3) {
            ["key"]=>
            string(16) "add_to_resources"
            ["value"]=>
            string(9) "documents"
            ["compare"]=>
            string(1) "="
          }
          [1]=>
          array(3) {
            ["key"]=>
            string(6) "search"
            ["value"]=>
            array(1) {
              [0]=>
              string(7) "capture"
            }
            ["compare"]=>
            string(2) "IN"
          }
        }
        ["order"]=>
        string(3) "ASC"
        ["orderby"]=>
        string(5) "title"
        ["post_status"]=>
        string(7) "inherit"
        ["s"]=>
        string(7) "capture"
      }

    but getting the query example for you i think i figured it out. the tax_query is basically empty, in 4.1.4 this wasn’t an issue but now this is what is breaking it. I have a function when setting the args to filter the parameters of the request, it will return an array like above by default. If i comment this out it works again

    Plugin Author Mikko Saari

    (@msaari)

    Ok, very good – I’ll have to make sure that an empty tax query like that doesn’t break the search. It’s a query that shouldn’t happen, but Relevanssi shouldn’t choke on it either.

    Plugin Author Mikko Saari

    (@msaari)

    This will be fixed in the next version. If you want a hot fix now, change relevanssi_process_term_tax_ids() function in the /lib/search-tax-query.php so that before the return you add this:

    if ( ' AND ' === $query_restrictions ) {
        $query_restrictions = '';
    }

    This will get you rid of the error.

    Thread Starter phinn

    (@phinn)

    Thanks for your help

Viewing 5 replies - 1 through 5 (of 5 total)
  • The topic ‘getting a database error using relevanssi_do_query with 4.2’ is closed to new replies.