Re: [sqlite] Database Encryption (System.Data.SQLite)

[Jim Borden]

From looking at the System.Data.SQLite source (someone please correct me if I 
am wrong)

It would use whatever cipher was provided to it via the native library that it 
was deployed with.  It's designed with sqlite encryption extension in mind but 
I suppose in theory it would work with any implementation that properly 
implements the sqlite3_key APIs / PRAGMAs.  As far as I can tell it is not a 
foregone conclusion in the C# as to what algorithm is used.

The one on Nuget just ships with the vanilla sqlite which has no encryption 
support.  Decompiling and searching for the sqlite3_key binding shows that it 
is not present in the library (which makes sense since it is guarded by an #if 
in the source base)

On 2019/04/01 18:27, "sqlite-users on behalf of Mattock Paul" 
 wrote:

All,

Would anyone be able to confirm what cipher is used for encrypting an 
SQLite database when password="" is used?


I have seen old posts online which state its 128bit but assume this is now 
wrong and am after completing a design document which requires I state the 
encryption level.


Regards,

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



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

Re: [sqlite] sqlite3AtoF giving incorrect results

[Jim Borden]

Basically a variant of the final idea you had is what we went with.  Thanks for 
the suggestions!

Jim Borden

Privacy Policy<http://www.couchbase.com/privacy-policy>
Marketing 
Preferences<http://info.couchbase.com/unsubscribe-or-manage-preferences>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3AtoF giving incorrect results

[Jim Borden]

The platforms exhibiting the behavior have so far been MSVC Windows 10 64-bit 
(both 32 and 64 bit processes), and Android x86.  iOS 64-bit was not affected 
and iOS 32-bit was not testable due to outside factors but my guess is that it 
is not affected.  macOS 64-bit was not affected.

I tried to come up with a simpler test case than the one I had but I was unsure 
of how to trigger a path that entered 'codeReal'.  For reference here was the 
query that I was working with:

SELECT fl_result(prediction('EchoModel', dict_of('max_double', 
1.7976931348623153e+308))) FROM kv_default AS _doc WHERE (_doc.flags & 1 = 0);

Between here and when dict_of was called, the value became a false infinity 
(i.e. sqlite3_value_double returned inf).  I couldn't reproduce this by just 
selecting a literal from a query, which made me think it might be taking 
another path.  Since I couldn't figure out how to trigger it that way, I just 
put in a call to strtod alongside the call to sqlite3AtoF inside of codeReal 
and compared the results.


Jim Borden

On 2019/02/28 8:50, "drhsql...@gmail.com on behalf of Richard Hipp" 
 wrote:

    On 2/27/19, Jim Borden  wrote:
>
> My theory as to why this varies by platform is because of the use of long
> double during this calculation, which can vary in size.  On the failing
> platforms they are the same size as regular double.  This results in a
> rounding error of the "scale" portion of the calculation (via 
sqlite3Pow10)
> which results in a false infinity.

Just out of curiosity, what platforms are you having trouble with.  I
tried the script below on each of Linux (Ubuntu with gcc 5.4.0), Mac
(LLVM 10.0.0), and Win10 (MSVC) and got the answer you expect in all
three cases, namely 1.79769313486231522322e+308,
8.98846567431157611547e+307, Inf.

SELECT quote(1.7976931348623153E+308);
SELECT quote(1.7976931348623153E+308/2.0);
SELECT quote(1.7976931348623153E+308*2.0);

--
D. Richard Hipp
d...@sqlite.org



Privacy Policy<http://www.couchbase.com/privacy-policy>
Marketing 
Preferences<http://info.couchbase.com/unsubscribe-or-manage-preferences>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3AtoF giving incorrect results

[Jim Borden]

Hello,

I’ve been tracking down an odd problem where some of our results from an 
application defined SQLite function are coming back as infinity on some 
platforms (notably 32-bit Android, and all Windows).  I’ve narrowed it down to 
the following:

Given the example input "1.7976931348623153e+308" encoded as UTF-8, the 
following lines of code have the following results (z is the const char* 
variable holding the text):

double value = strtod(z, NULL); // value holds 1.7976931348623153E+308
sqlite3AtoF(z, , sqlite3Strlen30(z), SQLITE_UTF8); // value now hold +Inf

My theory as to why this varies by platform is because of the use of long 
double during this calculation, which can vary in size.  On the failing 
platforms they are the same size as regular double.  This results in a rounding 
error of the "scale" portion of the calculation (via sqlite3Pow10) which 
results in a false infinity.

Would this be considered a bug, or is precision not guaranteed to the same 
level as strtod because of other factors?

