Recieving DBD::mysql::db do failed: Duplicate entry errors

TheFiZi commented 3 years ago

General informations

Operating system : CentOS Linux release 7.9.2009 (Core)

Server informations

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

OCS Inventory informations

Ocs server version : 2.9.1

Problem's description

Cannot comment on when this started since we only started logging database errors since upgrading to 2.9.1.

I am getting random entries like this popping up:

DBD::mysql::db do failed: Duplicate entry '312427' for key 'PRIMARY' at /usr/local/share/perl5/Apache/Ocsinventory/Server/System.pm line 246.
DBD::mysql::db do failed: Duplicate entry '14999' for key 'PRIMARY' at /usr/local/share/perl5/Apache/Ocsinventory/Server/System.pm line 246.
DBD::mysql::db do failed: Duplicate entry '312427' for key 'PRIMARY' at /usr/local/share/perl5/Apache/Ocsinventory/Server/System.pm line 246.
DBD::mysql::db do failed: Duplicate entry '14999' for key 'PRIMARY' at /usr/local/share/perl5/Apache/Ocsinventory/Server/System.pm line 246.
DBD::mysql::db do failed: Duplicate entry '312427' for key 'PRIMARY' at /usr/local/share/perl5/Apache/Ocsinventory/Server/System.pm line 246.
DBD::mysql::db do failed: Duplicate entry '14999' for key 'PRIMARY' at /usr/local/share/perl5/Apache/Ocsinventory/Server/System.pm line 246.
DBD::mysql::db do failed: Duplicate entry '14999' for key 'PRIMARY' at /usr/local/share/perl5/Apache/Ocsinventory/Server/System.pm line 246.
DBD::mysql::db do failed: Duplicate entry '312427' for key 'PRIMARY' at /usr/local/share/perl5/Apache/Ocsinventory/Server/System.pm line 246.
DBD::mysql::db do failed: Duplicate entry '313013' for key 'PRIMARY' at /usr/local/share/perl5/Apache/Ocsinventory/Server/System.pm line 246.
DBD::mysql::db do failed: Duplicate entry '69972' for key 'PRIMARY' at /usr/local/share/perl5/Apache/Ocsinventory/Server/System.pm line 246.
DBD::mysql::db do failed: Duplicate entry '14999' for key 'PRIMARY' at /usr/local/share/perl5/Apache/Ocsinventory/Server/System.pm line 246.

Lea9250 commented 2 years ago

Hi @TheFiZi,

Can I ask how did you perform the update to the 2.9.1 version ?

Regards, Léa

TheFiZi commented 2 years ago

@Lea9250 This problem was something I found in the logs when checking out our system after a 2.9.1 upgrade.

To perform the upgrade I just followed the documentation provided by OCS: https://wiki.ocsinventory-ng.org/03.Basic-documentation/Updating-the-server/

Lea9250 commented 2 years ago

Hi @TheFiZi, What does you activity.log say ? Is there any line referencing a lock situation ?

Thanks, Léa

TheFiZi commented 2 years ago

The activity.log on that day has 42 entries that look like this:

Fri Jul 30 16:24:59 2021;6322;516;210-340-2046-2018-10-24-22-37-34;10.1.80.20;OCS-NG_WINDOWS_AGENT_v2.3.1.1;inventory;device_locked

Some are repeats for the same device.

Lea9250 commented 2 years ago

Would it be possible to see the other log entries too ?

Thanks, Léa

TheFiZi commented 2 years ago

@Lea9250 How far back do you want? The log goes back over a year I think.

Lea9250 commented 2 years ago

Hi @TheFiZi,

Maybe get a day where the issue did happen if possible, so I can check if everything seems right on the logs side. I'm trying to understand why would so much of these entries suddenly show up.

Thanks, Léa

TheFiZi commented 2 years ago

@Lea9250 Sorry for the delay. Here is the log for the entire day.

activity.log

jjmg84 commented 2 years ago

Hi, same here :(

DBD::mysql::db do failed: Duplicate entry 'INVENTORY_CACHE_REVALIDATE-ALL' for key 'PRIMARY' at /usr/local/share/perl/5.30.0/Apache/Ocsinventory/Server/Inventory/Cache.pm line 133. DBD::mysql::db do failed: Duplicate entry '8285' for key 'PRIMARY' at /usr/local/share/perl/5.30.0/Apache/Ocsinventory/Server/Inventory/Update/AccountInfos.pm line 66. DBD::mysql::db do failed: Duplicate entry 'INVENTORY_CACHE_REVALIDATE-ALL' for key 'PRIMARY' at /usr/local/share/perl/5.30.0/Apache/Ocsinventory/Server/Inventory/Cache.pm line 133. DBD::mysql::db do failed: Duplicate entry '1707' for key 'PRIMARY' at /usr/local/share/perl/5.30.0/Apache/Ocsinventory/Server/Inventory/Update/AccountInfos.pm line 66. DBD::mysql::db do failed: Duplicate entry 'INVENTORY_CACHE_REVALIDATE-ALL' for key 'PRIMARY' at /usr/local/share/perl/5.30.0/Apache/Ocsinventory/Server/Inventory/Cache.pm line 133. DBD::mysql::db do failed: Duplicate entry '1942' for key 'PRIMARY' at /usr/local/share/perl/5.30.0/Apache/Ocsinventory/Server/Inventory/Update/AccountInfos.pm line 66.

Update:

have tryed to update to 2.9.2 and same error, disabled all plugins and same errors...

thanks!

ScriptHash commented 2 years ago

hello, the same here

DBD::mysql::db do failed: Duplicate entry '331' for key 'PRIMARY' at /usr/local/share/perl/5.28.1/Apache/Ocsinventory/Server/Inventory/Update/AccountInfos.pm line 66. DBD::mysql::db do failed: Duplicate entry '323' for key 'PRIMARY' at /usr/local/share/perl/5.28.1/Apache/Ocsinventory/Server/Inventory/Update/AccountInfos.pm line 66. DBD::mysql::db do failed: Duplicate entry '143' for key 'PRIMARY' at /usr/local/share/perl/5.28.1/Apache/Ocsinventory/Server/Inventory/Update/AccountInfos.pm line 66. DBD::mysql::db do failed: Duplicate entry '144' for key 'PRIMARY' at /usr/local/share/perl/5.28.1/Apache/Ocsinventory/Server/Inventory/Update/AccountInfos.pm line 66. DBD::mysql::db do failed: Duplicate entry '263' for key 'PRIMARY' at /usr/local/share/perl/5.28.1/Apache/Ocsinventory/Server/Inventory/Update/AccountInfos.pm line 66. DBD::mysql::db do failed: Duplicate entry '89' for key 'PRIMARY' at /usr/local/share/perl/5.28.1/Apache/Ocsinventory/Server/Inventory/Update/AccountInfos.pm line 66. DBD::mysql::db do failed: Duplicate entry '140' for key 'PRIMARY' at /usr/local/share/perl/5.28.1/Apache/Ocsinventory/Server/Inventory/Update/AccountInfos.pm line 66. DBD::mysql::db do failed: Duplicate entry '234' for key 'PRIMARY' at /usr/local/share/perl/5.28.1/Apache/Ocsinventory/Server/Inventory/Update/AccountInfos.pm line 66. DBD::mysql::db do failed: Duplicate entry '155' for key 'PRIMARY' at /usr/local/share/perl/5.28.1/Apache/Ocsinventory/Server/Inventory/Update/AccountInfos.pm line 66. DBD::mysql::db do failed: Duplicate entry '134' for key 'PRIMARY' at /usr/local/share/perl/5.28.1/Apache/Ocsinventory/Server/Inventory/Update/AccountInfos.pm line 66.

Serverversion is the git version.

jjmg84 commented 2 years ago

Hi, 2.10 and same errors :(

DBD::mysql::db do failed: Duplicate entry 'SESSION-CLEAN' for key 'PRIMARY' at /usr/local/share/perl/5.30.0/Apache/Ocsinventory/Server/Communication/Session.pm line 59. DBD::mysql::db do failed: Duplicate entry 'SESSION-CLEAN' for key 'PRIMARY' at /usr/local/share/perl/5.30.0/Apache/Ocsinventory/Server/Communication/Session.pm line 59. DBD::mysql::db do failed: Duplicate entry 'INVENTORY_CACHE_REVALIDATE-ALL' for key 'PRIMARY' at /usr/local/share/perl/5.30.0/Apache/Ocsinventory/Server/Inventory/Cache.pm line 133. DBD::mysql::db do failed: Duplicate entry 'SESSION-CLEAN' for key 'PRIMARY' at /usr/local/share/perl/5.30.0/Apache/Ocsinventory/Server/Communication/Session.pm line 59. DBD::mysql::db do failed: Duplicate entry 'SESSION-CLEAN' for key 'PRIMARY' at /usr/local/share/perl/5.30.0/Apache/Ocsinventory/Server/Communication/Session.pm line 59. DBD::mysql::db do failed: Duplicate entry 'INVENTORY_CACHE_REVALIDATE-ALL' for key 'PRIMARY' at /usr/local/share/perl/5.30.0/Apache/Ocsinventory/Server/Inventory/Cache.pm line 133. DBD::mysql::db do failed: Duplicate entry '54531' for key 'PRIMARY' at /usr/local/share/perl/5.30.0/Apache/Ocsinventory/Server/Inventory/Update/AccountInfos.pm line 66. DBD::mysql::db do failed: Duplicate entry '23861' for key 'PRIMARY' at /usr/local/share/perl/5.30.0/Apache/Ocsinventory/Server/Inventory/Update/AccountInfos.pm line 66. DBD::mysql::db do failed: Lock wait timeout exceeded; try restarting transaction at /usr/local/share/perl/5.30.0/Apache/Ocsinventory/Server/Inventory/Update.pm line 102. DBD::mysql::db do failed: Duplicate entry 'SESSION-CLEAN' for key 'PRIMARY' at /usr/local/share/perl/5.30.0/Apache/Ocsinventory/Server/Communication/Session.pm line 59. DBD::mysql::db do failed: Duplicate entry 'SESSION-CLEAN' for key 'PRIMARY' at /usr/local/share/perl/5.30.0/Apache/Ocsinventory/Server/Communication/Session.pm line 59. DBD::mysql::db do failed: Duplicate entry 'SESSION-CLEAN' for key 'PRIMARY' at /usr/local/share/perl/5.30.0/Apache/Ocsinventory/Server/Communication/Session.pm line 59. DBD::mysql::db do failed: Duplicate entry 'SESSION-CLEAN' for key 'PRIMARY' at /usr/local/share/perl/5.30.0/Apache/Ocsinventory/Server/Communication/Session.pm line 59. DBD::mysql::db do failed: Duplicate entry 'INVENTORY_CACHE_REVALIDATE-ALL' for key 'PRIMARY' at /usr/local/share/perl/5.30.0/Apache/Ocsinventory/Server/Inventory/Cache.pm line 133. DBD::mysql::db do failed: Duplicate entry '73361' for key 'PRIMARY' at /usr/local/share/perl/5.30.0/Apache/Ocsinventory/Server/Inventory/Update/AccountInfos.pm line 66. DBD::mysql::db do failed: Duplicate entry '72936' for key 'PRIMARY' at /usr/local/share/perl/5.30.0/Apache/Ocsinventory/Server/System.pm line 246. DBD::mysql::db do failed: Duplicate entry '39327' for key 'PRIMARY' at /usr/local/share/perl/5.30.0/Apache/Ocsinventory/Server/System.pm line 246. DBD::mysql::db do failed: Lock wait timeout exceeded; try restarting transaction at /usr/local/share/perl/5.30.0/Apache/Ocsinventory/Server/Inventory/Update.pm line 102. DBD::mysql::db do failed: Duplicate entry 'SESSION-CLEAN' for key 'PRIMARY' at /usr/local/share/perl/5.30.0/Apache/Ocsinventory/Server/Communication/Session.pm line 59. DBD::mysql::db do failed: Duplicate entry 'SESSION-CLEAN' for key 'PRIMARY' at /usr/local/share/perl/5.30.0/Apache/Ocsinventory/Server/Communication/Session.pm line 59. DBD::mysql::db do failed: Duplicate entry '70196' for key 'PRIMARY' at /usr/local/share/perl/5.30.0/Apache/Ocsinventory/Server/System.pm line 246. DBD::mysql::db do failed: Duplicate entry 'INVENTORY_CACHE_REVALIDATE-ALL' for key 'PRIMARY' at /usr/local/share/perl/5.30.0/Apache/Ocsinventory/Server/Inventory/Cache.pm line 133.

charleneauger commented 1 year ago

Hi,

Can you run the following queries for all problematic tables and return the result to me :

DESCRIBE my_table;
SELECT TABLE STATUS FROM 'my_database' WHERE name LIKE 'my_table';
SELECT count(*) FROM my_table;

Best regards, Charlene

charleneauger commented 1 year ago

Hi,

Since there was no answer, I'll close this issue now

Regards, Charlene

OCSInventory-NG / OCSInventory-Server