Re: [sqlite] UNIQUE constraint fails when setting legacy_file_format=true

[Richard Hipp]

Addressed by https://www.sqlite.org/src/info/713caa382cf7ddef

On 5/1/19, Manuel Rigger  wrote:
> Hi everyone,
>
> I think that I found a bug that occurs when setting legacy_file_format to
> true and calling REINDEX, which then results in "Error: UNIQUE constraint
> failed: index 'index_0'" in the specific example below:
>
> CREATE TABLE test (c0, c1 TEXT);
> CREATE UNIQUE INDEX IF NOT EXISTS index_0 ON test(c1 == FALSE);
> CREATE INDEX IF NOT EXISTS index_1 ON test(c0 || FALSE) WHERE c1;
> INSERT OR IGNORE INTO test(c0, c1) VALUES ('a', TRUE);
> INSERT OR IGNORE INTO test(c0, c1) VALUES ('a', FALSE);
> PRAGMA legacy_file_format=true;
> REINDEX;
>
> I would not expect the UNIQUE constraint to fail, since there is only one
> value that equals to FALSE in c1:
>
> sqlite> SELECT * FROM test WHERE c1 == FALSE;
> a|0
>
> I could not reduce the example further. I originally found this issue with
> a slightly more complex example and VACUUM instead of REINDEX:
>
> CREATE TABLE test (c0, c1 TEXT);
> CREATE UNIQUE INDEX IF NOT EXISTS index_0 ON test(FALSE >= c1 DESC);
> CREATE UNIQUE INDEX IF NOT EXISTS index_1 ON test((c0 || FALSE) ASC) WHERE
> c1;
> INSERT OR IGNORE INTO test(c0, c1) VALUES ('', '1');
> INSERT OR IGNORE INTO test(c0, c1) VALUES ('', FALSE);
> SELECT * FROM test;
> PRAGMA legacy_file_format=true;
> VACUUM;
>
> Best,
> Manuel
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unique Constraint Failed

[Rob Richardson]

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 
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
>
> 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
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unique Constraint Failed

[Keith Medcalf]

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-
>boun...@mailinglists.sqlite.org] On Behalf Of Andrew Stewart
>Sent: Friday, 14 September, 2018 14:14
>To: sqlite-users@mailinglists.sqlite.org
>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
>
>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
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unique Constraint Failed

[ward]

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  
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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unique Constraint Failed

[Igor Korot]

Hi,
On Fri, Sep 14, 2018 at 3:14 PM Andrew Stewart
 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
>
> 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
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unique Constraint Failed

[Simon Slavin]

On 14 Sep 2018, at 8:56pm, Andrew Stewart  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unique Constraint Failed

[Simon Slavin]

On 14 Sep 2018, at 8:29pm, Andrew Stewart  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] unique constraint

[Mark Wagner]

Thanks for the responses.  Just a brain error.  Not sure what I was
thinking :)

On Tue, May 15, 2018 at 6:55 PM R Smith  wrote:

>
> On 2018/05/16 1:25 AM, Mark Wagner wrote:
> > OK, at the risk of exposing my lack of edification...
>
> Sometimes you pay the price for taking a risk. :)
>
> > create table t (k integer primary key, s text UNIQUE);
>
> Did you by any chance assume the "UNIQUE" constraint to cover both the
> fields k and s? It applies of course only to s here. If you wanted it to
> apply to both, the schema should read:
> create table t(k integer primary key, s text, unique(k,s) );
>
> (I'm just trying to guess at what caused the confusion - even if you do
> change to this schema, the uniqueness constraint will pass even better
> than before, since the primary key is by definition unique and
> automatically increments, so any key in which it appears MUST also be
> unique for every value in it, even if you added 'bar' ten times in a
> row, it will never fail.)
>
> > insert into t (s) values ("foo");
> > insert into t (s) values ("bar");
> > select * from t;
> > k   s
> > --  --
> > 1   foo
> > 2   bar
>
> foo and bar here (which should be quoted like 'foo' and 'bar' by the
> way, double-quotes are for identifiers) are very unique, so cannot
> possibly constitute a uniqueness violation.
>
> >
> > Adding to my confusion is this:
> >
> > insert into t values (3, "bar");
> > Error: UNIQUE constraint failed: t.s
>
> whereas 'bar' here definitely is not unique (there is already a 'bar' in
> there) and thus fails.
>
> I've always found the higher level languages to be slightly less than
> intuitive, since they try to "read like English", except the language is
> nuanced and often what something seems to mean in English is not what it
> really means in the computed sense. That means that a statement fragment
> like UNIQUE needs as much documentation as, for instance, the lower
> level assembly language MOV command, if not more, because MOV left you
> with zero assumptions, you had to check the docs to see what it did, and
> even then, it delightfully did the very minimum.
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] unique constraint

[R Smith]

On 2018/05/16 1:25 AM, Mark Wagner wrote:

OK, at the risk of exposing my lack of edification...


Sometimes you pay the price for taking a risk. :)


create table t (k integer primary key, s text UNIQUE);


Did you by any chance assume the "UNIQUE" constraint to cover both the 
fields k and s? It applies of course only to s here. If you wanted it to 
apply to both, the schema should read:

create table t(k integer primary key, s text, unique(k,s) );

(I'm just trying to guess at what caused the confusion - even if you do 
change to this schema, the uniqueness constraint will pass even better 
than before, since the primary key is by definition unique and 
automatically increments, so any key in which it appears MUST also be 
unique for every value in it, even if you added 'bar' ten times in a 
row, it will never fail.)



insert into t (s) values ("foo");
insert into t (s) values ("bar");
select * from t;
k   s
--  --
1   foo
2   bar


foo and bar here (which should be quoted like 'foo' and 'bar' by the 
way, double-quotes are for identifiers) are very unique, so cannot 
possibly constitute a uniqueness violation.




Adding to my confusion is this:

insert into t values (3, "bar");
Error: UNIQUE constraint failed: t.s


whereas 'bar' here definitely is not unique (there is already a 'bar' in 
there) and thus fails.


I've always found the higher level languages to be slightly less than 
intuitive, since they try to "read like English", except the language is 
nuanced and often what something seems to mean in English is not what it 
really means in the computed sense. That means that a statement fragment 
like UNIQUE needs as much documentation as, for instance, the lower 
level assembly language MOV command, if not more, because MOV left you 
with zero assumptions, you had to check the docs to see what it did, and 
even then, it delightfully did the very minimum.



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] unique constraint

[Keith Medcalf]

>OK, at the risk of exposing my lack of edification, I'm wondering if
>someone can explain why this simple test of unique column constraints
>doesn't work.  At least it doesn't work as I expected it would (i.e.
>that
>the second insert would yield a unique constraint violation).
>
>create table t (k integer primary key, s text UNIQUE);
>insert into t (s) values ("foo");
>insert into t (s) values ("bar");
>select * from t;
>k   s
>--  --
>1   foo
>2   bar

Other than the fact you happen to be a lucky duck hunter not getting yourself 
shot in the ass for using the wrong quotes, what is the problem?  

Why are you confused?

INTEGER PRIMARY KEY is the record number in the table.  Each record can only 
have one record number.  Each record number is unique.  The first is number 
one, the second is number 2, and so on and so forth.

>Adding to my confusion is this:

>insert into t values (3, "bar");
>Error: UNIQUE constraint failed: t.s
>

Any why should this be "confusing"?  Seems perfectly obvious to me.  You have 
said t.s should be unique.  You attempted to insert a duplicate value of t.s.  
You were told that the UNIQUE containt t.s failed.  The fact that this is 
"record number three" is irrelevant -- just as "the sun is shining today" is 
irrelevant.


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





___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] unique constraint

[Mark Wagner]

I am confused.  Too much experimenting :(

On Tue, May 15, 2018 at 4:55 PM Simon Slavin  wrote:

> On 16 May 2018, at 12:25am, Mark Wagner  wrote:
>
> > I'm wondering if
> > someone can explain why this simple test of unique column constraints
> > doesn't work.  At least it doesn't work as I expected it would (i.e. that
> > the second insert would yield a unique constraint violation).
> >
> > create table t (k integer primary key, s text UNIQUE);
> > insert into t (s) values ("foo");
> > insert into t (s) values ("bar");
>
> You appear to be confused.  How does the second INSERT violate the UNIQUE
> constraint ?
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] unique constraint

[Simon Slavin]

On 16 May 2018, at 12:25am, Mark Wagner  wrote:

> I'm wondering if
> someone can explain why this simple test of unique column constraints
> doesn't work.  At least it doesn't work as I expected it would (i.e. that
> the second insert would yield a unique constraint violation).
> 
> create table t (k integer primary key, s text UNIQUE);
> insert into t (s) values ("foo");
> insert into t (s) values ("bar");

You appear to be confused.  How does the second INSERT violate the UNIQUE 
constraint ?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UNIQUE Constraint failure better diagnostics

[Chris Brody]

+1 on my part

On Sun, Aug 14, 2016 at 9:59 AM, Василий Кудрявцев  wrote:
> Hi!
>
>
>
> I would like to propose an enhancement to SQLite diagnostics in case of
> unique constraint failure.
>
> Currently the message does not contain the name of constraint in question,
> only columns. For example:
>
>
>
> sqlite> CREATE TABLE c_test (c0 INTEGER, c1 INTEGER, c2 INTEGER, CONSTRAINT
> U_X1 UNIQUE(c0, c2), CONSTRAINT U_X2 UNIQUE(c1, c2) );
>
> sqlite> INSERT  INTO c_test VALUES (1, 1, 1);
>
> sqlite> INSERT  INTO c_test VALUES (2, 1, 1);
>
> Error: UNIQUE constraint failed: c_test.c1, c_test.c2
>
> sqlite> INSERT  INTO c_test VALUES (1, 2, 1);
>
> Error: UNIQUE constraint failed: c_test.c0, c_test.c2
>
>
>
> It is much more convenient in CHECK constraints:
>
>
>
> CREATE TABLE test (id INTEGER, CONSTRAINT U_X UNIQUE(id), CONSTRAINT C_X
> CHECK (id < 5));
>
> sqlite> INSERT  INTO test VALUES (7);
>
> Error: CHECK constraint failed: C_X
>
>
>
>
>
> The CHECK constraint name is included in the message and can easiliy be
> extracted.
>
>
>
> It seems to me that adding similar behavior to unique constraints will be
> beneficial, and, looking into the source code, not hard to implement.
>
>
>
> Vasiliy
>
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UNIQUE constraint violation

[James K. Lowden]

On Tue, 28 Jun 2016 19:19:43 -0700
J Decker  wrote:

> Duplication can also result as part of the - in process - moving of
> rows. To change the order of [1,2,3,4] to
> [1,3,2,4]  there is(can be) a state that is [1,2,2,4] before the
> second part that sets three back into 2.

I'd just like to point out to the OP that, while SQLite does behave in
the way described above, it's a flaw.  SQL semantics are
per-statement, not per-row.  From an SQL perspective, any "state" that
might occur within a statement is meaningless.  

In SQLite, an update to a primary key K fails

update T set K = K + 1;

if, for any value v in K, there exists v + 1.  By the rules of SQL, it
should succeed and, in most other DBMSs, does.  

--jkl

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UNIQUE constraint violation

[R Smith]

On 2016/06/29 4:53 PM, Joe Pasquariello wrote:

