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.31k stars 1.29k forks source link

GLPI 10.0.10 : *** MySQL query warnings: #15868

Closed EC2311 closed 6 months ago

EC2311 commented 1 year ago

Code of Conduct

Is there an existing issue for this?

Version

V10.0.10

Bug description

The research seems to be ok, but warnings in sql-errors.log

Relevant log output

SQL Warning in sql-errors.log
[2023-10-26 14:15:45] glpisqllog.WARNING: DBmysql::query() in C:\wamp\www\glpi\src\DBmysql.php line 419
  *** MySQL query warnings:
  SQL: SELECT DISTINCT `glpi_changes`.`id` AS id, 'User' AS currentuser,
                        `glpi_changes`.`entities_id`, `glpi_changes`.`is_recursive`, `glpi_changes`.`id` AS `ITEM_Change_2`,   `glpi_changes`.`name` AS `ITEM_Change_1`,
                        `glpi_changes`.`id` AS `ITEM_Change_1_id`,
                        `glpi_changes`.`id` AS `ITEM_Change_1_id`, `glpi_changes`.`status` AS `ITEM_Change_1_status`, `glpi_entities`.`completename` AS `ITEM_Change_80`,  `glpi_changes`.`impact` AS `ITEM_Change_11`,  `glpi_changes`.`status` AS `ITEM_Change_12`,  `glpi_changes`.`time_to_resolve` AS `ITEM_Change_18`,  `glpi_changes`.`solvedate` AS `ITEM_Change_18_solvedate`, `glpi_changes`.`status` AS `ITEM_Change_18_status`,  GROUP_CONCAT(DISTINCT CONCAT(IFNULL(`glpi_changetasks`.`begin`, '__NULL__'),
                                               '$#$',`glpi_changetasks`.`id`) ORDER BY `glpi_changetasks`.`id` SEPARATOR '$$##$$')
                              AS `ITEM_Change_173`,

                   GROUP_CONCAT(DISTINCT `glpi_users_967e12d0667a4ce90227260e9786f51b`.`id` SEPARATOR '$$##$$')
                                       AS `ITEM_Change_5`,
                           GROUP_CONCAT(DISTINCT CONCAT(`glpi_changes_users_c6f7f58b9365babf7388e555e526efd5`.`users_id`, ' ',
                                                        `glpi_changes_users_c6f7f58b9365babf7388e555e526efd5`.`alternative_email`)
                                                        SEPARATOR '$$##$$') AS `ITEM_Change_5_2`, 
                            GROUP_CONCAT(DISTINCT CONCAT(IFNULL(`glpi_changes_items`.`items_id`, '__NULL__'),
                                               '$#$',`glpi_changes_items`.`id`) ORDER BY `glpi_changes_items`.`id` SEPARATOR '$$##$$')
                              AS `ITEM_Change_13`,

                   IFNULL(GROUP_CONCAT(DISTINCT CONCAT(IFNULL(`glpi_changes_items`.`itemtype`,
                                                                         '__NULL__'),
                                                   '$#$', `glpi_changes_items`.`id`)ORDER BY `glpi_changes_items`.`id` SEPARATOR '$$##$$'), '__NULL__')
                                    AS `ITEM_Change_13_itemtype`, `glpi_changes`.`date` AS `ITEM_Change_15`,   GROUP_CONCAT(DISTINCT `glpi_users_746d2ef492ac60dc4bc2afc6a5988729`.`id` SEPARATOR '$$##$$')
                                       AS `ITEM_Change_4`,
                           GROUP_CONCAT(DISTINCT CONCAT(`glpi_changes_users_019878060c6d5f06cbe3c4d7c31dec24`.`users_id`, ' ',
                                                        `glpi_changes_users_019878060c6d5f06cbe3c4d7c31dec24`.`alternative_email`)
                                                        SEPARATOR '$$##$$') AS `ITEM_Change_4_2`, 
                            GROUP_CONCAT(DISTINCT CONCAT(IFNULL(`glpi_changevalidations`.`status`, '__NULL__'),
                                               '$#$',`glpi_changevalidations`.`id`) ORDER BY `glpi_changevalidations`.`id` SEPARATOR '$$##$$')
                              AS `ITEM_Change_55`,

                  `glpi_plugin_fields_changemorechanges_dateenvoicontactfield`.`dateenvoicontactfield` AS `ITEM_Change_76880`,  `glpi_changes`.`date_mod` AS `ITEM_Change_19` FROM `glpi_changes`LEFT JOIN `glpi_entities` 
                                          ON (`glpi_changes`.`entities_id` = `glpi_entities`.`id`
                                              ) LEFT JOIN `glpi_changetasks` 
                                             ON (`glpi_changes`.`id` = `glpi_changetasks`.`changes_id`
                                                  ) LEFT JOIN `glpi_changes_users`  AS `glpi_changes_users_c6f7f58b9365babf7388e555e526efd5`
                                             ON (`glpi_changes`.`id` = `glpi_changes_users_c6f7f58b9365babf7388e555e526efd5`.`changes_id`
                                                  AND `glpi_changes_users_c6f7f58b9365babf7388e555e526efd5`.`type` = '2' )LEFT JOIN `glpi_users`  AS `glpi_users_967e12d0667a4ce90227260e9786f51b`
                                          ON (`glpi_changes_users_c6f7f58b9365babf7388e555e526efd5`.`users_id` = `glpi_users_967e12d0667a4ce90227260e9786f51b`.`id`
                                              ) LEFT JOIN `glpi_changes_items` 
                                             ON (`glpi_changes`.`id` = `glpi_changes_items`.`changes_id`
                                                 ) LEFT JOIN `glpi_changes_users`  AS `glpi_changes_users_019878060c6d5f06cbe3c4d7c31dec24`
                                             ON (`glpi_changes`.`id` = `glpi_changes_users_019878060c6d5f06cbe3c4d7c31dec24`.`changes_id`
                                                  AND `glpi_changes_users_019878060c6d5f06cbe3c4d7c31dec24`.`type` = '1' )LEFT JOIN `glpi_users`  AS `glpi_users_746d2ef492ac60dc4bc2afc6a5988729`
                                          ON (`glpi_changes_users_019878060c6d5f06cbe3c4d7c31dec24`.`users_id` = `glpi_users_746d2ef492ac60dc4bc2afc6a5988729`.`id`
                                              ) LEFT JOIN `glpi_changevalidations` 
                                             ON (`glpi_changes`.`id` = `glpi_changevalidations`.`changes_id`
                                                 ) LEFT JOIN `glpi_plugin_fields_changemorechanges`  AS `glpi_plugin_fields_changemorechanges_dateenvoicontactfield`
                                          ON (`glpi_changes`.`id` = `glpi_plugin_fields_changemorechanges_dateenvoicontactfield`.`items_id`
                                              AND `glpi_plugin_fields_changemorechanges_dateenvoicontactfield`.`itemtype` = 'Change'
                                              )  WHERE  `glpi_changes`.`is_deleted` = 0  AND (   `glpi_changes`.`status` IN ('1','9','10','7','4','11','12') AND  (   (`glpi_changes`.`id`  LIKE '%roll%' )  OR  (`glpi_changes`.`name`  LIKE '%roll%' )  OR  (`glpi_entities`.`completename`  LIKE '%roll%' )  OR  (`glpi_changes`.`status`  LIKE '%roll%' )  OR  (CONVERT(`glpi_changes`.`time_to_resolve` USING utf8mb4)  LIKE '%roll%' )  OR  (CONVERT(`glpi_changetasks`.`begin` USING utf8mb4)  LIKE '%roll%' )  OR  `glpi_changes`.`id` IN (
                        SELECT `changes_id`
                        FROM `glpi_changes_users`
                        WHERE (`users_id` IN (
                            SELECT `id`
                            FROM `glpi_users`
                            WHERE `name`   LIKE '%roll%'   OR `realname`   LIKE '%roll%'  OR `firstname`   LIKE '%roll%'  OR CONCAT(`realname`, ' ', `firstname`)   LIKE '%roll%' 
                        )  OR (`alternative_email`  LIKE '%roll%' ))  AND `glpi_changes_users`.`type` = '2' 
                    )  OR  (CONVERT(`glpi_changes`.`date` USING utf8mb4)  LIKE '%roll%' )  OR  `glpi_changes`.`id` IN (
                        SELECT `changes_id`
                        FROM `glpi_changes_users`
                        WHERE (`users_id` IN (
                            SELECT `id`
                            FROM `glpi_users`
                            WHERE `name`   LIKE '%roll%'   OR `realname`   LIKE '%roll%'  OR `firstname`   LIKE '%roll%'  OR CONCAT(`realname`, ' ', `firstname`)   LIKE '%roll%' 
                        )  OR (`alternative_email`  LIKE '%roll%' ))  AND `glpi_changes_users`.`type` = '1' 
                    ) OR  `glpi_changevalidations`.`status` IN ('roll')  OR  (CONVERT(`glpi_plugin_fields_changemorechanges_dateenvoicontactfield`.`dateenvoicontactfield` USING utf8mb4)  LIKE '%roll%' )  OR  (CONVERT(`glpi_changes`.`date_mod` USING utf8mb4)  LIKE '%roll%' ))  ) GROUP BY `glpi_changes`.`id` ORDER BY `ITEM_Change_19` DESC 
  Warnings: 
