developers-list-request-d/
2012-12-26 08:35:52 UTC
This message was forwarded from developers-list-d/***@public.gmane.org The MonetDB
mailing lists have moved to monetdb.org. Please subscribe to
developers-list-d/***@public.gmane.org, and unsubscribe from this list.
See: http://mail.monetdb.org/mailman/listinfo/developers-list
Send developers-list mailing list submissions to
developers-list-d/***@public.gmane.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-request-d/***@public.gmane.org
You can reach the person managing the list at
developers-list-owner-d/***@public.gmane.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 <tapomay-/***@public.gmane.org>
To: "developers-list-d/***@public.gmane.org" <developers-list-d/***@public.gmane.org>
Subject: Unique key constraint violation violated
Message-ID:
<1356451440.59778.YahooMailNeo-UzGBI8gCnMUbWpotXP+qY5OW+***@public.gmane.org>
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 <tapomay-/***@public.gmane.org>
To: "Communication channel for developers of the MonetDB suite."
<developers-list-d/***@public.gmane.org>
Subject: Re: Unique key constraint violation violated
Message-ID:
<1356508286.77991.YahooMailNeo-UzGBI8gCnMXuQS8rMknbopOW+***@public.gmane.org>
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 <tapomay-/***@public.gmane.org>
To: "developers-list-d/***@public.gmane.org" <developers-list-d/***@public.gmane.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-d/***@public.gmane.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: <Loading Image...
>
------------------------------
_______________________________________________
developers-list mailing list
developers-list-d/***@public.gmane.org
http://mail.monetdb.org/mailman/listinfo/developers-list
End of developers-list Digest, Vol 4, Issue 18
**********************************************
mailing lists have moved to monetdb.org. Please subscribe to
developers-list-d/***@public.gmane.org, and unsubscribe from this list.
See: http://mail.monetdb.org/mailman/listinfo/developers-list
Send developers-list mailing list submissions to
developers-list-d/***@public.gmane.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-request-d/***@public.gmane.org
You can reach the person managing the list at
developers-list-owner-d/***@public.gmane.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 <tapomay-/***@public.gmane.org>
To: "developers-list-d/***@public.gmane.org" <developers-list-d/***@public.gmane.org>
Subject: Unique key constraint violation violated
Message-ID:
<1356451440.59778.YahooMailNeo-UzGBI8gCnMUbWpotXP+qY5OW+***@public.gmane.org>
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 <tapomay-/***@public.gmane.org>
To: "Communication channel for developers of the MonetDB suite."
<developers-list-d/***@public.gmane.org>
Subject: Re: Unique key constraint violation violated
Message-ID:
<1356508286.77991.YahooMailNeo-UzGBI8gCnMXuQS8rMknbopOW+***@public.gmane.org>
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 <tapomay-/***@public.gmane.org>
To: "developers-list-d/***@public.gmane.org" <developers-list-d/***@public.gmane.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-d/***@public.gmane.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: <Loading Image...
>------------------------------
_______________________________________________
developers-list mailing list
developers-list-d/***@public.gmane.org
http://mail.monetdb.org/mailman/listinfo/developers-list
End of developers-list Digest, Vol 4, Issue 18
**********************************************