Found SQL error in sql-erros.log after computer deletion #17019

g-bougard · 2024-04-26T09:59:58Z

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.15

Bug description

I was able to delete a computer from assets without any error but I found later in sql-errors.log a SQL error.
I don't think massive action context is relevant.

Relevant log output

Stacktrace found in sql-errors.log:

[2024-04-25 16:55:12] glpisqllog.ERROR: DBmysql::doQuery() in /srv/glpi/src/DBmysql.php line 403
  *** MySQL query error:
  SQL: UPDATE `glpi_databaseinstances` SET `items_id` = '0', `itemtype` = NULL, `date_mod` = '2024-04-25 16:55:10' WHERE `id` = '3'
  Error: Column 'itemtype' cannot be null
  Backtrace :
  src/DBmysql.php:1503                               DBmysql->doQuery()
  src/CommonDBTM.php:689                             DBmysql->update()
  src/CommonDBTM.php:1698                            CommonDBTM->updateInDB()
  src/CommonDBTM.php:950                             CommonDBTM->update()
  src/CommonDBTM.php:797                             CommonDBTM->cleanRelationData()
  src/CommonDBTM.php:2100                            CommonDBTM->deleteFromDB()
  src/MassiveAction.php:1508                         CommonDBTM->delete()
  src/MassiveAction.php:1420                         MassiveAction::processMassiveActionsForOneItemtype()
  src/MassiveAction.php:1398                         MassiveAction->processForSeveralItemtypes()
  front/massiveaction.php:62                         MassiveAction->process()
  public/index.php:82                                require()
  {"user":"2@glpi-10-0-15","mem_usage":"2.113\", 4.63Mio)"}

Page URL

No response

Steps To reproduce

Import a computer with database instances
Delete it from assets

Your GLPI setup information

Informations sur le système, l'installation et la configuration

GLPI 10.0.15 ( => /srv/glpi)
Installation mode: TARBALL
Current language:fr_FR

Server

Operating system: Linux glpi-10-0-15 6.8.6-200.fc39.x86_64 #1 SMP PREEMPT_DYNAMIC Sat Apr 13 15:14:23 UTC 2024 x86_64
PHP 8.2.18 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, standard, sysvmsg, sysvsem,
sysvshm, tokenizer, xml, xmlreader, xmlrpc, xmlwriter, xsl, zip, zlib)
Setup: max_execution_time="300" memory_limit="1280M" post_max_size="1068M" safe_mode="" session.save_handler="files"
upload_max_filesize="1024M" disable_functions=""
Software: Apache/2.4.59 (Debian) (Apache/2.4.59 (Debian) Server at 192.168.2.15 Port 443
)
Mozilla/5.0 (X11; Linux x86_64; rv:125.0) Gecko/20100101 Firefox/125.0
Server Software: Debian 12
Server Version: 10.11.6-MariaDB-0+deb12u1
Server SQL Mode: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Parameters: glpi@127.0.0.1/glpi
Host info: 127.0.0.1 via TCP/IP

PHP version (8.2.18) 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.11.6) is supported.
No files from previous GLPI version detected.
The log file has been created successfully.
Write access to /srv/glpi/files/_cache has been validated.
Write access to /srv/glpi/files/_cron has been validated.
Write access to /srv/glpi/files has been validated.
Write access to /srv/glpi/files/_dumps has been validated.
Write access to /srv/glpi/files/_graphs has been validated.
Write access to /srv/glpi/files/_lock has been validated.
Write access to /srv/glpi/files/_pictures has been validated.
Write access to /srv/glpi/files/_plugins has been validated.
Write access to /srv/glpi/files/_rss has been validated.
Write access to /srv/glpi/files/_sessions has been validated.
Write access to /srv/glpi/files/_tmp has been validated.
Write access to /srv/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 /srv/glpi/marketplace has been validated.
Access to timezone database (mysql) is not allowed.

GLPI constants

