[
https://issues.apache.org/jira/browse/DERBY-7099?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17286745#comment-17286745
]
Richard N. Hillegas commented on DERBY-7099:
--------------------------------------------
You appear to have a corrupted database. There are a lot of bugs associated
with table compression. See https://issues.apache.org/jira/browse/DERBY-5876.
Have you been compressing tables?
If you have lost confidence in the integrity of your database, you may want to
dump and reload it using one of the following techniques:
* SYSCS_UTIL.SYSCS_EXPORT_TABLE commands followed by TRUNCATE TABLE commands
followed by SYSCS_UTIL.SYSCS_IMPORT_TABLE commands. See the "Built-in system
procedures" topic in https://db.apache.org/derby/docs/10.15/ref/index.html
* Recreating an empty schema and then populating the tables using the
foreignViews optional tool described in
https://db.apache.org/derby/docs/10.15/tools/index.html
> DELETE skips a row matching the WHERE clause (single delete by PK)
> ------------------------------------------------------------------
>
> Key: DERBY-7099
> URL: https://issues.apache.org/jira/browse/DERBY-7099
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.14.2.0, 10.15.2.0
> Environment: Looks like environment-independent bug - reproduced in
> multiple environments (prod/test, win/linux, different Java and Derby
> versions).
> Reporter: Ondrej Bouda
> Priority: Critical
> Attachments: repro.zip
>
>
> 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...
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
