You are not logged in.

Announcement

 Téléchargez la dernière version stable de GLPI      -     Et vous, que pouvez vous faire pour le projet GLPI ? :  Contribuer
 Download last stable version of GLPI                      -     What can you do for GLPI ? :  Contribute

Pages: 1

Topic closed

#1 2013-09-12 17:20:34

antoniom
Member
From: Belo Horizonte, MG, Brasil
Registered: 2012-10-10
Posts: 9

Lost connection with database when searching by all fields of tickets

Good afternoon,

After upgrade to version 0.84.1, if I do search tickets with the option "All" from the list of fields, the query is locked. In version 0.83.91 the same search took an average of 8 seconds.
GLPI only back to work after restarting the database.

May be a problem with the new version of GLPI?

I can help by providing logs and other information, if necessary.

Thank you!

Offline

#2 2013-09-13 09:13:29

yllen
GLPI-DEV
From: Sillery (51)
Registered: 2008-01-14
Posts: 15,278

Re: Lost connection with database when searching by all fields of tickets

I have no problem.
Have you some errors ? in debug mode, in files glpi.files/_log/php-error.log, sql-error.log ?

CentOS 6.5 - CentOS 7.x
PHP 5.6 - PHP 7.x - MySQL 5.6  - MariaDB 10.2 + APC + oOPcache
GLPI from 0.72 to dev version
Certifiée ITIL (ITV2F, ITILF, ITILOSA)

Offline

#3 2013-09-18 17:52:42

antoniom
Member
From: Belo Horizonte, MG, Brasil
Registered: 2012-10-10
Posts: 9

Re: Lost connection with database when searching by all fields of tickets

Hi yllen,

I checked the php-error.log and sql-error.log files and not found any error.

But I noticed the following:

I was monitoring the MySQL queries with the SHOW FULL PROCESSLIST.
When I run a search with the field "All", the MySQL query gets the status "Copying to tmp table" and not finished running. If I kill the query, GLPI back to work.
I get the query executed by GLPI through debug mode and executed manually in the MySQL console. The result was the same: the query was locked.

Finally, I restored my backup version 0.83.91 on a test environment to do some tests.
When I ran the search with the field "All" in version 0.83.91 the query was successful, and it took approximately 5 seconds. Then I did the upgrade to version 0.84.2, tried to run the search again and occurred same problem reported above. I also tried running the query generated by version 0.83.91 manually in the MySQL console, and had no problems.

If it helps, sending the below two queries: one generated by version 0.83.91 and another for version 0.84.2.

Thank you very much!


Query generated by 0.83.91 (no problem)

-- Thread Id: 210
-- User: root
-- Host: localhost
-- DB: glpi08391
-- Command: Query
-- Time: 1
-- State: Copying to tmp table
SELECT 
    'antoniom' AS currentuser,
    `glpi_tickets`.`id` AS ITEM_0,
    `glpi_tickets`.`name` AS ITEM_1,
    `glpi_tickets`.`id` AS ITEM_1_2,
    `glpi_tickets`.`content` AS ITEM_1_3,
    `glpi_tickets`.`status` AS ITEM_1_4,
    `glpi_entities`.`completename` AS ITEM_2,
    `glpi_entities`.`id` AS ITEM_2_2,
    `glpi_itilcategories`.`completename` AS ITEM_3,
    GROUP_CONCAT(DISTINCT `glpi_users_f201be21cd638ee780d08cba4ceff0d4`.`id`
        SEPARATOR '$$$$') AS ITEM_4,
    GROUP_CONCAT(DISTINCT CONCAT(`glpi_tickets_users_819efb92c8b927b345e489211ec8e43b`.`users_id`,
                ' ',
                `glpi_tickets_users_819efb92c8b927b345e489211ec8e43b`.`alternative_email`)
        SEPARATOR '$$$$') AS ITEM_4_2,
    GROUP_CONCAT(DISTINCT `glpi_users_a5e2026cbcb683e774f93a59e24a62a4`.`id`
        SEPARATOR '$$$$') AS ITEM_5,
    GROUP_CONCAT(DISTINCT CONCAT(`glpi_tickets_users_d1524bb2ea1d461ab90aed3b5f0e7e60`.`users_id`,
                ' ',
                `glpi_tickets_users_d1524bb2ea1d461ab90aed3b5f0e7e60`.`alternative_email`)
        SEPARATOR '$$$$') AS ITEM_5_2,
    `glpi_tickets`.`date` AS ITEM_6,
    `glpi_tickets`.`closedate` AS ITEM_7,
    `glpi_tickets`.`status` AS ITEM_8,
    `glpi_tickets`.`id` AS id
FROM
    `glpi_tickets`
        LEFT JOIN
    `glpi_entities` ON (`glpi_tickets`.`entities_id` = `glpi_entities`.`id`)
        LEFT JOIN
    `glpi_itilcategories` ON (`glpi_tickets`.`itilcategories_id` = `glpi_itilcategories`.`id`)
        LEFT JOIN
    `glpi_tickets_users` AS glpi_tickets_users_819efb92c8b927b345e489211ec8e43b ON (`glpi_tickets`.`id` = `glpi_tickets_users_819efb92c8b927b345e489211ec8e43b`.`tickets_id`
        AND `glpi_tickets_users_819efb92c8b927b345e489211ec8e43b`.`type` = 2)
        LEFT JOIN
    `glpi_users` AS glpi_users_f201be21cd638ee780d08cba4ceff0d4 ON (`glpi_tickets_users_819efb92c8b927b345e489211ec8e43b`.`users_id` = `glpi_users_f201be21cd638ee780d08cba4ceff0d4`.`id`)
        LEFT JOIN
    `glpi_tickets_users` AS glpi_tickets_users_d1524bb2ea1d461ab90aed3b5f0e7e60 ON (`glpi_tickets`.`id` = `glpi_tickets_users_d1524bb2ea1d461ab90aed3b5f0e7e60`.`tickets_id`
        AND `glpi_tickets_users_d1524bb2ea1d461ab90aed3b5f0e7e60`.`type` = 1)
        LEFT JOIN
    `glpi_users` AS glpi_users_a5e2026cbcb683e774f93a59e24a62a4 ON (`glpi_tickets_users_d1524bb2ea1d461ab90aed3b5f0e7e60`.`users_id` = `glpi_users_a5e2026cbcb683e774f93a59e24a62a4`.`id`)
        LEFT JOIN
    `glpi_requesttypes` ON (`glpi_tickets`.`requesttypes_id` = `glpi_requesttypes`.`id`)
        LEFT JOIN
    `glpi_users` AS glpi_users_users_id_lastupdater ON (`glpi_tickets`.`users_id_lastupdater` = `glpi_users_users_id_lastupdater`.`id`)
        LEFT JOIN
    `glpi_groups_tickets` AS glpi_groups_tickets_d1524bb2ea1d461ab90aed3b5f0e7e60 ON (`glpi_tickets`.`id` = `glpi_groups_tickets_d1524bb2ea1d461ab90aed3b5f0e7e60`.`tickets_id`
        AND `glpi_groups_tickets_d1524bb2ea1d461ab90aed3b5f0e7e60`.`type` = 1)
        LEFT JOIN
    `glpi_groups` AS glpi_groups_a0fdb25a4e0967f334115fb0dbcee9ce ON (`glpi_groups_tickets_d1524bb2ea1d461ab90aed3b5f0e7e60`.`groups_id` = `glpi_groups_a0fdb25a4e0967f334115fb0dbcee9ce`.`id`)
        LEFT JOIN
    `glpi_users` AS glpi_users_users_id_recipient ON (`glpi_tickets`.`users_id_recipient` = `glpi_users_users_id_recipient`.`id`)
        LEFT JOIN
    `glpi_tickets_users` AS glpi_tickets_users_d86d1fe2a1ea996e3820de82e6aa57e8 ON (`glpi_tickets`.`id` = `glpi_tickets_users_d86d1fe2a1ea996e3820de82e6aa57e8`.`tickets_id`
        AND `glpi_tickets_users_d86d1fe2a1ea996e3820de82e6aa57e8`.`type` = 3)
        LEFT JOIN
    `glpi_users` AS glpi_users_b746b14c5f912caf02dd66b0df98d264 ON (`glpi_tickets_users_d86d1fe2a1ea996e3820de82e6aa57e8`.`users_id` = `glpi_users_b746b14c5f912caf02dd66b0df98d264`.`id`)
        LEFT JOIN
    `glpi_groups_tickets` AS glpi_groups_tickets_d86d1fe2a1ea996e3820de82e6aa57e8 ON (`glpi_tickets`.`id` = `glpi_groups_tickets_d86d1fe2a1ea996e3820de82e6aa57e8`.`tickets_id`
        AND `glpi_groups_tickets_d86d1fe2a1ea996e3820de82e6aa57e8`.`type` = 3)
        LEFT JOIN
    `glpi_groups` AS glpi_groups_c4fd3c0e88c6dca4af1d451af9d747fb ON (`glpi_groups_tickets_d86d1fe2a1ea996e3820de82e6aa57e8`.`groups_id` = `glpi_groups_c4fd3c0e88c6dca4af1d451af9d747fb`.`id`)
        LEFT JOIN
    `glpi_suppliers` AS glpi_suppliers_suppliers_id_assign ON (`glpi_tickets`.`suppliers_id_assign` = `glpi_suppliers_suppliers_id_assign`.`id`)
        LEFT JOIN
    `glpi_groups_tickets` AS glpi_groups_tickets_819efb92c8b927b345e489211ec8e43b ON (`glpi_tickets`.`id` = `glpi_groups_tickets_819efb92c8b927b345e489211ec8e43b`.`tickets_id`
        AND `glpi_groups_tickets_819efb92c8b927b345e489211ec8e43b`.`type` = 2)
        LEFT JOIN
    `glpi_groups` AS glpi_groups_1358be41ea7158319a888236ffba5355 ON (`glpi_groups_tickets_819efb92c8b927b345e489211ec8e43b`.`groups_id` = `glpi_groups_1358be41ea7158319a888236ffba5355`.`id`)
        LEFT JOIN
    `glpi_slas` ON (`glpi_tickets`.`slas_id` = `glpi_slas`.`id`)
        LEFT JOIN
    `glpi_slalevels` ON (`glpi_tickets`.`slalevels_id` = `glpi_slalevels`.`id`)
        LEFT JOIN
    `glpi_ticketvalidations` ON (`glpi_tickets`.`id` = `glpi_ticketvalidations`.`tickets_id`)
        LEFT JOIN
    `glpi_users` AS glpi_users_57751ba960bd8511d2ad8a01bd8487f4 ON (`glpi_ticketvalidations`.`users_id` = `glpi_users_57751ba960bd8511d2ad8a01bd8487f4`.`id`)
        LEFT JOIN
    `glpi_users` AS glpi_users_users_id_validate_57751ba960bd8511d2ad8a01bd8487f4 ON (`glpi_ticketvalidations`.`users_id_validate` = `glpi_users_users_id_validate_57751ba960bd8511d2ad8a01bd8487f4`.`id`)
        LEFT JOIN
    `glpi_ticketsatisfactions` ON (`glpi_tickets`.`id` = `glpi_ticketsatisfactions`.`tickets_id`)
        LEFT JOIN
    `glpi_ticketfollowups` ON (`glpi_tickets`.`id` = `glpi_ticketfollowups`.`tickets_id`)
        LEFT JOIN
    `glpi_requesttypes` AS glpi_requesttypes_c38aefef0996a025032b9f8e81ceee2a ON (`glpi_ticketfollowups`.`requesttypes_id` = `glpi_requesttypes_c38aefef0996a025032b9f8e81ceee2a`.`id`)
        LEFT JOIN
    `glpi_users` AS glpi_users_c38aefef0996a025032b9f8e81ceee2a ON (`glpi_ticketfollowups`.`users_id` = `glpi_users_c38aefef0996a025032b9f8e81ceee2a`.`id`)
        LEFT JOIN
    `glpi_tickets_tickets` ON ((`glpi_tickets`.`id` = `glpi_tickets_tickets`.`tickets_id_1`
        OR `glpi_tickets`.`id` = `glpi_tickets_tickets`.`tickets_id_2`))
        LEFT JOIN
    `glpi_tickets_tickets` AS glpi_tickets_tickets_9b9f1a767329ed99d9c2a164b41de828 ON ((`glpi_tickets`.`id` = `glpi_tickets_tickets_9b9f1a767329ed99d9c2a164b41de828`.`tickets_id_1`
        OR `glpi_tickets`.`id` = `glpi_tickets_tickets_9b9f1a767329ed99d9c2a164b41de828`.`tickets_id_2`)
        AND `glpi_tickets_tickets_9b9f1a767329ed99d9c2a164b41de828`.`link` = 2)
        LEFT JOIN
    `glpi_tickettasks` ON (`glpi_tickets`.`id` = `glpi_tickettasks`.`tickets_id`)
        LEFT JOIN
    `glpi_taskcategories` AS glpi_taskcategories_e5ca2f53018fdc28a31faf534186b3a2 ON (`glpi_tickettasks`.`taskcategories_id` = `glpi_taskcategories_e5ca2f53018fdc28a31faf534186b3a2`.`id`)
        LEFT JOIN
    `glpi_users` AS glpi_users_e5ca2f53018fdc28a31faf534186b3a2 ON (`glpi_tickettasks`.`users_id` = `glpi_users_e5ca2f53018fdc28a31faf534186b3a2`.`id`)
        LEFT JOIN
    `glpi_users` AS glpi_users_users_id_tech_e5ca2f53018fdc28a31faf534186b3a2 ON (`glpi_tickettasks`.`users_id_tech` = `glpi_users_users_id_tech_e5ca2f53018fdc28a31faf534186b3a2`.`id`)
        LEFT JOIN
    `glpi_solutiontypes` ON (`glpi_tickets`.`solutiontypes_id` = `glpi_solutiontypes`.`id`)
