[MDEV-11213] Mariadb Galera Cluster + Async slave replica problem consistent data on table level Created: 2016-11-02  Updated: 2019-05-20  Resolved: 2019-05-20

Status: Closed
Project: MariaDB Server
Component/s: Galera
Affects Version/s: 10.1.18
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Nicola Assignee: Jan Lindström (Inactive)
Resolution: Not a Bug Votes: 0
Labels: galera
Environment:

3 nodes multimaster(galera cluster) on Vmware environment with Oracle Linux 7
And 1 node slave on Vmware environment with Oracle Linux 7


 Description   

Hi,
I've this problem, the slave node replica(gtid mode) have status is uptodate :
[OK] This replication slave is running with the read_only option enabled.
[OK] This replication slave is uptodate with master.

But a level row is inconsistent :
DWHS (replica server have this tables ) :
SOURCE_XXX_LETTURE : row 3460
SOURCE_XXX_GESTORE: row 10685
DWH1,DWH2,DWH3 (Master nodes) :
SOURCE_XXX_LETTURE : row 5039
SOURCE_XXX_GESTORE: row 14.223

Actually the replica master has connect to :
2016-11-02 10:55:48 139671523392256 [Note] Slave I/O thread: connected to master 'repl_user@dwh2:3306',replication starts at GTID position '1-101-42535'

but i create a new table on dwh2.gamenet.psm the replication automatically creates the table created on the server dwh2, but if create a new table on dwh1 the table is created on dwh 2 server but but it is not applied on dwhs node.
but if on the node dwh1 insert a row on the new table created, the replica goes in the error state because in the replica slave the new table not exist and insert is lost.
All master server have the option log_slave_updates =1 on server.cnf.

The actual configuration of server.cnf on all master and slave :

DWH1

 
#
 
# These groups are read by MariaDB server.
 
# Use it for options that only the server (but not clients) should see
 
#
 
# See the examples of server my.cnf files in /usr/share/mysql/
 
#
 
# this is read by the standalone daemon and embedded servers
 
[server]
 
# this is only for the mysqld standalone daemon
 
[mysqld]
 
# * SLAVE CONFIGURATION
 
# #
 
 gtid-domain-id                 = 1
 
 server_id                      = 101
 
 log_slave_updates              = 1
 
 log_bin                        = binlog
 
 binlog_cache_size              = 16M
 
#
 
# * Galera-related settings
 
#
 
 large-pages
 
# BINARY LOGGING #
 
 expire-logs-days               = 14
 
 sync-binlog                    = 1
 
 
 
 # CACHES AND LIMITS #
 
 tmp-table-size                 = 128M
 
 max-heap-table-size            = 128M
 
 query-cache-type               = 1
 
 query-cache-size               = 128M
 
 query_cache_limit              = 1M
 
 max-connections                = 150
 
 thread-cache-size              = 50
 
 table-definition-cache         = 50
 
 table-open-cache               = 1250 # table_open_cache  is same as table_cache
 
 sort_buffer_size               = 16M   # 16M per connessione
 
 max_allowed_packet             = 16M
 
 join_buffer_size               = 8M
 
# # INNODB #
 
 innodb-flush-method            = O_DIRECT
 
 innodb-log-files-in-group      = 2
 
 innodb-log-file-size           = 1G
 
 innodb-flush-log-at-trx-commit = 1
 
 innodb-file-per-table          = 1
 
 innodb-buffer-pool-size        = 4G
 
 innodb_file_format             = barracuda
 
 innodb_large_prefix            = 1
 
 log_bin_trust_function_creators = ON
 
 innodb_file_per_table          = 1
 
 innodb_buffer_pool_instances   = 4
 
# # MyISAM #
 
 key_buffer_size                = 1M
 
 
 
#
 
# # LOGGING #
 
 log-output = FILE
 
# general_log
 
# general_log_file              = /var/log/mysql/mysql_query.log
 
 log-error                      = /var/log/mysql/mysql-error.log
 
 log-queries-not-using-indexes  = 1
 
 slow-query-log                 = 1
 
 slow-query-log-file            = /var/log/mysql/mysql-slow.log
 
 
 
[galera]
 
# Mandatory settings
 
wsrep_on                        =ON
 
wsrep_provider                  =/usr/lib64/galera/libgalera_smm.so
 
wsrep_cluster_address           =gcomm://172.16.31.15,172.16.31.16,172.16.31.17
 
wsrep_provider_options          = "gmcast.mcast_addr=239.192.0.11"
 
