Bug #2778
closedSearch in /reports without keyword make request never end on mysql
Description
Search like that :
/reports?utf8=✓&search=neverendingquery (Do not try in production)
I had to KILL the query in order to get foreman working again.
The query produced is :
SELECT DISTINCT `reports`.id FROM `reports` LEFT OUTER JOIN `hosts` ON `hosts`.`id` = `reports`.`host_id` AND `hosts`.`type` IN ('Host::Managed') LEFT OUTER JOIN `hosts` `hosts_reports_join` ON `hosts_reports_join`.`id` = `reports`.`host_id` AND `hosts_reports_join`.`type` IN ('Host::Managed') LEFT OUTER JOIN `environments` ON `environments`.`id` = `hosts_reports_join`.`environment_id` LEFT OUTER JOIN `logs` ON `logs`.`report_id` = `reports`.`id` LEFT OUTER JOIN `messages` ON `messages`.`id` = `logs`.`message_id` LEFT OUTER JOIN `logs` `logs_reports_join` ON `logs_reports_join`.`report_id` = `reports`.`id` LEFT OUTER JOIN `sources` ON `sources`.`id` = `logs_reports_join`.`source_id` WHERE ((`hosts`.`name` LIKE '%neverendingquery%' OR `environments`.`name` LIKE '%neverendingquery%' OR `messages`.`value` LIKE '%neverendingquery%' OR `sources`.`value` LIKE '%neverendingquery%')) ORDER BY `reports`.`reported_at` DESC LIMIT 20 OFFSET 0
mysql-server 5.5 seems to take A LOT of time to answer (more than 30 minutes in production for me).
Gem used : mysql (2.9.1) and mysql2 (0.3.11)
database.yml :
production:
adapter: mysql
database: foreman
host: localhost
port:
username: foreman
password: xxx
encoding: utf8
Updated by Dominic Cleal about 11 years ago
- Category changed from Database to Search
Updated by Alex Leonhardt over 10 years ago
We are having these same issues and significantly impact production performance by now. We're looking into hacking either the template to not enable users to use kw search or better try to fix how the SQL will be generated.
Updated by Dominic Cleal over 8 years ago
- Has duplicate Bug #13624: entering fulltext in filter report field can lead to database crash added
Updated by Dominic Cleal over 8 years ago
- Is duplicate of Bug #10053: Table lock hangs all processes added
Updated by Dominic Cleal over 8 years ago
- Status changed from New to Duplicate
This is now being handled with a pull request under ticket #10053. Thanks for the report.