I am hitting the same errors as this post from 2019 and want wondering if this is still the best way to deal with this.
I have a 3 node galera database and the librenms.log of my main install is showing database deadlock errors. It happens the most on the arp process but I have it happen on other process too. This is a main install and 3x pollers. ~1200 devices polled in under 200 sec between the 4 polling nodes.
SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction (Connection: mysql, SQL: DELETE T FROM `processors` T LEFT JOIN `devices` ON `devices`.`device_id` = T.`device_id` WHERE `devices`.`device_id` IS NULL) (Connection: dbFacile, SQL: DELETE T FROM `processors` T LEFT JOIN `devices` ON `devices`.`device_id` = T.`device_id` WHERE `devices`.`device_id` IS NULL)#0 /opt/librenms/LibreNMS/Device/Processor.php(162): dbDeleteOrphans()
#1 /opt/librenms/includes/discovery/processors.inc.php(5): LibreNMS\Device\Processor::runDiscovery()
#2 /opt/librenms/includes/discovery/functions.inc.php(164): include('...')
#3 /opt/librenms/discovery.php(108): discover_device()
#4 {main}
SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction (Connection: mysql, SQL: DELETE T FROM `ipv4_mac` T LEFT JOIN `ports` ON `ports`.`port_id` = T.`port_id` LEFT JOIN `devices` ON `devices`.`device_id` = T.`device_id` WHERE `ports`.`port_id` IS NULL AND `devices`.`device_id` IS NULL) (Connection: dbFacile, SQL: DELETE T FROM `ipv4_mac` T LEFT JOIN `ports` ON `ports`.`port_id` = T.`port_id` LEFT JOIN `devices` ON `devices`.`device_id` = T.`device_id` WHERE `ports`.`port_id` IS NULL AND `devices`.`device_id` IS NULL)#0 /opt/librenms/includes/discovery/arp-table.inc.php(115): dbDeleteOrphans()
#1 /opt/librenms/includes/discovery/functions.inc.php(164): include('...')
#2 /opt/librenms/discovery.php(108): discover_device()
#3 {main}
SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction (Connection: mysql, SQL: DELETE T FROM `links` T LEFT JOIN `devices` ON `devices`.`device_id` = T.`local_device_id` WHERE `devices`.`device_id` IS NULL) (Connection: dbFacile, SQL: DELETE T FROM `links` T LEFT JOIN `devices` ON `devices`.`device_id` = T.`local_device_id` WHERE `devices`.`device_id` IS NULL)#0 /opt/librenms/includes/discovery/discovery-protocols.inc.php(452): dbDeleteOrphans()
librenms@librenms:~$ ./validate.php
===========================================
Component | Version
--------- | -------
LibreNMS | 24.1.0-36-ge9725358e (2024-01-16T18:17:18-05:00)
DB Schema | 2023_12_15_105529_access_points_nummonbssid_integer (276)
PHP | 8.2.14
Python | 3.10.12
Database | MariaDB 10.6.12-MariaDB-0ubuntu0.22.04.1
RRDTool | 1.7.2
SNMP | 5.9.1
===========================================
[OK] Composer Version: 2.6.6
[OK] Dependencies up-to-date.
[OK] Database connection successful
[OK] Database Schema is current
[OK] SQL Server meets minimum requirements
[OK] lower_case_table_names is enabled
[OK] MySQL engine is optimal
[OK] Database and column collations are correct
[OK] Database schema correct
[OK] MySQL and PHP time match
[OK] Distributed Polling setting is enabled globally
[OK] Connected to rrdcached
[OK] Active pollers found
[OK] Dispatcher Service not detected
[OK] Locks are functional
[OK] Python poller wrapper is polling
[OK] Redis is functional
[OK] rrdtool version ok
[OK] Connected to rrdcached