XtraDB Cluster seems to completely ignore SQL_MODE

MySQL & MariaDB Percona XtraDB Cluster 5.x
1

Hi all! Thinking this is a bug, hoping someone cal help.

Main issue: Error: BLOB, TEXT, GEOMETRY or JSON column can’t have a default value

After some googling, this is due to SQL_MODE changes since 5.7 - we should be able to revert this by manually setting the SQL_MODE. However, the XtraDB Cluster doesn’t seem to be respecting the new change, even though it is coming through:


mysql> SELECT @@sql_mode;
+------------------------+
| @@sql_mode |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE `x` (`id` int(12) NOT NULL, `image` text NOT NULL DEFAULT 'hi') ENGINE=InnoDB DEFAULT CHARSET=latin1;

ERROR 1101 (42000): BLOB, TEXT, GEOMETRY or JSON column 'image' can't have a default value

The same query runs fine on an older mariadb 10.2.25 server (not a cluster.)

My install/config (ip and pass changed for obvious reasons):



wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb
dpkg -i percona-release_latest.generic_all.deb
rm percona-release_latest.generic_all.deb
apt-get update
apt-get -y install percona-xtradb-cluster-57

service mysql stop

cat <<EOF >> /etc/mysql/my.cnf
[mysqld]
wsrep_provider=/usr/lib/libgalera_smm.so

wsrep_cluster_name=pxc-cluster
wsrep_cluster_address=gcomm://127.0.0.1

wsrep_node_name=pxc1
wsrep_node_address=127.0.0.1

wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=sstuser:XXXXXXXXXX

pxc_strict_mode=DISABLED

binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2

max_connections = 64000
max_connect_errors = 4294967295
sql_mode="NO_ENGINE_SUBSTITUTION"


##
## DISABLE BINLOG
##

skip-log-bin

#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!

default_storage_engine = InnoDB
innodb_log_file_size = 1024M
innodb_buffer_pool_size = 2048M
innodb_log_buffer_size = 8M
innodb_file_per_table = 1
innodb_open_files = 400
innodb_io_capacity = 400
innodb_flush_method = O_DIRECT

#
# * Security Features
#

binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
query_cache_size=0
query_cache_type=0

innodb_read_io_threads = 64
innodb_write_io_threads = 16
innodb_log_buffer_size = 256M
innodb_flush_log_at_trx_commit = 0
query_cache_size = 0
innodb_purge_threads = 4
EOF

/etc/init.d/mysql bootstrap-pxc

mysql -u root -p
CREATE USER 'sstuser'&#64;'localhost' IDENTIFIED BY 'XXXXXXXXXX';
GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'sstuser'&#64;'localhost';
FLUSH PRIVILEGES;