Bug #5670
closedInvalid sql when searching for two words
Description
Hi
If i try to make a search based of facts name i have this issue.
ActiveRecord::StatementInvalid
Mysql2::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 'OR `hostgroups`.`name` LIKE '%host%' OR `fact_names`.`short_name` LIKE '%host%')' at line 1: SELECT DISTINCT `fact_values`.id FROM `fact_values` LEFT OUTER JOIN `fact_names` ON `fact_names`.`id` = `fact_values`.`fact_name_id` LEFT OUTER JOIN `hosts` ON `hosts`.`id` = `fact_values`.`host_id` AND `hosts`.`type` IN ('Host::Managed') LEFT OUTER JOIN `hostgroups` ON `hostgroups`.`id` = `hosts`.`hostgroup_id` LEFT OUTER JOIN `hosts` `hosts_fact_values` ON `hosts_fact_values`.`id` = `fact_values`.`host_id` AND `hosts_fact_values`.`type` IN ('Host::Managed') INNER JOIN `fact_names` fact_names_171758 ON (fact_names_171758.id = fact_values.fact_name_id) WHERE `fact_names`.`ancestry` IS NULL AND (fact_names.name <> '_timestamp') AND ((fact_names_171758.`name` = '') AND (`fact_values`.`value` LIKE '%host%' OR `fact_values`.`value` LIKE '%host%' OR `fact_names`.`name` LIKE '%host%' OR OR `hostgroups`.`name` LIKE '%host%' OR `fact_names`.`short_name` LIKE '%host%')) ORDER BY `fact_values`.`value` ASC LIMIT 100 OFFSET 0
app/views/fact_values/index.html.erb:15:in `_app_views_fact_values_index_html_erb__1210802909006200332_86901560'
app/models/concerns/foreman/thread_session.rb:33:in `clear_thread'
lib/middleware/catch_json_parse_errors.rb:9:in `call'
OS: Debian 7.5
Foreman : 1.5.0-1
Mysql : 5.5.37-0+wheezy1
Updated by Dominic Cleal over 10 years ago
- Category set to Search
- Status changed from New to Resolved
Glad you got it sorted, was it an error in the search query or something?
Updated by Apely AGAMAKOU over 10 years ago
It was due to a weird query from myself ,
I'm working on a feature that it seems doesnt work "Update the location from facts"
I did everything create a fact : "location" and update the setting with this informations but still doesnt work.
Regards.
Updated by Dominic Cleal over 10 years ago
- Subject changed from Invalid sql Request. to Invalid sql when searching for two words
- Status changed from Resolved to New
- Priority changed from Normal to Low
Reopening as this is a bug, probably in scoped_search.
To reproduce, search for "a b" on the fact_values page.
Updated by Dominic Cleal over 10 years ago
- Is duplicate of Bug #5566: Fact Values Query Produces Error added
Updated by Dominic Cleal over 10 years ago
- Status changed from New to Duplicate
Seems we had this open already as #5566, it's been fixed there. Thanks for the report!