| Bug #101797 | create as select skip sql_mode check for datetime default value | ||
|---|---|---|---|
| Submitted: | 30 Nov 2020 9:17 | Modified: | 1 Dec 2020 8:58 |
| Reporter: | yuxiang jiang (OCA) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 5.6 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | Contribution | ||
[1 Dec 2020 6:30]
yuxiang jiang
Tencent (Cloud-native Database Team CDB&CynosDB)-MySQL (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: 0001-bugfix-create-table-having-invalid-default-value-wit.patch (application/octet-stream, text), 1.26 KiB.
[1 Dec 2020 8:58]
MySQL Verification Team
Hello yuxiang jiang, Thank you for the report and contribution. regards, Umesh
[1 Dec 2020 9:00]
MySQL Verification Team
- 5.6 ./mtr bug101797 Logging: ./mtr bug101797 2020-12-01 09:57:36 0 [Warning] Insecure configuration for --secure-file-priv: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path. 2020-12-01 09:57:36 0 [Note] /home/umshastr/work/binaries/ga/mysql-5.6.50/bin/mysqld (mysqld 5.6.50) starting as process 15636 ... 2020-12-01 09:57:36 15636 [Note] Plugin 'FEDERATED' is disabled. 2020-12-01 09:57:36 15636 [Note] Binlog end 2020-12-01 09:57:36 15636 [Note] Shutting down plugin 'MyISAM' 2020-12-01 09:57:36 15636 [Note] Shutting down plugin 'CSV' MySQL Version 5.6.50 Checking supported features... - SSL connections supported Collecting tests... Checking leftover processes... Removing old var directory... Creating var directory '/home/umshastr/work/binaries/ga/mysql-5.6.50/mysql-test/var'... Installing system database... Using parallel: 1 ============================================================================== TEST RESULT TIME (ms) or COMMENT -------------------------------------------------------------------------- worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009 SET @@session.sql_mode=1436549120/*!*/; Warnings: Warning 1681 'ERROR_FOR_DIVISION_BY_ZERO' is deprecated and will be removed in a future release. Warning 1681 'NO_ZERO_DATE' is deprecated and will be removed in a future release. Warning 1681 'NO_ZERO_IN_DATE' is deprecated and will be removed in a future release. create table t1(f1 int); insert into t1 values (1); create table tsm1 as select CURRENT_TIMESTAMP as updatetime from t1; show create table tsm1; Table Create Table tsm1 CREATE TABLE `tsm1` ( `updatetime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 main.bug101797 [ pass ] 48
[1 Dec 2020 9:06]
MySQL Verification Team
- 5.7+ ./mtr bug101797 Logging: ./mtr bug101797 MySQL Version 5.7.32 Checking supported features... - SSL connections supported Collecting tests... Checking leftover processes... Removing old var directory... Creating var directory '/home/umshastr/work/binaries/ga/mysql-5.7.32/mysql-test/var'... Installing system database... Using parallel: 1 ============================================================================== TEST RESULT TIME (ms) or COMMENT -------------------------------------------------------------------------- worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009 SET @@session.sql_mode=1436549120/*!*/; create table t1(f1 int); insert into t1 values (1); create table tsm1 as select CURRENT_TIMESTAMP as updatetime from t1; show create table tsm1; Table Create Table tsm1 CREATE TABLE `tsm1` ( `updatetime` datetime NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 [ 50%] main.bug101797 [ pass ] 121 - With MyISAM SET @@session.sql_mode=1436549120/*!*/; create table t1(f1 int)engine=myisam; insert into t1 values (1); create table tsm1 engine=myisam as select CURRENT_TIMESTAMP as updatetime from t1; show create table tsm1; Table Create Table tsm1 CREATE TABLE `tsm1` ( `updatetime` datetime NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 [ 50%] main.bug101797 [ pass ] 61 I assume the contributed patch is for 5.6 only as 5.7+ not affected with this
Description: SQL create table as select can skip sql_mode check for datetime default value '0000-00-00 00:00:00' under strict mode. How to repeat: 1、SQLs to reproduce SET @@session.sql_mode=1436549120/*!*/; create table t1(f1 int); insert into t1 values (1); create table tsm1 as select CURRENT_TIMESTAMP as updatetime from t1; 2、verify result mysql> show create table tsm1; +-------+---------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+---------------------------------------------------------------------------------------------------------------------------+ | tsm9 | CREATE TABLE `tsm1` ( `updatetime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+---------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) Suggested fix: From 6a81399e5a2c62837b5ca0c2a99ab221c7f6aa86 Mon Sep 17 00:00:00 2001 From: yuxianjiang <yuxianjiang@tencent.com> Date: Thu, 26 Nov 2020 21:03:34 -0500 Subject: [PATCH] [bugfix] create table having invalid default value with strict sql_mode problem When sql_mode is strict, SQL 'create table tsm1 as select CURRENT_TIMESTAMP as updatetime from t1;' will create a table field updatetime with default value '0000-00-00 00:00:00'. In the same time, SQL 'create table tsm1 (updatetime datetime NOT NULL DEFAULT '0000-00-00 00:00:00')' will lead to error. solution During create table from select item list, check sql_mode for temporal type and make it nullable. --- sql/sql_insert.cc | 3 +++ 1 file changed, 3 insertions(+) diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc index 15d80fd05..98973c84d 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -2641,6 +2641,9 @@ static TABLE *create_table_from_items(THD *thd, HA_CREATE_INFO *create_info, if (item->maybe_null) cr_field->flags &= ~NOT_NULL_FLAG; + if ((cr_field->flags & NOT_NULL_FLAG) && item->is_temporal() + && (thd->variables.sql_mode & (MODE_NO_ZERO_IN_DATE | MODE_NO_ZERO_DATE))) + cr_field->flags &= ~NOT_NULL_FLAG; alter_info->create_list.push_back(cr_field); } -- 2.22.0