The IGNORE option tells the server to ignore some common errors.

IGNORE can be used with the following statements:

The logic used:

  • Variables out of ranges are replaced with the maximum/minimum value.
  • Inserting NULL in a NOT NULL field will insert 0 ( in a numerical field), 0000-00-00 ( in a date field) or an empty string ( in a character field).
  • Rows that cause a duplicate key error or break a foreign key constraint are not inserted, updated, or deleted.

The following errors are ignored:

Error numberSymbolic error nameDescription
1022ER_DUP_KEYCan't write; duplicate key in table '%s'
1048ER_BAD_NULL_ERRORColumn '%s' cannot be null
1062ER_DUP_ENTRYDuplicate entry '%s' for key %d
1242ER_SUBQUERY_NO_1_ROWSubquery returns more than 1 row
1264ER_WARN_DATA_OUT_OF_RANGEOut of range value for column '%s' at row %ld
1265WARN_DATA_TRUNCATEDData truncated for column '%s' at row %ld
1292ER_TRUNCATED_WRONG_VALUETruncated incorrect %s value: '%s'
1366ER_TRUNCATED_WRONG_VALUE_FOR_FIELDIncorrect integer value
1451ER_ROW_IS_REFERENCED_2Cannot delete or update a parent row
1452ER_NO_REFERENCED_ROW_2Cannot add or update a child row: a foreign key constraint fails (%s)
1526ER_NO_PARTITION_FOR_GIVEN_VALUETable has no partition for value %s
1586ER_DUP_ENTRY_WITH_KEY_NAMEDuplicate entry '%s' for key '%s'
1591ER_NO_PARTITION_FOR_GIVEN_VALUE_SILENTTable has no partition for some existing values
1748ER_ROW_DOES_NOT_MATCH_GIVEN_PARTITION_SETFound a row not matching the given partition set

Ignored errors normally generate a warning.

A property of the IGNORE clause consists in causing transactional engines and non-transactional engines (like XtraDB and Aria) to behave the same way. For example, normally a multi-row insert which tries to violate a UNIQUE contraint is completely rolled back on XtraDB/InnoDB, but might be partially executed on Aria. With the IGNORE clause, the statement will be partially executed in both engines.

MariaDB starting with 5.5.28

Starting from MariaDB 5.5.28 duplicate key errors also generate warnings. The OLD_MODE server variable can be used to prevent this.


Comments loading...