[sqlite] pragma temp_store_directory is deprecated, what is the alternative?

[James K. Lowden]

On Mon, 27 Jul 2015 20:35:30 +0100
Simon Slavin  wrote:

> On 27 Jul 2015, at 8:03pm, Zsb?n Ambrus  wrote:
> 
> > Does this work with the same environment variable name on both unix
> > and windows?  I'm asking because unix and windows programs each use
> > different conventions for what environment variable to care about
> > when determining the temporary directory, and these conventions
> > differ on unix and windows, though I believe TEMP is the customary
> > variable on windows
...
> If it's going to be documented, it would also be nice to see it on
> 
> 
> 
> which, according to my browser, doesn't mention the word
> 'environment'.

I would be nice to have clearly documented the means by which the
location of temporary files is controlled.  I assumed some flavor of
tmpfile(3) was used, but evidently SQLite doesn't rely on that
function.  

BTW, Posix is almost silent on the question.  It says TMPDIR will
define the location of a temporary store, but not how.  The C standard
library doesn't mention it for functions like tmpfile.   BSDs such
as OS X do, but according to the docs GNU glibc doesn't.  

The issue of thead safety when using environment variables is
spurious.  In a few places I can think of in libc (time zone, locale),
it consults the environment once on startup (or first use) and uses that
variable henceforward.  SQLite could do something similar, or otherwise
ensure that whenever the environment is read all threads get a
consistent view.  

--jkl

[sqlite] SQLite database becomes corrupt on iOS

[Rob Willett]

Simon,

Thanks very much for this information.

Rob.

> On 14 Aug 2015, at 17:38, Simon Slavin  wrote:
> 
> 
> On 14 Aug 2015, at 4:17pm, skywind mailing lists  
> wrote:
> 
>> this is the problem. I could never reproduce it by myself and even my 
>> customers have normally no problems. But it happens once a year or so to one 
>> of my customers.
>> Still this is very annoying as it results in data loss.
> 
> I have seen a problem like this only in one context, and it had nothing to do 
> with SQLite.  When an iDevice's power starts running low, it sends out 
> notifications to all running Apps and expects their cooperation in shutting 
> down.  An App is meant to react to the notification and shut down in less 
> than (IIRC) 6 seconds, without depending on network connections and without 
> using unusual amounts of power.
> 
> If one of the running Apps does not do this properly then the operating 
> system is allowed to terminate it at (IIRC) 10 seconds, even if it is still 
> working.  But if the application is really annoying it can block other apps 
> -- like yours -- from shutting down before the power is lost, by hogging 
> resources like CPU or file storage.  So your app gets terminated at 10 
> seconds even though it wasn't given a chance to close its files.  Either by 
> the OS or because the device just ran out of power.
> 
> This could happen because of a bug.  But I saw it happen in a big corporate 
> application where the programmer decided that it just had to do a ton of 
> communication with the server and clean up files every time the app quit.  
> Reasonable on a desktop computer and a disaster on a phone.
> 
> Testing correct quit behaviour is part of Apple's approval process.  But it 
> can't test every state the app may be in when it receives a quit notification.
> 
> So the thing that might be triggering your corruption is that your customer 
> is running another application at the same time -- one which hogs resources 
> so much that your own application doesn't get a chance to shut down cleanly 
> before the power runs out.  You're never going to be able to figure out the 
> problem in your own app, because there isn't one.  You need to look at the 
> environment as a whole.
> 
> One possible way to proceed is to ask the customer what other apps they were 
> likely to have had running when the device ran out of power.  Then google 
> those apps and see if there is any suggestion that any of them might be badly 
> written or badly behaved on shutdown.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite database becomes corrupt on iOS

[Simon Slavin]

On 14 Aug 2015, at 5:16pm, Random Coder  wrote:

> I've run into other issues
> that lead me to believe the OS is caching file writes until the app
> exits in some situations regardless of various sync calls, but I never
> did have time to track down if I was just fooling myself, or if the OS
> was indeed doing things to "help" me out.

The hardware in iDevices varies (obviously) between the many devices which have 
been produced over the years.  But I believe that you're right in that it's not 
possible for the OS to tell when data has /really/ been written to permanent 
storage.  Such writing takes a lot of power and it makes sense that a device 
would want to do it infrequently.

Simon.

[sqlite] SQLite database becomes corrupt on iOS

[skywind mailing lists]

Hi,

this is the problem. I could never reproduce it by myself and even my customers 
have normally no problems. But it happens once a year or so to one of my 
customers.
Still this is very annoying as it results in data loss.

Regards,
Hartwig

> Am 14.08.2015 um 17:15 schrieb Simon Slavin :
> 
> 
> On 14 Aug 2015, at 4:10pm, skywind mailing lists  
> wrote:
> 
>> I do not get any error message from SQLite. And the database only gets 
>> corrupted when the iDevice has to shut down due to battery issues. I have 
>> never had a customer complaining about a corrupt database during normal 
>> operation.
> 
> If you run the application on your own iDevice, and let it run out of power, 
> do you get the corruption yourself ?
> 
> Do you have any way to log when you're getting background and quit 
> notifications ?  Could you have your application store them in a text file 
> (not a SQLite database, obviously) so you can check to see whether it's 
> getting them before the device runs out of power ?
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite database becomes corrupt on iOS

[skywind mailing lists]

Hi,

I think that I can exclude for 99.99% that there are two SQLite version because 
I should get linker errors. Anyway, what is definitely guaranteed that these 
two versions know anything from each other and that they are working on the 
same database.

Regards,
Hartwig

> Am 13.08.2015 um 21:42 schrieb Eric Sink :
> 
> https://www.sqlite.org/howtocorrupt.html
> 
> I know you said you already checked this, so just ignore the following
> remark:
> 
> iOS is one of the easiest platforms to accidentally end up with "Multiple
> copies of SQLite linked into the same application".
> 
> Just sayin'.
> 
> --
> E
> 
> 
> On Thu, Aug 13, 2015 at 2:04 PM, Simon Slavin  wrote:
> 
>> 
>> On 13 Aug 2015, at 6:32pm, skywind mailing lists 
>> wrote:
>> 
>>> Before my app closes I close the database explicitly. Though I do not
>> know if this happens always when the iDevice shuts down due to battery
>> issues.
>> 
>> iDevices shut down quite a time before they'd actually run out of power.
>> Before your device shuts down your App will get notified that it's going to
>> background, and then get notified that it's going to quit.  If you're
>> handling those two notifications properly (i.e. closing all SQLite
>> connections at one or the other) then you should not be getting database
>> corruption.
>> 
>> Things to check are running your App inside some sort of memory logger,
>> e.g. valgrind .  I don't know if this is possible on the Xcode simulator, I
>> think it's built in as a 'Tool'.  And also to check the values returned
>> from /all/ your SQLite calls, even ones like _close() where if they fail
>> there's nothing you can do about it.  If one of them is not SQLITE_OK then
>> you should show an error message.  The call which corrupts the database is
>> sometimes an apparently harmless call.
>> 
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite database becomes corrupt on iOS

[skywind mailing lists]

Hi,

I do not get any error message from SQLite. And the database only gets 
corrupted when the iDevice has to shut down due to battery issues. I have never 
had a customer complaining about a corrupt database during normal operation.

Regards,
Hartwig

> Am 13.08.2015 um 21:04 schrieb Simon Slavin :
> 
> 
> On 13 Aug 2015, at 6:32pm, skywind mailing lists  
> wrote:
> 
>> Before my app closes I close the database explicitly. Though I do not know 
>> if this happens always when the iDevice shuts down due to battery issues.
> 
> iDevices shut down quite a time before they'd actually run out of power.  
> Before your device shuts down your App will get notified that it's going to 
> background, and then get notified that it's going to quit.  If you're 
> handling those two notifications properly (i.e. closing all SQLite 
> connections at one or the other) then you should not be getting database 
> corruption.
> 
> Things to check are running your App inside some sort of memory logger, e.g. 
> valgrind .  I don't know if this is possible on the Xcode simulator, I think 
> it's built in as a 'Tool'.  And also to check the values returned from /all/ 
> your SQLite calls, even ones like _close() where if they fail there's nothing 
> you can do about it.  If one of them is not SQLITE_OK then you should show an 
> error message.  The call which corrupts the database is sometimes an 
> apparently harmless call.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite database in a Windows CE6.0 mobile device

[J Trahair]

Hi

