IGNORE
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.
- SQL_MODEs
STRICT_TRANS_TABLES
,STRICT_ALL_TABLES
,NO_ZERO_IN_DATE
,NO_ZERO_DATE
are ignored.
- Inserting
NULL
in aNOT 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 number | Symbolic error name | Description |
---|---|---|
1022 | ER_DUP_KEY | Can't write; duplicate key in table 'xxx'" |
1048 | ER_BAD_NULL_ERROR | Column 'xxx' cannot be null |
1242 | ER_SUBQUERY_NO_1_ROW | Subquery returns more than 1 row |
1264 | ER_WARN_DATA_OUT_OF_RANGE | Out of range value for column 'xxx' |
1265 | WARN_DATA_TRUNCATED | Data truncated for column 'xxx' |
1292 | ER_TRUNCATED_WRONG_VALUE | Incorrect date value: 'xxx' |
1366 | ER_TRUNCATED_WRONG_VALUE_FOR_FIELD | Incorrect integer value |
1369 | ER_VIEW_CHECK_FAILED | CHECK OPTION failed 'xxx' |
1451 | ER_ROW_IS_REFERENCED_2 | Cannot delete or update a parent row |
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.