Re: [sqlite] Help with row values

[Dominique Devienne]

On Wed, Feb 14, 2018 at 8:44 AM, Clemens Ladisch  wrote:

> Dominique Devienne wrote:
> > in https://www.sqlite.org/src/info/f3112e67cdb27c1a
> > to fix above ticket, I see queries with order by +a,
> > but in https://www.sqlite.org/lang_select.html#orderby
> > I don't see any obvious mention about that +.
>
> "Unary plus" does not change the value:
>
>   sqlite> select 1;
>   1
>   sqlite> select -1;
>   -1
>   sqlite> select +1;
>   1
>
> It's used to change the expression from a column reference to something
> that is computed, which means that SQLite cannot use an index for it
> (unless you created an expression index on +a):
> 


Thanks. That's interesting. But then, why use it in this context?
Why DRH wants to purposely bypass the index in this case?
How is that relevant to testing tuple / row-values comparisons? --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with row values

[Clemens Ladisch]

Dominique Devienne wrote:
> in https://www.sqlite.org/src/info/f3112e67cdb27c1a
> to fix above ticket, I see queries with order by +a,
> but in https://www.sqlite.org/lang_select.html#orderby
> I don't see any obvious mention about that +.

"Unary plus" does not change the value:

  sqlite> select 1;
  1
  sqlite> select -1;
  -1
  sqlite> select +1;
  1

It's used to change the expression from a column reference to something
that is computed, which means that SQLite cannot use an index for it
(unless you created an expression index on +a):



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

Re: [sqlite] Help with row values

[Dominique Devienne]

On Tue, Feb 13, 2018 at 7:09 PM, Richard Hipp  wrote:

> On 2/13/18, Simon Slavin  wrote:
> > On 13 Feb 2018, at 5:32pm, x  wrote:
> >
> >> Surely it should be 3 in both cases?
> >
> > I agree.  Here's verification with a version number:
>
> https://www.sqlite.org/src/tktview/f484b65f3d623059


in https://www.sqlite.org/src/info/f3112e67cdb27c1a
to fix above ticket, I see queries with order by +a,
but in https://www.sqlite.org/lang_select.html#orderby
I don't see any obvious mention about that +.

Could someone please explain what this + is? Thanks, --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about threadsafe

[Nick]

>> So I think "threadsafe=2 + more than 1 connection + busy_handler" is a
good 
>> way to use. 

>This is the normal way to use SQLite. 

I ran a test and I can still find "database is locked" even if I use
busy_handler(threadsafe=2, 2 connections).
When thread 1 executing a writing transaction, thread 2 runs the code below
at the same time: 
sqlite3_exec("BEGIN")
//SELECT
sqlite3_prepare_v2("SELECT * FROM t1;");
sqlite3_step;
sqlite3_reset;
//INSERT
sqlite3_exec("INSERT INTO t1 VALUES(1, 1, \"aweagsr\")");//database is
locked
sqlite3_exec("COMMIT");

Writing in thread 1 will no block SELECTs in thread 2 as I use WAL. But the
INSERT within the transaction of thread 2 still returns SQLITE_BUSY.
I think I have used sqlite3_busy_timeout() in right way and I find that
sqliteDefaultBusyCallback() did not be called.

Is it expected? 




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

Re: [sqlite] Question about threadsafe

[Keith Medcalf]

THREADSAFE has NOTHING to do with transactions.  Repeat, there is no value to 
which you can set the THREADSAFE constant which has any effect whatsoever on 
transactions.

Transactions are commenced ON A CONNECTION with either (a) implicitly as 
required if you do not do it yourself (known as "magical mystery mode" or "hope 
and pray" mode) or (b) when one of the BEGIN transaction statements is PREPARED 
and STEPped to completion on a CONNECTION.  That connection and all threads and 
statements associated with or prepared from that CONNECTION are now part of the 
transaction on that CONNECTION.  The transaction is ended when the "last active 
statement" is reset on the connection (for transactions that are implicitly 
commenced by sqlite3 and not explicitly by you) or when you prepare and step to 
completion a COMMIT or ROLLBACK statement on the CONNECTION.

Nothing in the above paragraph is affected by the THREADSAFE setting, the phase 
of the moon, or the depth of the snow on the ground.



The THREADSAFE setting determines the "sloppiness" of the programming style you 
use to interact with the sqlite3 library.  

THREADSAFE=0 means that your slopiness does not matter because the program is 
SINGLE-THREADED and you will make calls only from a SINGLE (MAIN) thread.

THREADSAFE=1 means that you are likely sloppy and that sqlite3 itself will 
ensure that you do not, through your slopiness, cause AHTBL.

THREADSAFE=2 means that you are extra careful to make sure that you NEVER EVER 
have the possibility of multiple entrances to the sqlite3 library on the same 
CONNECTION (which would require multiple threads, or multiple fibres, or just 
an OS that plays dipsy poodle (such as Windows)).  In case you are not 
sufficiently careful in your design and programming, sqlite3 WILL NOT take 
precautions to prevent you from killing yourself, your data, your application, 
or your database file (in other words, sqlite3 will NOT prevent AHTBL if you 
happen to be in actuality sloppy in your design or implementation)

Nothing in the above 4 paragraphs is affected by the TRANSACTION state of a 
CONNECTION, the phase of the moon, or the depth of the snow on the ground.


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Nick
>Sent: Tuesday, 13 February, 2018 02:14
>To: sqlite-users@mailinglists.sqlite.org
>Subject: Re: [sqlite] Question about threadsafe
>
>>> is it OK to use "threadsafe=2 and
>>> 2 connections" in my apps if the 2 threads may write at the same
>time?
>
>>Yes.
>
>So I think "threadsafe=2 + more than 1 connection + busy_handler" is
>a good
>way to use.
>
>Another possible way is "threadsafe=1 and share 1 connection", but if
>thread
>1 begins a transaction, then the SQL of thread 2 will also be
>executed
>within the transaction I guess. That may cause some unpredictable
>problems.
>
>BTW, if I use "threadsafe=0 and more than 1 connection", there will
>not be
>"database is locked" any more even if two threads writing at the same
>time,
>as mutex is disabled on core. Is it correct?
>
>
>
>--
>Sent from: http://sqlite.1065341.n5.nabble.com/
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] More sqlite header questions

[Simon Slavin]

On 13 Feb 2018, at 9:17pm, Richard Hipp  wrote:

> On 2/13/18, Jens Alfke  wrote:
> 
>> On iOS (can’t speak for Android) apps do get notice that they’re going to be
>> terminated.
> 
> I am told that that notification is best-effort and is not guaranteed
> to occur, nor is it guaranteed to be delivered to the application
> prior to the application being forcibly terminated.

The precise way this is done has changed from iOS version 6 to iOS version 10.  
The documentation states (and has always stated) that your app will get a 
backgrounding notification and a termination notification where possible.

One problem comes if your application does not acknowledge a backgrounding 
notification.  If this happens, then your application may not later get 
notified that it is being terminated.  So the protocol breaks down once either 
party isn't following it.

Another problem occurs if the phone receives a call at a time when the battery 
is running low.  Low battery means that the phone has little power to devote to 
background processing, since by law handling the phone call takes priority.  
This is probably the situation that Apple was talking about in Dr Hipp's post.

The most obvious problem happens if the phone runs out of power.  However 
current versions of iOS shut the device down (giving appropriate notifications) 
before the battery is truly out of power.  So they're not in much hurry.

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

Re: [sqlite] More sqlite header questions

[Richard Hipp]

On 2/13/18, Jens Alfke  wrote:
>
> On iOS (can’t speak for Android) apps do get notice that they’re going to be
> terminated.

I am told that that notification is best-effort and is not guaranteed
to occur, nor is it guaranteed to be delivered to the application
prior to the application being forcibly terminated.
-- 
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

Re: [sqlite] More sqlite header questions

[Jens Alfke]

> On Feb 13, 2018, at 12:22 PM, Chris Brody  wrote:
> 
> I think this is especially important for mobile apps which may be
> terminated without notice, especially when using hybrid app frameworks
> such as Cordova/PhoneGap.

On iOS (can’t speak for Android) apps do get notice that they’re going to be 
terminated. That’s no different for hybrid frameworks; the JS code must be 
calling a SQLite API backed by native code, and that native code can listen for 
the appropriate notifications and close databases.

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

Re: [sqlite] More sqlite header questions

[Simon Slavin]

On 13 Feb 2018, at 8:22pm, Chris Brody  wrote:

> Thanks Simon for the quick response.

You're welcome.

> Can you clarify the following:
> - Does this imply that a SQLite database may be left in some kind of
> unrecoverable, corrupted, or otherwise invalid state in case an
> application would terminate without calling sqlite3_close() on all
> open database connections?