On 6/29/2016 6:13 AM, R Smith wrote:
Very happy this is sorted for you. Note that Synchronous=OFF should 
only ever be used on a read-only DB (or at least one where writing 
only happens when you the user/DB Admin actively makes changes via 
queries. For anything that should run autonomously, Synchronous 
should preferably be FULL and at least be ON.


There is a time-penalty to be paid for Synchronous=FULL, but if it 
isn't part of a user interface and no user to notice that specific 
thread is lagging a second or two when writing, then it rarely matters. 


Okay, thanks. I thought it was generally preferable to use "lower" 
levels of synchronous. I am not using WAL, so I'll use FULL rather 
than NORMAL. Seems like I lucked out in the table being corrupted, but 
recoverable.


Synchronous (from Latin meaning more or less: At-Same-Time or In-Step) 
in very short computer terms means something like "Wait for the OS to do 
the actual writes before accepting that they are written", so the more 
Synchronous you do things, the more safe-ish it is - with the downside 
of a bit of extra time taken by the waiting for the OS to commit the 
writes[1].


Is there a simple explanation as to why your query to find duplicates 
worked correctly, despite the corrupted index, whereas the one I had 
used did not? Does it mean that your query doesn't use the index?


It's simple really - there are 2 possible reasons:
1 - I did post another query before to demonstrate how grouping doesn't 
group values together that differ in case (if you haven't added NOCASE 
statements) and so it won't look like 2 similar rows using a GROUP BY, 
but still can make a UNIQUE constraint fail. Ex. Smith and SMITH will be 
in 2 different groupings in the query in the original table (if it did 
not have NOCASE specified), but will be failing a UNIQUE constraint in 
the new table where you definitely specified NOCASE. (this is the only 
explanation if the DB was healthy, but wasn't likely in this case).


2 - More importantly, my Query checked specifically the row_id, not just 
the uniqueness of the groupings, which means the Query-Planner had to 
consult/walk the row_id index and found those rows which were not 
showing up when using the normal other indices you had (like the one 
that's been broken and causing the integrity check to fail).


These are the reasons I suggested the Query - but please note there is 
nothing technically wrong with the query you found on StackOverflow - it 
just assumes correct case, encoding and valid indices - basically a 
valid working DB.

We did not assume such. That's the only difference.

Hope that makes it clear!
Ryan


[1] : I say Safe-ish because there's a whole other world in which hard 
drives lie to the OS about what they have committed to platters and 
such, but it's a long story, varies from manufacturer to manufacturer, 
and in general doesn't need to be accounted for, if you use 
Synchronous-full, you should be fine barring only the worst of 
coincidences.  There's been some posts on this forum discussing it if 
you are more interested and feel like searching.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UNIQUE constraint violation

[Simon Slavin]

On 29 Jun 2016, at 3:53pm, Joe Pasquariello  wrote:

> Okay, thanks. I thought it was generally preferable to use "lower" levels of 
> synchronous.

No.  Other way around.

Two principles to guide you through the use of PRAGMAs:

A) The default settings are meant to be safe, probably safest.
B) If it makes something faster, it's dangerous in some way.  Otherwise it 
would be the default.  See (A).

So start off by leaving all PRAGMAs alone.  Only if you have performance 
problems think about changing them.  There are a couple of exceptions (e.g. 
journal_mode) but that's the way to bet.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UNIQUE constraint violation

[Joe Pasquariello]

On 6/29/2016 6:13 AM, R Smith wrote:
Very happy this is sorted for you. Note that Synchronous=OFF should 
only ever be used on a read-only DB (or at least one where writing 
only happens when you the user/DB Admin actively makes changes via 
queries. For anything that should run autonomously, Synchronous should 
preferably be FULL and at least be ON.


There is a time-penalty to be paid for Synchronous=FULL, but if it 
isn't part of a user interface and no user to notice that specific 
thread is lagging a second or two when writing, then it rarely matters. 


Okay, thanks. I thought it was generally preferable to use "lower" 
levels of synchronous. I am not using WAL, so I'll use FULL rather than 
NORMAL. Seems like I lucked out in the table being corrupted, but 
recoverable. Is there a simple explanation as to why your query to find 
duplicates worked correctly, despite the corrupted index, whereas the 
one I had used did not? Does it mean that your query doesn't use the index?


Joe
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UNIQUE constraint violation

[R Smith]

On 2016/06/29 2:48 PM, Joe Pasquariello wrote:


starting from the original, corrupted DB...

integrity_check
row 18029 missing from index sqlite_autoindex_EventLog_1
row 18030 missing from index sqlite_autoindex_EventLog_1
row 18031 missing from index sqlite_autoindex_EventLog_1
row 18032 missing from index sqlite_autoindex_EventLog_1
row 18033 missing from index sqlite_autoindex_EventLog_1
row 18034 missing from index sqlite_autoindex_EventLog_1
row 18035 missing from index sqlite_autoindex_EventLog_1
wrong # of entries in index sqlite_autoindex_EventLog_1

delete from eventlog where rowid >=18029 and rowid <= 18035
(checked integrity again here, and got same result as above)

reindex
integrity_check
ok

vacuum
integrity_check
ok

Query for duplicates returns no records.

Thanks very much, Ryan and Simon. My application monitors wind 
turbines, and there are somewhat frequent power issues at the sites. 
At one particular site, I had frequent corruption on server crashes, 
but none since changing SYNCHRONOUS from OFF to NORMAL. This 
particular site still had SYNCHRONOUS OFF, but will now have NORMAL.


Very happy this is sorted for you. Note that Synchronous=OFF should only 
ever be used on a read-only DB (or at least one where writing only 
happens when you the user/DB Admin actively makes changes via queries. 
For anything that should run autonomously, Synchronous should preferably 
be FULL and at least be ON.


There is a time-penalty to be paid for Synchronous=FULL, but if it isn't 
part of a user interface and no user to notice that specific thread is 
lagging a second or two when writing, then it rarely matters.


Best,
Ryan

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UNIQUE constraint violation

[Joe Pasquariello]

On 6/29/2016 1:04 AM, R Smith wrote:


These are the duplicate records. This index is associated with a 
UNIQUE constraint on the original table. Is there a way to clean it up?


That means your DB is corrupted by some method. It's not bad yet, just 
missing Index entries.
Make a copy of the database file, then try to delete those rows and 
recreate all indices with:


DELETE FROM eventlog WHERE row_id IN 
(18029,18030,18031,18032,18033,18034,18035);

REINDEX;
PRAGMA integrity_check;

If you do not delete the rows, it can't re-index because the Unique 
Index constraint will fail since the rows are not unique.


If this worked, test all tables and queries. Be sure to have that 
backup at hand - once integrity is broken, the DB state is not secure 
until integrity check passes.



PS: Nice catch Simon - I did not even consider a broken index. I've 
never had an SQLite file go corrupt on me, even though using many 
SQLite DBs in all our systems, even as application file formats, so 
I'm always a bit oblivious to that possibility.




starting from the original, corrupted DB...

integrity_check
row 18029 missing from index sqlite_autoindex_EventLog_1
row 18030 missing from index sqlite_autoindex_EventLog_1
row 18031 missing from index sqlite_autoindex_EventLog_1
row 18032 missing from index sqlite_autoindex_EventLog_1
row 18033 missing from index sqlite_autoindex_EventLog_1
row 18034 missing from index sqlite_autoindex_EventLog_1
row 18035 missing from index sqlite_autoindex_EventLog_1
wrong # of entries in index sqlite_autoindex_EventLog_1

delete from eventlog where rowid >=18029 and rowid <= 18035
(checked integrity again here, and got same result as above)

reindex
integrity_check
ok

vacuum
integrity_check
ok

Query for duplicates returns no records.

Thanks very much, Ryan and Simon. My application monitors wind turbines, 
and there are somewhat frequent power issues at the sites. At one 
particular site, I had frequent corruption on server crashes, but none 
since changing SYNCHRONOUS from OFF to NORMAL. This particular site 
still had SYNCHRONOUS OFF, but will now have NORMAL.


Joe

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UNIQUE constraint violation

[Simon Slavin]

On 29 Jun 2016, at 5:36am, Joe Pasquariello  wrote:

> These are the duplicate records. This index is associated with a UNIQUE 
> constraint on the original table. Is there a way to clean it up?

Unfortunately it's an automatic index and I hesitate to manipulate it manually 
in case it messes up something else automatic.  First, using the SQLite shell 
tool in interactive mode, try a simple way:

 ...

1) Make absolutely sure you have a backup copy of the database.
2) Execute the SQL command "VACUUM".
3) Test one of the SELECTs which was giving the wrong results.

If that doesn't fix the problem I would ...

1) Use .dump to dump your data to a text file.
2) Quit the shell tool.
3) Start it back up with another filename to create a new blank database.
4) Use .read to read the text file and execute it as commands
5) Test one of the SELECTs which was giving the wrong results.

If it works, delete the old database file and rename the new one.

On 29 Jun 2016, at 9:04am, R Smith  wrote:

> PS: Nice catch Simon - I did not even consider a broken index. I've never had 
> an SQLite file go corrupt on me


It was the only thing I could think of.  OP typing in diagnostic SELECT 
commands seemed to demonstrate enough weirdness that it was worth checking.  
Unfortunately it means that we have to wonder how the database got corrupted.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UNIQUE constraint violation

[R Smith]

On 2016/06/29 6:36 AM, Joe Pasquariello wrote:



On 6/28/2016 4:52 PM, Simon Slavin wrote:

On 28 Jun 2016, at 11:22pm, Joe Pasquariello  wrote:

SELECT udatetime,typeof(udatetime),udatetime-1415000934 FROM eventlog 
where device like '%M14' and udatetime=1415000934

udatetimetypeof(udatetime)udatetime-1415000934
1415000934integer0

PRAGMA integrity_check

integrity_check
row 18029 missing from index sqlite_autoindex_EventLog_1
row 18030 missing from index sqlite_autoindex_EventLog_1
row 18031 missing from index sqlite_autoindex_EventLog_1
row 18032 missing from index sqlite_autoindex_EventLog_1
row 18033 missing from index sqlite_autoindex_EventLog_1
row 18034 missing from index sqlite_autoindex_EventLog_1
row 18035 missing from index sqlite_autoindex_EventLog_1
wrong # of entries in index sqlite_autoindex_EventLog_1