WHERE
    `glpi_tickets`.`is_deleted` = '0'
        AND ((`glpi_tickets`.`name` LIKE '%printaudit%'
        OR `glpi_tickets`.`content` LIKE '%printaudit%'
        OR `glpi_tickets`.`id` LIKE '%printaudit%'
        OR `glpi_tickets`.`status` IN ('printaudit')
        OR `glpi_tickets`.`type` LIKE '%printaudit%'
        OR `glpi_tickets`.`date` LIKE '%printaudit%'
        OR `glpi_tickets`.`closedate` LIKE '%printaudit%'
        OR `glpi_tickets`.`due_date` LIKE '%printaudit%'
        OR IF(`glpi_tickets`.`due_date` IS NOT NULL
            AND (`glpi_tickets`.`solvedate` > `glpi_tickets`.`due_date`
            OR (`glpi_tickets`.`solvedate` IS NULL
            AND `glpi_tickets`.`due_date` < NOW())),
        1,
        0) LIKE '%printaudit%'
        OR `glpi_tickets`.`solvedate` LIKE '%printaudit%'
        OR `glpi_tickets`.`date_mod` LIKE '%printaudit%'
        OR `glpi_itilcategories`.`completename` LIKE '%printaudit%'
        OR `glpi_tickets`.`itemtype` LIKE '%printaudit%'
        OR `glpi_requesttypes`.`name` LIKE '%printaudit%'
        OR `glpi_entities`.`completename` LIKE '%printaudit%'
        OR (((`glpi_users_users_id_lastupdater`.`firstname` LIKE '%printaudit%'
        OR `glpi_users_users_id_lastupdater`.`realname` LIKE '%printaudit%'
        OR `glpi_users_users_id_lastupdater`.`name` LIKE '%printaudit%'
        OR CONCAT(`glpi_users_users_id_lastupdater`.`firstname`,
            ' ',
            `glpi_users_users_id_lastupdater`.`realname`) LIKE '%printaudit%')))
        OR (((`glpi_users_a5e2026cbcb683e774f93a59e24a62a4`.`firstname` LIKE '%printaudit%'
        OR `glpi_users_a5e2026cbcb683e774f93a59e24a62a4`.`realname` LIKE '%printaudit%'
        OR `glpi_users_a5e2026cbcb683e774f93a59e24a62a4`.`name` LIKE '%printaudit%'
        OR CONCAT(`glpi_users_a5e2026cbcb683e774f93a59e24a62a4`.`firstname`,
            ' ',
            `glpi_users_a5e2026cbcb683e774f93a59e24a62a4`.`realname`) LIKE '%printaudit%'))
        OR `glpi_tickets_users_d1524bb2ea1d461ab90aed3b5f0e7e60`.`alternative_email` LIKE '%printaudit%')
        OR `glpi_groups_a0fdb25a4e0967f334115fb0dbcee9ce`.`completename` LIKE '%printaudit%'
        OR (((`glpi_users_users_id_recipient`.`firstname` LIKE '%printaudit%'
        OR `glpi_users_users_id_recipient`.`realname` LIKE '%printaudit%'
        OR `glpi_users_users_id_recipient`.`name` LIKE '%printaudit%'
        OR CONCAT(`glpi_users_users_id_recipient`.`firstname`,
            ' ',
            `glpi_users_users_id_recipient`.`realname`) LIKE '%printaudit%')))
        OR (((`glpi_users_b746b14c5f912caf02dd66b0df98d264`.`firstname` LIKE '%printaudit%'
        OR `glpi_users_b746b14c5f912caf02dd66b0df98d264`.`realname` LIKE '%printaudit%'
        OR `glpi_users_b746b14c5f912caf02dd66b0df98d264`.`name` LIKE '%printaudit%'
        OR CONCAT(`glpi_users_b746b14c5f912caf02dd66b0df98d264`.`firstname`,
            ' ',
            `glpi_users_b746b14c5f912caf02dd66b0df98d264`.`realname`) LIKE '%printaudit%'))
        OR `glpi_tickets_users_d86d1fe2a1ea996e3820de82e6aa57e8`.`alternative_email` LIKE '%printaudit%')
        OR `glpi_groups_c4fd3c0e88c6dca4af1d451af9d747fb`.`completename` LIKE '%printaudit%'
        OR (((`glpi_users_f201be21cd638ee780d08cba4ceff0d4`.`firstname` LIKE '%printaudit%'
        OR `glpi_users_f201be21cd638ee780d08cba4ceff0d4`.`realname` LIKE '%printaudit%'
        OR `glpi_users_f201be21cd638ee780d08cba4ceff0d4`.`name` LIKE '%printaudit%'
        OR CONCAT(`glpi_users_f201be21cd638ee780d08cba4ceff0d4`.`firstname`,
            ' ',
            `glpi_users_f201be21cd638ee780d08cba4ceff0d4`.`realname`) LIKE '%printaudit%'))
        OR `glpi_tickets_users_819efb92c8b927b345e489211ec8e43b`.`alternative_email` LIKE '%printaudit%')
        OR `glpi_suppliers_suppliers_id_assign`.`name` LIKE '%printaudit%'
        OR `glpi_groups_1358be41ea7158319a888236ffba5355`.`completename` LIKE '%printaudit%'
        OR `glpi_tickets_users_d1524bb2ea1d461ab90aed3b5f0e7e60`.`use_notification` LIKE '%printaudit%'
        OR `glpi_tickets_users_d1524bb2ea1d461ab90aed3b5f0e7e60`.`alternative_email` LIKE '%printaudit%'
        OR `glpi_slas`.`name` LIKE '%printaudit%'
        OR `glpi_slalevels`.`name` LIKE '%printaudit%'
        OR `glpi_tickets`.`global_validation` LIKE '%printaudit%'
        OR `glpi_ticketvalidations`.`comment_submission` LIKE '%printaudit%'
        OR `glpi_ticketvalidations`.`comment_validation` LIKE '%printaudit%'
        OR `glpi_ticketvalidations`.`status` LIKE '%printaudit%'
        OR `glpi_ticketvalidations`.`submission_date` LIKE '%printaudit%'
        OR `glpi_ticketvalidations`.`validation_date` LIKE '%printaudit%'
        OR (((`glpi_users_57751ba960bd8511d2ad8a01bd8487f4`.`firstname` LIKE '%printaudit%'
        OR `glpi_users_57751ba960bd8511d2ad8a01bd8487f4`.`realname` LIKE '%printaudit%'
        OR `glpi_users_57751ba960bd8511d2ad8a01bd8487f4`.`name` LIKE '%printaudit%'
        OR CONCAT(`glpi_users_57751ba960bd8511d2ad8a01bd8487f4`.`firstname`,
            ' ',
            `glpi_users_57751ba960bd8511d2ad8a01bd8487f4`.`realname`) LIKE '%printaudit%')))
        OR (((`glpi_users_users_id_validate_57751ba960bd8511d2ad8a01bd8487f4`.`firstname` LIKE '%printaudit%'
        OR `glpi_users_users_id_validate_57751ba960bd8511d2ad8a01bd8487f4`.`realname` LIKE '%printaudit%'
        OR `glpi_users_users_id_validate_57751ba960bd8511d2ad8a01bd8487f4`.`name` LIKE '%printaudit%'
        OR CONCAT(`glpi_users_users_id_validate_57751ba960bd8511d2ad8a01bd8487f4`.`firstname`,
            ' ',
            `glpi_users_users_id_validate_57751ba960bd8511d2ad8a01bd8487f4`.`realname`) LIKE '%printaudit%')))
        OR `glpi_ticketsatisfactions`.`type` = 'printaudit'
        OR `glpi_ticketsatisfactions`.`date_begin` LIKE '%printaudit%'
        OR `glpi_ticketsatisfactions`.`date_answered` LIKE '%printaudit%'
        OR `glpi_ticketsatisfactions`.`satisfaction` LIKE '%printaudit%'
        OR `glpi_ticketsatisfactions`.`comment` LIKE '%printaudit%'
        OR `glpi_ticketfollowups`.`content` LIKE '%printaudit%'
        OR `glpi_requesttypes_c38aefef0996a025032b9f8e81ceee2a`.`name` LIKE '%printaudit%'
        OR `glpi_ticketfollowups`.`is_private` LIKE '%printaudit%'
        OR (((`glpi_users_c38aefef0996a025032b9f8e81ceee2a`.`firstname` LIKE '%printaudit%'
        OR `glpi_users_c38aefef0996a025032b9f8e81ceee2a`.`realname` LIKE '%printaudit%'
        OR `glpi_users_c38aefef0996a025032b9f8e81ceee2a`.`name` LIKE '%printaudit%'
        OR CONCAT(`glpi_users_c38aefef0996a025032b9f8e81ceee2a`.`firstname`,
            ' ',
            `glpi_users_c38aefef0996a025032b9f8e81ceee2a`.`realname`) LIKE '%printaudit%')))
        OR `glpi_tickets`.`solve_delay_stat` LIKE '%printaudit%'
        OR `glpi_tickets`.`close_delay_stat` LIKE '%printaudit%'
        OR `glpi_tickets`.`waiting_duration` LIKE '%printaudit%'
        OR `glpi_tickets`.`takeintoaccount_delay_stat` LIKE '%printaudit%'
        OR (((`glpi_tickets_tickets`.`tickets_id_1` = 'printaudit'
        OR `glpi_tickets_tickets`.`tickets_id_2` = 'printaudit')
        AND `glpi_tickets`.`id` <> 'printaudit'))
        OR (((`glpi_tickets_tickets_9b9f1a767329ed99d9c2a164b41de828`.`tickets_id_1` = 'printaudit'
        OR `glpi_tickets_tickets_9b9f1a767329ed99d9c2a164b41de828`.`tickets_id_2` = 'printaudit')
        AND `glpi_tickets`.`id` <> 'printaudit'))
        OR `glpi_tickettasks`.`content` LIKE '%printaudit%'
        OR `glpi_taskcategories_e5ca2f53018fdc28a31faf534186b3a2`.`name` LIKE '%printaudit%'
        OR `glpi_tickettasks`.`is_private` LIKE '%printaudit%'
        OR (((`glpi_users_e5ca2f53018fdc28a31faf534186b3a2`.`firstname` LIKE '%printaudit%'
        OR `glpi_users_e5ca2f53018fdc28a31faf534186b3a2`.`realname` LIKE '%printaudit%'
        OR `glpi_users_e5ca2f53018fdc28a31faf534186b3a2`.`name` LIKE '%printaudit%'
        OR CONCAT(`glpi_users_e5ca2f53018fdc28a31faf534186b3a2`.`firstname`,
            ' ',
            `glpi_users_e5ca2f53018fdc28a31faf534186b3a2`.`realname`) LIKE '%printaudit%')))
        OR (((`glpi_users_users_id_tech_e5ca2f53018fdc28a31faf534186b3a2`.`firstname` LIKE '%printaudit%'
        OR `glpi_users_users_id_tech_e5ca2f53018fdc28a31faf534186b3a2`.`realname` LIKE '%printaudit%'
        OR `glpi_users_users_id_tech_e5ca2f53018fdc28a31faf534186b3a2`.`name` LIKE '%printaudit%'
        OR CONCAT(`glpi_users_users_id_tech_e5ca2f53018fdc28a31faf534186b3a2`.`firstname`,
            ' ',
            `glpi_users_users_id_tech_e5ca2f53018fdc28a31faf534186b3a2`.`realname`) LIKE '%printaudit%')))
        OR `glpi_tickettasks`.`actiontime` LIKE '%printaudit%'
        OR `glpi_tickettasks`.`date` LIKE '%printaudit%'
        OR `glpi_solutiontypes`.`name` LIKE '%printaudit%'
        OR `glpi_tickets`.`solution` LIKE '%printaudit%'
        OR `glpi_tickets`.`cost_time` LIKE '%printaudit%'
        OR `glpi_tickets`.`cost_fixed` LIKE '%printaudit%'
        OR `glpi_tickets`.`cost_material` LIKE '%printaudit%'))