Jim Borden
Senior Software Engineer
e: jim.bor...@couchbase.com
t: @borrrden



Privacy Policy<http://www.couchbase.com/privacy-policy>
Marketing 
Preferences<http://info.couchbase.com/unsubscribe-or-manage-preferences>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SEE Temp Files

[Jim Borden]

I see.  That complicates things a bit.  What happens to SQLite temp files when 
they are "done being used" (if such a concept exists).  Are they deleted or 
simply left there for the OS to clean up?

Jim Borden


On 2019/01/29 8:35, "sqlite-users on behalf of Richard Hipp" 
 
wrote:

Intermediate results may be written into a temp file, if they overflow
memory.  This could result in an information leak, yes.  On the other
hand, if you set SQLITE_TEMP_STORE=2 so that intermediate results are
held in memory, then intermediate results may be written to swap space
when the device gets under memory pressure.  So, I'm not sure it makes
that much difference.  I kind of suspect that a explicit TEMP file
would be safer, as it seems to me it will be harder to find forensic
traces of a TEMP file amid the clutter of a massive volume, and TEMP
files are likely to be overwritten quickly, which I'm not sure happens
with the swap file.  But maybe a security geek can correct me on this.
And maybe it depends a lot on what OS you are running, or what version
of that OS, or how you have the system configured.

So it is hard to know what the right thing to do is.

On 1/28/19, Jim Borden  wrote:
> Having moved from SQLCipher to SEE some time ago, the old advice that
> SQLCipher gave has stuck with me (from
> https://www.zetetic.net/sqlcipher/design/ in the Database Encryption and
> Temporary Files section):  “Provided that you taken the important step of
> disabling file base temporary stores (i.e. --enable-tempstore=yes during
> configuration and define SQLITE_TEMP_STORE=2 during build)”
>
> The reasoning behind this is that certain temporary files are not 
encrypted
> when being written and thus are a violation of the security provided by
> encryption of the database.  I couldn’t find any equivalent warnings
> regarding SQLite Encryption Extension (or anything to assure me that this
> was *not* the case with SEE) so I thought I would ask here if the same
> advice applies or can we be assured that anything SQLite with SEE writes 
to
> the disk relating to the encrypted database will also be encrypted?
>
> 
> The reason why I ask this is because there is a debate going on about the
> appropriate setting of SQLITE_TEMP_STORE on mobile devices and whether or
> not it will end up using too much memory and getting an application
> terminated.  However, the above advice would pretty much force our hand on
    > the version of our product that uses SEE.
> 
>
> Thanks,
> Jim Borden
>
>
>
> Privacy Policy<http://www.couchbase.com/privacy-policy>
> Marketing
> Preferences<http://info.couchbase.com/unsubscribe-or-manage-preferences>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


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



Privacy Policy<http://www.couchbase.com/privacy-policy>
Marketing 
Preferences<http://info.couchbase.com/unsubscribe-or-manage-preferences>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SEE Temp Files

[Jim Borden]

Having moved from SQLCipher to SEE some time ago, the old advice that SQLCipher 
gave has stuck with me (from https://www.zetetic.net/sqlcipher/design/ in the 
Database Encryption and Temporary Files section):  “Provided that you taken the 
important step of disabling file base temporary stores (i.e. 
--enable-tempstore=yes during configuration and define SQLITE_TEMP_STORE=2 
during build)”

The reasoning behind this is that certain temporary files are not encrypted 
when being written and thus are a violation of the security provided by 
encryption of the database.  I couldn’t find any equivalent warnings regarding 
SQLite Encryption Extension (or anything to assure me that this was *not* the 
case with SEE) so I thought I would ask here if the same advice applies or can 
we be assured that anything SQLite with SEE writes to the disk relating to the 
encrypted database will also be encrypted?


The reason why I ask this is because there is a debate going on about the 
appropriate setting of SQLITE_TEMP_STORE on mobile devices and whether or not 
it will end up using too much memory and getting an application terminated.  
However, the above advice would pretty much force our hand on the version of 
our product that uses SEE.


Thanks,
Jim Borden



Privacy Policy<http://www.couchbase.com/privacy-policy>
Marketing 
Preferences<http://info.couchbase.com/unsubscribe-or-manage-preferences>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite list spam from raypoker79

[Jim Borden]

For what it is worth, those messages reached my inbox twice on two different 
threads at least.

Jim Borden
Software Engineer

jim.bor...@couchbase.com

On 2017/09/13 9:02, "sqlite-users on behalf of Simon Slavin" 
<sqlite-users-boun...@mailinglists.sqlite.org on behalf of 
slav...@bigfraud.org> wrote:



On 13 Sep 2017, at 12:55am, Richard Hipp <d...@sqlite.org> wrote:

> If anybody else is seeing these emails, and/or has advice on how I can
> stop them, please send me private email.

No messages from that address reached me.  My spam/junk/trash system has 
full logging and that email address does not appear.

There are traces of its use on the web, though they don’t seem to involve 
trolling, just not the type of person to subscribe to this mailing list.

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


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

Re: [sqlite] SQLite does not support ARM platform?

[Jim Borden]

Oh, you are talking about WinCE?  I don’t think you should be thinking in terms 
of “ARM vs non-ARM” but rather “X platform vs Y platform.”  As others have 
noted, sqlite obviously runs on ARM and the issue is not with sqlite but with 
the way you are trying to compile it or the platform you are trying to compile 
it for.  It totally slipped my mind that WinCE would probably be ARM as well 
(and honestly I didn’t even realize it was still around!).  

I think WinCE has its own build for sqlite:  
http://sqlite-wince.sourceforge.net/ probably due to rather extreme limitations.

On 2017/03/21 7:41, "sqlite-users on behalf of Jaime Stuardo" 
 wrote:

I use to develop mobile applications for years using VS 2008, and in fact, 
I have just developed a MFC C++ application that runs in the ARM machine 
without problem. I need to use SQLite3 for my application. That is why I need 
to compile it.




> On 20 Mar 2017, at 23:17, Jaime Stuardo  wrote:
> 
> Thanks all who answered.
> 
> Yes... it seems a MSVC limitation, however, it is very strange. I am 
using Visual Studio 2008 which does support ARM compilation.  I did that, 
adding some compiler switches for target WIN32_CE but I got million of 
warnings, of this type:
> 
> sqlite3.c(166) : warning C4005: 'OS_VXWORKS' : macro redefinition
>sqlite3.c(158) : see previous definition of 'OS_VXWORKS'
> sqlite3.c(216) : warning C4005: 'GCC_VERSION' : macro redefinition
>sqlite3.c(214) : see previous definition of 'GCC_VERSION'
> sqlite3.c(222) : warning C4005: 'CLANG_VERSION' : macro redefinition
>sqlite3.c(220) : see previous definition of 'CLANG_VERSION'
> sqlite3.c(227) : warning C4005: 'MSVC_VERSION' : macro redefinition
>sqlite3.c(225) : see previous definition of 'MSVC_VERSION'
> sqlite3.c(10924) : warning C4005: 'SQLITE_DEFAULT_PAGE_SIZE' : macro 
redefinition
>sqlite3.c(10920) : see previous definition of 
'SQLITE_DEFAULT_PAGE_SIZE'
> sqlite3.c(10939) : warning C4005: 'SQLITE_MAX_DEFAULT_PAGE_SIZE' : macro 
redefinition
>sqlite3.c(10935) : see previous definition of 
'SQLITE_MAX_DEFAULT_PAGE_SIZE'
> sqlite3.c(11015) : warning C4005: 'SQLITE_INT_TO_PTR' : macro redefinition
>sqlite3.c(11012) : see previous definition of 'SQLITE_INT_TO_PTR'
> sqlite3.c(11016) : warning C4005: 'SQLITE_PTR_TO_INT' : macro redefinition
>sqlite3.c(11013) : see previous definition of 'SQLITE_PTR_TO_INT'
> sqlite3.c(11018) : warning C4005: 'SQLITE_INT_TO_PTR' : macro redefinition
>sqlite3.c(11015) : see previous definition of 'SQLITE_INT_TO_PTR'
> sqlite3.c(11019) : warning C4005: 'SQLITE_PTR_TO_INT' : macro redefinition
>sqlite3.c(11016) : see previous definition of 'SQLITE_PTR_TO_INT'
> sqlite3.c(11021) : warning C4005: 'SQLITE_INT_TO_PTR' : macro redefinition
> 
> I need to compile this for Windows Mobile ARM device. Is there another 
way to do it by mean of other compiler that works in Windows? I need to use 
Windows since the output file should be a DLL.
> 

Given that Windows for ARM was released around 2013 (if I remember 
correctly), I doubt that MSVC2008 will do. Sadly, GCC doesn’t support ARM for 
Windows as a target, and clang is on the way, but wasn’t ready yet last time I 
checked.
That being said, Windows for ARM/Windows RT doesn’t support “Desktop 
applications”. Is there any reason why need to have a DLL, and not the prebuilt 
package? I’d understand the need for native code, but then I’m not sure you can 
do that with anything earlier than Visual Studio 2013