These are the duplicate records. This index is associated with a 
UNIQUE constraint on the original table. Is there a way to clean it up?


That means your DB is corrupted by some method. It's not bad yet, just 
missing Index entries.
Make a copy of the database file, then try to delete those rows and 
recreate all indices with:


DELETE FROM eventlog WHERE row_id IN 
(18029,18030,18031,18032,18033,18034,18035);

REINDEX;
PRAGMA integrity_check;

If you do not delete the rows, it can't re-index because the Unique 
Index constraint will fail since the rows are not unique.


If this worked, test all tables and queries. Be sure to have that backup 
at hand - once integrity is broken, the DB state is not secure until 
integrity check passes.



PS: Nice catch Simon - I did not even consider a broken index. I've 
never had an SQLite file go corrupt on me, even though using many SQLite 
DBs in all our systems, even as application file formats, so I'm always 
a bit oblivious to that possibility.



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UNIQUE constraint violation

[Joe Pasquariello]

On 6/28/2016 4:52 PM, Simon Slavin wrote:

On 28 Jun 2016, at 11:22pm, Joe Pasquariello  wrote:


select rowid, device, hex(device), localtime, udatetime
from eventlog where device like '%M14' and udatetime=1415000934

rowiddevicehex(device)localtimeudatetime
18031M144D31342014-11-03 01:48:541415000934
18062M144D31342014-11-03 01:48:541415000934

(2)
select rowid, device, hex(device), udatetime
from eventlog where device like '%M14' and udatetime=1415000934

rowiddevicehex(device)udatetime
18062M144D31341415000934

SELECT udatetime,typeof(udatetime),udatetime-1415000934 FROM eventlog where 
device like '%M14' and udatetime=1415000934

udatetimetypeof(udatetime)udatetime-1415000934
1415000934integer0

PRAGMA integrity_check

integrity_check
row 18029 missing from index sqlite_autoindex_EventLog_1
row 18030 missing from index sqlite_autoindex_EventLog_1
row 18031 missing from index sqlite_autoindex_EventLog_1
row 18032 missing from index sqlite_autoindex_EventLog_1
row 18033 missing from index sqlite_autoindex_EventLog_1
row 18034 missing from index sqlite_autoindex_EventLog_1
row 18035 missing from index sqlite_autoindex_EventLog_1
wrong # of entries in index sqlite_autoindex_EventLog_1

These are the duplicate records. This index is associated with a UNIQUE 
constraint on the original table. Is there a way to clean it up?




Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users




--
Joe Pasquariello
Fenway Systems, LLC
2980 College Ave, Suite 7
Berkeley, CA  94705
510-665-4355
j...@fenway.com

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UNIQUE constraint violation

[Simon Slavin]

If you need to temporarily violate checks in order to rearrange your schema, I 
draw your attention to these two:





Obviously, once you're done rearranging/importing you reenable them both and run



To see what horrible things you've done to the database.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UNIQUE constraint violation

[J Decker]

Duplication can also result as part of the - in process - moving of rows.
To change the order of [1,2,3,4] to
[1,3,2,4]  there is(can be) a state that is [1,2,2,4] before the second
part that sets three back into 2.

This is a point where NULL can be useful to make the transition

[1,2,NULL,4]
[1,3,NULL,4]
[1,3,2,4]

( in which case NOT NULL is NOT recommended :)  )  It can be some other
unique value like -1; but in either case; some databases only allow one
NULL in unique anyway... and if you have two processes that are both in
partial steps of execution you can conflict that way too.

wrapping that in a transaction should suspend constraint checks until a
commiit   (IMO don't know if that's fact)

(or partial duplicates can happen if you get an exception that faults and
fails to complete the operation)


On Tue, Jun 28, 2016 at 2:18 PM, Joe Pasquariello  wrote:

> Firstly, the Index i_udatetime is Superfluous, you can remove it for
>> some efficiency points. (The Unique constraint already offers an Index
>> with udatetime as the left-most or main Index).
>>
>> All I can still suggest or ask is: Are you sure there are no duplicates?
>>
>> The correct SQL to check would be:
>>
>> SELECT A.*
>>FROM EventLog AS A
>>JOIN EventLog AS B ON B.udatetime=A.udatetime AND B.device=A.device
>> AND B.code=A.code AND B.type=A.type
>>   WHERE B.row_id <> A.row_id
>>
>> Any row that shows up is a duplicate that would violate that Unique Index.
>>
>> Further to this, I'm not sure if you omitted the other fields for
>> brevity or to explain the problem, but that INSERT query should never
>> work since you specify all those columns as NOT NULL, then you do not
>> give it DEFAULT values, and then you omit them from the INSERT, which
>> means it must fail on the NOT NULL constraint.
>>
>> If this is not the case, and the above query doesn't show up any rows,
>> and the constraint still fails - mind letting us have a copy of the DB
>> file that produces that violation?
>>
>>
>> Cheers,
>> Ryan
>>
>>
> Thank you, Ryan. I'm embarassed to say I don't know how to reply to your
> response. When I posted my original question, I simply sent email to the
> mailing list. I viewed your response on Nabble, but it doesn't seem to
> allow me to reply. How can I view your response and reply somewhere other
> than Nabble? To create this email, I copied and pasted from Nabble. I'm
> afraid it will show up as a new topic. There has to be a better way.
>
> To the point, your query to find duplicate records showed there were some
> duplicates after all.
>
> I removed the duplicates and the SQL as shown in my original post worked.
> I don't know what you mean about the insert statement being incorrect. It
> explicitly sets all fields in the new table except rowid. My understanding
> was that since rowid is INTEGER PRIMARY KEY, inserts to the new table would
> get new rowid values, in the order inserted, which is what I want, and it
> seems to be doing that correctly.
>
> The query I was using to find duplicates is one that I found on
> StackOverflow. I'm very new to SQL. Can you tell me why this query returns
> no records while yours does? If I change the last clause to having
> count(*)=1, the result has the same number of rows as the table. I thought
> that meant it was doing what I wanted, but obviously it doesn't.
>
> select udatetime,device,localtime,code,type,text,0 as status, count(*) as
> the_count
> from eventlog
> group by udatetime,device,code,type,status
> having count(*) > 1
>
> >
>
>
>
>
>> On 2016/06/28 9:37 PM, Joe Pasquariello wrote:
>>
>> > Hello,
>> >
>> > I'm a novice user of Sqlite, and could use some help. The code below
>> > is intended to update a simple table to a new format. The changes
>> > include reordering columns, dropping one column, and adding a new
>> > "status" column (which is always 0). A constraint violation is being
>> > reported on UNIQUE constraint for the new table. I assume this means
>> > there is a row in the existing table that violates the constraint
>> > imposed on the new table, but I've queried the existing table for rows
>> > that would violate the constraint, and I don't find any. Is there
>> > something else that could cause the violation?
>> >
>> > Thanks,
>> >
>> > Joe
>> >
>> > BEGIN TRANSACTION;
>> > DROP TABLE IF EXISTS TempEventLog;
>> > ALTER TABLE EventLog RENAME TO TempEventLog;
>> >
>> > CREATE TABLE IF NOT EXISTS EventLog(
>> >   rowid INTEGER PRIMARY KEY,
>> >   udatetime INTEGERNOT NULL,
>> >   deviceCHAR(16)   NOT NULL   COLLATE NOCASE,
>> >   localtime CHAR(32)   NOT NULL   COLLATE NOCASE,
>> >   code  INTEGERNOT NULL,
>> >   type  CHAR(16)   NOT NULL   COLLATE NOCASE,
>> >   text  CHAR(64)   NOT NULL   COLLATE NOCASE,
>> >   statusINTEGERNOT NULL,
>> >   UNIQUE(udatetime,device,code,type,status)
>> > );
>> >
>> > CREATE INDEX IF NOT EXISTS i_udatetime ON Ev