GROUP BY `glpi_tickets`.`id`
ORDER BY ITEM_0 DESC

Query generated by 0.84.2 (with problem)

-- Thread Id: 181
-- User: root
-- Host: localhost
-- DB: glpi
-- Command: Query
-- Time: 62
-- State: Copying to tmp table
SELECT 
    'antoniom' AS currentuser,
    `glpi_tickets`.`id` AS ITEM_0,
    `glpi_tickets`.`name` AS ITEM_1,
    `glpi_tickets`.`id` AS ITEM_1_2,
    `glpi_tickets`.`content` AS ITEM_1_3,
    `glpi_tickets`.`status` AS ITEM_1_4,
    `glpi_entities`.`completename` AS ITEM_2,
    `glpi_itilcategories`.`completename` AS ITEM_3,
    GROUP_CONCAT(DISTINCT `glpi_users_c5e682856a6d6fe48b5aed8f8b238708`.`id` SEPARATOR '$$$$') AS ITEM_4,
    GROUP_CONCAT(DISTINCT CONCAT(`glpi_tickets_users_74690f2626744a37ace4c70dd87cea83`.`users_id`, ' ',
                                                       `glpi_tickets_users_74690f2626744a37ace4c70dd87cea83`.`alternative_email`)
                                                       SEPARATOR '$$$$') AS ITEM_4_2,
    GROUP_CONCAT(DISTINCT `glpi_users_647c2805c3795643b0f52f520e7cdb86`.`id` SEPARATOR '$$$$')
                                      AS ITEM_5,
    GROUP_CONCAT(DISTINCT CONCAT(`glpi_tickets_users_a900a61824c3906cc82f90407e525192`.`users_id`, ' ',
                                                        `glpi_tickets_users_a900a61824c3906cc82f90407e525192`.`alternative_email`)
                                                        SEPARATOR '$$$$') AS ITEM_5_2,
    `glpi_tickets`.`date` AS ITEM_6,
    `glpi_tickets`.`closedate` AS ITEM_7,
    `glpi_tickets`.`status` AS ITEM_8,
    `glpi_tickets`.`date_mod` AS ITEM_9,
    `glpi_tickets`.`id` AS id  

FROM `glpi_tickets`

LEFT JOIN `glpi_entities` 
    ON (`glpi_tickets`.`entities_id` = `glpi_entities`.`id`)

LEFT JOIN `glpi_itilcategories` 
    ON (`glpi_tickets`.`itilcategories_id` = `glpi_itilcategories`.`id`) 

LEFT JOIN `glpi_tickets_users`  AS glpi_tickets_users_74690f2626744a37ace4c70dd87cea83
    ON (`glpi_tickets`.`id` = `glpi_tickets_users_74690f2626744a37ace4c70dd87cea83`.`tickets_id` AND `glpi_tickets_users_74690f2626744a37ace4c70dd87cea83`.`type`= 2 )

LEFT JOIN `glpi_users`  AS glpi_users_c5e682856a6d6fe48b5aed8f8b238708
    ON (`glpi_tickets_users_74690f2626744a37ace4c70dd87cea83`.`users_id` = `glpi_users_c5e682856a6d6fe48b5aed8f8b238708`.`id`) 

LEFT JOIN `glpi_tickets_users`  AS glpi_tickets_users_a900a61824c3906cc82f90407e525192
    ON (`glpi_tickets`.`id` = `glpi_tickets_users_a900a61824c3906cc82f90407e525192`.`tickets_id` AND `glpi_tickets_users_a900a61824c3906cc82f90407e525192`.`type`= 1 )

LEFT JOIN `glpi_users`  AS glpi_users_647c2805c3795643b0f52f520e7cdb86
    ON (`glpi_tickets_users_a900a61824c3906cc82f90407e525192`.`users_id` = `glpi_users_647c2805c3795643b0f52f520e7cdb86`.`id`)

LEFT JOIN `glpi_requesttypes` 
    ON (`glpi_tickets`.`requesttypes_id` = `glpi_requesttypes`.`id`)

LEFT JOIN `glpi_locations` 
    ON (`glpi_tickets`.`locations_id` = `glpi_locations`.`id`)

LEFT JOIN `glpi_users`  AS glpi_users_users_id_lastupdater
    ON (`glpi_tickets`.`users_id_lastupdater` = `glpi_users_users_id_lastupdater`.`id`) 

LEFT JOIN `glpi_groups_tickets`  AS glpi_groups_tickets_8fe4b8216083df6473c0597972c40717
    ON (`glpi_tickets`.`id` = `glpi_groups_tickets_8fe4b8216083df6473c0597972c40717`.`tickets_id`AND `glpi_groups_tickets_8fe4b8216083df6473c0597972c40717`.`type`= 1 )

LEFT JOIN `glpi_groups`  AS glpi_groups_77277850fac99c62dbc26a804f22f50c
    ON (`glpi_groups_tickets_8fe4b8216083df6473c0597972c40717`.`groups_id` = `glpi_groups_77277850fac99c62dbc26a804f22f50c`.`id`)

LEFT JOIN `glpi_users`  AS glpi_users_users_id_recipient
    ON (`glpi_tickets`.`users_id_recipient` = `glpi_users_users_id_recipient`.`id`) 

LEFT JOIN `glpi_tickets_users`  AS glpi_tickets_users_9201eed6268ba42a969f0541adcd73d9
    ON (`glpi_tickets`.`id` = `glpi_tickets_users_9201eed6268ba42a969f0541adcd73d9`.`tickets_id` AND `glpi_tickets_users_9201eed6268ba42a969f0541adcd73d9`.`type`= 3 )

