Buy Percona ServicesBuy Now!

SQL_MODE="NO_ENGINE_SUBSTITUTION" replication issue - 5.7.19 on Centos 7

Lastest Forum Posts - November 17, 2017 - 2:16am
Hi,

We are in the process of attempting to upgrade our production DB from 5.6 to 5.7, and are at the stage of adding in a new slave so that it can form a 3 slave 1 master setup, which will ultimately be promoted to be the new Master once the other 2 slaves have also been upgraded.

New slave is running on Centos 7.4, data volume is a 4 disk raid10 ssd lvm.

So, Percona 5.7 installed, full backup restored, mysql upgrade run.

Slave setup and started.

Both Master (5.6) and Slave (5.7) have SQL_MODE="NO_ENGINE_SUBSTITUTION" set in ./etc/my.cnf, in the correct section. Checked in both mysqld.log and global variables.

However, despite this, once the slave is started and begins to catch up, it soon runs into error 1048's

2017-11-17T09:30:19.772355Z 13 [ERROR] Slave SQL for channel '': Error 'Column 'date' cannot be null' on query. Default database: 'my_database'. Query: 'INSERT INTO user_favourites (user_id, favourite_id, date) VALUES ('3504328', 2316904, NULL)', Error_code: 1048
2017-11-17T09:30:19.772377Z 13 [Warning] Slave: Column 'date' cannot be null Error_code: 1048

From what we have read, this is an expected feature of the new stricter sql_mode setting, however, our understanding was that setting to NO_ENGINE_SUBSTITUTION was the correct temporary work round.

As this is a new slave there is no pressing issue, however, this doesn't happen on test setup (Centos 6.9 Master and Slave, running same version, 5.7.19) and currently prevents further progress.

Any insights would be greatly appreciated.

Thanks,

Mike

New Cluster: nodes 2 and 3 don't join but rather start their own clusters

Lastest Forum Posts - November 16, 2017 - 4:07am
Followed the instructions exactly by using copy/paste, changing nothing but the IP addresses involved.

However. when I start the second node using the instructions above, it comes up not as a member of the cluster but isolated.

first host:
+--------------------------+--------------------------------------+
| Variable_name | Value |
+--------------------------+--------------------------------------+
| wsrep_cluster_conf_id | 1 |
| wsrep_cluster_size | 1 |
| wsrep_cluster_state_uuid | f28ae440-cabb-11e7-976c-17bca5fe1b6b |
| wsrep_cluster_status | Primary |
+--------------------------+--------------------------------------+

second host:
+--------------------------+--------------------------------------+
| Variable_name | Value |
+--------------------------+--------------------------------------+
| wsrep_cluster_conf_id | 1 |
| wsrep_cluster_size | 1 |
| wsrep_cluster_state_uuid | 9df5f23e-cabc-11e7-b6e2-030d02b0e16b |
| wsrep_cluster_status | Primary |
+--------------------------+--------------------------------------+

Environment: Ubuntu 16.04, fresh install with only apt-get update && apt-get upgrade -y done prior to installing cluster. ufw firewall is disabled

config from host 1:
# Template my.cnf for PXC
# Edit to your requirements.
[mysqld]
server-id=1
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log-bin log_slave_updates
expire_logs_days=7

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

wsrep_provider=/usr/lib/libgalera_smm.so
wsrep_cluster_name=pxc-cluster
wsrep_cluster_address=gcomm://192.168.175.141,192.168.168.30
wsrep_node_name=pxc1
wsrep_node_address=192.168.175.141
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=sstuserassw0rd
pxc_strict_mode=ENFORCING
binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2

config from host 2:
# Template my.cnf for PXC
# Edit to your requirements.
[mysqld]
server-id=2
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log-bin log_slave_updates
expire_logs_days=7

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

wsrep_provider=/usr/lib/libgalera_smm.so
wsrep_cluster_name=pxc-cluster
wsrep_cluster_address=gcomm://192.168.175.141,192.168.168.30
wsrep_node_name=pxc2
wsrep_node_address=192.168.168.30
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=sstuserassw0rd
pxc_strict_mode=ENFORCING
binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2

sst user was created on host 1
+---------------+-----------+
| user | host |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
| sstuser | localhost |
+---------------+-----------+
+----------------------------------------------------------------------------------------+
| Grants for sstuser@localhost |
+----------------------------------------------------------------------------------------+
| GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost' |
+----------------------------------------------------------------------------------------+

Logs are posted to https://pastebin.com/XLUjX7HF

This seems to be similar to https://www.percona.com/forums/quest...fter-bootstrap and https://www.percona.com/forums/quest...ster-size-of-1 -- neither of which has an answer.
Visit Percona Store


General Inquiries

For general inquiries, please send us your question and someone will contact you.