Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Won't Fix
-
10.1.39, 10.3.14, 10.3.15, 10.3.16
-
Debian 9.8 + MariaDB 10.3
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.
Attachments
Issue Links
- relates to
-
-
- Closed
-
-
MDEV-23048 mysqladmin to set SET SESSION wsrep_on=OFF as part of --local
-
- Open
-
-
-
- Closed
-
Activity
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?
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.
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 !
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
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.
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.