[sqlite] Affinity of expression indexes

[Jens Alfke]

Consider
CREATE INDEX foo_idx ON tbl (myfunction(a));
where ‘myfunction’ is a deterministic C function I’ve registered with the 
SQLite connection (and ‘a’ is a column of ‘tbl’ of course.)

SQLite has no idea what data type(s) ‘myfunction’ returns, and it might well 
return different data types for different inputs. So unlike a column index, 
there’s no natural type affinity.

Question: In such an index, does SQLite assume no affinity and just compare the 
different types using the rules in section 4.1 of “Datatypes In SQLite Version 
3”? (I.e. numbers are compared as numbers and sort before any strings, etc.)

In that case, I’m confused how this interacts with ‘applying affinity’ in a 
query as described in section 4.2. For example, let’s say that ‘myfunction’ 
always returns a number. In that case, ‘foo_idx’ will be sorted numerically. 
But if I do a query like
SELECT * FROM tbl WHERE myfunction(a) > ‘dog’;
then the rules say that text affinity will be applied to the function call 
since the other side of the comparison is a string. In that case, the numbers 
it returns will be interpreted as strings. That leads to an entirely different 
sorting order, so the index can’t be used. But how does SQLite know that?

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

Re: [sqlite] sqlite_btreeinfo

[Keith Medcalf]

I have the extension compiled in.  When a database does not exist I get:

SQLite version 3.27.0 2018-12-10 01:48:29
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select * from sqlite_btreeinfo;
Error: not an error


However, when a database is loaded is works fine:

sqlite> .open tz.db
sqlite> select * from sqlite_btreeinfo;
table|sqlite_master|sqlite_master|1||1|23|1|1|
table|sqlite_stat1|sqlite_stat1|2|CREATE TABLE 
sqlite_stat1(tbl,idx,stat)|1|16|1|1|
table|sqlite_stat4|sqlite_stat4|3|CREATE TABLE 
sqlite_stat4(tbl,idx,neq,nlt,ndlt,sample)|1|1|1|1|
table|tz_geopoly_rowid|tz_geopoly_rowid|4|CREATE TABLE "tz_geopoly_rowid"(rowid 
INTEGER PRIMARY KEY,nodeno,a0,a1)|1|2028|507|2|
table|tz_geopoly_node|tz_geopoly_node|5|CREATE TABLE "tz_geopoly_node"(nodeno 
INTEGER PRIMARY KEY,data)|1|44|11|2|
table|tz_geopoly_parent|tz_geopoly_parent|6|CREATE TABLE 
"tz_geopoly_parent"(nodeno INTEGER PRIMARY KEY,parentnode)|1|33|1|1|
table|TZ_Version|TZ_Version|7|CREATE TABLE TZ_Version
(
Version text collate nocase,
PIPVersion  text collate nocase
)|1|2|1|1|
table|TZ_Countries|TZ_Countries|8|CREATE TABLE TZ_Countries
(
Codetext not null collate nocase primary key,
Country text not null collate nocase unique
) WITHOUT ROWID|0|34|2|2|
index|sqlite_autoindex_TZ_Countries_2|TZ_Countries|9||0|32|2|2|
table|TZ_Zones|TZ_Zones|10|CREATE TABLE TZ_Zones
(
ID  INTEGER PRIMARY KEY,
Country_Codetext collate nocase references TZ_Countries(Code),
Areatext collate nocase,
Locationtext collate nocase,
Citytext collate nocase,
Zonetext not null collate nocase unique
)|1|658|7|2|
index|sqlite_autoindex_TZ_Zones_1|TZ_Zones|11||0|720|4|2|
table|TZ_ZoneData|TZ_ZoneData|12|CREATE TABLE TZ_ZoneData
(
Zone_ID integer not null references TZ_Zones(ID),
Abbreviationtext collate nocase not null,
StartTime   integer not null,
Offset  integer not null,
isDST   integer not null
)|1|35088|204|2|
index|TZ_Country|TZ_Countries|13|CREATE INDEX TZ_Country on TZ_Countries 
(Country, Code)|0|32|2|2|
index|TZ_ZonesCountry|TZ_Zones|14|CREATE INDEX TZ_ZonesCountry  on TZ_Zones 
(Country_Code)|0|210|2|2|
index|TZ_ZonesArea|TZ_Zones|15|CREATE INDEX TZ_ZonesArea on TZ_Zones 
(Area)|0|508|2|2|
index|TZ_ZonesLocation|TZ_Zones|16|CREATE INDEX TZ_ZonesLocation on TZ_Zones 
(Location)|0|774|3|2|
index|TZ_ZonesCity|TZ_Zones|17|CREATE INDEX TZ_ZonesCity on TZ_Zones 
(City)|0|438|2|2|
index|TZ_ZoneDataID|TZ_ZoneData|18|CREATE INDEX TZ_ZoneDataID on TZ_ZoneData 
(Zone_ID)|0|36401|89|2|
index|TZ_ZoneStart|TZ_ZoneData|19|CREATE INDEX TZ_ZoneStart  on TZ_ZoneData 
(Zone_ID, StartTime, Offset, Abbreviation)|0|14616|84|3|
index|TZ_ZoneOffset|TZ_ZoneData|20|CREATE INDEX TZ_ZoneOffset on TZ_ZoneData 
(Zone_ID, StartTime + Offset, Offset)|0|35903|161|2|
sqlite>


---
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 Wout Mertens
>Sent: Wednesday, 12 December, 2018 13:41
>To: SQLite mailing list
>Subject: [sqlite] sqlite_btreeinfo
>
>I can't figure out how to get access to the sqlite_btreeinfo vtable
>that
>was added in 3.22 :-( The only documentation is the C file and there
>doesn't seem to be a compile flag for it.
>
>I went and downloaded the file from
>https://sqlite.org/src/artifact/4f0ebf278f46e68e, then compiled it on
>on
>macOS with
>
>$ gcc -g -fPIC -dynamiclib btreeinfo.c -o btreeinfo.dylib
>btreeinfo.c:414:5: warning: excess elements in struct initializer
>0/* xShadowName */
>^
>1 warning generated.
>
>and tried loading it but:
>
>sqlite> .load btreeinfo.dylib
>sqlite> select * from sqlite_btreeinfo;
>Error: no such table: sqlite_btreeinfo
>
>:-(
>
>Wout.
>___
>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] ALTER TABLE, modifying columns

[Thomas Kurz]

> I never would have allowed the recent
> enhancements to ALTER TABLE that broke it.

The enhancements made have been way overdue. Personally, I appreciate them very 
much and they are worth the "trouble". And I hope that the small problem does 
not prevent you from taking MODIFY COLUMN and DROP COLUMN into account. I think 
it should solve most problems as the quite complex procedure (which obviously 
seems to be improperly implemented quite often, not only from me *g*) would 
then be obsolete. And, in addition, SQLite could possibly even use a more 
efficient way for the modifications than just copying all the data. (In fact, a 
13th step "VACUUM" may be required in many cases, as dropping the table in step 
6 can lead to a large "hole" in the database.)

As far as the example is concerned, I will try to reproduce tomorrow.

Kind regards,
Thomas

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

[sqlite] sqlite_btreeinfo

[Wout Mertens]

I can't figure out how to get access to the sqlite_btreeinfo vtable that
was added in 3.22 :-( The only documentation is the C file and there
doesn't seem to be a compile flag for it.

I went and downloaded the file from
https://sqlite.org/src/artifact/4f0ebf278f46e68e, then compiled it on on
macOS with

$ gcc -g -fPIC -dynamiclib btreeinfo.c -o btreeinfo.dylib
btreeinfo.c:414:5: warning: excess elements in struct initializer
0/* xShadowName */
^
1 warning generated.

and tried loading it but:

sqlite> .load btreeinfo.dylib
sqlite> select * from sqlite_btreeinfo;
Error: no such table: sqlite_btreeinfo

:-(

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

Re: [sqlite] Mac: Users receive "database disk image is malformed" errors after restoring database from Time Machine backup

[Scott Perry]

On Dec 11, 2018, at 04:01, Daniel Alm  wrote:
> 
> Hi,
> 
> For the past half year we’ve been receiving reports from users who had 
> restored their SQLite-based databases from a Time Machine backup. Afterwards, 
> they would receive "database disk image is malformed” errors. The app also 
> backs up the user’s data “manually” to a ZIP file every week; those backups 
> seem to be working fine. We also haven’t received reports from other backup 
> tools causing issues. I have also suspected a bug in Time Machine, but it is 
> striking that the issues did seem to start occurring after an update to the 
> app (luckily, in fact, with the same update that also introduced the “manual” 
> backups).

Time Machine achieves eventual consistency by restarting when it detects that a 
file has changed since the backup was started. It does not have special 
provisions for SQLite database files.

Even if the scheduled backup never runs, the conditions under which a database 
would be captured in an utterly inconsistent state should be vanishingly rare. 
It would be most useful if you could share a representative database with 
Richard for analysis.

> Changes that we made to our setup in the update that coincided with the 
> errors occurring:
> - Upgraded SQLite from 3.21 to 3.24 (we have since reverted to 3.23.1 in 
> another update; no improvement).
> - Used memory mapping for read accesses via “PRAGMA mmap_size = 1073741824;” 
> (we have since reverted to “PRAGMA mmap_size = 0;” after reading 
> http://sqlite.1065341.n5.nabble.com/Re-Database-corruption-and-PRAGMA-fullfsync-on-macOS-td95366.html
>  
> ;
>  no improvement).
> - Using a secondary database via [ATTACH 
> DATABASE](https://www.sqlite.org/lang_attach.html 
> ) (although this also seems to occur 
> for users without such a database).
> 
> At this point, I am at a loss, especially given that SQLite should be fairly 
> robust against database corruption. While our app is running in the 
> background all the time, it is not very write-heavy (~ one transaction per 
> minute taking just a few milliseconds). Also, the app had been running fine 
> before the update for a long time without any reports of this issue. I might 
> be doing something wrong or have changed anything else, but I don’t know 
> what; if you have any ideas, let me know.
> 
> Any suggestions on what could be the culprit or what else I could try besides 
> downgrading all the way to SQLite 3.21 would be appreciated.

Out of curiosity, why aren't you using the SQLite that comes with the OS?

Scott

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

Re: [sqlite] ALTER TABLE, modifying columns

[Richard Hipp]

On 12/12/18, Thomas Kurz  wrote:
> This doesn't work either. The error now occurs in the "ALTER TABLE" line,
> which is correct as the table "x" being refered to doesn't exist that
> moment. Tested with both 3.25.2 and 3.26.

Can you please post a script showing us exactly what you are trying to
do when you get the error?

-- 
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] ALTER TABLE, modifying columns

[Richard Hipp]

On 12/12/18, Thomas Kurz  wrote:
>
> Btw, has the "correct vs. incorrect" table that you've cited already been
> there before release 3.25?

The procedure description is unchanged for many years.  I added the
"Caution:" section recently, because a lot of people have been having
the same problem you are currently having.  Had I known in advance
that so many people were doing the "incorrect" procedure for making
schema modifications, I never would have allowed the recent
enhancements to ALTER TABLE that broke it.  But I didn't know.  And
that is now water under the bridge.

-- 
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] Mac: Users receive "database disk image is malformed" errors after restoring database from Time Machine backup

[Simon Slavin]

On 12 Dec 2018, at 2:59pm, Olivier Mascia  wrote:

> When TimeMachine makes copies of the files, the database file and -wal file 
> will be copied at different points in time, albeit they should be copied from 
> a filesystem snapshot, so should be consistent to each other. But this should 
> be checked, and verified for different macOS versions.

Yes.  My initial thought was that the journal file would be out-of-sync with 
the database file.  Though I could not guess which would be earlier and which 
later.

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

Re: [sqlite] How can custom tokenizer tell it's parsing a search string?

[Jens Alfke]

Thanks for the reply, Dan!

> On Dec 12, 2018, at 7:08 AM, Dan Kennedy  wrote:
> 
> Leaving stop words in while parsing queries won't quite work anyway. If your 
> tokenizer returns "the" when parsing a query, FTS3/4 will search for "the" in 
> the index. And it won't be there if the tokenizer used for parsing documents 
> stripped it out.

I was only talking about leaving them in when followed immediately by a “*” — 
so it would preserve “the*” but not “the”. Then FTS4 will interpret “the*” as a 
prefix match, not the word “the”.

> I think your best options might be to switch to FTS5

I haven’t looked into how hard it would be to switch to FTS5. I recall that 
when I started writing this code a few years ago, FTS5 had some issues or 
limitations that led me to use FTS4 instead.

Also, there are by now many databases out in the field that have FTS4 
tables/indexes in them. If I switch to FTS5 will those be upgraded, or do I 
need to do so manually?

>  or to write a tokenizer smart enough to remove the AND or other syntax 
> tokens when required.

Not sure what you mean by this — the “when required” part is the sticking 
point, which is the reason I posted.

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

Re: [sqlite] ALTER TABLE, modifying columns

[Thomas Kurz]

This doesn't work either. The error now occurs in the "ALTER TABLE" line, which 
is correct as the table "x" being refered to doesn't exist that moment. Tested 
with both 3.25.2 and 3.26.

Btw, has the "correct vs. incorrect" table that you've cited already been there 
before release 3.25?


- Original Message - 
From: Shawn Wagner 
To: SQLite mailing list 
Sent: Wednesday, December 12, 2018, 18:02:54
Subject: [sqlite] ALTER TABLE, modifying columns

You're using a workflow that https://www.sqlite.org/lang_altertable.html
explicitly calls out as incorrect and error prone...

Try to create a new table, copy data over, drop the original and then
rename the new one to see if that fixes the issue.

On Wed, Dec 12, 2018, 8:54 AM Thomas Kurz  Dear all,

> I don't know whether the behavior is intentional or a bug, so let me
> describe it (occurs since 3.25):

> Due to the lack of ALTER TABLE MODIFY COLUMN, I use the following
> construction:

> PRAGMA foreign_keys=0
> BEGIN TRANSACTION
> ALTER TABLE x RENAME TO x_old
> CREATE TABLE IF NOT EXISTS x (... new declaration ...)
> INSERT INTO x (...) SELECT ... FROM x_old
> DROP TABLE x_old
>  more to do here ...
> COMMIT
> PRAGMA foreign_keys=1

> Usually, this works fine, but now I have a VIEW that references table x,
> which leads to an error "error in view ...: no such table: main.x_old".

> Of course, this happens because renaming x to x_old also changes the
> view's reference from x to x_old which is not intended in this case.

> As a workaround, I have now added "PRAGMA legacy_alter_table" before and
> after the transaction.

> The behavior makes modifying columns even more complicated, so I'd like to
> beg for an ALTER TABLE MODIFY COLUMN statement once more ;-))