GLPI_ROOT: "/srv/glpi"
GLPI_CONFIG_DIR: "/srv/glpi/config"
GLPI_VAR_DIR: "/srv/glpi/files"
GLPI_MARKETPLACE_DIR: "/srv/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: "/srv/glpi/files"
GLPI_CACHE_DIR: "/srv/glpi/files/_cache"
GLPI_CRON_DIR: "/srv/glpi/files/_cron"
GLPI_DUMP_DIR: "/srv/glpi/files/_dumps"
GLPI_GRAPH_DIR: "/srv/glpi/files/_graphs"
GLPI_LOCAL_I18N_DIR: "/srv/glpi/files/_locales"
GLPI_LOCK_DIR: "/srv/glpi/files/_lock"
GLPI_LOG_DIR: "/srv/glpi/files/_log"
GLPI_PICTURE_DIR: "/srv/glpi/files/_pictures"
GLPI_PLUGIN_DOC_DIR: "/srv/glpi/files/_plugins"
GLPI_RSS_DIR: "/srv/glpi/files/_rss"
GLPI_SESSION_DIR: "/srv/glpi/files/_sessions"
GLPI_TMP_DIR: "/srv/glpi/files/_tmp"
GLPI_UPLOAD_DIR: "/srv/glpi/files/_uploads"
GLPI_INVENTORY_DIR: "/srv/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: "/srv/glpi/locales"
GLPI_VERSION: "10.0.15"
GLPI_SCHEMA_VERSION: "10.0.15@2eed74704cb07e0bac48b933cbd5c1c356f09629"
GLPI_MARKETPLACE_PRERELEASES: false
GLPI_MIN_PHP: "7.4.0"
GLPI_MAX_PHP: "8.4.0"
GLPI_YEAR: "2024"

Libraries

 

htmlawed/htmlawed version 1.2.14 in (/srv/glpi/vendor/htmlawed/htmlawed)

phpmailer/phpmailer version 6.8.0 in (/srv/glpi/vendor/phpmailer/phpmailer/src)

simplepie/simplepie version 1.5.8 in (/srv/glpi/vendor/simplepie/simplepie/library)

tecnickcom/tcpdf version 6.7.5 in (/srv/glpi/vendor/tecnickcom/tcpdf)

michelf/php-markdown in (/srv/glpi/vendor/michelf/php-markdown/Michelf)

true/punycode in (/srv/glpi/vendor/true/punycode/src)

iamcal/lib_autolink in (/srv/glpi/vendor/iamcal/lib_autolink)

sabre/dav in (/srv/glpi/vendor/sabre/dav/lib/DAV)

sabre/http in (/srv/glpi/vendor/sabre/http/lib)

sabre/uri in (/srv/glpi/vendor/sabre/uri/lib)

sabre/vobject in (/srv/glpi/vendor/sabre/vobject/lib)

laminas/laminas-i18n in (/srv/glpi/vendor/laminas/laminas-i18n/src)

laminas/laminas-servicemanager in (/srv/glpi/vendor/laminas/laminas-servicemanager/src)

monolog/monolog in (/srv/glpi/vendor/monolog/monolog/src/Monolog)

sebastian/diff in (/srv/glpi/vendor/sebastian/diff/src)

donatj/phpuseragentparser in (/srv/glpi/vendor/donatj/phpuseragentparser/src/UserAgent)

elvanto/litemoji in (/srv/glpi/vendor/elvanto/litemoji/src)

symfony/console in (/srv/glpi/vendor/symfony/console)

scssphp/scssphp in (/srv/glpi/vendor/scssphp/scssphp/src)

laminas/laminas-mail in (/srv/glpi/vendor/laminas/laminas-mail/src/Protocol)

laminas/laminas-mime in (/srv/glpi/vendor/laminas/laminas-mime/src)

rlanvin/php-rrule in (/srv/glpi/vendor/rlanvin/php-rrule/src)

ramsey/uuid in (/srv/glpi/vendor/ramsey/uuid/src)

psr/log in (/srv/glpi/vendor/psr/log/Psr/Log)

psr/simple-cache in (/srv/glpi/vendor/psr/simple-cache/src)

psr/cache in (/srv/glpi/vendor/psr/cache/src)

league/csv in (/srv/glpi/vendor/league/csv/src)

mexitek/phpcolors in (/srv/glpi/vendor/mexitek/phpcolors/src/Mexitek/PHPColors)

guzzlehttp/guzzle in (/srv/glpi/vendor/guzzlehttp/guzzle/src)

guzzlehttp/psr7 in (/srv/glpi/vendor/guzzlehttp/psr7/src)

glpi-project/inventory_format in (/srv/glpi/vendor/glpi-project/inventory_format/lib/php)

wapmorgan/unified-archive in (/srv/glpi/vendor/wapmorgan/unified-archive/src)

paragonie/sodium_compat in (/srv/glpi/vendor/paragonie/sodium_compat/src)

symfony/cache in (/srv/glpi/vendor/symfony/cache)

