Re: [sqlite] WAL mode readonly errors to SELECT statements

[Simon Slavin]

On 24 Jan 2019, at 2:37am, Robert Searle  wrote:

> occasionally get either
> SQLITE_READONLY_RECOVERY or SQLITE_READONLY_CANTINIT responses to select
> statements

No programmer should be seeing these.  They indicate low-level errors that 
cannot be handled in a systematic manner.  Rather than dealing with them, 
figure out why they're happening in the first place.  You do not want to deal 
with a database which is constantly being corrupted, since recovery of it may 
lead to lost data/changes.

As temporary diagnostic measures, try one or both of

(A) Moving the database out of tmpfs to a normal drive
(B) Opening the database as read/write

See whether either/both of those make the fault go away.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL mode readonly errors to SELECT statements

[Clemens Ladisch]

Robert Searle wrote:
> We have recently started trying to provide read-only access to the database
> (service run as user with group/other read access permissions under Linux,
> service not database owner) and occasionally get either
> SQLITE_READONLY_RECOVERY or SQLITE_READONLY_CANTINIT responses

 says:
| The SQLITE_READONLY_RECOVERY error code indicates that a WAL mode
| database cannot be opened because the database file needs to be
| recovered and recovery requires write access but only read access is
| available.

| The SQLITE_READONLY_CANTINIT result code originates in the xShmMap
| method of a VFS to indicate that the shared memory region used by WAL
| mode exists buts its content is unreliable and unusable by the current
| process since the current process does not have write permission on
| the shared memory region.

> 1) Should we treat these responses as an invitation to retry later rather
> than asserts?

Waiting might work if some other process opens the database and actually
does the recovery.

> 2) Do these responses indicate that the variable(s) requested in the select
> have not been returned?

Error codes indicate that the call failed.  The query did not even begin
to execute.

> 3) Are there any configuration settings on the database that might reduce
> the probability of occurrence?

Open the database with write access (so that recovery can be done), but set
PRAGMA query_only.

> 4) If there aren't any configuration settings, are there any usage patterns
> to avoid or to embrace?

Don't corrupt the database in the first place.  ;-)
You aren't using WAL over a network, or across a VM boundary, are you?

Normally, recovery is needed if some writer crashes.


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

Re: [sqlite] sqlite-src-3260000 tests throw Error: couldn't fork child process: not enough memory

[Dennis Clarke]

On 1/23/19 7:10 PM, Richard Hipp wrote:

On 1/23/19, Dennis Clarke  wrote:


Perhaps I was mistaken to enable --enable-tempstore=yes  during configure ?



Maybe.  Does it work if you omit that option?




The solution seems to be to throw hardware at the problem and then it
goes away.  I allocated 32G of memory to the process and also allowed
the Solaris zone to lock 32G and everything runs with the exception
of a single test :

.
.
.
Time: sharedA.test 1052 ms
Time: sharedB.test 104 ms
Time: sharedlock.test 42 ms
! shell1-1.7.1 expected: [0 1 1]
! shell1-1.7.1 got:  [1 1 1]
Time: shell1.test 7604 ms
Time: shell2.test 841 ms
Time: shell3.test 398 ms
Time: shell4.test 319 ms
Time: shell5.test 2315 ms
.
.
.
SQLite 2018-12-01 12:34:55 
bf8c1b2b7a5960c282e543b9c293686dccff272512d08865f4600fb58238b4f9

1 errors out of 146673 tests on corv SunOS 64-bit big-endian
!Failures on these tests: shell1-1.7.1
All memory allocations freed - no leaks
Maximum memory usage: 9276472 bytes
Current memory usage: 0 bytes
Number of malloc()  : -1 calls
gmake: *** [Makefile:1234: tcltest] Error 1

Not sure what the issue is with a tcltest Error or why some process
called "testfixture" needed 2G of memory :


   PID USERNAME  SIZE   RSS STATE  PRI NICE  TIME  CPU PROCESS/NLWP 

 5 root0K0K sleep   99  -20   0:36:29 2.2% 
zpool-jupiter_r/166

 29033 dclarke  2087M  221M sleep00   0:07:29 0.3% testfixture/2
 27749 root 4976K 3760K cpu0   100-   0:00:15 0.1% prstat/1
   119 root0K0K sleep   99  -20   7:19:26 0.1% 
