Project

General

Profile

Actions

Bug #2778

closed

Search in /reports without keyword make request never end on mysql

Added by Romain Vrignaud over 11 years ago. Updated over 8 years ago.

Status:
Duplicate
Priority:
Normal
Assignee:
-
Category:
Search
Target version:
-
Difficulty:
Triaged:
Fixed in Releases:
Found in Releases:

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


Related issues 2 (0 open2 closed)

Has duplicate Foreman - Bug #13624: entering fulltext in filter report field can lead to database crashDuplicate02/09/2016Actions
Is duplicate of Foreman - Bug #10053: Table lock hangs all processesClosedMarek Hulán04/07/2015Actions
Actions #1

Updated by Dominic Cleal about 11 years ago

  • Category changed from Database to Search
Actions #2

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.

Actions #3

Updated by Dominic Cleal over 8 years ago

  • Has duplicate Bug #13624: entering fulltext in filter report field can lead to database crash added
Actions #4

Updated by Dominic Cleal over 8 years ago

  • Is duplicate of Bug #10053: Table lock hangs all processes added
Actions #5

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.

Actions

Also available in: Atom PDF