LEFT JOIN `glpi_users`  AS glpi_users_a1c9cdee57b96c1259435fb94656bf07
    ON (`glpi_tickets_users_9201eed6268ba42a969f0541adcd73d9`.`users_id` = `glpi_users_a1c9cdee57b96c1259435fb94656bf07`.`id`) 

LEFT JOIN `glpi_groups_tickets`  AS glpi_groups_tickets_9201eed6268ba42a969f0541adcd73d9
    ON (`glpi_tickets`.`id` = `glpi_groups_tickets_9201eed6268ba42a969f0541adcd73d9`.`tickets_id` AND `glpi_groups_tickets_9201eed6268ba42a969f0541adcd73d9`.`type` = 3 )

LEFT JOIN `glpi_groups`  AS glpi_groups_e2329ee8584cf5b8a84437506dd2ca66
    ON (`glpi_groups_tickets_9201eed6268ba42a969f0541adcd73d9`.`groups_id` = `glpi_groups_e2329ee8584cf5b8a84437506dd2ca66`.`id`) 

LEFT JOIN `glpi_suppliers_tickets`  AS glpi_suppliers_tickets_74690f2626744a37ace4c70dd87cea83
    ON (`glpi_tickets`.`id` = `glpi_suppliers_tickets_74690f2626744a37ace4c70dd87cea83`.`tickets_id` AND `glpi_suppliers_tickets_74690f2626744a37ace4c70dd87cea83`.`type`= 2 )

LEFT JOIN `glpi_suppliers`  AS glpi_suppliers_54b493b9140ea0c46d5e2442edf1b74e 
    ON (`glpi_suppliers_tickets_74690f2626744a37ace4c70dd87cea83`.`suppliers_id` = `glpi_suppliers_54b493b9140ea0c46d5e2442edf1b74e`.`id`) 

LEFT JOIN `glpi_groups_tickets`  AS glpi_groups_tickets_74690f2626744a37ace4c70dd87cea83 
    ON (`glpi_tickets`.`id` = `glpi_groups_tickets_74690f2626744a37ace4c70dd87cea83`.`tickets_id` AND `glpi_groups_tickets_74690f2626744a37ace4c70dd87cea83`.`type` = 2 )

LEFT JOIN `glpi_groups`  AS glpi_groups_80a94b3f6bc6957c5f1fd062f3013524 
    ON (`glpi_groups_tickets_74690f2626744a37ace4c70dd87cea83`.`groups_id` = `glpi_groups_80a94b3f6bc6957c5f1fd062f3013524`.`id`) 

LEFT JOIN `glpi_tickets_users`  AS glpi_tickets_users_c929cf9ec7348a7ddc947bee038ca5e8 
    ON (`glpi_tickets`.`id` = `glpi_tickets_users_c929cf9ec7348a7ddc947bee038ca5e8`.`tickets_id` AND `glpi_tickets_users_c929cf9ec7348a7ddc947bee038ca5e8`.`type`= 1 )

LEFT JOIN `glpi_slas` 
    ON (`glpi_tickets`.`slas_id` = `glpi_slas`.`id`)

LEFT JOIN `glpi_slalevels` 
    ON (`glpi_tickets`.`slalevels_id` = `glpi_slalevels`.`id`) 

LEFT JOIN `glpi_ticketvalidations` 
    ON (`glpi_tickets`.`id` = `glpi_ticketvalidations`.`tickets_id`)

LEFT JOIN `glpi_users`  AS glpi_users_57751ba960bd8511d2ad8a01bd8487f4
    ON (`glpi_ticketvalidations`.`users_id` = `glpi_users_57751ba960bd8511d2ad8a01bd8487f4`.`id`)

LEFT JOIN `glpi_users`  AS glpi_users_users_id_validate_57751ba960bd8511d2ad8a01bd8487f4
    ON (`glpi_ticketvalidations`.`users_id_validate` = `glpi_users_users_id_validate_57751ba960bd8511d2ad8a01bd8487f4`.`id`) 

LEFT JOIN `glpi_ticketsatisfactions` 
    ON (`glpi_tickets`.`id` = `glpi_ticketsatisfactions`.`tickets_id`) 

LEFT JOIN `glpi_ticketfollowups` 
    ON (`glpi_tickets`.`id` = `glpi_ticketfollowups`.`tickets_id`)

LEFT JOIN `glpi_requesttypes`  AS glpi_requesttypes_c38aefef0996a025032b9f8e81ceee2a
    ON (`glpi_ticketfollowups`.`requesttypes_id` = `glpi_requesttypes_c38aefef0996a025032b9f8e81ceee2a`.`id`)

LEFT JOIN `glpi_users`  AS glpi_users_c38aefef0996a025032b9f8e81ceee2a
    ON (`glpi_ticketfollowups`.`users_id` = `glpi_users_c38aefef0996a025032b9f8e81ceee2a`.`id`) 

LEFT JOIN `glpi_tickets_tickets` 
    ON ((`glpi_tickets`.`id` = `glpi_tickets_tickets`.`tickets_id_1`
        OR `glpi_tickets`.`id`= `glpi_tickets_tickets`.`tickets_id_2`)) 

LEFT JOIN `glpi_tickets`  AS glpi_tickets_c8682549752288b41bb384bcbe018c97
    ON ((`glpi_tickets_c8682549752288b41bb384bcbe018c97`.`id`= `glpi_tickets_tickets`.`tickets_id_1` 
        OR `glpi_tickets_c8682549752288b41bb384bcbe018c97`.`id`= `glpi_tickets_tickets`.`tickets_id_2`)
        AND `glpi_tickets_c8682549752288b41bb384bcbe018c97`.`id` <> `glpi_tickets`.`id` ) 

LEFT JOIN `glpi_tickets_tickets`  AS glpi_tickets_tickets_9b9f1a767329ed99d9c2a164b41de828
    ON ((`glpi_tickets`.`id`= `glpi_tickets_tickets_9b9f1a767329ed99d9c2a164b41de828`.`tickets_id_1`
        OR `glpi_tickets`.`id` = `glpi_tickets_tickets_9b9f1a767329ed99d9c2a164b41de828`.`tickets_id_2`)
        AND `glpi_tickets_tickets_9b9f1a767329ed99d9c2a164b41de828`.`link` = 2 ) 

LEFT JOIN `glpi_tickets`  AS glpi_tickets_1229cc66fadd719839b324249d9944e3 
    ON ((`glpi_tickets_1229cc66fadd719839b324249d9944e3`.`id` = `glpi_tickets_tickets_9b9f1a767329ed99d9c2a164b41de828`.`tickets_id_1`
        OR `glpi_tickets_1229cc66fadd719839b324249d9944e3`.`id`= `glpi_tickets_tickets_9b9f1a767329ed99d9c2a164b41de828`.`tickets_id_2`)
        AND `glpi_tickets_1229cc66fadd719839b324249d9944e3`.`id` <> `glpi_tickets`.`id` ) 

LEFT JOIN `glpi_tickettasks` 
    ON (`glpi_tickets`.`id` = `glpi_tickettasks`.`tickets_id`)

LEFT JOIN `glpi_taskcategories`  AS glpi_taskcategories_e5ca2f53018fdc28a31faf534186b3a2
    ON (`glpi_tickettasks`.`taskcategories_id` = `glpi_taskcategories_e5ca2f53018fdc28a31faf534186b3a2`.`id`)

LEFT JOIN `glpi_users`  AS glpi_users_e5ca2f53018fdc28a31faf534186b3a2
    ON (`glpi_tickettasks`.`users_id` = `glpi_users_e5ca2f53018fdc28a31faf534186b3a2`.`id`)

LEFT JOIN `glpi_users`  AS glpi_users_users_id_tech_e5ca2f53018fdc28a31faf534186b3a2
    ON (`glpi_tickettasks`.`users_id_tech` = `glpi_users_users_id_tech_e5ca2f53018fdc28a31faf534186b3a2`.`id`)

LEFT JOIN `glpi_solutiontypes` 
    ON (`glpi_tickets`.`solutiontypes_id` = `glpi_solutiontypes`.`id`)

LEFT JOIN `glpi_ticketcosts` 
    ON (`glpi_tickets`.`id` = `glpi_ticketcosts`.`tickets_id`) 

LEFT JOIN `glpi_problems_tickets` 
    ON (`glpi_tickets`.`id` = `glpi_problems_tickets`.`tickets_id`) 

LEFT JOIN `glpi_documents_items` 
    ON (`glpi_tickets`.`id` = `glpi_documents_items`.`items_id`
        AND `glpi_documents_items`.`itemtype` = 'Ticket')  