Re: [sqlite] UNIQUE constraint violation

[Simon Slavin]

On 28 Jun 2016, at 11:22pm, Joe Pasquariello  wrote:

> select rowid, device, hex(device), localtime, udatetime
> from eventlog where device like '%M14' and udatetime=1415000934
> 
> rowiddevicehex(device)localtimeudatetime
> 18031M144D31342014-11-03 01:48:541415000934
> 18062M144D31342014-11-03 01:48:541415000934
> 
> (2)
> select rowid, device, hex(device), udatetime
> from eventlog where device like '%M14' and udatetime=1415000934
> 
> rowiddevicehex(device)udatetime
> 18062M144D31341415000934

SELECT udatetime,typeof(udatetime),udatetime-1415000934 FROM eventlog where 
device like '%M14' and udatetime=1415000934


PRAGMA integrity_check

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UNIQUE constraint violation

[Joe Pasquariello]

On 6/28/2016 3:05 PM, Simon Slavin wrote:

SELECT device,HEX(device) FROM myTable WHERE


Thanks, Simon. Now it's getting really strange. Two queries and their 
results are shown below. The value of "device" seems to be the same in 
both rows. One query includes the field "localtime" and the other 
doesn't. Perhaps I should not be using "localtime" as a field name. I 
know it's a modifier to strftime().


(1)
select rowid, device, hex(device), localtime, udatetime
from eventlog where device like '%M14' and udatetime=1415000934

rowiddevicehex(device)localtimeudatetime
18031M144D31342014-11-03 01:48:541415000934
18062M144D31342014-11-03 01:48:541415000934

(2)
select rowid, device, hex(device), udatetime
from eventlog where device like '%M14' and udatetime=1415000934

rowiddevicehex(device)udatetime
18062M144D31341415000934

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UNIQUE constraint violation

[Joe Pasquariello]

On 6/28/2016 2:35 PM, R Smith wrote:


On 2016/06/28 11:21 PM, R Smith wrote:

CREATE TABLE t(A TEXT COLLATE NOCASE, BTEXT COLLATE NOCASE);


Correction: I meant to paste that line below saying it is what you 
need to fix the grouping, and added it to the script in stead. The 
original meant to be without the NOCASE collations.




Thanks. I just changed my account settings so that instead of getting 
only the digest, I get the individual emails. That seems to be required 
if one wants to post and reply.


Regarding the failure of my StackOverflow-sourced query to find 
duplicates, it seems you're right. Somehow, the two "device" values 
shown in the table below are not the same, and I have no idea why. I get 
both rows if I select where device like '%M14', but I get only one row 
if select where device='M14', yet length(device) is 3 for both. Hm.


rowiddevicelocaltime type  code 
text udatetime jdatetime
18031M14 2014-11-03 01:48:54E   0Failurefree 
system14150009342456964.825625
18062M14 2014-11-03 01:48:54E   0Failurefree 
system14150009342456964.825625





___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users




--
Joe Pasquariello
Fenway Systems, LLC
2980 College Ave, Suite 7
Berkeley, CA  94705
510-665-4355
j...@fenway.com

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UNIQUE constraint violation

[Simon Slavin]

On 28 Jun 2016, at 11:03pm, Joe Pasquariello  wrote:

> Somehow, the two "device" values shown in the table below are not the same, 
> and I have no idea why.

SELECT device,HEX(device) FROM myTable WHERE ...

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UNIQUE constraint violation

[R Smith]

On 2016/06/28 11:21 PM, R Smith wrote:

CREATE TABLE t(A TEXT COLLATE NOCASE, BTEXT COLLATE NOCASE);


Correction: I meant to paste that line below saying it is what you need 
to fix the grouping, and added it to the script in stead. The original 
meant to be without the NOCASE collations.




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UNIQUE constraint violation

[R Smith]

On 2016/06/28 10:52 PM, j...@fenway.com wrote:

Thank you, Ryan. I'm embarassed to say I don't know how to reply to your 
response. When I posted my original question, I simply sent email to the 
mailing list. I viewed your response on Nabble, but it doesn't seem to allow me 
to reply. How can I view your response and reply somewhere other than Nabble?


My pleasure

I'm now also embarrassed to say, I have no idea how Nabble works, I 
simply responded to the mail from the SQLite-users mailing list - 
sometimes GMail filters my mails, so some people do not see it on the 
mailing list. I will send this reply to the SQLite-users mailing list 
for completeness.





To the point, your query to find duplicate records showed there were some.

I removed the duplicates and the SQL as shown in my original post worked. I
don't know what you mean about the insert statement being incorrect. It
explicitly sets all fields in the new table except rowid. My understanding
was that since rowid is INTEGER PRIMARY KEY, inserts to the new table would
get new rowid values, in the order inserted, which is what I want, and it
seems to be doing that correctly.


Yes, perfect - that field set was my mistake, not sue how I looked, but 
at the moment I was looking I did not notice all the fields were in the 
prototype for the Insert. It is however and should work perfectly - my 
apologies.



The query I was using to find duplicates is one that I found on
StackOverflow. I'm very new to SQL. Can you tell me why this query returns
no records while yours does? If I change the last clause to having
count(*)=1, the result has the same number of rows as the table. I thought
that meant it was doing what I wanted, but obviously it doesn't.

select udatetime,device,localtime,code,type,text,0 as status, count(*) as
the_count
from eventlog
group by udatetime,device,code,type,status
having count(*) > 1



This Query is a valiant effort from someone on StackOverflow, but it 
assumes homogeneous data with respect to character sets encoding and 
casing - i.e. what qualifies as a Duplicate in constraint terms doesn't 
qualify as a grouped item necessarily. This is why I volunteered that 
SQL. To demonstrate, please consider this script:


  -- SQLite version 3.9.2  [ Release: 2015-11-02 ]  on SQLitespeed 
version 2.0.2.4.


  -- Script Items: 5  Parameter Count: 0
  -- 2016-06-28 23:09:52.846  |  [Info]   Script Initialized, 
Started executing...
  -- 



CREATE TABLE t(A TEXT COLLATE NOCASE, BTEXT COLLATE NOCASE);

INSERT INTO t VALUES
('John','Smith'),
('john','Smith'),
('JOHN','Smith'),
('Johnson','Smithers'),
('Babe','Ruth');


SELECT * FROM t;

  -- A | B
  -- - | --
  -- John  | Smith
  -- john  | Smith
  -- JOHN  | Smith
  -- Johnson   | Smithers
  -- Babe  | Ruth


SELECT A, MAX(B) FROM t GROUP BY A ;

  -- A | MAX(B)
  -- - | --
  -- Babe  | Ruth
  -- JOHN  | Smith
  -- John  | Smith
  -- Johnson   | Smithers
  -- john  | Smith


