Re: [sqlite] 'database disk image is malformed' only on the mac

[Simon Slavin]

On 6 Oct 2017, at 1:55pm, Fahad  wrote:

> I can now reliably corrupt my database - and this happens only when some
> other process is writing to the database while I perform sqlite3_close_v2 on
> the connections.

Reliable corruption will help investigation tremendously.  To help investigate 
this ..

What macOS version ?  If you’re not running the 'bash' that comes with it, tell 
us.

What format is the drive the database is stored on in ?  Is it the boot drive 
for that computer ?

Is any part of your test setup still running as a Safari addon/plugin ?

Just for debugging purposes, can you try your program running with the database 
stored on some other drive ?  Perhaps an external spinning disk, or a Flash 
drive.  Does that increase or decrease the frequency of corruption ?

> #define SQLITE_DEFAULT_SYNCHRONOUS 1 // 1: Normal, 2: Full, 3: Extra
> #define SQLITE_TEMP_STORE 3 // always use memory

Just for debugging purposes, can you try removing these ?  Just let SQLite do 
whatever it would do by default.  Does that increase or decrease the frequency 
of corruption ?

> Page 24256: btreeInitPage() returns error code 11
> On tree page 722 cell 1: 2nd reference to page 24256
> On tree page 932 cell 3: 2nd reference to page 24255

This suggests that something has overwritten your database file starting at 
page 24255.  It is a common source of errors reported by SQLite: some other 
part of a program (Safari ?) suddenly decides to write to the wrong file 
handle.  If you have a hex viewer for the Mac (I recommend '0xED.app') you 
could take a look and see if the text starting there looks like your data (i.e. 
preceding pages) or something completely different.

Having received error messages like this, please close all connections to the 
database, then use the shell tool to execute "PRAGMA integrity_check()" and 
check that the database file on disk is corrupt, rather than some in-memory 
version of it accessed by your software.

Simon.

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

Re: [sqlite] 'database disk image is malformed' only on the mac

[Fahad]

I can now reliably corrupt my database - and this happens only when some
other process is writing to the database while I perform sqlite3_close_v2 on
the connections. I'll explain what I'm doing.

Since the last I wrote, I've disabled all the flags other than these:

#define SQLITE_ENABLE_FTS3 1
#define SQLITE_ENABLE_STAT4 1
#define SQLITE_DEFAULT_SYNCHRONOUS 1 // 1: Normal, 2: Full, 3: Extra
#define SQLITE_TEMP_STORE 3 // always use memory

It now assumes that sqlite is in serialised mode (and thus, thread safe).
I've removed all sorts of clever caching within the app and have simply two
connections in WAL mode (one for writing, one for reading). These are used
by various different threads, presumably at the same time but that should
not be an issue with sqlite in serialised mode.

This is how I can reproduce the malformed bug:

1) I have a bash script that runs in a loop:

#!/bin/bash
while :
do
  ls -alF
  sqlite3 mydb.db "CREATE TABLE IF NOT EXISTS junk (INTEGER a);"
  sqlite3 mydb.db "SELECT count(*) FROM junk;"
  sqlite3 mydb.db "SELECT count(*) FROM junk;INSERT INTO junk VALUES (1);"
  sqlite3 mydb.db "PRAGMA integrity_check;"

  sleep 0.5
done


2) I have my app running on the side. I launch it with a fresh copy of the
mydb.db for the app (pre-corruption, with data from the app in there
already). The app does it's thing (selecting / inserting data). I then quit
the app, at which point it closes the two connections with sqlite3_close_v2
*successfully*. 

3) 8/10, my bash script suddenly starts spewing this on the console:

Page 24256: btreeInitPage() returns error code 11
On tree page 722 cell 1: 2nd reference to page 24256
On tree page 932 cell 3: 2nd reference to page 24255

This happens only after I am able to close the connections successfully from
the app and the app successfully quits (connected to Xcode's debugger).

I have no idea what is going on since I've dumbed down the code to the point
where it's simply opening a connection at launch, closing it at termination.
I'm not using any extra mutex's within the app, assuming the sqlite works
correctly in serialised mode.

Please help!



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 'database disk image is malformed' only on the mac

[David Raymond]

The terminology for that option has always caught me out. The best phrase is 
the one below.
http://www.sqlite.org/compile.html#threadsafe
"When compiled with SQLITE_THREADSAFE=2, SQLite can be used in a multithreaded 
program so long as no two threads attempt to use the same database connection 
(or any prepared statements derived from that database connection) at the same 
time."

So for thread safety level, 0 < 2 < 1


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Fahad
Sent: Monday, September 25, 2017 5:27 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] 'database disk image is malformed' only on the mac

