MySQL :: Improvements to the MySQL `IGNORE` Implementation

In 5.7.5, as a part of the larger effort to improve error handling, we re-implemented the IGNORE clause (WL#6614). The IGNORE clause is a MySQL extension to the SQL standard. It affects the errors which occur for each row. The new implementation aims to make the behavior of the IGNORE clause more consistent.

Statements which support the IGNORE clause are:

INSERT [ IGNORE ]
UPDATE [ IGNORE ]
DELETE [ IGNORE ]
LOAD DATA [ IGNORE ]
LOAD XML [ IGNORE ]
CREATE TABLE… [ IGNORE ] SELECT

When the INSERT statement is used to insert a number of rows into a table, an exception during processing would normally abort the statement and return an error message. With the IGNORE keyword, rows that cause certain exceptions are ignored, but the remaining rows are inserted and the execution of the statement is regarded as successful. The same principle applies to UPDATE, DELETE, LOAD, and CREATE…SELECT statements (the IGNORE keyword only affects the DML part of a CREATE…SELECT statement).

The IGNORE clause has two basic functions which we’ll now describe.

Independent of ‘STRICT’ mode

The IGNORE clause downgrades errors to warnings and continues the processing of a statement. The IGNORE keyword affects the following error codes:

ER_SUBQUERY_NO_1_ROW : Subquery returned more than 1 row when one is expected
ER_ROW_IS_REFERENCED_2 : Foreign key constraint violation in parent table
ER_NO_REFERENCED_ROW_2 : Foreign key constraint violation in child table
ER_BAD_NULL_ERROR : NOT NULL constraint violation
ER_DUP_ENTRY : Unique key constraint violation
ER_DUP_ENTRY_WITH_KEY_NAME : Unique key constraint violation
ER_DUP_KEY : Unique key constraint violation
ER_VIEW_CHECK_FAILED : CHECK constraint for view fails
ER_NO_PARTITION_FOR_GIVEN_VALUE : No partition in the table for the given value
ER_NO_PARTITION_FOR_GIVEN_VALUE_SILENT : No partition in the table for the existing value
ER_ROW_DOES_NOT_MATCH_GIVEN_PARTITION_SET : Row not found matching the given

The row which caused the error is then skipped and the operation continues with the next row. One warning is output for each ignored exception. If two rows violate a UNIQUE constraint, we will get 2 warnings. We do not allow constraints to be broken inside the database.

A Practical Use Case for IGNORE

We have a table ‘t’ created with the following statement:
CREATE TABLE t(a INT) ENGINE = InnoDB;

Let’s look at an example of 3 rows with 1 duplicate among them.

mysql>CREATE TABLE t(a INT) ENGINE = InnoDB;
Query OK, 0 rows affected (0.44 sec)
 
mysql> INSERT INTO t VALUES(1),(3),(1);
Query OK, 3 rows affected (0.04 sec)
 
mysql>ALTER TABLE t ADD CONSTRAINT UNIQUE(a);
ERROR 1062 (23000): Duplicate entry '1' for key 'a'
...

mysql>CREATE TABLE t(a INT) ENGINE = InnoDB;

Query OK, 0 rows affected (0.44 sec)

mysql> INSERT INTO t VALUES(1),(3),(1);

Query OK, 3 rows affected (0.04 sec)

mysql>ALTER TABLE t ADD CONSTRAINT UNIQUE(a);

ERROR 1062 (23000): Duplicate entry '1' for key 'a'

...

We can not add a UNIQUE constraint directly here, but there is a workaround using the IGNORE clause (when adding a UNIQUE key, we will lose the data found in duplicate rows).

...   
mysql>CREATE TABLE t_temp LIKE t;
Query OK, 0 rows affected (0.29 sec)

mysql>ALTER TABLE t_temp ADD CONSTRAINT UNIQUE(a);
Query OK, 0 rows affected (0.54 sec)

mysql>INSERT IGNORE INTO t_temp SELECT * from t;
Query OK, 2 rows affected, 1 warning (0.04 sec)

mysql>SHOW WARNINGS;
+---------+------+---------------------------------+
| Level   | Code | Message                         |
+---------+------+---------------------------------+
| Warning | 1062 | Duplicate entry '1' for key 'a' |
+---------+------+---------------------------------+
1 row in set (0.00 sec)

mysql>DROP TABLE t;
Query OK, 0 rows affected (0.19 sec)

mysql>ALTER TABLE t_temp RENAME TO t;
Query OK, 0 rows affected (0.13 sec)

mysql>SELECT * FROM t;
+------+
| a    |
+------+
|    1 |
|    3 |
+------+
2 rows in set (0.01 sec)
...

...

mysql>CREATE TABLE t_temp LIKE t;

Query OK, 0 rows affected (0.29 sec)

mysql>ALTER TABLE t_temp ADD CONSTRAINT UNIQUE(a);

Query OK, 0 rows affected (0.54 sec)

mysql>INSERT IGNORE INTO t_temp SELECT * from t;

Query OK, 2 rows affected, 1 warning (0.04 sec)

mysql>SHOW WARNINGS;

+---------+------+---------------------------------+

| Level | Code | Message |

+---------+------+---------------------------------+

| Warning | 1062 | Duplicate entry '1' for key 'a' |

+---------+------+---------------------------------+

1 row in set (0.00 sec)

mysql>DROP TABLE t;

Query OK, 0 rows affected (0.19 sec)

mysql>ALTER TABLE t_temp RENAME TO t;

Query OK, 0 rows affected (0.13 sec)

mysql>SELECT * FROM t;

+------+

| a |

+------+

| 1 |

| 3 |

+------+

2 rows in set (0.01 sec)

...

In the case of 3 rows there is little reason to use IGNORE, but if the number of rows is very large IGNORE can be of much help. This simply serves as a simple example.

When ‘STRICT’ Mode is ON

STRICT mode controls how MySQL handles invalid or missing values in data changing statements such as INSERT or UPDATE. With STRICT mode ON, MySQL produces an error for invalid values and aborts the statement. Using the IGNORE keyword in a statement cancels the effects of STRICT mode for that statement. The following error codes are not upgraded from warnings to errors by STRICT mode when the IGNORE clause is used:

ER_TRUNCATED_WRONG_VALUE : Truncated incorrect value
ER_WRONG_VALUE_FOR_TYPE : Incorrect value for function
ER_WARN_DATA_OUT_OF_RANGE : Out of range value
ER_DIVISION_BY_ZERO : Division by 0″
ER_TRUNCATED_WRONG_VALUE_FOR_FIELD : Incorrect value for column at row
WARN_DATA_TRUNCATED : Data truncated for column
ER_DATA_TOO_LONG : Data too long for column
ER_BAD_NULL_ERROR : Column cannot be null
ER_NO_DEFAULT_FOR_FIELD : Field doesn’t have a default value”
ER_NO_DEFAULT_FOR_VIEW_FIELD : Field of view underlying table doesn’t have a default value
ER_CUT_VALUE_GROUP_CONCAT : Row was cut by GROUP_CONCAT()
ER_DATETIME_FUNCTION_OVERFLOW : Datetime function field overflow
ER_WARN_TOO_FEW_RECORDS : Row doesn’t contain data for all columns
ER_WARN_NULL_TO_NOTNULL : Column set to default value: NULL supplied to NOT NULL column”
ER_INVALID_ARGUMENT_FOR_LOGARITHM : Invalid argument for logarithm
ER_WRONG_ARGUMENTS : Incorrect arguments

In these cases MySQL inserts the adjusted values for invalid or missing values and produces warnings.

An Example Use Case

...
mysql>SET sql_mode='STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.00 sec)

mysql>CREATE TABLE t(a VARCHAR(5));
Query OK, 0 rows affected (0.34 sec)

mysql>INSERT INTO t VALUES('abcdef');
ERROR 1406 (22001): Data too long for column 'a' at row 1

mysql>INSERT IGNORE INTO t VALUES('abcdef');
Query OK, 1 row affected, 1 warning (0.05 sec)

mysql>SHOW WARNINGS;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1265 | Data truncated for column 'a' at row 1 |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

mysql>SELECT * FROM t;
+-------+
| a     |
+-------+
| abcde |
+-------+
1 row in set (0.00 sec)

...

...

mysql>SET sql_mode='STRICT_ALL_TABLES';

Query OK, 0 rows affected (0.00 sec)

mysql>CREATE TABLE t(a VARCHAR(5));

Query OK, 0 rows affected (0.34 sec)

mysql>INSERT INTO t VALUES('abcdef');

ERROR 1406 (22001): Data too long for column 'a' at row 1

mysql>INSERT IGNORE INTO t VALUES('abcdef');

Query OK, 1 row affected, 1 warning (0.05 sec)

mysql>SHOW WARNINGS;

+---------+------+----------------------------------------+

| Level | Code | Message |

+---------+------+----------------------------------------+

| Warning | 1265 | Data truncated for column 'a' at row 1 |

+---------+------+----------------------------------------+

1 row in set (0.00 sec)

mysql>SELECT * FROM t;

+-------+

| a |

+-------+

| abcde |

+-------+

1 row in set (0.00 sec)

...

Bugs Fixed by This Worklog

Bug#6196: INSERT IGNORE should return warnings
For the error codes mentioned in case (a) above, there were no warnings before this worklog when a constraint would fail. For example, INSERT IGNORE will silently ignore the duplicate values.
Bug#43895: Multi-DELETE IGNORE does not report warnings
Same case as mentioned in #1 above.
Bug#68726: Update trigger invoked when update ignore means that no update is performed
When IGNORE is used with an UPDATE statement, for all supported error codes there will be no error and the statement will be marked as having successfully executed. For the rows which did not get updated, however, the AFTER UPDATE trigger will not be executed.