zpool-jupiter_z/166

 7 root0K0K sleep0  -20   0:03:13 0.0% vmtasks/8
  2294 dclarke10M 8568K sleep00   0:00:00 0.0% testfixture/1
   729 root 3096K 1600K sleep   590   0:02:44 0.0% dhcpagent/1
   572 root   62M   23M sleep   590   0:02:22 0.0% poold/9
   175 daemon   1896K 1264K sleep   60  -20   0:00:00 0.0% kcfd/3
   235 root 2680K 1368K sleep   590   0:00:00 0.0% 
iscsi-initiator/2

   327 root 2952K 2184K sleep   590   0:00:00 0.0% rpc.bootparamd/1
   256 root 8056K 5256K sleep   590   0:00:24 0.0% ntpd/1
ZONEIDNPROC  SWAP   RSS MEMORY  TIME  CPU ZONE 

 0   53  156M  145M   0.2%   8:55:32 2.4% global 

11   33 2150M  337M   0.5%   0:07:32 0.3% z_001 

 6   20   68M   92M   0.1%   0:00:29 0.0% z_005 

 5   16   41M   41M   0.1%   0:00:24 0.0% z_002 

 2   43  100M  122M   0.2%   0:01:53 0.0% z_000 


 4   20   70M   97M   0.1%   0:01:09 0.0% z_006

Also, since I am here with memory tossed at the issue :

Time: between.test 70 ms
Skipping bigmmap.test - requires SQLITE_MAX_MMAP_SIZE >= 8G
Time: bigmmap.test 29 ms

What do I do here?

Set an env var SQLITE_MAX_MMAP_SIZE=17179869184 for 16G and then see
what happens?

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

Re: [sqlite] sqlite-src-3260000 tests throw Error: couldn't fork child process: not enough memory

[Dennis Clarke]

On 1/23/19 7:10 PM, Richard Hipp wrote:

On 1/23/19, Dennis Clarke  wrote:


Perhaps I was mistaken to enable --enable-tempstore=yes  during configure ?



Maybe.  Does it work if you omit that option?



I just tried without and also went back to sqlite-src-324 and they
all fail in the same way.  This has me baffled. I may try this with less
strict CFLAGS but I don't see how that is related. I am checking my user
session limits but there is nothing interesting there either.

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

[sqlite] WAL mode readonly errors to SELECT statements

[Robert Searle]

Hi,

I have an sqlite3 database (version 3.25.3) in tmpfs which has many readers
and writers.

The database is running in WAL mode and seems to work efficiently in that
mode.
Since the database files are in a memory based file-system, we don't care
about the usual corruption on power-cycle issues because we rebuild a clean
database from scratch when we power up again.

We have recently started trying to provide read-only access to the database
(service run as user with group/other read access permissions under Linux,
service not database owner) and occasionally get either
SQLITE_READONLY_RECOVERY or SQLITE_READONLY_CANTINIT responses to select
statements ie. Not attempting to modify the database - The service
typically runs for a few minutes trying to read the database about 1-2
times per second before asserting.
The service is 'nicer' than the other reader/writers.
Each writer is responsible for a distinct subset of the values in the
database. ie. Every variable/row in the database has a unique owner that
can change the value. There are multiple readers for each variable/row.

These response codes are currently treated as cause for assert (accept only
SQLITE_OK or SQLITE_DONE responses, assert on all other response codes).
The sqlite3 documentation indicates that these errors indicate possible
database corruption, the sqlite3 source code comments seem to indicate that
they can also occur on race conditions with a writer.

Questions:
1) Should we treat these responses as an invitation to retry later rather
than asserts?
2) Do these responses indicate that the variable(s) requested in the select
have not been returned? ie we need to accept the possibility that the
requested values are temporarily unavailable.
3) Are there any configuration settings on the database that might reduce
the probability of occurrence?
4) If there aren't any configuration settings, are there any usage patterns
to avoid or to embrace?



-- 
Robert Searle

Lead Design Engineer

Tait Communications

DDI: +64 3 3570717

Email: robert.sea...@taitradio.com



www.taitradio.com

-- 
This Communication is Confidential. We only send and receive email on the

basis of the terms set out at www.taitradio.com/email_disclaimer 

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

Re: [sqlite] sqlite-src-3260000 tests throw Error: couldn't fork child process: not enough memory

[Richard Hipp]

On 1/23/19, Dennis Clarke  wrote:
>
> Perhaps I was mistaken to enable --enable-tempstore=yes  during configure ?
>

Maybe.  Does it work if you omit that option?

-- 
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] sqlite-src-3260000 tests throw Error: couldn't fork child process: not enough memory

[Dennis Clarke]

On Oracle solaris 10 sparc with 16GB of memory I was surprised to see :

