Forum

Thread tagged as: Problem, Error, Runway

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.

Stéphane Mégécaze

Stéphane Mégécaze 0 points

  • 6 years ago
Drew McLellan

Drew McLellan 2638 points
Perch Support

Can you try running an update by visiting this URL?

/cms/core/update/?force=update

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

'from-path' => '/blog/posts'

And using the apps filter

'apps' => array('PerchBlog')

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.