Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Can insert DUPLICATE keys in UNIQUE index [CORE3610] #3964

Closed
firebird-automations opened this issue Sep 27, 2011 · 23 comments
Closed

Can insert DUPLICATE keys in UNIQUE index [CORE3610] #3964

firebird-automations opened this issue Sep 27, 2011 · 23 comments

Comments

@firebird-automations
Copy link
Collaborator

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
Copy link
Collaborator Author

Modified by: @hvlad

assignee: Vlad Khorsun [ hvlad ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

Transaction isolation level doesn't matters here

@firebird-automations
Copy link
Collaborator Author

Modified by: @hvlad

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

@firebird-automations
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

Commented by: @dyemanov

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

@firebird-automations
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

Modified by: Sean Leyne (seanleyne)

Link: This issue relate to CORE3675 [ CORE3675 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Version: 2.5.1 [ 10333 ]

Version: 2.5.0 [ 10221 ]

Version: 3.0 Initial [ 10301 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue relate to CORE4078 [ CORE4078 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Link: This issue is related to QA489 [ QA489 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

QA Status: Done successfully

@firebird-automations
Copy link
Collaborator Author

Commented by: @pcisar

Test created.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment