Re: [sqlite] UNIQUE constraint violation

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.

Re: [sqlite] UNIQUE constraint violation

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

Re: [sqlite] UNIQUE constraint violation

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,

Re: [sqlite] UNIQUE constraint violation

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,

Re: [sqlite] UNIQUE constraint violation

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

Re: [sqlite] UNIQUE constraint violation

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

Re: [sqlite] UNIQUE constraint violation

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

Re: [sqlite] UNIQUE constraint violation

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 udatetime

Re: [sqlite] UNIQUE constraint violation

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

Re: [sqlite] UNIQUE constraint violation

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

Re: [sqlite] UNIQUE constraint violation

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]

Re: [sqlite] UNIQUE constraint violation

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

Re: [sqlite] UNIQUE constraint violation

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

Re: [sqlite] UNIQUE constraint violation

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

Re: [sqlite] UNIQUE constraint violation

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

Re: [sqlite] UNIQUE constraint violation

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.

Re: [sqlite] UNIQUE constraint violation

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

Re: [sqlite] UNIQUE constraint violation

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;

Re: [sqlite] UNIQUE constraint violation

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