We are transitioning over to a new forum platform. Please post new issues there. Existing threads will be dealt with here unless you choose to move them over. Visit the new forum

Forum

Thread tagged as: Problem, Error, Blog

Search SQL error when searching just Blog

Trying to search just the Blog app and getting no results. If I search the entire sites results come in.

$query = perch_get('q');

                perch_content_search($query, array(
                    'apps' => array('PerchBlog')
                    ));

I don't know what to make of the Invalid Query in the debug output:

SELECT u.*, r.* FROM perch2_users u, perch2_user_roles r WHERE u.roleID=r.roleID AND u.userEnabled=1 AND u.userID=1 AND u.userHash='6cb1b86c6fec54451adc7e7e24eb9fa3' LIMIT 1
UPDATE perch2_users SET userHash='553a29dc746fa942fcf084ce5b210ee1' WHERE userID=1
SELECT p.privKey FROM perch2_user_privileges p
SELECT * FROM perch2_pages WHERE pagePath='/blog-search.php' LIMIT 1
Using template: /templates/pages/attributes/seo_content_display.html
SELECT * FROM perch2_pages WHERE pageNew=0 AND pageHidden=0 ORDER BY pageTreePosition ASC
SELECT pageTreePosition FROM perch2_pages WHERE pagePath='/blog-search.php' LIMIT 1
Using template: /templates/navigation/main_top.html
Using template: /templates/navigation/plain_ul.html
Using template: /templates/navigation/plain_ul.html
Using template: /templates/navigation/plain_ul.html
Using template: /templates/navigation/plain_ul.html
Search term: marriage
SELECT DISTINCT settingID, settingValue FROM perch2_settings WHERE userID=0
SELECT SQL_CALC_FOUND_ROWS DISTINCT 'PerchContent_SearchHandler' AS source, '' AS score, '' AS col1, '' AS col2, '' AS col3, '' AS col4, '' AS col5, '' AS col6, '' AS col7, '' AS col8 FROM perch2_content_regions WHERE 1=0 SELECT 'PerchBlog_SearchHandler' AS source, MATCH(postTitle, postDescRaw, postTags) AGAINST('marriage') AS score, postTitle, postSlug, postDateTime, postDescHTML, postID, sectionSlug, "", "" FROM perch2_blog_posts p, perch2_blog_sections s WHERE postStatus='Published' AND postDateTime<'2015-04-21 09:45:07' AND p.sectionID=s.sectionID AND MATCH(postTitle, postDescRaw, postTags) AGAINST('marriage') ORDER BY score DESC LIMIT 0, 10
Invalid query: SQLSTATE[42000]: Syntax error or access violation: 1064 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 'SELECT 'PerchBlog_SearchHandler' AS source, MATCH(postTitle, postDescRaw, postTa' at line 1
SELECT SQL_CALC_FOUND_ROWS DISTINCT 'PerchContent_SearchHandler' AS source, '' AS score, '' AS col1, '' AS col2, '' AS col3, '' AS col4, '' AS col5, '' AS col6, '' AS col7, '' AS col8 FROM perch2_content_regions WHERE 1=0 SELECT 'PerchBlog_SearchHandler' AS source, postDateTime AS score, postTitle, postSlug, postDateTime, postDescHTML, postID, sectionSlug, "", "" FROM perch2_blog_posts p, perch2_blog_sections s WHERE postStatus='Published' AND postDateTime<'2015-04-21 09:45:07' AND p.sectionID=s.sectionID AND ( concat(" ", postTitle, " ") REGEXP '[[:<:]]marriage[[:>:]]' OR concat(" ", postDescRaw, " ") REGEXP '[[:<:]]marriage[[:>:]]' OR concat(" ", postTags, " ") REGEXP '[[:<:]]marriage[[:>:]]' ) ORDER BY score ASC LIMIT 0, 10
Invalid query: SQLSTATE[42000]: Syntax error or access violation: 1064 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 'SELECT 'PerchBlog_SearchHandler' AS source, postDateTime AS score, postTitle, po' at line 1
SELECT FOUND_ROWS() AS `count`
Using template: /templates/search/search-result.html

Why is there an "invalid query"? What can I do about it?

Perch: 2.8.5, PHP: 5.4.26, MySQL: 5.5.34, with PDO
Server OS: Darwin, apache2handler
Installed apps: content (2.8.5), assets (2.8.5), categories (2.8.5), perch_blog (4.6), perch_forms (1.8.3)
Kirk Roberts

Kirk Roberts 0 points

  • 4 years ago
Drew McLellan

Drew McLellan 2638 points
Perch Support

Do you have any sections configured?

Just the default "Posts". I saw a previous forum thread where adding a section cleared the issue, but wasn't able to make that work (added a section, added a post to that new section, and search did not return the new post).

Drew McLellan

Drew McLellan 2638 points
Perch Support

Can you post your diagnostics report?

Sure:

