Fix for MDEV-5168: MariaDB returns warnings for INSERT IGNORE

Added variable "OLD_MODE" that can be used to turn off the new behavior

mysql-test/r/insert.result:
  Added test case
mysql-test/r/mysqld--help.result:
  Added old_mode
mysql-test/suite/sys_vars/r/old_mode_basic.result:
  Added testing of new variable
mysql-test/suite/sys_vars/t/old_mode_basic.test:
  Added testing of new variable
mysql-test/t/insert.test:
  Added test case
sql/sql_class.h:
  Added bit flags for OLD_MODE
sql/sql_insert.cc:
  Disable duplicate key warnings for INSERT IGNORE of OLD_MODE NO_DUP_KEY_WARNINGS_WITH_IGNORE is used
sql/sql_show.cc:
  Don't show progress reporting on SHOW PROCESSLIST if OLD_MODE NO_PROGRESS_INFO is used
sql/sys_vars.cc:
  Added OLD_MODE

5 files changed, 462 insertions, 1 deletions

diff --git a/mysql-test/r/insert.result b/mysql-test/r/insert.result
index a722ab8d97f..82f3977e231 100644
--- a/mysql-test/r/insert.result
+++ b/mysql-test/r/insert.result
@@ -697,3 +697,23 @@ ERROR 42000: Column 'a' specified twice
 INSERT IGNORE t1 (a, a) SELECT 1,1 UNION SELECT 2,2;
 ERROR 42000: Column 'a' specified twice
 DROP TABLE t1;
+#
+# MDEV-5168: Ensure that we can disable duplicate key warnings
+# from INSERT IGNORE
+#
+create table t1 (f1 int unique, f2 int unique);
+insert into t1 values (1,12);
+insert into t1 values (2,13);
+insert into t1 values (1,12);
+ERROR 23000: Duplicate entry '1' for key 'f1'
+insert ignore into t1 values (1,12);
+Warnings:
+Warning	1062	Duplicate entry '1' for key 'f1'
+set @@old_mode="NO_DUP_KEY_WARNINGS_WITH_IGNORE";
+insert ignore into t1 values (1,12);
+insert ignore into t1 values (1,12) on duplicate key update f2=13;
+set @@old_mode="";
+insert ignore into t1 values (1,12);
+Warnings:
+Warning	1062	Duplicate entry '1' for key 'f1'
+DROP TABLE t1;
diff --git a/mysql-test/r/mysqld--help.result b/mysql-test/r/mysqld--help.result
index c36cc96bb1a..16da8eae7f9 100644
--- a/mysql-test/r/mysqld--help.result
+++ b/mysql-test/r/mysqld--help.result
@@ -454,8 +454,12 @@ The following options may be given as the first argument:
  --net-write-timeout=# 
  Number of seconds to wait for a block to be written to a
  connection before aborting the write
- --old               Use compatible behavior
+ --old               Use compatible behavior from previous MariaDB version.
+ See also --old-mode
  --old-alter-table   Use old, non-optimized alter table
+ --old-mode=name     Used to emulate old behavior from earlier MariaDB or
+ MySQL versions. Syntax: old_mode=mode[,mode[,mode...]].
+ See the manual for the complete list of valid old modes
  --old-passwords     Use old password encryption method (needed for 4.0 and
  older clients)
  --old-style-user-limits 
@@ -997,6 +1001,7 @@ net-retry-count 10
 net-write-timeout 60
 old FALSE
 old-alter-table FALSE
+old-mode 
 old-passwords FALSE
 old-style-user-limits FALSE
 optimizer-prune-level 1