I don't think so:

https://sqlite.org/threadsafe.html

"With -DSQLITE_THREADSAFE=2 the threading mode is multi-thread." Setting it
to 0 disables all mutexes (assumes single threaded)

So I've set it to be multi-threaded.

Okay so I've wrapped @synchronized(..) around my database usage, stopped
caching / re-using prepared statements and am finalising them as soon as
they're used. I am still re-using the same database connection linked to the
same thread (i.e. I still have multiple threads, each with their own
database connection opened, however only one thread at any given time is
able to perform a db-operation, such as SELECT / UPDATE and so on).

Database corruption has stopped completely, except I now am seeing
occasional reports of disk I/O errors (error 522). I have no idea how the
database file is being truncated. I've made sure with the user that no other
instance of the app is running. I've made sure the code itself is not
deleting or touching any of the -wal and -shm files. I've also fallen back
to using these flags now:

#define SQLITE_ENABLE_FTS3 1

#define SQLITE_DEFAULT_MMAP_SIZE 0
#define SQLITE_DEFAULT_MEMSTATUS 0
#define SQLITE_DEFAULT_SYNCHRONOUS 1 // 1: Normal, 2: Full, 3: Extra

#define SQLITE_THREADSAFE 2 // 1: Serialized, 2: Multi-threaded, 3:
Single-threaded

#define SQLITE_MAX_MMAP_SIZE 0
#define SQLITE_TEMP_STORE 3 // use memory

Like I said before, I am tempted to just fallback to using DELETE / TRUNCATE
journaling mode; I'm worried about concurrency though. The app has various
plugins that can at any time access and write to the same database that's
already in-use by the main app. Will this pose a problem with DELETE or
TRUNCATE journaling? 

Thanks
Fahad



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
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] 'database disk image is malformed' only on the mac

[Fahad]

I don't think so:

https://sqlite.org/threadsafe.html

"With -DSQLITE_THREADSAFE=2 the threading mode is multi-thread." Setting it
to 0 disables all mutexes (assumes single threaded)

So I've set it to be multi-threaded.

Okay so I've wrapped @synchronized(..) around my database usage, stopped
caching / re-using prepared statements and am finalising them as soon as
they're used. I am still re-using the same database connection linked to the
same thread (i.e. I still have multiple threads, each with their own
database connection opened, however only one thread at any given time is
able to perform a db-operation, such as SELECT / UPDATE and so on).

Database corruption has stopped completely, except I now am seeing
occasional reports of disk I/O errors (error 522). I have no idea how the
database file is being truncated. I've made sure with the user that no other
instance of the app is running. I've made sure the code itself is not
deleting or touching any of the -wal and -shm files. I've also fallen back
to using these flags now:

#define SQLITE_ENABLE_FTS3 1

#define SQLITE_DEFAULT_MMAP_SIZE 0
#define SQLITE_DEFAULT_MEMSTATUS 0
#define SQLITE_DEFAULT_SYNCHRONOUS 1 // 1: Normal, 2: Full, 3: Extra

#define SQLITE_THREADSAFE 2 // 1: Serialized, 2: Multi-threaded, 3:
Single-threaded

#define SQLITE_MAX_MMAP_SIZE 0
#define SQLITE_TEMP_STORE 3 // use memory

Like I said before, I am tempted to just fallback to using DELETE / TRUNCATE
journaling mode; I'm worried about concurrency though. The app has various
plugins that can at any time access and write to the same database that's
already in-use by the main app. Will this pose a problem with DELETE or
TRUNCATE journaling? 

Thanks
Fahad



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 'database disk image is malformed' only on the mac

[Keith Medcalf]