1292: Truncated incorrect DOUBLE value: 'roll'
  Backtrace :
  src\Search.php:1452                                
  src\Search.php:448                                 Search::constructData()
  front\search.php:65                                Search::getDatas()

Page URL

No response

Steps To reproduce

Using 'global research', up to the right, on the main menu : type what ever (here roll), validate

either something is found, either nothing is found (in my case some items are found)

SQL-errors.log is filled with warnings 1292: Truncated incorrect DOUBLE value: 'roll'

Your GLPI setup information

No response

Anything else?

No response

trasher commented 1 year ago

You should try disabling plugins; I do not reproduce on my local instance.

cedric-anne commented 1 year ago

Also, please provide requested system information. I think this kind of issues is only reproducible with a MariaDB SQL server.

EC2311 commented 1 year ago

Hello Sorry for the delay : holidays

GLPI 10.0.10 (/glpi => \wamp\www\glpi) Installation mode: TARBALL Current language:fr_FR

Server Operating system: Windows NT Ordi 10.0 build 20348 (Windows Server 2016) AMD64 PHP 8.0.26 apache2handler (Core, PDO, PDO_ODBC, Phar, Reflection, SPL, SimpleXML, Zend OPcache, apache2handler, bcmath, bz2, calendar, com_dotnet, ctype, curl, date, dom, exif, fileinfo, filter, gd, gettext, gmp, hash, iconv, imap, intl, json, ldap, libxml, mbstring, mysqli, mysqlnd, odbc, openssl, pcre, pdo_mysql, pdo_sqlite, readline, session, soap, sockets, sodium, sqlite3, standard, tokenizer, xdebug, xml, xmlreader, xmlwriter, xsl, zip, zlib) Setup: max_execution_time="120" memory_limit="128M" post_max_size="8M" safe_mode="" session.save_handler="files" upload_max_filesize="2M" Software: Apache/2.4.54 (Win64) OpenSSL/1.1.1s PHP/8.0.26 mod_fcgid/2.3.10-dev (Apache/2.4.54 (Win64) OpenSSL/1.1.1s PHP/8.0.26 mod_fcgid/2.3.10-dev Server at xxxxxxx@yyyyyy.net Port 80 ) Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/119.0.0.0 Safari/537.36 Server Software: MySQL Community Server - GPL Server Version: 8.0.31 Server SQL Mode: Parameters: yyyyyy@localhost/glpi Host info: localhost via TCP/IP

PHP version (8.0.26) 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 (8.0.31) is supported. No files from previous GLPI version detected. The log file has been created successfully. Write access to c:\wamp\www\glpi/files/_cache has been validated. Write access to c:\wamp\www\glpi/config has been validated. Write access to c:\wamp\www\glpi/files/_cron has been validated. Write access to c:\wamp\www\glpi/files has been validated. Write access to c:\wamp\www\glpi/files/_dumps has been validated. Write access to c:\wamp\www\glpi/files/_graphs has been validated. Write access to c:\wamp\www\glpi/files/_lock has been validated. Write access to c:\wamp\www\glpi/files/_pictures has been validated. Write access to c:\wamp\www\glpi/files/_plugins has been validated. Write access to c:\wamp\www\glpi/files/_rss has been validated. Write access to c:\wamp\www\glpi/files/_sessions has been validated. Write access to c:\wamp\www\glpi/files/_tmp has been validated. Write access to c:\wamp\www\glpi/files/_uploads has been validated.

Web server root directory configuration is not safe as it permits access to non-public files. See installation documentation for more details. The following directories should be placed outside "c:\wamp\www\glpi": ‣ "c:\wamp\www\glpi/files" ("GLPI_VAR_DIR") ‣ "c:\wamp\www\glpi\config" ("GLPI_CONFIG_DIR") You can ignore this suggestion if your web server root directory is "c:\wamp\www\glpi\public". PHP directive "session.cookie_httponly" should be set to "on" to prevent client-side script to access cookie values. 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 c:\wamp\www\glpi/marketplace has been validated. Timezones seems loaded in database.

