December 2020 - discuss - lists.mariadb.org

[Maria-discuss] MariaDB Server MiniFest Dec 9 14h00 CET (UTC+1)
by Ian Gilfillan 22 Dec '20

22 Dec '20

The MariaDB Server MiniFest is scheduled to start on December 9th 2020, at 14:00 UTC+1. The main topic for the MiniFest is MariaDB Server Releases, their quality, frequency and security. You will get a view both from a developer perspective and from a user perspective when it comes to new MariaDB Server releases and we will conclude with a Panel discussing the topic. Details at: https://mariadb.org/minifest2020/ Hope to see you there!

1 1

[Maria-discuss] Galera replication - optimistic locking problems
by Antony Stone 16 Dec '20

16 Dec '20

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.

3 4

Re: [Maria-discuss] Is GET_LOCK() & friends safe when using MIXED mode replication?
by pslawek83 08 Dec '20

08 Dec '20

I think what the documentation is trying to say is that you cannot get VALUE of GET_LOCK(...) inside insert, update, etc. because in statement based replication that VALUE will be obtained both on the master and then again, on the replica itself. So it would probably create data discrepancy between master and slave. INSERT INTO mytable VALUES(GET_LOCK(...)) with replication in STATEMENT mode, you can get eg. "1" inserted to your db on the master and "0" on the slave. True for functions which are non-deterministic, while GET_LOCK is deterministic, we could say is not deterministic on the slave because slave could hold different locks so you can be sure what it'll return on the master but you can't be sure what it'll return on the slave AFTER it gets read from replication log. a. It's of course safe to do in your case, it isn't even DB related as you're not storing the returned value in the DB so the replica can't get out of sync because of that. b. correct Best, S. Dnia 8 grudnia 2020 10:28 Jan Zankowski <jan.zankowski(a)gmail.com> napisał(a): Hello, I'd like to use GET_LOCK() and RELEASE_LOCK() on MariaDB 10.2 to implement basic distributed locking, to prevent simultaneous runs of a job unrelated to the DB. I see however in the mariadb.com MariaDB docs that: "Statements using the GET_LOCK function are not safe for statement-based replication." I also see in dev.mysql.com MySQL docs that this is likely only for statements such as "INSERT INTO mytable VALUES(GET_LOCK(...))." Full excerpt from MySQL docs: The following restriction applies to statement-based replication only, not to row-based replication. The GET_LOCK(), RELEASE_LOCK(), IS_FREE_LOCK(), and IS_USED_LOCK() functions that handle user-level locks are replicated without the replica knowing the concurrency context on the source. Therefore, these functions should not be used to insert into a source table because the content on the replica would differ. For example, do not issue a statement such as INSERT INTO mytable VALUES(GET_LOCK(...)). These functions are automatically replicated using row-based replication when using MIXED mode, and generate a warning in STATEMENT mode. Could someone please confirm that: (a) We will be safe using GET_LOCK() & RELEASE_LOCK() in MIXED mode. (b) If we used STATEMENT mode, the only case of not being safe is when the return value of GET_LOCK() etc. is directly involved in some other DB operations (e.g. inserted into a table). In particular, the use case when the locks are used only to prevent simultaneous runs of an external job should be fine. I strongly suspect this is not what "unsafe" means in this context, but not being an expert on replication, what I'm really worried about is that our replication would simply blow up or get totally out of sync if I used GET_LOCK(). Please calm these fears if you can. :) Thanks! Jan ______________________________ Mailing list: launchpad.net launchpad.net Post to : maria-discuss(a)lists.launchpad. Unsubscribe : launchpad.net launchpad.net More help : help.launchpad.net help.launchpad.net

1 0

[Maria-discuss] Is GET_LOCK() & friends safe when using MIXED mode replication?
by Jan Zankowski 08 Dec '20

08 Dec '20

Hello, I'd like to use GET_LOCK() and RELEASE_LOCK() on MariaDB 10.2 to implement basic distributed locking, to prevent simultaneous runs of a job unrelated to the DB. I see however in the MariaDB docs <https://mariadb.com/kb/en/get_lock/> that: "Statements using the GET_LOCK function are not safe for statement-based replication." I also see in MySQL docs <https://dev.mysql.com/doc/refman/5.7/en/replication-features-functions.html> that this is likely only for statements such as "INSERT INTO mytable VALUES(GET_LOCK(...))." Full excerpt from MySQL docs: *The following restriction applies to statement-based replication only, not to row-based replication. The GET_LOCK(), RELEASE_LOCK(), IS_FREE_LOCK(), and IS_USED_LOCK() functions that handle user-level locks are replicated without the replica knowing the concurrency context on the source. Therefore, these functions should not be used to insert into a source table because the content on the replica would differ. For example, do not issue a statement such as INSERT INTO mytable VALUES(GET_LOCK(...)). These functions are automatically replicated using row-based replication when using MIXED mode, and generate a warning in STATEMENT mode.* Could someone please confirm that: (a) We will be safe using GET_LOCK() & RELEASE_LOCK() in MIXED mode. (b) If we used STATEMENT mode, the only case of not being safe is when the return value of GET_LOCK() etc. is directly involved in some other DB operations (e.g. inserted into a table). In particular, the use case when the locks are used only to prevent simultaneous runs of an external job should be fine. I strongly suspect this is not what "unsafe" means in this context, but not being an expert on replication, what I'm really worried about is that our replication would simply blow up or get totally out of sync if I used GET_LOCK(). Please calm these fears if you can. :) Thanks! Jan

1 0