#wsrep_cluster_address=gcomm://239.192.0.11
 
binlog_format                   = ROW
 
wsrep_forced_binlog_format      = ROW
 
wsrep_gtid_domain_id            = 1
 
wsrep_gtid_mode                 = 1
 
default_storage_engine          = InnoDB
 
innodb_autoinc_lock_mode        = 2
 
wsrep_sst_auth                  =repl_user:Replication$$2016!
 
wsrep_debug                     =OFF
 
wsrep_log_conflicts             = 1
 
wsrep_slave_threads             =4
 
#
 
# Allow server to accept connections on all interfaces.
 
#
 
bind-address=0.0.0.0
 
#
 
# Optional setting
 
 
 
wsrep_cluster_name="DWH_CLUSTER"
 
wsrep_node_address="172.16.31.15"
 
wsrep_node_name="dwh1"
 
wsrep_node_incoming_address="172.16.33.15"
 
wsrep_sst_method=rsync
 
wsrep_slave_threads=4
 
 
 
 
 
# this is only for embedded server
 
[embedded]
 
 
 
# This group is only read by MariaDB servers, not by MySQL.
 
# If you use the same .cnf file for MySQL and MariaDB,
 
# you can put MariaDB-only options here
 
[mariadb]
 
 
 
# This group is only read by MariaDB-10.1 servers.
 
# If you use the same .cnf file for MariaDB of different versions,
 
# use this group for options that older servers don't understand
 
[mariadb-10.1]

DWH2

 
#
 
# These groups are read by MariaDB server.
 
# Use it for options that only the server (but not clients) should see
 
#
 
# See the examples of server my.cnf files in /usr/share/mysql/
 
#
 
 
 
# this is read by the standalone daemon and embedded servers
 
[server]
 
 
 
# this is only for the mysqld standalone daemon
 
[mysqld]
 
# * SLAVE CONFIGURATION
 
# #
 
 gtid-domain-id                 = 1
 
 server_id                      = 201
 
 log_slave_updates              = 1
 
 log_bin                        = binlog
 
 binlog_cache_size              = 16M
 
#
 
# * Galera-related settings
 
#
 
 large-pages
 
# BINARY LOGGING #
 
 expire-logs-days               = 14
 
 sync-binlog                    = 1
 
 # CACHES AND LIMITS #
 
 tmp-table-size                 = 128M
 
 max-heap-table-size            = 128M
 
 query-cache-type               = 1
 
 query-cache-size               = 128M
 
 max-connections                = 150
 
 thread-cache-size              = 50
 
 table-definition-cache         = 50
 
 table-open-cache               = 1250
 
 sort_buffer_size               = 16M
 
 max_allowed_packet             = 16M
 
 join_buffer_size               = 8M
 
# # INNODB #
 
 innodb-flush-method            = O_DIRECT
 
 innodb-log-files-in-group      = 2
 
 innodb-log-file-size           = 1G
 
 innodb-flush-log-at-trx-commit = 1
 
 innodb-file-per-table          = 1
 
 innodb-buffer-pool-size        = 4G
 
 innodb_file_format             = barracuda
 
 innodb_large_prefix            = 1
 
 log_bin_trust_function_creators = ON
 
 innodb_file_per_table          = 1
 
 innodb_buffer_pool_instances   = 4
 
#
 
# # LOGGING #
 
 log-error                      = /var/log/mysql/mysql-error.log
 
 log-queries-not-using-indexes  = 1
 
 slow-query-log                 = 1
 
 slow-query-log-file            = /var/log/mysql/mysql-slow.log
 
 
 
[galera]
 
# Mandatory settings
 
wsrep_on                        = ON
 
wsrep_provider                  = /usr/lib64/galera/libgalera_smm.so
 
wsrep_cluster_address           = gcomm://172.16.31.15,172.16.31.16,172.16.31.17
 
wsrep_provider_options          = "gmcast.mcast_addr=239.192.0.11"
 
#wsrep_cluster_address=gcomm://239.192.0.11
 
binlog_format                   = ROW
 
wsrep_forced_binlog_format      = ROW
 
wsrep_gtid_mode                 = 1
 
wsrep_gtid_domain_id            = 1
 
default_storage_engine          = InnoDB
 
innodb_autoinc_lock_mode        = 2
 
wsrep_sst_auth                  = repl_user:Replication$$2016!
 
wsrep_slave_threads             = 4
 
#
 
# Allow server to accept connections on all interfaces.
 
#
 
bind-address=0.0.0.0
 
#
 
# Optional setting
 
 
 
