Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-10764 PL/SQL parser - Phase 2
  3. MDEV-10589

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

    XMLWordPrintable

    Details

    • Type: Technical task
    • Status: Stalled (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: OTHER
    • Labels:

      Description

      When running in sql_mode=ORACLE, MariaDB will understand an optional LOG ERRORS clause with this syntax:

      LOG ERRORS
        [INTO [schema.]table]
        [('simple_expression')] 
        [REJECT LIMIT integer|UNLIMITED]
      

      Example:

      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
      

      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:

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

      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.

      Example:

      Preparations

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

      Testing LOG ERRORS REJECT LIMIT 1

      Now I insert a row which would normally raise a "err_dup_key" exception:

      INSERT INTO t1 VALUES (10) LOG ERRORS REJECT LIMIT 1;
      

      0 rows created.
      

      Notice, no exceptions were raised, and a record in err$_t1 was created instead:

      SELECT ORA_ERR_NUMBER$,ORA_ERR_MESG$ FROM err$_t1;
      

      ORA_ERR_NUMBER$
      ---------------
      ORA_ERR_MESG$
      --------------------------------------------------------------------------------
      	      1
      ORA-00001: unique constraint (SYSTEM.SYS_C0071433) violated
      

      Testing LOG ERRORS with no REJECT LIMIT clause.

      DELETE FROM err$_t1;
      INSERT INTO t1 VALUES (10) LOG ERRORS;
      

      ERROR at line 1:
      ORA-00001: unique constraint (SYSTEM.SYS_C0071433) violated
      

      SELECT ORA_ERR_NUMBER$,ORA_ERR_MESG$ FROM err$_t1;
      

      ORA_ERR_NUMBER$
      ---------------
      ORA_ERR_MESG$
      --------------------------------------------------------------------------------
      	      1
      ORA-00001: unique constraint (SYSTEM.SYS_C0071433) violated
      

        Attachments

          Activity

            People

            Assignee:
            bar Alexander Barkov
            Reporter:
            bar Alexander Barkov
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated: