Unique Constraint Failed

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

Unique Constraint Failed

Andrew Stewart
Hi,
                I am having problems with a database reporting Unique Constraint Failed when doing an insert.
Table consists of 3 columns:
ID, DateTime, data
Constraint is on ID,DateTime.

                DateTime trying to enter is current time.

                File is 200+ GB.

                I have tested this same to a 1.4TB file, but have updated my copy of SQLite source since that test.

Thanks,
Andrew Stewart
Software Designer

ARGUS CONTROLS
18445 53rd Avenue | Surrey, BC | V3S 7A4 | Canada
t +1.604.538.3531  ext. 108 | +1.800.667.2090 | f +1.604.538.4728
www.arguscontrols.com<http://www.arguscontrols.com/>

Notice: This electronic transmission contains confidential information, intended only for the person(s) named above. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or any other use of this email is strictly prohibited. If you have received this transmission by error, please notify us immediately by return email and destroy the original transmission immediately and all copies thereof.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Unique Constraint Failed

CedricCicada
Dumb question:  are you sure you're only inserting one record at a time?
Is it possible you're inserting records so fast that the timestamp is the
same for two of them?


On Fri, Sep 14, 2018 at 3:30 PM Andrew Stewart <[hidden email]>
wrote:

> Hi,
>                 I am having problems with a database reporting Unique
> Constraint Failed when doing an insert.
> Table consists of 3 columns:
> ID, DateTime, data
> Constraint is on ID,DateTime.
>
>                 DateTime trying to enter is current time.
>
>                 File is 200+ GB.
>
>                 I have tested this same to a 1.4TB file, but have updated
> my copy of SQLite source since that test.
>
> Thanks,
> Andrew Stewart
> Software Designer
>
> ARGUS CONTROLS
> 18445 53rd Avenue | Surrey, BC | V3S 7A4 | Canada
> t +1.604.538.3531  ext. 108 | +1.800.667.2090 | f +1.604.538.4728
> www.arguscontrols.com<http://www.arguscontrols.com/>
>
> Notice: This electronic transmission contains confidential information,
> intended only for the person(s) named above. If you are not the intended
> recipient, you are hereby notified that any disclosure, copying,
> distribution, or any other use of this email is strictly prohibited. If you
> have received this transmission by error, please notify us immediately by
> return email and destroy the original transmission immediately and all
> copies thereof.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Unique Constraint Failed

Simon Slavin-3
In reply to this post by Andrew Stewart
On 14 Sep 2018, at 8:29pm, Andrew Stewart <[hidden email]> wrote:

>                I am having problems with a database reporting Unique Constraint Failed when doing an insert.
> Table consists of 3 columns:
> ID, DateTime, data
> Constraint is on ID,DateTime.
>
>                DateTime trying to enter is current time.

We need to know what UNIQUE constraints are being applied.  They could appear in the table definition or in any index for the table.  What's the definition for the table itself ?

It seems most likely that there's a UNIQUE constraint on DateTime, and you are inserting rows so quickly that two of them have the same DateTime.

Simon.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Unique Constraint Failed

Andrew Stewart
In reply to this post by Andrew Stewart
Hi Simon,

                I am having a problem receiving the emails and therefore cannot do this as a reply to the message.  I am seeing your responses on the forum site.



                Below is the DDL for creating the table.  It should not be possible for 2 elements to have the same time/ID unless I am redoing the data.  This data comes live from a greenhouse control system.



CREATE TABLE dataStreamRecord (

fwParameterID INTEGER NOT NULL,

dateTime INTEGER NOT NULL,

data INTEGER NOT NULL,

UNIQUE (

fwParameterID,

dateTime

)

);




Andrew Stewart
Software Designer

ARGUS CONTROLS
18445 53rd Avenue | Surrey, BC | V3S 7A4 | Canada
t +1.604.538.3531  ext. 108 | +1.800.667.2090 | f +1.604.538.4728
www.arguscontrols.com<http://www.arguscontrols.com/>

Notice: This electronic transmission contains confidential information, intended only for the person(s) named above. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or any other use of this email is strictly prohibited. If you have received this transmission by error, please notify us immediately by return email and destroy the original transmission immediately and all copies thereof.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Unique Constraint Failed

Simon Slavin-3
On 14 Sep 2018, at 8:56pm, Andrew Stewart <[hidden email]> wrote:

> CREATE TABLE dataStreamRecord (
>     fwParameterID INTEGER NOT NULL,
>     dateTime INTEGER NOT NULL,
>     data INTEGER NOT NULL,
>     UNIQUE (
>         fwParameterID,
>         dateTime
>     )
> );

Well, there is only one UNIQUE constraint there.  Unless you have a UNIQUE INDEX on that table, that must be the constraint that's causing the problem.

As a diagnostic test to figure out what's wrong, you might want to remove the UNIQUE constraint on the TABLE and allow the data to be inserted into it.  You could then print the resulting data to a text (CSV ?) file, and use a spreadsheet program to look for duplicate values.

Or you might have a better idea about how to find your problem.

PS: Your method of reply is fine.  You can keep doing that.  All replies should go to the entire mailing list because we frequently improve each-other's solutions.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Unique Constraint Failed

Andrew Stewart
In reply to this post by Andrew Stewart
Hi all,
                I realize that this is the constraint that is failing.  The data is very large, encrypted and at a customer's site - not easy to use an external program to view or to transfer to my office.

                What I am wondering is if there are any limits on the Unique table that is maintained or if any of the code that is used for this has been changed in the last couple of years.

Andrew Stewart
Software Designer