> Kind regards,
> Thomas

> ___
> 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

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

Re: [sqlite] Mac: Users receive "database disk image is malformed" errors after restoring database from Time Machine backup

[Rob Willett]

Whilst Time Machine does not do snapshots how enterprise storage do 
snapshots, literally a freeze and recovery point. Time Machine does make 
backups suitable for booting from. Apple considers Time Machine suitable 
for home use backups.


You can backup with TimeMachine and boot from it. My personal experience 
of TM is that its flaky and unreliable and we use Carbon Copy Cloner AND 
Chronosynd to get to backups that I trust to work. YMMV


However we are going off the point from the OP. I personally think TM is 
to blame and would advice the users to check the TM setup and experiment 
with delaying the WAL backup just to see what would happen.


Rob

On 12 Dec 2018, at 16:58, Peter da Silva wrote:

Apple uses Sqlite in a number of applications, including Apple Mail, 
so
they have to have some kind of accommodation for saving sqlite 
databases.


The Time Machine patent does not describe using file system snapshots:


*"An algorithm or other monitoring can be used to detect changes that 
occur

during the backup operation in order to maintain consistency between
related data in the backup. The back up can be performed again for 
related

data that was modified during prior backup operation. *

*"In general, in one aspect, a method is provided. A backup operation 
of
data including a plurality of related items is initiated. 
Modifications to

one or more items of the plurality of related items are monitored for
during the backup operation. The backup operation is completed. If a
modification occurred to one or more items, a second backup operation 
is

performed for the modified items."*

This does not seem to authoritatively state that multiple files will 
be

backed up consistently.

On Wed, Dec 12, 2018 at 9:06 AM Keith Medcalf  
wrote:




I know nothing about "Time Machine", but does it copy the entire
filesystem in (at least) "crash consistent" state?

---
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 Daniel Alm
Sent: Tuesday, 11 December, 2018 05:02
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Mac: Users receive "database disk image is
malformed" errors after restoring database from Time Machine backup

Hi,

For the past half year we’ve been receiving reports from users who
had restored their SQLite-based databases from a Time Machine 
backup.

Afterwards, they would receive "database disk image is malformed”
errors. The app also backs up the user’s data “manually” to a 
ZIP

file every week; those backups seem to be working fine. We also
haven’t received reports from other backup tools causing issues. I
have also suspected a bug in Time Machine, but it is striking that
the issues did seem to start occurring after an update to the app
(luckily, in fact, with the same update that also introduced the
“manual” backups).