> By opening sqlite3.c I found directives such as this:
> 
> if defined(_MSC_VER) && _MSC_VER>=1400
> 
> 
> That suggests me that the code is prepared for MSVC also, but it is not 
actually.
> 
> 
> Does somebody have experience compiling this in MSVC?
> 
> Regards
> Jaime


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

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


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

Re: [sqlite] SQLite does not support ARM platform?

[Jim Borden]

That error has nothing to do with SQLite, really.  SQLite is deployed on ARM 
platforms and ships as a system library on iOS.  It sounds like you are running 
into this -> 
https://stackoverflow.com/questions/11151474/can-arm-desktop-programs-be-built-using-visual-studio-2012

Keep in mind that Visual Studio ARM development is fairly new.  There was 
absolutely no support for it as far as I know before 2012 and the introduction 
of Windows ARM tablets, and even then the things you could do with it were 
extremely limited.  

On 2017/03/21 6:24, "sqlite-users on behalf of Jaime Stuardo" 
 wrote:

Hello,

I have finally given up. After almost all day trying to compile SQLite 
amalgamated source code files for ARM platform I got the conclusion that SQLite 
is not for ARM platform, even when documentation says the contrary. I have VS 
2008 and I tried to compile some C++ wrappers out there that contains 
amalgamated files but with no success

I tried several command line parameters, but nothing.

Last try was, by using only amalgamated files (shell.c, sqlite3.c, 
sqlite3.h, sqlite3ext.h). There is no science with this. I open a VS2008 
command prompt and VS2012 ARM Command prompt and compile with:

"cl sqlite3.c -link -dll -out:sqlite3.dll -machine:ARM"

In both cases, I got this error:

"crtdefs.h(338) : fatal error C1189: #error :  Compiling Desktop 
applications for the ARM platform is not supported."

That took  me to the conclusion about SQLite is only for Desktop 
applications, which is very bad. Is there another alternative or trick to make 
it compile for ARM platform?

Thanks
Jaime

___
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] bug? suggestion? add full version number to files, so tools can warn of forward-incompatibility

[Jim Borden]

For what it's worth the file stores the version number of library that most 
recently opened the file as part of the 100 byte header (the last four bytes 
specifically).  It's just not in the first 16 byte magic portion. Changing this 
would immediately cause all previous versions to report database files as "not 
a database" as far as I know.  Furthermore the file format itself is compatible 
with future versions, but certain features are not if I am not mistaken. 

You can compare bytes 96-100 with the SQLITE_VERSION_NUMBER of the tool you are 
using to achieve the effect you want. 

Jim Borden
(Sent from a mobile device)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database malformed after 6000 Inserts?

[Jim Borden]

I had a problem similar to this before. What is the threading model for access 
to the database and how is the native library compiled and configured?

Jim Borden
(Sent from a mobile device)

> On 4 Oct 2016, at 19:12, Werner Kleiner <sqlitetes...@gmail.com> wrote:
> 
> Hello,
> a program written in C# makes inserts from an SQL script into a sqlite db.
> We now saw that the database will be malformed after 6000 records.
> 
> Is there a limitation with huge inserts?
> What could be the problem?
> 
> regards
> Werner
> ___
> 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] Regarding the effects of the COMMIT keyword

[Jim Borden]

Thanks for all the input, all of your comments put me on exactly the right 
track.  I was too focused on the behavior of the writes and I didn’t consider 
the behavior of the reads.  I reviewed the logs again and it turns out there 
was a longer running query that surrounded the entire PUT / GET sequence, and 
since the same connection was being used for both queries (long, and GET) the 
GET was being held back on the outer result set.  To add to the confusion, 
another one of these long queries had just started before the previous one 
finished and so it appeared that it ran and finished quickly after the GET when 
in reality it was another starting and the previous one finishing.

Jim Borden
Software Engineer

jim.bor...@couchbase.com
 

On 2016/09/29 18:09, "sqlite-users on behalf of Clemens Ladisch" 
<sqlite-users-boun...@mailinglists.sqlite.org on behalf of clem...@ladisch.de> 
wrote:

Hick Gunter wrote:
> Reading "stale" data (i.e. the DB state at the beginning of a transaction)
> is at least almost always caused by indvertently leaving a transaction
> open. Setting the journal mode to WAL hides this problem, because the
> writer is no longer blocked by the reader's transaction. Disable WAL mode
> and you will probably find that the writer will find that the "database is
> locked".

Alternatively, put explicit BEGIN/COMMITs around all reads; then you'll
see if you accidentally try to nest transactions.


Regards,
Clemens
___
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] Regarding the effects of the COMMIT keyword

[Jim Borden]

It’s less complicated than a web service.  There is no “server” per se, only a 
lightweight listener object that can accept and respond to HTTP requests (C# 
HttpListener class).  The short explanation is that the library I develop 
(Couchbase Lite) has a replication function that allows it to communicate with 
an endpoint that implements the CouchDB sync protocol.  As part of that, in 
order to enable device to device replication, there is also a listener 
component distributed as an optional part of Couchbase Lite which embeds a 
listener for REST requests into the process.  So yes, multiple threads are at 
play here, but in the scenario there would be three at most:  The thread that 
requested the PUT, the thread that wrote to the database, and the thread the 
requested the GET.  All write requests to the database are moderated through 
the write connection thread and will block the calling thread until the write 
operation is complete.  So, as you noted, this is why I am able to return the 
correct status for any given operation (almost, aside from this odd 404 issue). 
Also, this situation is relatively rare which makes it more annoying.  

All of the code is in one process compiled together (the listener, the storage 
API, the app, etc), and written all in C# with interop calls to C.  

Thanks for your input.  The clarification about COMMIT was enough for me to 
focus my attention elsewhere and has been helpful.

Jim Borden
Software Engineer

jim.bor...@couchbase.com
 

On 2016/09/29 17:32, "sqlite-users on behalf of Simon Slavin" 
<sqlite-users-boun...@mailinglists.sqlite.org on behalf of 
slav...@bigfraud.org> wrote:


On 29 Sep 2016, at 8:59am, Jim Borden <jim.bor...@couchbase.com> wrote:

> There is a web API

If you're using a conventional server as the front end to your web service 
(e.g. Apache, with your code written in PHP/Python/C/whatever) then the server 
spawns a new process to handle each incoming request.  So it's possible for two 
calls to execute at the same time and you do have to worry about 
multiprocessing.

However, there is a question of how SQLite connections are maintained.  
Does the web service open some connections when it is started and maintain them 
throughout its life, or does it create a new connection to answer each PUT or 
GET ?  The answer is important because a savepoint is handled by a specific 
connection.  Close the connection and your savepoint vanishes.

Also, depending on how your code is written the server may be sending back 
the acknowledgement for the PUT first, and then doing the database operations 
while your program is already moving on to do something else.  But that would 
make it impossible to return a different HTTP response code if the PUT fails.  
It's more likely that the PUT operation waits until the database connection is 
finished before returning its HTTP response code, to allow it to report errors.

In which case I don't see how your problem could occur.  But someone else 
might.

Simon.
___
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] Regarding the effects of the COMMIT keyword

[Jim Borden]

I am using multiple threads, but in this instance just 2 inside of one process. 
 I do not change any PRAGMA settings other than user_version and journal_mode.  
The two connections differ only by the fact that one is read only and one is 
read-write. It’s possible that I’ve forgotten a finalize somewhere, etc, but 
unlikely because that stuff gets processed in a pretty centralized way when the 
object holding the sqlite3 object is disposed (C# term).

But in the end, my original understanding should hold that after the COMMIT 
execution finishes all data should be immediately visible to other connections 
from that point forward?

Jim Borden
Software Engineer

jim.bor...@couchbase.com
 

On 2016/09/29 16:52, "sqlite-users on behalf of Simon Slavin" 
<sqlite-users-boun...@mailinglists.sqlite.org on behalf of 
slav...@bigfraud.org> wrote:


On 29 Sep 2016, at 8:39am, Jim Borden <jim.bor...@couchbase.com> wrote:

> I found the following snippet from https://www.sqlite.org/lockingv3.html
> 
> ➢ The SQL command "COMMIT" does not actually commit the changes to disk. 
It just turns autocommit back on. Then, at the conclusion of the command, the 
regular autocommit logic takes over and causes the actual commit to disk to 
occur.
> 
> Does that mean that the actual commit happens after the execution of 
“COMMIT” (i.e. it is in a sense asynchronous)?

No.  It happens before the API call you're doing finishes.  Remember that 
the SQLite library is just a collection of procedures which you run inside your 
program.  SQLite does not run in a different thread or on a different computer 
while your program does its own thing.

>  I have two connections to a database file and reports that writing to 
one and then immediately querying from another causes the second connection to 
show the entry as missing.

Are you using multiple processes or threads ?

Are you using any PRAGMAs which look like they might speed up SQLite 
operations ?

Are the two connections accessing the file using identical file 
specifications ?

Are you forgetting to finalize a _prepare,_step,_finalize sequence ?

Simon.
___
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] Regarding the effects of the COMMIT keyword

[Jim Borden]

