maria-discuss team mailing list archive

[Antony Stone <Antony.Stone@xxxxxxxxxxxxxxxxxxxxxx>]

Hi.

I hope this is a reasonable place to ask about Galera replication problems - 
if not, please point me elsewhere and I'll happily ask others :)

I have four Debian 10 "Buster" servers installed with MariaDB 10.1.47 and 
Galera 25.3.19-2.

I have other servers which write to these machines (there's no single 
"master", since I don't want any particular client to depend on any single 
master being available), and also read from them (in case it matters, those 
machines are running Asterisk, and writing Call Event Logs and Call Detail 
Records into MariaDB via ODBC).

My problem is the optimistic locking used by Galera when replicating between 
nodes.  It results in Asterisk log file entries such as:

WARNING[20089]: res_odbc.c:460 in ast_odbc_print_errors: SQL Execute returned 
an error: 40001: [ma-3.0.6][10.1.47-MariaDB-0+deb9u1]Deadlock found when 
trying to get lock; try restarting transaction

Now, that's all very well for an application which can respond to such a 
warning (personally I'd call it an error, when a write did not take place, but 
that's a question for Asterisk, not MariaDB) and perform the retry, but 
Asterisk does not do this.

Therefore I'd like to find some way to:

1. tell Galera to use pessimistic locking for replication if possible (I can 
accept the performance penalty)

2. tell MariaDB to automatically retry the write when the error occurs 
(although I can't think of any way that could be done, since I can't create a 
transaction of any sort - the operation is entirely determined for me by 
Asterisk)

3. find a High-Availability (which basically means no single point of failure) 
front-end for the whole cluster of 4 MariaDB servers so that the problem does 
not occur.


Questions:

1. How do other people deal with this problem?

2. Are any of my potential solutions above actually feasible?  (If so, how?)

3. Does anyone have any alternative ideas about how to connect an application 
which doesn't understand retrying database writes (Asterisk) with a database 
which doesn't guarantee to write the data you give it (MariaDB + Galera)?


Thanks,


Antony.

-- 
Schrödinger's rule of data integrity: the condition of any backup is unknown 
until a restore is attempted.

                                                   Please reply to the list;
                                                         please *don't* CC me.