Your program is meant to do one of two things:

A) Close all database connections.
B) Call sqlite3_shutdown(), which will close everything and release all memory 
in a correct and orderly fashion.



If you do not do either of these things, it /might/ be possible to find that 
your database files are left in an inconsistent state.  But even if it does 
happen, the next time the database is opened using the SQLite API, SQLite will 
figure out what happened and restore the database to valid state.

Rather than unexpected termination you should be more worried about a program 
crashing or losing power in the middle of a SQLite call.  But SQLite was 
written to cope with this, too.

> - If yes, what can a programmer do to protect the data in case an
> application is abruptly terminated for any reason?

If there was anything, the fix would already be built into SQLite, or it would 
be prominently listed in the "how to use SQLite" pages.

If you think you have a corrupted database, reopen it using the SQLite API, 
then (after using it if you want) close it properly.  This is always the answer 
unless you want to forensically investigate the cause of corruption. 

> - Would using SQLITE_DEFAULT_SYNCHRONOUS=3 (extra durable) help
> mitigate this kind of possible corruption?

I'm gonna let one of the developer team answer this.  I suspect that the answer 
depends on your operating system, and your storage device and its driver.

Durability is the foe of execution time.  It would be possible to make SQLite 
one third as subject to corruption -- at the cost of every command that reads 
or writes the database taking nine times as long.

> I think this is especially important for mobile apps which may be
> terminated without notice, especially when using hybrid app frameworks
> such as Cordova/PhoneGap.

No mobile OS I'm aware of will allow termination of a program while it's in the 
middle of a SQLite API call, unless some other part of the application is hung 
and refusing to terminate.  This is part of the design of mobile operating 
systems which are designed to expect unpredictable backgrounding and 
termination.

I can go into great detail about how iOS warns a program about backgrounding 
and termination, so that it can close in a graceful manner.  I assume Android 
does something similar.

>> However, the SQLite library goes through heroic measures
>> [...]
> 
> I am sure that this was at the cost of many heroic programmer hours.

Not to mention the proportion of SQLite's source code which is devoted to 
detecting and fixing corruption rather than doing mundane database work.

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

Re: [sqlite] More sqlite header questions

[Richard Hipp]

On 2/13/18, Chris Brody  wrote:
> - Does this imply that a SQLite database may be left in some kind of
> unrecoverable, corrupted, or otherwise invalid state in case an
> application would terminate without calling sqlite3_close() on all
> open database connections?

No.  The database might be left in a weird state, but it will
automatically recover the next time you or any other process opens the
database.

Unless, that is, you try to get clever and delete the *-journal or
*-wal file or do something else that interferes with the automatic
recovery process.  If you delete the information that SQLite needs to
recover then it won't, and the database will be left in its weird
state, which is now considered corrupt.

Do not do any of the bad things listed under
https://www.sqlite.org/howtocorrupt.html and you will be fine.

-- 
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

Re: [sqlite] More sqlite header questions

[Chris Brody]

On Tue, Feb 13, 2018 at 2:03 PM, Simon Slavin  wrote:
> [...]
> There are two possibilities:
>
> A) The SQLite API was used correctly, including being allowed to close all 
> files it opened.
> B) Any other situation.
>
> If (A) happened, you can predict things about the database header and you can 
> rely on SQLite documentation about the format of files it uses.  Otherwise 
> you can't.

Thanks Simon for the quick response. Can you clarify the following:
- Does this imply that a SQLite database may be left in some kind of
unrecoverable, corrupted, or otherwise invalid state in case an
application would terminate without calling sqlite3_close() on all
open database connections?
- If yes, what can a programmer do to protect the data in case an
application is abruptly terminated for any reason?
- Would using SQLITE_DEFAULT_SYNCHRONOUS=3 (extra durable) help
mitigate this kind of possible corruption?

I think this is especially important for mobile apps which may be
terminated without notice, especially when using hybrid app frameworks
such as Cordova/PhoneGap.

> However, the SQLite library goes through heroic measures
> [...]

I am sure that this was at the cost of many heroic programmer hours.

> The SQLite header is less than 100 bytes long.  It all fits within one sector 
> / page of a storage device i.e. the entire header is written in one 
> operation.  If you ever discover an inconsistent header there's a bug in 
> SQLite.  The contents of the header are listed in
>
> 