WHERE  
    `glpi_tickets`.`is_deleted` = '0'  
    AND  ( 
        `glpi_tickets`.`entities_id` IN ('0', '72')
    )  
    AND (
        (
            `glpi_tickets`.`name`  LIKE '%printaudit%'
            OR  `glpi_tickets`.`content`  LIKE '%printaudit%'    
            OR  `glpi_tickets`.`id`  LIKE '%printaudit%'   
            OR  `glpi_tickets`.`status` IN ('printaudit')  
            OR  `glpi_tickets`.`type`  LIKE '%printaudit%'    
            OR  `glpi_tickets`.`date`  LIKE '%printaudit%'    
            OR  `glpi_tickets`.`closedate`  LIKE '%printaudit%'    
            OR  `glpi_tickets`.`due_date`  LIKE '%printaudit%'    
            OR  IF(
                `glpi_tickets`.`due_date` IS NOT NULL
                AND (
                    `glpi_tickets`.`solvedate` > `glpi_tickets`.`due_date`
                    OR (
                        `glpi_tickets`.`solvedate` IS NULL
                        AND `glpi_tickets`.`due_date` < NOW()
                    )
                ),
            1, 0)    LIKE '%printaudit%'
            OR  `glpi_tickets`.`solvedate`  LIKE '%printaudit%'    
            OR  `glpi_tickets`.`date_mod`  LIKE '%printaudit%'
            OR  `glpi_itilcategories`.`completename`  LIKE '%printaudit%'   
            OR  `glpi_tickets`.`itemtype`  LIKE '%printaudit%'   
            OR  `glpi_requesttypes`.`name`  LIKE '%printaudit%'    
            OR  `glpi_locations`.`completename`  LIKE '%printaudit%'    
            OR  `glpi_entities`.`completename`  LIKE '%printaudit%'   
            OR  (
                (
                    (
                        `glpi_users_users_id_lastupdater`.`firstname`   LIKE '%printaudit%' 
                        OR `glpi_users_users_id_lastupdater`.`realname`   LIKE '%printaudit%' 
                        OR `glpi_users_users_id_lastupdater`.`name`   LIKE '%printaudit%' 
                        OR CONCAT(`glpi_users_users_id_lastupdater`.`firstname`, ' ', `glpi_users_users_id_lastupdater`.`realname`)   LIKE '%printaudit%'
                    )
                )
            )
            OR  (
                (
                    (
                        `glpi_users_647c2805c3795643b0f52f520e7cdb86`.`firstname`   LIKE '%printaudit%' 
                        OR `glpi_users_647c2805c3795643b0f52f520e7cdb86`.`realname`   LIKE '%printaudit%' 
                        OR `glpi_users_647c2805c3795643b0f52f520e7cdb86`.`name`   LIKE '%printaudit%' 
                        OR CONCAT(`glpi_users_647c2805c3795643b0f52f520e7cdb86`.`firstname`, ' ', `glpi_users_647c2805c3795643b0f52f520e7cdb86`.`realname`)   LIKE '%printaudit%'
                    )
                )
                OR `glpi_tickets_users_a900a61824c3906cc82f90407e525192`.`alternative_email`  LIKE '%printaudit%'
            )  
            OR  `glpi_groups_77277850fac99c62dbc26a804f22f50c`.`completename`  LIKE '%printaudit%'   
            OR  (
                (
                    (
                        `glpi_users_users_id_recipient`.`firstname`   LIKE '%printaudit%' 
                        OR `glpi_users_users_id_recipient`.`realname`   LIKE '%printaudit%' 
                        OR `glpi_users_users_id_recipient`.`name`   LIKE '%printaudit%' 
                        OR CONCAT(`glpi_users_users_id_recipient`.`firstname`, ' ', `glpi_users_users_id_recipient`.`realname`)   LIKE '%printaudit%'  
                    )
                )
            ) 
            OR  (
                (
                    (
                        `glpi_users_a1c9cdee57b96c1259435fb94656bf07`.`firstname`   LIKE '%printaudit%' 
                        OR `glpi_users_a1c9cdee57b96c1259435fb94656bf07`.`realname`   LIKE '%printaudit%' 
                        OR `glpi_users_a1c9cdee57b96c1259435fb94656bf07`.`name`   LIKE '%printaudit%' 
                        OR CONCAT(`glpi_users_a1c9cdee57b96c1259435fb94656bf07`.`firstname`, ' ', `glpi_users_a1c9cdee57b96c1259435fb94656bf07`.`realname`)   LIKE '%printaudit%'  
                    )
                )  
                OR `glpi_tickets_users_9201eed6268ba42a969f0541adcd73d9`.`alternative_email`  LIKE '%printaudit%'
            )  
            OR  `glpi_groups_e2329ee8584cf5b8a84437506dd2ca66`.`completename`  LIKE '%printaudit%'   
            OR  (
                (
                    (
                        `glpi_users_c5e682856a6d6fe48b5aed8f8b238708`.`firstname`   LIKE '%printaudit%' 
                        OR `glpi_users_c5e682856a6d6fe48b5aed8f8b238708`.`realname`   LIKE '%printaudit%' 
                        OR `glpi_users_c5e682856a6d6fe48b5aed8f8b238708`.`name`   LIKE '%printaudit%' 
                        OR CONCAT(`glpi_users_c5e682856a6d6fe48b5aed8f8b238708`.`firstname`, ' ', `glpi_users_c5e682856a6d6fe48b5aed8f8b238708`.`realname`)   LIKE '%printaudit%'  
                    )
                )
                OR `glpi_tickets_users_74690f2626744a37ace4c70dd87cea83`.`alternative_email`  LIKE '%printaudit%'
            )  
            OR  `glpi_suppliers_54b493b9140ea0c46d5e2442edf1b74e`.`name`  LIKE '%printaudit%'    
            OR  `glpi_groups_80a94b3f6bc6957c5f1fd062f3013524`.`completename`  LIKE '%printaudit%'    
            OR  `glpi_tickets_users_c929cf9ec7348a7ddc947bee038ca5e8`.`use_notification`  LIKE '%printaudit%'    
            OR  `glpi_tickets_users_c929cf9ec7348a7ddc947bee038ca5e8`.`alternative_email`  LIKE '%printaudit%'    
            OR  `glpi_slas`.`name`  LIKE '%printaudit%'    
            OR  `glpi_slalevels`.`name`  LIKE '%printaudit%'    
            OR  `glpi_tickets`.`global_validation`  LIKE '%printaudit%'    
            OR  `glpi_ticketvalidations`.`comment_submission`  LIKE '%printaudit%'    
            OR  `glpi_ticketvalidations`.`comment_validation`  LIKE '%printaudit%'    
            OR  `glpi_ticketvalidations`.`status`  LIKE '%printaudit%'    
            OR  `glpi_ticketvalidations`.`submission_date`  LIKE '%printaudit%'    
            OR  `glpi_ticketvalidations`.`validation_date`  LIKE '%printaudit%'   
            OR  (
                (
                    (
                        `glpi_users_57751ba960bd8511d2ad8a01bd8487f4`.`firstname`   LIKE '%printaudit%' 
                        OR `glpi_users_57751ba960bd8511d2ad8a01bd8487f4`.`realname`   LIKE '%printaudit%' 
                        OR `glpi_users_57751ba960bd8511d2ad8a01bd8487f4`.`name`   LIKE '%printaudit%' 
                        OR CONCAT(`glpi_users_57751ba960bd8511d2ad8a01bd8487f4`.`firstname`, ' ', `glpi_users_57751ba960bd8511d2ad8a01bd8487f4`.`realname`) LIKE '%printaudit%'  
                    )
                )
            ) 
            OR  (
                (
                    (
                        `glpi_users_users_id_validate_57751ba960bd8511d2ad8a01bd8487f4`.`firstname`   LIKE '%printaudit%' 
                        OR `glpi_users_users_id_validate_57751ba960bd8511d2ad8a01bd8487f4`.`realname`   LIKE '%printaudit%' 
                        OR `glpi_users_users_id_validate_57751ba960bd8511d2ad8a01bd8487f4`.`name`   LIKE '%printaudit%' 
                        OR CONCAT(`glpi_users_users_id_validate_57751ba960bd8511d2ad8a01bd8487f4`.`firstname`, ' ', `glpi_users_users_id_validate_57751ba960bd8511d2ad8a01bd8487f4`.`realname`)   LIKE '%printaudit%'  
                    )
                )
            ) 
            OR  `glpi_ticketsatisfactions`.`type` = 'printaudit'   
            OR  `glpi_ticketsatisfactions`.`date_begin`  LIKE '%printaudit%'    
            OR  `glpi_ticketsatisfactions`.`date_answered`  LIKE '%printaudit%'    
            OR  `glpi_ticketsatisfactions`.`satisfaction`  LIKE '%printaudit%'    
            OR  `glpi_ticketsatisfactions`.`comment`  LIKE '%printaudit%'    
            OR  `glpi_ticketfollowups`.`content`  LIKE '%printaudit%'    
            OR  `glpi_requesttypes_c38aefef0996a025032b9f8e81ceee2a`.`name`  LIKE '%printaudit%'    
            OR  `glpi_ticketfollowups`.`is_private`  LIKE '%printaudit%'   
            OR  (
                (
                    (
                        `glpi_users_c38aefef0996a025032b9f8e81ceee2a`.`firstname`   LIKE '%printaudit%' 
                        OR `glpi_users_c38aefef0996a025032b9f8e81ceee2a`.`realname`   LIKE '%printaudit%' 
                        OR `glpi_users_c38aefef0996a025032b9f8e81ceee2a`.`name`   LIKE '%printaudit%' 
                        OR CONCAT(`glpi_users_c38aefef0996a025032b9f8e81ceee2a`.`firstname`, ' ', `glpi_users_c38aefef0996a025032b9f8e81ceee2a`.`realname`)   LIKE '%printaudit%'  
                    )
                )
            )
            OR  `glpi_tickets`.`close_delay_stat`  LIKE '%printaudit%'    
            OR  `glpi_tickets`.`waiting_duration`  LIKE '%printaudit%'    
            OR  `glpi_tickets`.`takeintoaccount_delay_stat`  LIKE '%printaudit%'    
            OR  `glpi_tickets_c8682549752288b41bb384bcbe018c97`.`name`  LIKE '%printaudit%'    
            OR  `glpi_tickets_1229cc66fadd719839b324249d9944e3`.`name`  LIKE '%printaudit%'    
            OR  `glpi_tickettasks`.`content`  LIKE '%printaudit%'    
            OR  `glpi_taskcategories_e5ca2f53018fdc28a31faf534186b3a2`.`name`  LIKE '%printaudit%'    
            OR  `glpi_tickettasks`.`is_private`  LIKE '%printaudit%'   
            OR  (
                (
                    (
                        `glpi_users_e5ca2f53018fdc28a31faf534186b3a2`.`firstname`   LIKE '%printaudit%' 
                        OR `glpi_users_e5ca2f53018fdc28a31faf534186b3a2`.`realname`   LIKE '%printaudit%' 
                        OR `glpi_users_e5ca2f53018fdc28a31faf534186b3a2`.`name`   LIKE '%printaudit%' 
                        OR CONCAT(`glpi_users_e5ca2f53018fdc28a31faf534186b3a2`.`firstname`, ' ', `glpi_users_e5ca2f53018fdc28a31faf534186b3a2`.`realname`)   LIKE '%printaudit%'  
                    )
                )
            )
            OR  (
                (
                    (
                        `glpi_users_users_id_tech_e5ca2f53018fdc28a31faf534186b3a2`.`firstname`   LIKE '%printaudit%' 
                        OR `glpi_users_users_id_tech_e5ca2f53018fdc28a31faf534186b3a2`.`realname`   LIKE '%printaudit%' 
                        OR `glpi_users_users_id_tech_e5ca2f53018fdc28a31faf534186b3a2`.`name`   LIKE '%printaudit%' 
                        OR CONCAT(`glpi_users_users_id_tech_e5ca2f53018fdc28a31faf534186b3a2`.`firstname`, ' ', `glpi_users_users_id_tech_e5ca2f53018fdc28a31faf534186b3a2`.`realname`)   LIKE '%printaudit%'  
                    )
                )
            )
            OR  `glpi_tickettasks`.`actiontime`  LIKE '%printaudit%'    
            OR  `glpi_tickettasks`.`date`  LIKE '%printaudit%'    
            OR  `glpi_tickettasks`.`state`  LIKE '%printaudit%'    
            OR  `glpi_solutiontypes`.`name`  LIKE '%printaudit%'    
            OR  `glpi_tickets`.`solution`  LIKE '%printaudit%'    
            OR  `glpi_ticketcosts`.`cost_time`  LIKE '%printaudit%'    
            OR  `glpi_ticketcosts`.`actiontime`  LIKE '%printaudit%'    
            OR  `glpi_ticketcosts`.`cost_fixed`  LIKE '%printaudit%'    
            OR  `glpi_ticketcosts`.`cost_material`  LIKE '%printaudit%'   
        )
    )
