Buy Percona ServicesBuy Now!

XtraDB Cluster seems to completely ignore SQL_MODE

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • XtraDB Cluster seems to completely ignore SQL_MODE

    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:

    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
    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):

    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;
Working...
X