Tweet
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:
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):
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:
Code:
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
My install/config (ip and pass changed for obvious reasons):
Code:
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'@'localhost' IDENTIFIED BY 'XXXXXXXXXX'; GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost'; FLUSH PRIVILEGES;