search

fusioninventory / fusioninventory-for-glpi

FusionInventory plugin for GLPI
http://www.FusionInventory.org/
GNU Affero General Public License v3.0
362 stars 150 forks source link

9.4 Sql Errors in Network Discovery (inc/inventoryruleimport.class.php) #2772

Open FranciscoVLopes opened 5 years ago

FranciscoVLopes commented 5 years ago

Hi, After upgranding GLPI (9.3.3->9.4.0) and Fusion Inventory (9.3+1.3->9.4.0+1.0), i have several sql errors (and php errors) in network discovery.

[2019-02-22 10:57:29] glpisqllog.ERROR: DBmysql::query() in /u01/apps/glpi/product/glpi-9.4.0/inc/dbmysql.class.php line 177 *** MySQL query error: SQL: SELECT glpi_lines.id FROM glpi_lines WHERE glpi_lines.is_template = '0' AND (glpi_lines.name='172') GROUP BY glpi_lines.id ORDER BY glpi_lines.is_deleted ASC LIMIT 1 Error: Unknown column 'glpi_lines.is_template' in 'where clause' Backtrace : ...inventory/inc/inventoryruleimport.class.php:695 inc/rule.class.php:1563 PluginFusioninventoryInventoryRuleImport->findWithGlobalCriteria() inc/rule.class.php:1429 Rule->checkCriterias() inc/rulecollection.class.php:1539 Rule->process() ...inc/communicationnetworkdiscovery.class.php:233 RuleCollection->processAllRules() ...ventory/inc/snmpmodelimportexport.class.php:106 PluginFusioninventoryCommunicationNetworkDiscovery->sendCriteria() ...inc/communicationnetworkdiscovery.class.php:108 PluginFusioninventorySnmpmodelImportExport->import_netdiscovery() ...fusioninventory/inc/communication.class.php:229 PluginFusioninventoryCommunicationNetworkDiscovery->import() ...fusioninventory/inc/communication.class.php:461 PluginFusioninventoryCommunication->import() plugins/fusioninventory/front/communication.php:93 PluginFusioninventoryCommunication->handleOCSCommunication() plugins/fusioninventory/index.php:59 include_once()

[2019-02-22 10:57:29] glpisqllog.ERROR: DBmysql::query() in /u01/apps/glpi/product/glpi-9.4.0/inc/dbmysql.class.php line 177 *** MySQL query error: SQL: SELECT glpi_softwareversions.id FROM glpi_softwareversions WHERE glpi_softwareversions.is_template = '0' AND (glpi_softwareversions.name='172') GROUP BY glpi_softwareversions.id ORDER BY glpi_softwareversions.is_deleted ASC LIMIT 1 Error: Unknown column 'glpi_softwareversions.is_template' in 'where clause' Backtrace : ...inventory/inc/inventoryruleimport.class.php:695 inc/rule.class.php:1563 PluginFusioninventoryInventoryRuleImport->findWithGlobalCriteria() inc/rule.class.php:1429 Rule->checkCriterias() inc/rulecollection.class.php:1539 Rule->process() ...inc/communicationnetworkdiscovery.class.php:233 RuleCollection->processAllRules() ...ventory/inc/snmpmodelimportexport.class.php:106 PluginFusioninventoryCommunicationNetworkDiscovery->sendCriteria() ...inc/communicationnetworkdiscovery.class.php:108 PluginFusioninventorySnmpmodelImportExport->import_netdiscovery() ...fusioninventory/inc/communication.class.php:229 PluginFusioninventoryCommunicationNetworkDiscovery->import() ...fusioninventory/inc/communication.class.php:461 PluginFusioninventoryCommunication->import() plugins/fusioninventory/front/communication.php:93 PluginFusioninventoryCommunication->handleOCSCommunication() plugins/fusioninventory/index.php:59 include_once()

Rules with "is already present in GLPI" criteria.

Thanks in advance

Stoatwblr commented 5 years ago

What this is attempting to do is exclude templates from the lookup.

However looking at the bare sql table in glpi/install/mysql, there is no "glpi_softwareversions.is_template"

However, there IS "glpi_computers_softwareversions.is_template" also glpi_softwarelicenses.is_template and glpi_softwares.is_template

there is nothing resembling a glpi_lines (anything) with an is_template column.

I suspect that the "is_template" exclusions are spurious.

Stoatwblr commented 5 years ago

it looks like the culprit is this block around inc/inventoryruleimport.class.php:695 which is checking for itemtype in foo whilst also assuming that foo.is_template always exists.

It's unsafe because it's going to always give false negatives or false positives depending how it's wired to respond to the error outcome.

    $item = new $itemtype();
     $sql_glpi = "SELECT `[typetable]`.`id`
                  FROM $sql_from_temp
                  WHERE $sql_where_temp
                  GROUP BY `[typetable]`.`id`
                  ORDER BY `[typetable]`.`is_deleted` ASC
                  LIMIT 1";
     if (strstr($sql_glpi, "`[typetable]`.`is_template` = '0'  AND")) {

        if ($itemtype == "PluginFusioninventoryUnmanaged") {
           $sql_glpi = str_replace("`[typetable]`.`is_template` = '0'  AND", "", $sql_glpi);
        }
        $sql_glpi = str_replace("[typetable]", $item->getTable(), $sql_glpi);
        $sql_glpi = str_replace("[typename]", $itemtype, $sql_glpi);
           $sql_glpi = str_replace("[typenamefortable]", strtolower($itemtype), $sql_glpi);
mathieumd commented 5 years ago

We have the same problem with GLPI 9.4.2 and FusionInventory 9.4+1.1.

Any progress on a fix?

adamcios commented 5 years ago

I have the same problem with GLPI 9.4.2 and FusionInventory 9.4+1.1. glpisqllog.ERROR: DBmysql::query() in /home/www/glpi/inc/dbmysql.class.php line 177 ** MySQL query error: SQL: SELECT COUNT() AS cpt FROM glpi_itils_projects WHERE itemtype = 'Ticket' AND items_id = '538' Error: Unknown column 'items_id' in 'where clause' Any suggestions?

Rico29 commented 4 years ago

Hello. this ticket is open for +1year and the issue is not fixed. @Stoatwblr Any hint / update ?