Unique key constraint violation violated

Tapomay Dey tapomay at yahoo.com
Wed Dec 26 08:51:26 CET 2012


Found a case where count* doesn't match the actual no. of rows.
Screenshot attached.
select ad_group_id, count(*) from ad_groups group by ad_group_id having count(ad_group_id)=1; gives 21500 rows.

select count* gives 21400.
Out of the two DBs that these queries are running on problem seems to occur on exactly one of them everytime.
Will try re-installing monetdb and report back.
Regards,
Tapomay.


________________________________
 From: Tapomay Dey <tapomay at yahoo.com>
To: "developers-list at monetdb.org" <developers-list at monetdb.org> 
Sent: Tuesday, December 25, 2012 9:34 PM
Subject: Unique key constraint violation violated
 

Is this a known issue that you are working on? If so please give me the bug id where I would know when its fixed.

The case is as follows:
I have a table with 32 columns.
It has a unique key on 3 of them.
I know that it's recommended that you do bulk inserts from csv for performance. But in my case I am running live ETLs for putting data into monetdb. These ETLs are generating sql insert scripts.(inserts/updates/ddls)
I use JDBC and c3p0 connection pooling for executing these sqls.
I have a failover mechanism where the scripts are executed on multiple DBs and I try to keep their states consistent.
One common consistency check would be a count*.
I am running the latest stable installation from repo on Ubuntu 10.04 64 bit.

Observation:
After running 9100 queries the count* on one DB was 9100 but in the other was 9200.
I have diagnostics in place to do a select count* after running a batch of 100 queries in a single JDBC transaction.
I observed that the count* was consistent until 9000 queries were executed. It failed at 9100.
I did a manual check to see if duplicate records exist with same unique key values and they did.
Following is the query I used for the same:
select ad_group_id, count(*) from ad_groups group by ad_group_id having count(ad_group_id)>1 and client_id=X and account_id=Y;

My constraint is as follows:  CONSTRAINT "ad_groups_client_id_account_id_ad_group_id_unique" UNIQUE ("client_id", "account_id", "ad_group_id")
I found exactly 100 tuples with group_by_count* = 2 and the ad_group_id(unique key) values for these exactly matched queries 8901 to 9000.
It means when I inserted 9001 to 9100 the previous batch went into the store again with it and somehow surpassed the unique key constraint check.
Please note that I am converting a batch of 100 insert queries into a single insert query and running this single bulk insert in a single JDBC transaction to reduce network traffic.

This same issue recurred at 21500 only this time it happened for 300 records with same unique key value being duplicated.
And their unique key values were exactly matching the ones in the last 3 batches
Ad_group_id s 21103-21202, 21203-21302, 21303-21402 existed twice. 21403-21502 existed exactly once.

REQUEST:
Can anyone tell me if I can enable query logs - something like what mysql has.
I will try re-installing monetdb and see if this problem goes away. :)

Merry Christmas and Warm Regards,
Tapomay
_______________________________________________
developers-list mailing list
developers-list at monetdb.org
http://mail.monetdb.org/mailman/listinfo/developers-list
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.monetdb.org/pipermail/developers-list/attachments/20121225/4c1c1db9/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: Screenshot from 2012-12-26 13:13:56 (copy).jpg
Type: image/jpeg
Size: 25690 bytes
Desc: not available
URL: <http://www.monetdb.org/pipermail/developers-list/attachments/20121225/4c1c1db9/attachment.jpg>
-------------- next part --------------
_______________________________________________
developers-list mailing list
developers-list at monetdb.org
http://mail.monetdb.org/mailman/listinfo/developers-list


More information about the developers-list mailing list