[Glpi 10.0.9] Excessive logging of the message: Error: Deadlock found when trying to get lock; try restarting transaction

[laszlokertesz]

Code of Conduct

• I agree to follow this project's Code of Conduct

Is there an existing issue for this?

• I have searched the existing issues

Version

10.0.9

Bug description

The sql-errors.log file is spammed with messages related to sql related deadlocks and lock wait timeouts, several times per second and it fills up the disk.

Relevant log output

Most common error message:

`[2023-10-26 07:26:33] glpisqllog.ERROR: DBmysql::query() in /var/www/html/glpi/src/DBmysql.php line 379
  *** MySQL query error:
  SQL: UPDATE `glpi_softwares` SET `softwarecategories_id` = '429', `date_mod` = '2023-10-26 07:25:20' WHERE `id` = '63150'
  Error: Deadlock found when trying to get lock; try restarting transaction
  Backtrace :
  src/DBmysql.php:1468                               
  src/CommonDBTM.php:683                             DBmysql->update()
  src/CommonDBTM.php:1672                            CommonDBTM->updateInDB()
  src/Inventory/Asset/Software.php:403               CommonDBTM->update()
  src/Inventory/Asset/MainAsset.php:933              Glpi\Inventory\Asset\Software->handle()
  src/Inventory/Asset/MainAsset.php:848              Glpi\Inventory\Asset\MainAsset->handleAssets()
  src/RuleImportAsset.php:969                        Glpi\Inventory\Asset\MainAsset->rulepassed()
  src/Rule.php:1525                                  RuleImportAsset->executeActions()
  src/RuleCollection.php:1640                        Rule->process()
  src/Inventory/Asset/MainAsset.php:573              RuleCollection->processAllRules()
  src/Inventory/Inventory.php:709                    Glpi\Inventory\Asset\MainAsset->handle()
  src/Inventory/Inventory.php:340                    Glpi\Inventory\Inventory->handleItem()
  src/Inventory/Request.php:360                      Glpi\Inventory\Inventory->doInventory()
  src/Inventory/Request.php:90                       Glpi\Inventory\Request->inventory()
  src/Agent/Communication/AbstractRequest.php:359    Glpi\Inventory\Request->handleAction()
  src/Agent/Communication/AbstractRequest.php:271    Glpi\Agent\Communication\AbstractRequest->handleJSONRequest()
  front/inventory.php:92                             Glpi\Agent\Communication\AbstractRequest->handleRequest()
  index.php:93                                       include_once()
  {"user":"inventory@domain.com","mem_usage":"33.551\", 7.20Mio)"} 
`
Less common but still present in large numbers:

`[2023-10-26 07:26:33] glpisqllog.ERROR: DBmysql::query() in /var/www/html/glpi/src/DBmysql.php line 379
  *** MySQL query error:
  SQL: UPDATE `glpi_softwares` SET `softwarecategories_id` = '430', `date_mod` = '2023-10-26 07:25:24' WHERE `id` = '144084'
  Error: Lock wait timeout exceeded; try restarting transaction
  Backtrace :
  src/DBmysql.php:1468                               
  src/CommonDBTM.php:683                             DBmysql->update()
  src/CommonDBTM.php:1672                            CommonDBTM->updateInDB()
  src/Inventory/Asset/Software.php:403               CommonDBTM->update()
  src/Inventory/Asset/MainAsset.php:933              Glpi\Inventory\Asset\Software->handle()
  src/Inventory/Asset/MainAsset.php:848              Glpi\Inventory\Asset\MainAsset->handleAssets()
  src/RuleImportAsset.php:969                        Glpi\Inventory\Asset\MainAsset->rulepassed()
  src/Rule.php:1525                                  RuleImportAsset->executeActions()
  src/RuleCollection.php:1640                        Rule->process()
  src/Inventory/Asset/MainAsset.php:573              RuleCollection->processAllRules()
  src/Inventory/Inventory.php:709                    Glpi\Inventory\Asset\MainAsset->handle()
  src/Inventory/Inventory.php:340                    Glpi\Inventory\Inventory->handleItem()
  src/Inventory/Request.php:360                      Glpi\Inventory\Inventory->doInventory()
  src/Inventory/Request.php:90                       Glpi\Inventory\Request->inventory()
  src/Agent/Communication/AbstractRequest.php:359    Glpi\Inventory\Request->handleAction()
  src/Agent/Communication/AbstractRequest.php:271    Glpi\Agent\Communication\AbstractRequest->handleJSONRequest()
  front/inventory.php:92                             Glpi\Agent\Communication\AbstractRequest->handleRequest()
  index.php:93                                       include_once()
  {"user":"inventory@domain.com","mem_usage":"50.102\", 8.84Mio)"} 
`
And sometimes

