Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-7099

DELETE skips a row matching the WHERE clause (single delete by PK)

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Open
    • Priority: Critical
    • Resolution: Unresolved
    • Affects Version/s: 10.14.2.0, 10.15.2.0
    • Fix Version/s: None
    • Component/s: SQL
    • Labels:
      None
    • Environment:
      Looks like environment-independent bug - reproduced in multiple environments (prod/test, win/linux, different Java and Derby versions).
    • Urgency:
      Normal
    • Issue & fix info:
      Repro attached
    • Bug behavior facts:
      Data corruption, Seen in production

      Description

      We came across a bug in Derby when a DELETE by primary key actually does not delete the row (although it exists).

      • We found the issue in a delete-reinsert use case when the reinsert was failing with a "duplicate key" error.
      • Note: The attached app to reproduce the issue follows this reinsert pattern.

      To reproduce the issue, a SELECT (by PK) is required before the DELETE (by PK).

      • SELECT => SELECT => DELETE => RE-INSERT => duplicate key error.
      • The DELETE works OK if no or just 1 SELECT is executed before.
      • The "failed" DELETE correctly reports "0 rows affected".
      • Retrying the DELETE makes the delete really happen.
      • The behavior is the same both for autocommit and manual commit mode.
      • The behavior is 100% reproducible in the attached demo app.
        However, it is not reproducible from DBeaver (using the same queries).

      Notes regarding the attached database + demo app to reproduce the issue:

      • The app takes 3 arguments: path to the database (string), how many times to execute the initial SELECT (int) and an optional "retry" string to retry the DELETE statement.
      • Use "test_app.bat" => there are several execution scenarios, uncomment the one you intend to test.
      • With 0-1 SELECTs, the demo app works without any issues.
      • With 2+ SELECTs, the demo app fails on "duplicate key" when trying to reinsert the deleted row. The app reports that the DELETE affected 0 rows.
      • In the "retry" mode, you can see that 2nd DELETE is successful (even for 3+ SELECTs).

      Further notes

      • The issue persists even after a successful delete-reinsert.
      • Check for corruption (SYSCS_CHECK_TABLE) indicates that the table is healthy.
      • Full rebuild of the affected table (SYSCS_COMPRESS_TABLE) resolves the issue but - there is no way to tell whether a tables needs rebuild to avoid this kind of issue...

        Attachments

        1. repro.zip
          8.38 MB
          Ondrej Bouda

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                obouda@gk-software.com Ondrej Bouda
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated: