diff options
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(); |