wsrep_cluster_name="DWH_CLUSTER"
 
wsrep_node_address="172.16.31.16"
 
wsrep_node_name="dwh2"
 
wsrep_node_incoming_address="172.16.33.16"
 
wsrep_sst_method=rsync
 
wsrep_slave_threads=4
 
 
 
 
 
# this is only for embedded server
 
[embedded]
 
 
 
# This group is only read by MariaDB servers, not by MySQL.
 
# If you use the same .cnf file for MySQL and MariaDB,
 
# you can put MariaDB-only options here
 
[mariadb]
 
 
 
# This group is only read by MariaDB-10.1 servers.
 
# If you use the same .cnf file for MariaDB of different versions,
 
# use this group for options that older servers don't understand
 
[mariadb-10.1]

DWH3

 
#
 
# These groups are read by MariaDB server.
 
# Use it for options that only the server (but not clients) should see
 
#
 
# See the examples of server my.cnf files in /usr/share/mysql/
 
#
 
 
 
# this is read by the standalone daemon and embedded servers
 
[server]
 
 
 
# this is only for the mysqld standalone daemon
 
[mysqld]
 
# * SLAVE CONFIGURATION
 
# # #
 
 gtid-domain-id                 = 1
 
 server_id                      = 301
 
 log_slave_updates              = 1
 
 log_bin                        = binlog
 
 binlog_cache_size              = 16M
 
#
 
# * Galera-related settings
 
#
 
 large-pages
 
# BINARY LOGGING #
 
 expire-logs-days               = 14
 
 sync-binlog                    = 1
 
 
 
 # CACHES AND LIMITS #
 
 tmp-table-size                 = 128M
 
 max-heap-table-size            = 128M
 
 query-cache-type               = 1
 
 query-cache-size               = 512M
 
 max-connections                = 150
 
 thread-cache-size              = 50
 
 table-definition-cache         = 50
 
 table-open-cache               = 1250
 
 sort_buffer_size               = 16M
 
 max_allowed_packet             = 16M
 
 join_buffer_size               = 8M
 
# # INNODB #
 
 innodb-flush-method            = O_DIRECT
 
 innodb-log-files-in-group      = 2
 
 innodb-log-file-size           = 1G
 
 innodb-flush-log-at-trx-commit = 1
 
 innodb-file-per-table          = 1
 
 innodb-buffer-pool-size        = 4G
 
 innodb_file_format             = barracuda
 
 innodb_large_prefix            = 1
 
 log_bin_trust_function_creators = ON
 
 innodb_file_per_table          = 1
 
 innodb_buffer_pool_instances   = 4
 
#
 
# # LOGGING #
 
 log-error                      = /var/log/mysql/mysql-error.log
 
 log-queries-not-using-indexes  = 1
 
 slow-query-log                 = 1
 
 slow-query-log-file            = /var/log/mysql/mysql-slow.log
 
 
 
[galera]
 
 # Mandatory settings
 
 wsrep_on                        = ON
 
 wsrep_provider                  = /usr/lib64/galera/libgalera_smm.so
 
 wsrep_cluster_address           = gcomm://172.16.31.15,172.16.31.16,172.16.31.17
 
 wsrep_provider_options          = "gmcast.mcast_addr=239.192.0.11"
 
 #wsrep_cluster_address=gcomm://239.192.0.11
 
 binlog_format                   = ROW
 
 wsrep_forced_binlog_format      = ROW
 
 wsrep_gtid_mode                 = 1
 
 wsrep_gtid_domain_id            = 1
 
 default_storage_engine          = InnoDB
 
 innodb_autoinc_lock_mode        = 2
 
 wsrep_sst_auth                  = repl_user:Replication$$2016!
 
 wsrep_slave_threads             = 4
 
#
 
# Allow server to accept connections on all interfaces.
 
#
 
 bind-address                    =0.0.0.0
 
#
 
# Optional setting
 
 
 
wsrep_cluster_name              ="DWH_CLUSTER"
 
wsrep_node_address              ="172.16.31.17"
 
wsrep_node_name                 ="dwh3"
 
wsrep_node_incoming_address     ="172.16.33.17"
 
wsrep_sst_method                =rsync
 
 
 
 
 
# this is only for embedded server
 
[embedded]
 
 
 
# This group is only read by MariaDB servers, not by MySQL.
 
# If you use the same .cnf file for MySQL and MariaDB,
 
# you can put MariaDB-only options here
 
[mariadb]
 
 
 
# This group is only read by MariaDB-10.1 servers.
 
