[Commit] [Deadlock]

[smoeller]

Hello.

I am using a 3 node Mariadb cluster and before that I have a Maxscale Proxy installed.
So far everything works quite well.
But I keep getting the error message in the Zabbix interface :

Error in query [COMMIT] [Deadlock found when trying to get lock; try restarting transaction] [zabbix. php:17 → require_once() → ZBase->run() → ZBase->authenticateUser() → CWebUser:: checkAuthentication() → CApiWrapper->__call() → CFrontendApiWrapper->callMethod() → CApiWrapper->callMethod() → CFrontendApiWrapper->callClientMethod() → CLocalApiClient->callMethod() → DBend() → DBcommit() → DBexecute() → trigger_error() in include/db. inc.php:362]


Here ist the Mariadb Galera Configuration from node1

[galera]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql

binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0

# Galera Provider Configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so

# Galera Cluster Configuration
wsrep_cluster_name="zabbix_cluster"
wsrep_cluster_address="gcomm://***,***,***"

# Galera Synchronization Configuration
wsrep_sst_method=rsync

# Galera Node Configuration
wsrep_node_address="*****"
wsrep_node_name="****"

wsrep_retry_autocommit=3
innodb_lock_wait_timeout=10
innodb_deadlock_detect=OFF
wsrep_sync_wait=1
wsrep_slave_threads=4
wsrep_certify_nonPK=1

Here the Maxscale.cnf

[maxscale]
threads=auto
admin_host=0.0.0.0
admin_port=8989
admin_secure_gui=false


[Galera-Monitor]
type=monitor
module=galeramon
servers=srv-zbx-k-db01,srv-zbx-h-db02,srv-zbx-m-db03
user=maxscale_user
password=*******
monitor_interval=2s
disable_master_failback=true
available_when_donor=true

[srv-zbx-k-db01]
type=server
address=****
port=3306
protocol=MariaDBBackend

[srv-zbx-h-db02]
type=server
address=*****
port=3306
protocol=MariaDBBackend

[srv-zbx-m-db03]
type=server
address=****
port=3306
protocol=MariaDBBackend

[Galera-Service]
type=service
router=readconnroute
router_options=synced
servers=srv-zbx-k-db01,srv-zbx-h-db02,srv-zbx-m-db03
user=maxscale_user
password=******

[Galera-Listener]
type=listener
service=Galera-Service
protocol=MariaDBClient
address=0.0.0.0
port=3306

Can someone help me what this error is and how I can possibly fix it?

[Monck]

Hi smoeller, I also have the same problem, I have tried to configure the DBs but it still gives the error, especially in the dashboards.

[mjmj]

Hi,

Signed up specially to comment here, as we are seeing the same. We are running zabbix 7.0.6, native HA, against a galera cluster (on alma9, 10.11.6-MariaDB MariaDB Server)
Galera is configured with things like wsrep_retry_autocommit = 6, innodb_autoinc_lock_mode=2 and innodb_flush_log_at_trx_commit=2. The load is very low, the 3-node cluster is running zabbix-server and galera, and is basically just monitoring itself at the moment.

Our mysql connection goes through haproxy on the localhost (so not maxscale here) and haproxy takes care that all DB traffic is directed at one single galera host. Only in the case of node unavailability, haproxy will switch to another galera cluster node. We expected that principe to avoid the DEADLOCKs, but now we're seeing them anyways.

We're also seeing them in the dashboards. Is there any other place where DEADLOCKS can potentially can happen too?

[mjmj]

For the record: we are accessing the web GUI through a (haproxy) load balancer. Initially the balance was set to roundrobin, which caused the deadlocks to appear. We have now changed to:
> balance source
> hash-type consistent
and that seems to make things better.

I would be curious to learn more about your setups en environments, and also how you are accessing th GUI.

[mjmj]

This is the way logging the error shows in /var/log/httpd/error.log:

[Mon Dec 16 16:33:18.198546 2024] [proxy_fcgi:error] [pid 25410:tid 25566] [client 192.168.56.32:42180] AH01071: Got error 'PHP message: PHP Warning: Undefined array key "file" in /usr/share/zabbix/include/classes/debug/CProfiler.php on line 374PHP message: PHP Warning: Undefined array key "line" in /usr/share/zabbix/include/classes/debug/CProfiler.php on line 374PHP message: PHP Warning: Error in query [COMMIT] [Deadlock found when trying to get lock; try restarting transaction] in /usr/share/zabbix/include/db.inc.php on line 362', referer: http://192.168.56.32/zabbix/zabbix.php?action=host.dashboard.view&hostid=10650 &dashboardid=15

Are we all seeing the same? (strangely, my posts seem to be "unapproved", and I don't think any of what I've been writing here, today, is actually published here for others to read and respond to...)

[cyber]

as a new user, your first posts went to pre-approval... but it does not happen right away... Humans.
Its all visible...

[mjmj]

I can confirm that (in our case) the problem is triggered by the load balancer that we put in front of our three-host zabbix/galera cluster.

When accessing the web GUI through HAProxy configured to load-balance (spread) incoming requests equally over the three available backend servers (zabbix1/zabbix2/zabbix3) (haproxy terminology: balance round-robin) we frequently see the above happening. They go away, when haproxy is configured to 'stick' clients to backendserver. (haproxy terminology: balance source)

This gives the same high-availability, spreads load perhaps a bit less equally, but all errors went away.

Still, perhaps this is something that could be fixed in zabbix, with the data above. Since I'm still designing / testing I can test whatever would help to fix this. (Not sure though, if zabbix devs are reading along here)

[mjmj]

Pity that there are no replies. Would be nice for this to be solved...

[cyber]

How many users you have there, that you need to have 3 loadbalanced hosts for GUI?

if you are buildng a HA setup already, why run things in same hosts anyway... I would split it all up, GUI, server, DB... each with their own HA, their own VIP-s if needed.. would be more stable, I think...

[minkay]

Hi! I also encountered the same error, does anyone know or have you solved this issue?
P.S: I am new to Zabbix.

Error in query [COMMIT] [Deadlock found when trying to get lock; try restarting transaction] [zabbix.php:17 → require_once() → ZBase->run() → ZBase->authenticateUser() → CWebUser::checkAuthentication() → CApiWrapper->__call() → CFrontendApiWrapper->callMethod() → CApiWrapper->callMethod() → CFrontendApiWrapper->callClientMethod() → CLocalApiClient->callMethod() → DBend() → DBcommit() → DBexecute() → trigger_error() in include/db.inc.php:362]