Sujatha Sivakumar (Inactive) added a comment - 2019-07-19 08:13 Background: =========== In Row based replication statements such as CREATE TABLE ... SELECT, a CREATE statement is generated from the table definition and replicated using statement-based format, while the row insertions are replicated using row-based format For example: Query: create table t1 (a int) select b from t; ============ 1) binlog-format= STATEMENT MariaDB [test]> create table t1 (a int) select b from t; #190719 13:07:55 server id 1 end_log_pos 502 CRC32 0x5c74db35 GTID 0-1-2 ddl /*!100001 SET @@session.gtid_seq_no=2*//*!*/; # at 502 #190719 13:07:55 server id 1 end_log_pos 608 CRC32 0xa14ceae2 Query thread_id=4 exec_time=0 error_code=0 SET TIMESTAMP=1563521875/*!*/; create table t1 (a int) select b from t /*!*/; 2) binlog-format= ROW MariaDB [test]> create table t1 (a int) select b from t; #190719 13:06:01 server id 1 end_log_pos 502 CRC32 0x12f5a142 GTID 0-1-2 ddl /*!100001 SET @@session.gtid_seq_no=2*//*!*/; BEGIN /*!*/; # at 502 #190719 13:06:01 server id 1 end_log_pos 645 CRC32 0xf3a1ca57 Query thread_id=4 exec_time=0 error_code=0 SET TIMESTAMP=1563521761/*!*/; CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL ) Reported Bug: ============= Query is "CREATE TABLE t1 AS SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE 1 = 0;" The "TABLE_NAME" field in "INFORMATION_SCHEMA.TABLES" is defined as shown below. `TABLE_NAME` varchar(64) NOT NULL DEFAULT '' On master the above query gets executed successfully and it gets rewritten in the binarylog as per the resulting table definition as shown below. CREATE TABLE `t1` (`TABLE_NAME` varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT '') On Slave: The rewritten query arrives for execution. The SQL_MODE='EMPTY_STRING_IS_NULL' is set. The above DDL is sent to parser for validation. Parser invokes "Column_definition::check" for "TABLE_NAME" column. The DEFAULT is '' (Empty String) and it gets mapped to NULL due to the "SQL_MODE" which contradicts with field definition being "NOT NULL". Hence it results in "ER_INVALID_DEFAULT", 1067, "Invalid default value for \'%-.192s\'" error. Replication behaves as expected.

Andrei Elkin added a comment - 2020-06-22 13:14 Looks relating to MDEV-19632 . It should be verified if this particular sql_mode does not create a proper table definition in the CREATE-SELECT case. Compare the created def against the plain CREATE and CREATE-LIKE.

Alexander Barkov added a comment - 2021-11-22 16:40 - edited The problem is not really related to replication. It's repeatable on a single box: SET SQL_MODE= 'EMPTY_STRING_IS_NULL' ; CREATE or replace TABLE t1 AS SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE 1 = 0; SHOW CREATE TABLE t1; DROP TABLE t1; +-------+------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+------------------------------------------------------------------------------------------------------------------------------+ | t1 | CREATE TABLE `t1` ( `TABLE_NAME` varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT '' ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+------------------------------------------------------------------------------------------------------------------------------+ Now I copy the table definition to the clipboard and paste it: MariaDB [test]> CREATE TABLE `t1` ( -> `TABLE_NAME` varchar (64) CHARACTER SET utf8 NOT NULL DEFAULT '' -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 -> ; ERROR 1067 (42000): Invalid default value for 'TABLE_NAME' The CREATE..SELECT statement should probably create the column without any defaults when EMPTY_STRING_IS_NULL is enabled.

Alexander Barkov added a comment - 2021-12-28 13:56 - edited serg , please review a patch: https://github.com/MariaDB/server/commit/eb019e3d6c9772c1caa10ee788a4d4c3be116a64 It's in this branch: https://github.com/MariaDB/server/tree/bb-10.3-bar-MDEV-18918 The 10.5 version of the patch: https://github.com/MariaDB/server/commit/0cfc092f26b9cc4cdd6e44bd5465953e63754795 https://github.com/MariaDB/server/tree/bb-10.5-bar-MDEV-18918 Thanks.

Sergei Golubchik added a comment - 2022-01-21 14:08 ok to push