diff --git a/mysql-test/suite/sys_vars/r/old_mode_basic.result b/mysql-test/suite/sys_vars/r/old_mode_basic.result
new file mode 100644
index 00000000000..c9a650e7b15
--- /dev/null
+++ b/mysql-test/suite/sys_vars/r/old_mode_basic.result
@@ -0,0 +1,174 @@
+SET @global_start_value = @@global.old_mode;
+SELECT @global_start_value;
+@global_start_value
+
+SET @session_start_value = @@session.old_mode;
+SELECT @session_start_value;
+@session_start_value
+
+'#--------------------FN_DYNVARS_152_01------------------------#'
+SET @@global.old_mode = "NO_PROGRESS_INFO";
+SET @@global.old_mode = DEFAULT;
+SELECT @@global.old_mode;
+@@global.old_mode
+
+SET @@session.old_mode = "NO_PROGRESS_INFO";
+SET @@session.old_mode = DEFAULT;
+SELECT @@session.old_mode;
+@@session.old_mode
+
+'#---------------------FN_DYNVARS_152_02-------------------------#'
+SET @@global.old_mode = NULL;
+ERROR 42000: Variable 'old_mode' can't be set to the value of 'NULL'
+SET @@global.old_mode = '';
+SELECT @@global.old_mode;
+@@global.old_mode
+
+SET @@global.old_mode = ' ';
+SELECT @@global.old_mode;
+@@global.old_mode
+
+SET @@session.old_mode = NULL;
+ERROR 42000: Variable 'old_mode' can't be set to the value of 'NULL'
+SET @@session.old_mode = '';
+SELECT @@session.old_mode;
+@@session.old_mode
+
+SET @@session.old_mode = ' ';
+SELECT @@session.old_mode;
+@@session.old_mode
+
+'#--------------------FN_DYNVARS_152_03------------------------#'
+SET @@global.old_mode = NO_PROGRESS_INFO;
+SELECT @@global.old_mode;
+@@global.old_mode
+NO_PROGRESS_INFO
+SET @@global.old_mode = NO_DUP_KEY_WARNINGS_WITH_IGNORE;
+SELECT @@global.old_mode;
+@@global.old_mode
+NO_DUP_KEY_WARNINGS_WITH_IGNORE
+SET @@global.old_mode = OFF;
+ERROR 42000: Variable 'old_mode' can't be set to the value of 'OFF'
+SET @@session.old_mode = NO_PROGRESS_INFO;
+SELECT @@session.old_mode;
+@@session.old_mode
+NO_PROGRESS_INFO
+SET @@session.old_mode = NO_DUP_KEY_WARNINGS_WITH_IGNORE;
+SELECT @@session.old_mode;
+@@session.old_mode
+NO_DUP_KEY_WARNINGS_WITH_IGNORE
+SET @@session.old_mode = OFF;
+ERROR 42000: Variable 'old_mode' can't be set to the value of 'OFF'
+SET @@global.old_mode = '?';
+ERROR 42000: Variable 'old_mode' can't be set to the value of '?'
+SELECT @@global.old_mode;
+@@global.old_mode
+NO_DUP_KEY_WARNINGS_WITH_IGNORE
+'#--------------------FN_DYNVARS_152_04-------------------------#'
+SET @@global.old_mode = -1;
+ERROR 42000: Variable 'old_mode' can't be set to the value of '-1'
+SET @@global.old_mode = ASCII;
+ERROR 42000: Variable 'old_mode' can't be set to the value of 'ASCII'
+SET @@global.old_mode = NON_TRADITIONAL;
+ERROR 42000: Variable 'old_mode' can't be set to the value of 'NON_TRADITIONAL'
+SET @@global.old_mode = 'OF';
+ERROR 42000: Variable 'old_mode' can't be set to the value of 'OF'
+SET @@global.old_mode = NONE;
+ERROR 42000: Variable 'old_mode' can't be set to the value of 'NONE'
+SET @@session.old_mode = -1;
+ERROR 42000: Variable 'old_mode' can't be set to the value of '-1'
+SET @@session.old_mode = ANSI_SINGLE_QUOTES;
+ERROR 42000: Variable 'old_mode' can't be set to the value of 'ANSI_SINGLE_QUOTES'
+SET @@session.old_mode = 'ON';
+ERROR 42000: Variable 'old_mode' can't be set to the value of 'ON'
+SET @@session.old_mode = 'OF';
+ERROR 42000: Variable 'old_mode' can't be set to the value of 'OF'
+SET @@session.old_mode = DISABLE;
+ERROR 42000: Variable 'old_mode' can't be set to the value of 'DISABLE'
+'#-------------------FN_DYNVARS_152_05----------------------------#'
+SELECT @@session.old_mode = VARIABLE_VALUE 
+FROM INFORMATION_SCHEMA.SESSION_VARIABLES 
+WHERE VARIABLE_NAME='old_mode';
+@@session.old_mode = VARIABLE_VALUE
+1
+'#----------------------FN_DYNVARS_152_06------------------------#'
+SELECT @@global.old_mode = VARIABLE_VALUE 
+FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES 
+WHERE VARIABLE_NAME='old_mode';
+@@global.old_mode = VARIABLE_VALUE
+1
+'#---------------------FN_DYNVARS_152_07-------------------------#'
+SET @@global.old_mode = 0;
+SELECT @@global.old_mode;
+@@global.old_mode
+
+SET @@global.old_mode = 1;
+SELECT @@global.old_mode;
+@@global.old_mode
+NO_DUP_KEY_WARNINGS_WITH_IGNORE
+SET @@global.old_mode = 2;
+SELECT @@global.old_mode;
+@@global.old_mode
+NO_PROGRESS_INFO
+SET @@global.old_mode = 4;
+ERROR 42000: Variable 'old_mode' can't be set to the value of '4'
+SELECT @@global.old_mode;
+@@global.old_mode
+NO_PROGRESS_INFO
+SET @@global.old_mode = 0.4;
+ERROR 42000: Incorrect argument type to variable 'old_mode'
+'#---------------------FN_DYNVARS_152_08----------------------#'
+SET @@global.old_mode = TRUE;
+SELECT @@global.old_mode;
+@@global.old_mode
+NO_DUP_KEY_WARNINGS_WITH_IGNORE
+SET @@global.old_mode = FALSE;
+SELECT @@global.old_mode;
+@@global.old_mode
+
+'#---------------------FN_DYNVARS_152_09----------------------#'
+SET old_mode = 'NO_PROGRESS_INFO';
+SET session.old_mode = 'ANSI';
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'old_mode = 'ANSI'' at line 1
+SET global.old_mode = 'ANSI';
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'old_mode = 'ANSI'' at line 1
+SET session old_mode = 1;
+SELECT @@old_mode;
+@@old_mode
+NO_DUP_KEY_WARNINGS_WITH_IGNORE
+SET global old_mode = 0;
+SELECT @@global.old_mode;
+@@global.old_mode
+
+'#---------------------FN_DYNVARS_152_10----------------------#'
+SET @@session.old_mode = 'NO_PROGRESS_INFO,NO_DUP_KEY_WARNINGS_WITH_IGNORE';
+SELECT @@session.old_mode;
+@@session.old_mode
+NO_DUP_KEY_WARNINGS_WITH_IGNORE,NO_PROGRESS_INFO
+SET @@global.old_mode = 'NO_DUP_KEY_WARNINGS_WITH_IGNORE,NO_PROGRESS_INFO';
+SELECT @@global.old_mode;
+@@global.old_mode
+NO_DUP_KEY_WARNINGS_WITH_IGNORE,NO_PROGRESS_INFO
+SET @@session.old_mode = 'NO_PROGRESS_INFO,NO_SUCH_MODE';
+ERROR 42000: Variable 'old_mode' can't be set to the value of 'NO_SUCH_MODE'
+SET @@old_mode=',';
+SELECT @@old_mode;
+@@old_mode
+
+SET @@old_mode=',,,,NO_PROGRESS_INFO,,,';
+SELECT @@old_mode;
+@@old_mode
+NO_PROGRESS_INFO
+SET @@old_mode=',,,,FOOBAR,,,,,';
+ERROR 42000: Variable 'old_mode' can't be set to the value of 'FOOBAR'
+SELECT @@old_mode;
+@@old_mode
+NO_PROGRESS_INFO
+SET @@global.old_mode = @global_start_value;
+SELECT @@global.old_mode;
+@@global.old_mode
+
+SET @@session.old_mode = @session_start_value;
+SELECT @@session.old_mode;
+@@session.old_mode
+
diff --git a/mysql-test/suite/sys_vars/t/old_mode_basic.test b/mysql-test/suite/sys_vars/t/old_mode_basic.test
new file mode 100644
index 00000000000..6770219887d
--- /dev/null
+++ b/mysql-test/suite/sys_vars/t/old_mode_basic.test
@@ -0,0 +1,244 @@
+#################### mysql-test\t\sql_mode_basic.test #########################
+#                                                                             #
+# Variable Name: old_mode                                                     #
+# Scope: GLOBAL | SESSION                                                     #
+# Access Type: Dynamic                                                        #
+# Data Type: enumeration                                                      #
+# Default Value: YES                                                          #
+# Valid Values : NO_DUP_KEY_WARNINGS_WITH_IGNORE, NO_PROGRESS_INFO            #
+#                                                                             #
+#                                                                             #
+# Description: Test Cases of Dynamic System Variable old_mode                 #
+#              that checks the behavior of this variable in the following ways#
+#              * Default Value                                                #
+#              * Valid & Invalid values                                       #
+#              * Scope & Access method                                        #
+#              * Data Integrity                                               #
+#                                                                             #
+#                                                                             #
+###############################################################################
+
+--source include/load_sysvars.inc
+
+################################################################
+#                    START OF sql_mode TESTS                   #
+################################################################
+
+
+###################################################################
+#     Saving initial value of sql_mode in a temporary variable    #
+###################################################################
+
+SET @global_start_value = @@global.old_mode;
+SELECT @global_start_value;
+
+SET @session_start_value = @@session.old_mode;
+SELECT @session_start_value;
+
+--echo '#--------------------FN_DYNVARS_152_01------------------------#'
+################################################################
+#                    Display the DEFAULT value of old_mode     #
+################################################################
+
+SET @@global.old_mode = "NO_PROGRESS_INFO";
+SET @@global.old_mode = DEFAULT;
+SELECT @@global.old_mode;
+
+SET @@session.old_mode = "NO_PROGRESS_INFO";
+SET @@session.old_mode = DEFAULT;
+SELECT @@session.old_mode;
+
+--echo '#---------------------FN_DYNVARS_152_02-------------------------#'
+######################################################### 
+#      Check if NULL or empty value is accepeted        #
+######################################################### 
+
+--Error ER_WRONG_VALUE_FOR_VAR
+SET @@global.old_mode = NULL;
+
+# resets sql mode to nothing
+SET @@global.old_mode = '';
+SELECT @@global.old_mode;
+
+SET @@global.old_mode = ' ';
+SELECT @@global.old_mode;
+
+--Error ER_WRONG_VALUE_FOR_VAR
+SET @@session.old_mode = NULL;
+
+SET @@session.old_mode = '';
+SELECT @@session.old_mode;
+
+SET @@session.old_mode = ' ';
+SELECT @@session.old_mode;
+
+
+--echo '#--------------------FN_DYNVARS_152_03------------------------#'
+######################################################################## 
+#              Change the value of old_mode to a valid value           #
+######################################################################## 
+
+# sql modes ref: http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html
+
+# check valid values for global
+
+SET @@global.old_mode = NO_PROGRESS_INFO;
+SELECT @@global.old_mode;
+SET @@global.old_mode = NO_DUP_KEY_WARNINGS_WITH_IGNORE;
+SELECT @@global.old_mode;
+
+--Error ER_WRONG_VALUE_FOR_VAR
+SET @@global.old_mode = OFF;
+
+#check valid values for session
+SET @@session.old_mode = NO_PROGRESS_INFO;
+SELECT @@session.old_mode;
+SET @@session.old_mode = NO_DUP_KEY_WARNINGS_WITH_IGNORE;
+SELECT @@session.old_mode;
+
+--Error ER_WRONG_VALUE_FOR_VAR
+SET @@session.old_mode = OFF;
+
+--Error ER_WRONG_VALUE_FOR_VAR
+SET @@global.old_mode = '?';
+SELECT @@global.old_mode;
+
+--echo '#--------------------FN_DYNVARS_152_04-------------------------#'
+########################################################################### 
+#               Change the value of old_mode to invalid value             #
+###########################################################################
+
+# invalid values for global
+--Error ER_WRONG_VALUE_FOR_VAR
+SET @@global.old_mode = -1;
+--Error ER_WRONG_VALUE_FOR_VAR
+SET @@global.old_mode = ASCII;
+--Error ER_WRONG_VALUE_FOR_VAR
+SET @@global.old_mode = NON_TRADITIONAL;
+--Error ER_WRONG_VALUE_FOR_VAR
+SET @@global.old_mode = 'OF';
+--Error ER_WRONG_VALUE_FOR_VAR
+SET @@global.old_mode = NONE;
+
+#invalid values for session
+
+--Error ER_WRONG_VALUE_FOR_VAR
+SET @@session.old_mode = -1;
+--Error ER_WRONG_VALUE_FOR_VAR
+SET @@session.old_mode = ANSI_SINGLE_QUOTES;
+--Error ER_WRONG_VALUE_FOR_VAR
+SET @@session.old_mode = 'ON';
+--Error ER_WRONG_VALUE_FOR_VAR
+SET @@session.old_mode = 'OF';
+--Error ER_WRONG_VALUE_FOR_VAR
+SET @@session.old_mode = DISABLE;
+
+
+--echo '#-------------------FN_DYNVARS_152_05----------------------------#'
+######################################################################### 
+#     Check if the value in session Table matches value in variable     #
+#########################################################################
+
+SELECT @@session.old_mode = VARIABLE_VALUE 
+FROM INFORMATION_SCHEMA.SESSION_VARIABLES 
+WHERE VARIABLE_NAME='old_mode';
+
+--echo '#----------------------FN_DYNVARS_152_06------------------------#'
+######################################################################### 
+#     Check if the value in GLOBAL Table matches value in variable      #
+#########################################################################
+
+SELECT @@global.old_mode = VARIABLE_VALUE 
+FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES 
+WHERE VARIABLE_NAME='old_mode';
+
+
+--echo '#---------------------FN_DYNVARS_152_07-------------------------#'
+################################################################### 
+#        Check if numbers can be used on variable                 #
+################################################################### 
+
+# test if variable accepts 0,1,2
+SET @@global.old_mode = 0;
+SELECT @@global.old_mode;
+
+SET @@global.old_mode = 1;
+SELECT @@global.old_mode;
+
+SET @@global.old_mode = 2;
+SELECT @@global.old_mode;
+
+--Error ER_WRONG_VALUE_FOR_VAR
+SET @@global.old_mode = 4;
+SELECT @@global.old_mode;
+
+# use of decimal values
+
+--Error ER_WRONG_TYPE_FOR_VAR
+SET @@global.old_mode = 0.4;
+
+--echo '#---------------------FN_DYNVARS_152_08----------------------#'
+################################################################### 
+#      Check if TRUE and FALSE values can be used on variable     #
+################################################################### 
+
+SET @@global.old_mode = TRUE;
+SELECT @@global.old_mode;
+SET @@global.old_mode = FALSE;
+SELECT @@global.old_mode;
+
+--echo '#---------------------FN_DYNVARS_152_09----------------------#'
+#########################################################################  
+# Check if old_mode can be accessed with and without @@ sign            #
+#########################################################################
+
+SET old_mode = 'NO_PROGRESS_INFO';
+
+--Error ER_PARSE_ERROR
+SET session.old_mode = 'ANSI';
+--Error ER_PARSE_ERROR
+SET global.old_mode = 'ANSI';
+
+SET session old_mode = 1;
+SELECT @@old_mode;
+
+SET global old_mode = 0;
+SELECT @@global.old_mode;
+
+--echo '#---------------------FN_DYNVARS_152_10----------------------#'
+#######################################################################
+#    Check if old_mode values can be combined as specified            #
+#######################################################################
+
+SET @@session.old_mode = 'NO_PROGRESS_INFO,NO_DUP_KEY_WARNINGS_WITH_IGNORE';
+SELECT @@session.old_mode;
+
+SET @@global.old_mode = 'NO_DUP_KEY_WARNINGS_WITH_IGNORE,NO_PROGRESS_INFO';
+SELECT @@global.old_mode;
+
+#try combining invalid mode with correct mode
+--Error ER_WRONG_VALUE_FOR_VAR
+SET @@session.old_mode = 'NO_PROGRESS_INFO,NO_SUCH_MODE';
+
+#zero-length values are ok
+SET @@old_mode=',';
+SELECT @@old_mode;
+SET @@old_mode=',,,,NO_PROGRESS_INFO,,,';
+SELECT @@old_mode;
+--Error ER_WRONG_VALUE_FOR_VAR
+SET @@old_mode=',,,,FOOBAR,,,,,';
+SELECT @@old_mode;
+
+##############################
+#   Restore initial value    #
+##############################
+
+SET @@global.old_mode = @global_start_value;
+SELECT @@global.old_mode;
+
+SET @@session.old_mode = @session_start_value;
+SELECT @@session.old_mode;
+
+################################################################
+#                    END OF old_mode TESTS                     #
+################################################################
diff --git a/mysql-test/t/insert.test b/mysql-test/t/insert.test
index f9b9fcf266d..ff8396fd7fd 100644
--- a/mysql-test/t/insert.test
+++ b/mysql-test/t/insert.test
@@ -555,3 +555,21 @@ INSERT IGNORE t1 (a, a) SELECT 1,1;
 INSERT IGNORE t1 (a, a) SELECT 1,1 UNION SELECT 2,2;
 
 DROP TABLE t1;
+
+--echo #
+--echo # MDEV-5168: Ensure that we can disable duplicate key warnings
+--echo # from INSERT IGNORE
+--echo #
+
+create table t1 (f1 int unique, f2 int unique);
+insert into t1 values (1,12);
+insert into t1 values (2,13);
+--error ER_DUP_ENTRY
+insert into t1 values (1,12);
+insert ignore into t1 values (1,12);
+set @@old_mode="NO_DUP_KEY_WARNINGS_WITH_IGNORE";
+insert ignore into t1 values (1,12);
+insert ignore into t1 values (1,12) on duplicate key update f2=13;
+set @@old_mode="";
+insert ignore into t1 values (1,12);
+DROP TABLE t1;