W3cubDocs

/MariaDB

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 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 number Symbolic error name Description
1022 ER_DUP_KEY Can't write; duplicate key in table '%s'
1048 ER_BAD_NULL_ERROR Column '%s' cannot be null
1062 ER_DUP_ENTRY Duplicate entry '%s' for key %d
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 '%s' at row %ld
1265 WARN_DATA_TRUNCATED Data truncated for column '%s' at row %ld
1292 ER_TRUNCATED_WRONG_VALUE Truncated incorrect %s value: '%s'
1366 ER_TRUNCATED_WRONG_VALUE_FOR_FIELD Incorrect integer value
1369 ER_VIEW_CHECK_FAILED CHECK OPTION failed '%s.%s'
1451 ER_ROW_IS_REFERENCED_2 Cannot delete or update a parent row
1452 ER_NO_REFERENCED_ROW_2 Cannot add or update a child row: a foreign key constraint fails (%s)
1526 ER_NO_PARTITION_FOR_GIVEN_VALUE Table has no partition for value %s
1586 ER_DUP_ENTRY_WITH_KEY_NAME Duplicate entry '%s' for key '%s'
1591 ER_NO_PARTITION_FOR_GIVEN_VALUE_SILENT Table has no partition for some existing values
1748 ER_ROW_DOES_NOT_MATCH_GIVEN_PARTITION_SET Found 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.

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.

© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/ignore/