.
.
.
Time: keyword1.test 229 ms
Time: lastinsert.test 64 ms
Time: laststmtchanges.test 60 ms
 (82 ms - want less than 1000)  (80 ms - want less than 1000) Time: 
like.test 188 ms

Time: like2.test 215 ms
Time: like3.test 56 ms
Time: limit.test 233 ms
Time: limit2.test 103 ms
Time: loadext2.test 38 ms
Time: lock.test 913 ms
lock2-1.1...
Error: couldn't fork child process: not enough memory
lock2-1.3...
Error: can't read "::tf1": no such variable
lock2-1.4...
Error: can't read "::tf1": no such variable
! lock2-1.5 expected: [1 {database is locked}]
! lock2-1.5 got:  [0 {}]
lock2-1.6...
Error: can't read "::tf1": no such variable
lock2-1.7...
Error: can't read "::tf1": no such variable
! lock2-1.8 expected: [0 {}]
! lock2-1.8 got:  [1 {cannot commit - no transaction is active}]
lock2-1.10...
Error: can't read "::tf1": no such variable
Time: lock2.test 39 ms
Time: lock3.test 35 ms
lock4-1.2...
Error: couldn't fork child process: not enough memory
lock4-1.3...
Error: invalid command name "db2"
lock4-999.1...
Error: can't delete "db2": command doesn't exist
Time: lock4.test 61 ms
Time: lock5.test 37 ms
.
.
.

Perhaps I was mistaken to enable --enable-tempstore=yes  during configure ?

Any thoughts ?

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

Re: [sqlite] CoreData - when database gets closed

[Scott Perry]

On Jan 22, 2019, at 9:02 AM, Simon Slavin  wrote:
> 
> On 22 Jan 2019, at 3:45pm, Maldonado-Salazar, Carlos 
>  wrote:
> 
>> Is there a way to know when CoreData closes sqlite files?. I’m using 
>> CoreData in an iOS app and I set file attributes for sqlite file to be 
>> NSFileProtectionCompleteUnlessOpen which throws away the key to encrypt the 
>> file when it’s closed, denying access to it.
> 
> I don't think I've seen one.  Technically, CoreData might hold its file open 
> after your application has quit, for example if it's doing lazy writing.  So 
> there'd be nothing to notify

A persistent store coordinator will close its handle when the store is removed. 
This happens naturally when the coordinator is deallocated, or you can do it 
manually using -[NSPersistentStoreCoordinator removePersistentStore:error:].

One common problem people run into is unexpected object lifetime extension when 
the coordinator gets added to a autorelease pool higher up in the call stack. 
In those conditions you can make the lifetime semantics of the coordinator more 
precise through explicit use of @autoreleasepool blocks.

> Using NSFileProtectionCompleteUnlessOpen or NSFileProtectionComplete should 
> do a good job of keeping your data secure.

I'd recommend using NSFileProtectionComplete over 
NSFileProtectionCompleteUnlessOpen. The former is simpler and self-securing 
(the filesystem will return an error if Core Data attempts to read or write 
data when the device is locked).

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

[sqlite] Uncompressed SQLite Archive Files

[Andre deBruin]

 
I am a Big fan of the recent support for 'SQLite Archive' files.
Basically I was planning to use  this as a generic version of
an "Application File Format" (sqlite.org/appfileformat.html).
 
As far as I can see the Archive Files functionality does always
compress the blobs if it is found that compression is sensible, other blobs
(as mentioned in the documentation for example in the case of Jpeg data) will
be left uncompressed.
 
That means that the sqlite database files created this way can only be read
if the reader program calls the new sqlar_uncompress code.
 
I am contemplating using this format as alternative for ZIP files for
data which consists mainly of image files (jpeg, tiff) but also contain a large
number of compressible (XML, JSON, etc.) files.
 
It would be really good to have a "do not compress" option in the create 
function.
 
If there would be an option (just like 'zip -0' ) to DISABLE compression
altogether the advantages could be:
 
- speed of file creation (no compression attempted)the
- speed of extraction (no decompression)
- files can be read with legacy client programs (i.e. programs using basic 
sqlite API, not the 'sqlar' extensions).
- the compression implementation appears to be experimental (there is mention 
of supporting new types in
future) so there is a possibility that files written with today's 
implementation have  to be upgraded when the  
implementation evolves (there is no metadata in the blob to identify 
compression type).
 
(this last one  point was partly addressed in thread  [sqlite] Issues about 
SQLite archive files mar 15 2018)
 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite linked to plugin binary

[Clemens Ladisch]

