Erreur 500 entre agent et serveur OCS. Problème au niveau de la BD?

[thenamelessthing]

It's hard to solve a problem when important details are missing, that why we added this template, to help you and us.

General informations

Operating system : CentOS 7

Server informations

Perl version : v5.16.3
Mysql / Mariadb / Percona version : 10.2.31-MariaDB

OCS Inventory informations

Ocs server version : 2.12.1

Problem's description

Bon voilà, nous avons plusieurs appareils dont l'agent ne réussi plus à communiquer avec notre serveur OCS. Nous obtenons une erreur 500 dans les logs au niveau de l'agent. (les agents sont en version 2.9.0 ou 2.9.2. Peut-être même en 2.10.1 à confirmer.)

ERROR *** AGENT => Failed to send Inventory <HTTP Status Code #500>

Après beaucoup de recherche, et j'ai finalement décidé de demander directement à vous. Nous avons activé le mode débogue sur le serveur et dans le fichier activity.log nous avons l'erreur suivante (est-ce lié, je ne sais pas):

DBD::mysql::st execute failed: Out of range value for column 'ID' at row 1 at /usr/local/share/perl5/Apache/Ocsinventory/Server/Inventory/Update.pm line 173.

et parfois l'erreur:

DBD::mysql::st execute failed: Table 'ocsweb.softwares' doesn't exist at /usr/local/share/perl5/Apache/Ocsinventory/Server/Groups.pm line 165

Hier, j'ai supprimé un appareil qui ne synchronisait plus sur le serveur, j'ai réinstallé l'agent et cela c'est mis à fonctionner correctement.

J'ai également remarqué que nous avions quelques dizaines d'appareils dans l'inventaire dont le contenu était vide (nom d'ordinateur, etc)

J'ai donc supprimé ces entrées.

Au départ, cela me faisait penser à un ou des tables qui sont pleines. du moins la colonne "max(id)", mais je ne suis pas certain. Voici le résultat, peut-être cela pourra-t-il vous aider à identifier la cause?