>#define SQLITE_ENABLE_FTS3 1
>#define SQLITE_OMIT_DEPRECATED 1
>#define SQLITE_OMIT_SHARED_CACHE 1
>#define SQLITE_OMIT_AUTOMATIC_INDEX 1
>#define SQLITE_OMIT_DECLTYPE 1
>
>#define SQLITE_DEFAULT_MMAP_SIZE 0
>#define SQLITE_DEFAULT_MEMSTATUS 0
>#define SQLITE_DEFAULT_SYNCHRONOUS 1
>
>#define SQLITE_THREADSAFE 2
>
>#define SQLITE_MAX_MMAP_SIZE 0
>#define SQLITE_TEMP_STORE 3
>
>
>I've set it to be thread-safe. 

Actually, no.  You have set it to "thread unsafe".  Thread Safe is the default, 
which is SQLITE_THREADSAFE=1

The values of SQLITE_THREADSAFE are
  0:  No threading. SQLite3 routines will only ever be called from a single 
thread.
  1:  Thread Safe.  SQLite3 will impose thread-safety on your code so that 
you may freely do whatever you want from any thread.
  2:  No Thread Safety: Thread Safety is turned off.  You are responsible for 
ensuring thread safety.

If you turn on thread safety (return to the default), does the application work 
properly?  If so, you have made a threading error in your code from which 
"thread safe" is protecting you -- on the other hand, if it still does not work 
properly then the error does not involve threading (within the SQLite3 code) 
but is rather something else the application is doing wrong (like tromping on 
memory owned by SQLite3).




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

Re: [sqlite] 'database disk image is malformed' only on the mac

[Dan Kennedy]

On 09/21/2017 04:20 AM, Fahad wrote:

Thanks Jens, yes I didn't take the warnings from the Thread Sanitizer
lightly. Although I'm very confident with the actual implementation (using
thread local dictionaries) and have verified time and again using unit tests
that my code is otherwise thread-safe, I really do think there are perhaps
certain things I'm not totally clear about.

The oddest thing about the thread sanitiser race condition warnings was that
the thread 'reading' the same object was in fact from a 'Query-only'
connection (with PRAGMA query_only=1 set) and the thread 'writing' was
usually the writer. I've compiled SQLite with multi-threaded support and
that didn't seem to help.


If it's the one I think it is, its safe. It happens when a writer 
updates the hash table stored in the *-shm file while a reader is 
reading it. But the hash table is designed so that:


  * To add an entry, a single 32-bit 0x is overwritten with a 
non-zero 32-bit integer is written to the shared-memory, and
  * It doesn't matter to the reader whether or not it sees the 0x00 or 
the new value


So, although there is a race condition that affects which branch of a 
condition the reader takes, both branches are safe.


If you post one of the call stacks we can confirm that that is the error 
you're seeing.


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

Re: [sqlite] 'database disk image is malformed' only on the mac

[Kees Nuyt]

On Wed, 20 Sep 2017 06:41:05 -0700 (MST), Fahad
 wrote:

[...]
> 1) Thread A: Create a new connection, if one already does not exist for the
> thread. Store it's 'reference count' in the thread storage (I close a
> connection when the count becomes zero).
> 2) Thread A: Get a previously stored statement for that connection against a
> name (using a dictionary for this) from the statement cache (again, from the
> thread local storage) - if one doesn't exist, call sqlite3_prepare_v2 on a
> new statement and save it in the statement cache. Since a single thread can
> always only have a single db connection, the statement is thread-safe.
>
> ... assume some nested asynchronous calls
>
> 3) Thread A: Grab a cached connection, and then grab an existing statement.
> If found, re-use it by first calling sqlite3_clear_bindings(pStmt).

I'm not an expert, but: _clear_bindings() is not enough to clear
the statement context data. To reuse a statement, you'd have to
_reset() it.

> 4) Thread A: Close connection (i.e. decrement the reference count, if it's
> zero first clear the statement cache by calling sqlite3_reset and
> sqlite3_finalize on all the cached statements). In case the reference count
> is't 0, the connection is kept alive. 
[...]

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

Re: [sqlite] 'database disk image is malformed' only on the mac

[Fahad]

Thanks Jens, yes I didn't take the warnings from the Thread Sanitizer
lightly. Although I'm very confident with the actual implementation (using
thread local dictionaries) and have verified time and again using unit tests
that my code is otherwise thread-safe, I really do think there are perhaps
certain things I'm not totally clear about. 

The oddest thing about the thread sanitiser race condition warnings was that
the thread 'reading' the same object was in fact from a 'Query-only'
connection (with PRAGMA query_only=1 set) and the thread 'writing' was
usually the writer. I've compiled SQLite with multi-threaded support and
that didn't seem to help. 