There is a web API, and the application flow is sending a PUT request, which 
stores the data and then returns a successful status.  After that status is 
received, a GET request is sent.  The way the write connection works is that 
everything is pumped through a single thread and all other threads must wait 
their turn while the single thread executes the work one by one (the exception 
being, as I found out painfully a few months ago, stepping through prepared 
statements).  If a transaction is entered, then any further transactions 
requested by the transaction are handled immediately as save points.  Only when 
the outermost transaction commits may the next work item be executed.  So by 
the time the successful HTTP response code is returned the data should be saved 
and committed.  That is why it is so puzzling that the next request (which does 
not use explicit transactions, just executes a few SELECT statements) returns 
404.  To me it seemed obvious that it was seeing an old state, but the question 
was why is it seeing an old state?

PRAGMA synchronous is 2 (NORMAL, I believe?)

Jim Borden
Software Engineer

jim.bor...@couchbase.com
 <http://www.couchbase.com/connect>
 

On 2016/09/29 16:51, "sqlite-users on behalf of Clemens Ladisch" 
<sqlite-users-boun...@mailinglists.sqlite.org on behalf of clem...@ladisch.de> 
wrote:

Jim Borden wrote:
> I found the following snippet from https://www.sqlite.org/lockingv3.html
>
>   The SQL command "COMMIT" does not actually commit the changes to disk.
>   It just turns autocommit back on. Then, at the conclusion of the
>   command, the regular autocommit logic takes over and causes the actual
>   commit to disk to occur.
>
> Does that mean that the actual commit happens after the execution of
> “COMMIT” (i.e. it is in a sense asynchronous)?

No, "at the conclusion" happens _before_ the execution has finished.

> I have two connections to a database file and reports that writing to
> one and then immediately querying from another causes the second
> connection to show the entry as missing.  [...]
> The database file is operating in WAL mode

In WAL mode, the writer and the reader do not block each other.  This
implies that the reader sees the old state of the database.

What exactly does "immediately" mean?  Is there some synchronization
mechanism that actually ensures that the second transaction is started
_after_ the first one has finished?  (And when doing multiple SELECTs,
it's easy to have them in a single transaction because the first one
isn't finalized early enough.)

What is the PRAGMA synchronous setting?


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


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

[sqlite] Regarding the effects of the COMMIT keyword

[Jim Borden]

I found the following snippet from https://www.sqlite.org/lockingv3.html
 
➢ The SQL command "COMMIT" does not actually commit the changes to disk. It 
just turns autocommit back on. Then, at the conclusion of the command, the 
regular autocommit logic takes over and causes the actual commit to disk to 
occur.
 
Does that mean that the actual commit happens after the execution of “COMMIT” 
(i.e. it is in a sense asynchronous)?

Could that explain some odd behavior that I am seeing?  I have two connections 
to a database file and reports that writing to one and then immediately 
querying from another causes the second connection to show the entry as 
missing.  I was working under the assumption that after COMMIT finished that 
everything would be visible to all connections but is this a false assumption?  
The suspiciousness is also due in part to this only happening when:
 
1)   There are more than ~1200 entries in the table
2)   The conclusion of the write and the read happen <= ~500ms apart (not 
very accurate timing, going by log timestamps)
 
Further queries after the initial one return the entry as expected.  Also, if 
either of the above conditions are not met then even the initial one is ok.  
The database file is operating in WAL mode on version 3.8.10.2 (technically it 
is SQLCipher) with no pragma modifications.  All access is on the same physical 
disk coming from the same process, but with two different threads (one writing 
and one reading).  Really I’d just like to know if I’m barking up the wrong 
tree here trying to explain why this odd behavior occurs.


Jim Borden
Software Engineer

jim.bor...@couchbase.com
 

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

Re: [sqlite] File is encrypted or is not a database problem

[Jim Borden]

This happened to me before and it took a while to track down, but have a look 
at your threading model as well.  What is the thread safety mode you have 
compiled with and how are you managing connections to databases across multiple 
threads?  For me the gotcha was sharing and iterating through results of 
prepared statements on multiple threads on a connection that was not used in 
serialized mode (it was in threadsafe mode which means it worked MOST of the 
time, but every so often it would fail).  The kicker is that sometimes the 
corruption was not even there (just reported by the API, but gone upon later 
inspection on the command line).  

Jim Borden
Software Engineer

jim.bor...@couchbase.com


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

Re: [sqlite] Sudden error 26 / 11

[Jim Borden]

Hey all,

As a follow up, the suggestion to change the write connection from 
multithreaded to serialized mode seems to have done the trick.  We set up some 
automated testing and we now have approximately 300 runs in a row with no 
corruption (as a control, with the multithreaded mode the database corrupted 
approximately 19 times in 147 runs).

