diff options
| author | Thirunarayanan Balathandayuthapani <thiru@mariadb.com> | 2018-06-25 14:52:38 +0530 |
|---|---|---|
| committer | Thirunarayanan Balathandayuthapani <thiru@mariadb.com> | 2018-06-25 14:52:38 +0530 |
| commit | 88aaf590ac9fa8c8030a5831cebd867a7f35478f (patch) | |
| tree | 98292a41dce622c8f7de750c067ac87ea6613e98 | |
| parent | 1abd877e2df9e83bc1c2f5195796f427a35bd3f1 (diff) | |
| download | mariadb-git-88aaf590ac9fa8c8030a5831cebd867a7f35478f.tar.gz | |
MDEV-16365 Setting a column NOT NULL fails to return error for
NULL values when there is no DEFAULT
Copy and inplace algorithm works similarly for
NULL to NOT NULL conversion for the following cases:
(1) strict sql mode - Should give error.
(2) non-strict sql mode - Should give warnings alone
(3) alter ignore table command. - Should give warnings alone.
33 files changed, 663 insertions, 349 deletions
diff --git a/mysql-test/main/selectivity_innodb.result b/mysql-test/main/selectivity_innodb.result index 236647c6091..332cc947a6b 100644 --- a/mysql-test/main/selectivity_innodb.result +++ b/mysql-test/main/selectivity_innodb.result @@ -1150,6 +1150,7 @@ alter table t1 change column a a int; analyze table t1; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected +test.t1 analyze Note Data truncated for column 'avg_frequency' at row 1 test.t1 analyze status OK flush table t1; explain extended select * from t1 where a between 5 and 7; diff --git a/mysql-test/main/statistics.result b/mysql-test/main/statistics.result index 8f87b166e8d..b710bc0e51a 100644 --- a/mysql-test/main/statistics.result +++ b/mysql-test/main/statistics.result @@ -1086,6 +1086,9 @@ test t2 idx4 3 1.1304 ANALYZE TABLE t2 PERSISTENT FOR COLUMNS() INDEXES ALL; Table Op Msg_type Msg_text test.t2 analyze status Engine-independent statistics collected +test.t2 analyze Note Data truncated for column 'avg_frequency' at row 1 +test.t2 analyze Note Data truncated for column 'avg_frequency' at row 1 +test.t2 analyze Note Data truncated for column 'avg_frequency' at row 1 test.t2 analyze status OK SELECT * FROM mysql.index_stats ORDER BY index_name, prefix_arity, table_name; db_name table_name index_name prefix_arity avg_frequency @@ -1146,6 +1149,11 @@ test t2 idx4 4 1.0000 ANALYZE TABLE t2 PERSISTENT FOR COLUMNS ALL INDEXES ALL; Table Op Msg_type Msg_text test.t2 analyze status Engine-independent statistics collected +test.t2 analyze Note Data truncated for column 'avg_length' at row 1 +test.t2 analyze Note Data truncated for column 'avg_frequency' at row 1 +test.t2 analyze Note Data truncated for column 'avg_frequency' at row 1 +test.t2 analyze Note Data truncated for column 'avg_frequency' at row 1 +test.t2 analyze Note Data truncated for column 'avg_frequency' at row 1 test.t2 analyze status OK SELECT * FROM mysql.index_stats ORDER BY index_name, prefix_arity, table_name; db_name table_name index_name prefix_arity avg_frequency @@ -1171,6 +1179,8 @@ test t2 idx3 1 8.5000 ANALYZE TABLE t2 PERSISTENT FOR COLUMNS() INDEXES ALL; Table Op Msg_type Msg_text test.t2 analyze status Engine-independent statistics collected +test.t2 analyze Note Data truncated for column 'avg_frequency' at row 1 +test.t2 analyze Note Data truncated for column 'avg_frequency' at row 1 test.t2 analyze status OK SELECT * FROM mysql.index_stats ORDER BY index_name, prefix_arity, table_name; db_name table_name index_name prefix_arity avg_frequency diff --git a/mysql-test/suite/gcol/r/gcol_keys_innodb.result b/mysql-test/suite/gcol/r/gcol_keys_innodb.result index fadbb3b8031..577b3255620 100644 --- a/mysql-test/suite/gcol/r/gcol_keys_innodb.result +++ b/mysql-test/suite/gcol/r/gcol_keys_innodb.result @@ -691,9 +691,13 @@ a b c 1 127 0 SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR ALTER TABLE t ADD UNIQUE INDEX (c(1)); +Warnings: +Warning 1264 Out of range value for column 'b' at row 1 SELECT * FROM t WHERE c = '0'; a b c 1 127 0 +Warnings: +Warning 1264 Out of range value for column 'b' at row 1 DROP TABLE t; # # Bug#21688115 VIRTUAL COLUMN COMPUTATION SAVE_IN_FIELD() diff --git a/mysql-test/suite/innodb/include/alter_non_null.inc b/mysql-test/suite/innodb/include/alter_non_null.inc new file mode 100644 index 00000000000..8acecfb08dc --- /dev/null +++ b/mysql-test/suite/innodb/include/alter_non_null.inc @@ -0,0 +1,71 @@ +--source include/have_innodb.inc + +CREATE TABLE t1(f1 INT)ENGINE=INNODB; +INSERT INTO t1 VALUES(NULL); +SELECT * FROM t1; +--enable_info +--error $error_code +ALTER TABLE t1 CHANGE f1 f1 INT NOT NULL; +--disable_info +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1(f1 CHAR(10))ENGINE=INNODB; +INSERT INTO t1 VALUES(NULL); +SELECT * FROM t1; +--enable_info +--error $error_code +ALTER TABLE t1 CHANGE f1 f1 CHAR(10) NOT NULL; +--disable_info +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1(f1 VARCHAR(10))ENGINE=INNODB; +INSERT INTO t1 VALUES(NULL); +SELECT * FROM t1; +--enable_info +--error $error_code +ALTER TABLE t1 CHANGE f1 f1 VARCHAR(20) NOT NULL; +--disable_info +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1(f1 TEXT)ENGINE=INNODB; +INSERT INTO t1 VALUES(NULL); +SELECT * FROM t1; +--enable_info +--error $error_code +ALTER TABLE t1 CHANGE f1 f1 TEXT NOT NULL DEFAULT 'abc'; +--disable_info +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1(f1 INT NOT NULL, f2 INT NOT NULL, f3 INT)ENGINE=INNODB; +INSERT INTO t1 VALUES(2, 2, NULL); +SELECT * FROM t1; +--enable_info +--error $error_code +ALTER TABLE t1 CHANGE f3 f3 INT NOT NULL DEFAULT (f1 + f2); +--disable_info +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1(f1 INT NOT NULL DEFAULT 0, b TINYINT)ENGINE=InnoDB; +INSERT INTO t1 VALUES(10, NULL); +SELECT * FROM t1; +--enable_info +--error $error_code +ALTER TABLE t1 CHANGE b b TINYINT NOT NULL DEFAULT if(unix_timestamp()>1,1000,0); +--disable_info +SELECT * FROM t1; +DROP TABLE t1; + +# Alter ignore should work irrespective of sql mode + +CREATE TABLE t1(c1 INT NOT NULL, c2 INT, PRIMARY KEY(c1))ENGINE=INNODB; +INSERT INTO t1 VALUES(1, NULL); +--enable_info +ALTER IGNORE TABLE t1 CHANGE c2 c2 INT NOT NULL DEFAULT 2; +--disable_info +SELECT * FROM t1; +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/include/alter_non_null_debug.inc b/mysql-test/suite/innodb/include/alter_non_null_debug.inc new file mode 100644 index 00000000000..f5989391723 --- /dev/null +++ b/mysql-test/suite/innodb/include/alter_non_null_debug.inc @@ -0,0 +1,63 @@ +--source include/have_innodb.inc +--source include/have_debug.inc +--source include/have_debug_sync.inc + +# Alter table should fail for strict sql mode + +CREATE TABLE t1(c1 INT NOT NULL, c2 INT, PRIMARY KEY(c1))ENGINE=INNODB; +INSERT INTO t1 VALUES(1, 1); +--enable_info +SET DEBUG_SYNC= 'row_merge_after_scan +SIGNAL opened WAIT_FOR flushed'; +send ALTER TABLE t1 CHANGE c2 c2 INT NOT NULL DEFAULT 2; +connect (con1,localhost,root); +SET DEBUG_SYNC= 'now WAIT_FOR opened'; +INSERT INTO t1 VALUES(2, NULL); +UPDATE t1 SET c1 = 3 WHERE c2 = 1; +SET DEBUG_SYNC= 'now SIGNAL flushed'; +connection default; +--error $error_code +reap; +--disable_info +SELECT * FROM t1; +DROP TABLE t1; + +# Alter table should successfully apply the log for the alter operation + +CREATE TABLE t1(c1 INT NOT NULL, c2 INT, PRIMARY KEY(c1))ENGINE=INNODB; +INSERT INTO t1 VALUES(1, 1); +--enable_info +SET DEBUG_SYNC= 'row_merge_after_scan +SIGNAL opened WAIT_FOR flushed'; +send ALTER TABLE t1 CHANGE c2 c2 INT NOT NULL DEFAULT 2; +connection con1; +SET DEBUG_SYNC= 'now WAIT_FOR opened'; +INSERT INTO t1 VALUES(2, 3); +UPDATE t1 SET c1 = 3 WHERE c2 = 1; +SET DEBUG_SYNC= 'now SIGNAL flushed'; +connection default; +reap; +--disable_info +SELECT * FROM t1; +DROP TABLE t1; + +# Alter ignore should not give error + +CREATE TABLE t1(c1 INT NOT NULL, c2 INT, c3 INT, PRIMARY KEY(c1))ENGINE=INNODB; +INSERT INTO t1 VALUES(1, 2, 3); +--enable_info +SET DEBUG_SYNC= 'row_merge_after_scan +SIGNAL opened WAIT_FOR flushed'; +send ALTER IGNORE TABLE t1 CHANGE c2 c2 INT NOT NULL DEFAULT 2; +connection con1; +SET DEBUG_SYNC= 'now WAIT_FOR opened'; +UPDATE t1 SET c2= 2 WHERE c1 = 1; +INSERT INTO t1 VALUES (2, NULL, 4); +SET DEBUG_SYNC= 'now SIGNAL flushed'; +connection default; +reap; +--disable_info +SELECT * FROM t1; +DROP TABLE t1; +disconnect con1; +SET DEBUG_SYNC='RESET'; diff --git a/mysql-test/suite/innodb/r/alter_non_null,COPY,NON-STRICT.rdiff b/mysql-test/suite/innodb/r/alter_non_null,COPY,NON-STRICT.rdiff new file mode 100644 index 00000000000..ee1ee3fd8d7 --- /dev/null +++ b/mysql-test/suite/innodb/r/alter_non_null,COPY,NON-STRICT.rdiff @@ -0,0 +1,42 @@ +7,8c7,8 +< affected rows: 0 +< info: Records: 0 Duplicates: 0 Warnings: 1 +--- +> affected rows: 1 +> info: Records: 1 Duplicates: 0 Warnings: 1 +21,22c21,22 +< affected rows: 0 +< info: Records: 0 Duplicates: 0 Warnings: 1 +--- +> affected rows: 1 +> info: Records: 1 Duplicates: 0 Warnings: 1 +35,36c35,36 +< affected rows: 0 +< info: Records: 0 Duplicates: 0 Warnings: 1 +--- +> affected rows: 1 +> info: Records: 1 Duplicates: 0 Warnings: 1 +49,50c49,50 +< affected rows: 0 +< info: Records: 0 Duplicates: 0 Warnings: 1 +--- +> affected rows: 1 +> info: Records: 1 Duplicates: 0 Warnings: 1 +63,64c63,64 +< affected rows: 0 +< info: Records: 0 Duplicates: 0 Warnings: 1 +--- +> affected rows: 1 +> info: Records: 1 Duplicates: 0 Warnings: 1 +77,78c77,78 +< affected rows: 0 +< info: Records: 0 Duplicates: 0 Warnings: 1 +--- +> affected rows: 1 +> info: Records: 1 Duplicates: 0 Warnings: 1 +88,89c88,89 +< affected rows: 0 +< info: Records: 0 Duplicates: 0 Warnings: 1 +--- +> affected rows: 1 +> info: Records: 1 Duplicates: 0 Warnings: 1 diff --git a/mysql-test/suite/innodb/r/alter_non_null,COPY,STRICT.rdiff b/mysql-test/suite/innodb/r/alter_non_null,COPY,STRICT.rdiff new file mode 100644 index 00000000000..d8094c4d2be --- /dev/null +++ b/mysql-test/suite/innodb/r/alter_non_null,COPY,STRICT.rdiff @@ -0,0 +1,72 @@ +7,10c7 +< affected rows: 0 +< info: Records: 0 Duplicates: 0 Warnings: 1 +< Warnings: +< Warning 1265 Data truncated for column 'f1' at row 1 +--- +> ERROR 01000: Data truncated for column 'f1' at row 1 +13c10 +< 0 +--- +> NULL +21,24c18 +< affected rows: 0 +< info: Records: 0 Duplicates: 0 Warnings: 1 +< Warnings: +< Warning 1265 Data truncated for column 'f1' at row 1 +--- +> ERROR 01000: Data truncated for column 'f1' at row 1 +27c21 +< +--- +> NULL +35,38c29 +< affected rows: 0 +< info: Records: 0 Duplicates: 0 Warnings: 1 +< Warnings: +< Warning 1265 Data truncated for column 'f1' at row 1 +--- +> ERROR 01000: Data truncated for column 'f1' at row 1 +41c32 +< +--- +> NULL +49,52c40 +< affected rows: 0 +< info: Records: 0 Duplicates: 0 Warnings: 1 +< Warnings: +< Warning 1265 Data truncated for column 'f1' at row 1 +--- +> ERROR 01000: Data truncated for column 'f1' at row 1 +55c43 +< +--- +> NULL +63,66c51 +< affected rows: 0 +< info: Records: 0 Duplicates: 0 Warnings: 1 +< Warnings: +< Warning 1265 Data truncated for column 'f3' at row 1 +--- +> ERROR 01000: Data truncated for column 'f3' at row 1 +69c54 +< 2 2 0 +--- +> 2 2 NULL +77,80c62 +< affected rows: 0 +< info: Records: 0 Duplicates: 0 Warnings: 1 +< Warnings: +< Warning 1265 Data truncated for column 'b' at row 1 +--- +> ERROR 01000: Data truncated for column 'b' at row 1 +83c65 +< 10 0 +--- +> 10 NULL +88,89c70,71 +< affected rows: 0 +< info: Records: 0 Duplicates: 0 Warnings: 1 +--- +> affected rows: 1 +> info: Records: 1 Duplicates: 0 Warnings: 1 diff --git a/mysql-test/suite/innodb/r/alter_non_null,INPLACE,STRICT.rdiff b/mysql-test/suite/innodb/r/alter_non_null,INPLACE,STRICT.rdiff new file mode 100644 index 00000000000..ec97b174bdf --- /dev/null +++ b/mysql-test/suite/innodb/r/alter_non_null,INPLACE,STRICT.rdiff @@ -0,0 +1,66 @@ +7,10c7 +< affected rows: 0 +< info: Records: 0 Duplicates: 0 Warnings: 1 +< Warnings: +< Warning 1265 Data truncated for column 'f1' at row 1 +--- +> ERROR 01000: Data truncated for column 'f1' at row 1 +13c10 +< 0 +--- +> NULL +21,24c18 +< affected rows: 0 +< info: Records: 0 Duplicates: 0 Warnings: 1 +< Warnings: +< Warning 1265 Data truncated for column 'f1' at row 1 +--- +> ERROR 01000: Data truncated for column 'f1' at row 1 +27c21 +< +--- +> NULL +35,38c29 +< affected rows: 0 +< info: Records: 0 Duplicates: 0 Warnings: 1 +< Warnings: +< Warning 1265 Data truncated for column 'f1' at row 1 +--- +> ERROR 01000: Data truncated for column 'f1' at row 1 +41c32 +< +--- +> NULL +49,52c40 +< affected rows: 0 +< info: Records: 0 Duplicates: 0 Warnings: 1 +< Warnings: +< Warning 1265 Data truncated for column 'f1' at row 1 +--- +> ERROR 01000: Data truncated for column 'f1' at row 1 +55c43 +< +--- +> NULL +63,66c51 +< affected rows: 0 +< info: Records: 0 Duplicates: 0 Warnings: 1 +< Warnings: +< Warning 1265 Data truncated for column 'f3' at row 1 +--- +> ERROR 01000: Data truncated for column 'f3' at row 1 +69c54 +< 2 2 0 +--- +> 2 2 NULL +77,80c62 +< affected rows: 0 +< info: Records: 0 Duplicates: 0 Warnings: 1 +< Warnings: +< Warning 1265 Data truncated for column 'b' at row 1 +--- +> ERROR 01000: Data truncated for column 'b' at row 1 +83c65 +< 10 0 +--- +> 10 NULL diff --git a/mysql-test/suite/innodb/r/alter_non_null.result b/mysql-test/suite/innodb/r/alter_non_null.result new file mode 100644 index 00000000000..15ea6a38b5b --- /dev/null +++ b/mysql-test/suite/innodb/r/alter_non_null.result @@ -0,0 +1,95 @@ +CREATE TABLE t1(f1 INT)ENGINE=INNODB; +INSERT INTO t1 VALUES(NULL); +SELECT * FROM t1; +f1 +NULL +ALTER TABLE t1 CHANGE f1 f1 INT NOT NULL; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 1 +Warnings: +Warning 1265 Data truncated for column 'f1' at row 1 +SELECT * FROM t1; +f1 +0 +DROP TABLE t1; +CREATE TABLE t1(f1 CHAR(10))ENGINE=INNODB; +INSERT INTO t1 VALUES(NULL); +SELECT * FROM t1; +f1 +NULL +ALTER TABLE t1 CHANGE f1 f1 CHAR(10) NOT NULL; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 1 +Warnings: +Warning 1265 Data truncated for column 'f1' at row 1 +SELECT * FROM t1; +f1 + +DROP TABLE t1; +CREATE TABLE t1(f1 VARCHAR(10))ENGINE=INNODB; +INSERT INTO t1 VALUES(NULL); +SELECT * FROM t1; +f1 +NULL +ALTER TABLE t1 CHANGE f1 f1 VARCHAR(20) NOT NULL; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 1 +Warnings: +Warning 1265 Data truncated for column 'f1' at row 1 +SELECT * FROM t1; +f1 + +DROP TABLE t1; +CREATE TABLE t1(f1 TEXT)ENGINE=INNODB; +INSERT INTO t1 VALUES(NULL); +SELECT * FROM t1; +f1 +NULL +ALTER TABLE t1 CHANGE f1 f1 TEXT NOT NULL DEFAULT 'abc'; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 1 +Warnings: +Warning 1265 Data truncated for column 'f1' at row 1 +SELECT * FROM t1; +f1 + +DROP TABLE t1; +CREATE TABLE t1(f1 INT NOT NULL, f2 INT NOT NULL, f3 INT)ENGINE=INNODB; +INSERT INTO t1 VALUES(2, 2, NULL); +SELECT * FROM t1; +f1 f2 f3 +2 2 NULL +ALTER TABLE t1 CHANGE f3 f3 INT NOT NULL DEFAULT (f1 + f2); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 1 +Warnings: +Warning 1265 Data truncated for column 'f3' at row 1 +SELECT * FROM t1; +f1 f2 f3 +2 2 0 +DROP TABLE t1; +CREATE TABLE t1(f1 INT NOT NULL DEFAULT 0, b TINYINT)ENGINE=InnoDB; +INSERT INTO t1 VALUES(10, NULL); +SELECT * FROM t1; +f1 b +10 NULL +ALTER TABLE t1 CHANGE b b TINYINT NOT NULL DEFAULT if(unix_timestamp()>1,1000,0); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 1 +Warnings: +Warning 1265 Data truncated for column 'b' at row 1 +SELECT * FROM t1; +f1 b +10 0 +DROP TABLE t1; +CREATE TABLE t1(c1 INT NOT NULL, c2 INT, PRIMARY KEY(c1))ENGINE=INNODB; +INSERT INTO t1 VALUES(1, NULL); +ALTER IGNORE TABLE t1 CHANGE c2 c2 INT NOT NULL DEFAULT 2; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 1 +Warnings: +Warning 1265 Data truncated for column 'c2' at row 1 +SELECT * FROM t1; +c1 c2 +1 0 +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/r/alter_non_null_debug,STRICT.rdiff b/mysql-test/suite/innodb/r/alter_non_null_debug,STRICT.rdiff new file mode 100644 index 00000000000..2865c5d7f71 --- /dev/null +++ b/mysql-test/suite/innodb/r/alter_non_null_debug,STRICT.rdiff @@ -0,0 +1,15 @@ +18,21c18 +< affected rows: 0 +< info: Records: 0 Duplicates: 0 Warnings: 1 +< Warnings: +< Warning 1265 Data truncated for column 'c2' at row 3 +--- +> ERROR 01000: Data truncated for column 'c2' at row 3 +24c21 +< 2 0 +--- +> 2 NULL +71c68 +< Warning 1265 Data truncated for column 'c2' at row 7 +--- +> Warning 1265 Data truncated for column 'c2' at row 6 diff --git a/mysql-test/suite/innodb/r/alter_non_null_debug.result b/mysql-test/suite/innodb/r/alter_non_null_debug.result new file mode 100644 index 00000000000..e4c42241f5d --- /dev/null +++ b/mysql-test/suite/innodb/r/alter_non_null_debug.result @@ -0,0 +1,78 @@ +CREATE TABLE t1(c1 INT NOT NULL, c2 INT, PRIMARY KEY(c1))ENGINE=INNODB; +INSERT INTO t1 VALUES(1, 1); +SET DEBUG_SYNC= 'row_merge_after_scan +SIGNAL opened WAIT_FOR flushed'; +affected rows: 0 +ALTER TABLE t1 CHANGE c2 c2 INT NOT NULL DEFAULT 2; +connect con1,localhost,root; +SET DEBUG_SYNC= 'now WAIT_FOR opened'; +affected rows: 0 +INSERT INTO t1 VALUES(2, NULL); +affected rows: 1 +UPDATE t1 SET c1 = 3 WHERE c2 = 1; +affected rows: 1 +info: Rows matched: 1 Changed: 1 Warnings: 0 +SET DEBUG_SYNC= 'now SIGNAL flushed'; +affected rows: 0 +connection default; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 1 +Warnings: +Warning 1265 Data truncated for column 'c2' at row 3 +SELECT * FROM t1; +c1 c2 +2 0 +3 1 +DROP TABLE t1; +CREATE TABLE t1(c1 INT NOT NULL, c2 INT, PRIMARY KEY(c1))ENGINE=INNODB; +INSERT INTO t1 VALUES(1, 1); +SET DEBUG_SYNC= 'row_merge_after_scan +SIGNAL opened WAIT_FOR flushed'; +affected rows: 0 +ALTER TABLE t1 CHANGE c2 c2 INT NOT NULL DEFAULT 2; +connection con1; +SET DEBUG_SYNC= 'now WAIT_FOR opened'; +affected rows: 0 +INSERT INTO t1 VALUES(2, 3); +affected rows: 1 +UPDATE t1 SET c1 = 3 WHERE c2 = 1; +affected rows: 1 +info: Rows matched: 1 Changed: 1 Warnings: 0 +SET DEBUG_SYNC= 'now SIGNAL flushed'; +affected rows: 0 +connection default; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +SELECT * FROM t1; +c1 c2 +2 3 +3 1 +DROP TABLE t1; +CREATE TABLE t1(c1 INT NOT NULL, c2 INT, c3 INT, PRIMARY KEY(c1))ENGINE=INNODB; +INSERT INTO t1 VALUES(1, 2, 3); +SET DEBUG_SYNC= 'row_merge_after_scan +SIGNAL opened WAIT_FOR flushed'; +affected rows: 0 +ALTER IGNORE TABLE t1 CHANGE c2 c2 INT NOT NULL DEFAULT 2; +connection con1; +SET DEBUG_SYNC= 'now WAIT_FOR opened'; +affected rows: 0 +UPDATE t1 SET c2= 2 WHERE c1 = 1; +affected rows: 0 +info: Rows matched: 1 Changed: 0 Warnings: 0 +INSERT INTO t1 VALUES (2, NULL, 4); +affected rows: 1 +SET DEBUG_SYNC= 'now SIGNAL flushed'; +affected rows: 0 +connection default; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 1 +Warnings: +Warning 1265 Data truncated for column 'c2' at row 7 +SELECT * FROM t1; +c1 c2 c3 +1 2 3 +2 0 4 +DROP TABLE t1; +disconnect con1; +SET DEBUG_SYNC='RESET'; diff --git a/mysql-test/suite/innodb/r/alter_not_null.result b/mysql-test/suite/innodb/r/alter_not_null.result index 8380378593d..aab9ffa0592 100644 --- a/mysql-test/suite/innodb/r/alter_not_null.result +++ b/mysql-test/suite/innodb/r/alter_not_null.result @@ -1,78 +1,4 @@ set @@sql_mode = 'STRICT_TRANS_TABLES'; -CREATE TABLE t1(f1 INT)ENGINE=INNODB; -INSERT INTO t1 VALUES(NULL); -SELECT * FROM t1; -f1 -NULL -ALTER TABLE t1 CHANGE f1 f1 INT NOT NULL; -affected rows: 0 -info: Records: 0 Duplicates: 0 Warnings: 0 -SELECT * FROM t1; -f1 -0 -DROP TABLE t1; -CREATE TABLE t1(f1 CHAR(10))ENGINE=INNODB; -INSERT INTO t1 VALUES(NULL); -SELECT * FROM t1; -f1 -NULL -ALTER TABLE t1 CHANGE f1 f1 CHAR(10) NOT NULL; -affected rows: 0 -info: Records: 0 Duplicates: 0 Warnings: 0 -SELECT * FROM t1; -f1 - -DROP TABLE t1; -CREATE TABLE t1(f1 VARCHAR(10))ENGINE=INNODB; -INSERT INTO t1 VALUES(NULL); -SELECT * FROM t1; -f1 -NULL -ALTER TABLE t1 CHANGE f1 f1 VARCHAR(20) NOT NULL; -affected rows: 0 -info: Records: 0 Duplicates: 0 Warnings: 0 -SELECT * FROM t1; -f1 - -DROP TABLE t1; -CREATE TABLE t1(f1 TEXT)ENGINE=INNODB; -INSERT INTO t1 VALUES(NULL); -SELECT * FROM t1; -f1 -NULL -ALTER TABLE t1 CHANGE f1 f1 TEXT NOT NULL DEFAULT 'abc'; -affected rows: 0 -info: Records: 0 Duplicates: 0 Warnings: 0 -SELECT * FROM t1; -f1 -abc -DROP TABLE t1; -CREATE TABLE t1(f1 INT NOT NULL, f2 INT NOT NULL, f3 INT)ENGINE=INNODB; -INSERT INTO t1 VALUES(2, 2, NULL); -SELECT * FROM t1; -f1 f2 f3 -2 2 NULL -ALTER TABLE t1 CHANGE f3 f3 INT NOT NULL DEFAULT (f1 + f2), ALGORITHM=INPLACE; -ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: cannot convert NULL to non-constant DEFAULT. Try ALGORITHM=COPY -UPDATE t1 SET f3 = 0; -SELECT * FROM t1; -f1 f2 f3 -2 2 0 -ALTER TABLE t1 CHANGE f3 f3 INT NOT NULL DEFAULT (f1 + f2); -affected rows: 1 -info: Records: 1 Duplicates: 0 Warnings: 0 -SELECT * FROM t1; -f1 f2 f3 -2 2 0 -DROP TABLE t1; -CREATE TABLE t1(f1 INT NOT NULL DEFAULT 0, b TINYINT)ENGINE=InnoDB; -INSERT INTO t1 VALUES(10, NULL); -SELECT * FROM t1; -f1 b -10 NULL -ALTER TABLE t1 CHANGE b b TINYINT NOT NULL DEFAULT if(unix_timestamp()>1,1000,0), algorithm=INPLACE; -ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: cannot convert NULL to non-constant DEFAULT. Try ALGORITHM=COPY -DROP TABLE t1; CREATE TABLE t1(a INT, v INT AS (a), c INT, d INT NOT NULL, e INT) ENGINE=InnoDB; ALTER TABLE t1 DROP COLUMN c, CHANGE COLUMN e e INT NOT NULL, ALGORITHM=INPLACE; DROP TABLE t1; diff --git a/mysql-test/suite/innodb/r/alter_not_null_debug.result b/mysql-test/suite/innodb/r/alter_not_null_debug.result deleted file mode 100644 index 788eef6420b..00000000000 --- a/mysql-test/suite/innodb/r/alter_not_null_debug.result +++ /dev/null @@ -1,68 +0,0 @@ -CREATE TABLE t1(c1 INT NOT NULL, c2 INT, PRIMARY KEY(c1))ENGINE=INNODB; -INSERT INTO t1 VALUES(1, NULL); -SET DEBUG_SYNC= 'row_merge_after_scan -SIGNAL opened WAIT_FOR flushed'; -ALTER TABLE t1 CHANGE c2 c2 INT NOT NULL DEFAULT 2, ALGORITHM=INPLACE; -connect con1,localhost,root; -SET DEBUG_SYNC= 'now WAIT_FOR opened'; -INSERT INTO t1 VALUES(2, NULL); -SET DEBUG_SYNC= 'now SIGNAL flushed'; -connection default; -ERROR 22004: Invalid use of NULL value -SELECT * FROM t1; -c1 c2 -1 NULL -2 NULL -UPDATE t1 SET c2 = 0 WHERE c1 = 2; -SET DEBUG_SYNC= 'row_merge_after_scan -SIGNAL opened WAIT_FOR flushed'; -# Alter ignore can convert the NULL values from -# CONCURRENT DML to constants -ALTER IGNORE TABLE t1 CHANGE c2 c2 INT NOT NULL DEFAULT 2, ALGORITHM=INPLACE; -connection con1; -SET DEBUG_SYNC= 'now WAIT_FOR opened'; -UPDATE t1 SET c2 = NULL WHERE c1 = 2; -INSERT INTO t1 VALUES (3, NULL); -SET DEBUG_SYNC= 'now SIGNAL flushed'; -connection default; -SELECT * FROM t1; -c1 c2 -1 2 -2 2 -3 2 -DROP TABLE t1; -CREATE TABLE t1(c1 INT NOT NULL, c2 INT, c3 INT, PRIMARY KEY(c1))ENGINE=INNODB; -INSERT INTO t1 VALUES(1, NULL, NULL); -SET DEBUG_SYNC= 'row_merge_after_scan -SIGNAL opened WAIT_FOR flushed'; -# Alter Successfully converts from null to not null -ALTER TABLE t1 CHANGE c2 c2 INT NOT NULL DEFAULT 2, ALGORITHM=INPLACE; -connection con1; -SET DEBUG_SYNC= 'now WAIT_FOR opened'; -UPDATE t1 SET c2= 2 WHERE c1 = 1; -INSERT INTO t1 VALUES (2, 3, 4); -SET DEBUG_SYNC= 'now SIGNAL flushed'; -connection default; -SELECT * FROM t1; -c1 c2 c3 -1 2 NULL -2 3 4 -SET DEBUG_SYNC= 'row_merge_after_scan -SIGNAL opened WAIT_FOR flushed'; -# Alter fails because concurrent dml inserts null value -ALTER TABLE t1 CHANGE c3 c3 INT NOT NULL DEFAULT 2, ALGORITHM=INPLACE; -connection con1; -SET DEBUG_SYNC= 'now WAIT_FOR opened'; -UPDATE t1 SET c3= 2 WHERE c1 = 2; -INSERT INTO t1 VALUES (4, 3, NULL); -SET DEBUG_SYNC= 'now SIGNAL flushed'; -connection default; -ERROR 22004: Invalid use of NULL value -SELECT * FROM t1; -c1 c2 c3 -1 2 NULL -2 3 2 -4 3 NULL -DROP TABLE t1; -disconnect con1; -SET DEBUG_SYNC='RESET'; diff --git a/mysql-test/suite/innodb/r/innodb-alter-timestamp.result b/mysql-test/suite/innodb/r/innodb-alter-timestamp.result index b8686d6812e..d4c0aa6a50e 100644 --- a/mysql-test/suite/innodb/r/innodb-alter-timestamp.result +++ b/mysql-test/suite/innodb/r/innodb-alter-timestamp.result @@ -2,16 +2,18 @@ CREATE TABLE t1 (i1 INT UNSIGNED NULL DEFAULT 42) ENGINE=innodb; INSERT INTO t1 VALUES(NULL); ALTER TABLE t1 CHANGE i1 i1 INT UNSIGNED NOT NULL DEFAULT rand(), ALGORITHM=INPLACE; -ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: cannot convert NULL to non-constant DEFAULT. Try ALGORITHM=COPY +ERROR 01000: Data truncated for column 'i1' at row 1 ALTER TABLE t1 CHANGE i1 i1 INT UNSIGNED NOT NULL DEFAULT rand(), ALGORITHM=COPY; ERROR 01000: Data truncated for column 'i1' at row 1 ALTER TABLE t1 CHANGE i1 id INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY(id), ALGORITHM=INPLACE; ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY -ALTER TABLE t1 ADD PRIMARY KEY(i1), ALGORITHM=INPLACE; +ALTER IGNORE TABLE t1 ADD PRIMARY KEY(i1), ALGORITHM=INPLACE; affected rows: 0 -info: Records: 0 Duplicates: 0 Warnings: 0 +info: Records: 0 Duplicates: 0 Warnings: 1 +Warnings: +Warning 1265 Data truncated for column 'i1' at row 1 ALTER TABLE t1 CHANGE i1 id INT UNSIGNED NOT NULL AUTO_INCREMENT; affected rows: 1 info: Records: 1 Duplicates: 0 Warnings: 0 diff --git a/mysql-test/suite/innodb/r/innodb-table-online.result b/mysql-test/suite/innodb/r/innodb-table-online.result index d8482ebb23a..dc7e7e4538f 100644 --- a/mysql-test/suite/innodb/r/innodb-table-online.result +++ b/mysql-test/suite/innodb/r/innodb-table-online.result @@ -409,7 +409,7 @@ ALTER TABLE t1 DROP COLUMN c22f, ADD PRIMARY KEY c3p5(c3(5)); ERROR 42000: Key column 'c22f' doesn't exist in table SET @old_sql_mode = @@sql_mode; SET @@sql_mode = 'STRICT_TRANS_TABLES'; -ALTER TABLE t1 DROP COLUMN c22f, DROP PRIMARY KEY, ADD PRIMARY KEY c3p5(c3(5)), +ALTER IGNORE TABLE t1 DROP COLUMN c22f, DROP PRIMARY KEY, ADD PRIMARY KEY c3p5(c3(5)), ALGORITHM = INPLACE; ERROR 23000: Duplicate entry '' for key 'PRIMARY' SET @@sql_mode = @old_sql_mode; @@ -428,7 +428,7 @@ INSERT INTO t1 VALUES(33101,347,NULL,''); SET DEBUG_SYNC = 'now SIGNAL ins_done0'; # session con1 connection con1; -ERROR 22004: Invalid use of NULL value +ERROR 01000: Data truncated for column 'c3' at row 1 SET @@sql_mode = @old_sql_mode; # session default connection default; diff --git a/mysql-test/suite/innodb/t/alter_non_null.combinations b/mysql-test/suite/innodb/t/alter_non_null.combinations new file mode 100644 index 00000000000..815223ce1cb --- /dev/null +++ b/mysql-test/suite/innodb/t/alter_non_null.combinations @@ -0,0 +1,5 @@ +[COPY] +--alter_algorithm=copy + +[INPLACE] +--alter_algorithm=inplace diff --git a/mysql-test/suite/innodb/t/alter_non_null.inc b/mysql-test/suite/innodb/t/alter_non_null.inc new file mode 100644 index 00000000000..3d26aab5ab1 --- /dev/null +++ b/mysql-test/suite/innodb/t/alter_non_null.inc @@ -0,0 +1,2 @@ +# See also alter_non_null.combinations +--source include/have_innodb.inc diff --git a/mysql-test/suite/innodb/t/alter_non_null.test b/mysql-test/suite/innodb/t/alter_non_null.test new file mode 100644 index 00000000000..bfa159922b4 --- /dev/null +++ b/mysql-test/suite/innodb/t/alter_non_null.test @@ -0,0 +1,10 @@ +--source alter_sql_mode.inc + +let $sql_mode = `SELECT @@SQL_MODE`; +let $error_code = 0; + +if ($sql_mode == "STRICT_TRANS_TABLES") { + let $error_code = WARN_DATA_TRUNCATED; +} + +--source include/alter_non_null.inc diff --git a/mysql-test/suite/innodb/t/alter_non_null_debug.test b/mysql-test/suite/innodb/t/alter_non_null_debug.test new file mode 100644 index 00000000000..8bfa7a4bee4 --- /dev/null +++ b/mysql-test/suite/innodb/t/alter_non_null_debug.test @@ -0,0 +1,10 @@ +--source alter_sql_mode.inc + +let $sql_mode = `SELECT @@SQL_MODE`; +let $error_code = 0; + +if ($sql_mode == "STRICT_TRANS_TABLES") { + let $error_code = WARN_DATA_TRUNCATED; +} + +--source include/alter_non_null_debug.inc diff --git a/mysql-test/suite/innodb/t/alter_not_null.test b/mysql-test/suite/innodb/t/alter_not_null.test index f4606dfa6c5..696df34df68 100644 --- a/mysql-test/suite/innodb/t/alter_not_null.test +++ b/mysql-test/suite/innodb/t/alter_not_null.test @@ -1,62 +1,6 @@ --source include/have_innodb.inc set @@sql_mode = 'STRICT_TRANS_TABLES'; -CREATE TABLE t1(f1 INT)ENGINE=INNODB; -INSERT INTO t1 VALUES(NULL); -SELECT * FROM t1; ---enable_info -ALTER TABLE t1 CHANGE f1 f1 INT NOT NULL; ---disable_info -SELECT * FROM t1; -DROP TABLE t1; - -CREATE TABLE t1(f1 CHAR(10))ENGINE=INNODB; -INSERT INTO t1 VALUES(NULL); -SELECT * FROM t1; ---enable_info -ALTER TABLE t1 CHANGE f1 f1 CHAR(10) NOT NULL; ---disable_info -SELECT * FROM t1; -DROP TABLE t1; - -CREATE TABLE t1(f1 VARCHAR(10))ENGINE=INNODB; -INSERT INTO t1 VALUES(NULL); -SELECT * FROM t1; ---enable_info -ALTER TABLE t1 CHANGE f1 f1 VARCHAR(20) NOT NULL; ---disable_info -SELECT * FROM t1; -DROP TABLE t1; - -CREATE TABLE t1(f1 TEXT)ENGINE=INNODB; -INSERT INTO t1 VALUES(NULL); -SELECT * FROM t1; ---enable_info -ALTER TABLE t1 CHANGE f1 f1 TEXT NOT NULL DEFAULT 'abc'; ---disable_info -SELECT * FROM t1; -DROP TABLE t1; - -CREATE TABLE t1(f1 INT NOT NULL, f2 INT NOT NULL, f3 INT)ENGINE=INNODB; -INSERT INTO t1 VALUES(2, 2, NULL); -SELECT * FROM t1; ---error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON -ALTER TABLE t1 CHANGE f3 f3 INT NOT NULL DEFAULT (f1 + f2), ALGORITHM=INPLACE; -UPDATE t1 SET f3 = 0; -SELECT * FROM t1; ---enable_info -ALTER TABLE t1 CHANGE f3 f3 INT NOT NULL DEFAULT (f1 + f2); ---disable_info -SELECT * FROM t1; -DROP TABLE t1; - -CREATE TABLE t1(f1 INT NOT NULL DEFAULT 0, b TINYINT)ENGINE=InnoDB; -INSERT INTO t1 VALUES(10, NULL); -SELECT * FROM t1; ---error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON -ALTER TABLE t1 CHANGE b b TINYINT NOT NULL DEFAULT if(unix_timestamp()>1,1000,0), algorithm=INPLACE; -DROP TABLE t1; - CREATE TABLE t1(a INT, v INT AS (a), c INT, d INT NOT NULL, e INT) ENGINE=InnoDB; ALTER TABLE t1 DROP COLUMN c, CHANGE COLUMN e e INT NOT NULL, ALGORITHM=INPLACE; DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/alter_not_null_debug.test b/mysql-test/suite/innodb/t/alter_not_null_debug.test deleted file mode 100644 index 9c1500dc829..00000000000 --- a/mysql-test/suite/innodb/t/alter_not_null_debug.test +++ /dev/null @@ -1,68 +0,0 @@ ---source include/have_innodb.inc ---source include/have_debug.inc ---source include/have_debug_sync.inc - -CREATE TABLE t1(c1 INT NOT NULL, c2 INT, PRIMARY KEY(c1))ENGINE=INNODB; -INSERT INTO t1 VALUES(1, NULL); -SET DEBUG_SYNC= 'row_merge_after_scan -SIGNAL opened WAIT_FOR flushed'; -send ALTER TABLE t1 CHANGE c2 c2 INT NOT NULL DEFAULT 2, ALGORITHM=INPLACE; -connect (con1,localhost,root); -SET DEBUG_SYNC= 'now WAIT_FOR opened'; -INSERT INTO t1 VALUES(2, NULL); -SET DEBUG_SYNC= 'now SIGNAL flushed'; -connection default; ---error ER_INVALID_USE_OF_NULL -reap; -SELECT * FROM t1; -UPDATE t1 SET c2 = 0 WHERE c1 = 2; -SET DEBUG_SYNC= 'row_merge_after_scan -SIGNAL opened WAIT_FOR flushed'; ---echo # Alter ignore can convert the NULL values from ---echo # CONCURRENT DML to constants -send ALTER IGNORE TABLE t1 CHANGE c2 c2 INT NOT NULL DEFAULT 2, ALGORITHM=INPLACE; -connection con1; -SET DEBUG_SYNC= 'now WAIT_FOR opened'; -UPDATE t1 SET c2 = NULL WHERE c1 = 2; -INSERT INTO t1 VALUES (3, NULL); -SET DEBUG_SYNC= 'now SIGNAL flushed'; -connection default; -reap; -SELECT * FROM t1; -DROP TABLE t1; - - -CREATE TABLE t1(c1 INT NOT NULL, c2 INT, c3 INT, PRIMARY KEY(c1))ENGINE=INNODB; -INSERT INTO t1 VALUES(1, NULL, NULL); -SET DEBUG_SYNC= 'row_merge_after_scan -SIGNAL opened WAIT_FOR flushed'; ---echo # Alter Successfully converts from null to not null - -send ALTER TABLE t1 CHANGE c2 c2 INT NOT NULL DEFAULT 2, ALGORITHM=INPLACE; - -connection con1; -SET DEBUG_SYNC= 'now WAIT_FOR opened'; -UPDATE t1 SET c2= 2 WHERE c1 = 1; -INSERT INTO t1 VALUES (2, 3, 4); -SET DEBUG_SYNC= 'now SIGNAL flushed'; -connection default; -reap; -SELECT * FROM t1; - -SET DEBUG_SYNC= 'row_merge_after_scan -SIGNAL opened WAIT_FOR flushed'; ---echo # Alter fails because concurrent dml inserts null value - -send ALTER TABLE t1 CHANGE c3 c3 INT NOT NULL DEFAULT 2, ALGORITHM=INPLACE; -connection con1; -SET DEBUG_SYNC= 'now WAIT_FOR opened'; -UPDATE t1 SET c3= 2 WHERE c1 = 2; -INSERT INTO t1 VALUES (4, 3, NULL); -SET DEBUG_SYNC= 'now SIGNAL flushed'; -connection default; ---error ER_INVALID_USE_OF_NULL -reap; -SELECT * FROM t1; -DROP TABLE t1; -disconnect con1; -SET DEBUG_SYNC='RESET'; diff --git a/mysql-test/suite/innodb/t/alter_sql_mode.combinations b/mysql-test/suite/innodb/t/alter_sql_mode.combinations new file mode 100644 index 00000000000..2749bd1077c --- /dev/null +++ b/mysql-test/suite/innodb/t/alter_sql_mode.combinations @@ -0,0 +1,5 @@ +[STRICT] +--sql_mode=STRICT_TRANS_TABLES + +[NON-STRICT] +--sql_mode= diff --git a/mysql-test/suite/innodb/t/alter_sql_mode.inc b/mysql-test/suite/innodb/t/alter_sql_mode.inc new file mode 100644 index 00000000000..d51208502a7 --- /dev/null +++ b/mysql-test/suite/innodb/t/alter_sql_mode.inc @@ -0,0 +1,2 @@ +# See also alter_sql_mode.combinations +--source include/have_innodb.inc diff --git a/mysql-test/suite/innodb/t/innodb-alter-timestamp.test b/mysql-test/suite/innodb/t/innodb-alter-timestamp.test index 32a54354016..8b56fb5be5d 100644 --- a/mysql-test/suite/innodb/t/innodb-alter-timestamp.test +++ b/mysql-test/suite/innodb/t/innodb-alter-timestamp.test @@ -3,7 +3,7 @@ CREATE TABLE t1 (i1 INT UNSIGNED NULL DEFAULT 42) ENGINE=innodb; INSERT INTO t1 VALUES(NULL); --enable_info ---error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +--error WARN_DATA_TRUNCATED ALTER TABLE t1 CHANGE i1 i1 INT UNSIGNED NOT NULL DEFAULT rand(), ALGORITHM=INPLACE; --error WARN_DATA_TRUNCATED @@ -12,7 +12,7 @@ ALGORITHM=COPY; --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON ALTER TABLE t1 CHANGE i1 id INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY(id), ALGORITHM=INPLACE; -ALTER TABLE t1 ADD PRIMARY KEY(i1), ALGORITHM=INPLACE; +ALTER IGNORE TABLE t1 ADD PRIMARY KEY(i1), ALGORITHM=INPLACE; ALTER TABLE t1 CHANGE i1 id INT UNSIGNED NOT NULL AUTO_INCREMENT; --disable_info SELECT * FROM t1; diff --git a/mysql-test/suite/innodb/t/innodb-table-online.test b/mysql-test/suite/innodb/t/innodb-table-online.test index 538e4b68762..edf83247142 100644 --- a/mysql-test/suite/innodb/t/innodb-table-online.test +++ b/mysql-test/suite/innodb/t/innodb-table-online.test @@ -359,7 +359,7 @@ SET @old_sql_mode = @@sql_mode; # And adding a PRIMARY KEY will also add NOT NULL implicitly! SET @@sql_mode = 'STRICT_TRANS_TABLES'; --error ER_DUP_ENTRY -ALTER TABLE t1 DROP COLUMN c22f, DROP PRIMARY KEY, ADD PRIMARY KEY c3p5(c3(5)), +ALTER IGNORE TABLE t1 DROP COLUMN c22f, DROP PRIMARY KEY, ADD PRIMARY KEY c3p5(c3(5)), ALGORITHM = INPLACE; SET @@sql_mode = @old_sql_mode; @@ -384,7 +384,7 @@ SET DEBUG_SYNC = 'now SIGNAL ins_done0'; --echo # session con1 connection con1; ---error ER_INVALID_USE_OF_NULL +--error WARN_DATA_TRUNCATED reap; SET @@sql_mode = @old_sql_mode; diff --git a/sql/field.cc b/sql/field.cc index 88b23028d09..7c1798b983e 100644 --- a/sql/field.cc +++ b/sql/field.cc @@ -11135,6 +11135,7 @@ uint32 Field_blob::max_display_length() const @param level - level of message (Note/Warning/Error) @param code - error code of message to be produced @param cut_increment - whenever we should increase cut fields count + @current_row - current row number @note This function won't produce warning or notes or increase cut fields counter @@ -11152,7 +11153,7 @@ uint32 Field_blob::max_display_length() const bool Field::set_warning(Sql_condition::enum_warning_level level, uint code, - int cut_increment) const + int cut_increment, ulong current_row) const { /* If this field was created only for type conversion purposes it @@ -11163,7 +11164,8 @@ Field::set_warning(Sql_condition::enum_warning_level level, uint code, { thd->cuted_fields+= cut_increment; push_warning_printf(thd, level, code, ER_THD(thd, code), field_name.str, - thd->get_stmt_da()->current_row_for_warning()); + current_row ? current_row + : thd->get_stmt_da()->current_row_for_warning()); return 0; } return level >= Sql_condition::WARN_LEVEL_WARN; diff --git a/sql/field.h b/sql/field.h index 69e53087478..7db9dfbdaa2 100644 --- a/sql/field.h +++ b/sql/field.h @@ -1368,7 +1368,7 @@ public: virtual uint repertoire(void) const { return MY_REPERTOIRE_UNICODE30; } virtual int set_time() { return 1; } bool set_warning(Sql_condition::enum_warning_level, unsigned int code, - int cuted_increment) const; + int cuted_increment, ulong current_row=0) const; protected: bool set_warning(unsigned int code, int cuted_increment) const { diff --git a/sql/sql_table.cc b/sql/sql_table.cc index ac65fbb90c2..0473561fd9e 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -7412,6 +7412,11 @@ static bool mysql_inplace_alter_table(THD *thd, bool reopen_tables= false; bool res; + /* + Set the truncated column values of thd as warning + for alter table. + */ + thd->count_cuted_fields = CHECK_FIELD_WARN; DBUG_ENTER("mysql_inplace_alter_table"); /* diff --git a/storage/innobase/handler/handler0alter.cc b/storage/innobase/handler/handler0alter.cc index 435b2b2403f..665dd40250e 100644 --- a/storage/innobase/handler/handler0alter.cc +++ b/storage/innobase/handler/handler0alter.cc @@ -203,8 +203,12 @@ struct ha_innobase_inplace_ctx : public inplace_alter_handler_ctx /** original column names of the table */ const char* const old_col_names; - /** Whether alter ignore issued. */ - const bool ignore; + /** Allow non-null conversion. + (1) Alter ignore should allow the conversion + irrespective of sql mode. + (2) Don't allow the conversion in strict mode + (3) Allow the conversion only in non-strict mode. */ + const bool allow_not_null; ha_innobase_inplace_ctx(row_prebuilt_t*& prebuilt_arg, dict_index_t** drop_arg, @@ -222,7 +226,7 @@ struct ha_innobase_inplace_ctx : public inplace_alter_handler_ctx ulint add_autoinc_arg, ulonglong autoinc_col_min_value_arg, ulonglong autoinc_col_max_value_arg, - bool ignore_flag) : + bool allow_not_null_flag) : inplace_alter_handler_ctx(), prebuilt (prebuilt_arg), add_index (0), add_key_numbers (0), num_to_add_index (0), @@ -250,7 +254,7 @@ struct ha_innobase_inplace_ctx : public inplace_alter_handler_ctx old_n_cols(prebuilt_arg->table->n_cols), old_cols(prebuilt_arg->table->cols), old_col_names(prebuilt_arg->table->col_names), - ignore(ignore_flag) + allow_not_null(allow_not_null_flag) { ut_ad(old_n_cols >= DATA_N_SYS_COLS); #ifdef UNIV_DEBUG @@ -919,18 +923,6 @@ ha_innobase::check_if_supported_inplace_alter( DBUG_RETURN(HA_ALTER_INPLACE_INSTANT); } - /* Only support NULL -> NOT NULL change if strict table sql_mode - is set. Fall back to COPY for conversion if not strict tables. - In-Place will fail with an error when trying to convert - NULL to a NOT NULL value. */ - if ((ha_alter_info->handler_flags - & ALTER_COLUMN_NOT_NULLABLE) - && !thd_is_strict_mode(m_user_thd)) { - ha_alter_info->unsupported_reason = my_get_err_msg( - ER_ALTER_OPERATION_NOT_SUPPORTED_REASON_NOT_NULL); - DBUG_RETURN(HA_ALTER_INPLACE_NOT_SUPPORTED); - } - /* DROP PRIMARY KEY is only allowed in combination with ADD PRIMARY KEY. */ if ((ha_alter_info->handler_flags @@ -1253,20 +1245,6 @@ ha_innobase::check_if_supported_inplace_alter( } break; default: - /* Changing from NULL to NOT NULL and - set the default constant values. */ - if (f->real_maybe_null() - && !(*af)->real_maybe_null()) { - - if (is_non_const_value(*af)) { - break; - } - - if (!set_default_value(*af)) { - break; - } - } - /* For any other data type, NULL values are not converted. (An AUTO_INCREMENT attribute cannot @@ -3252,20 +3230,23 @@ innobase_check_foreigns( } /** Convert a default value for ADD COLUMN. - -@param heap Memory heap where allocated -@param dfield InnoDB data field to copy to -@param field MySQL value for the column -@param comp nonzero if in compact format */ -static MY_ATTRIBUTE((nonnull)) -void -innobase_build_col_map_add( -/*=======================*/ +@param[in,out] heap Memory heap where allocated +@param[out] dfield InnoDB data field to copy to +@param[in] field MySQL value for the column +@param[in] old_field Old field or NULL if new col is added +@param[in] comp nonzero if in compact format. */ +static void innobase_build_col_map_add( mem_heap_t* heap, dfield_t* dfield, const Field* field, + const Field* old_field, ulint comp) { + if (old_field && old_field->real_maybe_null() + && field->real_maybe_null()) { + return; + } + if (field->is_real_null()) { dfield_set_null(dfield); return; @@ -3275,7 +3256,7 @@ innobase_build_col_map_add( byte* buf = static_cast<byte*>(mem_heap_alloc(heap, size)); - const byte* mysql_data = field->ptr; + const byte* mysql_data = old_field ? old_field->ptr : field->ptr; row_mysql_store_col_in_innobase_format( dfield, buf, true, mysql_data, size, comp); @@ -3362,16 +3343,15 @@ innobase_build_col_map( const Field* altered_field = altered_table->field[i + num_v]; - if (field->real_maybe_null() - && !altered_field->real_maybe_null()) { - /* Don't consider virtual column. - NULL to NOT NULL is not applicable - for virtual column. */ + if (defaults) { innobase_build_col_map_add( - heap, dtuple_get_nth_field( + heap, + dtuple_get_nth_field( defaults, i), altered_field, - dict_table_is_comp(new_table)); + field, + dict_table_is_comp( + new_table)); } col_map[old_i - num_old_v] = i; @@ -3383,6 +3363,7 @@ innobase_build_col_map( innobase_build_col_map_add( heap, dtuple_get_nth_field(defaults, i), altered_table->field[i + num_v], + NULL, dict_table_is_comp(new_table)); found_col: if (is_v) { @@ -5611,7 +5592,8 @@ new_table_failed: !(ha_alter_info->handler_flags & ALTER_ADD_PK_INDEX), ctx->defaults, ctx->col_map, path, - ctx->ignore); + old_table, + ctx->allow_not_null); rw_lock_x_unlock(&clust_index->lock); if (!ok) { @@ -5669,7 +5651,9 @@ error_handling_drop_uncached: ctx->prebuilt->trx, index, NULL, true, NULL, NULL, - path, ctx->ignore); + path, old_table, + ctx->allow_not_null); + rw_lock_x_unlock(&index->lock); if (!ok) { @@ -6850,7 +6834,8 @@ err_exit: ha_alter_info->online, heap, indexed_table, col_names, ULINT_UNDEFINED, 0, 0, - ha_alter_info->ignore); + (ha_alter_info->ignore + || !thd_is_strict_mode(m_user_thd))); } DBUG_ASSERT(m_prebuilt->trx->dict_operation_lock_mode == 0); @@ -6978,7 +6963,8 @@ found_col: heap, m_prebuilt->table, col_names, add_autoinc_col_no, ha_alter_info->create_info->auto_increment_value, - autoinc_col_max_value, ha_alter_info->ignore); + autoinc_col_max_value, + ha_alter_info->ignore || !thd_is_strict_mode(m_user_thd)); DBUG_RETURN(prepare_inplace_alter_table_dict( ha_alter_info, altered_table, table, @@ -7210,7 +7196,7 @@ ok_exit: ctx->add_index, ctx->add_key_numbers, ctx->num_to_add_index, altered_table, ctx->defaults, ctx->col_map, ctx->add_autoinc, ctx->sequence, ctx->skip_pk_sort, - ctx->m_stage, add_v, eval_table); + ctx->m_stage, add_v, eval_table, ctx->allow_not_null); #ifndef DBUG_OFF oom: diff --git a/storage/innobase/include/row0log.h b/storage/innobase/include/row0log.h index 6974ce1b56b..544de02db70 100644 --- a/storage/innobase/include/row0log.h +++ b/storage/innobase/include/row0log.h @@ -61,7 +61,9 @@ row_log_allocate( const ulint* col_map,/*!< in: mapping of old column numbers to new ones, or NULL if !table */ const char* path, /*!< in: where to create temporary file */ - bool ignore) /*!< in: Whether alter ignore issued */ + const TABLE* old_table, /*!< in:table definition before alter */ + bool allow_not_null) /*!< in: allow null to non-null + conversion */ MY_ATTRIBUTE((nonnull(1), warn_unused_result)); /******************************************************//** diff --git a/storage/innobase/include/row0merge.h b/storage/innobase/include/row0merge.h index 7330031c186..ad4005239c3 100644 --- a/storage/innobase/include/row0merge.h +++ b/storage/innobase/include/row0merge.h @@ -322,6 +322,7 @@ this function and it will be passed to other functions for further accounting. @param[in] add_v new virtual columns added along with indexes @param[in] eval_table mysql table used to evaluate virtual column value, see innobase_get_computed_value(). +@param[in] allow_non_null allow the conversion from null to not-null @return DB_SUCCESS or error code */ dberr_t row_merge_build_indexes( @@ -340,7 +341,8 @@ row_merge_build_indexes( bool skip_pk_sort, ut_stage_alter_t* stage, const dict_add_v_col_t* add_v, - struct TABLE* eval_table) + struct TABLE* eval_table, + bool allow_non_null) MY_ATTRIBUTE((warn_unused_result)); /********************************************************************//** diff --git a/storage/innobase/row/row0log.cc b/storage/innobase/row/row0log.cc index bf20debbef6..ebd15ab5020 100644 --- a/storage/innobase/row/row0log.cc +++ b/storage/innobase/row/row0log.cc @@ -39,6 +39,7 @@ Created 2011-05-26 Marko Makela #include "ut0stage.h" #include "trx0rec.h" +#include <sql_class.h> #include <algorithm> #include <map> @@ -226,9 +227,11 @@ struct row_log_t { table could be emptied, so that table->is_instant() no longer holds, but all log records must be in the "instant" format. */ unsigned n_core_fields; - bool ignore; /*!< Whether the alter ignore is being used; + bool allow_not_null; /*!< Whether the alter ignore is being + used or if the sql mode is non-strict mode; if not, NULL values will not be converted to defaults */ + const TABLE* old_table; /*< Use old table in case of error. */ /** Determine whether the log should be in the 'instant ADD' format @param[in] index the clustered index of the source table @@ -1107,6 +1110,7 @@ table @param[in] i rec field corresponding to col @param[in] page_size page size of the old table @param[in] max_len maximum length of dfield +@param[in] log row log for the table @retval DB_INVALID_NULL if a NULL value is encountered @retval DB_TOO_BIG_INDEX_COL if the maximum prefix length is exceeded */ static @@ -1120,8 +1124,7 @@ row_log_table_get_pk_col( ulint i, const page_size_t& page_size, ulint max_len, - bool ignore, - const dtuple_t* defaults) + const row_log_t* log) { const byte* field; ulint len; @@ -1129,12 +1132,16 @@ row_log_table_get_pk_col( field = rec_get_nth_field(rec, offsets, i, &len); if (len == UNIV_SQL_NULL) { - if (!ignore || !defaults->fields[i].data) { + + if (!log->allow_not_null) { return(DB_INVALID_NULL); } - field = static_cast<const byte*>(defaults->fields[i].data); - len = defaults->fields[i].len; + ulint n_default_cols = i - DATA_N_SYS_COLS; + + field = static_cast<const byte*>( + log->defaults->fields[n_default_cols].data); + len = log->defaults->fields[i - DATA_N_SYS_COLS].len; } if (rec_offs_nth_extern(offsets, i)) { @@ -1298,8 +1305,7 @@ row_log_table_get_pk( log->error = row_log_table_get_pk_col( ifield, dfield, *heap, - rec, offsets, i, page_size, max_len, - log->ignore, log->defaults); + rec, offsets, i, page_size, max_len, log); if (log->error != DB_SUCCESS) { err_exit: @@ -1484,7 +1490,9 @@ row_log_table_apply_convert_mrec( reason of failure */ { dtuple_t* row; + static ulong n_rows = index->table->stat_n_rows; + n_rows++; *error = DB_SUCCESS; /* This is based on row_build(). */ @@ -1613,8 +1621,12 @@ blob_done: const dfield_t& default_field = log->defaults->fields[col_no]; + Field* field = log->old_table->field[col_no]; + + field->set_warning(Sql_condition::WARN_LEVEL_WARN, + WARN_DATA_TRUNCATED, 1, n_rows); - if (!log->ignore || !default_field.data) { + if (!log->allow_not_null) { /* We got a NULL value for a NOT NULL column. */ *error = DB_INVALID_NULL; return NULL; @@ -3109,7 +3121,9 @@ row_log_allocate( const ulint* col_map,/*!< in: mapping of old column numbers to new ones, or NULL if !table */ const char* path, /*!< in: where to create temporary file */ - const bool ignore) /*!< in: alter ignore issued */ + const TABLE* old_table, /*!< in: table definition before alter */ + const bool allow_not_null) /*!< in: allow null to not-null + conversion */ { row_log_t* log; DBUG_ENTER("row_log_allocate"); @@ -3150,7 +3164,8 @@ row_log_allocate( log->path = path; log->n_core_fields = index->n_core_fields; ut_ad(!table || log->is_instant(index) == index->is_instant()); - log->ignore=ignore; + log->allow_not_null = allow_not_null; + log->old_table = old_table; dict_index_set_online_status(index, ONLINE_INDEX_CREATION); index->online_log = log; diff --git a/storage/innobase/row/row0merge.cc b/storage/innobase/row/row0merge.cc index afa4ce208e9..8f1bb43041f 100644 --- a/storage/innobase/row/row0merge.cc +++ b/storage/innobase/row/row0merge.cc @@ -1674,6 +1674,7 @@ stage->inc() will be called for each page read. @param[in,out] crypt_block crypted file buffer @param[in] eval_table mysql table used to evaluate virtual column value, see innobase_get_computed_value(). +@param[in] allow_not_null allow null to not-null conversion @return DB_SUCCESS or error */ static MY_ATTRIBUTE((warn_unused_result)) dberr_t @@ -1700,7 +1701,8 @@ row_merge_read_clustered_index( ut_stage_alter_t* stage, double pct_cost, row_merge_block_t* crypt_block, - struct TABLE* eval_table) + struct TABLE* eval_table, + bool allow_not_null) { dict_index_t* clust_index; /* Clustered index */ mem_heap_t* row_heap; /* Heap memory to create @@ -1914,6 +1916,7 @@ row_merge_read_clustered_index( mach_write_to_8(new_sys_trx_start, trx->id); mach_write_to_8(new_sys_trx_end, TRX_ID_MAX); + ulong n_rows = 0; /* Scan the clustered index. */ for (;;) { @@ -2046,6 +2049,8 @@ end_of_index: rec = page_cur_get_rec(cur); + n_rows++; + if (online) { offsets = rec_get_offsets(rec, clust_index, NULL, true, ULINT_UNDEFINED, &row_heap); @@ -2174,15 +2179,23 @@ end_of_index: ut_ad(dfield_get_type(field)->prtype & DATA_NOT_NULL); if (dfield_is_null(field)) { - const dfield_t& default_field - = defaults->fields[nonnull[i]]; - if (default_field.data == NULL) { + Field* null_field = + table->field[nonnull[i]]; + + null_field->set_warning( + Sql_condition::WARN_LEVEL_WARN, + WARN_DATA_TRUNCATED, 1, n_rows); + + if (!allow_not_null) { err = DB_INVALID_NULL; trx->error_key_num = 0; goto func_exit; } + const dfield_t& default_field + = defaults->fields[nonnull[i]]; + *field = default_field; } } @@ -4547,6 +4560,7 @@ this function and it will be passed to other functions for further accounting. @param[in] add_v new virtual columns added along with indexes @param[in] eval_table mysql table used to evaluate virtual column value, see innobase_get_computed_value(). +@param[in] allow_not_null allow the conversion from null to not-null @return DB_SUCCESS or error code */ dberr_t row_merge_build_indexes( @@ -4565,7 +4579,8 @@ row_merge_build_indexes( bool skip_pk_sort, ut_stage_alter_t* stage, const dict_add_v_col_t* add_v, - struct TABLE* eval_table) + struct TABLE* eval_table, + bool allow_not_null) { merge_file_t* merge_files; row_merge_block_t* block; @@ -4729,7 +4744,7 @@ row_merge_build_indexes( fts_sort_idx, psort_info, merge_files, key_numbers, n_indexes, defaults, add_v, col_map, add_autoinc, sequence, block, skip_pk_sort, &tmpfd, stage, - pct_cost, crypt_block, eval_table); + pct_cost, crypt_block, eval_table, allow_not_null); stage->end_phase_read_pk(); |