SELECT MAX(A), B FROM t GROUP BY B

  -- MAX(A)| B
  -- - | --
  -- Babe  | Ruth
  -- john  | Smith
  -- Johnson   | Smithers


And that Table will obviously fail a UNIQUE constraint, but taht is not 
obvious from the grouping.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UNIQUE constraint violation

[R Smith]

Additionally - You should add NOT NULL to the PRIMARY KEY constraint 
too, since a backwards-compatible peculiarity in SQLite allows the PK to 
have NULL values if not explicitly disallowed.




BEGIN TRANSACTION;
DROP TABLE IF EXISTS TempEventLog;
ALTER TABLE EventLog RENAME TO TempEventLog;

CREATE TABLE IF NOT EXISTS EventLog(
  rowid INTEGER PRIMARY KEY,
  udatetime INTEGERNOT NULL,
  deviceCHAR(16)   NOT NULL   COLLATE NOCASE,
  localtime CHAR(32)   NOT NULL   COLLATE NOCASE,
  code  INTEGERNOT NULL,
  type  CHAR(16)   NOT NULL   COLLATE NOCASE,
  text  CHAR(64)   NOT NULL   COLLATE NOCASE,
  statusINTEGERNOT NULL,
  UNIQUE(udatetime,device,code,type,status)
);

CREATE INDEX IF NOT EXISTS i_udatetime ON EventLog(udatetime);
CREATE INDEX IF NOT EXISTS i_code ON EventLog(code);
CREATE INDEX IF NOT EXISTS i_device_code ON EventLog(device,code);

INSERT OR ROLLBACK INTO EventLog (
  udatetime, device, localtime, code, type, text, status )
SELECT udatetime, device, localtime, code, type, text, 0
  FROM TempEventLog ORDER BY rowid;

DROP TABLE TempEventLog;
PRAGMA user_version = 1;
COMMIT;





___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UNIQUE constraint violation

[R Smith]

Firstly, the Index i_udatetime is Superfluous, you can remove it for 
some efficiency points. (The Unique constraint already offers an Index 
with udatetime as the left-most or main Index).


All I can still suggest or ask is: Are you sure there are no duplicates?

The correct SQL to check would be:

SELECT A.*
  FROM EventLog AS A
  JOIN EventLog AS B ON B.udatetime=A.udatetime AND B.device=A.device 
AND B.code=A.code AND B.type=A.type

 WHERE B.row_id <> A.row_id

Any row that shows up is a duplicate that would violate that Unique Index.

Further to this, I'm not sure if you omitted the other fields for 
brevity or to explain the problem, but that INSERT query should never 
work since you specify all those columns as NOT NULL, then you do not 
give it DEFAULT values, and then you omit them from the INSERT, which 
means it must fail on the NOT NULL constraint.


If this is not the case, and the above query doesn't show up any rows, 
and the constraint still fails - mind letting us have a copy of the DB 
file that produces that violation?



Cheers,
Ryan


On 2016/06/28 9:37 PM, Joe Pasquariello wrote:

Hello,

I'm a novice user of Sqlite, and could use some help. The code below 
is intended to update a simple table to a new format. The changes 
include reordering columns, dropping one column, and adding a new 
"status" column (which is always 0). A constraint violation is being 
reported on UNIQUE constraint for the new table. I assume this means 
there is a row in the existing table that violates the constraint 
imposed on the new table, but I've queried the existing table for rows 
that would violate the constraint, and I don't find any. Is there 
something else that could cause the violation?


Thanks,

Joe

BEGIN TRANSACTION;
DROP TABLE IF EXISTS TempEventLog;
ALTER TABLE EventLog RENAME TO TempEventLog;

CREATE TABLE IF NOT EXISTS EventLog(
  rowid INTEGER PRIMARY KEY,
  udatetime INTEGERNOT NULL,
  deviceCHAR(16)   NOT NULL   COLLATE NOCASE,
  localtime CHAR(32)   NOT NULL   COLLATE NOCASE,
  code  INTEGERNOT NULL,
  type  CHAR(16)   NOT NULL   COLLATE NOCASE,
  text  CHAR(64)   NOT NULL   COLLATE NOCASE,
  statusINTEGERNOT NULL,
  UNIQUE(udatetime,device,code,type,status)
);

CREATE INDEX IF NOT EXISTS i_udatetime ON EventLog(udatetime);
CREATE INDEX IF NOT EXISTS i_code ON EventLog(code);
CREATE INDEX IF NOT EXISTS i_device_code ON EventLog(device,code);

INSERT OR ROLLBACK INTO EventLog (
  udatetime, device, localtime, code, type, text, status )
SELECT udatetime, device, localtime, code, type, text, 0
  FROM TempEventLog ORDER BY rowid;

DROP TABLE TempEventLog;
PRAGMA user_version = 1;
COMMIT;






___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UNIQUE constraint

[Fabian]

2011/10/23 Simon Slavin 

>
> In this example, the indexed column is a text column.  The text fields
> could have been very long, and checking long text fields for uniqueness can
> involve comparing every byte.  Nevertheless, I do not understand the results
> you quoted.  I wonder whether some use of transactions would have vastly
> reduced the problem.
>
>
In my case, the indexed column is a text column too, and the original author
already stated he batched the inserts into transactions, and made use of
PRAGMA statements. Only after pre-sorting the data, the problem disappeared,
but I'd rather avoid having to pre-sort everything (because SQLite's C code
should be able to do it faster, than my C# code).
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UNIQUE constraint

[Simon Slavin]

On 23 Oct 2011, at 3:49pm, Fabian wrote:

> So the only overhead for UNIQUE is that extra check? [snip]

Right.  When doing an INSERT or UPDATE, it checks to see whether the value it's 
trying to add to the index already exists in the index.  If it does, the result 
is an error.  There is no difference in the file format used to store the index.

> The difference between 9 and 156 seconds is too large for me. The original
> author solved this by pre-sorting the data for the indexed column, which
> made the difference 9 to 12 seconds (including the pre-sort), which is very
> acceptable.
> 
> So why does SQLite not pre-sort the data itself, when doing such large batch
> inserts inside a transaction?

In this example, the indexed column is a text column.  The text fields could 
have been very long, and checking long text fields for uniqueness can involve 
comparing every byte.  Nevertheless, I do not understand the results you 
quoted.  I wonder whether some use of transactions would have vastly reduced 
the problem.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UNIQUE constraint

[Fabian]

>
>
> No, a UNIQUE index and a regular index are implemented the exact same way.
> It's just that, at INSERT and UPDATE time, after finding a proper place to
> insert the new value, an additional check is made that the place isn't
> already occupied.
>

So the only overhead for UNIQUE is that extra check?


> I suspect the way you are going to manually filter duplicates will involve
> inserting them into a moral equivalent of a UNIQUE index. The performace is
> probably going to be similar to that of just creating a UNIQUE index up
> front. But only measurements with your particular data can tell for sure.
>

I was planning using the HashList from the .Net framework, which should be
the fastest way to do it.

If SQLite only has to check if a certain position is occupied, it should
outperform creating a hash for each value.

What got me worried was the post "Slow insertion for Unique Text Column" to
this mailinglist, I quote:

