[MDEV-7205] Galera cluster & sql_log_bin = off don't work Created: 2014-11-25  Updated: 2019-07-18  Due: 2015-05-07  Resolved: 2015-08-08

Status: Closed
Project: MariaDB Server
Component/s: Galera
Affects Version/s: 10.0.13-galera, 10.0.14-galera
Fix Version/s: 10.1.7

Type: Bug Priority: Blocker
Reporter: Aurélien LEQUOY Assignee: Nirbhay Choubey (Inactive)
Resolution: Fixed Votes: 1
Labels: galera

Attachments: JPEG File 15874745401_d3975432f6_b.jpg    
Issue Links:
Relates
relates to MDEV-9037 DML statements on a Galera Cluster no... Closed
relates to MDEV-20087 Galera + SET SQL_LOG_BIN=0 on binlog ... Closed

 Description   

my servers:

https://farm8.staticflickr.com/7544/15874745401_d3975432f6_b.jpg

configuration on each node : (except server-id ofc)

[mysqld]
 
#mysql settings
 
 
 
log-slave-updates=1
 
default-storage-engine=innodb
 
query_cache_size=0
 
query_cache_type=0
 
bind-address=0.0.0.0
 
 
 
server-id=1746717299
 
 
 
skip-slave-start
 
 
 
datadir = /data/mysql/data
 
#galera settings
 
wsrep_provider=/usr/lib/galera/libgalera_smm.so
 
wsrep_cluster_name="iways_cluster"
 
wsrep_cluster_address="gcomm://10.7.20.3,10.7.20.4,10.7.20.6"
 
wsrep_sst_method=rsync
 
wsrep_provider_options="gcache.size=20G"
 
 
 
#innoDB
 
innodb_file_per_table
 
connect_timeout         = 60
 
wait_timeout            = 3600
 
innodb_buffer_pool_size = 16G
 
innodb_flush_method     = O_DIRECT
 
 
 
innodb_log_file_size = 64M
 
innodb_log_buffer_size = 8M
 
innodb_flush_log_at_trx_commit = 1
 
innodb_autoinc_lock_mode=2
 
 
 
#for master
 
 
 
binlog_format=ROW
 
log_bin                 = /data/mysql/binlog/mariadb-bin
 
log_bin_index           = /data/mysql/binlog/mariadb-bin.index
 
 
 
max_binlog_size         = 1G
 
expire_logs_days        = 10
 
 
 
sync_binlog=1
 
 
 
sort_buffer_size        = 10M
 
bulk_insert_buffer_size = 16M
 
tmp_table_size          = 64M
 
max_heap_table_size     = 64M
 
 
 
#log
 
slow_query_log_file     = /data/mysql/log/mariadb-slow.log
 
long_query_time = 1
 
 
 
performance_schema = 'ON'
 
 
 
collation-server = utf8_unicode_ci
 
character-set-server = utf8
 
init-connect = 'SET NAMES utf8'
 
 
 
max_connections = 500
 
open_files_limit =  65535
 
 
 
log-error=/data/mysql/log/error.log

on node 1 :

use PRODUCTION;
 
CREATE TABLE `test` (
 
 `id` int(11) NOT NULL AUTO_INCREMENT,
 
 PRIMARY KEY (`id`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8
 
 
 
INSERT INTO `PRODUCTION`.`test` (`id`) VALUES ('1'), ('2');

After on each node (1 at once)

SET sql_log_bin = OFF;
 
delete from test where id =1;

successfull on the node where it's executed and the DELETE is not applied on other node of cluster ....

The problem here the cluster is desynchronized.

My goal, got a cluster and a server of history behind. (where delete not executed on slave)

 Comments   
Comment by Daniel Black [ 2014-12-23 ]

your goal can probably be achieved with set session wsrep_on=off

Comment by Aurélien LEQUOY [ 2015-03-24 ]

the problem is that

sql_log_bin = off

work exactly like wsrep_on=off

for me when we set sql_log_bin = off, and you do one insert / delete / update it's should be replecated each node of the cluster but not on their slave.

Comment by Nirbhay Choubey (Inactive) [ 2015-04-28 ]

http://lists.askmonty.org/pipermail/commits/2015-April/007790.html

Comment by Jan Lindström (Inactive) [ 2015-04-28 ]

From Galera point of view patch looks correct, I just do not know all the possible side effects.

Comment by Aurélien LEQUOY [ 2015-05-05 ]

my problem :

I wanna make an operation, it's should replicated on each nodes of galera cluster but not on any slave behind.

Actually sql_log_bin work exactly like an other command of WSREP, if we set it to off the is unsynced.

I spoke with Seppo Jaakola and he told it was the standard comportement, but I think it's shoudln't, because we have so many other to do it with galera cluster, no one to do what explained previously.

Aurélien

Comment by Sergei Golubchik [ 2015-05-08 ]

Agree with jplindst. Looks ok, but I'm not sure I can see all side effects of this change.

Comment by Aurélien LEQUOY [ 2015-05-08 ]

Normally it could only affect Galera, but I am agree will be not easy to do it.

Comment by Nirbhay Choubey (Inactive) [ 2015-05-11 ]

Right, I see some tests failing. This would require some more investigation.

Comment by Aurélien LEQUOY [ 2015-08-07 ]

I really interesting by that !

any can give me some idea where to search ?

Comment by Nirbhay Choubey (Inactive) [ 2015-08-08 ]

https://github.com/MariaDB/server/commit/cd1a11ace3b4a4b034307590d58cc7a9d37004f6

Comment by Nirbhay Choubey (Inactive) [ 2015-08-08 ]

Aurelien_LEQUOY While I have pushed the fix for this bug to 10.1, I skipped
5.5-galera and 10.0-galera as the patch modifies some critical areas that seem
quite risky for GA versions.
I can later backport the fix to GA versions if it causes no side effects.

Comment by Daniel Black [ 2015-08-09 ]

nirbhay_c can this implemented behaviour be documented at least on the binary logging system variables page

Comment by Aurélien LEQUOY [ 2015-08-10 ]

thx, I will check if it's work fine in 10.1

Comment by Nirbhay Choubey (Inactive) [ 2015-08-12 ]

danblack Added a note under 'sql_log_bin' :
https://mariadb.com/kb/en/mariadb/replication-and-binary-log-server-system-variables/#sql_log_bin

Thanks!

Comment by Daniel Black [ 2015-08-12 ]

Thanks nirbhay_c.

Generated at Thu Feb 08 07:17:48 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.