I am trying to run a SQLite database in a CE6.0 device (Psion Omnii 
XT15). I have persuaded it to run the demo from 
sqlite-netFx35-binary-PocketPC-ARM-2008-1.0.97.0.zip, which creates a db 
(containing info on broccoli and onions) from testce.exe, but on running 
my VB.net app it produces an error message - File or assembly name 
Microsoft.VisualBasic Version=8.0.0.0 ... not found.

How do I get around the Microsoft.VisualBasic problem? What resources 
does testce.exe need? Or may I see the testce source code?

Thank you.

Jonathan Trahair

[sqlite] SQLite database becomes corrupt on iOS

[Simon Slavin]

On 14 Aug 2015, at 4:17pm, skywind mailing lists  
wrote:

> this is the problem. I could never reproduce it by myself and even my 
> customers have normally no problems. But it happens once a year or so to one 
> of my customers.
> Still this is very annoying as it results in data loss.

I have seen a problem like this only in one context, and it had nothing to do 
with SQLite.  When an iDevice's power starts running low, it sends out 
notifications to all running Apps and expects their cooperation in shutting 
down.  An App is meant to react to the notification and shut down in less than 
(IIRC) 6 seconds, without depending on network connections and without using 
unusual amounts of power.

If one of the running Apps does not do this properly then the operating system 
is allowed to terminate it at (IIRC) 10 seconds, even if it is still working.  
But if the application is really annoying it can block other apps -- like yours 
-- from shutting down before the power is lost, by hogging resources like CPU 
or file storage.  So your app gets terminated at 10 seconds even though it 
wasn't given a chance to close its files.  Either by the OS or because the 
device just ran out of power.

This could happen because of a bug.  But I saw it happen in a big corporate 
application where the programmer decided that it just had to do a ton of 
communication with the server and clean up files every time the app quit.  
Reasonable on a desktop computer and a disaster on a phone.

Testing correct quit behaviour is part of Apple's approval process.  But it 
can't test every state the app may be in when it receives a quit notification.

So the thing that might be triggering your corruption is that your customer is 
running another application at the same time -- one which hogs resources so 
much that your own application doesn't get a chance to shut down cleanly before 
the power runs out.  You're never going to be able to figure out the problem in 
your own app, because there isn't one.  You need to look at the environment as 
a whole.

One possible way to proceed is to ask the customer what other apps they were 
likely to have had running when the device ran out of power.  Then google those 
apps and see if there is any suggestion that any of them might be badly written 
or badly behaved on shutdown.

Simon.

[sqlite] SQLite database becomes corrupt on iOS

[Simon Slavin]

On 14 Aug 2015, at 4:10pm, skywind mailing lists  
wrote:

> I do not get any error message from SQLite. And the database only gets 
> corrupted when the iDevice has to shut down due to battery issues. I have 
> never had a customer complaining about a corrupt database during normal 
> operation.

If you run the application on your own iDevice, and let it run out of power, do 
you get the corruption yourself ?

Do you have any way to log when you're getting background and quit 
notifications ?  Could you have your application store them in a text file (not 
a SQLite database, obviously) so you can check to see whether it's getting them 
before the device runs out of power ?

Simon.

[sqlite] SQLite database becomes corrupt on iOS

[Rousselot, Richard A]

Are all your customers on the latest version of iOS?  Maybe you need to make 
your app only compatible with current versions that behave properly?  What 
version of iOS and model is the customer that has the issue running?

Regards,

A fly on the wall...

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of skywind 
mailing lists
Sent: Friday, August 14, 2015 10:18 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQLite database becomes corrupt on iOS

Hi,

this is the problem. I could never reproduce it by myself and even my customers 
have normally no problems. But it happens once a year or so to one of my 
customers.
Still this is very annoying as it results in data loss.

Regards,
Hartwig

> Am 14.08.2015 um 17:15 schrieb Simon Slavin :
>
>
> On 14 Aug 2015, at 4:10pm, skywind mailing lists  
> wrote:
>
>> I do not get any error message from SQLite. And the database only gets 
>> corrupted when the iDevice has to shut down due to battery issues. I have 
>> never had a customer complaining about a corrupt database during normal 
>> operation.
>
> If you run the application on your own iDevice, and let it run out of power, 
> do you get the corruption yourself ?
>
> Do you have any way to log when you're getting background and quit 
> notifications ?  Could you have your application store them in a text file 
> (not a SQLite database, obviously) so you can check to see whether it's 
> getting them before the device runs out of power ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
This communication is the property of CenturyLink and may contain confidential 
or privileged information. Unauthorized use of this communication is strictly 
prohibited and may be unlawful. If you have received this communication in 
error, please immediately notify the sender by reply e-mail and destroy all 
copies of the communication and any attachments.