ARGUS CONTROLS
18445 53rd Avenue | Surrey, BC | V3S 7A4 | Canada
t +1.604.538.3531  ext. 108 | +1.800.667.2090 | f +1.604.538.4728
www.arguscontrols.com<http://www.arguscontrols.com/>

Notice: This electronic transmission contains confidential information, intended only for the person(s) named above. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or any other use of this email is strictly prohibited. If you have received this transmission by error, please notify us immediately by return email and destroy the original transmission immediately and all copies thereof.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Unique Constraint Failed

Igor Korot
Hi,
On Fri, Sep 14, 2018 at 3:14 PM Andrew Stewart
<[hidden email]> wrote:
>
> Hi all,
>                 I realize that this is the constraint that is failing.  The data is very large, encrypted and at a customer's site - not easy to use an external program to view or to transfer to my office.
>
>                 What I am wondering is if there are any limits on the Unique table that is maintained or if any of the code that is used for this has been changed in the last couple of years.

Can you try and reproduce the problem locally?
Just create a table and try to continuously insert some data in it.

It would also be nice if your software had some logging capabilities -
that way you can turn it on and see what data is coming in and what is
being stored in the DB/table.

Basically there should be a way to reproduce a problem to simplify the
debugging on both yours and the SQLite ends.

Thank you.

>
> Andrew Stewart
> Software Designer
>
> ARGUS CONTROLS
> 18445 53rd Avenue | Surrey, BC | V3S 7A4 | Canada
> t +1.604.538.3531  ext. 108 | +1.800.667.2090 | f +1.604.538.4728
> www.arguscontrols.com<http://www.arguscontrols.com/>
>
> Notice: This electronic transmission contains confidential information, intended only for the person(s) named above. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or any other use of this email is strictly prohibited. If you have received this transmission by error, please notify us immediately by return email and destroy the original transmission immediately and all copies thereof.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Unique Constraint Failed

ward
In reply to this post by Simon Slavin-3
I'm just beginning to look at sqlite so this approach might not apply.
In instances where I had no control on the input stream and an
occasional
duplicate could occur I fed the input stream into a temp table then used
a
select from that temp with a count function and a group by ID, DateTime
where
count < 2. That will filter the dups or count > 1 to find the dups.

Lyle Ward

On 2018-09-14 16:05, Simon Slavin wrote:

> On 14 Sep 2018, at 8:56pm, Andrew Stewart <[hidden email]>
> wrote:
>
>> CREATE TABLE dataStreamRecord (
>>     fwParameterID INTEGER NOT NULL,
>>     dateTime INTEGER NOT NULL,
>>     data INTEGER NOT NULL,
>>     UNIQUE (
>>         fwParameterID,
>>         dateTime
>>     )
>> );
>
> Well, there is only one UNIQUE constraint there.  Unless you have a
> UNIQUE INDEX on that table, that must be the constraint that's causing
> the problem.
>
> As a diagnostic test to figure out what's wrong, you might want to
> remove the UNIQUE constraint on the TABLE and allow the data to be
> inserted into it.  You could then print the resulting data to a text
> (CSV ?) file, and use a spreadsheet program to look for duplicate
> values.
>
> Or you might have a better idea about how to find your problem.
>
> PS: Your method of reply is fine.  You can keep doing that.  All
> replies should go to the entire mailing list because we frequently
> improve each-other's solutions.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Unique Constraint Failed

Keith Medcalf
In reply to this post by Andrew Stewart

Change is not likely.  Putting a "UNIQUE" constraint is syntactic sugar for creating a unique index.  That is

CREATE TABLE dataStreamRecord
(
    fwParameterID INTEGER NOT NULL,
    dateTime INTEGER NOT NULL,
    data INTEGER NOT NULL,
    UNIQUE (fwParameterID, dateTime)
);

is merely an alternate way of saying:

CREATE TABLE dataStreamRecord
(
    fwParameterID INTEGER NOT NULL,
    dateTime INTEGER NOT NULL,
    data INTEGER NOT NULL
);
create unique index dataStreamRecord_autoindex_1 on dataStreamRecord(fwParameterID, dateTime);

in other words, the UNIQUE constraint is implemented as a unique index (as it is in just about every other database system).

If you are absolutely certain that the record being inserted is not a duplicate then perhaps the index is corrupt?  Checking this (pragma integrity_check), however, or rebuilding the index (the reindex command) will take a while on a big database though ...

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.


>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Andrew Stewart
>Sent: Friday, 14 September, 2018 14:14
>To: [hidden email]
>Subject: [sqlite] Unique Constraint Failed
>
>Hi all,
>                I realize that this is the constraint that is
>failing.  The data is very large, encrypted and at a customer's site
>- not easy to use an external program to view or to transfer to my
>office.
>
>                What I am wondering is if there are any limits on the
>Unique table that is maintained or if any of the code that is used
>for this has been changed in the last couple of years.
>
>Andrew Stewart
>Software Designer
>
>ARGUS CONTROLS
>18445 53rd Avenue | Surrey, BC | V3S 7A4 | Canada
>t +1.604.538.3531  ext. 108 | +1.800.667.2090 | f +1.604.538.4728
>www.arguscontrols.com<http://www.arguscontrols.com/>
>
>Notice: This electronic transmission contains confidential
>information, intended only for the person(s) named above. If you are
>not the intended recipient, you are hereby notified that any
>disclosure, copying, distribution, or any other use of this email is
>strictly prohibited. If you have received this transmission by error,
>please notify us immediately by return email and destroy the original
>transmission immediately and all copies thereof.
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users