`[2023-10-26 07:26:34] glpisqllog.WARNING: DBmysql::query() in /var/www/html/glpi/src/DBmysql.php line 419
  *** MySQL query warnings:
  SQL: SELECT * FROM `glpi_lockedfields` WHERE `itemtype` = 'Item_DeviceBattery' AND ((`items_id` = '' OR `is_global` = '1'))
  Warnings: 
1292: Truncated incorrect DECIMAL value: ''
  Backtrace :
  src/DBmysqlIterator.php:112                        
  src/DBmysql.php:1078                               DBmysqlIterator->execute()
  src/Lockedfield.php:178                            DBmysql->request()
  src/Lockedfield.php:158                            Lockedfield->getLocks()
  src/Inventory/Asset/InventoryAsset.php:433         Lockedfield->getLockedNames()
  src/Inventory/Asset/Device.php:175                 Glpi\Inventory\Asset\InventoryAsset->handleInput()
  src/Inventory/Asset/MainAsset.php:933              Glpi\Inventory\Asset\Device->handle()
  src/Inventory/Asset/MainAsset.php:848              Glpi\Inventory\Asset\MainAsset->handleAssets()
  src/RuleImportAsset.php:969                        Glpi\Inventory\Asset\MainAsset->rulepassed()
  src/Rule.php:1525                                  RuleImportAsset->executeActions()
  src/RuleCollection.php:1640                        Rule->process()
  src/Inventory/Asset/MainAsset.php:573              RuleCollection->processAllRules()
  src/Inventory/Inventory.php:709                    Glpi\Inventory\Asset\MainAsset->handle()
  src/Inventory/Inventory.php:340                    Glpi\Inventory\Inventory->handleItem()
  src/Inventory/Request.php:360                      Glpi\Inventory\Inventory->doInventory()
  src/Inventory/Request.php:90                       Glpi\Inventory\Request->inventory()
  src/Agent/Communication/AbstractRequest.php:359    Glpi\Inventory\Request->handleAction()
  src/Agent/Communication/AbstractRequest.php:271    Glpi\Agent\Communication\AbstractRequest->handleJSONRequest()
  front/inventory.php:92                             Glpi\Agent\Communication\AbstractRequest->handleRequest()
  index.php:93                                       include_once()
  {"user":"inventory@domain.com"} 
`

Page URL

No response

Steps To reproduce

Just tail the sql-errors.log file, it is updating several times a second with these messages.

Your GLPI setup information

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

