Forum
Perch Runway: Search yields no results (database error)
Doing a search on a fresh install of Perch Runway yields no results, I can see there's a database error in the debug messages, but don't know how to fix it. Content has been added (blog, collections); and results should appear.
The error:
Invalid query: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'cr.itemSearchable' in 'where clause'
Full debug message & diagnostics below.
Debug Message
Time Δ Debug Message
0.0483 0 SELECT p.pagePath, pr.routePattern, pr.routeRegExp, p.pageTemplate FROM perch2_pages p LEFT JOIN perch2_page_routes pr ON p.pageID=pr.pageID ORDER BY pr.routeOrder ASC
0.0497 0.0014 Using master page: /templates/pages/search.php
0.0497 0 Page arguments:
0.0505 0.0007 SELECT * FROM perch2_pages WHERE pagePath='/search' LIMIT 1
0.0509 0.0004 SELECT * FROM perch2_pages WHERE pagePath='/search' LIMIT 1
0.0513 0.0004 Using template: /templates/pages/attributes/default.html
0.0515 0.0002 Using sub-template: /templates/pages/attributes/seo.html
0.0529 0.0014 SELECT * FROM perch2_pages WHERE pageNew=0 AND pageHidden=0 AND pageDepth >=0 AND pageDepth<=1 ORDER BY pageTreePosition ASC
0.0532 0.0003 SELECT pageTreePosition FROM perch2_pages WHERE pagePath='/search' LIMIT 1
0.0534 0.0002 SELECT pageID FROM perch2_pages WHERE pageTreePosition IN ('000-007', '000')
0.0538 0.0003 Using template: /templates/navigation/item.html
0.0575 0.0038 Using template: /templates/search/site-search-form.html
0.0579 0.0003 Search term: global
0.059 0.0011 SELECT DISTINCT settingID, settingValue FROM perch2_settings WHERE userID=0
0.0625 0.0035 SELECT SQL_CALC_FOUND_ROWS DISTINCT 'PerchContent_SearchHandler' AS source, MATCH(ci.itemSearch) AGAINST('global') AS score, r.regionPage AS col1, ci.itemSearch AS col2, ci.itemJSON AS col3, r.regionOptions AS col4, p.pageNavText AS col5, p.pageTitle AS col6, regionTemplate AS col7, r.regionKey AS col8 FROM perch2_content_regions r, perch2_content_items ci, perch2_pages p WHERE r.regionID=ci.regionID AND r.regionRev=ci.itemRev AND r.pageID=p.pageID AND r.regionPage!='*' AND r.regionSearchable=1 AND (MATCH(ci.itemSearch) AGAINST('global') OR MATCH(ci.itemSearch) AGAINST('global') ) AND r.regionPage LIKE '/%' UNION SELECT 'PerchContent_RunwaySearch' AS source, MATCH(ci.itemSearch) AGAINST('global') AS score, c.collectionKey AS col1, ci.itemSearch AS col2, ci.itemJSON AS col3, c.collectionOptions AS col4, c.collectionKey AS col5, c.collectionKey AS col6, collectionTemplate AS col7, c.collectionKey AS col8 FROM perch2_collections c, perch2_collection_items ci, perch2_collection_revisions cr WHERE c.collectionID=cr.collectionID AND cr.itemID=ci.itemID AND cr.itemRev=ci.itemRev AND c.collectionSearchable=1 AND cr.itemSearchable=1 AND (MATCH(ci.itemSearch) AGAINST('global') OR MATCH(ci.itemSearch) AGAINST('global') ) UNION SELECT 'PerchBlog_SearchHandler' AS source, MATCH(postTitle, postDescRaw, postTags) AGAINST('global') AS score, postTitle, postSlug, postDateTime, postDescHTML, postID, sectionSlug, "", "" FROM perch2_blog_posts p, perch2_blog_sections s WHERE postStatus='Published' AND postDateTime<'2015-02-21 23:50:18' AND p.sectionID=s.sectionID AND MATCH(postTitle, postDescRaw, postTags) AGAINST('global') ORDER BY score DESC LIMIT 0, 10
0.0633 0.0008 Invalid query: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'cr.itemSearchable' in 'where clause'
0.0635 0.0002 SELECT SQL_CALC_FOUND_ROWS DISTINCT 'PerchContent_SearchHandler' AS source, 0-(LENGTH(r.regionPage)-LENGTH(REPLACE(r.regionPage, '/', ''))) AS score, r.regionPage AS col1, ci.itemSearch AS col2, ci.itemJSON AS col3, r.regionOptions AS col4, p.pageNavText AS col5, p.pageTitle AS col6, regionTemplate AS col7, r.regionKey AS col8 FROM perch2_content_regions r, perch2_content_items ci, perch2_pages p WHERE r.regionID=ci.regionID AND r.regionRev=ci.itemRev AND r.pageID=p.pageID AND r.regionPage!='*' AND r.regionSearchable=1 AND ci.itemSearch REGEXP '[[:<:]]global[[:>:]]' AND r.regionPage LIKE '/%' UNION SELECT 'PerchContent_RunwaySearch' AS source, 1 AS score, c.collectionKey AS col1, ci.itemSearch AS col2, ci.itemJSON AS col3, c.collectionOptions AS col4, c.collectionKey AS col5, c.collectionKey AS col6, collectionTemplate AS col7, c.collectionKey AS col8 FROM perch2_collections c, perch2_collection_items ci, perch2_collection_revisions cr WHERE c.collectionID=cr.collectionID AND cr.itemID=ci.itemID AND cr.itemRev=ci.itemRev AND c.collectionSearchable=1 AND cr.itemSearchable=1 AND ci.itemSearch REGEXP '[[:<:]]global[[:>:]]' UNION 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-02-21 23:50:18' AND p.sectionID=s.sectionID AND ( concat(" ", postTitle, " ") REGEXP '[[:<:]]global[[:>:]]' OR concat(" ", postDescRaw, " ") REGEXP '[[:<:]]global[[:>:]]' OR concat(" ", postTags, " ") REGEXP '[[:<:]]global[[:>:]]' ) ORDER BY score ASC LIMIT 0, 10
0.0638 0.0003 Invalid query: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'cr.itemSearchable' in 'where clause'
0.0638 0 SELECT FOUND_ROWS() AS `count`
0.0639 0.0001 Using template: /templates/search/search-result.html
0.065 0.0011 Time: 0.0649
0.065 0 Memory: 5.3199
Diagnostics
PERCH INFORMATION
Perch: 2.7.10
Production mode: Development (10)
Installed apps: content (2.7.10), assets (2.7.10), categories (2.7.10), perch_blog (4.5.4), collection_1 (2.7.10)
DB driver: PDO
DB tables: perch2_backup_plans, perch2_backup_resources, perch2_backup_runs, 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_collection_index, perch2_collection_items, perch2_collection_revisions, perch2_collections, perch2_content_index, perch2_content_items, perch2_content_regions, perch2_navigation, perch2_navigation_pages, perch2_page_routes, perch2_page_templates, perch2_pages, perch2_resource_tags, perch2_resources, perch2_resources_to_tags, perch2_settings, perch2_user_privileges, perch2_user_role_privileges, perch2_user_roles, perch2_users
Users: 1
App runtimes:
<?php
$apps_list = array(
'content',
'categories',
'perch_blog',
);
Scheduled tasks for perch_blog: delete_spam_comments (1440 mins)
Editor plug-ins: markitup
H1: 6e8be235585c54e3115f7a328c10f3a5
L1: 78b0506ff3d96eb068dd93a00a6c5fc6
headerColour: #f79e2a
content_singlePageEdit: 1
helpURL:
siteURL: /
hideBranding: 1
content_collapseList: 0
lang: en-gb
update_2.7.10: done
headerScheme: dark
latest_version: 2.7.10
on_sale_version: 2.7.10
perch_blog_update: 5.0
perch_blog_post_url: /news/{postSlug}
perch_blog_slug_format: %Y-%m-%d-{postTitle}
perch_blog_akismet_key:
perch_blog_max_spam_days: 0
dashboard: 0
hide_pwd_reset: 0
content_hideNonEditableRegions: 1
content_frontend_edit: 0
perch_blog_comment_notify: 0
PERCH_DEVELOPMENT: 10
PERCH_STAGING: 50
PERCH_PRODUCTION: 100
PERCH_SITEPATH: /Users/xxx/Dropbox/Sites/orbit
PERCH_SCHEDULE_SECRET: ykkvE8lf1ntKy9Fw
PERCH_DB_USERNAME: root
PERCH_DB_SERVER: localhost
PERCH_DB_DATABASE: orbit
PERCH_DB_PREFIX: perch2_
PERCH_PRODUCTION_MODE: 10
PERCH_DEBUG: 1
PERCH_EMAIL_FROM: xxx@xxx.xxx
PERCH_EMAIL_FROM_NAME: xxx xxx
PERCH_LOGINPATH: /cms
PERCH_PATH: /Users/xxx/Dropbox/Sites/orbit/cms
PERCH_CORE: /Users/xxx/Dropbox/Sites/orbit/cms/core
PERCH_RESFILEPATH: /Users/xxx/Dropbox/Sites/orbit/cms/resources
PERCH_RESPATH: /cms/resources
PERCH_HTML5: 1
PERCH_TZ: Asia/Bangkok
PERCH_RUNWAY: 1
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_PREVIEW_ARG: preview
PERCH_TEMPLATE_PATH: /Users/xxx/Dropbox/Sites/orbit/cms/templates
PERCH_DEFAULT_DOC: index.php
PERCH_DEFAULT_EXT: .php
PERCH_RWD:
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.34
Zend: 2.4.0
OS: Darwin
SAPI: apache2handler
Safe mode: not detected
MySQL client: 5.5.38
MySQL server: 5.5.38
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, wddx, xml, xmlreader, xmlwriter, xsl, zip, apache2handler, imap, gettext, mcrypt, yaz, pgsql, pdo_pgsql, pdo_mysql, xdebug
GD: Yes
ImageMagick: No
PHP max upload size: 32M
PHP max form post size: 32M
PHP memory limit: 64M
Total max uploadable file size: 32M
Resource folder writeable: Yes
Session timeout: 24 minutes
Native JSON: Yes
Filter functions: Yes
Transliteration functions: Yes
TZ: Pacific/Auckland
HTTP_HOST: orbit.dev
HTTP_ACCEPT_ENCODING: gzip, deflate
HTTP_COOKIE: cmsa=1; PHPSESSID=27588f9ef3651606fffbfdfe1ee95ef5
HTTP_CONNECTION: keep-alive
HTTP_ACCEPT: text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8
HTTP_USER_AGENT: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_10_2) AppleWebKit/600.3.18 (KHTML, like Gecko) Version/8.0.3 Safari/600.3.18
HTTP_ACCEPT_LANGUAGE: fr-fr
HTTP_REFERER: https://orbit.dev/cms/core/settings/diagnostics/
HTTP_DNT: 1
PATH: /usr/bin:/bin:/usr/sbin:/sbin
SERVER_SOFTWARE: Apache
SERVER_NAME: orbit.dev
SERVER_ADDR: 127.0.0.1
SERVER_PORT: 80
REMOTE_ADDR: 127.0.0.1
DOCUMENT_ROOT: /Users/xxx/Dropbox/Sites/orbit
SERVER_ADMIN: you@example.com
SCRIPT_FILENAME: /Users/xxx/Dropbox/Sites/orbit/cms/core/settings/diagnostics/index.php
REMOTE_PORT: 64243
GATEWAY_INTERFACE: CGI/1.1
SERVER_PROTOCOL: HTTP/1.1
REQUEST_METHOD: GET
QUERY_STRING: extended
REQUEST_URI: /cms/core/settings/diagnostics/?extended
SCRIPT_NAME: /cms/core/settings/diagnostics/index.php
PHP_SELF: /cms/core/settings/diagnostics/index.php
REQUEST_TIME_FLOAT: 1424537965.02
REQUEST_TIME: 1424537965
argc: 1
Thanks for your help.
Can you try running an update by visiting this URL?
Forced update done, issue solved. Thanks Drew!
Hi
Is there a bug with the current perch_content_search function (included in Runway). To restrict content search, I used the function with a from path for blog post of
And using the apps filter
However when DEBUG is enabled I saw a query error. It would seem that when you specify the apps to filter by you still need to pass the PerchContent (the default search) in as well otherwise the generated query constructs incorrectly for the SearchHandler interface.