# If you use the same .cnf file for MariaDB of different versions,
 
# use this group for options that older servers don't understand
 
[mariadb-10.1]

DWHS1

 
#
 
# These groups are read by MariaDB server.
 
# Use it for options that only the server (but not clients) should see
 
#
 
# See the examples of server my.cnf files in /usr/share/mysql/
 
#
 
 
 
# this is read by the standalone daemon and embedded servers
 
[server]
 
 
 
# this is only for the mysqld standalone daemon
 
[mysqld]
 
# * SLAVE CONFIGURATION
 
# #
 
 gtid-domain-id                 = 1
 
 server_id                      = 1001
 
 log_slave_updates              = 1
 
 log_bin                        = binlog
 
 relay-log                      = DWHS1-relay-bin
 
 read_only
 
#
 
# * Galera-related settings
 
#
 
 large-pages
 
# BINARY LOGGING #
 
 expire-logs-days               = 14
 
 sync-binlog                    = 1
 
 binlog_cache_size              = 128M
 
 # CACHES AND LIMITS #
 
 tmp-table-size                 = 1G
 
 max-heap-table-size            = 512M
 
 query-cache-type               = 1
 
 query-cache-size               = 4G
 
 query_cache_limit              = 1M
 
 max-connections                = 40
 
 thread-cache-size              = 100
 
 table-definition-cache         = 100
 
 table-open-cache               = 1250 # table_open_cache  is same as table_cache
 
 sort_buffer_size               = 64M   # 32M per connessione
 
 max_allowed_packet             = 32M
 
 join_buffer_size               = 16M
 
# # INNODB #
 
 innodb-flush-method            = O_DIRECT
 
 innodb-log-files-in-group      = 2
 
 innodb-log-file-size           = 1G
 
 innodb-flush-log-at-trx-commit = 1
 
 innodb-file-per-table          = 1
 
 innodb-buffer-pool-size        = 4G
 
 innodb_file_format             = barracuda
 
 innodb_large_prefix            = 1
 
 log_bin_trust_function_creators = ON
 
 innodb_file_per_table          = 1
 
 innodb_buffer_pool_instances   = 4
 
# # MyISAM #
 
 key_buffer_size                = 2M
 
 
 
#
 
# # LOGGING #
 
 log-output = FILE
 
# general_log
 
# general_log_file              = /var/log/mysql/mysql_query.log
 
 log-error                      = /var/log/mysql/mysql.log
 
 log-queries-not-using-indexes  = 1
 
 slow-query-log                 = 1
 
 slow-query-log-file            = /var/log/mysql/mysql-slow.log
 
 
 
[galera]
 
# Mandatory settings
 
#wsrep_on=ON
 
#wsrep_provider=/usr/lib64/galera/libgalera_smm.so
 
#wsrep_cluster_address=gcomm://172.16.31.15,172.16.31.16,172.16.31.17
 
#wsrep_provider_options          = "gmcast.mcast_addr=239.192.0.11"
 
#wsrep_cluster_address=gcomm://239.192.0.11
 
binlog_format=row
 
default_storage_engine=InnoDB
 
#innodb_autoinc_lock_mode=2
 
#wsrep_sst_auth=repl_user:Replication$$2016!
 
#wsrep_debug=OFF
 
#wsrep_log_conflicts = 1
 
#
 
# Allow server to accept connections on all interfaces.
 
#
 
bind-address=0.0.0.0
 
#
 
# Optional setting
 
 
 
#wsrep_cluster_name="DWH_CLUSTER"
 
#wsrep_node_address="172.16.31.15"
 
#wsrep_node_name="dwh1"
 
#wsrep_node_incoming_address="172.16.33.15"
 
#wsrep_sst_method=rsync
 
#wsrep_slave_threads=4
 
 
 
 
 
# this is only for embedded server
 
[embedded]
 
 
 
# This group is only read by MariaDB servers, not by MySQL.
 
# If you use the same .cnf file for MySQL and MariaDB,
 
# you can put MariaDB-only options here
 
[mariadb]
 
 
 
# This group is only read by MariaDB-10.1 servers.
 
# If you use the same .cnf file for MariaDB of different versions,
 
# use this group for options that older servers don't understand
 
[mariadb-10.1]

sorry for my bad english.
Thanks.
Regards.

 Comments   
Comment by Jan Lindström (Inactive) [ 2019-05-20 ]

In galera cluster it is not a good idea to run slave on read-only mode. As all writes to other nodes need to be replicated also to this slave and these writes can't be applied if it is read-only.

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