WordPress.org

Forums

[resolved] SQL Server query errors for search (6 posts)

  1. EdKaim
    Member
    Posted 1 year ago #

    Last week we set up an installation hosted in Windows Azure using the Brandoo distro from the Windows Azure gallery. It's using SQL Azure for the DB. It also has some typical plugins installed, such as Jetpack and Yoast.

    While just about everything works as expected, we're getting no search results. A look at the log provides a message like the following for each query:

    [15-Dec-2013 02:15:20 UTC] WordPress database error 42000 : [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near the keyword 'LIKE'. for query SELECT TOP 10 * FROM wp_posts WHERE 1=1 AND (((wp_posts.post_title LIKE '%korea%') OR (wp_posts.post_content LIKE '%korea%'))) AND wp_posts.post_type IN ('post', 'page', 'attachment') AND (wp_posts.post_status = 'publish' OR wp_posts.post_author = 1 AND wp_posts.post_status LIKE 'private') ORDER BY wp_posts.post_title, LIKE, '%korea%' DESC, wp_posts.post_date DESC made by require('C:\DWASFiles\Sites\voyajitsu\VirtualDirectory0\site\wwwroot\wp-blog-header.php'), wp, WP->main, WP->query_posts, WP_Query->query, WP_Query->get_posts, pdo_wpdb->query, pdo_wpdb->_post_query, pdo_wpdb->print_error

    There are also a bunch of other SQL errors for various things that have happened, but we haven't noticed any problems otherwise. Any thoughts?

  2. mechx1
    Member
    Posted 1 year ago #

    You might be better off pursuing this with Brandoo support. Looking at this SQL I'm seeing some commas in places I don't think they should be which could be producing this error.

    I really don't know what you mean when you say "we're getting no search results". Do you mean the search function on your website isn't working? Maybe if you clarified it could be something this forum could help you with.

  3. EdKaim
    Member
    Posted 1 year ago #

    Thanks for getting back to me so quickly. We're getting a search results page that says there are no hits. It doesn't crash, which is nice, but the query is obviously wrong in the "order by".

  4. cwalls
    Member
    Posted 1 year ago #

    I'm encountering a similar issue. The ORDER BY clause is including the LIKE condition. Did anyone ever find a resolution for this?

  5. EdKaim
    Member
    Posted 1 year ago #

    I dug into the MSSQL plugin and it turns out that it's a complete hack. Maybe it's the only way to support MSSQL, but the solution is to perform just-in-time string manipulation to translate the MySQL query to the MSSQL syntax. As a result, it's extremely brittle and very risky to take a dependency on.

    I wouldn't recommend anyone use it because you can't rely on WordPress updates (as they might break the translation layer since they don't QA for it). You also can't rely on plugins since they don't build or QA for it. This isn't really anyone's fault, but just a fact-of-life for software. In my case, I dug into the WordPress changelog to track down the specific error I was getting in search to a seemingly innocuous change to order the results by relevance in title before body. There was no quick & easy fix, so I simply short-circuited the logic to not do that. I have other errors that happen occasionally, but my pet project site isn't worth the effort to address them at this time.

    To be clear, WordPress is awesome and MSSQL is awesome. However, WordPress on MSSQL is a bad choice because WordPress was not built to be DB-agnostic. Maybe someday that will change, but it'll still take a long time for plugin vendors to migrate forward as well, and I doubt most will. I completely regret taking the Brandoo path. You should get off it as soon as possible.

  6. Jose Luis Rodriguez
    Member
    Posted 7 months ago #

    For alll that have the same problem, and try to use the MSSQL as database engine, I rewrite some of the code in the query.php file, so you can use without error.
    I explain :
    In the Paging code :

    // Paging
    if ( empty($q['nopaging']) && !$this->is_singular ) {
    $page = absint($q['paged']);
    if ( !$page )
    $page = 1;

    if ( empty($q['offset']) ) {
    $pgstrt = ($page - 1) * $q['posts_per_page'] . ' ROWS FETCH NEXT ';
    } else { // we're ignoring $page and using 'offset'
    $q['offset'] = absint($q['offset']);
    $pgstrt = $q['offset'] . ' ROWS FETCH NEXT ';
    }
    $limits = 'OFFSET ' . $pgstrt . $q['posts_per_page'] . ' ROWS ONLY';
    }
    And in the search OrderBY :
    /**
    * Filter the ORDER BY used when ordering search results.
    *
    * @since 3.7.0
    *
    * @param string $search_orderby The ORDER BY clause.
    * @param WP_Query $this The current WP_Query instance.
    */
    $search_orderby = apply_filters( 'posts_search_orderby', $search_orderby, $this );
    if ( $search_orderby )
    $orderby = $orderby ;
    }

Topic Closed

This topic has been closed to new replies.

About this Topic

Tags

No tags yet.