Changes that we made to our setup in the update that coincided with
the errors occurring:
- Upgraded SQLite from 3.21 to 3.24 (we have since reverted to 
3.23.1

in another update; no improvement).
- Used memory mapping for read accesses via “PRAGMA mmap_size =
1073741824;” (we have since reverted to “PRAGMA mmap_size = 
0;” after

reading http://sqlite.1065341.n5.nabble.com/Re-Database-corruption-
and-PRAGMA-fullfsync-on-macOS-td95366.html
; no improvement).
- Using a secondary database via [ATTACH
DATABASE](https://www.sqlite.org/lang_attach.html
) (although this also seems
to occur for users without such a database).

At this point, I am at a loss, especially given that SQLite should 
be

fairly robust against database corruption. While our app is running
in the background all the time, it is not very write-heavy (~ one
transaction per minute taking just a few milliseconds). Also, the 
app

had been running fine before the update for a long time without any
reports of this issue. I might be doing something wrong or have
changed anything else, but I don’t know what; if you have any 
ideas,

let me know.

Any suggestions on what could be the culprit or what else I could 
try

besides downgrading all the way to SQLite 3.21 would be appreciated.

Thanks,
Daniel Alm

P.S.: Our database currently uses the following PRAGMAs:

PRAGMA mmap_size = 0;
PRAGMA page_size = 4096;
PRAGMA cache_size = -10240;
PRAGMA foreign_keys = ON;
PRAGMA journal_size_limit = 8388608;
PRAGMA checkpoint_fullfsync = 1;
PRAGMA wal_autocheckpoint = 2048;
PRAGMA journal_mode = WAL;

Happy to provide any more details as needed.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users




___
sqlite-users mailing 

Re: [sqlite] Index with calculated value not covering?

[Wout Mertens]

So the missing optimization is to use a covering index vs just the index,
right? Are there any plans in that direction? Or maybe a way to hint it?

And, to replace this functionality, would it be best to add a column and a
trigger that calculates the length on insert or update? Or are there better
ways

Wout.


On Wed, Dec 12, 2018 at 4:54 PM Richard Hipp  wrote:

> On 12/12/18, Wout Mertens  wrote:
> > sqlite> CREATE TABLE "history"("v" INTEGER PRIMARY KEY AUTOINCREMENT,
> > "type" TEXT, "data" JSON);
> > sqlite> CREATE INDEX "type_size" on history(type, length(data));
> > sqlite> explain query plan select type from history group by type;
> > QUERY PLAN
> > `--SCAN TABLE history USING COVERING INDEX type_size
> > sqlite> explain query plan select type, length(data) from history group
> by
> > type;
> > QUERY PLAN
> > `--SCAN TABLE history USING INDEX type_size
> >
> > I would expect the latter query to use COVERING index. Is there a way to
> > hint this?
> >
>
> The query planner does not currently implement that optimization.
>
> --
> 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ALTER TABLE, modifying columns

[Shawn Wagner]

You're using a workflow that https://www.sqlite.org/lang_altertable.html
explicitly calls out as incorrect and error prone...

Try to create a new table, copy data over, drop the original and then
rename the new one to see if that fixes the issue.

On Wed, Dec 12, 2018, 8:54 AM Thomas Kurz  Dear all,
>
> I don't know whether the behavior is intentional or a bug, so let me
> describe it (occurs since 3.25):
>
> Due to the lack of ALTER TABLE MODIFY COLUMN, I use the following
> construction:
>
> PRAGMA foreign_keys=0
> BEGIN TRANSACTION
> ALTER TABLE x RENAME TO x_old
> CREATE TABLE IF NOT EXISTS x (... new declaration ...)
> INSERT INTO x (...) SELECT ... FROM x_old
> DROP TABLE x_old
>  more to do here ...
> COMMIT
> PRAGMA foreign_keys=1
>
> Usually, this works fine, but now I have a VIEW that references table x,
> which leads to an error "error in view ...: no such table: main.x_old".
>
> Of course, this happens because renaming x to x_old also changes the
> view's reference from x to x_old which is not intended in this case.
>
> As a workaround, I have now added "PRAGMA legacy_alter_table" before and
> after the transaction.
>
> The behavior makes modifying columns even more complicated, so I'd like to
> beg for an ALTER TABLE MODIFY COLUMN statement once more ;-))
>
> Kind regards,
> Thomas
>
> ___
> 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] Mac: Users receive "database disk image is malformed" errors after restoring database from Time Machine backup

[Peter da Silva]

Apple uses Sqlite in a number of applications, including Apple Mail, so
they have to have some kind of accommodation for saving sqlite databases.

The Time Machine patent does not describe using file system snapshots:


*"An algorithm or other monitoring can be used to detect changes that occur
during the backup operation in order to maintain consistency between
related data in the backup. The back up can be performed again for related
data that was modified during prior backup operation. *

*"In general, in one aspect, a method is provided. A backup operation of
data including a plurality of related items is initiated. Modifications to
one or more items of the plurality of related items are monitored for
during the backup operation. The backup operation is completed. If a
modification occurred to one or more items, a second backup operation is
performed for the modified items."*

This does not seem to authoritatively state that multiple files will be
backed up consistently.

On Wed, Dec 12, 2018 at 9:06 AM Keith Medcalf  wrote:

