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

Duplicate values in columns with a unique constraint [CORE5694] #5960

Closed
firebird-issue-importer opened this issue Jan 4, 2018 · 7 comments

Comments

@firebird-issue-importer
Copy link

firebird-issue-importer commented Jan 4, 2018

Submitted by: Damyan Ivanov (dam)

Attachments:
fb-dupl-uniq.fbk.gz

It is possible to get rows with duplicate values in columns with a unique constraint.

Sample database backup is in fb-dupl-uniq.fbk.gz

Notice the unique constraint on test(obj_id, prj_id) and the procedure add_obj_to_prj which inserts records in TEST ignoring constraint violation. That procedure is invoked by an after update/insert trigger of table OBJ.

To trigger the problem, run the following shell command:

(triggers updates in 5000 rows, in random order, in 6 parallel processes)

for j in $(seq 1 6); do ( ( for i in $(seq 1 5000); do echo $i; done | shuf | while read i; do printf "update obj set data='Object %d' where id='911%07d';\n commit;\n" $i $i; done ) | isql-fb localhost:/var/lib/firebird/3.0/data/test.fdb ) &; done

there may be some update conflicts, but that's irrelevant -- I see the problem even when there are no update conflicts.

The above may need to be run several times, or the process count to be increased to surpass the number of CPU cores.

Execute the following SQL to see if the problem is there:

select obj_id, prj_id, count(*) from test
group by 1,2
having count(*) > 1;

Commits: 66d9f43 0166b87

====== Test Details ======

Can not add such test to fpt-repo because it can be reproduced under heavy load and races only

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jan 4, 2018

Commented by: Damyan Ivanov (dam)

compressed backup demonstrating the problem

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jan 4, 2018

Modified by: Damyan Ivanov (dam)

Attachment: fb-dupl-uniq.fbk.gz [ 13190 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jan 5, 2018

Modified by: @hvlad

assignee: Vlad Khorsun [ hvlad ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jan 9, 2018

Modified by: @hvlad

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

resolution: Fixed [ 1 ]

Fix Version: 3.0.3 [ 10810 ]

Fix Version: 4.0 Beta 1 [ 10750 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jan 16, 2018

Modified by: @pavel-zotov

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

QA Status: No test => Deferred

Test Details: Sent letter to dimitr, 16.01.2018 09:24, wait for reply.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jan 16, 2018

Modified by: @pavel-zotov

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

QA Status: Deferred => Cannot be tested

Test Details: Sent letter to dimitr, 16.01.2018 09:24, wait for reply. => Can not add such test to fpt-repo because it can be reproduced under heavy load and races only

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jan 16, 2018

Modified by: @pavel-zotov

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

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

No branches or pull requests

2 participants