GLPI constants

GLPI_ROOT: "c:\wamp\www\glpi" GLPI_CONFIG_DIR: "c:\wamp\www\glpi/config" GLPI_VAR_DIR: "c:\wamp\www\glpi/files" GLPI_MARKETPLACE_DIR: "c:\wamp\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_DOC_DIR: "c:\wamp\www\glpi/files" GLPI_CACHE_DIR: "c:\wamp\www\glpi/files/_cache" GLPI_CRON_DIR: "c:\wamp\www\glpi/files/_cron" GLPI_DUMP_DIR: "c:\wamp\www\glpi/files/_dumps" GLPI_GRAPH_DIR: "c:\wamp\www\glpi/files/_graphs" GLPI_LOCAL_I18N_DIR: "c:\wamp\www\glpi/files/_locales" GLPI_LOCK_DIR: "c:\wamp\www\glpi/files/_lock" GLPI_LOG_DIR: "c:\wamp\www\glpi/files/_log" GLPI_PICTURE_DIR: "c:\wamp\www\glpi/files/_pictures" GLPI_PLUGIN_DOC_DIR: "c:\wamp\www\glpi/files/_plugins" GLPI_RSS_DIR: "c:\wamp\www\glpi/files/_rss" GLPI_SESSION_DIR: "c:\wamp\www\glpi/files/_sessions" GLPI_TMP_DIR: "c:\wamp\www\glpi/files/_tmp" GLPI_UPLOAD_DIR: "c:\wamp\www\glpi/files/_uploads" GLPI_INVENTORY_DIR: "c:\wamp\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: "c:\wamp\www\glpi/locales" GLPI_VERSION: "10.0.10" GLPI_SCHEMA_VERSION: "10.0.10@05de68add675fb55abaeec10f3a2552085594a16" 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 (c:\wamp\www\glpi\vendor\htmlawed\htmlawed) phpmailer/phpmailer version 6.8.0 in (c:\wamp\www\glpi\vendor\phpmailer\phpmailer\src) simplepie/simplepie version 1.5.8 in (c:\wamp\www\glpi\vendor\simplepie\simplepie\library) tecnickcom/tcpdf version 6.6.2 in (c:\wamp\www\glpi\vendor\tecnickcom\tcpdf) michelf/php-markdown in (c:\wamp\www\glpi\vendor\michelf\php-markdown\Michelf) true/punycode in (c:\wamp\www\glpi\vendor\true\punycode\src) iamcal/lib_autolink in (c:\wamp\www\glpi\vendor\iamcal\lib_autolink) sabre/dav in (c:\wamp\www\glpi\vendor\sabre\dav\lib\DAV) sabre/http in (c:\wamp\www\glpi\vendor\sabre\http\lib) sabre/uri in (c:\wamp\www\glpi\vendor\sabre\uri\lib) sabre/vobject in (c:\wamp\www\glpi\vendor\sabre\vobject\lib) laminas/laminas-i18n in (c:\wamp\www\glpi\vendor\laminas\laminas-i18n\src) laminas/laminas-servicemanager in (c:\wamp\www\glpi\vendor\laminas\laminas-servicemanager\src) monolog/monolog in (c:\wamp\www\glpi\vendor\monolog\monolog\src\Monolog) sebastian/diff in (c:\wamp\www\glpi\vendor\sebastian\diff\src) donatj/phpuseragentparser in (c:\wamp\www\glpi\vendor\donatj\phpuseragentparser\src\UserAgent) elvanto/litemoji in (c:\wamp\www\glpi\vendor\elvanto\litemoji\src) symfony/console in (c:\wamp\www\glpi\vendor\symfony\console) scssphp/scssphp in (c:\wamp\www\glpi\vendor\scssphp\scssphp\src) laminas/laminas-mail in (c:\wamp\www\glpi\vendor\laminas\laminas-mail\src\Protocol) laminas/laminas-mime in (c:\wamp\www\glpi\vendor\laminas\laminas-mime\src) rlanvin/php-rrule in (c:\wamp\www\glpi\vendor\rlanvin\php-rrule\src) ramsey/uuid in (c:\wamp\www\glpi\vendor\ramsey\uuid\src) psr/log in (c:\wamp\www\glpi\vendor\psr\log\Psr\Log) psr/simple-cache in (c:\wamp\www\glpi\vendor\psr\simple-cache\src) psr/cache in (c:\wamp\www\glpi\vendor\psr\cache\src) league/csv in (c:\wamp\www\glpi\vendor\league\csv\src) mexitek/phpcolors in (c:\wamp\www\glpi\vendor\mexitek\phpcolors\src\Mexitek\PHPColors) guzzlehttp/guzzle in (c:\wamp\www\glpi\vendor\guzzlehttp\guzzle\src) guzzlehttp/psr7 in (c:\wamp\www\glpi\vendor\guzzlehttp\psr7\src) glpi-project/inventory_format in (c:\wamp\www\glpi\vendor\glpi-project\inventory_format\lib\php) wapmorgan/unified-archive in (c:\wamp\www\glpi\vendor\wapmorgan\unified-archive\src) paragonie/sodium_compat in (c:\wamp\www\glpi\vendor\paragonie\sodium_compat\src) symfony/cache in (c:\wamp\www\glpi\vendor\symfony\cache) html2text/html2text in (c:\wamp\www\glpi\vendor\html2text\html2text\src) symfony/css-selector in (c:\wamp\www\glpi\vendor\symfony\css-selector) symfony/dom-crawler in (c:\wamp\www\glpi\vendor\symfony\dom-crawler) twig/twig in (c:\wamp\www\glpi\vendor\twig\twig\src) twig/string-extra in (c:\wamp\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 in (c:\wamp\www\glpi\vendor\symfony\polyfill-php81) symfony/polyfill-php82 in (c:\wamp\www\glpi\vendor\symfony\polyfill-php82) league/oauth2-client in (c:\wamp\www\glpi\vendor\league\oauth2-client\src\Provider) league/oauth2-google in (c:\wamp\www\glpi\vendor\league\oauth2-google\src\Provider) thenetworg/oauth2-azure in (c:\wamp\www\glpi\vendor\thenetworg\oauth2-azure\src\Provider)

LDAP directories

SQL replicas

Not active

Notifications

Way of sending emails: SMTP (yyyyyy@xxx.xxx.xxx.xxx)

Mails receivers

Plugins list

fields               Name: Additional fields              Version: 1.21.4     State: Installed / not activated               
    Install Method: Manual
news                 Name: Alertes                        Version: 1.12.1     State: Installed / not activated               
    Install Method: Manual
databases            Name: Bases de données               Version: 2.3.2      State: Installed / not activated               
    Install Method: Manual
shellcommands        Name: Commandes Shell                Version: 4.0.1      State: Installed / not activated               
    Install Method: Manual
behaviors            Name: Comportements                  Version: 2.7.2      State: Installed / not activated               
    Install Method: Manual
uninstall            Name: Cycle de vie des matériels (un Version: 2.9.0      State: Installed / not activated               
    Install Method: Manual
mydashboard          Name: Dashboard                      Version: 2.1.5      State: Installed / not activated               
    Install Method: Manual
datainjection        Name: Data Injection                 Version: 2.13.3     State: Installed / not activated               
    Install Method: Manual
formcreator          Name: Form Creator                   Version: 2.13.7     State: Installed / not activated               
    Install Method: Manual
fusioninventory      Name: FusionInventory                Version: 9.5+4.2    State: Replaced                                
    Install Method: Manual
glpiinventory        Name: GLPI Inventory                 Version: 1.3.0      State: Installed / not activated               
    Install Method: Manual
oauthimap            Name: Oauth IMAP                     Version: 1.4.3      State: Installed / not activated               
    Install Method: Manual
mreporting           Name: Plus de rapports               Version: 1.8.3      State: Installed / not activated               
    Install Method: Manual
reports              Name: Rapports                       Version: 1.16.0     State: Installed / not activated               
    Install Method: Manual

Locales overrides c:\wamp\www\glpi/files/_locales/formcreator/form_148_fr_FR.php c:\wamp\www\glpi/files/_locales/formcreator/form_149_en_GB.php c:\wamp\www\glpi/files/_locales/formcreator/form_167_en_GB.php c:\wamp\www\glpi/files/_locales/formcreator/form_169_en_GB.php c:\wamp\www\glpi/files/_locales/formcreator/form_175_en_GB.php

cconard96 commented 11 months ago

I confirm the issue. Issue can be recreated by making Approval > Approval status a visible column for changes. The status search option for CommonITILObject has its datatype set to specific so the special handling for numeric columns that prevents searching text values against them isn't applied.

cedric-anne commented 6 months ago

Fixed by #16968