Can insert DUPLICATE keys in UNIQUE index [CORE3610]

[firebird-automations]

Submitted by: @pavel-zotov

Relate to CORE4078
Is related to QA489

Server version:
WI-V2.5.1.26353 Firebird 2.5
---------------------------------------
SQL> create database "testz.fdb" pagesize 2048;
SQL> commit;
SQL> connect testz.fdb;
Database: testz.fdb
---------------------------------------
-- SESSION #⁠1:
recreate table t(id int not null, f01 int, constraint t_pk primary key(id), constraint t_unq unique(f01));
commit;
insert into t values(1, 1 );
insert into t values(2,null);
insert into t values(3,null);
commit;
update t set f01=null where id=1; -- <<<<<<<<<<<<< SET UNIQUE FIELD TO NULL <<<<<<<

-- SESSION #⁠2:
commit; set transaction read committed record_version no wait; update t set f01=1 where id=3;

-- SESSION #⁠1:
ROLLBACK;

-- SESSION #⁠2:
COMMIT; -- PASSED! NO MESSAGES ABOUT VIOLATION OF UNIQUE INDEX!

-- SESSION #⁠1:
SQL> select * from t;

      ID          F01

============ ============
1 1
2 <null>
3 1

-- SESSION #⁠2:
SQL> select * from t;

      ID          F01

============ ============
1 1
2 <null>
3 1

Commits: fb36296 2affc68

[firebird-automations]

Modified by: @hvlad

assignee: Vlad Khorsun [ hvlad ]

[firebird-automations]

Commented by: @hvlad

Transaction isolation level doesn't matters here

[firebird-automations]

Modified by: @hvlad

summary: Can insert DUPLICATE keys in UNIQUE index when using RECORD_VERSION => Can insert DUPLICATE keys in UNIQUE index

[firebird-automations]

Commented by: @pavel-zotov

> Transaction isolation level doesn't matters here

yes, you're right: only 'NO WAIT" clause leads to this effect.

[firebird-automations]

Commented by: @hvlad

The bug was introduced in FB 2.5 Beta 1 with fix for CORE1606, so other versions are not affected

[firebird-automations]

Modified by: @hvlad

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 3.0 Alpha 1 [ 10331 ]

Fix Version: 2.5.2 [ 10450 ]

[firebird-automations]

Commented by: Jesus Angel Garcia Zarco (cointec)

Is not an important bug to include the fix in 2.5.1 that is not out at this moment?

[firebird-automations]

Commented by: @dyemanov

It's too late, v2.5.1 is already signed for release.

[firebird-automations]

Commented by: Jesus Angel Garcia Zarco (cointec)

This error affects only when modifying a field value of a table with a unique index or can i insert duplicated values in a unique key index?

[firebird-automations]

Commented by: @pavel-zotov

> This error affects only when modifying a field value of a table with a unique index or can i insert duplicated values in a unique key index?

INSERT of dup. key in session #⁠2 also could be done:

SESSION #⁠1:
update t set f01=null;

SESSION #⁠2:
commit; set transaction no wait;
insert into t values(4,1);

SESSION #⁠1:
rollback;

SESSION #⁠2:
commit;
SQL> select * from t;

      ID          F01

============ ============
1 1
2 <null>
3 <null>
4 1

[firebird-automations]

Commented by: @pmakowski

if you want fb 2.5.1 you can download a snapshot build
they should be ready soon with this fix
ps I packaged 2.5.1 with this fix in Mageia, Fedora and EPEL (RHEL, Centos, Scientific Linux)

[firebird-automations]

Commented by: Jesus Angel Garcia Zarco (cointec)

I know the fix will be solved in next snapshot of 2.5.2, but it has been repeated in the forums not to use snapshot in production enviroments.

[firebird-automations]

Commented by: @pavel-zotov

Any bug can appear in any kind of build - no matter is this build "official release" or no. There is no code without errors - I mean not FB only but all the software industry.

Currently all development of FB engine is carried by only FOUR men (compare with Oracle or MS, please :)).
Obviously they can not test even 1% of all possible cases and so I think that any built of FB needs to be tested by us (as much as we can).

[firebird-automations]

Commented by: Jesus Angel Garcia Zarco (cointec)

May be, because my english is not my natural language, i have not explain well myself.

What i was trying to say is that if this is an important bug for production systems, and 2.5.1 is not out, and people is waiting 2.5.1 to upgrade from 1.X, 2.X, and may be 2.5.2 will be out in few months, and as Philippe says that some packages of 2.5.1 has this fix included, may be is better to delay a few days 2.5.1, and have it fixed. It was just a suggestion because i do not know how important is this bug for the production systems using firebird and if this can affects so much pepoble easily.

I have used snapshots in production systems, because that snapshots solved problems that i have, but i have read in forums people that only uses official releases, and as i said, is waiting to update/upgrade to 2.5.1.

On the oder side, i know that no system is perfect and i know and value the work is done, and i admire the work is done by FB developers.

[firebird-automations]

Commented by: @pavel-zotov

> if this is an important bug for production systems <...> may be is better to delay a few days 2.5.1

2.5.0, the OFFICIAL release of FB, has much more serious bugs!

I talk about probability of index corruption on high-load systems, when message about 'missing entries' during validation of database appears in firebird.log. This bug occures in my PRODUCTION many times in 2010 and we have lost some money due to this.

Second serious bug - CORE3137 (partial rollback). If I understand correct this bug was NO fixed in official 2.5.0 (only in 2.5.1).

Also I want mention about some good improvements that were implemented by Vlad & Dmitry in 2.5.1: ability to write into GTT inside RO transaction and some optimizer improvements. Without these improvement performance of my production would be much poor.

There is no chance to get some built without errors. The question is how to reproduce these errors, report about them here and how quick FB-developers can fix them.

[firebird-automations]

Modified by: Sean Leyne (seanleyne)

Link: This issue relate to CORE3675 [ CORE3675 ]

[firebird-automations]

Modified by: @dyemanov

Version: 2.5.1 [ 10333 ]

Version: 2.5.0 [ 10221 ]

Version: 3.0 Initial [ 10301 ]

[firebird-automations]

Modified by: @dyemanov

Link: This issue relate to CORE3675 [ CORE3675 ] =>

[firebird-automations]

Modified by: @dyemanov

Link: This issue relate to CORE4078 [ CORE4078 ]

[firebird-automations]

Modified by: @pcisar

Link: This issue is related to QA489 [ QA489 ]

[firebird-automations]

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: Done successfully

[firebird-automations]

Commented by: @pcisar

Test created.

[firebird-automations]

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]