Alexmothe93 commented 2 years ago

After migration from GLPI 9.4.5 + FusionInventory 9.4+2.4 to GLPI 10.0.1 + GLPIInventory 1.0.2, I have many SQL warnings and slow loading pages on /plugins/glpiinventory/front/deploygroup.form.php, mainly on dynamic groups with global rule criteria. The debug and sql-errors.log indicates weird SQL request like theses :

LEFT JOIN glpi_computers AS glpi_computers_name_Software ON (glpi_softwares.name = glpi_computers_name_Software.id => sql-error.example.txt
Same weird request from an empty new GLPI instance :

stonebuzz commented 2 years ago

Hi @Alexmothe93

can you retry with latest GLPI version (10.0.2)

Can you past here a screen of your default computer list to see headers

Best regards

Alexmothe93 commented 2 years ago

The issue is still present in GLPI 10.0.2 and GLPI Inventory Plugin 1.0.3, the weird request took (only) 24 seconds and still generates many warnings. I have many columns in computer list : header1 header2

When I delete most of them, the request is faster, but I still get SQL warnings.

stonebuzz commented 2 years ago

hi @Alexmothe93

For SQL warning can you past here result oh this SQL query

SHOW VARIABLES LIKE 'sql_mode';

For colmuns in computer list, you need to reduce them (plugin use global or personnal view from this list) because most of them add one or two LEFT JOIN

ex for graphic card :

LEFT JOIN `glpi_items_devicegraphiccards` 
                                          ON (`glpi_computers`.`id` = `glpi_items_devicegraphiccards`.`items_id`
                                              AND `glpi_items_devicegraphiccards`.`itemtype` = 'Computer'
                                              ) LEFT JOIN `glpi_devicegraphiccards`  AS `glpi_devicegraphiccards_acd922555250a073623bf0451b9d9a38`
                                          ON (`glpi_items_devicegraphiccards`.`devicegraphiccards_id` = `glpi_devicegraphiccards_acd922555250a073623bf0451b9d9a38`.`id`
                                              )

and can match severals rows like IP Address

If you cannot do without all this information, you should consider improving the database.

Best regards

Alexmothe93 commented 2 years ago

Hi,

Here is the result :


| Variable_name | Value                                                                                     |
+---------------+-------------------------------------------------------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)```

stonebuzz commented 2 years ago

Can you retry by using this SQL_MODE

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

Best regards

Alexmothe93 commented 2 years ago

No changes when applying theses SQL modes.

stonebuzz commented 2 years ago

Do you use MariaDB or MySQL? Which version?

Alexmothe93 commented 2 years ago

MariaDB 10.5.15

Information about system installation and configuration

GLPI 10.0.2 (/10.0.2_new => /var/www/glpi/10.0.2_new)
Installation mode: TARBALL
Current language:en_GB

Server

 
Operating system: Linux SRVGLPI-DEV2 5.10.0-14-amd64 #1 SMP Debian 5.10.113-1 (2022-04-29) x86_64
PHP 7.4.28 fpm-fcgi (Core, FFI, PDO, Phar, Reflection, SPL, SimpleXML, Zend OPcache, bz2, calendar, cgi-fcgi, ctype, curl, date,
    dom, exif, fileinfo, filter, ftp, gd, gettext, hash, iconv, intl, json, ldap, libxml, mbstring, mysqli, mysqlnd, openssl, pcre,
    pdo_mysql, posix, readline, session, shmop, sockets, sodium, standard, sysvmsg, sysvsem, sysvshm, tokenizer, xml, xmlreader,
    xmlrpc, xmlwriter, xsl, zip, zlib)
Setup: max_execution_time="30" memory_limit="128M" post_max_size="8M" safe_mode="" session.save_handler="files"
    upload_max_filesize="2M" 
Software: nginx/1.18.0
    Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:103.0) Gecko/20100101 Firefox/103.0
Server Software: Debian 11
    Server Version: 10.5.15-MariaDB-0+deb11u1
    Server SQL Mode: STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
    Parameters: glpi@127.0.0.1/glpi_10.0.2_new
    Host info: 127.0.0.1 via TCP/IP

PHP version (7.4.28) is supported.
Sessions configuration is OK.
Allocated memory is sufficient.
mysqli extension is installed.
Following extensions are installed: dom, fileinfo, json, simplexml.
curl extension is installed.
gd extension is installed.
intl extension is installed.
libxml extension is installed.
zlib extension is installed.
The constant SODIUM_CRYPTO_AEAD_XCHACHA20POLY1305_IETF_NPUBBYTES is present.
Database engine version (10.5.15) is supported.
The log file has been created successfully.
Write access to /var/www/glpi/10.0.2_new/files/_cache has been validated.
Write access to /var/www/glpi/10.0.2_new/config has been validated.
Write access to /var/www/glpi/10.0.2_new/files/_cron has been validated.
Write access to /var/www/glpi/10.0.2_new/files has been validated.
Write access to /var/www/glpi/10.0.2_new/files/_dumps has been validated.
Write access to /var/www/glpi/10.0.2_new/files/_graphs has been validated.
Write access to /var/www/glpi/10.0.2_new/files/_lock has been validated.
Write access to /var/www/glpi/10.0.2_new/files/_pictures has been validated.
Write access to /var/www/glpi/10.0.2_new/files/_plugins has been validated.
Write access to /var/www/glpi/10.0.2_new/files/_rss has been validated.
Write access to /var/www/glpi/10.0.2_new/files/_sessions has been validated.
Write access to /var/www/glpi/10.0.2_new/files/_tmp has been validated.
Write access to /var/www/glpi/10.0.2_new/files/_uploads has been validated.
Web access to the files directory should not be allowed but this cannot be checked automatically on this instance.
Make sure access to error log file (/10.0.2_new/files/_log/php-errors.log) is forbidden; otherwise review .htaccess file and web server configuration.
exif extension is installed.
ldap extension is installed.
openssl extension is installed.
zip extension is installed.
bz2 extension is installed.
Zend OPcache extension is installed.
Following extensions are installed: ctype, iconv, mbstring, sodium.
Write access to /var/www/glpi/10.0.2_new/marketplace has been validated.
Timezones seems loaded in database.

GLPI constants

 
GLPI_ROOT: /var/www/glpi/10.0.2_new
GLPI_CONFIG_DIR: /var/www/glpi/10.0.2_new/config
GLPI_VAR_DIR: /var/www/glpi/10.0.2_new/files
GLPI_MARKETPLACE_DIR: /var/www/glpi/10.0.2_new/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: 
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: 1
GLPI_MARKETPLACE_MANUAL_DOWNLOADS: 1
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: /var/www/glpi/10.0.2_new/files
GLPI_CACHE_DIR: /var/www/glpi/10.0.2_new/files/_cache
GLPI_CRON_DIR: /var/www/glpi/10.0.2_new/files/_cron
GLPI_DUMP_DIR: /var/www/glpi/10.0.2_new/files/_dumps
GLPI_GRAPH_DIR: /var/www/glpi/10.0.2_new/files/_graphs
GLPI_LOCAL_I18N_DIR: /var/www/glpi/10.0.2_new/files/_locales
GLPI_LOCK_DIR: /var/www/glpi/10.0.2_new/files/_lock
GLPI_LOG_DIR: /var/www/glpi/10.0.2_new/files/_log
GLPI_PICTURE_DIR: /var/www/glpi/10.0.2_new/files/_pictures
GLPI_PLUGIN_DOC_DIR: /var/www/glpi/10.0.2_new/files/_plugins
GLPI_RSS_DIR: /var/www/glpi/10.0.2_new/files/_rss
GLPI_SESSION_DIR: /var/www/glpi/10.0.2_new/files/_sessions
GLPI_TMP_DIR: /var/www/glpi/10.0.2_new/files/_tmp
GLPI_UPLOAD_DIR: /var/www/glpi/10.0.2_new/files/_uploads
GLPI_INVENTORY_DIR: /var/www/glpi/10.0.2_new/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/glpi-plugins/
GLPI_I18N_DIR: /var/www/glpi/10.0.2_new/locales
GLPI_VERSION: 10.0.2
GLPI_SCHEMA_VERSION: 10.0.2@a130db99c7d9b131c2e2ea59fe0d6260fe93d831
GLPI_MARKETPLACE_PRERELEASES: 
GLPI_MIN_PHP: 7.4.0
GLPI_MAX_PHP: 8.2.0
GLPI_YEAR: 2022

Libraries

 
htmlawed/htmlawed version 1.2.6 in (/var/www/glpi/10.0.2_new/vendor/htmlawed/htmlawed)
phpmailer/phpmailer version 6.6.0 in (/var/www/glpi/10.0.2_new/vendor/phpmailer/phpmailer/src)
simplepie/simplepie version 1.5.8 in (/var/www/glpi/10.0.2_new/vendor/simplepie/simplepie/library)
tecnickcom/tcpdf version 6.4.4 in (/var/www/glpi/10.0.2_new/vendor/tecnickcom/tcpdf)
michelf/php-markdown in (/var/www/glpi/10.0.2_new/vendor/michelf/php-markdown/Michelf)
true/punycode in (/var/www/glpi/10.0.2_new/vendor/true/punycode/src)
iamcal/lib_autolink in (/var/www/glpi/10.0.2_new/vendor/iamcal/lib_autolink)
sabre/dav in (/var/www/glpi/10.0.2_new/vendor/sabre/dav/lib/DAV)
sabre/http in (/var/www/glpi/10.0.2_new/vendor/sabre/http/lib)
sabre/uri in (/var/www/glpi/10.0.2_new/vendor/sabre/uri/lib)
sabre/vobject in (/var/www/glpi/10.0.2_new/vendor/sabre/vobject/lib)
laminas/laminas-i18n in (/var/www/glpi/10.0.2_new/vendor/laminas/laminas-i18n/src)
laminas/laminas-servicemanager in (/var/www/glpi/10.0.2_new/vendor/laminas/laminas-servicemanager/src)
monolog/monolog in (/var/www/glpi/10.0.2_new/vendor/monolog/monolog/src/Monolog)
sebastian/diff in (/var/www/glpi/10.0.2_new/vendor/sebastian/diff/src)
donatj/phpuseragentparser in (/var/www/glpi/10.0.2_new/vendor/donatj/phpuseragentparser/src/UserAgent)
elvanto/litemoji in (/var/www/glpi/10.0.2_new/vendor/elvanto/litemoji/src)
symfony/console in (/var/www/glpi/10.0.2_new/vendor/symfony/console)
scssphp/scssphp in (/var/www/glpi/10.0.2_new/vendor/scssphp/scssphp/src)
laminas/laminas-mail in (/var/www/glpi/10.0.2_new/vendor/laminas/laminas-mail/src/Protocol)
laminas/laminas-mime in (/var/www/glpi/10.0.2_new/vendor/laminas/laminas-mime/src)
rlanvin/php-rrule in (/var/www/glpi/10.0.2_new/vendor/rlanvin/php-rrule/src)
blueimp/jquery-file-upload in (/var/www/glpi/10.0.2_new/vendor/blueimp/jquery-file-upload/server/php)
ramsey/uuid in (/var/www/glpi/10.0.2_new/vendor/ramsey/uuid/src)
psr/log in (/var/www/glpi/10.0.2_new/vendor/psr/log/Psr/Log)
psr/simple-cache in (/var/www/glpi/10.0.2_new/vendor/psr/simple-cache/src)
psr/cache in (/var/www/glpi/10.0.2_new/vendor/psr/cache/src)
league/csv in (/var/www/glpi/10.0.2_new/vendor/league/csv/src)
mexitek/phpcolors in (/var/www/glpi/10.0.2_new/vendor/mexitek/phpcolors/src/Mexitek/PHPColors)
guzzlehttp/guzzle in (/var/www/glpi/10.0.2_new/vendor/guzzlehttp/guzzle/src)
guzzlehttp/psr7 in (/var/www/glpi/10.0.2_new/vendor/guzzlehttp/psr7/src)
glpi-project/inventory_format in (/var/www/glpi/10.0.2_new/vendor/glpi-project/inventory_format/lib/php)
wapmorgan/unified-archive in (/var/www/glpi/10.0.2_new/vendor/wapmorgan/unified-archive/src)
paragonie/sodium_compat in (/var/www/glpi/10.0.2_new/vendor/paragonie/sodium_compat/src)
symfony/cache in (/var/www/glpi/10.0.2_new/vendor/symfony/cache)
html2text/html2text in (/var/www/glpi/10.0.2_new/vendor/html2text/html2text/src)
symfony/css-selector in (/var/www/glpi/10.0.2_new/vendor/symfony/css-selector)
symfony/dom-crawler in (/var/www/glpi/10.0.2_new/vendor/symfony/dom-crawler)
twig/twig in (/var/www/glpi/10.0.2_new/vendor/twig/twig/src)
twig/string-extra in (/var/www/glpi/10.0.2_new/vendor/twig/string-extra)
symfony/polyfill-ctype not found
symfony/polyfill-iconv not found
symfony/polyfill-mbstring not found
symfony/polyfill-php80 in (/var/www/glpi/10.0.2_new/vendor/symfony/polyfill-php80)
symfony/polyfill-php81 in (/var/www/glpi/10.0.2_new/vendor/symfony/polyfill-php81)

SQL replicas

 
Not active

Notifications

 
Way of sending emails: PHP

Plugins list

stonebuzz commented 2 years ago

Can you retry by removing STRICT_TRANS_TABLES from SQL_MODE

Is not a definitive solution, is just to see if this mode is the root cause.

Best regards

Alexmothe93 commented 2 years ago

Still no changes without STRICT_TRANS_TABLES mode.

I'm not sure why you are troubleshooting at the SQL server level, as the issue is the SQL request generated by GLPI, not the execution of this SQL request. Did I miss something?

stonebuzz commented 2 years ago

hi @Alexmothe93 can you try this -> #205

Best regards

Alexmothe93 commented 2 years ago

205 didn't fix.

Few updates:

The issue is from GLPI, not GLPI Inventory plugin actually, when we go to Assets > Computers, criterias from the dynamic group are set in the search form (another issue), and SQL Warning errors are also generated.
Theses errors are generated also when setting manual criteria on Assets > Computers in this order :

Steps to reproduce:

Start from a GLPI instance version 9.4.5 with FusionInventory 9.4+2.4
Create dynamic group with these criterias (or similar)
Migrate to GLPI 10.0.2 and GLPI Inventory plugin 1.0.3
Go to dynamic group created in step 2.

Alexmothe93 commented 2 years ago

Also, the "FusInv - Last inventory" criteria isn't converted in "Last inventory date", but in Items seen when upgrading GLPI.

stonebuzz commented 2 years ago

@Alexmothe93

thanks for feedback,

For SQL warning, can you open new issue from GLPI side -> https://github.com/glpi-project/glpi/issues

For "FusInv - Last inventory" not converted, I look on my side

Best regards

stonebuzz commented 2 years ago

@Alexmothe93 can you try this -> #210

You need to replay from SQL dump (before migrating to glpiinventory)

Best regards

glpi-project / glpi-inventory-plugin

Dynamic group issues #147

205 didn't fix.