>
> I know nothing about "Time Machine", but does it copy the entire
> filesystem in (at least) "crash consistent" state?
>
> ---
> 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 Daniel Alm
> >Sent: Tuesday, 11 December, 2018 05:02
> >To: sqlite-users@mailinglists.sqlite.org
> >Subject: [sqlite] Mac: Users receive "database disk image is
> >malformed" errors after restoring database from Time Machine backup
> >
> >Hi,
> >
> >For the past half year we’ve been receiving reports from users who
> >had restored their SQLite-based databases from a Time Machine backup.
> >Afterwards, they would receive "database disk image is malformed”
> >errors. The app also backs up the user’s data “manually” to a ZIP
> >file every week; those backups seem to be working fine. We also
> >haven’t received reports from other backup tools causing issues. I
> >have also suspected a bug in Time Machine, but it is striking that
> >the issues did seem to start occurring after an update to the app
> >(luckily, in fact, with the same update that also introduced the
> >“manual” backups).
> >
> >Changes that we made to our setup in the update that coincided with
> >the errors occurring:
> >- Upgraded SQLite from 3.21 to 3.24 (we have since reverted to 3.23.1
> >in another update; no improvement).
> >- Used memory mapping for read accesses via “PRAGMA mmap_size =
> >1073741824;” (we have since reverted to “PRAGMA mmap_size = 0;” after
> >reading http://sqlite.1065341.n5.nabble.com/Re-Database-corruption-
> >and-PRAGMA-fullfsync-on-macOS-td95366.html
> > >PRAGMA-fullfsync-on-macOS-td95366.html>; no improvement).
> >- Using a secondary database via [ATTACH
> >DATABASE](https://www.sqlite.org/lang_attach.html
> >) (although this also seems
> >to occur for users without such a database).
> >
> >At this point, I am at a loss, especially given that SQLite should be
> >fairly robust against database corruption. While our app is running
> >in the background all the time, it is not very write-heavy (~ one
> >transaction per minute taking just a few milliseconds). Also, the app
> >had been running fine before the update for a long time without any
> >reports of this issue. I might be doing something wrong or have
> >changed anything else, but I don’t know what; if you have any ideas,
> >let me know.
> >
> >Any suggestions on what could be the culprit or what else I could try
> >besides downgrading all the way to SQLite 3.21 would be appreciated.
> >
> >Thanks,
> >Daniel Alm
> >
> >P.S.: Our database currently uses the following PRAGMAs:
> >
> >PRAGMA mmap_size = 0;
> >PRAGMA page_size = 4096;
> >PRAGMA cache_size = -10240;
> >PRAGMA foreign_keys = ON;
> >PRAGMA journal_size_limit = 8388608;
> >PRAGMA checkpoint_fullfsync = 1;
> >PRAGMA wal_autocheckpoint = 2048;
> >PRAGMA journal_mode = WAL;
> >
> >Happy to provide any more details as needed.
> >___
> >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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] ALTER TABLE, modifying columns

[Thomas Kurz]

Dear all,

I don't know whether the behavior is intentional or a bug, so let me describe 
it (occurs since 3.25):

Due to the lack of ALTER TABLE MODIFY COLUMN, I use the following construction:

PRAGMA foreign_keys=0
BEGIN TRANSACTION
ALTER TABLE x RENAME TO x_old
CREATE TABLE IF NOT EXISTS x (... new declaration ...)
INSERT INTO x (...) SELECT ... FROM x_old
DROP TABLE x_old
 more to do here ...
COMMIT
PRAGMA foreign_keys=1

Usually, this works fine, but now I have a VIEW that references table x, which 
leads to an error "error in view ...: no such table: main.x_old".

Of course, this happens because renaming x to x_old also changes the view's 
reference from x to x_old which is not intended in this case.

As a workaround, I have now added "PRAGMA legacy_alter_table" before and after 
the transaction.

The behavior makes modifying columns even more complicated, so I'd like to beg 
for an ALTER TABLE MODIFY COLUMN statement once more ;-))

Kind regards,
Thomas

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

Re: [sqlite] [EXTERNAL] Re: SQLITE gives incorrect results for 'NOT IN' query if partial index exists

[Wout Mertens]

Well since this thread is very off topic anyway: I think that would be
wildly specific spam, I think she genuinely wanted to unsubscribe.

Also, my message to Luuk was supposed to be unicast. I even forwarded the
mail and typed his address manually but somehow gmail thought it opportune
to keep the mailing list in copy ¯\_(ツ)_/¯

Wout.


On Wed, Dec 12, 2018 at 3:44 PM Richard Hipp  wrote:

> On 12/12/18, Hick Gunter  wrote:
> > Serves you right for spying on your boyfriend ;P
> >
> > Check the link at the bottom of each and every message from the list for
> the
> > way to unsubscribe. We don't enjoy the prospect of free floating pieces
> of
> > brain on this list ;)
>
> I think those two messages from Ms. Sexton are spam.  The sender is
> not a member of the mailing list and so the messages went to
> moderation.  I approved them because they referenced a valid thread,
> but I think I should have read more closely before clicking the
> approve button.  Sorry.
>
> --
> 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Index with calculated value not covering?

[Richard Hipp]

On 12/12/18, Wout Mertens  wrote:
> sqlite> CREATE TABLE "history"("v" INTEGER PRIMARY KEY AUTOINCREMENT,
> "type" TEXT, "data" JSON);
> sqlite> CREATE INDEX "type_size" on history(type, length(data));
> sqlite> explain query plan select type from history group by type;
> QUERY PLAN
> `--SCAN TABLE history USING COVERING INDEX type_size
> sqlite> explain query plan select type, length(data) from history group by
> type;
> QUERY PLAN
> `--SCAN TABLE history USING INDEX type_size
>
> I would expect the latter query to use COVERING index. Is there a way to
> hint this?
>

The query planner does not currently implement that optimization.

-- 
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] How can custom tokenizer tell it's parsing a search string?

[Dan Kennedy]

On 12/12/2018 03:37 AM, Jens Alfke wrote:

Is there any way for a custom FTS4 tokenizer to know when it’s
tokenizing a search string (the argument of a MATCH expression), as
opposed to text to be indexed?

Here’s my problem: I’ve implemented a custom tokenizer that skips
“stop words” (noise words, like “the” and “a” in English.) It works
well. But I’ve just gotten a bug report that some search strings with
wild-cards don’t work. For example, “mo* AND the*” would be expected
to match text containing the words “Moog” and “theremin”, but instead
the query fails with the SQLite error "malformed MATCH expression:
[mo* AND the*]”.

The reason for the error is that when the query runs, FTS4 uses my
tokenizer to break the search string into words. My tokenizer skips
“the” because it’s a stop word, so the sequence of tokens FTS4 gets
is “mo”, “*”, “AND”, “*” … which is invalid since there’s no prefix
before the second “*”.

I can fix this by preserving stop-words when the tokenizer is being
used to scan the search string. But I can’t find any way for the
tokenizer to tell the difference! It’s the same tokenizer instance
used for indexing, and the SQLite function getNextToken opens it in
the normal way and calls its xNext function.



I don't think there is any way to tell with FTS3/4. FTS5 passes a 
parameter to the tokenizer to indicate this (the mask of 
FTS5_TOKENIZER_* flags), but FTS3/4 does not. But you wouldn't have this 
problem with FTS5 anyhow, because it handles the AND or "*" syntax 
before passing whatever is left to the tokenizer.


  https://sqlite.org/fts5.html#custom_tokenizers

Leaving stop words in while parsing queries won't quite work anyway. If 
your tokenizer returns "the" when parsing a query, FTS3/4 will search 
for "the" in the index. And it won't be there if the tokenizer used for 
parsing documents stripped it out.


I think your best options might be to switch to FTS5 or to write a 
tokenizer smart enough to remove the AND or other syntax tokens when 
required.


Dan.





The best workaround I can think of is to make the tokenizer preserve
a stop-word when it’s followed by a “*” … but there are contexts
where this can happen in regular text being indexed, when the “*” is
a footnote marker or the end of a Markdown emphasis sequence.

—Jens ___ 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] Mac: Users receive "database disk image is malformed" errors after restoring database from Time Machine backup

[Keith Medcalf]

I know nothing about "Time Machine", but does it copy the entire filesystem in 
(at least) "crash consistent" state?  

---
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 Daniel Alm
>Sent: Tuesday, 11 December, 2018 05:02
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] Mac: Users receive "database disk image is
>malformed" errors after restoring database from Time Machine backup
>
>Hi,
>
>For the past half year we’ve been receiving reports from users who
>had restored their SQLite-based databases from a Time Machine backup.
>Afterwards, they would receive "database disk image is malformed”
>errors. The app also backs up the user’s data “manually” to a ZIP
>file every week; those backups seem to be working fine. We also
>haven’t received reports from other backup tools causing issues. I
>have also suspected a bug in Time Machine, but it is striking that
>the issues did seem to start occurring after an update to the app
>(luckily, in fact, with the same update that also introduced the
>“manual” backups).
>
>Changes that we made to our setup in the update that coincided with
>the errors occurring:
>- Upgraded SQLite from 3.21 to 3.24 (we have since reverted to 3.23.1
>in another update; no improvement).
>- Used memory mapping for read accesses via “PRAGMA mmap_size =
>1073741824;” (we have since reverted to “PRAGMA mmap_size = 0;” after
>reading http://sqlite.1065341.n5.nabble.com/Re-Database-corruption-
>and-PRAGMA-fullfsync-on-macOS-td95366.html
>PRAGMA-fullfsync-on-macOS-td95366.html>; no improvement).
>- Using a secondary database via [ATTACH
>DATABASE](https://www.sqlite.org/lang_attach.html
>) (although this also seems
>to occur for users without such a database).
>
>At this point, I am at a loss, especially given that SQLite should be
>fairly robust against database corruption. While our app is running
>in the background all the time, it is not very write-heavy (~ one
>transaction per minute taking just a few milliseconds). Also, the app
>had been running fine before the update for a long time without any
>reports of this issue. I might be doing something wrong or have
>changed anything else, but I don’t know what; if you have any ideas,
>let me know.
>
>Any suggestions on what could be the culprit or what else I could try
>besides downgrading all the way to SQLite 3.21 would be appreciated.
>
>Thanks,
>Daniel Alm
>
>P.S.: Our database currently uses the following PRAGMAs:
>
>PRAGMA mmap_size = 0;
>PRAGMA page_size = 4096;
>PRAGMA cache_size = -10240;
>PRAGMA foreign_keys = ON;
>PRAGMA journal_size_limit = 8388608;
>PRAGMA checkpoint_fullfsync = 1;
>PRAGMA wal_autocheckpoint = 2048;
>PRAGMA journal_mode = WAL;
>
>Happy to provide any more details as needed.
>___
>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] Mac: Users receive "database disk image is malformed" errors after restoring database from Time Machine backup

[Rob Willett]

Also are they using Time Machine on a networked drive?

Whilst Time Machine was not supposed to work across networks, people 
have made it work using 3rd party software. I know because we tried it 
for a laugh and abandoned it (and Time Machine) as it was wholly 
unreliable.


However I think the WAL commit (or uncommit) is a more likely scenario.

Rob

On 12 Dec 2018, at 15:00, R Smith wrote:


On 2018/12/12 4:48 PM, Richard Hipp wrote:

On 12/11/18, Daniel Alm  wrote:
Any suggestions on what could be the culprit or what else I could 
try

besides downgrading all the way to SQLite 3.21 would be appreciated.


Nothing about SQLite has changed that should make a difference here.

Do you know if the corruption is occurring when TimeMachine makes its
backup, or is occurring when the backed up database is restored?  Can
you capture some unrestored TimeMachine backups to see if they are
corrupt?


My best guess here is that TimeMachine somehow captures the sqlite DB 
files a few milliseconds apart "sometimes" so that a journal file that 
has just been committed is in the TimeMachine backup captured still in 
its uncommitted state while the DB itself is in the committed state 
already (or perhaps vice-versa).