---

I was creating a new table and populating it with 100,000 rows of data (as
a
test case; I really wanted to populate it with over a million rows).

[Insertion A] When a Text Column was NOT Unique it would take:
8875 ms = ~9 seconds

[Insertion B] When a Text Column was Unique it would take:
155781 ms = ~156 seconds

-

The difference between 9 and 156 seconds is too large for me. The original
author solved this by pre-sorting the data for the indexed column, which
made the difference 9 to 12 seconds (including the pre-sort), which is very
acceptable.

So why does SQLite not pre-sort the data itself, when doing such large batch
inserts inside a transaction?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UNIQUE constraint

[Igor Tandetnik]

Fabian  wrote:
> I have a column with a normal INDEX, and I would like to turn it into an
> UNIQUE index, but I'm a bit worried about the performance implications for
> inserts. Can someone give some insight into how UNIQUE is implemented in
> SQLite, does it create extra tables compared to a normale index, are there
> many extra checks?

No, a UNIQUE index and a regular index are implemented the exact same way. It's 
just that, at INSERT and UPDATE time, after finding a proper place to insert 
the new value, an additional check is made that the place isn't already 
occupied.

> And a related question: I need to insert 1 million rows, and currently I
> create the INDEX afterwards, because that should be faster. I cannot create
> the UNIQUE INDEX afterwards, because there's a slight possibility there will
> be 1 or 2 duplicates, which will make the creation fail. I now have the
> possibility to specify UNIQUE upfront (in the TABLE definition) or manually
> filter out any doubles before the insert (which will also take CPU time).
> Would there be any advantage doing this manually, or will SQLite do it just
> as efficiently?

I suspect the way you are going to manually filter duplicates will involve 
inserting them into a moral equivalent of a UNIQUE index. The performace is 
probably going to be similar to that of just creating a UNIQUE index up front. 
But only measurements with your particular data can tell for sure.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UNIQUE constraint on column

[drh]

"A. Pagaltzis" <[EMAIL PROTECTED]> wrote:

> It's a pity that INSERT OR IGNORE (apparently?) does not set
> last_insert_id properly regardless of outcome,

Consider this case:

  CREATE TABLE t1(x INTEGER PRIMARY KEY, y UNIQUE, z UNIQUE);
  INSERT INTO t1 VALUES(1,'a','b');
  INSERT INTO t1 VALUES(2,'x','y');

  INSERT OR IGNORE INTO t1 VALUES(NULL, 'a','y');

For the INSERT OR IGNORE statement, does the last_insert_rowid
get set to 1 or 2?

--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] UNIQUE constraint on column

[Shane Harrelson]

"Shane Harrelson" <[EMAIL PROTECTED]> wrote:
>
> I have two tables, an "Objects" table with a foreign key into a second
> "Strings" table which is composed of unique values.  It is a many to
> one relationship, that is, several Objects may reference the same
> String.   When an Object is added, its associated String is added to
> the Strings table.   If the String already exists in the Strings
> table, I'd like the new Object to reference the existing copy.
>
> Currently, I've implemented it as so (leaving out error handling, etc.):
>
> begin transaction
> insert into Strings (value) VALUES ( 'foo')
> if string insert result is SQLITE_OK
>get rowid of last insert (sqlite3_last_insert_rowid)
> else if result is SQLITE_CONSTRAINT
>select rowid from Strings where value = 'foo'
> end if
> if rowid
>insert into Objects (string_id) VALUES (rowid)
> end if
> if no error
>commit transaction
> else
>rollback transaction
> end if
>
> With my dataset, there is about a 10% chance of the string being a
> duplicate -- that is about 1 in 10 string inserts hit the UNIQUE
> constraint violation.
>
> I've tested "viloating" the internals of the VBDE and pulling the
> rowid of the duplicate off the VBDE stack when the SQLITE_CONSTRAINT
> result is returned and it is measurably (5-10%) faster then doing the
> subsequent SELECT.
>

What you are doing is the most efficient way that I can think of
right off hand.  If "violating" the internals is something you want
to do that's fine - just remember that it is likely to break in
irrepaiable ways in some future point release.  No tears.

Notice that an sqlite3_last_constraint_rowid() function doesn't really
work because an insert might fail due to multiple constraint violations
all on different rows.  In your schema, perhaps, there can be no more
than one constraint violated at a time, but it is easy enough to
construct a schema where multiple rows can violate a constraint, so the
sqlite3_last_constraint_rowid() idea does not generalize well.

--
D. Richard Hipp  <[EMAIL PROTECTED]>



Thanks all for the feedback.   I was able to get comparable speed-ups
to my "vbde hack" on the string insertion by using a small "cache" of
rowids for previously inserted strings.  This reduced the duplicate
inserts to about 1 in 100.

I still think someway of getting the rowid of the record that forced
the query to end with a constraint violation (even if it isn't the
only one) would be useful.  Perhaps it could be added to the current
error string which already includes the column name(s).

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] UNIQUE constraint on column

[drh]

"Shane Harrelson" <[EMAIL PROTECTED]> wrote:
> 
> I have two tables, an "Objects" table with a foreign key into a second
> "Strings" table which is composed of unique values.  It is a many to
> one relationship, that is, several Objects may reference the same
> String.   When an Object is added, its associated String is added to
> the Strings table.   If the String already exists in the Strings
> table, I'd like the new Object to reference the existing copy.
> 
> Currently, I've implemented it as so (leaving out error handling, etc.):
> 
> begin transaction
> insert into Strings (value) VALUES ( 'foo')
> if string insert result is SQLITE_OK
>get rowid of last insert (sqlite3_last_insert_rowid)
> else if result is SQLITE_CONSTRAINT
>select rowid from Strings where value = 'foo'
> end if
> if rowid
>insert into Objects (string_id) VALUES (rowid)
> end if
> if no error
>commit transaction
> else
>rollback transaction
> end if
> 
> With my dataset, there is about a 10% chance of the string being a
> duplicate -- that is about 1 in 10 string inserts hit the UNIQUE
> constraint violation.
> 
> I've tested "viloating" the internals of the VBDE and pulling the
> rowid of the duplicate off the VBDE stack when the SQLITE_CONSTRAINT
> result is returned and it is measurably (5-10%) faster then doing the
> subsequent SELECT.
> 

What you are doing is the most efficient way that I can think of
right off hand.  If "violating" the internals is something you want
to do that's fine - just remember that it is likely to break in
irrepaiable ways in some future point release.  No tears.

Notice that an sqlite3_last_constraint_rowid() function doesn't really
work because an insert might fail due to multiple constraint violations
all on different rows.  In your schema, perhaps, there can be no more
than one constraint violated at a time, but it is easy enough to 
construct a schema where multiple rows can violate a constraint, so the
sqlite3_last_constraint_rowid() idea does not generalize well.

--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] UNIQUE constraint on column

[Nicolas Williams]

On Wed, Jan 31, 2007 at 05:23:29PM -0500, Shane Harrelson wrote:
> 
> I have two tables, an "Objects" table with a foreign key into a second
> "Strings" table which is composed of unique values.  It is a many to
> one relationship, that is, several Objects may reference the same
> String.   When an Object is added, its associated String is added to
> the Strings table.   If the String already exists in the Strings
> table, I'd like the new Object to reference the existing copy.
> 
> Currently, I've implemented it as so (leaving out error handling, etc.):
> 
> begin transaction
> insert into Strings (value) VALUES ( 'foo')
> if string insert result is SQLITE_OK