`MariaDB [ocsweb]> SELECT DISTINCT TABLE_NAME,DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME IN ('ID') AND TABLE_SCHEMA='ocsweb';
+---------------------------+-----------+
| TABLE_NAME | DATA_TYPE |
+---------------------------+-----------+
| accesslog | int |
| accountinfo_config | int |
| archive | int |
| assets_categories | int |
| auth_attempt | int |
| batteries | int |
| bitlockerstatus | int |
| blacklist_macaddresses | int |
| blacklist_serials | int |
| blacklist_subnet | int |
| checkfiles | int |
| controllers | int |
| cpus | int |
| customapp | int |
| cve_search | int |
| cve_search_computer | int |
| cve_search_correspondance | int |
| cve_search_history | int |
| deleted_equiv | int |
| devices | int |
| devicetype | int |
| download_affect_rules | int |
| download_enable | int |
| downloadwk_conf_values | int |
| downloadwk_fields | int |
| downloadwk_history | int |
| downloadwk_pack | int |
| downloadwk_statut_request | int |
| downloadwk_tab_values | int |
| drives | int |
| engine_persistent | int |
| extensions | varchar |
| hardware | int |
| hardware_osname_cache | int |
| history | bigint |
| inputs | int |
| itmgmt_comments | int |
| journallog | int |
| layouts | bigint |
| listprinters | int |
| local_groups | int |
| local_users | int |
| locks | int |
| memories | int |
| modems | int |
| monitors | int |
| network_devices | int |
| networks | int |
| notification | int |
| notification_config | int |
| officepack | int |
| operators | varchar |
| ports | int |
| printers | int |
| prolog_conntrack | int |
| regconfig | int |
| registry | int |
| registry_name_cache | int |
| registry_regvalue_cache | int |
| reports_notifications | bigint |
| repository | int |
| saas | int |
| saas_exp | int |
| save_query | int |
| schedule_wol | int |
| securitycenter | int |
| service | int |
| sim | int |
| slots | int |
| snmp_accountinfo | int |
| snmp_cards | int |
| snmp_cartridges | int |
| snmp_communities | int |
| snmp_configs | int |
| snmp_cpus | int |
| snmp_default | int |
| snmp_drives | int |
| snmp_labels | int |
| snmp_mibs | int |
| snmp_types | int |
| snmp_types_conditions | int |
| software | bigint |
| software_categories | int |
| software_categories_link | bigint |
| software_category_exp | int |
| software_link | bigint |
| software_name | int |
| software_publisher | int |
| software_version | int |
| softwares_name_cache | int |
| sounds | int |
| ssl_store | int |
| storages | int |
| subnet | varchar |
| teamviewer | int |
| temp_files | int |
| usbdevices | int |
| videos | int |
| virtualmachines | int |
| winusers | int |
+---------------------------+-----------+
100 rows in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from accesslog;
+---------+
| max(id) |
+---------+
| NULL |
+---------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from accountinfo_config;
+---------+
| max(id) |
+---------+
| 2 |
+---------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from archive;
+---------+
| max(id) |
+---------+
| 455 |
+---------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from assets_categories;
+---------+
| max(id) |
+---------+
| NULL |
+---------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from auth_attempt;
+---------+
| max(id) |
+---------+
| NULL |
+---------+
1 row in set (0.02 sec)

MariaDB [ocsweb]> select max(id) from batteries;
+---------+
| max(id) |
+---------+
| NULL |
+---------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from bitlockerstatus;
+---------+
| max(id) |
+---------+
| 7525883 |
+---------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from blacklist_macaddresses;
+---------+
| max(id) |
+---------+
| 14 |
+---------+
1 row in set (0.02 sec)

MariaDB [ocsweb]> select max(id) from blacklist_serials;
+---------+
| max(id) |
+---------+
| 34 |
+---------+
1 row in set (0.04 sec)

MariaDB [ocsweb]> select max(id) from blacklist_subnet;
+---------+
| max(id) |
+---------+
| NULL |
+---------+
1 row in set (0.03 sec)

MariaDB [ocsweb]> select max(id) from checkfiles;
+----------+
| max(id) |
+----------+
| 20298090 |
+----------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from controllers;
+---------+
| max(id) |
+---------+
| 1417567 |
+---------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from cpus;
+---------+
| max(id) |
+---------+
| 375154 |
+---------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from customapp;
+---------+
| max(id) |
+---------+
| 6708173 |
+---------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from cve_search;
+---------+
| max(id) |
+---------+
| 104354 |
+---------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from cve_search_computer;
+---------+
| max(id) |
+---------+
| NULL |
+---------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from cve_search_correspondance;
+---------+
| max(id) |
+---------+
| NULL |
+---------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from cve_search_history;
+---------+
| max(id) |
+---------+
| 2646 |
+---------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from deleted_equiv;
+---------+
| max(id) |
+---------+
| 87561 |
+---------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from devices;
+---------+
| max(id) |
+---------+
| 72939 |
+---------+
1 row in set (0.01 sec)

MariaDB [ocsweb]> select max(id) from devicetype;
+---------+
| max(id) |
+---------+
| NULL |
+---------+
1 row in set (0.04 sec)

MariaDB [ocsweb]> select max(id) from downloadwk_conf_values;
+---------+
| max(id) |
+---------+
| NULL |
+---------+
1 row in set (0.02 sec)

MariaDB [ocsweb]> select max(id) from downloadwk_fields;
+---------+
| max(id) |
+---------+
| 10 |
+---------+
1 row in set (0.02 sec)

MariaDB [ocsweb]> select max(id) from downloadwk_history;
+---------+
| max(id) |
+---------+
| NULL |
+---------+
1 row in set (0.11 sec)

MariaDB [ocsweb]> select max(id) from downloadwk_pack;
+---------+
| max(id) |
+---------+
| NULL |
+---------+
1 row in set (0.04 sec)

MariaDB [ocsweb]> select max(id) from downloadwk_statut_request;
+---------+
| max(id) |
+---------+
| 9 |
+---------+
1 row in set (0.01 sec)

MariaDB [ocsweb]> select max(id) from downloadwk_tab_values;
+---------+
| max(id) |
+---------+
| 5 |
+---------+
1 row in set (0.01 sec)

MariaDB [ocsweb]> select max(id) from download_affect_rules;
+---------+
| max(id) |
+---------+
| NULL |
+---------+
1 row in set (0.01 sec)

MariaDB [ocsweb]> select max(id) from download_enable;
+---------+
| max(id) |
+---------+
| 175 |
+---------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from drives;
+----------+
| max(id) |
+----------+
| 11122717 |
+----------+
1 row in set (0.01 sec)

MariaDB [ocsweb]> select max(id) from engine_persistent;
+---------+
| max(id) |
+---------+
| 2 |
+---------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from extensions;
+----------+
| max(id) |
+----------+
| winusers |
+----------+
1 row in set (0.06 sec)

MariaDB [ocsweb]> select max(id) from hardware;
+---------+
| max(id) |
+---------+
| 335385 |
+---------+
1 row in set (0.02 sec)

MariaDB [ocsweb]> select max(id) from hardware_osname_cache;
+---------+
| max(id) |
+---------+
| 24826 |
+---------+
1 row in set (0.01 sec)

MariaDB [ocsweb]> select max(id) from history;
+---------+
| max(id) |
+---------+
| 315 |
+---------+
1 row in set (0.07 sec)

MariaDB [ocsweb]> select max(id) from inputs;
+---------+
| max(id) |
+---------+
| 1805400 |
+---------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from itmgmt_comments;
+---------+
| max(id) |
+---------+
| 1 |
+---------+
1 row in set (0.01 sec)

MariaDB [ocsweb]> select max(id) from journallog;
+---------+
| max(id) |
+---------+
| NULL |
+---------+
1 row in set (0.01 sec)

MariaDB [ocsweb]> select max(id) from layouts;
+---------+
| max(id) |
+---------+
| NULL |
+---------+
1 row in set (0.02 sec)

MariaDB [ocsweb]> select max(id) from listprinters;
+----------+
| max(id) |
+----------+
| 76750942 |
+----------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from local_groups;
+---------+
| max(id) |
+---------+
| 946498 |
+---------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from local_users;
+---------+
| max(id) |
+---------+
| 760844 |
+---------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from locks;
+---------+
| max(id) |
+---------+
| NULL |
+---------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from memories;
+---------+
| max(id) |
+---------+
| 167431 |
+---------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from modems;
+---------+
| max(id) |
+---------+
| 121180 |
+---------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from monitors;
+---------+
| max(id) |
+---------+
| 370856 |
+---------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from networks;
+---------+
| max(id) |
+---------+
| 2977360 |
+---------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from network_devices;
+---------+
| max(id) |
+---------+
| NULL |
+---------+
1 row in set (0.01 sec)

MariaDB [ocsweb]> select max(id) from notification;
+---------+
| max(id) |
+---------+
| 3 |
+---------+
1 row in set (0.01 sec)

MariaDB [ocsweb]> select max(id) from notification_config;
+---------+
| max(id) |
+---------+
| NULL |
+---------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from officepack;
+----------+
| max(id) |
+----------+
| 10013863 |
+----------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from operators;
+---------+
| max(id) |
+---------+
| wilsar |
+---------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from ports;
+---------+
| max(id) |
+---------+
| 2499553 |
+---------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from printers;
+---------+
| max(id) |
+---------+
| 1251277 |
+---------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from prolog_conntrack;
+---------+
| max(id) |
+---------+
| 8348596 |
+---------+
1 row in set (0.01 sec)

MariaDB [ocsweb]> select max(id) from regconfig;
+---------+
| max(id) |
+---------+
| 5 |
+---------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from registry;
+---------+
| max(id) |
+---------+
| 170199 |
+---------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from registry_name_cache;
+---------+
| max(id) |
+---------+
| 4924 |
+---------+
1 row in set (0.01 sec)

MariaDB [ocsweb]> select max(id) from registry_regvalue_cache;
+---------+
| max(id) |
+---------+
| 11753 |
+---------+
1 row in set (0.01 sec)

MariaDB [ocsweb]> select max(id) from reports_notifications;
+---------+
| max(id) |
+---------+
| NULL |
+---------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from repository;
+---------+
| max(id) |
+---------+
| NULL |
+---------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from saas;
+---------+
| max(id) |
+---------+
| NULL |
+---------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from saas_exp;
+---------+
| max(id) |
+---------+
| NULL |
+---------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from save_query;
+---------+
| max(id) |
+---------+
| 42 |
+---------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from schedule_wol;
+---------+
| max(id) |
+---------+
| NULL |
+---------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from securitycenter;
+----------+
| max(id) |
+----------+
| 28490844 |
+----------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from service;
+------------+
| max(id) |
+------------+
| 2147483373 |
+------------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from sim;
+---------+
| max(id) |
+---------+
| NULL |
+---------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from slots;
+---------+
| max(id) |
+---------+
| 1440774 |
+---------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from snmp_accountinfo;
+---------+
| max(id) |
+---------+
| NULL |
+---------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from snmp_cards;
+---------+
| max(id) |
+---------+
| NULL |
+---------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from snmp_cartridges;
+---------+
| max(id) |
+---------+
| NULL |
+---------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from snmp_communities;
+---------+
| max(id) |
+---------+
| NULL |
+---------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from snmp_configs;
+---------+
| max(id) |
+---------+
| 23 |
+---------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from snmp_cpus;
+---------+
| max(id) |
+---------+
| NULL |
+---------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from snmp_default;
+---------+
| max(id) |
+---------+
| NULL |
+---------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from snmp_drives;
+---------+
| max(id) |
+---------+
| NULL |
+---------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from snmp_labels;
+---------+
| max(id) |
+---------+
| 12 |
+---------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from snmp_mibs;
+---------+
| max(id) |
+---------+
| NULL |
+---------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from snmp_types;
+---------+
| max(id) |
+---------+
| 2 |
+---------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from snmp_types_conditions;
+---------+
| max(id) |
+---------+
| 3 |
+---------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from software;
+-----------+
| max(id) |
+-----------+
| 518554286 |
+-----------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from softwares_name_cache;
+---------+
| max(id) |
+---------+
| 1020796 |
+---------+
1 row in set (0.01 sec)

MariaDB [ocsweb]> select max(id) from software_categories;
+---------+
| max(id) |
+---------+
| NULL |
+---------+
1 row in set (0.01 sec)

MariaDB [ocsweb]> select max(id) from software_categories_link;
+---------+
| max(id) |
+---------+
| NULL |
+---------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from software_category_exp;
+---------+
| max(id) |
+---------+
| NULL |
+---------+
1 row in set (0.01 sec)

MariaDB [ocsweb]> select max(id) from software_link;
+-----------+
| max(id) |
+-----------+
| 151012641 |
+-----------+
1 row in set (0.01 sec)

MariaDB [ocsweb]> select max(id) from software_name;
+---------+
| max(id) |
+---------+
| 402128 |
+---------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from software_publisher;
+---------+
| max(id) |
+---------+
| 19958 |
+---------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from software_version;
+---------+
| max(id) |
+---------+
| 250371 |
+---------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from sounds;
+---------+
| max(id) |
+---------+
| 1657609 |
+---------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from ssl_store;
+---------+
| max(id) |
+---------+
| NULL |
+---------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from storages;
+---------+
| max(id) |
+---------+
| 170868 |
+---------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from subnet;
+---------+
| max(id) |
+---------+
| NULL |
+---------+
1 row in set (0.01 sec)

MariaDB [ocsweb]> select max(id) from teamviewer;
+---------+
| max(id) |
+---------+
| 392850 |
+---------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from temp_files;
+---------+
| max(id) |
+---------+
| NULL |
+---------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from usbdevices;
+---------+
| max(id) |
+---------+
| NULL |
+---------+
1 row in set (0.01 sec)

MariaDB [ocsweb]> select max(id) from videos;
+---------+
| max(id) |
+---------+
| 139869 |
+---------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from virtualmachines;
+---------+
| max(id) |
+---------+
| NULL |
+---------+
1 row in set (0.00 sec)

MariaDB [ocsweb]> select max(id) from winusers;
+---------+
| max(id) |
+---------+
| 6686860 |
+---------+
1 row in set (0.00 sec)`

