[MDEV-10589] sql_mode=ORACLE: LOG ERRORS clause in INSERT, DELETE, UPDATE - Jira

Alexander Barkov created issue - 2016-08-18 05:02

Alexander Barkov made changes - 2016-08-18 05:05

Field	Original Value	New Value
Description	When running in {{sql_mode=ORACLE}}, MariaDB will understand an optional {{LOG ERRORS}} clause with this syntax: {code:sql} LOG ERRORS [INTO [schema.]table] [('simple_expression')] [REJECT LIMIT integer\|UNLIMITED] {code} Example: {code:sql} DELETE FROM t1 LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1; INSERT INTO t1 (a,b,c) SELECT a,b,c FROM t2 LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1 {code}	When running in {{sql_mode=ORACLE}}, MariaDB will understand an optional {{LOG ERRORS}} clause with this syntax: {code:sql} LOG ERRORS [INTO [schema.]table] [('simple_expression')] [REJECT LIMIT integer\|UNLIMITED] {code} Example: {code:sql} DELETE FROM t1 LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1; INSERT INTO t1 (a,b,c) SELECT a,b,c FROM t2 LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1 {code}

Alexander Barkov made changes - 2016-08-18 05:11

Description

When running in {{sql_mode=ORACLE}}, MariaDB will understand an optional {{LOG ERRORS}} clause with this syntax:
{code:sql}
LOG ERRORS
  [INTO [schema.]table]
  [('simple_expression')]
  [REJECT LIMIT integer|UNLIMITED]
{code}

Example:
{code:sql}
DELETE FROM t1
  LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1;

INSERT INTO t1 (a,b,c) SELECT a,b,c FROM t2
  LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1
{code}

When running in {{sql_mode=ORACLE}}, MariaDB will understand an optional {{LOG ERRORS}} clause with this syntax:
{code:sql}
LOG ERRORS
  [INTO [schema.]table]
  [('simple_expression')]
  [REJECT LIMIT integer|UNLIMITED]
{code}

Example:
{code:sql}
DELETE FROM t1
  LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1;

INSERT INTO t1 (a,b,c) SELECT a,b,c FROM t2
  LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1
{code}

{{LOG ERRORS}} is used for two purposes:
1. For actual logging of errors
2. To change transaction error handling behavior. By default, when a DML statement fails on some row (e.g. due to a {{CONSTRAINT}} check, the whole statement is rolled back, regardless of how many rows were processed successfully before the error was detected. The {{REJECT LIMIT}} clause specifies the maximum number of errors before the statement fails.

Michael Widenius made changes - 2016-08-18 11:23

Link

This issue is part of MDEV-10137 [ MDEV-10137 ]

Michael Widenius made changes - 2016-08-18 12:09

Link

This issue is part of MDEV-10137 [ MDEV-10137 ]

Michael Widenius made changes - 2016-08-18 12:09

Parent		~~MDEV-10142~~ [ 56873 ]
Issue Type	Task [ 3 ]	Technical task [ 7 ]

Alvin Richards (Inactive) made changes - 2016-08-18 16:24

Labels

Compatibility

Alvin Richards (Inactive) made changes - 2016-08-18 16:31

Labels

Compatibility

Michael Widenius added a comment - 2016-08-18 16:40

For the first version, lets concentrate on LIMIT 1.
The reason for this is to not have to change all loops in the server to be able to handle more than one error.

We can probably reuse the code that provides general logging to tables by installing a temporary error handler for the user.

Michael Widenius added a comment - 2016-08-18 16:40 For the first version, lets concentrate on LIMIT 1. The reason for this is to not have to change all loops in the server to be able to handle more than one error. We can probably reuse the code that provides general logging to tables by installing a temporary error handler for the user.

Alvin Richards (Inactive) made changes - 2016-09-01 07:15

Labels

Compatibility

Alvin Richards (Inactive) made changes - 2016-09-01 17:29

Labels

Compatibility

Sergei Golubchik made changes - 2016-09-20 16:45

Labels

Compatibility

Alvin Richards (Inactive) made changes - 2016-11-07 23:32

Labels

Compatibility

Compatibility NRE-307517

Alexander Barkov made changes - 2016-11-15 12:41

Description

When running in {{sql_mode=ORACLE}}, MariaDB will understand an optional {{LOG ERRORS}} clause with this syntax:
{code:sql}
LOG ERRORS
  [INTO [schema.]table]
  [('simple_expression')]
  [REJECT LIMIT integer|UNLIMITED]
{code}

Example:
{code:sql}
DELETE FROM t1
  LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1;

INSERT INTO t1 (a,b,c) SELECT a,b,c FROM t2
  LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1
{code}

{{LOG ERRORS}} is used for two purposes:
1. For actual logging of errors
2. To change transaction error handling behavior. By default, when a DML statement fails on some row (e.g. due to a {{CONSTRAINT}} check, the whole statement is rolled back, regardless of how many rows were processed successfully before the error was detected. The {{REJECT LIMIT}} clause specifies the maximum number of errors before the statement fails.

When running in {{sql_mode=ORACLE}}, MariaDB will understand an optional {{LOG ERRORS}} clause with this syntax:
{code:sql}
LOG ERRORS
  [INTO [schema.]table]
  [('simple_expression')]
  [REJECT LIMIT integer|UNLIMITED]
{code}

Example:
{code:sql}
DELETE FROM t1
  LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1;

INSERT INTO t1 (a,b,c) SELECT a,b,c FROM t2
  LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1
{code}

{{LOG ERRORS}} is used for two purposes:
1. For actual logging of errors
2. To change transaction error handling behavior. By default, when a DML statement fails on some row (e.g. due to a {{CONSTRAINT}} check, the whole statement is rolled back, regardless of how many rows were processed successfully before the error was detected. The {{REJECT LIMIT}} clause specifies the maximum number of errors before the statement fails.

Example:
{code:sql}
DROP TABLE err$_t1;
BEGIN
  DBMS_ERRLOG.create_error_log (dml_table_name => 't1');
END;
/
DROP TABLE t1;
CREATE TABLE t1 (a INT PRIMARY KEY);
INSERT INTO t1 VALUES (10);
{code}

Now I insert a row which would normally raise a "err_dup_key" exception:
{code:sql}
INSERT INTO t1 VALUES (10) LOG ERRORS REJECT LIMIT 1;
{code}
{noformat}
0 rows created.
{noformat}

Notice, no exceptions were raised.

Alexander Barkov made changes - 2016-11-15 12:42

Description

When running in {{sql_mode=ORACLE}}, MariaDB will understand an optional {{LOG ERRORS}} clause with this syntax:
{code:sql}
LOG ERRORS
  [INTO [schema.]table]
  [('simple_expression')]
  [REJECT LIMIT integer|UNLIMITED]
{code}

Example:
{code:sql}
DELETE FROM t1
  LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1;

INSERT INTO t1 (a,b,c) SELECT a,b,c FROM t2
  LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1
{code}

{{LOG ERRORS}} is used for two purposes:
1. For actual logging of errors
2. To change transaction error handling behavior. By default, when a DML statement fails on some row (e.g. due to a {{CONSTRAINT}} check, the whole statement is rolled back, regardless of how many rows were processed successfully before the error was detected. The {{REJECT LIMIT}} clause specifies the maximum number of errors before the statement fails.

Example:
{code:sql}
DROP TABLE err$_t1;
BEGIN
  DBMS_ERRLOG.create_error_log (dml_table_name => 't1');
END;
/
DROP TABLE t1;
CREATE TABLE t1 (a INT PRIMARY KEY);
INSERT INTO t1 VALUES (10);
{code}

Now I insert a row which would normally raise a "err_dup_key" exception:
{code:sql}
INSERT INTO t1 VALUES (10) LOG ERRORS REJECT LIMIT 1;
{code}
{noformat}
0 rows created.
{noformat}

Notice, no exceptions were raised.

When running in {{sql_mode=ORACLE}}, MariaDB will understand an optional {{LOG ERRORS}} clause with this syntax:
{code:sql}
LOG ERRORS
  [INTO [schema.]table]
  [('simple_expression')]
  [REJECT LIMIT integer|UNLIMITED]
{code}

Example:
{code:sql}
DELETE FROM t1
  LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1;

INSERT INTO t1 (a,b,c) SELECT a,b,c FROM t2
  LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1
{code}

{{LOG ERRORS}} is used for two purposes:
1. For actual logging of errors
2. To change transaction error handling behavior. By default, when a DML statement fails on some row (e.g. due to a {{CONSTRAINT}} check, the whole statement is rolled back, regardless of how many rows were processed successfully before the error was detected. The {{REJECT LIMIT}} clause specifies the maximum number of errors before the statement fails.

Example:
{code:sql}
DROP TABLE err$_t1;
BEGIN
  DBMS_ERRLOG.create_error_log (dml_table_name => 't1');
END;
/
DROP TABLE t1;
CREATE TABLE t1 (a INT PRIMARY KEY);
INSERT INTO t1 VALUES (10);
{code}

Now I insert a row which would normally raise a "err_dup_key" exception:
{code:sql}
INSERT INTO t1 VALUES (10) LOG ERRORS REJECT LIMIT 1;
{code}
{noformat}
0 rows created.
{noformat}

Notice, no exceptions were raised, and a recod in {{err$_t1}} was created instead:
{code:sql}
SELECT ORA_ERR_NUMBER$,ORA_ERR_MESG$ FROM err$_t1;
{code}
{noformat}
ORA_ERR_NUMBER$
---------------
ORA_ERR_MESG$
--------------------------------------------------------------------------------
1
ORA-00001: unique constraint (SYSTEM.SYS_C0071433) violated
{noformat}

Alexander Barkov made changes - 2016-11-15 12:42

Description

When running in {{sql_mode=ORACLE}}, MariaDB will understand an optional {{LOG ERRORS}} clause with this syntax:
{code:sql}
LOG ERRORS
  [INTO [schema.]table]
  [('simple_expression')]
  [REJECT LIMIT integer|UNLIMITED]
{code}

Example:
{code:sql}
DELETE FROM t1
  LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1;

INSERT INTO t1 (a,b,c) SELECT a,b,c FROM t2
  LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1
{code}

{{LOG ERRORS}} is used for two purposes:
1. For actual logging of errors
2. To change transaction error handling behavior. By default, when a DML statement fails on some row (e.g. due to a {{CONSTRAINT}} check, the whole statement is rolled back, regardless of how many rows were processed successfully before the error was detected. The {{REJECT LIMIT}} clause specifies the maximum number of errors before the statement fails.

Example:
{code:sql}
DROP TABLE err$_t1;
BEGIN
  DBMS_ERRLOG.create_error_log (dml_table_name => 't1');
END;
/
DROP TABLE t1;
CREATE TABLE t1 (a INT PRIMARY KEY);
INSERT INTO t1 VALUES (10);
{code}

Now I insert a row which would normally raise a "err_dup_key" exception:
{code:sql}
INSERT INTO t1 VALUES (10) LOG ERRORS REJECT LIMIT 1;
{code}
{noformat}
0 rows created.
{noformat}

Notice, no exceptions were raised, and a recod in {{err$_t1}} was created instead:
{code:sql}
SELECT ORA_ERR_NUMBER$,ORA_ERR_MESG$ FROM err$_t1;
{code}
{noformat}
ORA_ERR_NUMBER$
---------------
ORA_ERR_MESG$
--------------------------------------------------------------------------------
1
ORA-00001: unique constraint (SYSTEM.SYS_C0071433) violated
{noformat}

When running in {{sql_mode=ORACLE}}, MariaDB will understand an optional {{LOG ERRORS}} clause with this syntax:
{code:sql}
LOG ERRORS
  [INTO [schema.]table]
  [('simple_expression')]
  [REJECT LIMIT integer|UNLIMITED]
{code}

Example:
{code:sql}
DELETE FROM t1
  LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1;

INSERT INTO t1 (a,b,c) SELECT a,b,c FROM t2
  LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1
{code}

{{LOG ERRORS}} is used for two purposes:
1. For actual logging of errors
2. To change transaction error handling behavior. By default, when a DML statement fails on some row (e.g. due to a {{CONSTRAINT}} check, the whole statement is rolled back, regardless of how many rows were processed successfully before the error was detected. The {{REJECT LIMIT}} clause specifies the maximum number of errors before the statement fails.

h2 Example:
{code:sql}
DROP TABLE err$_t1;
BEGIN
  DBMS_ERRLOG.create_error_log (dml_table_name => 't1');
END;
/
DROP TABLE t1;
CREATE TABLE t1 (a INT PRIMARY KEY);
INSERT INTO t1 VALUES (10);
{code}

Now I insert a row which would normally raise a "err_dup_key" exception:
{code:sql}
INSERT INTO t1 VALUES (10) LOG ERRORS REJECT LIMIT 1;
{code}
{noformat}
0 rows created.
{noformat}

Notice, no exceptions were raised, and a recod in {{err$_t1}} was created instead:
{code:sql}
SELECT ORA_ERR_NUMBER$,ORA_ERR_MESG$ FROM err$_t1;
{code}
{noformat}
ORA_ERR_NUMBER$
---------------
ORA_ERR_MESG$
--------------------------------------------------------------------------------
1
ORA-00001: unique constraint (SYSTEM.SYS_C0071433) violated
{noformat}

Alexander Barkov made changes - 2016-11-15 12:43

Description

When running in {{sql_mode=ORACLE}}, MariaDB will understand an optional {{LOG ERRORS}} clause with this syntax:
{code:sql}
LOG ERRORS
  [INTO [schema.]table]
  [('simple_expression')]
  [REJECT LIMIT integer|UNLIMITED]
{code}

Example:
{code:sql}
DELETE FROM t1
  LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1;

INSERT INTO t1 (a,b,c) SELECT a,b,c FROM t2
  LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1
{code}

{{LOG ERRORS}} is used for two purposes:
1. For actual logging of errors
2. To change transaction error handling behavior. By default, when a DML statement fails on some row (e.g. due to a {{CONSTRAINT}} check, the whole statement is rolled back, regardless of how many rows were processed successfully before the error was detected. The {{REJECT LIMIT}} clause specifies the maximum number of errors before the statement fails.

h2 Example:
{code:sql}
DROP TABLE err$_t1;
BEGIN
  DBMS_ERRLOG.create_error_log (dml_table_name => 't1');
END;
/
DROP TABLE t1;
CREATE TABLE t1 (a INT PRIMARY KEY);
INSERT INTO t1 VALUES (10);
{code}

Now I insert a row which would normally raise a "err_dup_key" exception:
{code:sql}
INSERT INTO t1 VALUES (10) LOG ERRORS REJECT LIMIT 1;
{code}
{noformat}
0 rows created.
{noformat}

Notice, no exceptions were raised, and a recod in {{err$_t1}} was created instead:
{code:sql}
SELECT ORA_ERR_NUMBER$,ORA_ERR_MESG$ FROM err$_t1;
{code}
{noformat}
ORA_ERR_NUMBER$
---------------
ORA_ERR_MESG$
--------------------------------------------------------------------------------
1
ORA-00001: unique constraint (SYSTEM.SYS_C0071433) violated
{noformat}

When running in {{sql_mode=ORACLE}}, MariaDB will understand an optional {{LOG ERRORS}} clause with this syntax:
{code:sql}
LOG ERRORS
  [INTO [schema.]table]
  [('simple_expression')]
  [REJECT LIMIT integer|UNLIMITED]
{code}

Example:
{code:sql}
DELETE FROM t1
  LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1;

INSERT INTO t1 (a,b,c) SELECT a,b,c FROM t2
  LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1
{code}

{{LOG ERRORS}} is used for two purposes:
1. For actual logging of errors
2. To change transaction error handling behavior. By default, when a DML statement fails on some row (e.g. due to a {{CONSTRAINT}} check, the whole statement is rolled back, regardless of how many rows were processed successfully before the error was detected. The {{REJECT LIMIT}} clause specifies the maximum number of errors before the statement fails.

.h2 Example:
{code:sql}
DROP TABLE err$_t1;
BEGIN
  DBMS_ERRLOG.create_error_log (dml_table_name => 't1');
END;
/
DROP TABLE t1;
CREATE TABLE t1 (a INT PRIMARY KEY);
INSERT INTO t1 VALUES (10);
{code}

Now I insert a row which would normally raise a "err_dup_key" exception:
{code:sql}
INSERT INTO t1 VALUES (10) LOG ERRORS REJECT LIMIT 1;
{code}
{noformat}
0 rows created.
{noformat}

Notice, no exceptions were raised, and a recod in {{err$_t1}} was created instead:
{code:sql}
SELECT ORA_ERR_NUMBER$,ORA_ERR_MESG$ FROM err$_t1;
{code}
{noformat}
ORA_ERR_NUMBER$
---------------
ORA_ERR_MESG$
--------------------------------------------------------------------------------
1
ORA-00001: unique constraint (SYSTEM.SYS_C0071433) violated
{noformat}

Alexander Barkov made changes - 2016-11-15 12:43

Description

When running in {{sql_mode=ORACLE}}, MariaDB will understand an optional {{LOG ERRORS}} clause with this syntax:
{code:sql}
LOG ERRORS
  [INTO [schema.]table]
  [('simple_expression')]
  [REJECT LIMIT integer|UNLIMITED]
{code}

Example:
{code:sql}
DELETE FROM t1
  LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1;

INSERT INTO t1 (a,b,c) SELECT a,b,c FROM t2
  LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1
{code}

{{LOG ERRORS}} is used for two purposes:
1. For actual logging of errors
2. To change transaction error handling behavior. By default, when a DML statement fails on some row (e.g. due to a {{CONSTRAINT}} check, the whole statement is rolled back, regardless of how many rows were processed successfully before the error was detected. The {{REJECT LIMIT}} clause specifies the maximum number of errors before the statement fails.

.h2 Example:
{code:sql}
DROP TABLE err$_t1;
BEGIN
  DBMS_ERRLOG.create_error_log (dml_table_name => 't1');
END;
/
DROP TABLE t1;
CREATE TABLE t1 (a INT PRIMARY KEY);
INSERT INTO t1 VALUES (10);
{code}

Now I insert a row which would normally raise a "err_dup_key" exception:
{code:sql}
INSERT INTO t1 VALUES (10) LOG ERRORS REJECT LIMIT 1;
{code}
{noformat}
0 rows created.
{noformat}

Notice, no exceptions were raised, and a recod in {{err$_t1}} was created instead:
{code:sql}
SELECT ORA_ERR_NUMBER$,ORA_ERR_MESG$ FROM err$_t1;
{code}
{noformat}
ORA_ERR_NUMBER$
---------------
ORA_ERR_MESG$
--------------------------------------------------------------------------------
1
ORA-00001: unique constraint (SYSTEM.SYS_C0071433) violated
{noformat}

When running in {{sql_mode=ORACLE}}, MariaDB will understand an optional {{LOG ERRORS}} clause with this syntax:
{code:sql}
LOG ERRORS
  [INTO [schema.]table]
  [('simple_expression')]
  [REJECT LIMIT integer|UNLIMITED]
{code}

Example:
{code:sql}
DELETE FROM t1
  LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1;

INSERT INTO t1 (a,b,c) SELECT a,b,c FROM t2
  LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1
{code}

{{LOG ERRORS}} is used for two purposes:
1. For actual logging of errors
2. To change transaction error handling behavior. By default, when a DML statement fails on some row (e.g. due to a {{CONSTRAINT}} check, the whole statement is rolled back, regardless of how many rows were processed successfully before the error was detected. The {{REJECT LIMIT}} clause specifies the maximum number of errors before the statement fails.

h2. Example:
{code:sql}
DROP TABLE err$_t1;
BEGIN
  DBMS_ERRLOG.create_error_log (dml_table_name => 't1');
END;
/
DROP TABLE t1;
CREATE TABLE t1 (a INT PRIMARY KEY);
INSERT INTO t1 VALUES (10);
{code}

Now I insert a row which would normally raise a "err_dup_key" exception:
{code:sql}
INSERT INTO t1 VALUES (10) LOG ERRORS REJECT LIMIT 1;
{code}
{noformat}
0 rows created.
{noformat}

Notice, no exceptions were raised, and a recod in {{err$_t1}} was created instead:
{code:sql}
SELECT ORA_ERR_NUMBER$,ORA_ERR_MESG$ FROM err$_t1;
{code}
{noformat}
ORA_ERR_NUMBER$
---------------
ORA_ERR_MESG$
--------------------------------------------------------------------------------
1
ORA-00001: unique constraint (SYSTEM.SYS_C0071433) violated
{noformat}

Alexander Barkov made changes - 2016-11-15 12:45

Description

When running in {{sql_mode=ORACLE}}, MariaDB will understand an optional {{LOG ERRORS}} clause with this syntax:
{code:sql}
LOG ERRORS
  [INTO [schema.]table]
  [('simple_expression')]
  [REJECT LIMIT integer|UNLIMITED]
{code}

Example:
{code:sql}
DELETE FROM t1
  LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1;

INSERT INTO t1 (a,b,c) SELECT a,b,c FROM t2
  LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1
{code}

{{LOG ERRORS}} is used for two purposes:
1. For actual logging of errors
2. To change transaction error handling behavior. By default, when a DML statement fails on some row (e.g. due to a {{CONSTRAINT}} check, the whole statement is rolled back, regardless of how many rows were processed successfully before the error was detected. The {{REJECT LIMIT}} clause specifies the maximum number of errors before the statement fails.

h2. Example:
{code:sql}
DROP TABLE err$_t1;
BEGIN
  DBMS_ERRLOG.create_error_log (dml_table_name => 't1');
END;
/
DROP TABLE t1;
CREATE TABLE t1 (a INT PRIMARY KEY);
INSERT INTO t1 VALUES (10);
{code}

Now I insert a row which would normally raise a "err_dup_key" exception:
{code:sql}
INSERT INTO t1 VALUES (10) LOG ERRORS REJECT LIMIT 1;
{code}
{noformat}
0 rows created.
{noformat}

Notice, no exceptions were raised, and a recod in {{err$_t1}} was created instead:
{code:sql}
SELECT ORA_ERR_NUMBER$,ORA_ERR_MESG$ FROM err$_t1;
{code}
{noformat}
ORA_ERR_NUMBER$
---------------
ORA_ERR_MESG$
--------------------------------------------------------------------------------
1
ORA-00001: unique constraint (SYSTEM.SYS_C0071433) violated
{noformat}

When running in {{sql_mode=ORACLE}}, MariaDB will understand an optional {{LOG ERRORS}} clause with this syntax:
{code:sql}
LOG ERRORS
  [INTO [schema.]table]
  [('simple_expression')]
  [REJECT LIMIT integer|UNLIMITED]
{code}

Example:
{code:sql}
DELETE FROM t1
  LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1;

INSERT INTO t1 (a,b,c) SELECT a,b,c FROM t2
  LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1
{code}

{{LOG ERRORS}} is used for two purposes:
1. For actual logging of errors
2. To change transaction error handling behavior. By default, when a DML statement fails on some row (e.g. due to a {{CONSTRAINT}} check, the whole statement is rolled back, regardless of how many rows were processed successfully before the error was detected. The {{REJECT LIMIT}} clause specifies the maximum number of errors before the statement fails.

If the {{INTO}} clause is omitted, then logging is done to the table {{ERR$_table}}, where {{table}} is the name of the subject table truncated to 25 characters.

h2. Example:
{code:sql}
DROP TABLE err$_t1;
BEGIN
  DBMS_ERRLOG.create_error_log (dml_table_name => 't1');
END;
/
DROP TABLE t1;
CREATE TABLE t1 (a INT PRIMARY KEY);
INSERT INTO t1 VALUES (10);
{code}

Now I insert a row which would normally raise a "err_dup_key" exception:
{code:sql}
INSERT INTO t1 VALUES (10) LOG ERRORS REJECT LIMIT 1;
{code}
{noformat}
0 rows created.
{noformat}

Notice, no exceptions were raised, and a recod in {{err$_t1}} was created instead:
{code:sql}
SELECT ORA_ERR_NUMBER$,ORA_ERR_MESG$ FROM err$_t1;
{code}
{noformat}
ORA_ERR_NUMBER$
---------------
ORA_ERR_MESG$
--------------------------------------------------------------------------------
1
ORA-00001: unique constraint (SYSTEM.SYS_C0071433) violated
{noformat}

Alexander Barkov made changes - 2016-11-15 12:46

Description

When running in {{sql_mode=ORACLE}}, MariaDB will understand an optional {{LOG ERRORS}} clause with this syntax:
{code:sql}
LOG ERRORS
  [INTO [schema.]table]
  [('simple_expression')]
  [REJECT LIMIT integer|UNLIMITED]
{code}

Example:
{code:sql}
DELETE FROM t1
  LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1;

INSERT INTO t1 (a,b,c) SELECT a,b,c FROM t2
  LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1
{code}

{{LOG ERRORS}} is used for two purposes:
1. For actual logging of errors
2. To change transaction error handling behavior. By default, when a DML statement fails on some row (e.g. due to a {{CONSTRAINT}} check, the whole statement is rolled back, regardless of how many rows were processed successfully before the error was detected. The {{REJECT LIMIT}} clause specifies the maximum number of errors before the statement fails.

If the {{INTO}} clause is omitted, then logging is done to the table {{ERR$_table}}, where {{table}} is the name of the subject table truncated to 25 characters.

h2. Example:
{code:sql}
DROP TABLE err$_t1;
BEGIN
  DBMS_ERRLOG.create_error_log (dml_table_name => 't1');
END;
/
DROP TABLE t1;
CREATE TABLE t1 (a INT PRIMARY KEY);
INSERT INTO t1 VALUES (10);
{code}

Now I insert a row which would normally raise a "err_dup_key" exception:
{code:sql}
INSERT INTO t1 VALUES (10) LOG ERRORS REJECT LIMIT 1;
{code}
{noformat}
0 rows created.
{noformat}

Notice, no exceptions were raised, and a recod in {{err$_t1}} was created instead:
{code:sql}
SELECT ORA_ERR_NUMBER$,ORA_ERR_MESG$ FROM err$_t1;
{code}
{noformat}
ORA_ERR_NUMBER$
---------------
ORA_ERR_MESG$
--------------------------------------------------------------------------------
1
ORA-00001: unique constraint (SYSTEM.SYS_C0071433) violated
{noformat}

When running in {{sql_mode=ORACLE}}, MariaDB will understand an optional {{LOG ERRORS}} clause with this syntax:
{code:sql}
LOG ERRORS
  [INTO [schema.]table]
  [('simple_expression')]
  [REJECT LIMIT integer|UNLIMITED]
{code}

Example:
{code:sql}
DELETE FROM t1
  LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1;

INSERT INTO t1 (a,b,c) SELECT a,b,c FROM t2
  LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1
{code}

{{LOG ERRORS}} is used for two purposes:
1. For actual logging of errors
2. To change transaction error handling behavior. By default, when a DML statement fails on some row (e.g. due to a {{CONSTRAINT}} check, the whole statement is rolled back, regardless of how many rows were processed successfully before the error was detected. The {{REJECT LIMIT}} clause specifies the maximum number of errors before the statement fails.

If the {{INTO}} clause is omitted, then logging is done to the table {{ERR$_table}}, where {{table}} is the name of the subject table truncated to 25 characters.

The log table must be created, either manually, or using a special stored procedure {{DBMS_ERRLOG.create_error_log}}.

h2. Example:
{code:sql}
DROP TABLE err$_t1;
BEGIN
  DBMS_ERRLOG.create_error_log (dml_table_name => 't1');
END;
/
DROP TABLE t1;
CREATE TABLE t1 (a INT PRIMARY KEY);
INSERT INTO t1 VALUES (10);
{code}

Now I insert a row which would normally raise a "err_dup_key" exception:
{code:sql}
INSERT INTO t1 VALUES (10) LOG ERRORS REJECT LIMIT 1;
{code}
{noformat}
0 rows created.
{noformat}

Notice, no exceptions were raised, and a recod in {{err$_t1}} was created instead:
{code:sql}
SELECT ORA_ERR_NUMBER$,ORA_ERR_MESG$ FROM err$_t1;
{code}
{noformat}
ORA_ERR_NUMBER$
---------------
ORA_ERR_MESG$
--------------------------------------------------------------------------------
1
ORA-00001: unique constraint (SYSTEM.SYS_C0071433) violated
{noformat}

Alexander Barkov made changes - 2016-11-15 12:47

Description

When running in {{sql_mode=ORACLE}}, MariaDB will understand an optional {{LOG ERRORS}} clause with this syntax:
{code:sql}
LOG ERRORS
  [INTO [schema.]table]
  [('simple_expression')]
  [REJECT LIMIT integer|UNLIMITED]
{code}

Example:
{code:sql}
DELETE FROM t1
  LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1;

INSERT INTO t1 (a,b,c) SELECT a,b,c FROM t2
  LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1
{code}

{{LOG ERRORS}} is used for two purposes:
1. For actual logging of errors
2. To change transaction error handling behavior. By default, when a DML statement fails on some row (e.g. due to a {{CONSTRAINT}} check, the whole statement is rolled back, regardless of how many rows were processed successfully before the error was detected. The {{REJECT LIMIT}} clause specifies the maximum number of errors before the statement fails.

If the {{INTO}} clause is omitted, then logging is done to the table {{ERR$_table}}, where {{table}} is the name of the subject table truncated to 25 characters.

The log table must be created, either manually, or using a special stored procedure {{DBMS_ERRLOG.create_error_log}}.

h2. Example:
{code:sql}
DROP TABLE err$_t1;
BEGIN
  DBMS_ERRLOG.create_error_log (dml_table_name => 't1');
END;
/
DROP TABLE t1;
CREATE TABLE t1 (a INT PRIMARY KEY);
INSERT INTO t1 VALUES (10);
{code}

Now I insert a row which would normally raise a "err_dup_key" exception:
{code:sql}
INSERT INTO t1 VALUES (10) LOG ERRORS REJECT LIMIT 1;
{code}
{noformat}
0 rows created.
{noformat}

Notice, no exceptions were raised, and a recod in {{err$_t1}} was created instead:
{code:sql}
SELECT ORA_ERR_NUMBER$,ORA_ERR_MESG$ FROM err$_t1;
{code}
{noformat}
ORA_ERR_NUMBER$
---------------
ORA_ERR_MESG$
--------------------------------------------------------------------------------
1
ORA-00001: unique constraint (SYSTEM.SYS_C0071433) violated
{noformat}

When running in {{sql_mode=ORACLE}}, MariaDB will understand an optional {{LOG ERRORS}} clause with this syntax:
{code:sql}
LOG ERRORS
  [INTO [schema.]table]
  [('simple_expression')]
  [REJECT LIMIT integer|UNLIMITED]
{code}

Example:
{code:sql}
DELETE FROM t1
  LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1;

INSERT INTO t1 (a,b,c) SELECT a,b,c FROM t2
  LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1
{code}

{{LOG ERRORS}} is used for two purposes:
1. For actual logging of errors
2. To change transaction error handling behavior. By default, when a DML statement fails on some row (e.g. due to a {{CONSTRAINT}} check, the whole statement is rolled back, regardless of how many rows were processed successfully before the error was detected. The {{REJECT LIMIT}} clause specifies the maximum number of errors before the statement fails.

If the {{INTO}} clause is omitted, then logging is done to the table {{ERR$_table}}, where {{table}} is the name of the subject table truncated to 25 characters.

The log table must be created, either manually, or using a special stored procedure {{DBMS_ERRLOG.create_error_log}}.

If the {{REJECT}} clause is omitted, then {{LIMIT}} is set to {{0}} by default, which means the traditional transaction behavior, i.e. rollback on errors.

h2. Example:
{code:sql}
DROP TABLE err$_t1;
BEGIN
  DBMS_ERRLOG.create_error_log (dml_table_name => 't1');
END;
/
DROP TABLE t1;
CREATE TABLE t1 (a INT PRIMARY KEY);
INSERT INTO t1 VALUES (10);
{code}

Now I insert a row which would normally raise a "err_dup_key" exception:
{code:sql}
INSERT INTO t1 VALUES (10) LOG ERRORS REJECT LIMIT 1;
{code}
{noformat}
0 rows created.
{noformat}

Notice, no exceptions were raised, and a recod in {{err$_t1}} was created instead:
{code:sql}
SELECT ORA_ERR_NUMBER$,ORA_ERR_MESG$ FROM err$_t1;
{code}
{noformat}
ORA_ERR_NUMBER$
---------------
ORA_ERR_MESG$
--------------------------------------------------------------------------------
1
ORA-00001: unique constraint (SYSTEM.SYS_C0071433) violated
{noformat}

Alexander Barkov made changes - 2016-11-15 12:58

Description

When running in {{sql_mode=ORACLE}}, MariaDB will understand an optional {{LOG ERRORS}} clause with this syntax:
{code:sql}
LOG ERRORS
  [INTO [schema.]table]
  [('simple_expression')]
  [REJECT LIMIT integer|UNLIMITED]
{code}

Example:
{code:sql}
DELETE FROM t1
  LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1;

INSERT INTO t1 (a,b,c) SELECT a,b,c FROM t2
  LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1
{code}

{{LOG ERRORS}} is used for two purposes:
1. For actual logging of errors
2. To change transaction error handling behavior. By default, when a DML statement fails on some row (e.g. due to a {{CONSTRAINT}} check, the whole statement is rolled back, regardless of how many rows were processed successfully before the error was detected. The {{REJECT LIMIT}} clause specifies the maximum number of errors before the statement fails.

If the {{INTO}} clause is omitted, then logging is done to the table {{ERR$_table}}, where {{table}} is the name of the subject table truncated to 25 characters.

The log table must be created, either manually, or using a special stored procedure {{DBMS_ERRLOG.create_error_log}}.

If the {{REJECT}} clause is omitted, then {{LIMIT}} is set to {{0}} by default, which means the traditional transaction behavior, i.e. rollback on errors.

h2. Example:
{code:sql}
DROP TABLE err$_t1;
BEGIN
  DBMS_ERRLOG.create_error_log (dml_table_name => 't1');
END;
/
DROP TABLE t1;
CREATE TABLE t1 (a INT PRIMARY KEY);
INSERT INTO t1 VALUES (10);
{code}

Now I insert a row which would normally raise a "err_dup_key" exception:
{code:sql}
INSERT INTO t1 VALUES (10) LOG ERRORS REJECT LIMIT 1;
{code}
{noformat}
0 rows created.
{noformat}

Notice, no exceptions were raised, and a recod in {{err$_t1}} was created instead:
{code:sql}
SELECT ORA_ERR_NUMBER$,ORA_ERR_MESG$ FROM err$_t1;
{code}
{noformat}
ORA_ERR_NUMBER$
---------------
ORA_ERR_MESG$
--------------------------------------------------------------------------------
1
ORA-00001: unique constraint (SYSTEM.SYS_C0071433) violated
{noformat}

When running in {{sql_mode=ORACLE}}, MariaDB will understand an optional {{LOG ERRORS}} clause with this syntax:
{code:sql}
LOG ERRORS
  [INTO [schema.]table]
  [('simple_expression')]
  [REJECT LIMIT integer|UNLIMITED]
{code}

Example:
{code:sql}
DELETE FROM t1
  LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1;

INSERT INTO t1 (a,b,c) SELECT a,b,c FROM t2
  LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1
{code}

{{LOG ERRORS}} is used for two purposes:
1. For actual logging of errors
2. To change transaction error handling behavior. By default, when a DML statement fails on some row (e.g. due to a {{CONSTRAINT}} check, the whole statement is rolled back, regardless of how many rows were processed successfully before the error was detected. The {{REJECT LIMIT}} clause specifies the maximum number of errors before the statement fails.

If the {{INTO}} clause is omitted, then logging is done to the table {{ERR$_table}}, where {{table}} is the name of the subject table truncated to 25 characters.

The log table must be created, either manually, or using a special stored procedure {{DBMS_ERRLOG.create_error_log}}.

If the {{REJECT}} clause is omitted, then {{LIMIT}} is set to {{0}} by default, which means the traditional transaction behavior, i.e. rollback on errors.

h2. Example:
{code:sql}
DROP TABLE err$_t1;
BEGIN
  DBMS_ERRLOG.create_error_log (dml_table_name => 't1');
END;
/
DROP TABLE t1;
CREATE TABLE t1 (a INT PRIMARY KEY);
INSERT INTO t1 VALUES (10);
{code}

Now I insert a row which would normally raise a "err_dup_key" exception:
{code:sql}
INSERT INTO t1 VALUES (10) LOG ERRORS REJECT LIMIT 1;
{code}
{noformat}
0 rows created.
{noformat}

Notice, no exceptions were raised, and a recod in {{err$_t1}} was created instead:
{code:sql}
SELECT ORA_ERR_NUMBER$,ORA_ERR_MESG$ FROM err$_t1;
{code}
{noformat}
ORA_ERR_NUMBER$
---------------
ORA_ERR_MESG$
--------------------------------------------------------------------------------
1
ORA-00001: unique constraint (SYSTEM.SYS_C0071433) violated
{noformat}

Let's try {{LOG ERRORS}} with no {{REJECT LIMIT}} clause.
{code:sql}
DELETE FROM err$_t1;
INSERT INTO t1 VALUES (10) LOG ERRORS;
{code}
{noformat}
ERROR at line 1:
ORA-00001: unique constraint (SYSTEM.SYS_C0071433) violated
{noformat}
{code:sql}
SELECT ORA_ERR_NUMBER$,ORA_ERR_MESG$ FROM err$_t1;
{code}
{noformat}
ORA_ERR_NUMBER$
---------------
ORA_ERR_MESG$
--------------------------------------------------------------------------------
1
ORA-00001: unique constraint (SYSTEM.SYS_C0071433) violated
{noformat}

Alexander Barkov made changes - 2016-11-15 12:59

Description

When running in {{sql_mode=ORACLE}}, MariaDB will understand an optional {{LOG ERRORS}} clause with this syntax:
{code:sql}
LOG ERRORS
  [INTO [schema.]table]
  [('simple_expression')]
  [REJECT LIMIT integer|UNLIMITED]
{code}

Example:
{code:sql}
DELETE FROM t1
  LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1;

INSERT INTO t1 (a,b,c) SELECT a,b,c FROM t2
  LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1
{code}

{{LOG ERRORS}} is used for two purposes:
1. For actual logging of errors
2. To change transaction error handling behavior. By default, when a DML statement fails on some row (e.g. due to a {{CONSTRAINT}} check, the whole statement is rolled back, regardless of how many rows were processed successfully before the error was detected. The {{REJECT LIMIT}} clause specifies the maximum number of errors before the statement fails.

If the {{INTO}} clause is omitted, then logging is done to the table {{ERR$_table}}, where {{table}} is the name of the subject table truncated to 25 characters.

The log table must be created, either manually, or using a special stored procedure {{DBMS_ERRLOG.create_error_log}}.

If the {{REJECT}} clause is omitted, then {{LIMIT}} is set to {{0}} by default, which means the traditional transaction behavior, i.e. rollback on errors.

h2. Example:
{code:sql}
DROP TABLE err$_t1;
BEGIN
  DBMS_ERRLOG.create_error_log (dml_table_name => 't1');
END;
/
DROP TABLE t1;
CREATE TABLE t1 (a INT PRIMARY KEY);
INSERT INTO t1 VALUES (10);
{code}

Now I insert a row which would normally raise a "err_dup_key" exception:
{code:sql}
INSERT INTO t1 VALUES (10) LOG ERRORS REJECT LIMIT 1;
{code}
{noformat}
0 rows created.
{noformat}

Notice, no exceptions were raised, and a recod in {{err$_t1}} was created instead:
{code:sql}
SELECT ORA_ERR_NUMBER$,ORA_ERR_MESG$ FROM err$_t1;
{code}
{noformat}
ORA_ERR_NUMBER$
---------------
ORA_ERR_MESG$
--------------------------------------------------------------------------------
1
ORA-00001: unique constraint (SYSTEM.SYS_C0071433) violated
{noformat}

Let's try {{LOG ERRORS}} with no {{REJECT LIMIT}} clause.
{code:sql}
DELETE FROM err$_t1;
INSERT INTO t1 VALUES (10) LOG ERRORS;
{code}
{noformat}
ERROR at line 1:
ORA-00001: unique constraint (SYSTEM.SYS_C0071433) violated
{noformat}
{code:sql}
SELECT ORA_ERR_NUMBER$,ORA_ERR_MESG$ FROM err$_t1;
{code}
{noformat}
ORA_ERR_NUMBER$
---------------
ORA_ERR_MESG$
--------------------------------------------------------------------------------
1
ORA-00001: unique constraint (SYSTEM.SYS_C0071433) violated
{noformat}

When running in {{sql_mode=ORACLE}}, MariaDB will understand an optional {{LOG ERRORS}} clause with this syntax:
{code:sql}
LOG ERRORS
  [INTO [schema.]table]
  [('simple_expression')]
  [REJECT LIMIT integer|UNLIMITED]
{code}

Example:
{code:sql}
DELETE FROM t1
  LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1;

INSERT INTO t1 (a,b,c) SELECT a,b,c FROM t2
  LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1
{code}

{{LOG ERRORS}} is used for two purposes:
1. For actual logging of errors
2. To change transaction error handling behavior. By default, when a DML statement fails on some row (e.g. due to a {{CONSTRAINT}} check, the whole statement is rolled back, regardless of how many rows were processed successfully before the error was detected. The {{REJECT LIMIT}} clause specifies the maximum number of errors before the statement fails.

If the {{INTO}} clause is omitted, then logging is done to the table {{ERR$_table}}, where {{table}} is the name of the subject table truncated to 25 characters.

The log table must be created, either manually, or using a special stored procedure {{DBMS_ERRLOG.create_error_log}}.

If the {{REJECT}} clause is omitted, then {{LIMIT}} is set to {{0}} by default, which means the traditional transaction behavior, i.e. rollback on errors.

h2. Example:
{code:sql}
DROP TABLE err$_t1;
BEGIN
  DBMS_ERRLOG.create_error_log (dml_table_name => 't1');
END;
/
DROP TABLE t1;
CREATE TABLE t1 (a INT PRIMARY KEY);
INSERT INTO t1 VALUES (10);
{code}

Now I insert a row which would normally raise a "err_dup_key" exception:
{code:sql}
INSERT INTO t1 VALUES (10) LOG ERRORS REJECT LIMIT 1;
{code}
{noformat}
0 rows created.
{noformat}

Notice, no exceptions were raised, and a record in {{err$_t1}} was created instead:
{code:sql}
SELECT ORA_ERR_NUMBER$,ORA_ERR_MESG$ FROM err$_t1;
{code}
{noformat}
ORA_ERR_NUMBER$
---------------
ORA_ERR_MESG$
--------------------------------------------------------------------------------
1
ORA-00001: unique constraint (SYSTEM.SYS_C0071433) violated
{noformat}

Let's try {{LOG ERRORS}} with no {{REJECT LIMIT}} clause.
{code:sql}
DELETE FROM err$_t1;
INSERT INTO t1 VALUES (10) LOG ERRORS;
{code}
{noformat}
ERROR at line 1:
ORA-00001: unique constraint (SYSTEM.SYS_C0071433) violated
{noformat}
{code:sql}
SELECT ORA_ERR_NUMBER$,ORA_ERR_MESG$ FROM err$_t1;
{code}
{noformat}
ORA_ERR_NUMBER$
---------------
ORA_ERR_MESG$
--------------------------------------------------------------------------------
1
ORA-00001: unique constraint (SYSTEM.SYS_C0071433) violated
{noformat}

Alexander Barkov made changes - 2016-11-15 13:00

Description

When running in {{sql_mode=ORACLE}}, MariaDB will understand an optional {{LOG ERRORS}} clause with this syntax:
{code:sql}
LOG ERRORS
  [INTO [schema.]table]
  [('simple_expression')]
  [REJECT LIMIT integer|UNLIMITED]
{code}

Example:
{code:sql}
DELETE FROM t1
  LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1;

INSERT INTO t1 (a,b,c) SELECT a,b,c FROM t2
  LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1
{code}

{{LOG ERRORS}} is used for two purposes:
1. For actual logging of errors
2. To change transaction error handling behavior. By default, when a DML statement fails on some row (e.g. due to a {{CONSTRAINT}} check, the whole statement is rolled back, regardless of how many rows were processed successfully before the error was detected. The {{REJECT LIMIT}} clause specifies the maximum number of errors before the statement fails.

If the {{INTO}} clause is omitted, then logging is done to the table {{ERR$_table}}, where {{table}} is the name of the subject table truncated to 25 characters.

The log table must be created, either manually, or using a special stored procedure {{DBMS_ERRLOG.create_error_log}}.

If the {{REJECT}} clause is omitted, then {{LIMIT}} is set to {{0}} by default, which means the traditional transaction behavior, i.e. rollback on errors.

h2. Example:
{code:sql}
DROP TABLE err$_t1;
BEGIN
  DBMS_ERRLOG.create_error_log (dml_table_name => 't1');
END;
/
DROP TABLE t1;
CREATE TABLE t1 (a INT PRIMARY KEY);
INSERT INTO t1 VALUES (10);
{code}

Now I insert a row which would normally raise a "err_dup_key" exception:
{code:sql}
INSERT INTO t1 VALUES (10) LOG ERRORS REJECT LIMIT 1;
{code}
{noformat}
0 rows created.
{noformat}

Notice, no exceptions were raised, and a record in {{err$_t1}} was created instead:
{code:sql}
SELECT ORA_ERR_NUMBER$,ORA_ERR_MESG$ FROM err$_t1;
{code}
{noformat}
ORA_ERR_NUMBER$
---------------
ORA_ERR_MESG$
--------------------------------------------------------------------------------
1
ORA-00001: unique constraint (SYSTEM.SYS_C0071433) violated
{noformat}

Let's try {{LOG ERRORS}} with no {{REJECT LIMIT}} clause.
{code:sql}
DELETE FROM err$_t1;
INSERT INTO t1 VALUES (10) LOG ERRORS;
{code}
{noformat}
ERROR at line 1:
ORA-00001: unique constraint (SYSTEM.SYS_C0071433) violated
{noformat}
{code:sql}
SELECT ORA_ERR_NUMBER$,ORA_ERR_MESG$ FROM err$_t1;
{code}
{noformat}
ORA_ERR_NUMBER$
---------------
ORA_ERR_MESG$
--------------------------------------------------------------------------------
1
ORA-00001: unique constraint (SYSTEM.SYS_C0071433) violated
{noformat}

When running in {{sql_mode=ORACLE}}, MariaDB will understand an optional {{LOG ERRORS}} clause with this syntax:
{code:sql}
LOG ERRORS
  [INTO [schema.]table]
  [('simple_expression')]
  [REJECT LIMIT integer|UNLIMITED]
{code}

Example:
{code:sql}
DELETE FROM t1
  LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1;

INSERT INTO t1 (a,b,c) SELECT a,b,c FROM t2
  LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1
{code}

{{LOG ERRORS}} is used for two purposes:
1. For actual logging of errors
2. To change transaction error handling behavior. By default, when a DML statement fails on some row (e.g. due to a {{CONSTRAINT}} check, the whole statement is rolled back, regardless of how many rows were processed successfully before the error was detected. The {{REJECT LIMIT}} clause specifies the maximum number of errors before the statement fails.

If the {{INTO}} clause is omitted, then logging is done to the table {{ERR$_table}}, where {{table}} is the name of the subject table truncated to 25 characters.

The log table must be created, either manually, or using a special stored procedure {{DBMS_ERRLOG.create_error_log}}.

If the {{REJECT}} clause is omitted, then {{LIMIT}} is set to {{0}} by default, which means the traditional transaction behavior, i.e. rollback on errors.

h2. Example:
h3. Preparations
{code:sql}
DROP TABLE err$_t1;
BEGIN
  DBMS_ERRLOG.create_error_log (dml_table_name => 't1');
END;
/
DROP TABLE t1;
CREATE TABLE t1 (a INT PRIMARY KEY);
INSERT INTO t1 VALUES (10);
{code}

h3. Testing {{LOG ERRORS REJECT LIMIT 1}}
Now I insert a row which would normally raise a "err_dup_key" exception:
{code:sql}
INSERT INTO t1 VALUES (10) LOG ERRORS REJECT LIMIT 1;
{code}
{noformat}
0 rows created.
{noformat}

Notice, no exceptions were raised, and a record in {{err$_t1}} was created instead:
{code:sql}
SELECT ORA_ERR_NUMBER$,ORA_ERR_MESG$ FROM err$_t1;
{code}
{noformat}
ORA_ERR_NUMBER$
---------------
ORA_ERR_MESG$
--------------------------------------------------------------------------------
1
ORA-00001: unique constraint (SYSTEM.SYS_C0071433) violated
{noformat}

h3. Testing {{LOG ERRORS}} with no {{REJECT LIMIT}} clause.
{code:sql}
DELETE FROM err$_t1;
INSERT INTO t1 VALUES (10) LOG ERRORS;
{code}
{noformat}
ERROR at line 1:
ORA-00001: unique constraint (SYSTEM.SYS_C0071433) violated
{noformat}
{code:sql}
SELECT ORA_ERR_NUMBER$,ORA_ERR_MESG$ FROM err$_t1;
{code}
{noformat}
ORA_ERR_NUMBER$
---------------
ORA_ERR_MESG$
--------------------------------------------------------------------------------
1
ORA-00001: unique constraint (SYSTEM.SYS_C0071433) violated
{noformat}

Alexander Barkov made changes - 2016-11-15 13:03

Description

When running in {{sql_mode=ORACLE}}, MariaDB will understand an optional {{LOG ERRORS}} clause with this syntax:
{code:sql}
LOG ERRORS
  [INTO [schema.]table]
  [('simple_expression')]
  [REJECT LIMIT integer|UNLIMITED]
{code}

Example:
{code:sql}
DELETE FROM t1
  LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1;

INSERT INTO t1 (a,b,c) SELECT a,b,c FROM t2
  LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1
{code}

{{LOG ERRORS}} is used for two purposes:
1. For actual logging of errors
2. To change transaction error handling behavior. By default, when a DML statement fails on some row (e.g. due to a {{CONSTRAINT}} check, the whole statement is rolled back, regardless of how many rows were processed successfully before the error was detected. The {{REJECT LIMIT}} clause specifies the maximum number of errors before the statement fails.

If the {{INTO}} clause is omitted, then logging is done to the table {{ERR$_table}}, where {{table}} is the name of the subject table truncated to 25 characters.

The log table must be created, either manually, or using a special stored procedure {{DBMS_ERRLOG.create_error_log}}.

If the {{REJECT}} clause is omitted, then {{LIMIT}} is set to {{0}} by default, which means the traditional transaction behavior, i.e. rollback on errors.

h2. Example:
h3. Preparations
{code:sql}
DROP TABLE err$_t1;
BEGIN
  DBMS_ERRLOG.create_error_log (dml_table_name => 't1');
END;
/
DROP TABLE t1;
CREATE TABLE t1 (a INT PRIMARY KEY);
INSERT INTO t1 VALUES (10);
{code}

h3. Testing {{LOG ERRORS REJECT LIMIT 1}}
Now I insert a row which would normally raise a "err_dup_key" exception:
{code:sql}
INSERT INTO t1 VALUES (10) LOG ERRORS REJECT LIMIT 1;
{code}
{noformat}
0 rows created.
{noformat}

Notice, no exceptions were raised, and a record in {{err$_t1}} was created instead:
{code:sql}
SELECT ORA_ERR_NUMBER$,ORA_ERR_MESG$ FROM err$_t1;
{code}
{noformat}
ORA_ERR_NUMBER$
---------------
ORA_ERR_MESG$
--------------------------------------------------------------------------------
1
ORA-00001: unique constraint (SYSTEM.SYS_C0071433) violated
{noformat}

h3. Testing {{LOG ERRORS}} with no {{REJECT LIMIT}} clause.
{code:sql}
DELETE FROM err$_t1;
INSERT INTO t1 VALUES (10) LOG ERRORS;
{code}
{noformat}
ERROR at line 1:
ORA-00001: unique constraint (SYSTEM.SYS_C0071433) violated
{noformat}
{code:sql}
SELECT ORA_ERR_NUMBER$,ORA_ERR_MESG$ FROM err$_t1;
{code}
{noformat}
ORA_ERR_NUMBER$
---------------
ORA_ERR_MESG$
--------------------------------------------------------------------------------
1
ORA-00001: unique constraint (SYSTEM.SYS_C0071433) violated
{noformat}

When running in {{sql_mode=ORACLE}}, MariaDB will understand an optional {{LOG ERRORS}} clause with this syntax:
{code:sql}
LOG ERRORS
  [INTO [schema.]table]
  [('simple_expression')]
  [REJECT LIMIT integer|UNLIMITED]
{code}

Example:
{code:sql}
DELETE FROM t1
  LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1;

INSERT INTO t1 (a,b,c) SELECT a,b,c FROM t2
  LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1
{code}

{{LOG ERRORS}} is used for two purposes:
1. For actual logging of errors
2. To change transaction error handling behavior. By default, when a DML statement fails on some row (e.g. due to a {{CONSTRAINT}} check, the whole statement is rolled back, regardless of how many rows were processed successfully before the error was detected. The {{REJECT LIMIT}} clause specifies the maximum number of errors before the statement fails.

If the {{INTO}} clause is omitted, then logging is done to the table {{ERR$_table}}, where {{table}} is the name of the subject table truncated to 25 characters.

The log table must be created, either manually, or using a special stored procedure {{DBMS_ERRLOG.create_error_log}}.

If the {{REJECT}} clause is omitted, then {{LIMIT}} is set to {{0}} by default, which means the traditional transaction behavior, i.e. rollback on errors.

h1. Example:
h2. Preparations
{code:sql}
DROP TABLE err$_t1;
BEGIN
  DBMS_ERRLOG.create_error_log (dml_table_name => 't1');
END;
/
DROP TABLE t1;
CREATE TABLE t1 (a INT PRIMARY KEY);
INSERT INTO t1 VALUES (10);
{code}

h2. Testing {{LOG ERRORS REJECT LIMIT 1}}
Now I insert a row which would normally raise a "err_dup_key" exception:
{code:sql}
INSERT INTO t1 VALUES (10) LOG ERRORS REJECT LIMIT 1;
{code}
{noformat}
0 rows created.
{noformat}

Notice, no exceptions were raised, and a record in {{err$_t1}} was created instead:
{code:sql}
SELECT ORA_ERR_NUMBER$,ORA_ERR_MESG$ FROM err$_t1;
{code}
{noformat}
ORA_ERR_NUMBER$
---------------
ORA_ERR_MESG$
--------------------------------------------------------------------------------
1
ORA-00001: unique constraint (SYSTEM.SYS_C0071433) violated
{noformat}

h2. Testing {{LOG ERRORS}} with no {{REJECT LIMIT}} clause.
{code:sql}
DELETE FROM err$_t1;
INSERT INTO t1 VALUES (10) LOG ERRORS;
{code}
{noformat}
ERROR at line 1:
ORA-00001: unique constraint (SYSTEM.SYS_C0071433) violated
{noformat}
{code:sql}
SELECT ORA_ERR_NUMBER$,ORA_ERR_MESG$ FROM err$_t1;
{code}
{noformat}
ORA_ERR_NUMBER$
---------------
ORA_ERR_MESG$
--------------------------------------------------------------------------------
1
ORA-00001: unique constraint (SYSTEM.SYS_C0071433) violated
{noformat}

Alexander Barkov made changes - 2016-11-15 13:19

Description

When running in {{sql_mode=ORACLE}}, MariaDB will understand an optional {{LOG ERRORS}} clause with this syntax:
{code:sql}
LOG ERRORS
  [INTO [schema.]table]
  [('simple_expression')]
  [REJECT LIMIT integer|UNLIMITED]
{code}

Example:
{code:sql}
DELETE FROM t1
  LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1;

INSERT INTO t1 (a,b,c) SELECT a,b,c FROM t2
  LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1
{code}

{{LOG ERRORS}} is used for two purposes:
1. For actual logging of errors
2. To change transaction error handling behavior. By default, when a DML statement fails on some row (e.g. due to a {{CONSTRAINT}} check, the whole statement is rolled back, regardless of how many rows were processed successfully before the error was detected. The {{REJECT LIMIT}} clause specifies the maximum number of errors before the statement fails.

If the {{INTO}} clause is omitted, then logging is done to the table {{ERR$_table}}, where {{table}} is the name of the subject table truncated to 25 characters.

The log table must be created, either manually, or using a special stored procedure {{DBMS_ERRLOG.create_error_log}}.

If the {{REJECT}} clause is omitted, then {{LIMIT}} is set to {{0}} by default, which means the traditional transaction behavior, i.e. rollback on errors.

h1. Example:
h2. Preparations
{code:sql}
DROP TABLE err$_t1;
BEGIN
  DBMS_ERRLOG.create_error_log (dml_table_name => 't1');
END;
/
DROP TABLE t1;
CREATE TABLE t1 (a INT PRIMARY KEY);
INSERT INTO t1 VALUES (10);
{code}

h2. Testing {{LOG ERRORS REJECT LIMIT 1}}
Now I insert a row which would normally raise a "err_dup_key" exception:
{code:sql}
INSERT INTO t1 VALUES (10) LOG ERRORS REJECT LIMIT 1;
{code}
{noformat}
0 rows created.
{noformat}

Notice, no exceptions were raised, and a record in {{err$_t1}} was created instead:
{code:sql}
SELECT ORA_ERR_NUMBER$,ORA_ERR_MESG$ FROM err$_t1;
{code}
{noformat}
ORA_ERR_NUMBER$
---------------
ORA_ERR_MESG$
--------------------------------------------------------------------------------
1
ORA-00001: unique constraint (SYSTEM.SYS_C0071433) violated
{noformat}

h2. Testing {{LOG ERRORS}} with no {{REJECT LIMIT}} clause.
{code:sql}
DELETE FROM err$_t1;
INSERT INTO t1 VALUES (10) LOG ERRORS;
{code}
{noformat}
ERROR at line 1:
ORA-00001: unique constraint (SYSTEM.SYS_C0071433) violated
{noformat}
{code:sql}
SELECT ORA_ERR_NUMBER$,ORA_ERR_MESG$ FROM err$_t1;
{code}
{noformat}
ORA_ERR_NUMBER$
---------------
ORA_ERR_MESG$
--------------------------------------------------------------------------------
1
ORA-00001: unique constraint (SYSTEM.SYS_C0071433) violated
{noformat}

When running in {{sql_mode=ORACLE}}, MariaDB will understand an optional {{LOG ERRORS}} clause with this syntax:
{code:sql}
LOG ERRORS
  [INTO [schema.]table]
  [('simple_expression')]
  [REJECT LIMIT integer|UNLIMITED]
{code}

Example:
{code:sql}
DELETE FROM t1
  LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1;

INSERT INTO t1 (a,b,c) SELECT a,b,c FROM t2
  LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1
{code}

{{LOG ERRORS}} is used for two purposes:
1. For actual logging of errors
2. To change transaction error handling behavior. By default, when a DML statement fails on some row (e.g. due to a {{CONSTRAINT}} check, the whole statement is rolled back, regardless of how many rows were processed successfully before the error was detected. The {{REJECT LIMIT}} clause specifies the maximum number of errors before the statement fails.

If the {{INTO}} clause is omitted, then logging is done to the table {{ERR$_table}}, where {{table}} is the name of the subject table truncated to 25 characters.

The log table must be created, either manually, or using a special stored procedure {{DBMS_ERRLOG.create_error_log}}. The log table have the following mandatory columns:
{code:sql}
CREATE TABLE ERR$_T1
(
  ORA_ERR_NUMBER$ NUMBER,
  ORA_ERR_MESG$ VARCHAR2(2000),
  ORA_ERR_ROWID$ ROWID,
  ORA_ERR_OPTYP$ VARCHAR2(2),
  ORA_ERR_TAG$ VARCHAR2(2000)
);
{code}

If the {{REJECT}} clause is omitted, then {{LIMIT}} is set to {{0}} by default, which means the traditional transaction behavior, i.e. rollback on errors.

h1. Example:
h2. Preparations
{code:sql}
DROP TABLE err$_t1;
BEGIN
  DBMS_ERRLOG.create_error_log (dml_table_name => 't1');
END;
/
DROP TABLE t1;
CREATE TABLE t1 (a INT PRIMARY KEY);
INSERT INTO t1 VALUES (10);
{code}

h2. Testing {{LOG ERRORS REJECT LIMIT 1}}
Now I insert a row which would normally raise a "err_dup_key" exception:
{code:sql}
INSERT INTO t1 VALUES (10) LOG ERRORS REJECT LIMIT 1;
{code}
{noformat}
0 rows created.
{noformat}

Notice, no exceptions were raised, and a record in {{err$_t1}} was created instead:
{code:sql}
SELECT ORA_ERR_NUMBER$,ORA_ERR_MESG$ FROM err$_t1;
{code}
{noformat}
ORA_ERR_NUMBER$
---------------
ORA_ERR_MESG$
--------------------------------------------------------------------------------
1
ORA-00001: unique constraint (SYSTEM.SYS_C0071433) violated
{noformat}

h2. Testing {{LOG ERRORS}} with no {{REJECT LIMIT}} clause.
{code:sql}
DELETE FROM err$_t1;
INSERT INTO t1 VALUES (10) LOG ERRORS;
{code}
{noformat}
ERROR at line 1:
ORA-00001: unique constraint (SYSTEM.SYS_C0071433) violated
{noformat}
{code:sql}
SELECT ORA_ERR_NUMBER$,ORA_ERR_MESG$ FROM err$_t1;
{code}
{noformat}
ORA_ERR_NUMBER$
---------------
ORA_ERR_MESG$
--------------------------------------------------------------------------------
1
ORA-00001: unique constraint (SYSTEM.SYS_C0071433) violated
{noformat}

Alexander Barkov made changes - 2016-11-15 13:20

Assignee

Alexander Barkov [ bar ]

Alexander Barkov made changes - 2016-11-15 13:20

Status

Open [ 1 ]

In Progress [ 3 ]

Alvin Richards (Inactive) made changes - 2016-12-21 19:29

NRE Projects

NRE-307517

Alvin Richards (Inactive) made changes - 2016-12-21 19:40

Labels

Compatibility NRE-307517

Compatibility

Alexander Barkov made changes - 2017-02-07 14:11

Fix Version/s		10.3 [ 22126 ]
Fix Version/s	10.2 [ 14601 ]

Alexander Barkov made changes - 2017-02-09 12:39

Status

In Progress [ 3 ]

Stalled [ 10000 ]

Alvin Richards (Inactive) made changes - 2017-09-22 18:03

Parent

~~MDEV-10142~~ [ 56873 ]

MDEV-10764 [ 57940 ]

Sergei Golubchik made changes - 2018-02-14 10:23

Fix Version/s

10.3 [ 22126 ]

Ralf Gebhardt made changes - 2019-02-14 09:47

Support case ID

not-26307

Ralf Gebhardt made changes - 2019-02-14 10:00

NRE Projects

AC-2610/DEFERRED

Ralf Gebhardt made changes - 2021-05-11 08:38

Assignee

Alexander Barkov [ bar ]

Sergei Golubchik made changes - 2021-12-06 21:35

Workflow

MariaDB v3 [ 76695 ]

MariaDB v4 [ 143483 ]

MariaDB Server

sql_mode=ORACLE: LOG ERRORS clause in INSERT, DELETE, UPDATE

Details

Description

Example:

Preparations

Testing `LOG ERRORS REJECT LIMIT 1`

Testing `LOG ERRORS` with no `REJECT LIMIT` clause.

Attachments

Activity

People

Dates

Git Integration

MariaDB Server

Details

Description

Example:

Preparations

Testing LOG ERRORS REJECT LIMIT 1

Testing LOG ERRORS with no REJECT LIMIT clause.

Attachments

Activity

People

Dates

Git Integration

Testing `LOG ERRORS REJECT LIMIT 1`

Testing `LOG ERRORS` with no `REJECT LIMIT` clause.