[sqlite] Patch to add sqlite3_trace_v2

[sqlite-mail]

Hello !  

After seem the question about "how to sync sqlite3 databases" and proposed
the creation/extension of sqlite3_trace function to make it easy to log
changes to the database I implemented what I'll call sqlite3_trace_v2 that
has an extra parameter to indicate if we only want to log sql statements that
modify the database:  

void *sqlite3_trace_v2(sqlite3 *db, void (*xTrace)(void*,const char*), void
*pArg, int onlyMod);  

If the "onlyMod" parameter is not zero than only sql statements that do not
return true to "sqlite3_stmt_readonly" call will be traced.  

This is a link to a gist on github
https://gist.github.com/mingodad/f32b680c901e360803bb  

The license for this contribution is the same of sqlite.  

Cheers !  

  

diff -urB sqlite-src-3081101/src/loadext.c sqlite-src-3081101-2/src/loadext.c
--- sqlite-src-3081101/src/loadext.c??? 2015-07-29 21:06:55.0
+0100
+++ sqlite-src-3081101-2/src/loadext.c??? 2015-08-14 11:07:01.779944573
+0100
@@ -94,6 +94,7 @@
?#ifdef SQLITE_OMIT_TRACE
?# define sqlite3_profile?? 0
?# define sqlite3_trace 0
+# define sqlite3_trace_v2 0
?#endif
?
?#ifdef SQLITE_OMIT_GET_TABLE
@@ -238,6 +239,7 @@
?#endif
?? sqlite3_total_changes,
?? sqlite3_trace,
+? sqlite3_trace_v2,
?#ifndef SQLITE_OMIT_DEPRECATED
?? sqlite3_transfer_bindings,
?#else
diff -urB sqlite-src-3081101/src/main.c sqlite-src-3081101-2/src/main.c
--- sqlite-src-3081101/src/main.c??? 2015-07-29 21:06:55.0 +0100
+++ sqlite-src-3081101-2/src/main.c??? 2015-08-14 11:07:36613070869 +0100
@@ -1748,7 +1748,7 @@
?** trace is a pointer to a function that is invoked at the start of each
?** SQL statement.
?*/
-void *sqlite3_trace(sqlite3 *db, void (*xTrace)(void*,const char*), void
*pArg){
+void *sqlite3_trace_v2(sqlite3 *db, void (*xTrace)(void*,const char*), void
*pArg, int onlyMod){
?? void *pOld;
?
?#ifdef SQLITE_ENABLE_API_ARMOR
@@ -1758,12 +1758,17 @@
?? }
?#endif
?? sqlite3_mutex_enter(db->mutex);
+? if(onlyMod) db->flags |= SQLITE_SqlTraceModOnly;
+? else db->flags &= ~SQLITE_SqlTraceModOnly;
?? pOld = db->pTraceArg;
?? db->xTrace = xTrace;
?? db->pTraceArg = pArg;
?? sqlite3_mutex_leave(db->mutex);
?? return pOld;
?}
+void *sqlite3_trace(sqlite3 *db, void (*xTrace)(void*,const char*), void
*pArg){
+? return sqlite3_trace_v2(db, xTrace, pArg, 0);
+}
?/*
?** Register a profile function.? The pArg from the previously registered 
?** profile function is returned.? 
diff -urB sqlite-src-3081101/src/sqlite3ext.h
sqlite-src-3081101-2/src/sqlite3ext.h
--- sqlite-src-3081101/src/sqlite3ext.h??? 2015-07-29 21:06:55.0
+0100
+++ sqlite-src-3081101-2/src/sqlite3ext.h??? 2015-08-14 11:08:19.798466419
+0100
@@ -143,6 +143,7 @@
?? void? (*thread_cleanup)(void);
?? int? (*total_changes)(sqlite3*);
?? void * (*trace)(sqlite3*,void(*xTrace)(void*,const char*),void*);
+? void * (*trace_v2)(sqlite3*,void(*xTrace)(void*,const char*),void*,int);
?? int? (*transfer_bindings)(sqlite3_stmt*,sqlite3_stmt*);
?? void * (*update_hook)(sqlite3*,void(*)(void*,int ,char const*,char
const*,
???
?? sqlite_int64),void*);
@@ -394,6 +395,7 @@
?#define sqlite3_thread_cleanup sqlite3_api->thread_cleanup
?#define sqlite3_total_changes? sqlite3_api->total_changes
?#define sqlite3_trace? sqlite3_api->trace
+#define sqlite3_trace_v2?? sqlite3_api->trace_v2
?#ifndef SQLITE_OMIT_DEPRECATED
?#define sqlite3_transfer_bindings? sqlite3_api->transfer_bindings
?#endif
diff -urB sqlite-src-3081101/src/sqlite.h.in
sqlite-src-3081101-2/src/sqlite.h.in
--- sqlite-src-3081101/src/sqlite.h.in??? 2015-07-29 21:06:55.0
+0100
+++ sqlite-src-3081101-2/src/sqlite.h.in??? 2015-08-14 11:05:14.484471489
+0100
@@ -2708,6 +2708,7 @@
?** subject to change in future versions of SQLite.
?*/
?void *sqlite3_trace(sqlite3*, void(*xTrace)(void*,const char*), void*);
+void *sqlite3_trace_v2(sqlite3*, void(*xTrace)(void*,const char*), void*,
int);
?SQLITE_EXPERIMENTAL void *sqlite3_profile(sqlite3*,
??? void(*xProfile)(void*,const char*,sqlite3_uint64), void*);
?
diff -urB sqlite-src-3081101/src/sqliteInt.h
sqlite-src-3081101-2/src/sqliteInt.h
--- sqlite-src-3081101/src/sqliteInt.h??? 2015-07-29 21:06:55.0
+0100
+++ sqlite-src-3081101-2/src/sqliteInt.h??? 2015-08-14 11:09:56.661611593
+0100
@@ -1287,6 +1287,7 @@
?#define SQLITE_VdbeEQP??? 0x0400? /* Debug EXPLAIN QUERY
PLAN */
?#define SQLITE_Vacuum 0x0800? /* Currently in a VACUUM
*/
?#define SQLITE_CellSizeCk 0x1000? /* Check btree cell sizes on
load */
+#define SQLITE_SqlTraceModOnly 0x8000? /* Only output queries
that modify the database */
?
?
?/*  

[sqlite] SQLite database becomes corrupt on iOS

[Richard Hipp]

On 8/14/15, skywind mailing lists  wrote:

> I think that I can exclude for 99.99% that there are two SQLite version
> because I should get linker errors.

Sadly, no.

But on the bright side, even if you do have two or more copies of
SQLite linked into your binary, everything will still work fine as
long as you do not open the same SQLite database file at the same time
in the same process using different versions of SQLite for each
connection.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] SQLite database becomes corrupt on iOS

[Random Coder]

On Fri, Aug 14, 2015 at 8:38 AM, Simon Slavin  wrote:
> I have seen a problem like this only in one context, and it had nothing to do 
> with SQLite.  When an iDevice's power starts running low, it sends out 
> notifications to all running Apps and expects their cooperation in shutting 
> down.  An App is meant to react to the notification and shut down in less 
> than (IIRC) 6 seconds, without depending on network connections and without 
> using unusual amounts of power.

And just to add to this:  This is is reasonably good conditions.

I've dealt with a bug (unrelated to SQLite) that a tester of mine
could create data corruption on an iOS device.  The tester's device
had a bad battery, so by the time the device thought it had minutes of
power, it had seconds of life left, at best.  The tester was quite
adept at getting their device in a specific state that would cause our
app to destroy a data file.  I could never reproduce the condition on
my device, and indeed this tester couldn't either.  It had to be this
specific device with it's battery.

This is a lot of words to say:  All sorts of bad things are possible
when these devices start to lose power.  I've run into other issues
that lead me to believe the OS is caching file writes until the app
exits in some situations regardless of various sync calls, but I never
did have time to track down if I was just fooling myself, or if the OS
was indeed doing things to "help" me out.