Merci pour votre aide.

[thenamelessthing]

On a aussi ces info:

[gillesdubois]

Bonjour,

Il faut que vous passiez les tables ayant 2147483648 entrées en bigint AUTOINCREMENT. En effet, celles-ci sont a la limite du int(11) que nous mettons dans le schéma de la version communautaire.

C'est une problématique assez courante sur les logiciels et les plugins.

Cordialement,
Gilles DUBOIS.

[thenamelessthing]

@gillesdubois, enfin une explication claire et tangible! Merci pour votre retour rapide. Je vais effectuer la correction en début de semaine.

Cordialement,

[thenamelessthing]

@gillesdubois ,je vous confirme que d'avoir passer la champ à BIGINT à résolu ce problème. Maintenant nous remarquons qu'au travers des erreurs. Nous avions également celle-ci:

DBD::mysql::st execute failed: Table 'ocsweb.softwares' doesn't exist at /usr/local/share/perl5/Apache/Ocsinventory/Server/Groups.pm line 165

Comment pouvons-nous la résoudre?

Merci

[gillesdubois]

Bonjour,

Nous avons changé le nom de la table software il y a plusieurs versions.
Assurez-vous que la partie communication du serveur est bien à jour.

Vous pouvez chercher le fichier Ocsinventory.pm sur votre système et voir les premières lignes du fichier.
Celui-ci vous indiquera si vous êtes dans la bonne version.