This theory however makes no sense unless either the journal mode in 
SQLite has changed (either via update or pragma change by user), or 
TimeMachine itself has changed its ways since when things used to work 
correctly.




___
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] Mac: Users receive "database disk image is malformed" errors after restoring database from Time Machine backup

[R Smith]

On 2018/12/12 4:48 PM, Richard Hipp wrote:

On 12/11/18, Daniel Alm  wrote:

Any suggestions on what could be the culprit or what else I could try
besides downgrading all the way to SQLite 3.21 would be appreciated.


Nothing about SQLite has changed that should make a difference here.

Do you know if the corruption is occurring when TimeMachine makes its
backup, or is occurring when the backed up database is restored?  Can
you capture some unrestored TimeMachine backups to see if they are
corrupt?


My best guess here is that TimeMachine somehow captures the sqlite DB 
files a few milliseconds apart "sometimes" so that a journal file that 
has just been committed is in the TimeMachine backup captured still in 
its uncommitted state while the DB itself is in the committed state 
already (or perhaps vice-versa).


This theory however makes no sense unless either the journal mode in 
SQLite has changed (either via update or pragma change by user), or 
TimeMachine itself has changed its ways since when things used to work 
correctly.




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

Re: [sqlite] Mac: Users receive "database disk image is malformed" errors after restoring database from Time Machine backup

[Olivier Mascia]

Dear Daniel,

I'm extracting two points out of your report:

> Le 11 déc. 2018 à 13:01, Daniel Alm  a écrit :
> 
> While our app is running in the background all the time, it is not very 
> write-heavy (~ one transaction per minute taking just a few milliseconds).
> PRAGMA journal_mode = WAL;

When TimeMachine makes copies of the files, the database file and -wal file 
will be copied at different points in time, albeit they should be copied from a 
filesystem snapshot, so should be consistent to each other. But this should be 
checked, and verified for different macOS versions.

a) If only the database file is restored (and not its -wal file) or if both 
files are restored (but were not in synch when backed up) this might, rightly, 
trigger the database is malformed message.

As said on page https://www.sqlite.org/wal.html:

"The WAL file is part of the persistent state of the database and should be 
kept with the database if the database is copied or moved. If a database file 
is separated from its WAL file, then transactions that were previously 
committed to the database might be lost, or the database file might become 
corrupted. The only safe way to remove a WAL file is to open the database file 
using one of the sqlite3_open() interfaces then immediately close the database 
using sqlite3_close()."


b) Maybe using WAL is not really useful for your use-case, if really there is 
mostly one very short write transaction per minute.  The default journal mode 
might be perfectly adequate. But surely you chose WAL mode for some specific 
reason.  I just don't instantly spot which one from your report.


-- 
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia


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

[sqlite] Index with calculated value not covering?

[Wout Mertens]

sqlite> CREATE TABLE "history"("v" INTEGER PRIMARY KEY AUTOINCREMENT,
"type" TEXT, "data" JSON);
sqlite> CREATE INDEX "type_size" on history(type, length(data));
sqlite> explain query plan select type from history group by type;
QUERY PLAN
`--SCAN TABLE history USING COVERING INDEX type_size
sqlite> explain query plan select type, length(data) from history group by
type;
QUERY PLAN
`--SCAN TABLE history USING INDEX type_size

I would expect the latter query to use COVERING index. Is there a way to
hint this?

(I checked the actual opcodes and indeed it reads the table column and runs
length again)

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

Re: [sqlite] Mac: Users receive "database disk image is malformed" errors after restoring database from Time Machine backup

[Richard Hipp]

On 12/11/18, Daniel Alm  wrote:
> Any suggestions on what could be the culprit or what else I could try
> besides downgrading all the way to SQLite 3.21 would be appreciated.
>

Nothing about SQLite has changed that should make a difference here.

Do you know if the corruption is occurring when TimeMachine makes its
backup, or is occurring when the backed up database is restored?  Can
you capture some unrestored TimeMachine backups to see if they are
corrupt?

Can you send us one of your corrupted database files for analysis?

-- 
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] [EXTERNAL] Re: SQLITE gives incorrect results for 'NOT IN' query if partial index exists

[Richard Hipp]

On 12/12/18, Hick Gunter  wrote:
> Serves you right for spying on your boyfriend ;P
>
> Check the link at the bottom of each and every message from the list for the
> way to unsubscribe. We don't enjoy the prospect of free floating pieces of
> brain on this list ;)

I think those two messages from Ms. Sexton are spam.  The sender is
not a member of the mailing list and so the messages went to
moderation.  I approved them because they referenced a valid thread,
but I think I should have read more closely before clicking the
approve button.  Sorry.

-- 
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] [EXTERNAL] Re: SQLITE gives incorrect results for 'NOT IN' query if partial index exists

[Hick Gunter]

Serves you right for spying on your boyfriend ;P

Check the link at the bottom of each and every message from the list for the 
way to unsubscribe. We don't enjoy the prospect of free floating pieces of 
brain on this list ;)

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Nicole Sexton
Gesendet: Mittwoch, 12. Dezember 2018 11:09
An: SQLite mailing list 
Betreff: [EXTERNAL] Re: [sqlite] SQLITE gives incorrect results for 'NOT IN' 
query if partial index exists

I'm very confused as I never sent that e-mail and no longer want to receive 
these e-mails. I signed up a long time ago to try to figure out what my 
boyfriend was up to and decrypt his messages. I somehow stubbled upon here 
joined the mailing list like an idiot. Like I have 0 clue hoe any of this 
works. I'd be forever thankful to not get these e-mails and longer. Someone 
must be using my e-mail. ugh. Any suggestions?? I mean I would love to learn 
all this but my brain may explode.


