MySQL Blog Archive
For the latest blogs go to blogs.oracle.com/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.

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).

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

Bugs Fixed by This Worklog

  1. 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.
  2. Bug#43895: Multi-DELETE IGNORE does not report warnings
    Same case as mentioned in #1 above.
  3. 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.