Gilles DUBOIS.

[thenamelessthing]

Vous pouvez chercher le fichier Ocsinventory.pm sur votre système et voir les premières lignes du fichier. Celui-ci vous indiquera si vous êtes dans la bonne version.
@gillesdubois voici le contenu, nous semblons pourtant à la plus récente version du fichier Ocsinventory.pm

[charleneauger]

Bonjour @thenamelessthing ,

Je remarque que l'erreur provient du fichier Group.pm.
Il se peut que vous ayez des groupes qui utilisent encore l'ancienne table softwares.

Cordialement,
Charlène

[thenamelessthing]

Bonjour @thenamelessthing ,

Je remarque que l'erreur provient du fichier Group.pm. Il se peut que vous ayez des groupes qui utilisent encore l'ancienne table softwares.

Cordialement, Charlène

Merci, effectivement bien vu! Je n'avais pas fait le lien. Mais après avoir regarder parmi mes groupes dynamiques. J'ai constaté que sur certains. Mes requêtes SQL ne fonctionnaient plus. J'ai donc détruit et recréé ces groupes.

Dernière question, je n'ai pas eu connaissance d'un quelconque avertissements lorsqu'il y a eu un changement au niveau de la table software. Il serait peut-être bien d'indiquer un message d'avertissement du genre: cette requête utilise une ancienne version de la table XX. Vous devrez recréer votre requête pour prendre en charge la nouvelle table YY. ?

Merci encore pour votre aide.

[charleneauger]

Bonjour @thenamelessthing ,

En effet, lorsque nous avons effectué la modification de la structure de la table nous n'avons pas précisé cette information.
A noter qu'un changement de structure aussi conséquent reste un cas exceptionnel.

Si cela se reproduit, nous essaierons d'être un peu plus communiquant quand aux démarches à suivre, surtout si cela impacte une partie annexe de l'application.

Le sujet initial étant résolu, je me permet de clore l'issue. :-)

Cordialement,
Charlène