Operating system: Linux inventory.3pillarglobal.com 6.1.0-12-amd64 #1 SMP PREEMPT_DYNAMIC Debian 6.1.52-1 (2023-09-07) x86_64
PHP 8.2.7 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, imap, intl, json, ldap, libxml, mbstring, mysqli,
mysqlnd, openssl, pcre, pdo_mysql, posix, random, readline, session, shmop, sockets, sodium, ssh2, standard, sysvmsg, sysvsem,
sysvshm, tokenizer, xml, xmlreader, xmlrpc, xmlwriter, xsl, zip, zlib)
Setup: max_execution_time="30" memory_limit="256M" post_max_size="8M" safe_mode="" session.save_handler="files"
upload_max_filesize="2M"
Software: Apache ()
Mozilla/5.0 (X11; Linux x86_64; rv:109.0) Gecko/20100101 Firefox/119.0
Server Software: Debian 12
Server Version: 10.11.3-MariaDB-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.7) is supported.PHP version (8.2.7) is supported.
Sessions configuration is OK.Sessions configuration is OK.
Allocated memory is sufficient.Allocated memory is sufficient.
mysqli extension is installed.mysqli extension is installed.
Following extensions are installed: dom, fileinfo, filter, libxml, json, simplexml, xmlreader, xmlwriter.Following extensions are installed: dom, fileinfo, filter, libxml, json, simplexml, xmlreader, xmlwriter.
curl extension is installed.curl extension is installed.
gd extension is installed.gd extension is installed.
intl extension is installed.intl extension is installed.
zlib extension is installed.zlib extension is installed.
The constant SODIUM_CRYPTO_AEAD_XCHACHA20POLY1305_IETF_NPUBBYTES is present.The constant SODIUM_CRYPTO_AEAD_XCHACHA20POLY1305_IETF_NPUBBYTES is present.
Database engine version (10.11.3) is supported.Database engine version (10.11.3) is supported.
No files from previous GLPI version detected.No files from previous GLPI version detected.
The log file has been created successfully.The log file has been created successfully.
Write access to /var/www/html/glpi/files/_cache has been validated. Write access to /var/www/html/glpi/config has been validated. Write access to /var/www/html/glpi/files/_cron has been validated. Write access to /var/www/html/glpi/files has been validated. Write access to /var/www/html/glpi/files/_dumps has been validated. Write access to /var/www/html/glpi/files/_graphs has been validated. Write access to /var/www/html/glpi/files/_lock has been validated. Write access to /var/www/html/glpi/files/_pictures has been validated. Write access to /var/www/html/glpi/files/_plugins has been validated. Write access to /var/www/html/glpi/files/_rss has been validated. Write access to /var/www/html/glpi/files/_sessions has been validated. Write access to /var/www/html/glpi/files/_tmp has been validated. Write access to /var/www/html/glpi/files/_uploads has been validated.Write access to /var/www/html/glpi/files/_cache has been validated.
Write access to /var/www/html/glpi/config has been validated.
Write access to /var/www/html/glpi/files/_cron has been validated.
Write access to /var/www/html/glpi/files has been validated.
Write access to /var/www/html/glpi/files/_dumps has been validated.
Write access to /var/www/html/glpi/files/_graphs has been validated.
Write access to /var/www/html/glpi/files/_lock has been validated.
Write access to /var/www/html/glpi/files/_pictures has been validated.
Write access to /var/www/html/glpi/files/_plugins has been validated.
Write access to /var/www/html/glpi/files/_rss has been validated.
Write access to /var/www/html/glpi/files/_sessions has been validated.
Write access to /var/www/html/glpi/files/_tmp has been validated.
Write access to /var/www/html/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.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 "/var/www/html/glpi": ‣ "/var/www/html/glpi/files" ("GLPI_VAR_DIR") ‣ "/var/www/html/glpi/config" ("GLPI_CONFIG_DIR") You can ignore this suggestion if your web server root directory is "/var/www/html/glpi/public".The following directories should be placed outside "/var/www/html/glpi":
‣ "/var/www/html/glpi/files" ("GLPI_VAR_DIR")
‣ "/var/www/html/glpi/config" ("GLPI_CONFIG_DIR")
You can ignore this suggestion if your web server root directory is "/var/www/html/glpi/public".
PHP directive "session.cookie_secure" should be set to "on" when GLPI can be accessed on HTTPS protocol. PHP directive "session.cookie_httponly" should be set to "on" to prevent client-side script to access cookie values.PHP directive "session.cookie_secure" should be set to "on" when GLPI can be accessed on HTTPS protocol.
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.OS and PHP are relying on 64 bits integers.
exif extension is installed.exif extension is installed.
ldap extension is installed.ldap extension is installed.
openssl extension is installed.openssl extension is installed.
Following extensions are installed: bz2, Phar, zip.Following extensions are installed: bz2, Phar, zip.
Zend OPcache extension is installed.Zend OPcache extension is installed.
Following extensions are installed: ctype, iconv, mbstring, sodium.Following extensions are installed: ctype, iconv, mbstring, sodium.
Write access to /var/www/html/glpi/marketplace has been validated.Write access to /var/www/html/glpi/marketplace has been validated.
Timezones seems loaded in database.Timezones seems loaded in database.

Anything else?

We have about 2500 endpoints reporting to this server.

[SilvaFernando]

Hi team, do you have some feedback for us? I have the same problem.

Thanks,
Fernando Dias da Silva

[github-actions]

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.

[ftoledo]

I have same errors at 10.0.16 with some other tables like softwareversions

MariaDB [glpi]> select count(*) from glpi_softwareversions;
+----------+
| count(*) |
+----------+
|  1346739 |
+----------+
1 row in set (2,192 sec)