Fix for MDEV-533: Confusing error code when doing auto-increment insert for out-of-range values

create table t1 (a smallint primary key auto_increment);
insert into t1 values(32767);
insert into t1 values(NULL);
ERROR 1062 (23000): Duplicate entry '32767' for key 'PRIMARY

Now on always gets error HA_ERR_AUTOINC_RANGE=167 "Out of range value for column", independent of
store engine, SQL Mode or number of inserted rows. This is an unique error that is easier to test for in replication.

Another bug fix is that we now get an error when trying to insert a too big auto-generated value, even in non-strict mode.
Before one get insted the max column value inserted.
This patch also fixes some issues with inserting negative numbers in an auto-increment column.
Fixed the ER_DUP_ENTRY and HA_ERR_AUTOINC_ERANGE are compared the same between master and slave.
This ensures that replication works between an old server to a new slave for auto-increment overflow errors.
Added SQLSTATE errors for handler errors

Smaller bug fixes:
* Added warnings for duplicate key errors when using INSERT IGNORE
* Fixed bug when using --skip-log-bin followed by --log-bin, which did set log-bin to "0"
* Allow one to see how cmake is called by using --just-print --just-configure


BUILD/FINISH.sh:
  --just-print --just-configure now shows how cmake would be invoked. Good for understanding parameters to cmake.
cmake/configure.pl:
  --just-print --just-configure now shows how cmake would be invoked. Good for understanding parameters to cmake.
include/CMakeLists.txt:
  Added handler_state.h
include/handler_state.h:
  SQLSTATE for handler error messages.
  Required for HA_ERR_AUTOINC_ERANGE, but solves also some other cases.
mysql-test/extra/binlog_tests/binlog.test:
  Fixed old wrong behaviour
  Added more tests
mysql-test/extra/binlog_tests/binlog_insert_delayed.test:
  Reset binary log to only print what's necessary in show_binlog_events
mysql-test/extra/rpl_tests/rpl_auto_increment.test:
  Update to new error codes
mysql-test/extra/rpl_tests/rpl_insert_delayed.test:
  Ignore warnings as this depends on how the test is run
mysql-test/include/strict_autoinc.inc:
  On now gets an error on overflow
mysql-test/r/auto_increment.result:
  Update results after fixing error message
mysql-test/r/auto_increment_ranges_innodb.result:
  Test new behaviour
mysql-test/r/auto_increment_ranges_myisam.result:
  Test new behaviour
mysql-test/r/commit_1innodb.result:
  Added warnings for duplicate key error
mysql-test/r/create.result:
  Added warnings for duplicate key error
mysql-test/r/insert.result:
  Added warnings for duplicate key error
mysql-test/r/insert_select.result:
  Added warnings for duplicate key error
mysql-test/r/insert_update.result:
  Added warnings for duplicate key error
mysql-test/r/mix2_myisam.result:
  Added warnings for duplicate key error
mysql-test/r/myisam_mrr.result:
  Added warnings for duplicate key error
mysql-test/r/null_key.result:
  Added warnings for duplicate key error
mysql-test/r/replace.result:
  Update to new error codes
mysql-test/r/strict_autoinc_1myisam.result:
  Update to new error codes
mysql-test/r/strict_autoinc_2innodb.result:
  Update to new error codes
mysql-test/r/strict_autoinc_3heap.result:
  Update to new error codes
mysql-test/r/trigger.result:
  Added warnings for duplicate key error
mysql-test/r/xtradb_mrr.result:
  Added warnings for duplicate key error
mysql-test/suite/binlog/r/binlog_innodb_row.result:
  Updated result
mysql-test/suite/binlog/r/binlog_row_binlog.result:
  Out of range data for auto-increment is not inserted anymore
mysql-test/suite/binlog/r/binlog_statement_insert_delayed.result:
  Updated result
mysql-test/suite/binlog/r/binlog_stm_binlog.result:
  Out of range data for auto-increment is not inserted anymore
mysql-test/suite/binlog/r/binlog_unsafe.result:
  Updated result
mysql-test/suite/innodb/r/innodb-autoinc.result:
  Update to new error codes
mysql-test/suite/innodb/r/innodb-lock.result:
  Updated results
mysql-test/suite/innodb/r/innodb.result:
  Updated results
mysql-test/suite/innodb/r/innodb_bug56947.result:
  Updated results
mysql-test/suite/innodb/r/innodb_mysql.result:
  Updated results
mysql-test/suite/innodb/t/innodb-autoinc.test:
  Update to new error codes
mysql-test/suite/maria/maria3.result:
  Updated result
mysql-test/suite/maria/mrr.result:
  Updated result
mysql-test/suite/optimizer_unfixed_bugs/r/bug43617.result:
  Updated result
mysql-test/suite/rpl/r/rpl_auto_increment.result:
  Update to new error codes
mysql-test/suite/rpl/r/rpl_insert_delayed,stmt.rdiff:
  Updated results
mysql-test/suite/rpl/r/rpl_loaddatalocal.result:
  Updated results
mysql-test/t/auto_increment.test:
  Update to new error codes
mysql-test/t/auto_increment_ranges.inc:
  Test new behaviour
mysql-test/t/auto_increment_ranges_innodb.test:
  Test new behaviour
mysql-test/t/auto_increment_ranges_myisam.test:
  Test new behaviour
mysql-test/t/replace.test:
  Update to new error codes
mysys/my_getopt.c:
  Fixed bug when using --skip-log-bin followed by --log-bin, which did set log-bin to "0"
sql/handler.cc:
  Ignore negative values for signed auto-increment columns
  Always give an error if we get an overflow for an auto-increment-column (instead of inserting the max value)
  Ensure that the row number is correct for the out-of-range-value error message.
  
  
  ******
  Fixed wrong printing of column namn for "Out of range value" errors
  Fixed that INSERT_ID is correctly replicated also for out-of-range autoincrement values
  Fixed that print_keydup_error() can also be used to generate warnings
  ******
  Return HA_ERR_AUTOINC_ERANGE (167) instead of ER_WARN_DATA_OUT_OF_RANGE for auto-increment overflow
sql/handler.h:
  Allow INSERT IGNORE to continue also after out-of-range inserts.
  Fixed that print_keydup_error() can also be used to generate warnings
sql/log_event.cc:
  Added DBUG_PRINT
  Fixed the ER_AUTOINC_READ_FAILED, ER_DUP_ENTRY and HA_ERR_AUTOINC_ERANGE are compared the same between master and slave.
  This ensures that replication works between an old server to a new slave for auto-increment overflow errors.
sql/sql_insert.cc:
  Add warnings for duplicate key errors when using INSERT IGNORE
sql/sql_state.c:
  Added handler errors
sql/sql_table.cc:
  Update call to print_keydup_error()
storage/innobase/handler/ha_innodb.cc:
  Fixed increment handling of auto-increment columns to be consistent with rest of MariaDB.
storage/xtradb/handler/ha_innodb.cc:
  Fixed increment handling of auto-increment columns to be consistent with rest of MariaDB.

17 files changed, 176 insertions, 92 deletions

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