[MDEV-28368] Writer node is 'stuck' and is not accepting any DDL queries until we kill -9 pid Created: 2022-04-20  Updated: 2022-07-03  Resolved: 2022-07-03

Status: Closed
Project: MariaDB Server
Component/s: Galera
Affects Version/s: 10.5.15, 10.6.7
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: Piotr Wa Assignee: Jan Lindström (Inactive)
Resolution: Cannot Reproduce Votes: 0
Labels: None

Attachments: File maria3.7z    

 Description   

We detected a galera entire galera cluster blocked no DDL operations could be run until we kill -9 writer node behaviour was on 10.6 and 10.5 series
There were such entries in error log:
[Warning] Aborted connection 22233 to db: 'unconnected' user: 'unauthenticated' host: 'IP' (This connection closed normally without authentication)

Root Cause
The Node stuck 'hangout ' was eventually tracked down to an insert update query:
INSERT INTO `bets_cashout_offers` (BetId, CashoutAmount, OfferedTime)VALUES (?betId, ?guaranteedPrice, ?offeredTime)ON DUPLICATE KEY UPDATE CashoutAmount = ?guaranteedPrice, OfferedTime = ?offeredTime;";

the problem was the query was being run as follows:
Connection.Query(sql, new

{ betId, guaranteedPrice, offeredTime });
changing it to the following resolves the issue:
Connection.Execute(sql, new { betId, guaranteedPrice, offeredTime }

);

Given enought time with Connection.Query (30 minutes give or take it will hang entire cluster)



 Comments   
Comment by Jan Lindström (Inactive) [ 2022-04-21 ]

Pietia10 Can you provide full error logs (if this is repeatable use wsrep-debug=1), show processlist output, show engine innodb output and node configuration. Please specify server and Galera library versions used.

Comment by Piotr Wa [ 2022-04-21 ]

Hi @Jan
26.4.11-1
10.6.7-3-MariaDB-enterprise-log

maria3.7z

Comment by Piotr Wa [ 2022-04-21 ]

Hi @Jan
26.4.11-1
10.6.7-3-MariaDB-enterprise-log

Comment by Jan Lindström (Inactive) [ 2022-06-03 ]

Pietia10 Can you reproduce this issue? Can I have show create table bets_cashout_offers and if your server hangs kill it and use debug package and open core file in debugger and provide ouput from thread apply all bt. Please use plain text format.

Comment by Piotr Wa [ 2022-06-03 ]

Hi @Jan,
More information you can find on enterprise support here : CS0394525 , we were asked by mariaDB consultant to post bug here to have it fast fixed.

I'm afraid we can't reproduce anymore as we changed the code from Connection.Query to Connection.Execute which effectively fixed problem with hang.

CREATE TABLE `bets_cashout_offers` (
`BetId` bigint(20) NOT NULL,
`CashoutAmount` decimal(10,2) NOT NULL,
`OfferedTime` datetime NOT NULL,
PRIMARY KEY (`BetId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Generated at Thu Feb 08 10:00:11 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.