search

OCSInventory-NG / OCSInventory-Server

Communication server of OCS Inventory
http://www.ocsinventory-ng.org/
GNU General Public License v2.0
352 stars 140 forks source link

Software Table Deadlock #360

Closed wiltonsr closed 2 years ago

wiltonsr commented 2 years ago

General informations

Docker Image 2.9

Server informations

Perl version : v5.16.3 Mysql / Mariadb / Percona version : mysql Ver 15.1 Distrib 10.3.31-MariaDB

OCS Inventory informations

Ocs server version : 2.9

Problem's description

I'm having a large amount of errors 500. When I enable OCS_OPT_DBI_PRINT_ERROR = 1 the following error appears at Apache's Logs:

DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at /usr/local/share/perl5/Apache/Ocsinventory/Server/Inventory/Software.pm line 59.

The database is a single server with 32GB RAM and 16 cores.

wiltonsr commented 2 years ago

Hi, @charleneauger @gillesdubois

Any news about this?

sgysz commented 2 years ago

I ran into the same problem and there is no solution at the moment.

haidars commented 2 years ago

since i upgrade de 2.9.1, am having a lot of deadlocks and HTTP 500 error

Deadlocks

image

HTTP 500

image
jjmg84 commented 2 years ago

since i upgrade de 2.9.1, am having a lot of deadlocks and HTTP 500 error

Deadlocks image

HTTP 500 image

Hi, good info i am in same situation, please can you say me how you get those graphs, its interesant to see those information on a graph.

Thanks in advance!

haidars commented 2 years ago

since i upgrade de 2.9.1, am having a lot of deadlocks and HTTP 500 error

Deadlocks image

HTTP 500 image

Hi, good info i am in same situation, please can you say me how you get those graphs, its interesant to see those information on a graph.

Thanks in advance!

All our logs are send to a logstash which is then send to an Elastic search and kibana. The graph are automatically generated

mujtaba-saboor commented 2 years ago

I was facing the same issue with our OCS server. I have almost 70K+ hardware devices and faced deadlock in the software table. I figured out that it was the query

DELETE FROM software WHERE HARDWARE_ID = ?

that was locking the table rows and causing deadlocks. I changed the logic to fetching all the software records that have to be deleted a simple SELECT query and then iterating over them and deleting them one after the other, therefore, no locking of table rows. The issue is fixed and everything is running smoothly for me.

PR #370

jjmg84 commented 2 years ago

ble rows and

Thanks, i have seen that the table that is locking for me is : software_categories_link imagen

wiltonsr commented 2 years ago

Hi, @mujtaba-saboor

Thanks for your reply, on next week I will test your PR's changes on my homologation environment with 100K of machines. Then I return the status here.

jjmg84 commented 2 years ago

Hi, i have fixed my problem too adapting @mujtaba-saboor fix, i atach here the code:

sub _del_categorysoft { my ($name, $publisher, $version) = @; my $sqlSoftwareCat; my $sql; my @arg = (); my $result; my $resultDelete;

$sqlSoftwareCat = "SELECT ID FROM software_categories_link WHERE NAME_ID = ? AND PUBLISHER_ID = ? AND VERSION_ID = ?";
push @arg, $name;
push @arg, $publisher;
push @arg, $version;

$result = _prepare_sql($sqlSoftwareCat, @arg);
if(!defined $result) { return 1; }
    $sql = "DELETE FROM software_categories_link WHERE WHERE ID = ?";
while(my $row = $result->fetchrow_array()){
  $resultDelete = _prepare_sql($sql, $row);
}
return 0;

}

Fixed delete sql query because was bad formulated.

mujtaba-saboor commented 2 years ago

Hi, i have fixed my problem too adapting @mujtaba-saboor fix, i atach here the code:

sub _del_categorysoft { my ($name, $publisher, $version) = @; my $sqlSoftwareCat; my $sql; my @arg = (); my $result; my $resultDelete;

$sqlSoftwareCat = "SELECT ID FROM software_categories_link WHERE NAME_ID = ? AND PUBLISHER_ID = ? AND VERSION_ID = ?";
push @arg, $name;
push @arg, $publisher;
push @arg, $version;

$result = _prepare_sql($sqlSoftwareCat, @arg);
if(!defined $result) { return 1; }
  $sql = "DELETE FROM software_categories_link WHERE NAME_ID = ? AND PUBLISHER_ID = ? AND VERSION_ID = ?";
while(my $row = $result->fetchrow_array()){
  $resultDelete = _prepare_sql($sql, $row);
}
return 0;

}

@jjmg84 Glad, it worked for you as well!

jjmg84 commented 2 years ago

