[MDEV-18918] SQL mode EMPTY_STRING_IS_NULL breaks RBR upon CREATE TABLE .. SELECT - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.3(EOL), 10.4(EOL)
Fix Version/s: 10.3.33, 10.4.23, 10.5.14, 10.6.6, 10.7.2, 10.8.1
Component/s: Data Definition - Create Table, Replication, Variables
Labels:
None

Description

--source include/master-slave.inc

--source include/have_binlog_format_row.inc

SET SQL_MODE= 'EMPTY_STRING_IS_NULL';

CREATE TABLE t1 AS SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE 1 = 0;

--sync_slave_with_master

SHOW CREATE TABLE t1;

# Cleanup

--connection master

DROP TABLE t1;

--source include/rpl_end.inc

10.3 f010c908
Last_Errno 1067
Last_Error Error 'Invalid default value for 'TABLE_NAME'' on query. Default database: 'test'. Query: 'CREATE TABLE `t1` (
`TABLE_NAME` varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT ''
)'

Technically the error isn't wrong, so I have no idea what should be done about it; but I guess it shouldn't just be allowed to fail.

Attachments

Issue Links

is duplicated by

MDEV-20254 Problems with EMPTY_STRING_IS_NULL and I_S tables

Closed

relates to

MDEV-20254 Problems with EMPTY_STRING_IS_NULL and I_S tables

Closed

MDEV-20253 DEFAULT is erroneously copied in `CREATE..SELECT column`

Confirmed

Activity

Ascending order - Click to sort in descending order

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.

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.

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-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.

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

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

MariaDB Server

SQL mode EMPTY_STRING_IS_NULL breaks RBR upon CREATE TABLE .. SELECT

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration