search

glpi-project / glpi

GLPI is a Free Asset and IT Management Software package, Data center management, ITIL Service Desk, licenses tracking and software auditing.
https://glpi-project.org
GNU General Public License v3.0
4.01k stars 1.26k forks source link

Having "All linked tickets" collumn causes slowness and SQL warnings #16571

Closed jfael-isol closed 2 months ago

jfael-isol commented 5 months ago

Code of Conduct

Is there an existing issue for this?

Version

10.0.11

Bug description

While searching for tickets, if you have the "All linked tickets" collumn in your personal view, and you search "Items seens"+"Contains", the search can take a very long time to finish, and it will generate SQL Warning messages

Relevant log output

[2024-02-15 14:51:57] glpisqllog.WARNING: DBmysql::doQuery() in /var/www/glpi/src/DBmysql.php line 443
  *** MySQL query warnings:
  SQL: SELECT DISTINCT `glpi_tickets`.`id` AS id, 'testuser' AS currentuser,
                        `glpi_tickets`.`id` AS `ITEM_Ticket_2`,   `glpi_tickets`.`name` AS `ITEM_Ticket_1`,
                        `glpi_tickets`.`id` AS `ITEM_Ticket_1_id`,
                        `glpi_tickets`.`id` AS `ITEM_Ticket_1_id`, `glpi_tickets`.`status` AS `ITEM_Ticket_1_status`, `glpi_entities`.`completename` AS `ITEM_Ticket_80`,   GROUP_CONCAT(DISTINCT CONCAT(IFNULL(`glpi_items_tickets`.`items_id`, '__NULL__'),
                                               '$#$',`glpi_items_tickets`.`id`) ORDER BY `glpi_items_tickets`.`id` SEPARATOR '$$##$$')
                              AS `ITEM_Ticket_13`,

                   IFNULL(GROUP_CONCAT(DISTINCT CONCAT(IFNULL(`glpi_items_tickets`.`itemtype`,
                                                                         '__NULL__'),
                                                   '$#$', `glpi_items_tickets`.`id`)ORDER BY `glpi_items_tickets`.`id` SEPARATOR '$$##$$'), '__NULL__')
                                    AS `ITEM_Ticket_13_itemtype`,  GROUP_CONCAT(DISTINCT `glpi_users_af1042e23ce6565cfe58c6db91f84692`.`id` SEPARATOR '$$##$$')
                                       AS `ITEM_Ticket_4`,
                           GROUP_CONCAT(DISTINCT CONCAT(`glpi_tickets_users_019878060c6d5f06cbe3c4d7c31dec24`.`users_id`, ' ',
                                                        `glpi_tickets_users_019878060c6d5f06cbe3c4d7c31dec24`.`alternative_email`)
                                                        SEPARATOR '$$##$$') AS `ITEM_Ticket_4_2`,
                           `glpi_tickets`.`type` AS `ITEM_Ticket_14`,  `glpi_tickets`.`status` AS `ITEM_Ticket_12`,  `glpi_tickets`.`date` AS `ITEM_Ticket_15`,  `glpi_tickets`.`date_mod` AS `ITEM_Ticket_19`,  `glpi_tickets`.`priority` AS `ITEM_Ticket_3`,   GROUP_CONCAT(DISTINCT `glpi_users_b1b92f6be5e70531688d870931e94a65`.`id` SEPARATOR '$$##$$')
                                       AS `ITEM_Ticket_5`,
                           GROUP_CONCAT(DISTINCT CONCAT(`glpi_tickets_users_c6f7f58b9365babf7388e555e526efd5`.`users_id`, ' ',
                                                        `glpi_tickets_users_c6f7f58b9365babf7388e555e526efd5`.`alternative_email`)
                                                        SEPARATOR '$$##$$') AS `ITEM_Ticket_5_2`,
                           `glpi_plugin_fields_ticketticketredmines_redminefield`.`redminefield` AS `ITEM_Ticket_76666`,   GROUP_CONCAT(DISTINCT CONCAT(IFNULL(`glpi_tickets_tickets`.`tickets_id_1`, '__NULL__'),
                                               '$#$',`glpi_tickets_tickets`.`id`) ORDER BY `glpi_tickets_tickets`.`id` SEPARATOR '$$##$$')
                              AS `ITEM_Ticket_40`,

                   IFNULL(GROUP_CONCAT(DISTINCT CONCAT(IFNULL(`glpi_tickets_tickets`.`tickets_id_2`,
                                                                         '__NULL__'),
                                                   '$#$', `glpi_tickets_tickets`.`id`)ORDER BY `glpi_tickets_tickets`.`id` SEPARATOR '$$##$$'), '__NULL__')
                                    AS `ITEM_Ticket_40_tickets_id_2`,  GROUP_CONCAT(DISTINCT CONCAT(IFNULL(`glpi_groups_accbd4432fbbbd44d4ac65bbb91a0812`.`completename`, '__NULL__'),
                                               '$#$',`glpi_groups_accbd4432fbbbd44d4ac65bbb91a0812`.`id`) ORDER BY `glpi_groups_accbd4432fbbbd44d4ac65bbb91a0812`.`id` SEPARATOR '$$##$$')
                              AS `ITEM_Ticket_8` FROM `glpi_tickets`LEFT JOIN `glpi_entities`
                                          ON (`glpi_tickets`.`entities_id` = `glpi_entities`.`id`
                                              ) LEFT JOIN `glpi_items_tickets`
                                             ON (`glpi_tickets`.`id` = `glpi_items_tickets`.`tickets_id`
                                                 ) LEFT JOIN `glpi_tickets_users`  AS `glpi_tickets_users_019878060c6d5f06cbe3c4d7c31dec24`
                                             ON (`glpi_tickets`.`id` = `glpi_tickets_users_019878060c6d5f06cbe3c4d7c31dec24`.`tickets_id`
                                                  AND `glpi_tickets_users_019878060c6d5f06cbe3c4d7c31dec24`.`type` = '1' )LEFT JOIN `glpi_users`  AS `glpi_users_af1042e23ce6565cfe58c6db91f84692`
                                          ON (`glpi_tickets_users_019878060c6d5f06cbe3c4d7c31dec24`.`users_id` = `glpi_users_af1042e23ce6565cfe58c6db91f84692`.`id`
                                              ) LEFT JOIN `glpi_tickets_users`  AS `glpi_tickets_users_c6f7f58b9365babf7388e555e526efd5`
                                             ON (`glpi_tickets`.`id` = `glpi_tickets_users_c6f7f58b9365babf7388e555e526efd5`.`tickets_id`
                                                  AND `glpi_tickets_users_c6f7f58b9365babf7388e555e526efd5`.`type` = '2' )LEFT JOIN `glpi_users`  AS `glpi_users_b1b92f6be5e70531688d870931e94a65`
                                          ON (`glpi_tickets_users_c6f7f58b9365babf7388e555e526efd5`.`users_id` = `glpi_users_b1b92f6be5e70531688d870931e94a65`.`id`
                                              ) LEFT JOIN `glpi_plugin_fields_ticketticketredmines`  AS `glpi_plugin_fields_ticketticketredmines_redminefield`
                                          ON (`glpi_tickets`.`id` = `glpi_plugin_fields_ticketticketredmines_redminefield`.`items_id`
                                              AND `glpi_plugin_fields_ticketticketredmines_redminefield`.`itemtype` = 'Ticket'
                                              )  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_groups_tickets`  AS `glpi_groups_tickets_c6f7f58b9365babf7388e555e526efd5`
                                             ON (`glpi_tickets`.`id` = `glpi_groups_tickets_c6f7f58b9365babf7388e555e526efd5`.`tickets_id`
                                                  AND `glpi_groups_tickets_c6f7f58b9365babf7388e555e526efd5`.`type` = '2' )LEFT JOIN `glpi_groups`  AS `glpi_groups_accbd4432fbbbd44d4ac65bbb91a0812`
                                          ON (`glpi_groups_tickets_c6f7f58b9365babf7388e555e526efd5`.`groups_id` = `glpi_groups_accbd4432fbbbd44d4ac65bbb91a0812`.`id`
                                              ) WHERE  `glpi_tickets`.`is_deleted` = 0  AND (  (   (`glpi_tickets`.`id`  LIKE '%TEST%' )  OR  (`glpi_tickets`.`name`  LIKE '%TEST%' )  OR  (`glpi_entities`.`completename`  LIKE '%TEST%' )  OR  `glpi_tickets`.`id` IN (
                        SELECT `tickets_id`
                        FROM `glpi_tickets_users`
                        WHERE (`users_id` IN (
                            SELECT `id`
                            FROM `glpi_users`
                            WHERE `name`   LIKE '%TEST%'   OR `firstname`   LIKE '%TEST%'  OR `realname`   LIKE '%TEST%'  OR CONCAT(`firstname`, ' ', `realname`)   LIKE '%TEST%'
                        )  OR (`alternative_email`  LIKE '%TEST%' ))  AND `glpi_tickets_users`.`type` = '1'
                    )  OR  (`glpi_tickets`.`type`  LIKE '%TEST%' )  OR  (`glpi_tickets`.`status`  LIKE '%TEST%' )  OR  (CONVERT(`glpi_tickets`.`date` USING utf8mb4)  LIKE '%TEST%' )  OR  (CONVERT(`glpi_tickets`.`date_mod` USING utf8mb4)  LIKE '%TEST%' )  OR  `glpi_tickets`.`id` IN (
                        SELECT `tickets_id`
                        FROM `glpi_tickets_users`
                        WHERE (`users_id` IN (
                            SELECT `id`
                            FROM `glpi_users`
                            WHERE `name`   LIKE '%TEST%'   OR `firstname`   LIKE '%TEST%'  OR `realname`   LIKE '%TEST%'  OR CONCAT(`firstname`, ' ', `realname`)   LIKE '%TEST%'
                        )  OR (`alternative_email`  LIKE '%TEST%' ))  AND `glpi_tickets_users`.`type` = '2'
                    )  OR  (`glpi_plugin_fields_ticketticketredmines_redminefield`.`redminefield`  LIKE '%TEST%' ) OR  (((`glpi_tickets_tickets`.`tickets_id_1` = 'TEST'
                              OR `glpi_tickets_tickets`.`tickets_id_2` = 'TEST')
                             AND `glpi_tickets`.`id` <> 'TEST')
                            )  OR  `glpi_tickets`.`id` IN (
                        SELECT `tickets_id`
                        FROM `glpi_groups_tickets`
                        WHERE `groups_id` IN (
                            SELECT `id`
                            FROM `glpi_groups`
                            WHERE `completename`   LIKE '%TEST%'
                        )  AND `glpi_groups_tickets`.`type` = '2'
                    ))  ) GROUP BY `glpi_tickets`.`id` ORDER BY `ITEM_Ticket_14` ASC
  Warnings:
1292: Truncated incorrect DECIMAL value: 'TEST'
1292: Truncated incorrect DECIMAL value: 'TEST'
1292: Truncated incorrect DECIMAL value: 'TEST'
  Backtrace :
  src/Search.php:1458                                DBmysql->doQuery()
  src/Search.php:449                                 Search::constructData()
  src/Search.php:200                                 Search::getDatas()
  src/Search.php:177                                 Search::showList()
  front/ticket.php:58                                Search::show()
  public/index.php:82                                require()
  {"user":"795@srv-support"}

Page URL

No response

Steps To reproduce

  1. In "Assistance -> Tickets" create a personal view and add the collumn "All linked tickets"
  2. Search by "Items seen" + "contains" + "\<anything>"

Your GLPI setup information

Information about system installation and configuration
GLPI 10.0.11 ( => /var/www/glpi)
Installation mode: TARBALL
Current language:en_GB
Server
 
Operating system: Linux srv-support 5.15.0-91-generic #​101-Ubuntu SMP Tue Nov 14 13:30:08 UTC 2023 x86_64
PHP 8.2.13 apache2handler (Core, FFI, PDO, Phar, Reflection, SPL, SimpleXML, Zend OPcache, apache2handler, apcu, bz2, calendar,
    ctype, curl, date, dom, exif, fileinfo, filter, ftp, gd, gettext, hash, iconv, igbinary, imap, intl, json, ldap, libxml,
    mbstring, mysqli, mysqlnd, openssl, pcre, pdo_mysql, posix, random, readline, redis, session, shmop, sockets, sodium, standard,
    sysvmsg, sysvsem, sysvshm, tokenizer, xml, xmlreader, xmlrpc, xmlwriter, xsl, zip, zlib)
Setup: max_execution_time="30" memory_limit="512M" post_max_size="8M" safe_mode="" session.save_handler="files"
    upload_max_filesize="2M" disable_functions="" 
Software: Apache/2.4.58 (Ubuntu) (Apache/2.4.58 (Ubuntu) Server at support.interlogsolutions.net Port 80
)
    Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/121.0.0.0 Safari/537.36
Server Software: Ubuntu 22.04
    Server Version: 10.6.16-MariaDB-0ubuntu0.22.04.1
    Server SQL Mode: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    Parameters: root@localhost/glpi
    Host info: Localhost via UNIX socket

PHP version (8.2.13) is supported.
Sessions configuration is OK.
Allocated memory is sufficient.
mysqli extension is installed.
Following extensions are installed: dom, fileinfo, filter, libxml, json, simplexml, xmlreader, xmlwriter.
curl extension is installed.
gd extension is installed.
intl extension is installed.
zlib extension is installed.
The constant SODIUM_CRYPTO_AEAD_XCHACHA20POLY1305_IETF_NPUBBYTES is present.
Database engine version (10.6.16) is supported.
No files from previous GLPI version detected.
The log file has been created successfully.
Write access to /var/www/glpi/files/_cache has been validated.
Write access to /var/www/glpi/files/_cron has been validated.
Write access to /var/www/glpi/files has been validated.
Write access to /var/www/glpi/files/_dumps has been validated.
Write access to /var/www/glpi/files/_graphs has been validated.
Write access to /var/www/glpi/files/_lock has been validated.
Write access to /var/www/glpi/files/_pictures has been validated.
Write access to /var/www/glpi/files/_plugins has been validated.
Write access to /var/www/glpi/files/_rss has been validated.
Write access to /var/www/glpi/files/_sessions has been validated.
Write access to /var/www/glpi/files/_tmp has been validated.
Write access to /var/www/glpi/files/_uploads has been validated.

Web server root directory configuration seems safe.
Sessions configuration is secured.
OS and PHP are relying on 64 bits integers.
exif extension is installed.
ldap extension is installed.
openssl extension is installed.
Following extensions are installed: bz2, Phar, zip.
Zend OPcache extension is installed.
Following extensions are installed: ctype, iconv, mbstring, sodium.
Write access to /var/www/glpi/marketplace has been validated.
Timezones seems loaded in database.
GLPI constants
 
GLPI_ROOT: "/var/www/glpi"
GLPI_CONFIG_DIR: "/var/www/glpi/config"
GLPI_VAR_DIR: "/var/www/glpi/files"
GLPI_MARKETPLACE_DIR: "/var/www/glpi/marketplace"
GLPI_USE_CSRF_CHECK: "1"
GLPI_CSRF_EXPIRES: "7200"
GLPI_CSRF_MAX_TOKENS: "100"
GLPI_USE_IDOR_CHECK: "1"
GLPI_IDOR_EXPIRES: "7200"
GLPI_ALLOW_IFRAME_IN_RICH_TEXT: false
GLPI_SERVERSIDE_URL_ALLOWLIST: ["/^(https?|feed):\\/\\/[^@:]+(\\/.*)?$/"]
GLPI_TELEMETRY_URI: "https://telemetry.glpi-project.org"
GLPI_INSTALL_MODE: "TARBALL"
GLPI_NETWORK_MAIL: "glpi@teclib.com"
GLPI_NETWORK_SERVICES: "https://services.glpi-network.com"
GLPI_MARKETPLACE_ALLOW_OVERRIDE: true
GLPI_MARKETPLACE_MANUAL_DOWNLOADS: true
GLPI_USER_AGENT_EXTRA_COMMENTS: ""
GLPI_DISABLE_ONLY_FULL_GROUP_BY_SQL_MODE: "1"
GLPI_AJAX_DASHBOARD: "1"
GLPI_CALDAV_IMPORT_STATE: 0
GLPI_DEMO_MODE: "0"
GLPI_CENTRAL_WARNINGS: "1"
GLPI_TEXT_MAXSIZE: "4000"
GLPI_DOC_DIR: "/var/www/glpi/files"
GLPI_CACHE_DIR: "/var/www/glpi/files/_cache"
GLPI_CRON_DIR: "/var/www/glpi/files/_cron"
GLPI_DUMP_DIR: "/var/www/glpi/files/_dumps"
GLPI_GRAPH_DIR: "/var/www/glpi/files/_graphs"
GLPI_LOCAL_I18N_DIR: "/var/www/glpi/files/_locales"
GLPI_LOCK_DIR: "/var/www/glpi/files/_lock"
GLPI_LOG_DIR: "/var/www/glpi/files/_log"
GLPI_PICTURE_DIR: "/var/www/glpi/files/_pictures"
GLPI_PLUGIN_DOC_DIR: "/var/www/glpi/files/_plugins"
GLPI_RSS_DIR: "/var/www/glpi/files/_rss"
GLPI_SESSION_DIR: "/var/www/glpi/files/_sessions"
GLPI_TMP_DIR: "/var/www/glpi/files/_tmp"
GLPI_UPLOAD_DIR: "/var/www/glpi/files/_uploads"
GLPI_INVENTORY_DIR: "/var/www/glpi/files/_inventories"
GLPI_NETWORK_REGISTRATION_API_URL: "https://services.glpi-network.com/api/registration/"
GLPI_MARKETPLACE_PLUGINS_API_URI: "https://services.glpi-network.com/api/marketplace/"
GLPI_I18N_DIR: "/var/www/glpi/locales"
GLPI_VERSION: "10.0.11"
GLPI_SCHEMA_VERSION: "10.0.11@6795e1eea6621428b5ef42350d555715bda8a96f"
GLPI_MARKETPLACE_PRERELEASES: false
GLPI_MIN_PHP: "7.4.0"
GLPI_MAX_PHP: "8.4.0"
GLPI_YEAR: "2023"
Libraries
 
htmlawed/htmlawed version 1.2.14 in (/var/www/glpi/vendor/htmlawed/htmlawed)
phpmailer/phpmailer version 6.8.0 in (/var/www/glpi/vendor/phpmailer/phpmailer/src)
simplepie/simplepie version 1.5.8 in (/var/www/glpi/vendor/simplepie/simplepie/library)
tecnickcom/tcpdf version 6.4.4 in (/var/www/glpi/marketplace/pdf/vendor/tecnickcom/tcpdf)
michelf/php-markdown in (/var/www/glpi/vendor/michelf/php-markdown/Michelf)
true/punycode in (/var/www/glpi/vendor/true/punycode/src)
iamcal/lib_autolink in (/var/www/glpi/vendor/iamcal/lib_autolink)
sabre/dav in (/var/www/glpi/vendor/sabre/dav/lib/DAV)
sabre/http in (/var/www/glpi/vendor/sabre/http/lib)
sabre/uri in (/var/www/glpi/vendor/sabre/uri/lib)
sabre/vobject in (/var/www/glpi/vendor/sabre/vobject/lib)
laminas/laminas-i18n in (/var/www/glpi/vendor/laminas/laminas-i18n/src)
laminas/laminas-servicemanager in (/var/www/glpi/vendor/laminas/laminas-servicemanager/src)
monolog/monolog in (/var/www/glpi/vendor/monolog/monolog/src/Monolog)
sebastian/diff in (/var/www/glpi/vendor/sebastian/diff/src)
donatj/phpuseragentparser in (/var/www/glpi/vendor/donatj/phpuseragentparser/src/UserAgent)
elvanto/litemoji in (/var/www/glpi/vendor/elvanto/litemoji/src)
symfony/console in (/var/www/glpi/vendor/symfony/console)
scssphp/scssphp in (/var/www/glpi/vendor/scssphp/scssphp/src)
laminas/laminas-mail in (/var/www/glpi/vendor/laminas/laminas-mail/src/Protocol)
laminas/laminas-mime in (/var/www/glpi/vendor/laminas/laminas-mime/src)
rlanvin/php-rrule in (/var/www/glpi/vendor/rlanvin/php-rrule/src)
ramsey/uuid in (/var/www/glpi/vendor/ramsey/uuid/src)
psr/log in (/var/www/glpi/vendor/psr/log/Psr/Log)
psr/simple-cache in (/var/www/glpi/vendor/psr/simple-cache/src)
psr/cache in (/var/www/glpi/vendor/psr/cache/src)
league/csv in (/var/www/glpi/vendor/league/csv/src)
mexitek/phpcolors in (/var/www/glpi/vendor/mexitek/phpcolors/src/Mexitek/PHPColors)
guzzlehttp/guzzle in (/var/www/glpi/vendor/guzzlehttp/guzzle/src)
guzzlehttp/psr7 in (/var/www/glpi/vendor/guzzlehttp/psr7/src)
glpi-project/inventory_format in (/var/www/glpi/vendor/glpi-project/inventory_format/lib/php)
wapmorgan/unified-archive in (/var/www/glpi/vendor/wapmorgan/unified-archive/src)
paragonie/sodium_compat in (/var/www/glpi/vendor/paragonie/sodium_compat/src)
symfony/cache in (/var/www/glpi/vendor/symfony/cache)
html2text/html2text in (/var/www/glpi/vendor/html2text/html2text/src)
symfony/css-selector in (/var/www/glpi/vendor/symfony/css-selector)
symfony/dom-crawler in (/var/www/glpi/vendor/symfony/dom-crawler)
twig/twig in (/var/www/glpi/vendor/twig/twig/src)
twig/string-extra in (/var/www/glpi/vendor/twig/string-extra)
symfony/polyfill-ctype not found
symfony/polyfill-iconv not found
symfony/polyfill-mbstring not found
symfony/polyfill-php80 not found
symfony/polyfill-php81 not found
symfony/polyfill-php82 in (/var/www/glpi/vendor/symfony/polyfill-php82)
league/oauth2-client in (/var/www/glpi/vendor/league/oauth2-client/src/Provider)
league/oauth2-google in (/var/www/glpi/vendor/league/oauth2-google/src/Provider)
thenetworg/oauth2-azure in (/var/www/glpi/vendor/thenetworg/oauth2-azure/src/Provider)
phpCas version 1.3.8 in (/usr/share/php/CAS/source)
LDAP directories
 

REDACTED
SQL replicas
 
Not active
Notifications
 
Way of sending emails: SMTP+TLS (anonymous@127.0.0.1)
Plugins list
 
    fields               Name: Additional fields              Version: 1.21.6     State: Enabled                                 
        Install Method: Marketplace
    news                 Name: Alerts                         Version: 1.12.1     State: Enabled                                 
        Install Method: Marketplace
    behaviors            Name: Behaviours                     Version: 2.7.2      State: Enabled                                 
        Install Method: Marketplace
    pdf                  Name: Print to pdf                   Version: 3.0.0      State: Enabled                                 
        Install Method: Marketplace

Anything else?

No response

jfael-isol commented 5 months ago

chrome_C6LnMHGlxP_2

trasher commented 5 months ago

Try if reproducible with latest stable release.

jfael-isol commented 5 months ago

Still present after the upgrade to 10.0.12.

Simply having the "All linked tickes" collumn showing has impact on the search time.

But it will be a lot worse if you are searching for "Items seen"+"contains" while the "All linked tickes" collumn is present.

github-actions[bot] commented 3 months ago

There has been no activity on this issue for some time and therefore it is considered stale and will be closed automatically in 10 days.

If this issue is related to a bug, please try to reproduce on latest release. If the problem persist, feel free to add a comment to revive this issue. If it is related to a new feature, please open a topic to discuss with community about this enhancement on suggestion website.

You may also consider taking a subscription to get professionnal support or contact GLPI editor team directly.