Hello team, I have a situation that I am having a 5-node cluster set up and when the user goes to the database and makes this query
SHOW CREATE VIEW ticket_section_extended;
The mysql of two nodes stops and the cluster stops responding, any ideas can help with this, because as I understand it, it should not stop writing or reading the databases because three nodes are active and for a query I think I shouldn’t stop a query. Here I leave my conf and tell me what it could be or if something else needs to be added to the configuration, thank you.
[client]
socket=/var/run/mysqld/mysqld.sock
[mysqld]
server-id=1
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
log-error=/var/log/mysql/error.log
pid-file=/var/run/mysqld/mysqld.pid
pxc_encrypt_cluster_traffic=ON
max_connections = 680
max_allowed_packet = 256M
wsrep_provider_options=“cert.optimistic_pa=NO”
wsrep_certification_rules=‘OPTIMIZED’
Binary log expiration period is 604800 seconds, which equals 7 days
binlog_expire_logs_seconds=604800
######## wsrep ###############
Path to Galera library
wsrep_provider=/usr/lib/galera4/libgalera_smm.so
Cluster connection URL contains IPs of nodes
#If no IP is found, this implies that a new cluster needs to be created,
#in order to do that you need to bootstrap this node
wsrep_cluster_address=gcomm://10.120.1.19, 10.120.1.20, 10.120.1.21, 10.120.1.22, 10.120.1.23
In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW
transaction_isolation = READ-COMMITTED
Using the MyISAM storage engine is not recommended.
default_storage_engine=InnoDB
Slave thread to use
#wsrep_slave_threads=8
wsrep_log_conflicts
This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2
Node IP address
wsrep_node_address=10.120.1.19
Cluster name
wsrep_cluster_name=pxc-cluster
#If wsrep_node_name is not specified, then system hostname will be used
wsrep_node_name=pxc-cluster-node-1
#pxc_strict_mode allowed values: DISABLED,PERMISSIVE,ENFORCING,MASTER
#pxc_strict_mode=ENFORCING
pxc_strict_mode=PERMISSIVE
SST method
wsrep_sst_method=xtrabackup-v2
wsrep_provider_options=“socket.ssl_key=server-key.pem;socket.ssl_cert=server-cert.pem;socket.ssl_ca=ca.pem”
[sst]
encrypt=4
ssl-key=server-key.pem
ssl-ca=ca.pem
skip-log-bin
character-set-server=utf8
default_time_zone=“+00:00”