html2text/html2text in (/srv/glpi/vendor/html2text/html2text/src)

symfony/css-selector in (/srv/glpi/vendor/symfony/css-selector)

symfony/dom-crawler in (/srv/glpi/vendor/symfony/dom-crawler)

twig/twig in (/srv/glpi/vendor/twig/twig/src)

twig/string-extra in (/srv/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 (/srv/glpi/vendor/symfony/polyfill-php82)

league/oauth2-client in (/srv/glpi/vendor/league/oauth2-client/src/Provider)

league/oauth2-google in (/srv/glpi/vendor/league/oauth2-google/src/Provider)

thenetworg/oauth2-azure in (/srv/glpi/vendor/thenetworg/oauth2-azure/src/Provider)

phpCas version 1.6.0 in (/usr/share/php/CAS/source)

SQL replicas

Not active

Notifications

Way of sending emails: PHP

Plugins list

glpiinventory Name: GLPI Inventory Version: 1.3.5 State: Enabled
Install Method: Manual

Anything else?

No response

trasher · 2024-04-26T10:04:40Z

To reproduce from tests suite:

diff --git a/tests/functional/Glpi/Inventory/Inventory.php b/tests/functional/Glpi/Inventory/Inventory.php
index 95c576353f..5ed5d1f78a 100644
--- a/tests/functional/Glpi/Inventory/Inventory.php
+++ b/tests/functional/Glpi/Inventory/Inventory.php
@@ -4931,7 +4931,7 @@ Compiled Tue 28-Sep-10 13:44 by prod_rel_team",
         $services = [$mysql];
         $json->content->databases_services = $services;
 
-        $this->doInventory($json);
+        $inventory = $this->doInventory($json);
 
         //check created databases & instances
         $this->integer(countElementsInTable(\DatabaseInstance::getTable(), ['is_deleted' => 0]))->isIdenticalTo(1);
@@ -4951,6 +4951,17 @@ Compiled Tue 28-Sep-10 13:44 by prod_rel_team",
         $this->array(array_pop($databases))
             ->string['name']->isIdenticalTo('glpi')
             ->integer['size']->isIdenticalTo(55000);
+
+        $computer = new \Computer();
+        global $DB;
+        $agents = $DB->request(['FROM' => \Agent::getTable()]);
+        $this->integer(count($agents))->isIdenticalTo(1);
+        $agent = $agents->current();
+        $computers_id = $agent['items_id'];
+        $this->boolean($computer->getFromDB($computers_id))->isTrue();
+        $this->boolean($computer->delete(['id' => $computers_id], true))->isTrue();
     }

I've not been able to find how to fix. To me, removing data in Computer::pre_deleteItem() should have done the trick, but it did not.

cedric-anne · 2024-04-29T11:58:57Z

When creating a databse instance from the UI, without selecting any Item type/Item, the itemtype value stored in DB is 0. It whould probably be null in this case. Anyway, the problem is that this field is not nullable, but it should probably be nullable, as the value is not mandatory.

trasher · 2024-04-29T12:27:13Z

Not sure null is a correct solution. Having database instance back from inventory would probably create a new record; in that case, it may be better to remove instance when attached computer is purged.
That kind of case must probably be added in tests so we know hos it works.

trasher added this to the 10.0.16 milestone Apr 26, 2024

trasher changed the title ~~Found SQL error in sql-erros.log after computer deletion via massive action~~ Found SQL error in sql-erros.log after computer deletion Apr 26, 2024

cedric-anne added the bug label Apr 29, 2024

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Found SQL error in sql-erros.log after computer deletion #17019

Found SQL error in sql-erros.log after computer deletion #17019

g-bougard commented Apr 26, 2024 •

edited

trasher commented Apr 26, 2024 •

edited

cedric-anne commented Apr 29, 2024

trasher commented Apr 29, 2024

Found SQL error in sql-erros.log after computer deletion #17019

Found SQL error in sql-erros.log after computer deletion #17019

Comments

g-bougard commented Apr 26, 2024 • edited

Code of Conduct

Is there an existing issue for this?

Version

Bug description

Relevant log output

Page URL

Steps To reproduce

Your GLPI setup information

Anything else?

trasher commented Apr 26, 2024 • edited

cedric-anne commented Apr 29, 2024

trasher commented Apr 29, 2024

g-bougard commented Apr 26, 2024 •

edited

trasher commented Apr 26, 2024 •

edited