[MDEV-20087] Galera + SET SQL_LOG_BIN=0 on binlog on others nodes Created: 2019-07-18  Updated: 2023-06-08  Resolved: 2023-04-11

Status: Closed
Project: MariaDB Server
Component/s: Galera
Affects Version/s: 10.1.39, 10.3.14, 10.3.15, 10.3.16
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Aurélien LEQUOY Assignee: Jan Lindström (Inactive)
Resolution: Won't Fix Votes: 2
Labels: galera, innodb, regression, replication
Environment:

Debian 9.8 + MariaDB 10.3


Issue Links:
Relates
relates to MDEV-7205 Galera cluster & sql_log_bin = off do... Closed
relates to MDEV-23048 mysqladmin to set SET SESSION wsrep_o... Open
relates to MDEV-31437 (Galera + SQL_LOG_BIN=0) = GTID and s... Closed

 Description   

On node 1 :

MariaDB [(none)]> set sql_log_bin=0;
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
MariaDB [(none)]> create database gg;
 
Query OK, 1 row affected (0.002 sec)
 
MariaDB [(none)]> use gg
 
Database changed
 
MariaDB [gg]> create table gg2 (a int(11));
 
Query OK, 0 rows affected (0.030 sec)
 
MariaDB [gg]> insert into gg2 values(1);
 
Query OK, 1 row affected (0.001 sec)
 
 
 
MariaDB [gg]> select * from gg2;
 
+------+
 
| a    |
 
+------+
 
|    1 |
 
+------+
 
1 row in set (0.000 sec)

On node 2 :

MariaDB [gg]> select * from gg2;
 
+------+
 
| a    |
 
+------+
 
|    1 |
 
+------+
 
1 row in set (0.000 sec)

Ok that's normal behavior !

But if I look in binlog I got this :

On node 1 :

mysqlbinlog --start-datetime="2019-07-18 11:50:00" --base64-output=decode-rows mariadb-bin.000027 > binlog.search
 
cat binlog.search | grep gg
 
(empty)

That's normal behavior too

On node 2 :

mysqlbinlog --start-datetime="2019-07-18 11:50:00" --base64-output=decode-rows mariadb-bin.000031 > binlog.search
 
cat binlog.search | grep gg
 
create database gg
 
use `gg`/*!*/;
 
create table gg2 (a int(11))
 
#Q> insert into gg2 values(1)
 
#190718 11:54:43 server id 473657853  end_log_pos 605411511 CRC32 0x4da5b0f0 	Table_map: `gg`.`gg2` mapped to number 673301494

WTF ? these lines should be not there.

As i Remember I pushed a bug for this in 10.0 (or similar), and it's happen again ! (could you please add this in test case the same with 'SET @@skip_replication = ON;'.

My goal was to load a database (I developed my own script to load) in a galera cluster of production (with other databases) but node 2 crashed because the size of binlog on other node took all disk space.

 Comments   
Comment by Geoff Montee (Inactive) [ 2019-07-18 ]

As a workaround, instead of setting sql_log_bin=0, you could try setting wsrep_on=OFF for your session. e.g.:

SET SESSION wsrep_on=OFF;

When used as a session variable, setting wsrep_on=OFF prevents that session's statements and transactions from replicating to the other cluster nodes, but other sessions replicate to the cluster, as usual.

Comment by Geoff Montee (Inactive) [ 2019-07-18 ]

See the note in the documentation:

Starting MariaDB 10.1.7, this variable does not affect the replication of events in a Galera cluster.

https://mariadb.com/kb/en/library/replication-and-binary-log-system-variables/#sql_log_bin

Everything seems to be working as expected. The other nodes have these statements in their binary logs because the statements were replicated to those nodes. If you do not want the statements to be replicated to those nodes, then you would need to set wsrep_on=OFF for your session.

Can you please clarify what exactly you expect to happen in this situation?

Comment by Aurélien LEQUOY [ 2019-07-19 ]

Hello, it's not a work around at all !!!

MariaDB [(none)]> SET SESSION wsrep_on=OFF;
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
MariaDB [(none)]> create database gg;
 
Query OK, 1 row affected (0.000 sec)
 
 
 
MariaDB [(none)]> show databases;
 
+--------------------+
 
| Database           |
 
+--------------------+
 
| gg                 |
 
| ...                |
 
+--------------------+
 
23 rows in set (0.001 sec)

On node 2 (of course )

MariaDB [(none)]> show databases;
 
+--------------------+
 
| Database           |
 
+--------------------+
 
| ...                |
 
+--------------------+
 
22 rows in set (0.001 sec)

And create database on binary log on node 1 and i don't want this

I want my data on all nodes of galera cluster, but in nothing on Binlog on all nodes. GaleraCluster act like one server, it's should be on binlog of no one.

My goal I want apply a script SQL for all cluster, but not for the slaves behind !!!

And launch X times time the same process for X nodes is not a solution, because one could finish and an other one could fail and back in SST.

Comment by Aurélien LEQUOY [ 2019-07-19 ]

it's opposite option of : https://jira.mariadb.org/browse/MDEV-7203

look : https://jira.mariadb.org/browse/MDEV-7205 interesting discution there !

for me it's a bug or a miss for 100% and not logical to have no binary log on one node and got this in binary logs for the others !

Comment by Geoff Montee (Inactive) [ 2019-07-19 ]

Unfortunately, in Galera Cluster, the binary logs on all nodes are not guaranteed to be consistent. There are many ways that they can become inconsistent. Setting sql_log_bin=0 on one node is one of those ways.

Galera Cluster does respect log_slave_updates, so if you want to execute a large transaction in Galera Cluster, and if you don't want the nodes that replicate the transaction to write it to their binary log, then you could temporarily set log_slave_updates=OFF on those nodes.

https://mariadb.com/kb/en/library/replication-and-binary-log-system-variables/#log_slave_updates

Comment by Matthias Rampke (Inactive) [ 2021-11-05 ]

Unfortunately, in Galera Cluster, the binary logs on all nodes are not guaranteed to be consistent. There are many ways that they can become inconsistent.

The documentation on Wsrep GTID mode suggests that inconsistent GTIDs are a bug. As per that page, I would like to report that

Setting sql_log_bin=0 on one node is one of those ways.

Prior to MDEV-7205, a statement would be logged on either all or no nodes. We relied on this to manage grants locally to each node. Now, any commit under sql_log_bin=0 is binlogged, and increments the GTID on all nodes but the one it was issued on, causing this node's GTID sequence to be offset, and subsequently the same GTID means different transactions on different nodes. This breaks reliable asynchronous replication from a Galera cluster.

Generated at Thu Feb 08 08:56:38 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.