Jim Borden
Software Engineer

jim.bor...@couchbase.com

On 6/30/16, 9:38 PM, "sqlite-users-boun...@mailinglists.sqlite.org on behalf of 
Jim Borden" <sqlite-users-boun...@mailinglists.sqlite.org on behalf of 
jim.bor...@couchbase.com> wrote:

>The pragmas I am using are just journal mode WAL and the add on pragma from 
>sqlcipher to set the key.  I am using threads fairly heavily (or rather C# is 
>via Tasks and asynchronous ops), but here is the overall model:
>
>Read connection (read only) shared between threads freely.  I can pump this 
>through the single thread as well, but it will still suffer from an error in 
>prepared statement handling (see below on write connection)
>Write connection (also internal reads) pumped through a single thread for 
>writes.  However, I realized that prepared statements were only being created 
>on the special thread but being executed on others.
>
>I read something that indicated that SQLite is never safe to be used from two 
>threads at once, but it was labeled as outdated and seems to be contradicted 
>by the newer information.
>
>I am only using one process in the program.
>
>Jim Borden
>Software Engineer
>
>jim.bor...@couchbase.com
>
>On 6/30/16, 6:26 PM, "sqlite-users-boun...@mailinglists.sqlite.org on behalf 
>of Simon Slavin" <sqlite-users-boun...@mailinglists.sqlite.org on behalf of 
>slav...@bigfraud.org> wrote:
>
>>
>>On 30 Jun 2016, at 3:11pm, Jim Borden <jim.bor...@couchbase.com> wrote:
>>
>>> There were two instances of reported corruption today
>>
>>Are you using any pragmas ?  Are you using threads ?  Are you using more than 
>>one process in one program ?
>>
>>Simon.
>>___
>>sqlite-users mailing list
>>sqlite-users@mailinglists.sqlite.org
>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] Sudden error 26 / 11

[Jim Borden]

The pragmas I am using are just journal mode WAL and the add on pragma from 
sqlcipher to set the key.  I am using threads fairly heavily (or rather C# is 
via Tasks and asynchronous ops), but here is the overall model:

Read connection (read only) shared between threads freely.  I can pump this 
through the single thread as well, but it will still suffer from an error in 
prepared statement handling (see below on write connection)
Write connection (also internal reads) pumped through a single thread for 
writes.  However, I realized that prepared statements were only being created 
on the special thread but being executed on others.

I read something that indicated that SQLite is never safe to be used from two 
threads at once, but it was labeled as outdated and seems to be contradicted by 
the newer information.

I am only using one process in the program.

Jim Borden
Software Engineer

jim.bor...@couchbase.com

On 6/30/16, 6:26 PM, "sqlite-users-boun...@mailinglists.sqlite.org on behalf of 
Simon Slavin" <sqlite-users-boun...@mailinglists.sqlite.org on behalf of 
slav...@bigfraud.org> wrote:

>
>On 30 Jun 2016, at 3:11pm, Jim Borden <jim.bor...@couchbase.com> wrote:
>
>> There were two instances of reported corruption today
>
>Are you using any pragmas ?  Are you using threads ?  Are you using more than 
>one process in one program ?
>
>Simon.
>___
>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] Sudden error 26 / 11

[Jim Borden]

Hello all,

Sorry for the delay.  It’s really hard to get this error to happen but I have 
some more relevant information.

I observed a read only connection (there are two connections open to the DB, 
one for reading only and one for writing / internal reading) suddenly start 
returning error code 26, and then error code 11 for a query so I pulled the 
database file.  It’s encrypted with SQLCipher, but I opened it with the 
sqlcipher command line and ran PRAGMA quick_check.  Normally when a corruption 
occurs this will show me what happened, but in this case it returned “ok.”  
However, the query results never ended.  I killed the process when it passed 2 
GB of output from a 6 MB database from a SELECT query.  This seems like 
corruption that the PRAGMA missed.  SELECT

One thing to note that I found was at one point during the testing today I 
found that sqlite3_prepare_v2 crashed while attempting to access invalid 
memory.  This led me to research prepared statements and find that operations 
on them are not actually thread safe.  So I will be changing the write 
connection mode to serialized and testing further but I wanted to report on the 
odd behavior I found above.  

I’m having trouble getting any output from the sqlite error log since this is a 
C# application.  I have written a P Invoke binding to the method but I’m unsure 
if it is succeeding or not.  How often should I expect output from it?  Is 
there a way I can force it to output something to test it?