> On Dec 11, 2018, at 2:17 PM, Luuk  wrote:
>
>
> On 11-12-2018 10:09, Wout Mertens wrote:
>> Hi Luuk,
>>
>> Not sure if you realize this, but your email comes over as very
>> aggressive, and if there's one person on this mailing list that
>> doesn't deserve that, it's dr Hipp.
>>
>> In particular, the quotes around forgot seem to imply that it was
>> forgotten on purpose.
>>
>> Personally, I would have worded it as "I looked at the test and I
>> wonder if this test case is addressed". Email communication is easy
>> to misconstrue…
>>
>> Cheers,
>>
>> Wout.
>>
>
> Sorry, again sorry,
>
> it must have been an interlingual misphrased wording of /me  ;)
>
> ___
> 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


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE gives incorrect results for 'NOT IN' query if partial index exists

[Nicole Sexton]

I'm very confused as I never sent that e-mail and no longer want to receive 
these e-mails. I signed up a long time ago to try to figure out what my 
boyfriend was up to and decrypt his messages. I somehow stubbled upon here 
joined the mailing list like an idiot. Like I have 0 clue hoe any of this 
works. I'd be forever thankful to not get these e-mails and longer. Someone 
must be using my e-mail. ugh. Any suggestions?? I mean I would love to learn 
all this but my brain may explode. 


> On Dec 11, 2018, at 2:17 PM, Luuk  wrote:
> 
> 
> On 11-12-2018 10:09, Wout Mertens wrote:
>> Hi Luuk,
>> 
>> Not sure if you realize this, but your email comes over as very aggressive,
>> and if there's one person on this mailing list that doesn't deserve that,
>> it's dr Hipp.
>> 
>> In particular, the quotes around forgot seem to imply that it was forgotten
>> on purpose.
>> 
>> Personally, I would have worded it as "I looked at the test and I wonder if
>> this test case is addressed". Email communication is easy to misconstrue…
>> 
>> Cheers,
>> 
>> Wout.
>> 
> 
> Sorry, again sorry,
> 
> it must have been an interlingual misphrased wording of /me  ;)
> 
> ___
> 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] SQLITE gives incorrect results for 'NOT IN' query if partial index exists

[Nicole Sexton]

I don't even know what interlingual means 
> On Dec 11, 2018, at 2:17 PM, Luuk  wrote:
> 
> 
> On 11-12-2018 10:09, Wout Mertens wrote:
>> Hi Luuk,
>> 
>> Not sure if you realize this, but your email comes over as very aggressive,
>> and if there's one person on this mailing list that doesn't deserve that,
>> it's dr Hipp.
>> 
>> In particular, the quotes around forgot seem to imply that it was forgotten
>> on purpose.
>> 
>> Personally, I would have worded it as "I looked at the test and I wonder if
>> this test case is addressed". Email communication is easy to misconstrue…
>> 
>> Cheers,
>> 
>> Wout.
>> 
> 
> Sorry, again sorry,
> 
> it must have been an interlingual misphrased wording of /me  ;)
> 
> ___
> 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

[sqlite] Mac: Users receive "database disk image is malformed" errors after restoring database from Time Machine backup

[Daniel Alm]

Hi,

For the past half year we’ve been receiving reports from users who had restored 
their SQLite-based databases from a Time Machine backup. Afterwards, they would 
receive "database disk image is malformed” errors. The app also backs up the 
user’s data “manually” to a ZIP file every week; those backups seem to be 
working fine. We also haven’t received reports from other backup tools causing 
issues. I have also suspected a bug in Time Machine, but it is striking that 
the issues did seem to start occurring after an update to the app (luckily, in 
fact, with the same update that also introduced the “manual” backups).

Changes that we made to our setup in the update that coincided with the errors 
occurring:
- Upgraded SQLite from 3.21 to 3.24 (we have since reverted to 3.23.1 in 
another update; no improvement).
- Used memory mapping for read accesses via “PRAGMA mmap_size = 1073741824;” 
(we have since reverted to “PRAGMA mmap_size = 0;” after reading 
http://sqlite.1065341.n5.nabble.com/Re-Database-corruption-and-PRAGMA-fullfsync-on-macOS-td95366.html
 
;
 no improvement).
- Using a secondary database via [ATTACH 
DATABASE](https://www.sqlite.org/lang_attach.html 
) (although this also seems to occur 
for users without such a database).

At this point, I am at a loss, especially given that SQLite should be fairly 
robust against database corruption. While our app is running in the background 
all the time, it is not very write-heavy (~ one transaction per minute taking 
just a few milliseconds). Also, the app had been running fine before the update 
for a long time without any reports of this issue. I might be doing something 
wrong or have changed anything else, but I don’t know what; if you have any 
ideas, let me know.

Any suggestions on what could be the culprit or what else I could try besides 
downgrading all the way to SQLite 3.21 would be appreciated.

Thanks,
Daniel Alm

P.S.: Our database currently uses the following PRAGMAs:

PRAGMA mmap_size = 0;
PRAGMA page_size = 4096;
PRAGMA cache_size = -10240;
PRAGMA foreign_keys = ON;
PRAGMA journal_size_limit = 8388608;
PRAGMA checkpoint_fullfsync = 1;
PRAGMA wal_autocheckpoint = 2048;
PRAGMA journal_mode = WAL;

Happy to provide any more details as needed.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Again https://www3.sqlite.org is down

[Richard Hipp]

On 12/12/18, Domingo Alvarez Duarte  wrote:
> Hello Richard !
>
> It seems that https://www3.sqlite.org is down for a few days.

Fixed now.  Thanks for the report.

-- 
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

[sqlite] Again https://www3.sqlite.org is down

[Domingo Alvarez Duarte]

Hello Richard !

It seems that https://www3.sqlite.org is down for a few days.

Cheers !

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