Perch: 2.8.5
Production mode: Production (100)
Installed apps: content (2.8.5), assets (2.8.5), categories (2.8.5), perch_blog (4.6), perch_forms (1.8.3)
DB driver: PDO
DB tables: perch2_blog_authors, perch2_blog_comments, perch2_blog_index, perch2_blog_posts, perch2_blog_posts_to_tags, perch2_blog_sections, perch2_blog_tags, perch2_categories, perch2_category_counts, perch2_category_sets, perch2_content_index, perch2_content_items, perch2_content_regions, perch2_forms, perch2_forms_responses, perch2_navigation, perch2_navigation_pages, perch2_page_templates, perch2_pages, perch2_resource_log, perch2_resource_tags, perch2_resources, perch2_resources_to_tags, perch2_settings, perch2_user_privileges, perch2_user_role_privileges, perch2_user_roles, perch2_users
Users: 3
App runtimes:
<?php
    $apps_list = array(
        'content', 
        'categories',
        'kirk_meta',
        'kirk_email_obfuscator',
        'kirk_admin_links',
        'perch_forms',
        'perch_blog'
    );
Scheduled tasks for perch_blog: delete_spam_comments (1440 mins)
Editor plug-ins: redactor
H1: f6b5e20e21cb556149fed5388dec9a0f
L1: 107a2e97e20397b7595df3019564d565
headerColour: #ffffff
content_singlePageEdit: 1
helpURL:
siteURL: /
hideBranding: 0
content_collapseList: 0
lang: en-gb
update_2.7.3: done
latest_version: 2.8
on_sale_version: 2.8.5
update_2.8.4: done
headerScheme: light
perch_blog_post_url: /blog/{postSlug}
perch_blog_slug_format: {postTitle}
perch_blog_akismet_key:
perch_blog_max_spam_days: 0
dashboard: 0
hide_pwd_reset: 0
content_hideNonEditableRegions: 0
content_frontend_edit: 0
perch_blog_comment_notify: 0
perch_blog_update: 5.0
update_2.8.5: done
PERCH_DEVELOPMENT: 10
PERCH_STAGING: 50
PERCH_PRODUCTION: 100
PERCH_DB_USERNAME: root
PERCH_DB_SERVER: localhost
PERCH_DB_DATABASE: exude
PERCH_DB_PREFIX: perch2_
PERCH_SCHEDULE_SECRET: kEft6mohRz6Cfa
PERCH_DONT_DELETE_FILES: 1
PERCH_RWD: 1
PERCH_TZ: America/New_York
PERCH_EMAIL_FROM: info@exude.com
PERCH_EMAIL_FROM_NAME: Exude
PERCH_LOGINPATH: /admin
PERCH_PATH: /[...]/3 Development/www/admin
PERCH_CORE: /[...]/3 Development/www/admin/core
PERCH_RESFILEPATH: /[...]/3 Development/www/admin/resources
PERCH_RESPATH: /admin/resources
PERCH_HTML5: 1
PERCH_RUNWAY:
PERCH_ERROR_MODE: DIE
PERCH_DATE_LONG: %d %B %Y
PERCH_DATE_SHORT: %d %b %Y
PERCH_TIME_SHORT: %H:%M
PERCH_TIME_LONG: %H:%M:%S
PERCH_DEBUG:
PERCH_PREVIEW_ARG: preview
PERCH_TEMPLATE_PATH: /[...]/3 Development/www/admin/templates
PERCH_DEFAULT_DOC: index.php
PERCH_DEFAULT_EXT: .php
PERCH_PRODUCTION_MODE: 100
PERCH_HTML_ENTITIES:
PERCH_SSL:
PERCH_STRIPSLASHES:
PERCH_PROGRESSIVE_FLUSH: 1
PERCH_AUTH_PLUGIN:
PERCH_DB_CHARSET: utf8
PERCH_DB_PORT:
PERCH_DB_SOCKET:
HOSTING SETTINGS

