This message was forwarded from developers-l...@monetdb.org. The MonetDB
mailing lists have moved to monetdb.org. Please subscribe to
developers-l...@monetdb.org, and unsubscribe from this list.
See: http://mail.monetdb.org/mailman/listinfo/developers-list
Send developers-list mailing list submissions to
developers-l...@monetdb.org
To subscribe or unsubscribe via the World Wide Web, visit
http://mail.monetdb.org/mailman/listinfo/developers-list
or, via email, send a message with subject or body 'help' to
developers-list-requ...@monetdb.org
You can reach the person managing the list at
developers-list-ow...@monetdb.org
When replying, please edit your Subject line so it is more specific
than "Re: Contents of developers-list digest..."
Today's Topics:
1. Unique key constraint violation violated (Tapomay Dey)
2. Re: Unique key constraint violation violated (Tapomay Dey)
----------------------------------------------------------------------
Message: 1
Date: Tue, 25 Dec 2012 08:04:00 -0800 (PST)
From: Tapomay Dey <tapo...@yahoo.com>
To: "developers-l...@monetdb.org" <developers-l...@monetdb.org>
Subject: Unique key constraint violation violated
Message-ID:
<1356451440.59778.yahoomail...@web126104.mail.ne1.yahoo.com>
Content-Type: text/plain; charset="iso-8859-1"
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
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
<http://mail.monetdb.org/pipermail/developers-list/attachments/20121225/107bc856/attachment.html>
------------------------------
Message: 2
Date: Tue, 25 Dec 2012 23:51:26 -0800 (PST)
From: Tapomay Dey <tapo...@yahoo.com>
To: "Communication channel for developers of the MonetDB suite."
<developers-l...@monetdb.org>
Subject: Re: Unique key constraint violation violated
Message-ID:
<1356508286.77991.yahoomail...@web126105.mail.ne1.yahoo.com>
Content-Type: text/plain; charset="iso-8859-1"
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 <tapo...@yahoo.com>
To: "developers-l...@monetdb.org" <developers-l...@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-l...@monetdb.org
http://mail.monetdb.org/mailman/listinfo/developers-list
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
<http://mail.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://mail.monetdb.org/pipermail/developers-list/attachments/20121225/4c1c1db9/attachment.jpg>
------------------------------
_______________________________________________
developers-list mailing list
developers-l...@monetdb.org
http://mail.monetdb.org/mailman/listinfo/developers-list
End of developers-list Digest, Vol 4, Issue 18
**********************************************
------------------------------------------------------------------------------
LogMeIn Rescue: Anywhere, Anytime Remote support for IT. Free Trial
Remotely access PCs and mobile devices and provide instant support
Improve your efficiency, and focus on delivering more value-add services
Discover what IT Professionals Know. Rescue delivers
http://p.sf.net/sfu/logmein_12329d2d
_______________________________________________
Monetdb-developers mailing list
Monetdb-developers@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/monetdb-developers
