diff options
Diffstat (limited to 'mysql-test')
44 files changed, 1066 insertions, 118 deletions
diff --git a/mysql-test/extra/binlog_tests/binlog.test b/mysql-test/extra/binlog_tests/binlog.test index 3bbc693ceda..5b57eab1005 100644 --- a/mysql-test/extra/binlog_tests/binlog.test +++ b/mysql-test/extra/binlog_tests/binlog.test @@ -205,16 +205,55 @@ DROP PROCEDURE p4; --echo End of 5.0 tests -# Test of a too big SET INSERT_ID: see if the truncated value goes -# into binlog (right), or the too big value (wrong); we look at the -# binlog further down with SHOW BINLOG EVENTS. +# Test of a too big SET INSERT_ID. +# This should generate an error and should not be put in binlog +# We look at the binlog further down with SHOW BINLOG EVENTS. + reset master; create table t1 (id tinyint auto_increment primary key); +insert into t1 values(5); +set insert_id=128; +--error 167 +insert into t1 values(null) /* Not binlogged */; + +# The followin insert ignore will be put in binlog set insert_id=128; -insert into t1 values(null); +insert ignore into t1 values(null) /* Insert 128 */; + +# Insert with duplicate key error should not go into binglo +set insert_id=5; +--error ER_DUP_ENTRY +insert into t1 values(null) /* Not binlogged */; + +# Insert with autogenerated key + duplicate key error should go into binlog +set insert_id=5; +insert ignore into t1 values(null) /* Insert 5 */; select * from t1; drop table t1; +# Same tests but with 2 rows inserted at a time + +create table t1 (id tinyint auto_increment primary key) engine=myisam; +set insert_id=128; +--error 167 +insert into t1 values(5),(null) /* Insert_id 128 */; + +# The followin insert ignore will be put in binlog +set insert_id=128; +insert ignore into t1 values (4),(null) /* Insert_id 128 */; + +# Insert with duplicate key error should not go into binglo +set insert_id=5; +--error ER_DUP_ENTRY +insert into t1 values(3),(null) /* Insert_id 5 */; + +# Insert with autogenerated key + duplicate key error should go into binlog +set insert_id=5; +insert ignore into t1 values(2),(null) /* Insert_id 5 */; +select * from t1 order by id; +drop table t1; + + # bug#22027 create table t1 (a int); create table if not exists t2 select * from t1; diff --git a/mysql-test/extra/binlog_tests/binlog_insert_delayed.test b/mysql-test/extra/binlog_tests/binlog_insert_delayed.test index 7b31a3ebf17..8f669fc1152 100644 --- a/mysql-test/extra/binlog_tests/binlog_insert_delayed.test +++ b/mysql-test/extra/binlog_tests/binlog_insert_delayed.test @@ -28,6 +28,7 @@ # BUG#20627: INSERT DELAYED does not honour auto_increment_* variables # Bug in this test: BUG#38068: binlog_stm_binlog fails sporadically in pushbuild +reset master; create table t1 (a int not null auto_increment, primary key (a)) engine=myisam; diff --git a/mysql-test/extra/rpl_tests/rpl_auto_increment.test b/mysql-test/extra/rpl_tests/rpl_auto_increment.test index bd5943d46ea..8cd86de8040 100644 --- a/mysql-test/extra/rpl_tests/rpl_auto_increment.test +++ b/mysql-test/extra/rpl_tests/rpl_auto_increment.test @@ -111,7 +111,7 @@ set auto_increment_increment=11; set auto_increment_offset=4; insert into t1 values(null); insert into t1 values(null); ---error ER_DUP_ENTRY +--error 167 insert into t1 values(null); select a, mod(a-@@auto_increment_offset,@@auto_increment_increment) from t1 order by a; @@ -120,6 +120,8 @@ create table t2 (a tinyint unsigned not null auto_increment primary key) engine= set auto_increment_increment=10; set auto_increment_offset=1; set insert_id=1000; +insert into t2 values(10); +--error 167 insert into t2 values(null); select a, mod(a-@@auto_increment_offset,@@auto_increment_increment) from t2 order by a; @@ -127,6 +129,7 @@ select a, mod(a-@@auto_increment_offset,@@auto_increment_increment) from t2 orde create table t3 like t1; set auto_increment_increment=1000; set auto_increment_offset=700; +--error 167 insert into t3 values(null); select * from t3 order by a; sync_slave_with_master; diff --git a/mysql-test/extra/rpl_tests/rpl_insert_delayed.test b/mysql-test/extra/rpl_tests/rpl_insert_delayed.test index df08622b0bd..62e90b629ba 100644 --- a/mysql-test/extra/rpl_tests/rpl_insert_delayed.test +++ b/mysql-test/extra/rpl_tests/rpl_insert_delayed.test @@ -102,7 +102,9 @@ CREATE TABLE t1(a int, UNIQUE(a)); --let $_start= query_get_value(SHOW MASTER STATUS, Position, 1) INSERT DELAYED IGNORE INTO t1 VALUES(1); +--disable_warnings INSERT DELAYED IGNORE INTO t1 VALUES(1); +--enable_warnings flush table t1; # to wait for INSERT DELAYED to be done if (`SELECT @@global.binlog_format = 'STATEMENT'`) { diff --git a/mysql-test/include/strict_autoinc.inc b/mysql-test/include/strict_autoinc.inc index 823efcc2040..3298008b66f 100644 --- a/mysql-test/include/strict_autoinc.inc +++ b/mysql-test/include/strict_autoinc.inc @@ -19,11 +19,12 @@ select count(*) from t1; set auto_increment_increment=1000; set auto_increment_offset=700; ---error ER_WARN_DATA_OUT_OF_RANGE +--error 167 insert into t1 values(null); select count(*) from t1; set @@sql_mode=@org_mode; +--error 167 insert into t1 values(null); select * from t1; diff --git a/mysql-test/r/auto_increment.result b/mysql-test/r/auto_increment.result index 30b4ff11227..12cbf294b69 100644 --- a/mysql-test/r/auto_increment.result +++ b/mysql-test/r/auto_increment.result @@ -150,7 +150,7 @@ select last_insert_id(); last_insert_id() 255 insert into t1 set i = null; -ERROR 23000: Duplicate entry '255' for key 'PRIMARY' +ERROR 22003: Out of range value for column 'i' at row 1 select last_insert_id(); last_insert_id() 255 @@ -162,8 +162,7 @@ select last_insert_id(); last_insert_id() 255 insert into t1 set i = null; -Warnings: -Warning 1264 Out of range value for column 'i' at row 1 +ERROR 22003: Out of range value for column 'i' at row 1 select last_insert_id(); last_insert_id() 255 @@ -487,7 +486,7 @@ SELECT @@SESSION.AUTO_INCREMENT_OFFSET; @@SESSION.AUTO_INCREMENT_OFFSET 1 INSERT INTO t1 VALUES (NULL), (NULL), (NULL); -ERROR 22003: Out of range value for column 't1' at row 167 +ERROR 22003: Out of range value for column 'c1' at row 2 SELECT * FROM t1; c1 1 diff --git a/mysql-test/r/auto_increment_ranges_innodb.result b/mysql-test/r/auto_increment_ranges_innodb.result new file mode 100644 index 00000000000..fb936ddfd2b --- /dev/null +++ b/mysql-test/r/auto_increment_ranges_innodb.result @@ -0,0 +1,266 @@ +set default_storage_engine=innodb; +drop table if exists t1; +# +# Testing ranges with smallint +# +create table t1 (a smallint primary key auto_increment); +insert into t1 values(32767); +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +truncate table t1; +insert into t1 values(32767-1); +insert into t1 values(NULL); +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +select * from t1; +a +32766 +32767 +truncate table t1; +insert into t1 values(32767),(NULL); +ERROR 22003: Out of range value for column 'a' at row 2 +select * from t1; +a +truncate table t1; +insert into t1 values(32767-1),(NULL),(NULL); +ERROR 22003: Out of range value for column 'a' at row 3 +truncate table t1; +insert into t1 values(32767+1); +Warnings: +Warning 1264 Out of range value for column 'a' at row 1 +select * from t1; +a +32767 +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +drop table t1; +# +# Testing ranges with unsigned smallint +# +create table t1 (a smallint unsigned primary key auto_increment); +insert into t1 values(65535); +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +truncate table t1; +insert into t1 values(65535-1); +insert into t1 values(NULL); +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +select * from t1; +a +65534 +65535 +truncate table t1; +insert into t1 values(65535),(NULL); +ERROR 22003: Out of range value for column 'a' at row 2 +select * from t1; +a +truncate table t1; +insert into t1 values(65535-1),(NULL),(NULL); +ERROR 22003: Out of range value for column 'a' at row 3 +truncate table t1; +insert into t1 values(65535+1); +Warnings: +Warning 1264 Out of range value for column 'a' at row 1 +select * from t1; +a +65535 +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +drop table t1; +# +# Testing ranges with integer +# +create table t1 (a int primary key auto_increment); +insert into t1 values(2147483647); +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +truncate table t1; +insert into t1 values(2147483647-1); +insert into t1 values(NULL); +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +select * from t1; +a +2147483646 +2147483647 +truncate table t1; +insert into t1 values(2147483647),(NULL); +ERROR 22003: Out of range value for column 'a' at row 2 +select * from t1; +a +truncate table t1; +insert into t1 values(2147483647-1),(NULL),(NULL); +ERROR 22003: Out of range value for column 'a' at row 3 +truncate table t1; +insert into t1 values(2147483647+1); +Warnings: +Warning 1264 Out of range value for column 'a' at row 1 +select * from t1; +a +2147483647 +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +drop table t1; +# +# Testing ranges with unsigned integer +# +create table t1 (a int unsigned primary key auto_increment); +insert into t1 values(4294967295); +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +truncate table t1; +insert into t1 values(4294967295-1); +insert into t1 values(NULL); +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +select * from t1; +a +4294967294 +4294967295 +truncate table t1; +insert into t1 values(4294967295),(NULL); +ERROR 22003: Out of range value for column 'a' at row 2 +select * from t1; +a +truncate table t1; +insert into t1 values(4294967295-1),(NULL),(NULL); +ERROR 22003: Out of range value for column 'a' at row 3 +truncate table t1; +insert into t1 values(4294967295+1); +Warnings: +Warning 1264 Out of range value for column 'a' at row 1 +select * from t1; +a +4294967295 +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +drop table t1; +# +# Testing ranges with bigint +# +create table t1 (a bigint primary key auto_increment); +insert into t1 values(cast(9223372036854775807 as unsigned)); +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +truncate table t1; +insert into t1 values(cast(9223372036854775807 as unsigned)-1); +insert into t1 values(NULL); +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +select * from t1; +a +9223372036854775806 +9223372036854775807 +truncate table t1; +insert into t1 values(cast(9223372036854775807 as unsigned)),(NULL); +ERROR 22003: Out of range value for column 'a' at row 2 +select * from t1; +a +truncate table t1; +insert into t1 values(cast(9223372036854775807 as unsigned)-1),(NULL),(NULL); +ERROR 22003: Out of range value for column 'a' at row 3 +truncate table t1; +insert into t1 values(cast(9223372036854775807 as unsigned)+1); +Warnings: +Warning 1264 Out of range value for column 'a' at row 1 +select * from t1; +a +9223372036854775807 +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +drop table t1; +# +# Testing ranges with unsigned bigint +# +create table t1 (a bigint unsigned primary key auto_increment); +insert into t1 values(18446744073709551615-1); +insert into t1 values(NULL); +ERROR HY000: Failed to read auto-increment value from storage engine +insert into t1 values(NULL); +ERROR HY000: Failed to read auto-increment value from storage engine +truncate table t1; +insert into t1 values(18446744073709551615-1); +insert into t1 values(NULL); +ERROR HY000: Failed to read auto-increment value from storage engine +insert into t1 values(NULL); +ERROR HY000: Failed to read auto-increment value from storage engine +select * from t1; +a +18446744073709551614 +truncate table t1; +insert into t1 values(18446744073709551615),(NULL); +ERROR HY000: Failed to read auto-increment value from storage engine +select * from t1; +a +truncate table t1; +insert into t1 values(18446744073709551615-1),(NULL),(NULL); +ERROR HY000: Failed to read auto-increment value from storage engine +drop table t1; +# +# Test IGNORE and strict mode +# +create table t1 (a smallint primary key auto_increment); +insert ignore into t1 values(32766),(NULL),(NULL),(1); +Warnings: +Warning 167 Out of range value for column 'a' at row 3 +select * from t1; +a +1 +32766 +32767 +truncate table t1; +set @org_mode=@@sql_mode; +set @@sql_mode='ansi,traditional'; +insert ignore into t1 values(32766),(NULL),(NULL); +Warnings: +Warning 167 Out of range value for column 'a' at row 3 +truncate table t1; +insert into t1 values(32766),(NULL),(NULL); +ERROR 22003: Out of range value for column 'a' at row 3 +set @@sql_mode=@org_mode; +drop table t1; +# +# Test auto increment with negative numbers +# +CREATE TABLE t1 (a INTEGER AUTO_INCREMENT, PRIMARY KEY (a)); +INSERT INTO t1 VALUES (NULL), (2), (-5), (NULL); +INSERT INTO t1 VALUES (NULL); +SELECT * FROM t1; +a +-5 +1 +2 +3 +5 +TRUNCATE TABLE t1; +INSERT INTO t1 VALUES (-5), (NULL); +SELECT * FROM t1; +a +-5 +1 +DROP TABLE t1; +# +# Test inserting a value out-of-range into an auto increment column +# +CREATE TABLE t1 (a smallint AUTO_INCREMENT, PRIMARY KEY (a)); +INSERT INTO t1 VALUES (2); +INSERT INTO t1 VALUES (32768); +Warnings: +Warning 1264 Out of range value for column 'a' at row 1 +INSERT INTO t1 VALUES (NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +SELECT * FROM t1; +a +2 +32767 +DROP TABLE t1; +# +# Test old behaviour +# +create table t1 (a smallint primary key auto_increment); +insert into t1 values(32766),(NULL); +delete from t1 where a=32767; +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +drop table t1; diff --git a/mysql-test/r/auto_increment_ranges_myisam.result b/mysql-test/r/auto_increment_ranges_myisam.result new file mode 100644 index 00000000000..5101321c864 --- /dev/null +++ b/mysql-test/r/auto_increment_ranges_myisam.result @@ -0,0 +1,272 @@ +set default_storage_engine=MYISAM; +drop table if exists t1; +# +# Testing ranges with smallint +# +create table t1 (a smallint primary key auto_increment); +insert into t1 values(32767); +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +truncate table t1; +insert into t1 values(32767-1); +insert into t1 values(NULL); +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +select * from t1; +a +32766 +32767 +truncate table t1; +insert into t1 values(32767),(NULL); +ERROR 22003: Out of range value for column 'a' at row 2 +select * from t1; +a +32767 +truncate table t1; +insert into t1 values(32767-1),(NULL),(NULL); +ERROR 22003: Out of range value for column 'a' at row 3 +truncate table t1; +insert into t1 values(32767+1); +Warnings: +Warning 1264 Out of range value for column 'a' at row 1 +select * from t1; +a +32767 +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +drop table t1; +# +# Testing ranges with unsigned smallint +# +create table t1 (a smallint unsigned primary key auto_increment); +insert into t1 values(65535); +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +truncate table t1; +insert into t1 values(65535-1); +insert into t1 values(NULL); +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +select * from t1; +a +65534 +65535 +truncate table t1; +insert into t1 values(65535),(NULL); +ERROR 22003: Out of range value for column 'a' at row 2 +select * from t1; +a +65535 +truncate table t1; +insert into t1 values(65535-1),(NULL),(NULL); +ERROR 22003: Out of range value for column 'a' at row 3 +truncate table t1; +insert into t1 values(65535+1); +Warnings: +Warning 1264 Out of range value for column 'a' at row 1 +select * from t1; +a +65535 +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +drop table t1; +# +# Testing ranges with integer +# +create table t1 (a int primary key auto_increment); +insert into t1 values(2147483647); +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +truncate table t1; +insert into t1 values(2147483647-1); +insert into t1 values(NULL); +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +select * from t1; +a +2147483646 +2147483647 +truncate table t1; +insert into t1 values(2147483647),(NULL); +ERROR 22003: Out of range value for column 'a' at row 2 +select * from t1; +a +2147483647 +truncate table t1; +insert into t1 values(2147483647-1),(NULL),(NULL); +ERROR 22003: Out of range value for column 'a' at row 3 +truncate table t1; +insert into t1 values(2147483647+1); +Warnings: +Warning 1264 Out of range value for column 'a' at row 1 +select * from t1; +a +2147483647 +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +drop table t1; +# +# Testing ranges with unsigned integer +# +create table t1 (a int unsigned primary key auto_increment); +insert into t1 values(4294967295); +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +truncate table t1; +insert into t1 values(4294967295-1); +insert into t1 values(NULL); +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +select * from t1; +a +4294967294 +4294967295 +truncate table t1; +insert into t1 values(4294967295),(NULL); +ERROR 22003: Out of range value for column 'a' at row 2 +select * from t1; +a +4294967295 +truncate table t1; +insert into t1 values(4294967295-1),(NULL),(NULL); +ERROR 22003: Out of range value for column 'a' at row 3 +truncate table t1; +insert into t1 values(4294967295+1); +Warnings: +Warning 1264 Out of range value for column 'a' at row 1 +select * from t1; +a +4294967295 +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +drop table t1; +# +# Testing ranges with bigint +# +create table t1 (a bigint primary key auto_increment); +insert into t1 values(cast(9223372036854775807 as unsigned)); +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +truncate table t1; +insert into t1 values(cast(9223372036854775807 as unsigned)-1); +insert into t1 values(NULL); +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +select * from t1; +a +9223372036854775806 +9223372036854775807 +truncate table t1; +insert into t1 values(cast(9223372036854775807 as unsigned)),(NULL); +ERROR 22003: Out of range value for column 'a' at row 2 +select * from t1; +a +9223372036854775807 +truncate table t1; +insert into t1 values(cast(9223372036854775807 as unsigned)-1),(NULL),(NULL); +ERROR 22003: Out of range value for column 'a' at row 3 +truncate table t1; +insert into t1 values(cast(9223372036854775807 as unsigned)+1); +Warnings: +Warning 1264 Out of range value for column 'a' at row 1 +select * from t1; +a +9223372036854775807 +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +drop table t1; +# +# Testing ranges with unsigned bigint +# +create table t1 (a bigint unsigned primary key auto_increment); +insert into t1 values(18446744073709551615-1); +insert into t1 values(NULL); +ERROR HY000: Failed to read auto-increment value from storage engine +insert into t1 values(NULL); +ERROR HY000: Failed to read auto-increment value from storage engine +truncate table t1; +insert into t1 values(18446744073709551615-1); +insert into t1 values(NULL); +ERROR HY000: Failed to read auto-increment value from storage engine +insert into t1 values(NULL); +ERROR HY000: Failed to read auto-increment value from storage engine +select * from t1; +a +18446744073709551614 +truncate table t1; +insert into t1 values(18446744073709551615),(NULL); +ERROR HY000: Failed to read auto-increment value from storage engine +select * from t1; +a +18446744073709551615 +truncate table t1; +insert into t1 values(18446744073709551615-1),(NULL),(NULL); +ERROR HY000: Failed to read auto-increment value from storage engine +drop table t1; +# +# Test IGNORE and strict mode +# +create table t1 (a smallint primary key auto_increment); +insert ignore into t1 values(32766),(NULL),(NULL),(1); +Warnings: +Warning 167 Out of range value for column 'a' at row 3 +select * from t1; +a +1 +32766 +32767 +truncate table t1; +set @org_mode=@@sql_mode; +set @@sql_mode='ansi,traditional'; +insert ignore into t1 values(32766),(NULL),(NULL); +Warnings: +Warning 167 Out of range value for column 'a' at row 3 +truncate table t1; +insert into t1 values(32766),(NULL),(NULL); +ERROR 22003: Out of range value for column 'a' at row 3 +set @@sql_mode=@org_mode; +drop table t1; +# +# Test auto increment with negative numbers +# +CREATE TABLE t1 (a INTEGER AUTO_INCREMENT, PRIMARY KEY (a)); +INSERT INTO t1 VALUES (NULL), (2), (-5), (NULL); +INSERT INTO t1 VALUES (NULL); +SELECT * FROM t1; +a +-5 +1 +2 +3 +4 +TRUNCATE TABLE t1; +INSERT INTO t1 VALUES (-5), (NULL); +SELECT * FROM t1; +a +-5 +1 +DROP TABLE t1; +# +# Test inserting a value out-of-range into an auto increment column +# +CREATE TABLE t1 (a smallint AUTO_INCREMENT, PRIMARY KEY (a)); +INSERT INTO t1 VALUES (2); +INSERT INTO t1 VALUES (32768); +Warnings: +Warning 1264 Out of range value for column 'a' at row 1 +INSERT INTO t1 VALUES (NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +SELECT * FROM t1; +a +2 +32767 +DROP TABLE t1; +# +# Test old behaviour +# +create table t1 (a smallint primary key auto_increment); +insert into t1 values(32766),(NULL); +delete from t1 where a=32767; +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +drop table t1; diff --git a/mysql-test/r/commit_1innodb.result b/mysql-test/r/commit_1innodb.result index 3e3d75f66e4..af198edc4ca 100644 --- a/mysql-test/r/commit_1innodb.result +++ b/mysql-test/r/commit_1innodb.result @@ -529,6 +529,8 @@ SUCCESS # 13. Read-write statement: INSERT IGNORE, change 0 rows. # insert ignore t1 set a=2; +Warnings: +Warning 1062 Duplicate entry '2' for key 'a' call p_verify_status_increment(2, 2, 1, 0); SUCCESS diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index 0a6dff6d9a3..ba52959be84 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -2396,6 +2396,8 @@ a b drop table t1; create table if not exists t1 (a int unique, b int) ignore select 1 as a, 1 as b union select 1 as a, 2 as b; +Warnings: +Warning 1062 Duplicate entry '1' for key 'a' select * from t1; a b 1 1 diff --git a/mysql-test/r/insert.result b/mysql-test/r/insert.result index 655303be7f4..7110f541fb7 100644 --- a/mysql-test/r/insert.result +++ b/mysql-test/r/insert.result @@ -327,9 +327,11 @@ select row_count(); row_count() 3 insert ignore into t1 values (1, 1); +Warnings: +Warning 1062 Duplicate entry '1' for key 'PRIMARY' select row_count(); row_count() -0 +-1 replace into t1 values (1, 11); select row_count(); row_count() @@ -371,7 +373,15 @@ drop table t1,t2; create table t1 (id int primary key auto_increment, data int, unique(data)); insert ignore into t1 values(NULL,100),(NULL,110),(NULL,120); insert ignore into t1 values(NULL,10),(NULL,20),(NULL,110),(NULL,120),(NULL,100),(NULL,90); +Warnings: +Warning 1062 Duplicate entry '110' for key 'data' +Warning 1062 Duplicate entry '120' for key 'data' +Warning 1062 Duplicate entry '100' for key 'data' insert ignore into t1 values(NULL,130),(NULL,140),(500,110),(550,120),(450,100),(NULL,150); +Warnings: +Warning 1062 Duplicate entry '110' for key 'data' +Warning 1062 Duplicate entry '120' for key 'data' +Warning 1062 Duplicate entry '100' for key 'data' select * from t1 order by id; id data 1 100 diff --git a/mysql-test/r/insert_select.result b/mysql-test/r/insert_select.result index c952b914167..8bfc4e9215e 100644 --- a/mysql-test/r/insert_select.result +++ b/mysql-test/r/insert_select.result @@ -6,6 +6,10 @@ insert into t2 (payoutID) SELECT DISTINCT payoutID FROM t1; insert into t2 (payoutID) SELECT payoutID+10 FROM t1; ERROR 23000: Duplicate entry '16' for key 'PRIMARY' insert ignore into t2 (payoutID) SELECT payoutID+10 FROM t1; +Warnings: +Warning 1062 Duplicate entry '16' for key 'PRIMARY' +Warning 1062 Duplicate entry '16' for key 'PRIMARY' +Warning 1062 Duplicate entry '22' for key 'PRIMARY' select * from t2; payoutID 1 diff --git a/mysql-test/r/insert_update.result b/mysql-test/r/insert_update.result index c3acea5d11f..1987c5c0559 100644 --- a/mysql-test/r/insert_update.result +++ b/mysql-test/r/insert_update.result @@ -172,11 +172,15 @@ DROP TABLE t2; create table t1 (a int not null unique) engine=myisam; insert into t1 values (1),(2); insert ignore into t1 select 1 on duplicate key update a=2; +Warnings: +Warning 1062 Duplicate entry '2' for key 'a' select * from t1; a 1 2 insert ignore into t1 select a from t1 as t2 on duplicate key update a=t1.a+1 ; +Warnings: +Warning 1062 Duplicate entry '2' for key 'a' select * from t1; a 1 diff --git a/mysql-test/r/mix2_myisam.result b/mysql-test/r/mix2_myisam.result index e5f5857d2c9..6f90555325b 100644 --- a/mysql-test/r/mix2_myisam.result +++ b/mysql-test/r/mix2_myisam.result @@ -702,6 +702,8 @@ id 2 99 insert ignore into t1 values (100,1,2,'D'),(1,1,99,'D'); +Warnings: +Warning 1062 Duplicate entry '1-1' for key 'PRIMARY' commit; select id,id3 from t1; id id3 diff --git a/mysql-test/r/myisam_mrr.result b/mysql-test/r/myisam_mrr.result index 03d212dbc04..bd50df7c40e 100644 --- a/mysql-test/r/myisam_mrr.result +++ b/mysql-test/r/myisam_mrr.result @@ -278,6 +278,8 @@ bb-1 NULL cc-2 NULL-1 drop table t1, t2, t3, t4; create table t1 (a int, b int not null,unique key (a,b),index(b)); insert ignore into t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(null,7),(9,9),(8,8),(7,7),(null,9),(null,9),(6,6); +Warnings: +Warning 1062 Duplicate entry '6-6' for key 'a' create table t2 like t1; insert into t2 select * from t1; alter table t1 modify b blob not null, add c int not null, drop key a, add unique key (a,b(20),c), drop key b, add key (b(10)); diff --git a/mysql-test/r/null_key.result b/mysql-test/r/null_key.result index 35c6ebe1442..ba79011f53e 100644 --- a/mysql-test/r/null_key.result +++ b/mysql-test/r/null_key.result @@ -1,6 +1,8 @@ drop table if exists t1,t2; create table t1 (a int, b int not null,unique key (a,b),index(b)) engine=myisam; insert ignore into t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(null,7),(9,9),(8,8),(7,7),(null,9),(null,9),(6,6); +Warnings: +Warning 1062 Duplicate entry '6-6' for key 'a' explain select * from t1 where a is null; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref a a 5 const 3 Using where; Using index diff --git a/mysql-test/r/replace.result b/mysql-test/r/replace.result index 842302c89ac..59b8565f671 100644 --- a/mysql-test/r/replace.result +++ b/mysql-test/r/replace.result @@ -13,9 +13,9 @@ drop table t1; create table t1 (a tinyint not null auto_increment primary key, b char(20) default "default_value"); insert into t1 values (126,"first"),(63, "middle"),(0,"last"); insert into t1 values (0,"error"); -ERROR 23000: Duplicate entry '127' for key 'PRIMARY' +ERROR 22003: Out of range value for column 'a' at row 1 replace into t1 values (0,"error"); -ERROR 23000: Duplicate entry '127' for key 'PRIMARY' +ERROR 22003: Out of range value for column 'a' at row 1 replace into t1 values (126,"first updated"); replace into t1 values (63,default); select * from t1; diff --git a/mysql-test/r/strict_autoinc_1myisam.result b/mysql-test/r/strict_autoinc_1myisam.result index afcccb1c40f..b22540f295b 100644 --- a/mysql-test/r/strict_autoinc_1myisam.result +++ b/mysql-test/r/strict_autoinc_1myisam.result @@ -20,9 +20,7 @@ count(*) 0 set @@sql_mode=@org_mode; insert into t1 values(null); -Warnings: -Warning 1264 Out of range value for column 'a' at row 1 +ERROR 22003: Out of range value for column 'a' at row 1 select * from t1; a -127 drop table t1; diff --git a/mysql-test/r/strict_autoinc_2innodb.result b/mysql-test/r/strict_autoinc_2innodb.result index e534286e2a2..1cf720da12a 100644 --- a/mysql-test/r/strict_autoinc_2innodb.result +++ b/mysql-test/r/strict_autoinc_2innodb.result @@ -20,9 +20,7 @@ count(*) 0 set @@sql_mode=@org_mode; insert into t1 values(null); -Warnings: -Warning 1264 Out of range value for column 'a' at row 1 +ERROR 22003: Out of range value for column 'a' at row 1 select * from t1; a -127 drop table t1; diff --git a/mysql-test/r/strict_autoinc_3heap.result b/mysql-test/r/strict_autoinc_3heap.result index 0a31da04460..21eea537af5 100644 --- a/mysql-test/r/strict_autoinc_3heap.result +++ b/mysql-test/r/strict_autoinc_3heap.result @@ -20,9 +20,7 @@ count(*) 0 set @@sql_mode=@org_mode; insert into t1 values(null); -Warnings: -Warning 1264 Out of range value for column 'a' at row 1 +ERROR 22003: Out of range value for column 'a' at row 1 select * from t1; a -127 drop table t1; diff --git a/mysql-test/r/trigger.result b/mysql-test/r/trigger.result index ef998abafcf..6047d00c858 100644 --- a/mysql-test/r/trigger.result +++ b/mysql-test/r/trigger.result @@ -165,6 +165,8 @@ select @log; (BEFORE_INSERT: new=(id=1, data=1))(AFTER_INSERT: new=(id=1, data=1)) set @log:= ""; insert ignore t1 values (1, 2); +Warnings: +Warning 1062 Duplicate entry '1' for key 'PRIMARY' select @log; @log (BEFORE_INSERT: new=(id=1, data=2)) diff --git a/mysql-test/r/xtradb_mrr.result b/mysql-test/r/xtradb_mrr.result index 069d3c25826..15b750d2fd3 100644 --- a/mysql-test/r/xtradb_mrr.result +++ b/mysql-test/r/xtradb_mrr.result @@ -276,6 +276,8 @@ bb-1 NULL cc-2 NULL-1 drop table t1, t2, t3, t4; create table t1 (a int, b int not null,unique key (a,b),index(b)); insert ignore into t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(null,7),(9,9),(8,8),(7,7),(null,9),(null,9),(6,6); +Warnings: +Warning 1062 Duplicate entry '6-6' for key 'a' create table t2 like t1; insert into t2 select * from t1; alter table t1 modify b blob not null, add c int not null, drop key a, add unique key (a,b(20),c), drop key b, add key (b(10)); diff --git a/mysql-test/suite/binlog/r/binlog_innodb_row.result b/mysql-test/suite/binlog/r/binlog_innodb_row.result index 61f961f16da..043f363c9c7 100644 --- a/mysql-test/suite/binlog/r/binlog_innodb_row.result +++ b/mysql-test/suite/binlog/r/binlog_innodb_row.result @@ -69,6 +69,8 @@ INSERT INTO t1 VALUES (1); START TRANSACTION; INSERT INTO t2 VALUES (1); INSERT IGNORE INTO t1 VALUES (1); +Warnings: +Warning 1062 Duplicate entry '1' for key 'PRIMARY' COMMIT; INSERT INTO t1 VALUES (2); START TRANSACTION; diff --git a/mysql-test/suite/binlog/r/binlog_row_binlog.result b/mysql-test/suite/binlog/r/binlog_row_binlog.result index 95477d13b50..a3ee21b9957 100644 --- a/mysql-test/suite/binlog/r/binlog_row_binlog.result +++ b/mysql-test/suite/binlog/r/binlog_row_binlog.result @@ -578,13 +578,46 @@ DROP PROCEDURE p4; End of 5.0 tests reset master; create table t1 (id tinyint auto_increment primary key); +insert into t1 values(5); set insert_id=128; -insert into t1 values(null); +insert into t1 values(null) /* Not binlogged */; +ERROR 22003: Out of range value for column 'id' at row 1 +set insert_id=128; +insert ignore into t1 values(null) /* Insert 128 */; +Warnings: +Warning 167 Out of range value for column 'id' at row 1 +set insert_id=5; +insert into t1 values(null) /* Not binlogged */; +ERROR 23000: Duplicate entry '5' for key 'PRIMARY' +set insert_id=5; +insert ignore into t1 values(null) /* Insert 5 */; Warnings: -Warning 1264 Out of range value for column 'id' at row 1 +Warning 1062 Duplicate entry '5' for key 'PRIMARY' select * from t1; id -127 +5 +drop table t1; +create table t1 (id tinyint auto_increment primary key) engine=myisam; +set insert_id=128; +insert into t1 values(5),(null) /* Insert_id 128 */; +ERROR 22003: Out of range value for column 'id' at row 2 +set insert_id=128; +insert ignore into t1 values (4),(null) /* Insert_id 128 */; +Warnings: +Warning 167 Out of range value for column 'id' at row 2 +set insert_id=5; +insert into t1 values(3),(null) /* Insert_id 5 */; +ERROR 23000: Duplicate entry '5' for key 'PRIMARY' +set insert_id=5; +insert ignore into t1 values(2),(null) /* Insert_id 5 */; +Warnings: +Warning 1062 Duplicate entry '5' for key 'PRIMARY' +select * from t1 order by id; +id +2 +3 +4 +5 drop table t1; create table t1 (a int); create table if not exists t2 select * from t1; @@ -603,36 +636,19 @@ master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F master-bin.000001 # Query # # COMMIT master-bin.000001 # Query # # use `test`; DROP TABLE `t1` /* generated by server */ -master-bin.000001 # Query # # use `test`; create table t1 (a int) +master-bin.000001 # Query # # use `test`; create table t1 (id tinyint auto_increment primary key) engine=myisam master-bin.000001 # Query # # BEGIN -master-bin.000001 # Query # # use `test`; CREATE TABLE IF NOT EXISTS `t2` ( - `a` int(11) DEFAULT NULL -) -master-bin.000001 # Query # # COMMIT -master-bin.000001 # Query # # use `test`; CREATE TABLE IF NOT EXISTS `t3` ( - `a` int(11) DEFAULT NULL -) -master-bin.000001 # Query # # BEGIN -master-bin.000001 # Table_map # # table_id: # (mysql.user) +master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F master-bin.000001 # Query # # COMMIT master-bin.000001 # Query # # BEGIN -master-bin.000001 # Table_map # # table_id: # (mysql.user) -master-bin.000001 # Update_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F master-bin.000001 # Query # # COMMIT master-bin.000001 # Query # # BEGIN -master-bin.000001 # Table_map # # table_id: # (mysql.user) -master-bin.000001 # Delete_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F master-bin.000001 # Query # # COMMIT -drop table t1,t2,t3,tt1; -create table t1 (a int not null auto_increment, primary key (a)) engine=myisam; -insert /* before delayed */ delayed /* after delayed */ into t1 values (207); -insert /*! delayed */ into t1 values (null); -insert delayed into t1 values (300); -FLUSH TABLES; -show binlog events from <binlog_start>; -Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 # Query # # use `test`; create table t1 (id tinyint auto_increment primary key) master-bin.000001 # Query # # BEGIN master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F @@ -659,8 +675,15 @@ master-bin.000001 # Query # # BEGIN master-bin.000001 # Table_map # # table_id: # (mysql.user) master-bin.000001 # Delete_rows # # table_id: # flags: STMT_END_F master-bin.000001 # Query # # COMMIT -master-bin.000001 # Query # # use `test`; DROP TEMPORARY TABLE IF EXISTS `tt1` /* generated by server */ -master-bin.000001 # Query # # use `test`; DROP TABLE `t1`,`t2`,`t3` /* generated by server */ +drop table t1,t2,t3,tt1; +reset master; +create table t1 (a int not null auto_increment, primary key (a)) engine=myisam; +insert /* before delayed */ delayed /* after delayed */ into t1 values (207); +insert /*! delayed */ into t1 values (null); +insert delayed into t1 values (300); +FLUSH TABLES; +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # use `test`; create table t1 (a int not null auto_increment, primary key (a)) engine=myisam master-bin.000001 # Query # # BEGIN master-bin.000001 # Table_map # # table_id: # (test.t1) diff --git a/mysql-test/suite/binlog/r/binlog_statement_insert_delayed.result b/mysql-test/suite/binlog/r/binlog_statement_insert_delayed.result index cdcc96b94b0..15610296d5c 100644 --- a/mysql-test/suite/binlog/r/binlog_statement_insert_delayed.result +++ b/mysql-test/suite/binlog/r/binlog_statement_insert_delayed.result @@ -1,3 +1,4 @@ +reset master; create table t1 (a int not null auto_increment, primary key (a)) engine=myisam; insert /* before delayed */ delayed /* after delayed */ into t1 values (207); insert /*! delayed */ into t1 values (null); @@ -5,9 +6,6 @@ insert delayed into t1 values (300); FLUSH TABLES; show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 # Query # # BEGIN -master-bin.000001 # Query # # use `mtr`; INSERT INTO test_suppressions (pattern) VALUES ( NAME_CONST('pattern',_latin1'Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT' COLLATE 'latin1_swedish_ci')) -master-bin.000001 # Query # # COMMIT master-bin.000001 # Query # # use `test`; create table t1 (a int not null auto_increment, primary key (a)) engine=myisam master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; insert /* before delayed */ /* after delayed */ into t1 values (207) diff --git a/mysql-test/suite/binlog/r/binlog_stm_binlog.result b/mysql-test/suite/binlog/r/binlog_stm_binlog.result index 062f4f4e906..68e76921ff3 100644 --- a/mysql-test/suite/binlog/r/binlog_stm_binlog.result +++ b/mysql-test/suite/binlog/r/binlog_stm_binlog.result @@ -387,13 +387,46 @@ DROP PROCEDURE p4; End of 5.0 tests reset master; create table t1 (id tinyint auto_increment primary key); +insert into t1 values(5); set insert_id=128; -insert into t1 values(null); +insert into t1 values(null) /* Not binlogged */; +ERROR 22003: Out of range value for column 'id' at row 1 +set insert_id=128; +insert ignore into t1 values(null) /* Insert 128 */; +Warnings: +Warning 167 Out of range value for column 'id' at row 1 +set insert_id=5; +insert into t1 values(null) /* Not binlogged */; +ERROR 23000: Duplicate entry '5' for key 'PRIMARY' +set insert_id=5; +insert ignore into t1 values(null) /* Insert 5 */; Warnings: -Warning 1264 Out of range value for column 'id' at row 1 +Warning 1062 Duplicate entry '5' for key 'PRIMARY' select * from t1; id -127 +5 +drop table t1; +create table t1 (id tinyint auto_increment primary key) engine=myisam; +set insert_id=128; +insert into t1 values(5),(null) /* Insert_id 128 */; +ERROR 22003: Out of range value for column 'id' at row 2 +set insert_id=128; +insert ignore into t1 values (4),(null) /* Insert_id 128 */; +Warnings: +Warning 167 Out of range value for column 'id' at row 2 +set insert_id=5; +insert into t1 values(3),(null) /* Insert_id 5 */; +ERROR 23000: Duplicate entry '5' for key 'PRIMARY' +set insert_id=5; +insert ignore into t1 values(2),(null) /* Insert_id 5 */; +Warnings: +Warning 1062 Duplicate entry '5' for key 'PRIMARY' +select * from t1 order by id; +id +2 +3 +4 +5 drop table t1; create table t1 (a int); create table if not exists t2 select * from t1; @@ -408,35 +441,33 @@ show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # use `test`; create table t1 (id tinyint auto_increment primary key) master-bin.000001 # Query # # BEGIN -master-bin.000001 # Intvar # # INSERT_ID=127 -master-bin.000001 # Query # # use `test`; insert into t1 values(null) +master-bin.000001 # Query # # use `test`; insert into t1 values(5) +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Intvar # # INSERT_ID=128 +master-bin.000001 # Query # # use `test`; insert ignore into t1 values(null) /* Insert 128 */ +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Intvar # # INSERT_ID=5 +master-bin.000001 # Query # # use `test`; insert ignore into t1 values(null) /* Insert 5 */ master-bin.000001 # Query # # COMMIT master-bin.000001 # Query # # use `test`; DROP TABLE `t1` /* generated by server */ -master-bin.000001 # Query # # use `test`; create table t1 (a int) -master-bin.000001 # Query # # use `test`; create table if not exists t2 select * from t1 -master-bin.000001 # Query # # use `test`; create temporary table tt1 (a int) -master-bin.000001 # Query # # use `test`; create table if not exists t3 like tt1 +master-bin.000001 # Query # # use `test`; create table t1 (id tinyint auto_increment primary key) engine=myisam master-bin.000001 # Query # # BEGIN -master-bin.000001 # Query # # use `mysql`; INSERT INTO user SET host='localhost', user='@#@', password=password('Just a test') +master-bin.000001 # Intvar # # INSERT_ID=128 +master-bin.000001 # Query # # use `test`; insert into t1 values(5),(null) /* Insert_id 128 */ master-bin.000001 # Query # # COMMIT master-bin.000001 # Query # # BEGIN -master-bin.000001 # Query # # use `mysql`; UPDATE user SET password=password('Another password') WHERE host='localhost' AND user='@#@' +master-bin.000001 # Intvar # # INSERT_ID=128 +master-bin.000001 # Query # # use `test`; insert ignore into t1 values (4),(null) /* Insert_id 128 */ master-bin.000001 # Query # # COMMIT master-bin.000001 # Query # # BEGIN -master-bin.000001 # Query # # use `mysql`; DELETE FROM user WHERE host='localhost' AND user='@#@' +master-bin.000001 # Intvar # # INSERT_ID=5 +master-bin.000001 # Query # # use `test`; insert into t1 values(3),(null) /* Insert_id 5 */ master-bin.000001 # Query # # COMMIT -drop table t1,t2,t3,tt1; -create table t1 (a int not null auto_increment, primary key (a)) engine=myisam; -insert /* before delayed */ delayed /* after delayed */ into t1 values (207); -insert /*! delayed */ into t1 values (null); -insert delayed into t1 values (300); -FLUSH TABLES; -show binlog events from <binlog_start>; -Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 # Query # # use `test`; create table t1 (id tinyint auto_increment primary key) master-bin.000001 # Query # # BEGIN -master-bin.000001 # Intvar # # INSERT_ID=127 -master-bin.000001 # Query # # use `test`; insert into t1 values(null) +master-bin.000001 # Intvar # # INSERT_ID=5 +master-bin.000001 # Query # # use `test`; insert ignore into t1 values(2),(null) /* Insert_id 5 */ master-bin.000001 # Query # # COMMIT master-bin.000001 # Query # # use `test`; DROP TABLE `t1` /* generated by server */ master-bin.000001 # Query # # use `test`; create table t1 (a int) @@ -452,8 +483,15 @@ master-bin.000001 # Query # # COMMIT master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `mysql`; DELETE FROM user WHERE host='localhost' AND user='@#@' master-bin.000001 # Query # # COMMIT -master-bin.000001 # Query # # use `test`; DROP TEMPORARY TABLE `tt1` /* generated by server */ -master-bin.000001 # Query # # use `test`; DROP TABLE `t1`,`t2`,`t3` /* generated by server */ +drop table t1,t2,t3,tt1; +reset master; +create table t1 (a int not null auto_increment, primary key (a)) engine=myisam; +insert /* before delayed */ delayed /* after delayed */ into t1 values (207); +insert /*! delayed */ into t1 values (null); +insert delayed into t1 values (300); +FLUSH TABLES; +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # use `test`; create table t1 (a int not null auto_increment, primary key (a)) engine=myisam master-bin.000001 # Query # # BEGIN master-bin.000001 # Table_map # # table_id: # (test.t1) diff --git a/mysql-test/suite/binlog/r/binlog_unsafe.result b/mysql-test/suite/binlog/r/binlog_unsafe.result index 2cc1ebdd2d7..043b3d1e7a6 100644 --- a/mysql-test/suite/binlog/r/binlog_unsafe.result +++ b/mysql-test/suite/binlog/r/binlog_unsafe.result @@ -2682,6 +2682,7 @@ CREATE TABLE insert_2_keys (a INT UNIQUE KEY, b INT UNIQUE KEY); INSERT INTO insert_2_keys values (1, 1); INSERT IGNORE INTO insert_table SELECT * FROM filler_table; Warnings: +Warning 1062 Duplicate entry '1' for key 'PRIMARY' Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT IGNORE... SELECT is unsafe because the order in which rows are retrieved by the SELECT determines which (if any) rows are ignored. This order cannot be predicted and may differ on master and the slave. TRUNCATE TABLE insert_table; INSERT INTO insert_table SELECT * FROM filler_table ON DUPLICATE KEY UPDATE a = 1; diff --git a/mysql-test/suite/innodb/r/innodb-autoinc.result b/mysql-test/suite/innodb/r/innodb-autoinc.result index 9eb89bead74..8c4c1c20590 100644 --- a/mysql-test/suite/innodb/r/innodb-autoinc.result +++ b/mysql-test/suite/innodb/r/innodb-autoinc.result @@ -2,7 +2,7 @@ drop table if exists t1; CREATE TABLE t1 (c1 BIGINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (9223372036854775807, null); INSERT INTO t1 (c2) VALUES ('innodb'); -Got one of the listed errors +ERROR 22003: Out of range value for column 'c1' at row 1 SELECT * FROM t1; c1 c2 9223372036854775807 NULL @@ -10,7 +10,7 @@ DROP TABLE t1; CREATE TABLE t1 (c1 TINYINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (127, null); INSERT INTO t1 (c2) VALUES ('innodb'); -Got one of the listed errors +ERROR 22003: Out of range value for column 'c1' at row 1 SELECT * FROM t1; c1 c2 127 NULL @@ -18,7 +18,7 @@ DROP TABLE t1; CREATE TABLE t1 (c1 TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (255, null); INSERT INTO t1 (c2) VALUES ('innodb'); -Got one of the listed errors +ERROR 22003: Out of range value for column 'c1' at row 1 SELECT * FROM t1; c1 c2 255 NULL @@ -26,7 +26,7 @@ DROP TABLE t1; CREATE TABLE t1 (c1 SMALLINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (32767, null); INSERT INTO t1 (c2) VALUES ('innodb'); -Got one of the listed errors +ERROR 22003: Out of range value for column 'c1' at row 1 SELECT * FROM t1; c1 c2 32767 NULL @@ -34,7 +34,7 @@ DROP TABLE t1; CREATE TABLE t1 (c1 SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (65535, null); INSERT INTO t1 (c2) VALUES ('innodb'); -Got one of the listed errors +ERROR 22003: Out of range value for column 'c1' at row 1 SELECT * FROM t1; c1 c2 65535 NULL @@ -42,7 +42,7 @@ DROP TABLE t1; CREATE TABLE t1 (c1 MEDIUMINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (8388607, null); INSERT INTO t1 (c2) VALUES ('innodb'); -Got one of the listed errors +ERROR 22003: Out of range value for column 'c1' at row 1 SELECT * FROM t1; c1 c2 8388607 NULL @@ -50,7 +50,7 @@ DROP TABLE t1; CREATE TABLE t1 (c1 MEDIUMINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (16777215, null); INSERT INTO t1 (c2) VALUES ('innodb'); -Got one of the listed errors +ERROR 22003: Out of range value for column 'c1' at row 1 SELECT * FROM t1; c1 c2 16777215 NULL @@ -58,7 +58,7 @@ DROP TABLE t1; CREATE TABLE t1 (c1 INT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (2147483647, null); INSERT INTO t1 (c2) VALUES ('innodb'); -Got one of the listed errors +ERROR 22003: Out of range value for column 'c1' at row 1 SELECT * FROM t1; c1 c2 2147483647 NULL @@ -66,7 +66,7 @@ DROP TABLE t1; CREATE TABLE t1 (c1 INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (4294967295, null); INSERT INTO t1 (c2) VALUES ('innodb'); -Got one of the listed errors +ERROR 22003: Out of range value for column 'c1' at row 1 SELECT * FROM t1; c1 c2 4294967295 NULL @@ -74,7 +74,7 @@ DROP TABLE t1; CREATE TABLE t1 (c1 BIGINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (9223372036854775807, null); INSERT INTO t1 (c2) VALUES ('innodb'); -Got one of the listed errors +ERROR 22003: Out of range value for column 'c1' at row 1 SELECT * FROM t1; c1 c2 9223372036854775807 NULL @@ -567,7 +567,7 @@ Variable_name Value auto_increment_increment 65535 auto_increment_offset 65535 INSERT INTO t1 VALUES (NULL),(NULL); -ERROR 22003: Out of range value for column 't1' at row 167 +ERROR 22003: Out of range value for column 'c1' at row 1 SELECT * FROM t1; c1 1 @@ -858,7 +858,7 @@ PRIMARY KEY) ENGINE=InnoDB; INSERT INTO t2 SELECT c1 FROM t1; Got one of the listed errors INSERT INTO t2 SELECT NULL FROM t1; -Got one of the listed errors +ERROR 22003: Out of range value for column 'c1' at row 1 DROP TABLE t1; DROP TABLE t2; SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; diff --git a/mysql-test/suite/innodb/r/innodb-lock.result b/mysql-test/suite/innodb/r/innodb-lock.result index 439a8d6513c..7191bcd238a 100644 --- a/mysql-test/suite/innodb/r/innodb-lock.result +++ b/mysql-test/suite/innodb/r/innodb-lock.result @@ -98,8 +98,12 @@ CREATE TABLE t1 (a INT PRIMARY KEY, b INT NOT NULL) ENGINE=InnoDB; INSERT INTO t1 VALUES(3,1); BEGIN; INSERT IGNORE INTO t1 VALUES(3,14); +Warnings: +Warning 1062 Duplicate entry '3' for key 'PRIMARY' BEGIN; INSERT IGNORE INTO t1 VALUES(3,23); +Warnings: +Warning 1062 Duplicate entry '3' for key 'PRIMARY' SELECT * FROM t1 FOR UPDATE; COMMIT; a b diff --git a/mysql-test/suite/innodb/r/innodb.result b/mysql-test/suite/innodb/r/innodb.result index bb4d783ccf4..f3f1b1ed045 100644 --- a/mysql-test/suite/innodb/r/innodb.result +++ b/mysql-test/suite/innodb/r/innodb.result @@ -816,6 +816,8 @@ id 1 2 insert ignore into t1 values (100,1,2,'D'),(1,1,99,'D'); +Warnings: +Warning 1062 Duplicate entry '1-1' for key 'PRIMARY' commit; select id,id3 from t1; id id3 diff --git a/mysql-test/suite/innodb/r/innodb_bug56947.result b/mysql-test/suite/innodb/r/innodb_bug56947.result index b279069d834..8b864b62e81 100644 --- a/mysql-test/suite/innodb/r/innodb_bug56947.result +++ b/mysql-test/suite/innodb/r/innodb_bug56947.result @@ -3,6 +3,6 @@ SET GLOBAL innodb_file_per_table=0; create table bug56947(a int not null) engine = innodb; CREATE TABLE `bug56947#1`(a int) ENGINE=InnoDB; alter table bug56947 add unique index (a); -ERROR HY000: Table 'test.bug56947#1' already exists +ERROR 42S01: Table 'test.bug56947#1' already exists drop table `bug56947#1`; drop table bug56947; diff --git a/mysql-test/suite/innodb/r/innodb_mysql.result b/mysql-test/suite/innodb/r/innodb_mysql.result index 2b3d24551c7..be2b4b28f6a 100644 --- a/mysql-test/suite/innodb/r/innodb_mysql.result +++ b/mysql-test/suite/innodb/r/innodb_mysql.result @@ -1480,10 +1480,15 @@ k a c 1 6 2 2 7 NULL insert ignore into t2 values (null,6,1),(10,8,1); +Warnings: +Warning 1062 Duplicate entry '6' for key 'idx_1' select last_insert_id(); last_insert_id() 0 insert ignore into t2 values (null,6,1),(null,8,1),(null,15,1),(null,20,1); +Warnings: +Warning 1062 Duplicate entry '6' for key 'idx_1' +Warning 1062 Duplicate entry '8' for key 'idx_1' select last_insert_id(); last_insert_id() 11 diff --git a/mysql-test/suite/innodb/t/innodb-autoinc.test b/mysql-test/suite/innodb/t/innodb-autoinc.test index 4f54a8ff957..888c73bacdf 100644 --- a/mysql-test/suite/innodb/t/innodb-autoinc.test +++ b/mysql-test/suite/innodb/t/innodb-autoinc.test @@ -11,7 +11,7 @@ drop table if exists t1; # CREATE TABLE t1 (c1 BIGINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (9223372036854775807, null); --- error ER_DUP_ENTRY,1062 +-- error 167 INSERT INTO t1 (c2) VALUES ('innodb'); SELECT * FROM t1; DROP TABLE t1; @@ -22,14 +22,14 @@ DROP TABLE t1; # TINYINT CREATE TABLE t1 (c1 TINYINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (127, null); --- error ER_DUP_ENTRY,1062 +-- error 167 INSERT INTO t1 (c2) VALUES ('innodb'); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (c1 TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (255, null); --- error ER_DUP_ENTRY,1062 +-- error 167 INSERT INTO t1 (c2) VALUES ('innodb'); SELECT * FROM t1; DROP TABLE t1; @@ -38,14 +38,14 @@ DROP TABLE t1; # CREATE TABLE t1 (c1 SMALLINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (32767, null); --- error ER_DUP_ENTRY,1062 +-- error 167 INSERT INTO t1 (c2) VALUES ('innodb'); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (c1 SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (65535, null); --- error ER_DUP_ENTRY,1062 +-- error 167 INSERT INTO t1 (c2) VALUES ('innodb'); SELECT * FROM t1; DROP TABLE t1; @@ -54,14 +54,14 @@ DROP TABLE t1; # CREATE TABLE t1 (c1 MEDIUMINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (8388607, null); --- error ER_DUP_ENTRY,1062 +-- error 167 INSERT INTO t1 (c2) VALUES ('innodb'); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (c1 MEDIUMINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (16777215, null); --- error ER_DUP_ENTRY,1062 +-- error 167 INSERT INTO t1 (c2) VALUES ('innodb'); SELECT * FROM t1; DROP TABLE t1; @@ -70,14 +70,14 @@ DROP TABLE t1; # CREATE TABLE t1 (c1 INT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (2147483647, null); --- error ER_DUP_ENTRY,1062 +-- error 167 INSERT INTO t1 (c2) VALUES ('innodb'); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (c1 INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (4294967295, null); --- error ER_DUP_ENTRY,1062 +-- error 167 INSERT INTO t1 (c2) VALUES ('innodb'); SELECT * FROM t1; DROP TABLE t1; @@ -86,7 +86,7 @@ DROP TABLE t1; # CREATE TABLE t1 (c1 BIGINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (9223372036854775807, null); --- error ER_DUP_ENTRY,1062 +-- error 167 INSERT INTO t1 (c2) VALUES ('innodb'); SELECT * FROM t1; DROP TABLE t1; @@ -349,7 +349,7 @@ INSERT INTO t1 VALUES (18446744073709551610); #-- 2^64 - 2 SELECT * FROM t1; SET @@SESSION.AUTO_INCREMENT_INCREMENT=1152921504606846976, @@SESSION.AUTO_INCREMENT_OFFSET=1152921504606846976; SHOW VARIABLES LIKE "%auto_inc%"; ---error ER_WARN_DATA_OUT_OF_RANGE +--error 167 INSERT INTO t1 VALUES (NULL),(NULL); SELECT * FROM t1; DROP TABLE t1; @@ -437,7 +437,7 @@ CREATE TABLE t2( PRIMARY KEY) ENGINE=InnoDB; -- error ER_DUP_ENTRY,1062 INSERT INTO t2 SELECT c1 FROM t1; --- error ER_DUP_ENTRY,1467 +-- error 167 INSERT INTO t2 SELECT NULL FROM t1; DROP TABLE t1; DROP TABLE t2; diff --git a/mysql-test/suite/maria/maria3.result b/mysql-test/suite/maria/maria3.result index 37613875f38..27d72b75930 100644 --- a/mysql-test/suite/maria/maria3.result +++ b/mysql-test/suite/maria/maria3.result @@ -452,6 +452,8 @@ SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO'; CREATE TABLE t1 (id int(11) PRIMARY KEY auto_increment,f1 varchar(10) NOT NULL UNIQUE); INSERT IGNORE INTO t1 (f1) VALUES ("test1"); INSERT IGNORE INTO t1 (f1) VALUES ("test1"); +Warnings: +Warning 1062 Duplicate entry 'test1' for key 'f1' INSERT IGNORE INTO t1 (f1) VALUES ("test2"); SELECT * FROM t1; id f1 diff --git a/mysql-test/suite/maria/mrr.result b/mysql-test/suite/maria/mrr.result index af7789eebff..06be64566e5 100644 --- a/mysql-test/suite/maria/mrr.result +++ b/mysql-test/suite/maria/mrr.result @@ -277,6 +277,8 @@ bb-1 NULL cc-2 NULL-1 drop table t1, t2, t3, t4; create table t1 (a int, b int not null,unique key (a,b),index(b)); insert ignore into t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(null,7),(9,9),(8,8),(7,7),(null,9),(null,9),(6,6); +Warnings: +Warning 1062 Duplicate entry '6-6' for key 'a' create table t2 like t1; insert into t2 select * from t1; alter table t1 modify b blob not null, add c int not null, drop key a, add unique key (a,b(20),c), drop key b, add key (b(10)); diff --git a/mysql-test/suite/optimizer_unfixed_bugs/r/bug43617.result b/mysql-test/suite/optimizer_unfixed_bugs/r/bug43617.result index 3c19a37c95d..505dd587d0b 100644 --- a/mysql-test/suite/optimizer_unfixed_bugs/r/bug43617.result +++ b/mysql-test/suite/optimizer_unfixed_bugs/r/bug43617.result @@ -38,7 +38,11 @@ c1 c2 c3 c4 2008-01-01 00:00:00 NULL 2008-01-02 2008-01-03 00:00:00 2009-01-29 11:11:27 2009-01-29 00:00:00 2009-01-29 2009-01-29 00:00:00 INSERT IGNORE INTO t1(c1,c2) VALUES('20070525','20070527') /* doesnt throw error */; +Warnings: +Warning 1062 Duplicate entry '2007-05-25 00:00:00' for key 'PRIMARY' INSERT IGNORE INTO t1(c1,c2) VALUES(19840905,830907) /* doesnt throw error */; +Warnings: +Warning 1062 Duplicate entry '1983-09-07 00:00:00' for key 'c2' SELECT * FROM t1 WHERE c1='20070527' /* Returns no rows */; c1 c2 c3 c4 INSERT INTO t1(c1) VALUES('20070525') ON DUPLICATE KEY UPDATE c1='20070527'; diff --git a/mysql-test/suite/rpl/r/rpl_auto_increment.result b/mysql-test/suite/rpl/r/rpl_auto_increment.result index 8b41a01cb6e..41bacd78f52 100644 --- a/mysql-test/suite/rpl/r/rpl_auto_increment.result +++ b/mysql-test/suite/rpl/r/rpl_auto_increment.result @@ -125,6 +125,10 @@ insert into t1 values(600),(NULL),(NULL); ERROR 23000: Duplicate entry '600' for key 'PRIMARY' set @@insert_id=600; insert ignore into t1 values(600),(NULL),(NULL),(610),(NULL); +Warnings: +Warning 1062 Duplicate entry '600' for key 'PRIMARY' +Warning 1062 Duplicate entry '600' for key 'PRIMARY' +Warning 1062 Duplicate entry '600' for key 'PRIMARY' select * from t1; a 1 @@ -186,7 +190,7 @@ set auto_increment_offset=4; insert into t1 values(null); insert into t1 values(null); insert into t1 values(null); -ERROR 23000: Duplicate entry '125' for key 'PRIMARY' +ERROR 22003: Out of range value for column 'a' at row 1 select a, mod(a-@@auto_increment_offset,@@auto_increment_increment) from t1 order by a; a mod(a-@@auto_increment_offset,@@auto_increment_increment) 103 0 @@ -196,21 +200,19 @@ create table t2 (a tinyint unsigned not null auto_increment primary key) engine= set auto_increment_increment=10; set auto_increment_offset=1; set insert_id=1000; +insert into t2 values(10); insert into t2 values(null); -Warnings: -Warning 1264 Out of range value for column 'a' at row 1 +ERROR 22003: Out of range value for column 'a' at row 1 select a, mod(a-@@auto_increment_offset,@@auto_increment_increment) from t2 order by a; a mod(a-@@auto_increment_offset,@@auto_increment_increment) -251 0 +10 9 create table t3 like t1; set auto_increment_increment=1000; set auto_increment_offset=700; insert into t3 values(null); -Warnings: -Warning 1264 Out of range value for column 'a' at row 1 +ERROR 22003: Out of range value for column 'a' at row 1 select * from t3 order by a; a -127 select * from t1 order by a; a 103 @@ -218,10 +220,9 @@ a 125 select * from t2 order by a; a -251 +10 select * from t3 order by a; a -127 drop table t1,t2,t3; set auto_increment_increment=1; set auto_increment_offset=1; diff --git a/mysql-test/suite/rpl/r/rpl_insert_delayed,stmt.rdiff b/mysql-test/suite/rpl/r/rpl_insert_delayed,stmt.rdiff index 5e0e7db5b63..ddb10d604c6 100644 --- a/mysql-test/suite/rpl/r/rpl_insert_delayed,stmt.rdiff +++ b/mysql-test/suite/rpl/r/rpl_insert_delayed,stmt.rdiff @@ -1,5 +1,5 @@ ---- suite/rpl/r/rpl_insert_delayed.result 2012-02-06 21:37:21.000000000 +0100 -+++ suite/rpl/r/rpl_insert_delayed,stmt.reject 2012-02-06 23:12:55.000000000 +0100 +--- suite/rpl/r/rpl_insert_delayed.result 2012-09-18 01:37:45.317521958 +0300 ++++ suite/rpl/r/rpl_insert_delayed,stmt.reject 2012-09-18 01:36:16.637514667 +0300 @@ -15,17 +15,17 @@ insert delayed into t1 values(10, "my name"); flush table t1; diff --git a/mysql-test/suite/rpl/r/rpl_loaddatalocal.result b/mysql-test/suite/rpl/r/rpl_loaddatalocal.result index 902bc1cda00..ae91e000c37 100644 --- a/mysql-test/suite/rpl/r/rpl_loaddatalocal.result +++ b/mysql-test/suite/rpl/r/rpl_loaddatalocal.result @@ -14,6 +14,8 @@ select * into outfile 'MYSQLD_DATADIR/rpl_loaddatalocal.select_outfile' from t1; drop table t1; create table t1(a int primary key); load data local infile 'MYSQLD_DATADIR/rpl_loaddatalocal.select_outfile' into table t1; +Warnings: +Warning 1062 Duplicate entry '2' for key 'PRIMARY' SELECT * FROM t1 ORDER BY a; a 1 diff --git a/mysql-test/t/auto_increment.test b/mysql-test/t/auto_increment.test index 884e0fabb5e..15156b89d8e 100644 --- a/mysql-test/t/auto_increment.test +++ b/mysql-test/t/auto_increment.test @@ -104,7 +104,7 @@ explain extended select last_insert_id(); --error ER_DUP_ENTRY insert into t1 set i = 254; select last_insert_id(); ---error ER_DUP_ENTRY +--error 167 insert into t1 set i = null; select last_insert_id(); drop table t1; @@ -113,6 +113,7 @@ create table t1 (i tinyint unsigned not null auto_increment, key (i)); insert into t1 set i = 254; insert into t1 set i = null; select last_insert_id(); +--error 167 insert into t1 set i = null; select last_insert_id(); drop table t1; @@ -353,7 +354,7 @@ INSERT INTO t1 VALUES (18446744073709551601); SET @@SESSION.AUTO_INCREMENT_INCREMENT=10; SELECT @@SESSION.AUTO_INCREMENT_OFFSET; ---error ER_WARN_DATA_OUT_OF_RANGE +--error 167 INSERT INTO t1 VALUES (NULL), (NULL), (NULL); SELECT * FROM t1; diff --git a/mysql-test/t/auto_increment_ranges.inc b/mysql-test/t/auto_increment_ranges.inc new file mode 100644 index 00000000000..a94aa46d38e --- /dev/null +++ b/mysql-test/t/auto_increment_ranges.inc @@ -0,0 +1,240 @@ +# +# Test of auto_increment at end of range +# +--disable_warnings +drop table if exists t1; +--enable_warnings + +--echo # +--echo # Testing ranges with smallint +--echo # +let $type=smallint; +let $range_max=32767; + +eval create table t1 (a $type primary key auto_increment); +eval insert into t1 values($range_max); +--error 167 +insert into t1 values(NULL); +truncate table t1; +eval insert into t1 values($range_max-1); +insert into t1 values(NULL); +--error 167 +insert into t1 values(NULL); +select * from t1; +truncate table t1; +--error 167 +eval insert into t1 values($range_max),(NULL); +select * from t1; +truncate table t1; +--error 167 +eval insert into t1 values($range_max-1),(NULL),(NULL); +truncate table t1; +eval insert into t1 values($range_max+1); +select * from t1; +--error 167 +eval insert into t1 values(NULL); +drop table t1; + +--echo # +--echo # Testing ranges with unsigned smallint +--echo # + +let $type=smallint unsigned; +let $range_max=65535; + +eval create table t1 (a $type primary key auto_increment); +eval insert into t1 values($range_max); +--error 167 +insert into t1 values(NULL); +truncate table t1; +eval insert into t1 values($range_max-1); +insert into t1 values(NULL); +--error 167 +insert into t1 values(NULL); +select * from t1; +truncate table t1; +--error 167 +eval insert into t1 values($range_max),(NULL); +select * from t1; +truncate table t1; +--error 167 +eval insert into t1 values($range_max-1),(NULL),(NULL); +truncate table t1; +eval insert into t1 values($range_max+1); +select * from t1; +--error 167 +eval insert into t1 values(NULL); +drop table t1; + +--echo # +--echo # Testing ranges with integer +--echo # + +let $type=int; +let $range_max=2147483647; + +eval create table t1 (a $type primary key auto_increment); +eval insert into t1 values($range_max); +--error 167 +insert into t1 values(NULL); +truncate table t1; +eval insert into t1 values($range_max-1); +insert into t1 values(NULL); +--error 167 +insert into t1 values(NULL); +select * from t1; +truncate table t1; +--error 167 +eval insert into t1 values($range_max),(NULL); +select * from t1; +truncate table t1; +--error 167 +eval insert into t1 values($range_max-1),(NULL),(NULL); +truncate table t1; +eval insert into t1 values($range_max+1); +select * from t1; +--error 167 +eval insert into t1 values(NULL); +drop table t1; + +--echo # +--echo # Testing ranges with unsigned integer +--echo # + +let $type=int unsigned; +let $range_max=4294967295; + +eval create table t1 (a $type primary key auto_increment); +eval insert into t1 values($range_max); +--error 167 +insert into t1 values(NULL); +truncate table t1; +eval insert into t1 values($range_max-1); +insert into t1 values(NULL); +--error 167 +insert into t1 values(NULL); +select * from t1; +truncate table t1; +--error 167 +eval insert into t1 values($range_max),(NULL); +select * from t1; +truncate table t1; +--error 167 +eval insert into t1 values($range_max-1),(NULL),(NULL); +truncate table t1; +eval insert into t1 values($range_max+1); +select * from t1; +--error 167 +eval insert into t1 values(NULL); +drop table t1; + +--echo # +--echo # Testing ranges with bigint +--echo # + +let $type=bigint; +let $range_max=cast(9223372036854775807 as unsigned); + +eval create table t1 (a $type primary key auto_increment); +eval insert into t1 values($range_max); +--error 167 +insert into t1 values(NULL); +truncate table t1; +eval insert into t1 values($range_max-1); +insert into t1 values(NULL); +--error 167 +insert into t1 values(NULL); +select * from t1; +truncate table t1; +--error 167 +eval insert into t1 values($range_max),(NULL); +select * from t1; +truncate table t1; +--error 167 +eval insert into t1 values($range_max-1),(NULL),(NULL); +truncate table t1; +eval insert into t1 values($range_max+1); +select * from t1; +--error 167 +eval insert into t1 values(NULL); +drop table t1; + +--echo # +--echo # Testing ranges with unsigned bigint +--echo # + +let $type=bigint unsigned; +let $range_max=18446744073709551615; + +eval create table t1 (a $type primary key auto_increment); +eval insert into t1 values($range_max-1); +--error ER_AUTOINC_READ_FAILED +insert into t1 values(NULL); +--error ER_AUTOINC_READ_FAILED +insert into t1 values(NULL); +truncate table t1; +eval insert into t1 values($range_max-1); +--error ER_AUTOINC_READ_FAILED +insert into t1 values(NULL); +--error ER_AUTOINC_READ_FAILED +insert into t1 values(NULL); +select * from t1; +truncate table t1; +--error ER_AUTOINC_READ_FAILED +eval insert into t1 values($range_max),(NULL); +select * from t1; +truncate table t1; +--error ER_AUTOINC_READ_FAILED +eval insert into t1 values($range_max-1),(NULL),(NULL); +drop table t1; + +--echo # +--echo # Test IGNORE and strict mode +--echo # +create table t1 (a smallint primary key auto_increment); +insert ignore into t1 values(32766),(NULL),(NULL),(1); +select * from t1; +truncate table t1; + +set @org_mode=@@sql_mode; +set @@sql_mode='ansi,traditional'; +insert ignore into t1 values(32766),(NULL),(NULL); +truncate table t1; +--error 167 +insert into t1 values(32766),(NULL),(NULL); +set @@sql_mode=@org_mode; +drop table t1; + +--echo # +--echo # Test auto increment with negative numbers +--echo # +CREATE TABLE t1 (a INTEGER AUTO_INCREMENT, PRIMARY KEY (a)); +INSERT INTO t1 VALUES (NULL), (2), (-5), (NULL); +INSERT INTO t1 VALUES (NULL); +SELECT * FROM t1; +TRUNCATE TABLE t1; +INSERT INTO t1 VALUES (-5), (NULL); +SELECT * FROM t1; +DROP TABLE t1; + +--echo # +--echo # Test inserting a value out-of-range into an auto increment column +--echo # +CREATE TABLE t1 (a smallint AUTO_INCREMENT, PRIMARY KEY (a)); +INSERT INTO t1 VALUES (2); +INSERT INTO t1 VALUES (32768); +--error 167 +INSERT INTO t1 VALUES (NULL); +SELECT * FROM t1; +DROP TABLE t1; + + +--echo # +--echo # Test old behaviour +--echo # +create table t1 (a smallint primary key auto_increment); +insert into t1 values(32766),(NULL); +delete from t1 where a=32767; +--error 167 +insert into t1 values(NULL); +drop table t1; diff --git a/mysql-test/t/auto_increment_ranges_innodb.test b/mysql-test/t/auto_increment_ranges_innodb.test new file mode 100644 index 00000000000..c2afee7ac66 --- /dev/null +++ b/mysql-test/t/auto_increment_ranges_innodb.test @@ -0,0 +1,7 @@ +# +# Test of auto_increment at end of range +# + +--source include/have_innodb.inc +set default_storage_engine=innodb; +--source auto_increment_ranges.inc diff --git a/mysql-test/t/auto_increment_ranges_myisam.test b/mysql-test/t/auto_increment_ranges_myisam.test new file mode 100644 index 00000000000..90e84377540 --- /dev/null +++ b/mysql-test/t/auto_increment_ranges_myisam.test @@ -0,0 +1,7 @@ +# +# Test of auto_increment at end of range +# + +set default_storage_engine=MYISAM; +--source auto_increment_ranges.inc + diff --git a/mysql-test/t/replace.test b/mysql-test/t/replace.test index ff910bdf5cf..3d32a8c0da6 100644 --- a/mysql-test/t/replace.test +++ b/mysql-test/t/replace.test @@ -25,9 +25,9 @@ drop table t1; create table t1 (a tinyint not null auto_increment primary key, b char(20) default "default_value"); insert into t1 values (126,"first"),(63, "middle"),(0,"last"); ---error ER_DUP_ENTRY +--error 167 insert into t1 values (0,"error"); ---error ER_DUP_ENTRY +--error 167 replace into t1 values (0,"error"); replace into t1 values (126,"first updated"); replace into t1 values (63,default); |