Thanks for the clarification.

>> - Any recommended explanations or resources to understand how it may
>> be possible to obtain the correct sqlite database information (such as
>> WAL or other journal mode, actual database size, number of pages,
>> page/cache size, etc.)?
>
> PRAGMAs are available for retrieving all this information.  See
>
> 
> 
> 
>
> The size of the database is page_count * page_size.  Other PRAGMAs on the 
> same page provide other information which might be covered in your "etc.".

Makes sense ... assuming that the database is not corrupted beyond the
heroic repair mechanism:)

> If you have any other questions, please do not hesitate to ask them here.

Will do. Looking forward to the requested clarification. Thanks for
the answers so far.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with row values

[x]

Thanks Richard.

From: Richard Hipp
Sent: 13 February 2018 19:17
To: SQLite mailing list
Subject: Re: [sqlite] Help with row values

Now fixed on trunk.  Thanks for the bug report.

--
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

Re: [sqlite] Help with row values

[Richard Hipp]

Now fixed on trunk.  Thanks for the bug report.

-- 
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

Re: [sqlite] More sqlite header questions

[Simon Slavin]

On 13 Feb 2018, at 5:49pm, Chris Brody  wrote:

> I still have the following questions:

Chris,

There are two possibilities:

A) The SQLite API was used correctly, including being allowed to close all 
files it opened.
B) Any other situation.

If (A) happened, you can predict things about the database header and you can 
rely on SQLite documentation about the format of files it uses.  Otherwise you 
can't.

However, the SQLite library goes through heroic measures, every time it opens a 
database, to detect whether the database was closed correctly and, if it sees 
any problem, to rescue an uncorrupt version of the database.  All you need to 
do is open the SQLite database using the SQLite API, and SQLite will take any 
measures necessary to remedy the results of any power-loss or crash, including 
returning an error message if it can't arrive at an uncorrupt version of the 
database.

> 3. rogue code writes data to the file descriptor in use by sqlite

As is true for any library which handles files, nothing can be done about 
something external overwriting the file.  However, there is no way within the 
SQLite API to list the a file handles that a SQLite connection is using.  
Anything trying to mess with SQLite's file handles would have to arrive at the 
handle using random numbers, or at the operating system level.

> My understanding is that iOS applications with shared databases can be
> especially sensitive to sqlite headers

This is not true as long as the iOS applications are using the standard SQLite 
API to access its databases.  There is nothing strange about iOS in this 
regard, it's just another version of Unix and works the same as other versions 
of Unix / Linux.

The Apple reference you provided refer to obsolete versions of iOS.  The 
current version of iOS is version 10, which no longer makes assumptions about 
SQLite database.  From the Apple page you referred to:

"This has since been resolved in iOS 8.2."

The SQLCipher page seems to indicate that SQLCipher is not compatible with a 
particular, rarely-used, feature of iOS 8.  The text in the page you supply 
states clearly that the use SQLCipher makes of this Apple feature is not 
properly supported.  This has nothing to do with SQLite.  A comment near the 
foot of the page you referred to says

"We consider this issue resolved. Thanks @sjlombardo and team!"

> - Any recommended explanations or resources that explain how iOS
> handles shared sqlite databases (in more detail, with simpler terms)?

iOS does not handle shared SQLite databases itself.  The 'sharing' is done by 
the same SQLite API used on other platforms.  iOS would not even know that a 
database is being shared.  Details on how SQLite handles shared databases can 
be found at



> - Any recommended explanations or resources to understand how and when
> sqlite header may be in inconsistent or otherwise incorrect state?

The SQLite header is less than 100 bytes long.  It all fits within one sector / 
page of a storage device i.e. the entire header is written in one operation.  
If you ever discover an inconsistent header there's a bug in SQLite.  The 
contents of the header are listed in



> - Any recommended explanations or resources to understand how it may
> be possible to obtain the correct sqlite database information (such as
> WAL or other journal mode, actual database size, number of pages,
> page/cache size, etc.)?

PRAGMAs are available for retrieving all this information.  See





The size of the database is page_count * page_size.  Other PRAGMAs on the same 
page provide other information which might be covered in your "etc.".

If you have any other questions, please do not hesitate to ask them here.

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

Re: [sqlite] Proposed registration for application/vnd.sqlite3 and +sqlite3

[Clemens Ladisch]

I wrote:
> Type name:
>
>   application
>
> Subtype name:
>
>   vnd.sqlite3