Once I added @synchronized(lockObj) { .. } around all my readers and
writers, I stopped getting these warnings from the sanitiser. Database
corruption stopped for one user but instead got replaced by disk I/O errors.
Since then I've now disabled all the statement caching as well. Thread local
instances are guaranteed to belong to that particular thread, but I'm no
longer taking any chances. I'm now preparing a new statement and finalising
it immediately inside of a synchronised block of code. Going to try if this
works, but in effect months of 'clever engineering' has been replaced with a
bunch of ugly synchronised blocks that defeat the point of using sqlite with
multi-threading support :)



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 'database disk image is malformed' only on the mac

[Fahad]

No I'm not.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 'database disk image is malformed' only on the mac

[Brian Macy]

Fahad,

Are you calling sqlite3_wal_checkpoint_v2?

Brian Macy


On Sep 20, 2017, 1:59 PM -0400, wrote:
>
> I've run the Thread Sanitizer with my own SQLite-based on macOS, and haven't 
> seen any warnings in sqlite3.c. So what you got could be a real warning sign.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 'database disk image is malformed' only on the mac

[Jens Alfke]

> On Sep 19, 2017, at 8:20 PM, Fahad  wrote:
> 
> I recently switched on the Thread Sanitizer in Xcode only to find that it
> was complaining of race conditions inside of the sqlite3.c code, that the
> various readers and writers were trying to read / write to the same
> wal-index.

I've run the Thread Sanitizer with my own SQLite-based on macOS, and haven't 
seen any warnings in sqlite3.c. So what you got could be a real warning sign.

It might be worth investigating some of those warnings to see if they stem from 
illegal usage on your part, like inadvertently using a connection or statement 
on the wrong thread. (When I was doing my own tests with the Thread Sanitizer, 
I found a warning on my own code which initially looked like a false positive, 
but I investigated anyway and discovered it really was a bug of mine.)

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

Re: [sqlite] 'database disk image is malformed' only on the mac

[Fahad]

I've tried that as well. Since I'm using PRAGMA journal_mode=WAL on all the
connections, I've had issues with MMAP (as acknowledged by the threads
above) so have had to disable that. I also need FTS 3 to work.

The rest of the flags to do with synchronisation and threading, I've enabled
/ disabled / modified / removed over and over again. What's worse is that I
cannot easily reproduce these corruptions, at least not using unit tests
(I've tried writing various tests with multiple threads reading and writing
at the same time). 



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 'database disk image is malformed' only on the mac

[Fahad]

I apologise for the many posts, but I'm writing in hope that one of you may
point out something that I'm either doing wrong, or a concept I haven't
fully grasped. 

I'm aware that prepared statements are tied to the database connection they
were created for. In order to get more speed out of my recurring statements
(almost every statement I sqlite3_prepare_v2, I'm then re-using it later), I
store them in the thread local cache as well. Roughly, here's what I am
doing:

1) Thread A: Create a new connection, if one already does not exist for the
thread. Store it's 'reference count' in the thread storage (I close a
connection when the count becomes zero).
2) Thread A: Get a previously stored statement for that connection against a
name (using a dictionary for this) from the statement cache (again, from the
thread local storage) - if one doesn't exist, call sqlite3_prepare_v2 on a
new statement and save it in the statement cache. Since a single thread can
always only have a single db connection, the statement is thread-safe.

... assume some nested asynchronous calls

3) Thread A: Grab a cached connection, and then grab an existing statement.
If found, re-use it by first calling sqlite3_clear_bindings(pStmt).

4) Thread A: Close connection (i.e. decrement the reference count, if it's
zero first clear the statement cache by calling sqlite3_reset and
sqlite3_finalize on all the cached statements). In case the reference count
is't 0, the connection is kept alive. 

Since a thread could be doing various things and a method call may result in
another nested method call that needs to query the db again, the connection
is kept alive and only closed when necessary. This keeps the overall active
'readers' in check. If there are 7 threads, there are 7 readers in theory
(although each thread will almost always run a 'task' and aim at closing the
connection if it can).

The above scenario can be imagined for multiple asynchronous threads. As I
explained, I do however use a persistent 'writer' sqlite connection (along
with a reference count for that too) and open / close only if there are no
more tasks requiring a writer. The writer was previously using a mutex in
case multiple threads need to access the same writer, but given I keep
prepared statements in a thread local cache, the statements were always
valid against the connection these were created. For the writer I use a
separate prepared statement cache by the way.