Carsten Müncheberg wrote:
> A host application (which is a black box for me I cannot change) is loading
> my plugin binaries which each have SQLite linked statically. When two
> plugins now access the same database file from the process of the host
> application none of the serialization mechanisms (file lock, mutex) works,

Not even unix-dotfile?

> my question is whether anyone of you can think of a solution for this
> unfortunate problem I am stuck with.

You could write your own VFS, with some other locking mechanism.


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

Re: [sqlite] SQLite linked to plugin binary

[Jens Alfke]

> On Jan 23, 2019, at 8:27 AM, Carsten Müncheberg 
>  wrote:
> 
> A host application (which is a black box for me I cannot change) is loading
> my plugin binaries which each have SQLite linked statically. When two
> plugins now access the same database file from the process of the host
> application none of the serialization mechanisms (file lock, mutex) works,
> and my database file will get corrupted eventually.

Build SQLite as a dynamic library, and link each of your plugins with it 
dynamically. (You may need an installation process that copies the SQLite 
library to a known location where each plugin can find it.)
Then there’s only one instance of SQLite, and locking should work properly.

(And if the platform is macOS or iOS, consider using the built-in system copy 
of SQLite instead of your own; that makes it even easier.)

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

Re: [sqlite] Query Planning Knowledge

[Clemens Ladisch]

Andy Bennett wrote:
>> foreign key constraints
>
> my experience with other engines taught me that it makes experimenting at the 
> monitor harder.

Then don't use them. :)  But do you actually want 'wrong' data?

> Are there any efficiency benefits or is it just there to enforce data 
> integrity?

Constraints just are additional checks.
(FKs require certain indexes, but you would want to have those anyway.)

> It looks like they have to be enabled on a per connection basis. In this case 
> I (currently)
> control all the client code but is it possible for the foreign key 
> relationships to get out
> of sync if one of the connections omits to apply the pragma?

Yes.  You could run PRAGMA foreign_key_check afterwards.


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

[sqlite] SQLite linked to plugin binary

[Carsten Müncheberg]

I am running into a problem with the following use-case:
A host application (which is a black box for me I cannot change) is loading
my plugin binaries which each have SQLite linked statically. When two
plugins now access the same database file from the process of the host
application none of the serialization mechanisms (file lock, mutex) works,
and my database file will get corrupted eventually.

The technical reasons for this are understood by me, my question is whether
anyone of you can think of a solution for this unfortunate problem I am
stuck with.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] System.Data.Sqlite Encryption Cipher

[Mattock Paul extern]

Hi,
Would anyone be able to confirm what encryption cipher is used when 
SetPassword="" is set on database creation?

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

Re: [sqlite] Query Planning Knowledge

[Andy Bennett]

Hi,


I could use the inner join for the "entrys" join and the "items" join
but not the "entry-items" join because each entry can have more than
one item.


  WITH a(id, name) AS (VALUES (1, 'A')),
   b(id, name) AS (VALUES (1, 'B1'), (1, 'B2'))
  SELECT * FROM a INNER JOIN b USING (id);

  1|A|B1
  1|A|B2

The only difference between inner and outer joins is how rows without
any match are handled.


Thanks Clemens! In my case entries can have zero items as well and I still 
want the entry itself to show up.




I started with an OUTER JOIN as I find it easier to show that it's
doing the correct thing because I can search the output for errant
NULLs. Trying to detect missing rows in an INNER JOIN is harder.


If the join columns have the same name, using USING is easier.

And it would be a good idea to enforce the relationships between the
tables with foreign key constraints: 


(However, constraints do not affect how you have to write your queries.)


Ah yes. It might be worth looking at this. I've always avoided it in the 
past because my experience with other engines taught me that it makes 
experimenting at the monitor harder. Are there any efficiency benefits or 
is it just there to enforce data integrity (very important, of course;-))?


It looks like they have to be enabled on a per connection basis. In this 
case I (currently) control all the client code but is it possible for the 
foreign key relationships to get out of sync if one of the connections 
omits to apply the pragma?



Thanks for the tips!




Regards,
@ndy

--
andy...@ashurst.eu.org
http://www.ashurst.eu.org/
0x7EBA75FF
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] 'no such table' error code XXX should be propagated up to the client instead of generic error code 1

[Anton Sukhin]

sqlite3LocateTable returns generic error code 1 (SQLITE_ERROR) and error
message 'no such table'. This makes it difficult to distinguish this error
from others as in order to find that the table is missing, string parsing
of error message for 'no such table' is required.

the same applies to 'no such table column' error.

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