Issue Details (XML | Word | Printable)

Key: CORE-3610
Type: Bug Bug
Status: Resolved Resolved
Resolution: Fixed
Priority: Critical Critical
Assignee: Vlad Khorsun
Reporter: Pavel Zotov
Votes: 0
Watchers: 5
Operations

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

Can insert DUPLICATE keys in UNIQUE index

Created: 27/Sep/11 04:57 AM   Updated: 27/Mar/14 01:04 PM
Component/s: None
Affects Version/s: 3.0 Initial, 2.5.0, 2.5.1
Fix Version/s: 2.5.2, 3.0 Alpha 1

Time Tracking:
Not Specified

Issue Links:
Relate

Planning Status: Unspecified


 Description  « Hide
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


 All   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Vlad Khorsun added a comment - 29/Sep/11 07:44 AM
Transaction isolation level doesn't matters here

Pavel Zotov added a comment - 29/Sep/11 08:56 AM
> Transaction isolation level doesn't matters here

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

Vlad Khorsun added a comment - 29/Sep/11 12:45 PM
The bug was introduced in FB 2.5 Beta 1 with fix for CORE-1606, so other versions are not affected

Jesus Angel Garcia Zarco added a comment - 29/Sep/11 04:43 PM
Is not an important bug to include the fix in 2.5.1 that is not out at this moment?

Dmitry Yemanov added a comment - 29/Sep/11 04:54 PM
It's too late, v2.5.1 is already signed for release.

Jesus Angel Garcia Zarco added a comment - 29/Sep/11 06:24 PM
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?

Pavel Zotov added a comment - 29/Sep/11 11:30 PM
> 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


Philippe Makowski added a comment - 30/Sep/11 05:51 AM
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)

Jesus Angel Garcia Zarco added a comment - 30/Sep/11 07:17 AM
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.

Pavel Zotov added a comment - 30/Sep/11 09:03 AM
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).

Jesus Angel Garcia Zarco added a comment - 30/Sep/11 09:37 AM
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.

Pavel Zotov added a comment - 30/Sep/11 10:53 AM
> 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 - CORE-3137 (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.