As you can see, the architecture is very elaborate, but all this ensures
utmost performance whilst ensuring thread-safety along with concurrency. The
app performs very well against multiple asynchronous tasks, but it's being
bogged with database corruption every now and then.

Could it be that I need to prepare and finalise the statements and not
re-use them like I do? Not sure how accurate this is but it seems to suggest
that we need to do this:

https://stackoverflow.com/questions/36364162/accessing-sqlite-database-from-multiple-processes-and-sqlite-busy

Reading up on look-aside memory (https://sqlite.org/malloc.html#lookaside)
I'm now even more confused as I think the memory is being stomped over
presumably and causing issues since I'm re-using my statements like I do?



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 'database disk image is malformed' only on the mac

[Fahad]

I can't be certain now but I think this thread is related:

http://sqlite.1065341.n5.nabble.com/Re-Database-corruption-and-PRAGMA-fullfsync-on-macOS-td95366.html

It was this thread that I landed on earlier this year to presumably fix the
issues I was experiencing personally. This did help, in fact it did cut down
on the reports (I was literally getting 3 reports a day at one time and had
to act quickly). As I explained, using rather primitive locking mechanism,
I'm able to 'serialize' access between threads and this has greatly helped
but am still seeing 'disk I/O' errors and these don't go away till the
process is killed and restarted.

Given WAL relies on the -shm memory mapped file, I think deep down somewhere
there's more to memory mapping and Mac OS than meets the eye. Given two
processes in my case are writing to the same database (both opening it in
WAL journal mode), I suspect somewhere down the line these go out of sync do
to the full sync flushing issues mentioned in the thread above.

I would love to continue using WAL but am now full of doubts. I'm going to
try and switch to DELETE journal mode on the Mac exclusively (as I
mentioned, the exact same code is shared between our iOS and Mac app - not
an atom's worth of difference between the two, and iOS has never troubled
me). 



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 'database disk image is malformed' only on the mac

[Simon Slavin]

On 20 Sep 2017, at 4:20am, Fahad  wrote:

> These are the flags I've finally settled on:

Revert all those settings.  Allow SQLite to use its default settings.  See if 
that makes your problem go away.

This is purely for testing.  Once you know whether it works or not you can 
start setting them again.

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

Re: [sqlite] 'database disk image is malformed' only on the mac

[Fahad]

Hi Dan

The plugin I'm referring to is a 'Share' plugin that one can embed inside of
a mac app, which then shows up in the "Sharing" menu in Safari. When you
click on it, it launches in its own process, allowing you to share the
currently viewed website with your main app. Thus, the main app and this
plugin are two separate processes accessing the same WAL database at the
same time (both could be 'writing').

I'll explain a bit more because disk I/O errors and disk corruption is
really killing me right now. These are the flags I've finally settled on:

#define SQLITE_ENABLE_FTS3 1
#define SQLITE_OMIT_DEPRECATED 1
#define SQLITE_OMIT_SHARED_CACHE 1
#define SQLITE_OMIT_AUTOMATIC_INDEX 1
#define SQLITE_OMIT_DECLTYPE 1

#define SQLITE_DEFAULT_MMAP_SIZE 0
#define SQLITE_DEFAULT_MEMSTATUS 0
#define SQLITE_DEFAULT_SYNCHRONOUS 1

#define SQLITE_THREADSAFE 2

#define SQLITE_MAX_MMAP_SIZE 0
#define SQLITE_TEMP_STORE 3


I've set it to be thread-safe. Although the app may have two processes
running at any given time (the main app,  and the plugin), any single
process itself has a single writer and multiple readers. Since WAL doesn't
support the read-only flag, I changed the readers to open like so (just as I
do my writer):

BOOL dbOpened = (sqlite3_open_v2(path.UTF8String, ,
SQLITE_OPEN_READWRITE, NULL) == SQLITE_OK);

and then I set these to be query_only:

if (sqlite3_exec(readOnlyDB, "PRAGMA query_only=1;", NULL, NULL, NULL) !=
SQLITE_OK) {
  // ...   
}

The single writer is being accessed and used by the process using a
@synchronized() block (Objective-C), ensuring any prepared statement is used
and immediately reset and finalized before leaving the block.

To ensure thread-safety and a bit of 'database pooling' (so I don't have to
open and close connections on the same thread over and over again,
especially if I have nested calls in my code), I'm open a read-only
connection and then store it in the thread-local dictionary (i.e. [[NSThread
currentThread] threadDictionary]).

This gives me the concurrency I need, along with thread-safety to ensure the
same database connection is not used in a different thread. Prepared
statements tied to a read-only connection are also being stored this way -
in the thread local dictionary). 

I recently switched on the Thread Sanitizer in Xcode only to find that it
was complaining of race conditions inside of the sqlite3.c code, that the
various readers and writers were trying to read / write to the same
wal-index. This may be desirable (as I read elsewhere that this is okay) I
felt this may be causing issues. I've thus far wrapped each and every call
to the databse using the same @synchronized(lockObj) call. Doing so
essentially has made my otherwise multi-threaded app, a serialized app since
readers wait on each other, as well as on the main writer before accessing
the database. I tested this with a user seeing disk corruption often (and
mostly when he's using both the plugin and the main app) and he's reported a
90% improvement. This time he didn't see malformed disk errors but instead
saw 'disk I/O' errors after a few hours, but a relaunch of the app fixed it.

Right now I've tried every single flag in SQLite. Ive read, and re-read the
how to corrupt your database as well as anything and everything. I'm
struggling to figure this out. The *exact same code* works in iOS just fine
- not a single complaint for years. It's only the mac (any mac, it seems).

Maybe I could just switch to TRUNCATE journal mode now that I've effectiely
serialized all database access and I'm not getting any of the benefits of
multi-threading? Would that help? I should add that I switched to WAL
earlier this year and ever since have had issues reported, at least once a
week. Nowadays it's almost once a day. With journal_mode DELETE I never had
an issue, but then the app was pretty serial then.

Thanks
Fahad



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 'database disk image is malformed' only on the mac

[Dan Kennedy]

On 09/15/2017 10:38 AM, Fahad wrote:

I tried it with sqlite3_shutdown(), didn't help. I was able to reproduce it
again using the main app and the safari share plugin.


You don't need to call sqlite3_shutdown(). All it does is release the 
resources allocated by sqlite3_initialize() - which are trivially small 
for a workstation app and in any case are released automatically when 
the process is closed.

Main App: Finished writing to db, just opened a new connection to read
(after opening a connection I set PRAGMA query_only=1; PRAGMA
read_uncommitted=1; and register some custom functions).


The two pragmas are benign but likely no-ops. "PRAGMA read_uncommitted" 
only affects shared cache connections, and "PRAGMA query_only" doesn't 
change the behaviour of read-only connections.

The connection opened fine however the moment the app ran a SELECT statement
I think it coincided with a write from the the Plugin (separate process; it
opens a connection, writes and then closes it), boom the -shm file vanished
and only the .db and .db-wal files were present in the folder. The -wal was
zero bytes.

I have a feeling the -shm file got deleted by the plugin while it was in
fact in use by the main app (the main app only releases all its connections
when closing, so at any given time it has at least one open connection, even
when not being used).


What is the "safari share plugin"? The only reference to it on the 
internet seems to be here:


  
http://webcache.googleusercontent.com/search?q=cache:aIggi9ZiFkoJ:appshopper.com/mac/productivity/2do+=1=en=clnk=th

"Fixed an issue with the Safari share plugin, which would at times corrupt the 
database". A similar problem perhaps.

Is your database stored on a network file-system?

Have you read this?

  https://www.sqlite.org/howtocorrupt.html

Dan.



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

Re: [sqlite] 'database disk image is malformed' only on the mac

[Simon Slavin]

On 15 Sep 2017, at 2:24pm, Fahad  wrote:

> #define SQLITE_ENABLE_STAT4 1

Should not affect your problem.

> I also changed this:
> 
> #define SQLITE_DEFAULT_WAL_AUTOCHECKPOINT 1000
> 
> to 250

That may affect your problem.  Try extreme values, with the objective being to 
trigger definite corruption.  That way you have a reliable demonstration of a 
bug you can post about.

Have you made any other changes to defaults, or used any PRAGMAs ?

I’m glad you found a way to minimise your problem.  I’m sorry I can help 
explain what’s actually going on.


Simon.
-- 
 http://www.bigfraud.org | I'd expect if a computer was involved
 | it all would have been much worse.
 No Buffy for you.   |-- John "West" McKenna
 Leave quickly now. -- Anya  |  THE FRENCH WAS THERE

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

Re: [sqlite] 'database disk image is malformed' only on the mac

[Fahad]

Hi Simon

Wanted to chime in to say I was able to in fact cause the database to go
corrupt from other external processes trying to write to the database (in
WAL mode) while the main app was also using the database. So I have a
feeling it's something else. I've tried hard to create a mini-app that
demonstrates this but have had no luck; it happens randomly but frequently
enough to warrant a red flag,

After 'stress' testing the app launched twice with various threads trying to
open and close the connection, I was able to crash the app once but for a
very different reason, something about a pointer being misaligned while
writing stats to disk. I had enabled this flag earlier and turning it off
seems to have helped:

#define SQLITE_ENABLE_STAT4 1

I also changed this:

#define SQLITE_DEFAULT_WAL_AUTOCHECKPOINT 1000

to 250

A smaller checkpoint seems to have either put a bandaid on the corruption
issue or I'm finding it increasingly difficult to corrupt the db. I've tried
launching 5 instances of the app while also trying to access and write from
the plugins as before - everything goes smoothly. No corruption.

Could the two changes I made above have had an impact somehow? Again, I'm
unable to reliably corrupt the db but I could sit and repeat the steps 20
times and have it go corrupt at least once before. Now I've been trying for
over two hours and it seems to be okay.

regards
Fahad



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 'database disk image is malformed' only on the mac

[Simon Slavin]

On 15 Sep 2017, at 4:38am, Fahad  wrote:

> How can I prevent this from happening? Like I said, using a unit test I was
> able to reproduce this 1 out of 20 tries but when using the real app and
> plugin at the same time, I am able to reproduce every 5th try (it seems the
> app is opening / using / closing connections in a peculiar way).

Was your unit test also using the Safari Plugin architecture ?

Have you every managed to reproduce the fault in a stand-alone program ?  It 
doesn’t need to be your complete App, just a simple test program which opens 
the file and does the INSERT.

Everything you write makes me think your problem is with the Safari Plugin, or 
the Safari architecture in general.  If that’s the case you may need to take 
the problem up with Apple’s developer forum, or using one of your free Apple 
Developer Membership support calls.

Simon.
-- 
 http://www.bigfraud.org | I'd expect if a computer was involved
 | it all would have been much worse.
 No Buffy for you.   |-- John "West" McKenna
 Leave quickly now. -- Anya  |  THE FRENCH WAS THERE

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

Re: [sqlite] 'database disk image is malformed' only on the mac

[Fahad]

Hi Simon

I tried it with sqlite3_shutdown(), didn't help. I was able to reproduce it
again using the main app and the safari share plugin.

Main App: Finished writing to db, just opened a new connection to read
(after opening a connection I set PRAGMA query_only=1; PRAGMA
read_uncommitted=1; and register some custom functions).

The connection opened fine however the moment the app ran a SELECT statement
I think it coincided with a write from the the Plugin (separate process; it
opens a connection, writes and then closes it), boom the -shm file vanished
and only the .db and .db-wal files were present in the folder. The -wal was
zero bytes.

I have a feeling the -shm file got deleted by the plugin while it was in
fact in use by the main app (the main app only releases all its connections
when closing, so at any given time it has at least one open connection, even
when not being used). 

How can I prevent this from happening? Like I said, using a unit test I was
able to reproduce this 1 out of 20 tries but when using the real app and
plugin at the same time, I am able to reproduce every 5th try (it seems the
app is opening / using / closing connections in a peculiar way).



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 'database disk image is malformed' only on the mac

[Simon Slavin]

On 18 Aug 2017, at 12:30am, Fahad G  wrote:

> I do not have a way to reproduce this just yet, but I've been seeing way too 
> many diagnostic logs from customers where their databases are being corrupt 
> primarily on the Mac (the exact same code is shared between a Mac app, iPhone 
> and iPad) past several months - more so when I switched to WAL and started 
> dedicating a 'reader' connection for all reads, and a 'writer' for all 
> writes. 

I would say that I don’t see anything wrong with your compiler settings and 
PRAGMAs but that doesn’t mean much because I don’t know much about that stuff.

Would like to check and find out some facts.

At what point does your software detect this corruption ?  Is the database okay 
when it’s opened but get corrupted while he program is working ?  Or is the 
corruption in the file on disk and gets noticed immediately after opening ?  
This might help us figure out whether something in your program is stomping on 
SQLite3 memory.

This started happening several months ago ?  Let’s call it May.  Did you change 
development environments or versions of your Dev tools ?  Do you develop in 
Xcode ?  If so, did you start using a new version fo Xcode ?  Is your 
development computer using a stable version of the OS or the latest Developer 
Release we’re not meant to talk about ?

Are your customers using all the same version of macOS and iOS or are they 
varied ?

Does your application use sqlite3_shutdown() when it quits ?  If not, can you 
make this change ?

Do you check the value returned when you close a database connection and show 
an appropriate error message ?

> I read on the forums that mmap could be at fault (as I was using it). 
> Disabling it almost immediately felt that it solved the problem. However I'm 
> still occasionally now getting reports (weekly) of users running into a 
> "database disk image is malformed" error.

You are correct that use of mmap was (rarely) causing corruption and/or false 
reports of corruption.  Current versions of SQLite no longer use mmap because 
of this.  I’m see you’re using the latest SQLite amalgamation version.

The type of corruption done by this bug was not detectable immediately the file 
was opened.  It might only be noticed when the program tried to read a specific 
record or use a specific index.  Is it possible that your users who are still 
reporting corruption are still using databases which were corrupted earlier ?  
In other words the software is no longer corrupting databases but your users 
have 'legacy corruption' in their files ?

Hope some of this helps or another reader can help you.

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

[sqlite] 'database disk image is malformed' only on the mac

[Fahad G]

Hi

Although I've read all the rules (and am otherwise aware of what it takes to 
report a bug), I want to apologise upfront. I do not have a way to reproduce 
this just yet, but I've been seeing way too many diagnostic logs from customers 
where their databases are being corrupt primarily on the Mac (the exact same 
code is shared between a Mac app, iPhone and iPad) past several months - more 
so when I switched to WAL and started dedicating a 'reader' connection for all 
reads, and a 'writer' for all writes. 