GROUP BY `glpi_tickets`.`id` 
ORDER BY ITEM_9 DESC

Offline

#4 2013-09-20 04:51:38

antoniom
Member
From: Belo Horizonte, MG, Brasil
Registered: 2012-10-10
Posts: 9

Re: Lost connection with database when searching by all fields of tickets

Hi,

After performing some tests I came to some conclusions:

1. The problem does not occur in a database with few tickets;

2. A search for "All" is not working in my production database that has about 30,000 tickets, some with documents, tasks and follow-ups. The query is running for several minutes, and while that the database is locked for running queries;

3. The problem of performance in the execution of the query is related to two table joins, to which post the SQL code below. I removed these joins the query and did a test running it. Without joins the query worked.

First JOIN:

 LEFT JOIN `glpi_tickets`  AS glpi_tickets_c8682549752288b41bb384bcbe018c97
    ON ((`glpi_tickets_c8682549752288b41bb384bcbe018c97`.`id`= `glpi_tickets_tickets`.`tickets_id_1` 
        OR `glpi_tickets_c8682549752288b41bb384bcbe018c97`.`id`= `glpi_tickets_tickets`.`tickets_id_2`)
        AND `glpi_tickets_c8682549752288b41bb384bcbe018c97`.`id` <> `glpi_tickets`.`id` )

Second JOIN:

 LEFT JOIN `glpi_tickets`  AS glpi_tickets_1229cc66fadd719839b324249d9944e3 
    ON ((`glpi_tickets_1229cc66fadd719839b324249d9944e3`.`id` = `glpi_tickets_tickets_9b9f1a767329ed99d9c2a164b41de828`.`tickets_id_1`
        OR `glpi_tickets_1229cc66fadd719839b324249d9944e3`.`id`= `glpi_tickets_tickets_9b9f1a767329ed99d9c2a164b41de828`.`tickets_id_2`)
        AND `glpi_tickets_1229cc66fadd719839b324249d9944e3`.`id` <> `glpi_tickets`.`id` )



That was the query that ran successfully (with JOINs commented):

SELECT 
    'antoniom' AS currentuser,
    `glpi_tickets`.`id` AS ITEM_0,
    `glpi_tickets`.`name` AS ITEM_1,
    `glpi_tickets`.`id` AS ITEM_1_2,
    `glpi_tickets`.`content` AS ITEM_1_3,
    `glpi_tickets`.`status` AS ITEM_1_4,
    `glpi_entities`.`completename` AS ITEM_2,
    `glpi_itilcategories`.`completename` AS ITEM_3,
    GROUP_CONCAT(DISTINCT `glpi_users_c5e682856a6d6fe48b5aed8f8b238708`.`id` SEPARATOR '$$$$') AS ITEM_4,
    GROUP_CONCAT(DISTINCT CONCAT(`glpi_tickets_users_74690f2626744a37ace4c70dd87cea83`.`users_id`, ' ',
                                                       `glpi_tickets_users_74690f2626744a37ace4c70dd87cea83`.`alternative_email`)
                                                       SEPARATOR '$$$$') AS ITEM_4_2,
    GROUP_CONCAT(DISTINCT `glpi_users_647c2805c3795643b0f52f520e7cdb86`.`id` SEPARATOR '$$$$')
                                      AS ITEM_5,
    GROUP_CONCAT(DISTINCT CONCAT(`glpi_tickets_users_a900a61824c3906cc82f90407e525192`.`users_id`, ' ',
                                                        `glpi_tickets_users_a900a61824c3906cc82f90407e525192`.`alternative_email`)
                                                        SEPARATOR '$$$$') AS ITEM_5_2,
    `glpi_tickets`.`date` AS ITEM_6,
    `glpi_tickets`.`closedate` AS ITEM_7,
    `glpi_tickets`.`status` AS ITEM_8,
    `glpi_tickets`.`date_mod` AS ITEM_9,
    `glpi_tickets`.`id` AS id  

FROM `glpi_tickets`

LEFT JOIN `glpi_entities` 
    ON (`glpi_tickets`.`entities_id` = `glpi_entities`.`id`)

LEFT JOIN `glpi_itilcategories` 
    ON (`glpi_tickets`.`itilcategories_id` = `glpi_itilcategories`.`id`) 

LEFT JOIN `glpi_tickets_users`  AS glpi_tickets_users_74690f2626744a37ace4c70dd87cea83
    ON (`glpi_tickets`.`id` = `glpi_tickets_users_74690f2626744a37ace4c70dd87cea83`.`tickets_id` AND `glpi_tickets_users_74690f2626744a37ace4c70dd87cea83`.`type`= 2 )

LEFT JOIN `glpi_users`  AS glpi_users_c5e682856a6d6fe48b5aed8f8b238708
    ON (`glpi_tickets_users_74690f2626744a37ace4c70dd87cea83`.`users_id` = `glpi_users_c5e682856a6d6fe48b5aed8f8b238708`.`id`) 

LEFT JOIN `glpi_tickets_users`  AS glpi_tickets_users_a900a61824c3906cc82f90407e525192
    ON (`glpi_tickets`.`id` = `glpi_tickets_users_a900a61824c3906cc82f90407e525192`.`tickets_id` AND `glpi_tickets_users_a900a61824c3906cc82f90407e525192`.`type`= 1 )

LEFT JOIN `glpi_users`  AS glpi_users_647c2805c3795643b0f52f520e7cdb86
    ON (`glpi_tickets_users_a900a61824c3906cc82f90407e525192`.`users_id` = `glpi_users_647c2805c3795643b0f52f520e7cdb86`.`id`)

LEFT JOIN `glpi_requesttypes` 
    ON (`glpi_tickets`.`requesttypes_id` = `glpi_requesttypes`.`id`)

LEFT JOIN `glpi_locations` 
    ON (`glpi_tickets`.`locations_id` = `glpi_locations`.`id`)

LEFT JOIN `glpi_users`  AS glpi_users_users_id_lastupdater
    ON (`glpi_tickets`.`users_id_lastupdater` = `glpi_users_users_id_lastupdater`.`id`) 

LEFT JOIN `glpi_groups_tickets`  AS glpi_groups_tickets_8fe4b8216083df6473c0597972c40717
    ON (`glpi_tickets`.`id` = `glpi_groups_tickets_8fe4b8216083df6473c0597972c40717`.`tickets_id`AND `glpi_groups_tickets_8fe4b8216083df6473c0597972c40717`.`type`= 1 )

LEFT JOIN `glpi_groups`  AS glpi_groups_77277850fac99c62dbc26a804f22f50c
    ON (`glpi_groups_tickets_8fe4b8216083df6473c0597972c40717`.`groups_id` = `glpi_groups_77277850fac99c62dbc26a804f22f50c`.`id`)

LEFT JOIN `glpi_users`  AS glpi_users_users_id_recipient
    ON (`glpi_tickets`.`users_id_recipient` = `glpi_users_users_id_recipient`.`id`) 

LEFT JOIN `glpi_tickets_users`  AS glpi_tickets_users_9201eed6268ba42a969f0541adcd73d9
    ON (`glpi_tickets`.`id` = `glpi_tickets_users_9201eed6268ba42a969f0541adcd73d9`.`tickets_id` AND `glpi_tickets_users_9201eed6268ba42a969f0541adcd73d9`.`type`= 3 )

LEFT JOIN `glpi_users`  AS glpi_users_a1c9cdee57b96c1259435fb94656bf07
    ON (`glpi_tickets_users_9201eed6268ba42a969f0541adcd73d9`.`users_id` = `glpi_users_a1c9cdee57b96c1259435fb94656bf07`.`id`) 

LEFT JOIN `glpi_groups_tickets`  AS glpi_groups_tickets_9201eed6268ba42a969f0541adcd73d9
    ON (`glpi_tickets`.`id` = `glpi_groups_tickets_9201eed6268ba42a969f0541adcd73d9`.`tickets_id` AND `glpi_groups_tickets_9201eed6268ba42a969f0541adcd73d9`.`type` = 3 )

LEFT JOIN `glpi_groups`  AS glpi_groups_e2329ee8584cf5b8a84437506dd2ca66
    ON (`glpi_groups_tickets_9201eed6268ba42a969f0541adcd73d9`.`groups_id` = `glpi_groups_e2329ee8584cf5b8a84437506dd2ca66`.`id`) 

LEFT JOIN `glpi_suppliers_tickets`  AS glpi_suppliers_tickets_74690f2626744a37ace4c70dd87cea83
    ON (`glpi_tickets`.`id` = `glpi_suppliers_tickets_74690f2626744a37ace4c70dd87cea83`.`tickets_id` AND `glpi_suppliers_tickets_74690f2626744a37ace4c70dd87cea83`.`type`= 2 )

LEFT JOIN `glpi_suppliers`  AS glpi_suppliers_54b493b9140ea0c46d5e2442edf1b74e 
    ON (`glpi_suppliers_tickets_74690f2626744a37ace4c70dd87cea83`.`suppliers_id` = `glpi_suppliers_54b493b9140ea0c46d5e2442edf1b74e`.`id`) 

LEFT JOIN `glpi_groups_tickets`  AS glpi_groups_tickets_74690f2626744a37ace4c70dd87cea83 
    ON (`glpi_tickets`.`id` = `glpi_groups_tickets_74690f2626744a37ace4c70dd87cea83`.`tickets_id` AND `glpi_groups_tickets_74690f2626744a37ace4c70dd87cea83`.`type` = 2 )

LEFT JOIN `glpi_groups`  AS glpi_groups_80a94b3f6bc6957c5f1fd062f3013524 
    ON (`glpi_groups_tickets_74690f2626744a37ace4c70dd87cea83`.`groups_id` = `glpi_groups_80a94b3f6bc6957c5f1fd062f3013524`.`id`) 

