Issue Details (XML | Word | Printable)

Key: CORE-3340
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Pavel Zotov
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Error in autonomous transaction with empty exception handler: can insert duplicate values into PK/UK column (leads to unrestorable backup)

Created: 09/Feb/11 05:32 AM   Updated: 04/Jun/13 12:39 PM
Component/s: None
Affects Version/s: 3.0 Initial, 2.5.0
Fix Version/s: 2.5.1

Time Tracking:
Not Specified

Issue Links:
Relate

Planning Status: Unspecified


 Description  « Hide
Let the file 'AAA' contains the following script:

recreate table tmp(id int not null primary key using index tmp_id_pk);
commit;
set transaction no wait isolation level read committed;
set term ^;
execute block as
begin
  insert into tmp values(1);
  insert into tmp values(2);
  in autonomous transaction do begin
     insert into tmp values(1);
     when any do begin
        --exception; -- <<<<<<<<<<<<<< ATTENTION: **NO** handle exception here
     end
  end
end^
set term ;^
commit;
select id from tmp;
select count(*) from tmp;
commit;

Create NEW EMPTY database, connect to it via ISQL -n and then:

SQL> IN AAA;

Result:

          ID
============
           1
           2
           1


       COUNT
============
           3

So, we have duplicate in PK. The same result will be if PK replaced by UK.
--------------------------------------
Moreover,
1) GFIX -V -FULL will *NOT* alert that table TMP has some problem in it's PK;
2) gbak -b will create UNRECOERABLE .fbk.
If then we'll try gbak -c test.fbk test_new.fdb then following messages will fills the screen:
...
...
gbak: The unique index has duplicate values or NULLs.
gbak: Delete or Update duplicate values or NULLs, and activate index with
gbak: ALTER INDEX "TMP_ID_PK" ACTIVE;
gbak:Index "TMP_ID_PK" failed to activate because:
gbak: The unique index has duplicate values or NULLs.
gbak: Delete or Update duplicate values or NULLs, and activate index with
gbak: ALTER INDEX "TMP_ID_PK" ACTIVE;
gbak:Index "TMP_ID_PK" failed to activate because:
gbak: The unique index has duplicate values or NULLs.
gbak: Delete or Update duplicate values or NULLs, and activate index with
gbak: ALTER INDEX "TMP_ID_PK" ACTIVE;

... etc ... (these messages repeats infinitely)


 All   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Dmitry Yemanov made changes - 09/Feb/11 05:51 AM
Field Original Value New Value
Summary Error in autonom. transaction WITHOUT exception handling: can insert duplicate values in table with PK (UK) ==> unrecoverable backup. Error in autonomous transaction with empty exception handler: can insert duplicate values into PK/UK column (leads to unrestorable backup)
Dmitry Yemanov made changes - 09/Feb/11 06:35 AM
Assignee Dmitry Yemanov [ dimitr ]
Dmitry Yemanov made changes - 09/Feb/11 06:35 AM
Status Open [ 1 ] In Progress [ 3 ]
Dmitry Yemanov made changes - 09/Feb/11 08:36 AM
Status In Progress [ 3 ] Open [ 1 ]
Dmitry Yemanov made changes - 09/Feb/11 10:44 AM
Security Developers [ 10012 ]
Affects Version/s 3.0 Initial [ 10301 ]
Dmitry Yemanov made changes - 09/Feb/11 10:45 AM
Link This issue relate to CORE-3341 [ CORE-3341 ]
Dmitry Yemanov made changes - 09/Feb/11 10:49 AM
Status Open [ 1 ] Resolved [ 5 ]
Fix Version/s 2.5.1 [ 10333 ]
Fix Version/s 3.0 Alpha 1 [ 10331 ]
Resolution Fixed [ 1 ]
Pavel Cisar made changes - 24/Feb/11 11:29 AM
Link This issue is related to QA-455 [ QA-455 ]
Philippe Makowski made changes - 04/Jun/13 12:39 PM
Status Resolved [ 5 ] Closed [ 6 ]
Fix Version/s 3.0 Alpha 1 [ 10331 ]