Hi, i have fixed my problem too adapting @mujtaba-saboor fix, i atach here the code: sub _del_categorysoft { my ($name, $publisher, $version) = @; my $sqlSoftwareCat; my $sql; my @arg = (); my $result; my $resultDelete;

$sqlSoftwareCat = "SELECT ID FROM software_categories_link WHERE NAME_ID = ? AND PUBLISHER_ID = ? AND VERSION_ID = ?";
push @arg, $name;
push @arg, $publisher;
push @arg, $version;

$result = _prepare_sql($sqlSoftwareCat, @arg);
if(!defined $result) { return 1; }
    $sql = "DELETE FROM software_categories_link WHERE NAME_ID = ? AND PUBLISHER_ID = ? AND VERSION_ID = ?";
while(my $row = $result->fetchrow_array()){
  $resultDelete = _prepare_sql($sql, $row);
}
return 0;

}

@jjmg84 Glad, it worked for you as well!

Yes, thank but I dont know how to create a commit, if you want test my code and add it to your commit, be free to add it :) thanks!

jjmg84 commented 2 years ago

@mujtaba-saboor im sorry but my code dont work, it continue making deadlocks :( and i dont know how to hadle this

DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at /usr/local/share/perl/5.30.0/Apache/Ocsinventory/Server/Inventory/Software.pm line 59.

imagen

i have configured a software category for microsoft office on OCS server

mujtaba-saboor commented 2 years ago

@jjmg84 are you sure the deadlock is from software_categories_link table and not from the software table? Because for me it was software table, maybe you could refactor the software deletion logic as well.

jjmg84 commented 2 years ago

@jjmg84 are you sure the deadlock is from software_categories_link table and not from the software table? Because for me it was software table, maybe you could refactor the software deletion logic as well.

Hi, yes im sure, imagen but i dont get to know how refactor the deletion logic :(

wiltonsr commented 2 years ago

Hello @mujtaba-saboor,

Today I tested your changes in my environment and the Deadlocks occurrences are gone.

Thanks for you contribution.

mujtaba-saboor commented 2 years ago

@jjmg84 are you sure the deadlock is from software_categories_link table and not from the software table? Because for me it was software table, maybe you could refactor the software deletion logic as well.

Hi, yes im sure, imagen but i dont get to know how refactor the deletion logic :(

What’s the count of rows in table software_categories_link?

jjmg84 commented 2 years ago

@jjmg84 are you sure the deadlock is from software_categories_link table and not from the software table? Because for me it was software table, maybe you could refactor the software deletion logic as well.

Hi, yes im sure, imagen but i dont get to know how refactor the deletion logic :(

What’s the count of rows in table software_categories_link?

Hi, imagen

jjmg84 commented 2 years ago

Hi, i have news, as i can see, after modify the code of :

sub _del_categorysoft { my ($name, $publisher, $version) = @; my $sqlSoftwareCat; my $sql; my @arg = (); my $result; my $resultDelete; $sqlSoftwareCat = "SELECT ID FROM software_categories_link WHERE NAME_ID = ? AND PUBLISHER_ID = ? AND VERSION_ID = ? order by ID"; push @arg, $name; push @arg, $publisher; push @arg, $version; $result = _prepare_sql($sqlSoftwareCat, @arg); if(!defined $result) { return 1; } $sql = "DELETE FROM software_categories_link WHERE ID = ?"; while(my $row = $result->fetchrow_array()){ $resultDelete = _prepare_sql($sql, $row); } return 0; }

i saw there are some locks to same row: imagen i added a count column to see how many locks for same register are. i are a little bit lost how to continue to fix that isue :(

jjmg84 commented 2 years ago

Nobody has same problem? this problem eats all my Mysql conections with locks and OCs dont work as desired :(

EldoBam commented 2 years ago

Hi,

I'm facing the same issue also with the deadlock with the software_categories_link statement.

I tried to fix it with the patches, but still the same issue.

I started to debug by activating the genreal logs in the mariadb server and found around 600 queries from one connection at this time the first error in sql_errors.log occured.

6 Query    DELETE FROM software_categories_link WHERE NAME_ID = '196' AND PUBLISHER_ID = '22' AND VERSION_ID = '44'
                     6 Query    INSERT INTO software_categories_link (NAME_ID, PUBLISHER_ID, VERSION_ID, CATEGORY_ID) VALUES('196','22','44','0')

So I was wondering about the fact, that I don't have software categories enabled, but still get those queries as above. And also those entries in database, even if I don't need them.

image

So I went through the code, and found something interesting :)

If you have a look at OCSInventory-Server/Apache/Ocsinventory/Server/Inventory/Software.pm You can see the following:

image

In my case $category is 0 but not undefined, so it genrerates for each client and each single software entry a delete and insert query. This just gives such a heavy load to my mariadb 10.5 server that it ends in a deadlock - that can't be healthy.

So I just commented out those lines, and I was free from deadlocks. A side effect is, that in inventory you can't see all softwares anymore, but if you look into a client, the software entries fits.

But that isn't the end of the line (っ °Д °;)っ

I'm not sure, what exactly is going wrong but this handling with the software_* tables and also with the engine_mutex is not really working for me:

I have the following error a lot in my sql_errors.log: @ localhost [] ERROR 1062: Duplicate entry 'SESSION-CLEAN' for key 'PRIMARY' : INSERT INTO engine_mutex(NAME, PID, TAG) VALUES('SESSION','232392','CLEAN')

And I am wondering about the order of the activity logs.

Is it right that it says: "session;already handled" and after "session;started"???

Fri Apr  8 14:16:34 2022;178911;103;XXXXXX-2021-04-26-03-27-06;10.135.90.65;OCS-NG_WINDOWS_AGENT_v2.6.0.0;prolog;new_deviceid
Fri Apr  8 14:16:34 2022;178911;100;XXXXXX-2021-04-26-03-27-06;10.135.90.65;OCS-NG_WINDOWS_AGENT_v2.6.0.0;prolog;accepted
Fri Apr  8 14:16:34 2022;178911;315;XXXXXX-2021-04-26-03-27-06;10.135.90.65;OCS-NG_WINDOWS_AGENT_v2.6.0.0;session;already handled
Fri Apr  8 14:16:34 2022;178911;311;XXXXXX-2021-04-26-03-27-06;10.135.90.65;OCS-NG_WINDOWS_AGENT_v2.6.0.0;session;started
Fri Apr  8 14:16:40 2022;179198;319;XXXXXX-2021-04-26-03-27-06;10.135.90.65;OCS-NG_WINDOWS_AGENT_v2.6.0.0;session;found
Fri Apr  8 14:16:40 2022;179198;104;XXXXXX-2021-04-26-03-27-06;10.135.90.65;OCS-NG_WINDOWS_AGENT_v2.6.0.0;inventory;incoming
Fri Apr  8 14:16:40 2022;179198;320;XXXXXX-2021-04-26-03-27-06;10.135.90.65;OCS-NG_WINDOWS_AGENT_v2.6.0.0;session;end
Fri Apr  8 14:16:40 2022;179198;101;XXXXXX-2021-04-26-03-27-06;10.135.90.65;OCS-NG_WINDOWS_AGENT_v2.6.0.0;inventory;transmitted

....

I also don't understand why there is a Uniqe key at software_* rows which are varchars... Like softare_version.VERSION ???

What if 2 different softwares have the same Versionnumber??? That wont work in this constallation.

....

That's so far, I'm facing a lot of issues, where it is very hard to see where they come from exactly, but as I tried to understand the peral code, some things don't really make sense in my opinion... Why will every software will be completely deleted and new created if I have the inventory diff enabled.

I hope this helps a bit, I try get the update from 2.7 to 2.9.2 working, but facing a lot of issues since the change from one software table to many software_* tables.

I think this software inventory has to be reviewed.

--> last sentence: I'm not facing any issues if only a few clients report their inventory, but if there comes a bit more load, then it all breaks together.

Please feel free to contact me if you got some questions or like to go through the code with me to find a working solution.

best regards

eldo

EldoBam commented 2 years ago

Update:

after the weekend, I can't find any deadlocks in the logs anymore. Now I found some "lock wait timeout" since tomorrow in the morning, after the people started to work and their PCs.

So my workaround was:

--> commenting out Software.pm lines 242 - 249

        # Delete software from software categories link
        if(_del_category_soft($arrayValue{NAME_ID}, $arrayValue{PUBLISHER_ID}, $arrayValue{VERSION_ID})) { return 1; }

        # Insert in software categories link table
        if(defined $category) {
            my $result_category = _insert_software_categories_link($arrayValue{NAME_ID}, $arrayValue{PUBLISHER_ID}, $arrayValue{VERSION_ID}, $category);
            if(!defined $result_category) { return 1; }
        }

The only side-effect is, that I don't have the overview of all softwares in GUI anymore --> it's empty. Therefore I will write a Script to fill this table once a day.

jjmg84 commented 2 years ago

Update:

after the weekend, I can't find any deadlocks in the logs anymore. Now I found some "lock wait timeout" since tomorrow in the morning, after the people started to work and their PCs.

So my workaround was:

--> commenting out Software.pm lines 242 - 249

        # Delete software from software categories link
        if(_del_category_soft($arrayValue{NAME_ID}, $arrayValue{PUBLISHER_ID}, $arrayValue{VERSION_ID})) { return 1; }

        # Insert in software categories link table
        if(defined $category) {
            my $result_category = _insert_software_categories_link($arrayValue{NAME_ID}, $arrayValue{PUBLISHER_ID}, $arrayValue{VERSION_ID}, $category);
            if(!defined $result_category) { return 1; }
        }

The only side-effect is, that I don't have the overview of all softwares in GUI anymore --> it's empty. Therefore I will write a Script to fill this table once a day.

This issue seems that dont has been resolved on 2.10 version :(

charleneauger commented 2 years ago

Hi all,

I implemented a first revision of what could be a fix for this issue. If you have some time / test environement to it out your are welcome :)

Best regards, Charlene

jjmg84 commented 2 years ago

Hi all,

I implemented a first revision of what could be a fix for this issue. If you have some time / test environement to it out your are welcome :)

Best regards, Charlene

Thanks!!

i will try it out.

I will post result of testing.