As for the second suggestion, I am able to open the database with the sqlcipher 
command line so I assume that the database header is intact.  There were two 
instances of reported corruption today (perhaps three if I interpret the 
invalid memory access as the same problem showing a different result).  One 
showed “bTreeInitPage() returned error code 11” for some page in the 5000 range 
(this is usually the error I get, with various pages being listed as the bad 
ones between runs) and the other showed “ok” but had the never ending SELECT 
output on certain queries.

Please let me know if anything comes to mind.  

Jim Borden
Software Engineer

jim.bor...@couchbase.com

On 6/24/16, 4:25 AM, "sqlite-users-boun...@mailinglists.sqlite.org on behalf of 
Richard Hipp" <sqlite-users-boun...@mailinglists.sqlite.org on behalf of 
d...@sqlite.org> wrote:

>On Thu, Jun 23, 2016 at 6:13 PM, Jim Borden <jim.bor...@couchbase.com> wrote:
>> The library will be happily running along and then suddenly a SELECT 
>> statement will return error code 26 upon step.
>
>Error code 26 is SQLITE_NOTADB.  That only happens when SQLite is
>reading the 100-byte header at the beginning of the database file and
>finds that header is correct.  This is given a discinct error code
>because usually that finding means that you are trying to open
>something that is not really an SQLite database file.
>
>Debugging hints:
>
>(1) Set error logging on your application:  https://www.sqlite.org/errlog.html
>
>(2) Compile the "showdb" utility program (using "make showdb") and
>then run "showdb DATABASE dbheader" on a corrupt database file.  Send
>the results to this list.  (There are many other useful run-time
>options for "showdb" that you might also find useful.)
>
>
>>
>>
>> This connection is in “multi thread” mode I think (compiled with thread 
>> safety on but opened with NO_MUTEX).
>
>Does the problem go away if you se the threading mode to "serialized"?
>
>-- 
>D. Richard Hipp
>d...@sqlite.org
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[sqlite] Sudden error 26 / 11

[Jim Borden]

I’m having an issue with a library I am writing.  This has not happened before 
in the 1 ½ years I have been developing the library, but for some reason now 
it’s rearing its ugly head from time to time.  The library will be happily 
running along and then suddenly a SELECT statement will return error code 26 
upon step.  After a while of this, it will change to error 11 instead.  The 
database has become corrupted and the integrity check reports a different kind 
of problem with a different page each time.  This seems to happen more often on 
Windows tablets.  I have only observed the behavior in real time once (and it 
was on a VM) but logs sent from a client have shown this behavior quite a few 
times.  Mostly they are using SQLCipher based on SQLite 3.8.10.2, but I 
observed the behavior with regular SQLite 3.12.2

The library manages logical databases, which contain a few backends (one being 
SQLite).  Each logical database contains two connections to an SQLite database. 
 A read only connection that is used for reads outside the library, and a read 
/ write connection used for writing and internal reads during transactions.  
The read only connection has full mutex enabled (though I’m not sure if this is 
necessary) so that I can share it between threads without caring.  The RW 
connection is pumped through a queue on a single thread and all other threads 
must wait their turn until their job is finished.  I confirmed that no writes 
are taking place outside of this thread.  This connection is in “multi thread” 
mode I think (compiled with thread safety on but opened with NO_MUTEX).

The library caches and manages logical databases so whenever a consuming app 
requests one they will either get a new one if one hasn’t been made or the same 
one if it has.  This ensures that only one logical database is ever interacting 
with the sqlite connection handles of an sqlite database.  The logs from the 
client and my own observations have backed this up.  

From what I have read, error 11 is extremely hard to cause through library 
usage alone.  The key offences seem to be:

1) Using two versions of SQLite at once in an application
2) Bad OS file locking
3) A rogue process writing garbage into the file

The library is written in C#, and it’s true that two versions of SQLite can be 
used with it (regular SQLite or SQLCipher), however I have limited this so once 
the user has chosen the implementation they want to use they are not allowed to 
change it after that.  The logs and my observations have shown that only one 
version of the storage engine is constructed (each storage engine binds with a 
native library via P/Invoke).  

My ultimate question is:  How can I figure out where the corruption is 
happening when it does?  I’ve learned quite a bit about the actual file format 
of SQLite and spent a number of hours pouring through the SQLite files I got 
from the client with a hex editor looking for signs of bad things getting 
written but in the end that was a bit difficult for me.  The thing is, by the 
time the symptoms show it is already past when the corruption occurred from 
what I gather.  There are no network file systems involved in this, it’s purely 
local.  Are there any things I should be on the look out for?  I’d appreciate 
any advice.  


--Jim

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