Not the answer you need?
Register and ask your own question!

mariadb 10.1.22 node failure on bad SQL cauases cluster to eventually fail.

cebowencebowen EntrantLegacy User Role Beginner
I am having an issue with my Galera setup and I am not sure how to fix it, or even if it is possible to fix it. 3 node cluster

So we use drupal and are in the process of upgraing our main site, the webteam is attempting to sync with new development servers from production, however there are a ton of problematic SQL being generated from drush, or even the XML imports so we see errors like



WSREP: Ignoring error for TO isolated action: source: 5a5cadb2-0d9c-11e7-ab52-a200c70c84fe version: 3 local: 0 state: APPLYING flags: 65 conn_id: 24791 trx_id: -1 seqnos (l: 160865, g: 21333163, s: 21333162, d: 21333162, ts: 2849868880088527

[ERROR] Slave SQL: Error 'Out of range value for column 'created'

Internal MariaDB error code: 1264

[Warning] WSREP: RBR event 1 Query apply warning: 1, 213331

Caused by something like a out of range int(11) in about 50 of the SQL inserts.

This eventually leads to the node disjoining

2017-03-21 13:46:23 140707578771200 [Note] WSREP: forgetting 557b5e4c (tcp://10.76.10.103:4567)
2017-03-21 13:46:23 140707578771200 [Note] WSREP: forgetting 5a5cadb2 (tcp://10.76.10.102:4567)
2017-03-21 13:46:23 140707578771200 [Note] WSREP: Node 41acb7bc state prim

Now this is bad enough as it will cause other applications connecting to the server to start receiving state errors from the node, however the problem gets worse as on occasion this will happen to multiple servers at the same time in the cluster starts to fail communication with each other and I will have to bootstrap from the last node to go down.

ive read this article:

https://www.percona.com/blog/2014/07/21/a-schema-change-inconsistency-with-galera-cluster-for-mysql/#comment-10967902

But I cannot seem to find out how to keep the cluster from crashing like this, other than to ensure the SQL is always clean, which is unrealistic. I could increase the nodes to 5, so 2 could fail and the system is maintained, but I am not sure that is best for our setup. TOI is set on all the machines.

thanks in advance for any help.

#
# 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]
bind-address = 0.0.0.0

# this is only for the mysqld standalone daemon
[mysqld]
#skip-name-resolve
datadir=/mysql_data/mysql
socket=/var/lib/mysql/mysql.sock
innodb_log_file_size=4GB
transaction-isolation=READ-COMMITTED
default-storage-engine=INNODB
character-set-server=utf8
collation-server=utf8_bin
max_allowed_packet=600M
general_log = on
general_log_file=/var/log/query.log

#
# * Galera-related settings
#
[galera]
# Mandatory settings
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address=gcomm://10.76.10.101,10.76.10.102
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
wsrep_cluster_name=' cluster1 '
wsrep_node_name='mysqlcl02'
wsrep_node_address='10.76.10.103'
wsrep_sst_method=rsync
wsrep_replicate_myisam=ON
wsrep_dirty_reads=1
wsrep_provider_options="gcs.fc_limit=500;gcs.fc_factor=1.0"
wsrep_notify_cmd=/usr/bin/galeranotify.py
#bind-address=0.0.0.0
#
# Optional setting
#wsrep_slave_threads=1
#innodb_flush_log_at_trx_commit=0

# 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.0 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.0]
ft_min_word_len=3
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.