LEFT JOIN `glpi_tickets_users`  AS glpi_tickets_users_c929cf9ec7348a7ddc947bee038ca5e8 
    ON (`glpi_tickets`.`id` = `glpi_tickets_users_c929cf9ec7348a7ddc947bee038ca5e8`.`tickets_id` AND `glpi_tickets_users_c929cf9ec7348a7ddc947bee038ca5e8`.`type`= 1 )

LEFT JOIN `glpi_slas` 
    ON (`glpi_tickets`.`slas_id` = `glpi_slas`.`id`)

LEFT JOIN `glpi_slalevels` 
    ON (`glpi_tickets`.`slalevels_id` = `glpi_slalevels`.`id`) 

LEFT JOIN `glpi_ticketvalidations` 
    ON (`glpi_tickets`.`id` = `glpi_ticketvalidations`.`tickets_id`)

LEFT JOIN `glpi_users`  AS glpi_users_57751ba960bd8511d2ad8a01bd8487f4
    ON (`glpi_ticketvalidations`.`users_id` = `glpi_users_57751ba960bd8511d2ad8a01bd8487f4`.`id`)

LEFT JOIN `glpi_users`  AS glpi_users_users_id_validate_57751ba960bd8511d2ad8a01bd8487f4
    ON (`glpi_ticketvalidations`.`users_id_validate` = `glpi_users_users_id_validate_57751ba960bd8511d2ad8a01bd8487f4`.`id`) 

LEFT JOIN `glpi_ticketsatisfactions` 
    ON (`glpi_tickets`.`id` = `glpi_ticketsatisfactions`.`tickets_id`) 

LEFT JOIN `glpi_ticketfollowups` 
    ON (`glpi_tickets`.`id` = `glpi_ticketfollowups`.`tickets_id`)

LEFT JOIN `glpi_requesttypes`  AS glpi_requesttypes_c38aefef0996a025032b9f8e81ceee2a
    ON (`glpi_ticketfollowups`.`requesttypes_id` = `glpi_requesttypes_c38aefef0996a025032b9f8e81ceee2a`.`id`)

LEFT JOIN `glpi_users`  AS glpi_users_c38aefef0996a025032b9f8e81ceee2a
    ON (`glpi_ticketfollowups`.`users_id` = `glpi_users_c38aefef0996a025032b9f8e81ceee2a`.`id`) 

LEFT JOIN `glpi_tickets_tickets` 
    ON ((`glpi_tickets`.`id` = `glpi_tickets_tickets`.`tickets_id_1`
        OR `glpi_tickets`.`id`= `glpi_tickets_tickets`.`tickets_id_2`)) 

-- LEFT JOIN `glpi_tickets`  AS glpi_tickets_c8682549752288b41bb384bcbe018c97
--    ON ((`glpi_tickets_c8682549752288b41bb384bcbe018c97`.`id`= `glpi_tickets_tickets`.`tickets_id_1` 
--        OR `glpi_tickets_c8682549752288b41bb384bcbe018c97`.`id`= `glpi_tickets_tickets`.`tickets_id_2`)
--        AND `glpi_tickets_c8682549752288b41bb384bcbe018c97`.`id` <> `glpi_tickets`.`id` ) 

LEFT JOIN `glpi_tickets_tickets`  AS glpi_tickets_tickets_9b9f1a767329ed99d9c2a164b41de828
    ON ((`glpi_tickets`.`id`= `glpi_tickets_tickets_9b9f1a767329ed99d9c2a164b41de828`.`tickets_id_1`
        OR `glpi_tickets`.`id` = `glpi_tickets_tickets_9b9f1a767329ed99d9c2a164b41de828`.`tickets_id_2`)
        AND `glpi_tickets_tickets_9b9f1a767329ed99d9c2a164b41de828`.`link` = 2 ) 

-- LEFT JOIN `glpi_tickets`  AS glpi_tickets_1229cc66fadd719839b324249d9944e3 
--    ON ((`glpi_tickets_1229cc66fadd719839b324249d9944e3`.`id` = `glpi_tickets_tickets_9b9f1a767329ed99d9c2a164b41de828`.`tickets_id_1`
--        OR `glpi_tickets_1229cc66fadd719839b324249d9944e3`.`id`= `glpi_tickets_tickets_9b9f1a767329ed99d9c2a164b41de828`.`tickets_id_2`)
--        AND `glpi_tickets_1229cc66fadd719839b324249d9944e3`.`id` <> `glpi_tickets`.`id` ) 

LEFT JOIN `glpi_tickettasks` 
    ON (`glpi_tickets`.`id` = `glpi_tickettasks`.`tickets_id`)

LEFT JOIN `glpi_taskcategories`  AS glpi_taskcategories_e5ca2f53018fdc28a31faf534186b3a2
    ON (`glpi_tickettasks`.`taskcategories_id` = `glpi_taskcategories_e5ca2f53018fdc28a31faf534186b3a2`.`id`)

LEFT JOIN `glpi_users`  AS glpi_users_e5ca2f53018fdc28a31faf534186b3a2
    ON (`glpi_tickettasks`.`users_id` = `glpi_users_e5ca2f53018fdc28a31faf534186b3a2`.`id`)

LEFT JOIN `glpi_users`  AS glpi_users_users_id_tech_e5ca2f53018fdc28a31faf534186b3a2
    ON (`glpi_tickettasks`.`users_id_tech` = `glpi_users_users_id_tech_e5ca2f53018fdc28a31faf534186b3a2`.`id`)

LEFT JOIN `glpi_solutiontypes` 
    ON (`glpi_tickets`.`solutiontypes_id` = `glpi_solutiontypes`.`id`)

LEFT JOIN `glpi_ticketcosts` 
    ON (`glpi_tickets`.`id` = `glpi_ticketcosts`.`tickets_id`) 

LEFT JOIN `glpi_problems_tickets` 
    ON (`glpi_tickets`.`id` = `glpi_problems_tickets`.`tickets_id`) 

LEFT JOIN `glpi_documents_items` 
    ON (`glpi_tickets`.`id` = `glpi_documents_items`.`items_id`
        AND `glpi_documents_items`.`itemtype` = 'Ticket')  