PHP: 5.4.26
Zend: 2.4.0
OS: Darwin
SAPI: apache2handler
Safe mode: not detected
MySQL client: 5.5.34
MySQL server: 5.5.34
Extensions: Core, date, ereg, libxml, openssl, pcre, sqlite3, zlib, bcmath, bz2, calendar, ctype, curl, dom, hash, fileinfo, filter, ftp, gd, SPL, iconv, intl, json, ldap, mbstring, mysql, mysqli, session, PDO, pdo_sqlite, standard, posix, Reflection, Phar, SimpleXML, soap, sockets, exif, tokenizer, xml, xmlreader, xmlwriter, xsl, zip, apache2handler, imap, gettext, mcrypt, yaz, pgsql, pdo_pgsql, pdo_mysql
GD: Yes
ImageMagick: No
PHP max upload size: 32M
PHP max form post size: 32M
PHP memory limit: 256M
Total max uploadable file size: 32M
Resource folder writeable: Yes
Session timeout: 24 minutes
Native JSON: Yes
Filter functions: Yes
Transliteration functions: Yes
HTTP_HOST: exude.dev
HTTP_CONNECTION: keep-alive
HTTP_ACCEPT: text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,*/*;q=0.8
HTTP_USER_AGENT: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_10_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/42.0.2311.90 Safari/537.36
HTTP_REFERER: http://exude.dev/admin/core/settings/diagnostics/
HTTP_ACCEPT_ENCODING: gzip, deflate, sdch
HTTP_ACCEPT_LANGUAGE: en-US,en;q=0.8
HTTP_COOKIE: cmssb=1; cmsa=1; _ga=GA1.2.259184286.1429107864; PHPSESSID=c79910332dc609bd2ba28743cd64c496
PATH: /usr/bin:/bin:/usr/sbin:/sbin
SERVER_SOFTWARE: Apache
SERVER_NAME: exude.dev
SERVER_ADDR: 127.0.0.1
SERVER_PORT: 80
REMOTE_ADDR: 127.0.0.1
DOCUMENT_ROOT: /[...]/3 Development/www
SERVER_ADMIN: you@example.com
SCRIPT_FILENAME: /[...]/3 Development/www/admin/core/settings/diagnostics/index.php
REMOTE_PORT: 57757
GATEWAY_INTERFACE: CGI/1.1
SERVER_PROTOCOL: HTTP/1.1
REQUEST_METHOD: GET
QUERY_STRING: extended
REQUEST_URI: /admin/core/settings/diagnostics/?extended
SCRIPT_NAME: /admin/core/settings/diagnostics/index.php
PHP_SELF: /admin/core/settings/diagnostics/index.php
REQUEST_TIME_FLOAT: 1429633783.03
REQUEST_TIME: 1429633783
argc: 1
Drew McLellan

Drew McLellan 2638 points
Perch Support

I've no idea why that's failing, but I'll see what I can find out.

Okay, thanks. Certainly the SQL "invalid query"s seem troubling, but SQL is a stretch for me. Here's a new one that has two such errors:

Search term: lorem
SELECT DISTINCT settingID, settingValue FROM perch2_settings WHERE userID=0
SELECT SQL_CALC_FOUND_ROWS DISTINCT 'PerchContent_SearchHandler' AS source, '' AS score, '' AS col1, '' AS col2, '' AS col3, '' AS col4, '' AS col5, '' AS col6, '' AS col7, '' AS col8 FROM perch2_content_regions WHERE 1=0 SELECT 'PerchBlog_SearchHandler' AS source, MATCH(postTitle, postDescRaw, postTags) AGAINST('lorem') AS score, postTitle, postSlug, postDateTime, postDescHTML, postID, sectionSlug, "", "" FROM perch2_blog_posts p, perch2_blog_sections s WHERE postStatus='Published' AND postDateTime<'2015-04-22 12:47:53' AND p.sectionID=s.sectionID AND MATCH(postTitle, postDescRaw, postTags) AGAINST('lorem') ORDER BY score DESC LIMIT 0, 10
Invalid query: SQLSTATE[42000]: Syntax error or access violation: 1064 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 'SELECT 'PerchBlog_SearchHandler' AS source, MATCH(postTitle, postDescRaw, postTa' at line 1
SELECT SQL_CALC_FOUND_ROWS DISTINCT 'PerchContent_SearchHandler' AS source, '' AS score, '' AS col1, '' AS col2, '' AS col3, '' AS col4, '' AS col5, '' AS col6, '' AS col7, '' AS col8 FROM perch2_content_regions WHERE 1=0 SELECT 'PerchBlog_SearchHandler' AS source, postDateTime AS score, postTitle, postSlug, postDateTime, postDescHTML, postID, sectionSlug, "", "" FROM perch2_blog_posts p, perch2_blog_sections s WHERE postStatus='Published' AND postDateTime<'2015-04-22 12:47:53' AND p.sectionID=s.sectionID AND ( concat(" ", postTitle, " ") REGEXP '[[:<:]]lorem[[:>:]]' OR concat(" ", postDescRaw, " ") REGEXP '[[:<:]]lorem[[:>:]]' OR concat(" ", postTags, " ") REGEXP '[[:<:]]lorem[[:>:]]' ) ORDER BY score ASC LIMIT 0, 10
Invalid query: SQLSTATE[42000]: Syntax error or access violation: 1064 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 'SELECT 'PerchBlog_SearchHandler' AS source, postDateTime AS score, postTitle, po' at line 1
SELECT FOUND_ROWS() AS `count`
Using template: /templates/search/search-result.html
Drew McLellan

Drew McLellan 2638 points
Perch Support

Right - I need to figure out how on earth your server is finding that query to be invalid when it works for everyone else. This is not an easy problem.

I have it on a MAMP setup and on a Bluehost server. What details can I share to help the investigation?

Drew McLellan

Drew McLellan 2638 points
Perch Support

I'm not sure just yet. I'll have to let you know once I'm back in the office tomorrow morning.