'database disk image is malformed' only on the mac

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
23 messages Options
12
Reply | Threaded
Open this post in threaded view
|

'database disk image is malformed' only on the mac

Fahad
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, &dbConnection, 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, &readOnlyDB, 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Simon Slavin-3
On 18 Aug 2017, at 12:30am, Fahad G <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: '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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Fahad
In reply to this post by Simon Slavin-3
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Simon Slavin-3
In reply to this post by Fahad


On 15 Sep 2017, at 4:38am, Fahad <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Simon Slavin-3
In reply to this post by Fahad


On 15 Sep 2017, at 2:24pm, Fahad <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Dan Kennedy-4
In reply to this post by Fahad
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+&cd=1&hl=en&ct=clnk&gl=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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: '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, &readOnlyDB,
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Fahad
In reply to this post by Dan Kennedy-4
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Fahad
In reply to this post by Dan Kennedy-4
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Simon Slavin-3
In reply to this post by Fahad


On 20 Sep 2017, at 4:20am, Fahad <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: '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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Jens Alfke-2
In reply to this post by Fahad


> On Sep 19, 2017, at 8:20 PM, Fahad <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: '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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: '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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Fahad
In reply to this post by Jens Alfke-2
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Kees Nuyt
In reply to this post by Fahad
On Wed, 20 Sep 2017 06:41:05 -0700 (MST), Fahad
<[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Dan Kennedy-4
In reply to this post by Fahad
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 0x00000000 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Keith Medcalf
In reply to this post by Fahad
>#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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: '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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
12