Sounds like you should want to use INSERT OR IGNORE ... INTO Strings and
then SELECT the rowid of the string for use in INSERTing INTO Object.

Nico
-- 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] UNIQUE constraint on column

[Shane Harrelson]

On 1/31/07, Dennis Cote <[EMAIL PROTECTED]> wrote:

Shane Harrelson wrote:
> On 1/31/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
>>
>> The official way to find the conflicting entry is to do a query.
>>
>>   SELECT rowid FROM table WHERE uniquecolumn=?;
>>
>>
>
> Thank you for the reply.  I assumed this was most likely the case, and
> as I said in my original email, I was hoping to avoid having to do a
> seperate select query for the sake of speed... especially since the
> value I needed was so tantalizing close in the VDBE struct.
>
Shane,

Why do you want the rowid of the conflicting row?

I only ask because I suspect there may be a better way to do what you
want to do.

Dennis Cote



I have two tables, an "Objects" table with a foreign key into a second
"Strings" table which is composed of unique values.  It is a many to
one relationship, that is, several Objects may reference the same
String.   When an Object is added, its associated String is added to
the Strings table.   If the String already exists in the Strings
table, I'd like the new Object to reference the existing copy.

Currently, I've implemented it as so (leaving out error handling, etc.):

begin transaction
insert into Strings (value) VALUES ( 'foo')
if string insert result is SQLITE_OK
  get rowid of last insert (sqlite3_last_insert_rowid)
else if result is SQLITE_CONSTRAINT
  select rowid from Strings where value = 'foo'
end if
if rowid
  insert into Objects (string_id) VALUES (rowid)
end if
if no error
  commit transaction
else
  rollback transaction
end if

With my dataset, there is about a 10% chance of the string being a
duplicate -- that is about 1 in 10 string inserts hit the UNIQUE
constraint violation.

I've tested "viloating" the internals of the VBDE and pulling the
rowid of the duplicate off the VBDE stack when the SQLITE_CONSTRAINT
result is returned and it is measurably (5-10%) faster then doing the
subsequent SELECT.

Any help or suggestions with how to do this better would be appreciated.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] UNIQUE constraint on column

[Kees Nuyt]

Hi Shane,

On Wed, 31 Jan 2007 09:29:24 -0500, you wrote:

>On 1/31/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
>> "Shane Harrelson" <[EMAIL PROTECTED]> wrote:
>> > when i try to insert a row into a table that has a UNIQUE constraint
>> > on a column, and I get the SQLITE_CONSTRAINT result code because i'm
>> > inserting a duplicate value, is there anyway to determine the rowid of
>> > the conflict?
>> >
>> > looking at the internals of the VDBE, i found that the rowid of the
>> > conflicting row is pushed on top of the VDBE stack.
>> >
>> > if i'm willing to violate the interface, i can dereference the rowid
>> > from the internals of the VDBE struct.  i'd rather not do this... is
>> > there a more formal mechanism for getting this value without having do
>> > to do a separate "select" query?  something like
>> > sqlite3_last_insert_rowid() -- ie.  sqlite3_last_conflict_rowid()?
>> >
>>
>> The official way to find the conflicting entry is to do a query.
>>
>>   SELECT rowid FROM table WHERE uniquecolumn=?;
>>
>> --
>> D. Richard Hipp  <[EMAIL PROTECTED]>
>>
>
>Thank you for the reply.  I assumed this was most likely the case, and
>as I said in my original email, I was hoping to avoid having to do a
>seperate select query for the sake of speed... especially since the
>value I needed was so tantalizing close in the VDBE struct.

The SELECT will be quite fast, chances are the required pages of
the unique index will still be in the cache.
In my opinion it is always better to write portable code, so I
would prefer to rely on generic SQL than on yet another
implementation specific API.

Just my 2 cents.
-- 
  (  Kees Nuyt
  )
c[_]

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] UNIQUE constraint on column

[Dennis Cote]

Shane Harrelson wrote:

On 1/31/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:


The official way to find the conflicting entry is to do a query.

  SELECT rowid FROM table WHERE uniquecolumn=?;




Thank you for the reply.  I assumed this was most likely the case, and
as I said in my original email, I was hoping to avoid having to do a
seperate select query for the sake of speed... especially since the
value I needed was so tantalizing close in the VDBE struct.


Shane,

Why do you want the rowid of the conflicting row?

I only ask because I suspect there may be a better way to do what you 
want to do.


Dennis Cote

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] UNIQUE constraint on column

[Shane Harrelson]

On 1/31/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:

"Shane Harrelson" <[EMAIL PROTECTED]> wrote:
> when i try to insert a row into a table that has a UNIQUE constraint
> on a column, and I get the SQLITE_CONSTRAINT result code because i'm
> inserting a duplicate value, is there anyway to determine the rowid of
> the conflict?
>
> looking at the internals of the VDBE, i found that the rowid of the
> conflicting row is pushed on top of the VDBE stack.
>
> if i'm willing to violate the interface, i can dereference the rowid
> from the internals of the VDBE struct.  i'd rather not do this... is
> there a more formal mechanism for getting this value without having do
> to do a separate "select" query?  something like
> sqlite3_last_insert_rowid() -- ie.  sqlite3_last_conflict_rowid()?
>

The official way to find the conflicting entry is to do a query.

  SELECT rowid FROM table WHERE uniquecolumn=?;

--
D. Richard Hipp  <[EMAIL PROTECTED]>



Thank you for the reply.  I assumed this was most likely the case, and
as I said in my original email, I was hoping to avoid having to do a
seperate select query for the sake of speed... especially since the
value I needed was so tantalizing close in the VDBE struct.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] UNIQUE constraint on column

[drh]

"Shane Harrelson" <[EMAIL PROTECTED]> wrote:
> when i try to insert a row into a table that has a UNIQUE constraint
> on a column, and I get the SQLITE_CONSTRAINT result code because i'm
> inserting a duplicate value, is there anyway to determine the rowid of
> the conflict?
> 
> looking at the internals of the VDBE, i found that the rowid of the
> conflicting row is pushed on top of the VDBE stack.
> 
> if i'm willing to violate the interface, i can dereference the rowid
> from the internals of the VDBE struct.  i'd rather not do this... is
> there a more formal mechanism for getting this value without having do
> to do a separate "select" query?  something like
> sqlite3_last_insert_rowid() -- ie.  sqlite3_last_conflict_rowid()?
> 

The official way to find the conflicting entry is to do a query.

   SELECT rowid FROM table WHERE uniquecolumn=?;

--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] UNIQUE Constraint but case sensitive

[Dan Kennedy]

> Hi,
> 
> I need to create a unique constraint on a column of type varchar, but it is
> not case sensitive by default. 
> Does any one know how to make a unique constraint case sensitive? 
> 

CREATE TABLE abc(a UNIQUE COLLATE NOCASE);

Bear in mind that the "NOCASE" collation sequence only knows about the 26
letters used in the English language.





__ 
Yahoo! Mail 
Stay connected, organized, and protected. Take the tour: 
http://tour.mail.yahoo.com/mailtour.html