WHERE  
    `glpi_tickets`.`is_deleted` = '0'  
    AND  ( 
        `glpi_tickets`.`entities_id` IN ('0', '72')
    )  
    AND (
        (
            `glpi_tickets`.`name`  LIKE '%printaudit%'
            OR  `glpi_tickets`.`content`  LIKE '%printaudit%'    
            OR  `glpi_tickets`.`id`  LIKE '%printaudit%'   
            OR  `glpi_tickets`.`status` IN ('printaudit')  
            OR  `glpi_tickets`.`type`  LIKE '%printaudit%'    
            OR  `glpi_tickets`.`date`  LIKE '%printaudit%'    
            OR  `glpi_tickets`.`closedate`  LIKE '%printaudit%'    
            OR  `glpi_tickets`.`due_date`  LIKE '%printaudit%'    
            OR  IF(
                `glpi_tickets`.`due_date` IS NOT NULL
                AND (
                    `glpi_tickets`.`solvedate` > `glpi_tickets`.`due_date`
                    OR (
                        `glpi_tickets`.`solvedate` IS NULL
                        AND `glpi_tickets`.`due_date` < NOW()
                    )
                ),
            1, 0)    LIKE '%printaudit%'
            OR  `glpi_tickets`.`solvedate`  LIKE '%printaudit%'    
            OR  `glpi_tickets`.`date_mod`  LIKE '%printaudit%'
            OR  `glpi_itilcategories`.`completename`  LIKE '%printaudit%'   
            OR  `glpi_tickets`.`itemtype`  LIKE '%printaudit%'   
            OR  `glpi_requesttypes`.`name`  LIKE '%printaudit%'    
            OR  `glpi_locations`.`completename`  LIKE '%printaudit%'    
            OR  `glpi_entities`.`completename`  LIKE '%printaudit%'   
            OR  (
                (
                    (
                        `glpi_users_users_id_lastupdater`.`firstname`   LIKE '%printaudit%' 
                        OR `glpi_users_users_id_lastupdater`.`realname`   LIKE '%printaudit%' 
                        OR `glpi_users_users_id_lastupdater`.`name`   LIKE '%printaudit%' 
                        OR CONCAT(`glpi_users_users_id_lastupdater`.`firstname`, ' ', `glpi_users_users_id_lastupdater`.`realname`)   LIKE '%printaudit%'
                    )
                )
            )
            OR  (
                (
                    (
                        `glpi_users_647c2805c3795643b0f52f520e7cdb86`.`firstname`   LIKE '%printaudit%' 
                        OR `glpi_users_647c2805c3795643b0f52f520e7cdb86`.`realname`   LIKE '%printaudit%' 
                        OR `glpi_users_647c2805c3795643b0f52f520e7cdb86`.`name`   LIKE '%printaudit%' 
                        OR CONCAT(`glpi_users_647c2805c3795643b0f52f520e7cdb86`.`firstname`, ' ', `glpi_users_647c2805c3795643b0f52f520e7cdb86`.`realname`)   LIKE '%printaudit%'
                    )
                )
                OR `glpi_tickets_users_a900a61824c3906cc82f90407e525192`.`alternative_email`  LIKE '%printaudit%'
            )  
            OR  `glpi_groups_77277850fac99c62dbc26a804f22f50c`.`completename`  LIKE '%printaudit%'   
            OR  (
                (
                    (
                        `glpi_users_users_id_recipient`.`firstname`   LIKE '%printaudit%' 
                        OR `glpi_users_users_id_recipient`.`realname`   LIKE '%printaudit%' 
                        OR `glpi_users_users_id_recipient`.`name`   LIKE '%printaudit%' 
                        OR CONCAT(`glpi_users_users_id_recipient`.`firstname`, ' ', `glpi_users_users_id_recipient`.`realname`)   LIKE '%printaudit%'  
                    )
                )
            ) 
            OR  (
                (
                    (
                        `glpi_users_a1c9cdee57b96c1259435fb94656bf07`.`firstname`   LIKE '%printaudit%' 
                        OR `glpi_users_a1c9cdee57b96c1259435fb94656bf07`.`realname`   LIKE '%printaudit%' 
                        OR `glpi_users_a1c9cdee57b96c1259435fb94656bf07`.`name`   LIKE '%printaudit%' 
                        OR CONCAT(`glpi_users_a1c9cdee57b96c1259435fb94656bf07`.`firstname`, ' ', `glpi_users_a1c9cdee57b96c1259435fb94656bf07`.`realname`)   LIKE '%printaudit%'  
                    )
                )  
                OR `glpi_tickets_users_9201eed6268ba42a969f0541adcd73d9`.`alternative_email`  LIKE '%printaudit%'
            )  
            OR  `glpi_groups_e2329ee8584cf5b8a84437506dd2ca66`.`completename`  LIKE '%printaudit%'   
            OR  (
                (
                    (
                        `glpi_users_c5e682856a6d6fe48b5aed8f8b238708`.`firstname`   LIKE '%printaudit%' 
                        OR `glpi_users_c5e682856a6d6fe48b5aed8f8b238708`.`realname`   LIKE '%printaudit%' 
                        OR `glpi_users_c5e682856a6d6fe48b5aed8f8b238708`.`name`   LIKE '%printaudit%' 
                        OR CONCAT(`glpi_users_c5e682856a6d6fe48b5aed8f8b238708`.`firstname`, ' ', `glpi_users_c5e682856a6d6fe48b5aed8f8b238708`.`realname`)   LIKE '%printaudit%'  
                    )
                )
                OR `glpi_tickets_users_74690f2626744a37ace4c70dd87cea83`.`alternative_email`  LIKE '%printaudit%'
            )  
            OR  `glpi_suppliers_54b493b9140ea0c46d5e2442edf1b74e`.`name`  LIKE '%printaudit%'    
            OR  `glpi_groups_80a94b3f6bc6957c5f1fd062f3013524`.`completename`  LIKE '%printaudit%'    
            OR  `glpi_tickets_users_c929cf9ec7348a7ddc947bee038ca5e8`.`use_notification`  LIKE '%printaudit%'    
            OR  `glpi_tickets_users_c929cf9ec7348a7ddc947bee038ca5e8`.`alternative_email`  LIKE '%printaudit%'    
            OR  `glpi_slas`.`name`  LIKE '%printaudit%'    
            OR  `glpi_slalevels`.`name`  LIKE '%printaudit%'    
            OR  `glpi_tickets`.`global_validation`  LIKE '%printaudit%'    
            OR  `glpi_ticketvalidations`.`comment_submission`  LIKE '%printaudit%'    
            OR  `glpi_ticketvalidations`.`comment_validation`  LIKE '%printaudit%'    
            OR  `glpi_ticketvalidations`.`status`  LIKE '%printaudit%'    
            OR  `glpi_ticketvalidations`.`submission_date`  LIKE '%printaudit%'    
            OR  `glpi_ticketvalidations`.`validation_date`  LIKE '%printaudit%'   
            OR  (
                (
                    (
                        `glpi_users_57751ba960bd8511d2ad8a01bd8487f4`.`firstname`   LIKE '%printaudit%' 
                        OR `glpi_users_57751ba960bd8511d2ad8a01bd8487f4`.`realname`   LIKE '%printaudit%' 
                        OR `glpi_users_57751ba960bd8511d2ad8a01bd8487f4`.`name`   LIKE '%printaudit%' 
                        OR CONCAT(`glpi_users_57751ba960bd8511d2ad8a01bd8487f4`.`firstname`, ' ', `glpi_users_57751ba960bd8511d2ad8a01bd8487f4`.`realname`) LIKE '%printaudit%'  
                    )
                )
            ) 
            OR  (
                (
                    (
                        `glpi_users_users_id_validate_57751ba960bd8511d2ad8a01bd8487f4`.`firstname`   LIKE '%printaudit%' 
                        OR `glpi_users_users_id_validate_57751ba960bd8511d2ad8a01bd8487f4`.`realname`   LIKE '%printaudit%' 
                        OR `glpi_users_users_id_validate_57751ba960bd8511d2ad8a01bd8487f4`.`name`   LIKE '%printaudit%' 
                        OR CONCAT(`glpi_users_users_id_validate_57751ba960bd8511d2ad8a01bd8487f4`.`firstname`, ' ', `glpi_users_users_id_validate_57751ba960bd8511d2ad8a01bd8487f4`.`realname`)   LIKE '%printaudit%'  
                    )
                )
            ) 
            OR  `glpi_ticketsatisfactions`.`type` = 'printaudit'   
            OR  `glpi_ticketsatisfactions`.`date_begin`  LIKE '%printaudit%'    
            OR  `glpi_ticketsatisfactions`.`date_answered`  LIKE '%printaudit%'    
            OR  `glpi_ticketsatisfactions`.`satisfaction`  LIKE '%printaudit%'    
            OR  `glpi_ticketsatisfactions`.`comment`  LIKE '%printaudit%'    
            OR  `glpi_ticketfollowups`.`content`  LIKE '%printaudit%'    
            OR  `glpi_requesttypes_c38aefef0996a025032b9f8e81ceee2a`.`name`  LIKE '%printaudit%'    
            OR  `glpi_ticketfollowups`.`is_private`  LIKE '%printaudit%'   
            OR  (
                (
                    (
                        `glpi_users_c38aefef0996a025032b9f8e81ceee2a`.`firstname`   LIKE '%printaudit%' 
                        OR `glpi_users_c38aefef0996a025032b9f8e81ceee2a`.`realname`   LIKE '%printaudit%' 
                        OR `glpi_users_c38aefef0996a025032b9f8e81ceee2a`.`name`   LIKE '%printaudit%' 
                        OR CONCAT(`glpi_users_c38aefef0996a025032b9f8e81ceee2a`.`firstname`, ' ', `glpi_users_c38aefef0996a025032b9f8e81ceee2a`.`realname`)   LIKE '%printaudit%'  
                    )
                )
            )
            OR  `glpi_tickets`.`close_delay_stat`  LIKE '%printaudit%'    
            OR  `glpi_tickets`.`waiting_duration`  LIKE '%printaudit%'    
            OR  `glpi_tickets`.`takeintoaccount_delay_stat`  LIKE '%printaudit%'    
--            OR  `glpi_tickets_c8682549752288b41bb384bcbe018c97`.`name`  LIKE '%printaudit%'    
--            OR  `glpi_tickets_1229cc66fadd719839b324249d9944e3`.`name`  LIKE '%printaudit%'    
            OR  `glpi_tickettasks`.`content`  LIKE '%printaudit%'    
            OR  `glpi_taskcategories_e5ca2f53018fdc28a31faf534186b3a2`.`name`  LIKE '%printaudit%'    
            OR  `glpi_tickettasks`.`is_private`  LIKE '%printaudit%'   
            OR  (
                (
                    (
                        `glpi_users_e5ca2f53018fdc28a31faf534186b3a2`.`firstname`   LIKE '%printaudit%' 
                        OR `glpi_users_e5ca2f53018fdc28a31faf534186b3a2`.`realname`   LIKE '%printaudit%' 
                        OR `glpi_users_e5ca2f53018fdc28a31faf534186b3a2`.`name`   LIKE '%printaudit%' 
                        OR CONCAT(`glpi_users_e5ca2f53018fdc28a31faf534186b3a2`.`firstname`, ' ', `glpi_users_e5ca2f53018fdc28a31faf534186b3a2`.`realname`)   LIKE '%printaudit%'  
                    )
                )
            )
            OR  (
                (
                    (
                        `glpi_users_users_id_tech_e5ca2f53018fdc28a31faf534186b3a2`.`firstname`   LIKE '%printaudit%' 
                        OR `glpi_users_users_id_tech_e5ca2f53018fdc28a31faf534186b3a2`.`realname`   LIKE '%printaudit%' 
                        OR `glpi_users_users_id_tech_e5ca2f53018fdc28a31faf534186b3a2`.`name`   LIKE '%printaudit%' 
                        OR CONCAT(`glpi_users_users_id_tech_e5ca2f53018fdc28a31faf534186b3a2`.`firstname`, ' ', `glpi_users_users_id_tech_e5ca2f53018fdc28a31faf534186b3a2`.`realname`)   LIKE '%printaudit%'  
                    )
                )
            )
            OR  `glpi_tickettasks`.`actiontime`  LIKE '%printaudit%'    
            OR  `glpi_tickettasks`.`date`  LIKE '%printaudit%'    
            OR  `glpi_tickettasks`.`state`  LIKE '%printaudit%'    
            OR  `glpi_solutiontypes`.`name`  LIKE '%printaudit%'    
            OR  `glpi_tickets`.`solution`  LIKE '%printaudit%'    
            OR  `glpi_ticketcosts`.`cost_time`  LIKE '%printaudit%'    
            OR  `glpi_ticketcosts`.`actiontime`  LIKE '%printaudit%'    
            OR  `glpi_ticketcosts`.`cost_fixed`  LIKE '%printaudit%'    
            OR  `glpi_ticketcosts`.`cost_material`  LIKE '%printaudit%'   
        )
    )
GROUP BY `glpi_tickets`.`id` 
ORDER BY ITEM_9 DESC

Finally, I have two questions which would be very grateful if it was answered:

1. What is the function of these JOINs in this query? They really necessary?
2. If so, you can optimize this query somehow? (creating indices, i.e.) It would be interesting to include in the structure of the database?

Thank you very much!

Offline

#5 2013-09-23 12:14:09

Snippie
Member
Registered: 2008-01-18
Posts: 7

Re: Lost connection with database when searching by all fields of tickets

I can confirm this behaviour.
A database with 10000 tickets will not react anymore when selecting ALL when searching.

Offline

#6 2013-09-25 08:40:33

MoYo
GLPI - Lead
From: Poitiers
Registered: 2004-09-13
Posts: 14,513
Website

Re: Lost connection with database when searching by all fields of tickets

WIth huge database, all search is not a good idea.
You could disable it on global config

MoYo - Julien Dombre - Association INDEPNET
Contribute to GLPI :    Support     Contribute     References     Freshmeat

Offline

#7 2017-10-06 20:06:04

dahvakana
Member
Registered: 2012-02-13
Posts: 12

Re: Lost connection with database when searching by all fields of tickets

If this is the expected behavior for over a certain number of tickets, Could you describe how to "disable it on global config" or link to a post that does describe it?

thanks,

Glpi        ->0.90.1
PHP        ->5.6.31
Apache    ->2.4.27
MySQL    ->5.6.27

Offline

#8 2017-10-09 15:43:04

yllen
GLPI-DEV
From: Sillery (51)
Registered: 2008-01-14
Posts: 15,278

Re: Lost connection with database when searching by all fields of tickets

dahvakana: this issue is for an old version of GLPI, not your.
Please create your own post with the description of your problem.

CentOS 6.5 - CentOS 7.x
PHP 5.6 - PHP 7.x - MySQL 5.6  - MariaDB 10.2 + APC + oOPcache
GLPI from 0.72 to dev version
Certifiée ITIL (ITV2F, ITILF, ITILOSA)

Offline

Pages: 1

Topic closed

Board footer

Powered by FluxBB