> +suffix
>
>   +sqlite3

... and they are registered:

https://www.iana.org/assignments/media-types/media-types.xhtml
https://www.iana.org/assignments/media-type-structured-suffix/media-type-structured-suffix.xhtml


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

Re: [sqlite] Help with row values

[Richard Hipp]

On 2/13/18, Simon Slavin  wrote:
> On 13 Feb 2018, at 5:32pm, x  wrote:
>
>> Surely it should be 3 in both cases?
>
> I agree.  Here's verification with a version number:

https://www.sqlite.org/src/tktview/f484b65f3d623059
-- 
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

Re: [sqlite] Help with row values

[Simon Slavin]

On 13 Feb 2018, at 5:32pm, x  wrote:

> Surely it should be 3 in both cases?

I agree.  Here's verification with a version number:

SQLite version 3.19.3 2017-06-27 16:48:08
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table t(a integer primary key);
sqlite> insert into t values (1), (3), (5);
sqlite> select count(*) from t where a>0;
3
sqlite> select count(*) from t where (a,1)>(0,0);
0

but also

sqlite> select * from t WHERE (a,0)>(0,0);
sqlite> SELECT (3,0) > (0,0);
1
sqlite> select a,typeof(a) FROM t;
1|integer
3|integer
5|integer
sqlite> select 3,typeof(3);
3|integer
sqlite> 

weird.

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

[sqlite] More sqlite header questions

[Chris Brody]

Thanks to DRH for the quick answer. So I would infer that the sqlite
file header would normally be in a consistent state, and only be in an
inconsistent or otherwise incorrect state in the following cases:
1. modification (write) is in progress (until the sqlite3 code has a
chance to finish the modification)
2. application crashed or otherwise terminated without finishing the
modification (until the application or some other sqlite program opens
the database again)
3. rogue code writes data to the file descriptor in use by sqlite
4. other causes described in http://www.sqlite.org/howtocorrupt.html
such as rogue code, rogue process, rogue script, OS bug, dishonest
hardware, etc.

My understanding is that iOS applications with shared databases can be
especially sensitive to sqlite headers as discussed in:
- https://developer.apple.com/library/content/technotes/tn2408/_index.html
- note referenced by SQLCipher project, not sure if I can understand
it 100%
-  https://github.com/sqlcipher/sqlcipher/issues/255#issuecomment-355063368
- discovery that iOS checks header of shared SQLite databases, with
special handling of sqlite databases in WAL mode

I still have the following questions:
- Am I correct to say "that the sqlite file header would normally be
in a consistent state"?
- Am I missing anything or otherwise mistaken here?
- How likely would the header continue to indicate that the database
is an sqlite database in case 1 or 2 above?
- How likely would the header continue to indicate whether the
database is in WAL or any other journal mode in case 1 or 2 above?
- Any recommended explanations or resources that explain how iOS
handles shared sqlite databases (in more detail, with simpler terms)?
- Any recommended explanations or resources to understand how and when
sqlite header may be in inconsistent or otherwise incorrect state?
- Any recommended explanations or resources to understand how it may
be possible to obtain the correct sqlite database information (such as
WAL or other journal mode, actual database size, number of pages,
page/cache size, etc.)?

On Tue, Feb 13, 2018 at 9:10 AM, Richard Hipp  wrote:
>
> On 2/13/18, Chris Brody  wrote:
> > I was wondering what would happen if there would be an application crash,
> > system crash, or power failure while SQLite is updating the file header?
> >
> > Did I miss an explanation somewhere?
>
> The content is replicated either in the rollback-journal or in the
> write-head log (depending on whether or not you are in WAL mode) and
> will be recovered automatically when the database is first opened
> after power has been restored.  See
> https://www.sqlite.org/atomiccommit.html for further information.
> --
> 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] Help with row values

[x]

sqlite> create table t(a integer primary key);
sqlite> insert into t values (1), (3), (5);
sqlite> select count(*) from t where a>0;
3
sqlite> select count(*) from t where (a,1)>(0,0);
0

Surely it should be 3 in both cases?

If t is created as ‘create table t(a)’ both return 3.

Tried it in 3.21 and 3.22.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Crash when writing header

[Richard Hipp]

On 2/13/18, Chris Brody  wrote:
> I was wondering what would happen if there would be an application crash,
> system crash, or power failure while SQLite is updating the file header?
>
> Did I miss an explanation somewhere?