I have read and tried every possible combination of flags and setting up the 
connections, making sure (via numerous unit tests) that the code in question is 
working, thread safe etc. I recently also switched to SERIALIZED mode (compile 
time option) in hope that this would go away. When this started happening on a 
daily basis a coupe of months ago, I read on the forums that mmap could be at 
fault (as I was using it). Disabling it almost immediately felt that it solved 
the problem. However I'm still occasionally now getting reports (weekly) of 
users running into a "database disk image is malformed" error. I've asked one 
of the users to send us a copy of the corrupt database, but this isn't always 
possible (waiting on them).

I open for writing using:

BOOL dbOpened = (sqlite3_open_v2(path.UTF8String, , 
SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_FULLMUTEX, NULL) == 
SQLITE_OK);

if (sqlite3_exec(dbConnection, "PRAGMA main.journal_mode=WAL; PRAGMA 
synchronous=normal;", NULL, NULL, NULL) != SQLITE_OK) {
 ...
}


And for reading:

BOOL dbOpened = (sqlite3_open_v2(path.UTF8String, , 
SQLITE_OPEN_FULLMUTEX | SQLITE_OPEN_READONLY | SQLITE_OPEN_WAL, NULL) == 
SQLITE_OK)


if (sqlite3_exec(readOnlyDB, "PRAGMA read_uncommitted=1; PRAGMA query_only=1; 
PRAGMA synchronous=normal;", NULL, NULL, NULL) != SQLITE_OK) {
 ...
}


I frequently would run VACUUM and ANALYZE but stopped doing that as well (in 
order to single this issue out), but am still seeing these error reports come 
in. 

I'm using the latest SQL amalgamation (v3.20.0) with the following compile time 
options:

#define SQLITE_ENABLE_FTS3 1
#define SQLITE_THREADSAFE 2
#define SQLITE_DEFAULT_MEMSTATUS 0
#define SQLITE_ENABLE_STAT4 1
#define SQLITE_MAX_MMAP_SIZE 0
#define SQLITE_OMIT_DEPRECATED 1
#define SQLITE_OMIT_SHARED_CACHE 1

Any help would be appreciated.

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