The content is replicated either in the rollback-journal or in the
write-head log (depending on whether or not you are in WAL mode) and
will be recovered automatically when the database is first opened
after power has been restored.  See
https://www.sqlite.org/atomiccommit.html for further information.
-- 
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] Crash when writing header

[Chris Brody]

I was wondering what would happen if there would be an application crash,
system crash, or power failure while SQLite is updating the file header?

Did I miss an explanation somewhere?

--
From: David Raymond 
Date: Tue, Feb 6, 2018 at 12:04 PM
Subject: Re: [sqlite] Header corruption
To: SQLite mailing list 


Things stored in the first 25 bytes include page size, WAL status, and the
file change counter. So at least part of the header there gets changed with
every committed write transaction.

http://www.sqlite.org/fileformat2.html


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
Behalf Of Deon Brewis
Sent: Tuesday, February 06, 2018 11:57 AM
To: SQLite mailing list
Subject: [sqlite] Header corruption

I’m trying to track down SQLITE corruptions that seems to corrupt our
databases in a consistent way. (Running on 3.20.1).

This isn’t related to a crash or hardware failure. The app is running and
reading and writing to the database fine, and then suddenly we start
getting a SQLITE_NOTADB errors. The first error we got was on trying to
execute a ‘BEGIN TRANSACTION’, and everything thereafter started failing.

When our database headers are fine, I see this:
h: 53 51 4C 69 74 65 20 66 6F 72 6D 61 74 20 33 00 ; SQLite format
3.
0010h: 10 00 02 02 00 40 20 20 00 00 10 9B 00 01 34 42 ; .@
...›..4B

On corruption #1, I see this:
h: 15 03 01 00 20 01 91 21 85 44 17 2C ED BE 21 FA ; 
.‘!…D.,í¾!ú
0010h: 9E 74 E7 EA 9A 0A 15 2C 99 B0 B7 3B C6 F0 35 FB ;
žtçêš..,™°·;Æð5û

On corruption #2, I see this:
h: 15 03 01 00 20 0F 1E CB B1 FF 9C 1E D0 D6 BB 22 ; 
..˱ÿœ.ÐÖ»"
0010h: 15 64 D3 F4 DD 38 FB DF A3 E0 47 B8 D7 F6 21 BC ;
.dÓôÝ8ûߣàG¸×ö!¼


This bit pattern of “15 03 01 00” for the first 4 characters where it
should be “SQLi” is a bit too much of a coincidence to ignore or write off
as disk error.

The rest of the header and file after the first 25-ish bytes or so, seem
fine. It’s just these first 25  bytes that gets corrupted. If I restore
Offsets 0 to Offsets 27 out of a “working” database, I get a usable file.

Under what circumstances would SQLITE even attempt to re-write the header
string?

- Deon

___
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] Question about threadsafe

[Simon Slavin]

On 13 Feb 2018, at 9:14am, Nick  wrote:

> So I think "threadsafe=2 + more than 1 connection + busy_handler" is a good
> way to use.

This is the normal way to use SQLite.

> Another possible way is "threadsafe=1 and share 1 connection", but if thread
> 1 begins a transaction, then the SQL of thread 2 will also be executed
> within the transaction I guess. That may cause some unpredictable problems.

SQLite does not know about your threads.  It expects each connection to be used 
only in sequences which make sense.

> BTW, if I use "threadsafe=0 and more than 1 connection", there will not be
> "database is locked" any more even if two threads writing at the same time,
> as mutex is disabled on core. Is it correct? 

threadsafe=0 disables mutexes / locking.  This makes SQLite faster.  But you 
should also think of it as using other tricks to speed up SQLite.  And these 
tricks are unsafe if you try to use SQLite from two threads at once.

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

Re: [sqlite] Question about threadsafe

[Nick]

>> is it OK to use "threadsafe=2 and 
>> 2 connections" in my apps if the 2 threads may write at the same time? 

>Yes.

So I think "threadsafe=2 + more than 1 connection + busy_handler" is a good
way to use.

Another possible way is "threadsafe=1 and share 1 connection", but if thread
1 begins a transaction, then the SQL of thread 2 will also be executed
within the transaction I guess. That may cause some unpredictable problems.

BTW, if I use "threadsafe=0 and more than 1 connection", there will not be
"database is locked" any more even if two threads writing at the same time,
as mutex is disabled on core. Is it correct? 



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