[sqlite] sqlite error code 1034

[Simon Slavin]

On 23 Jun 2015, at 8:49pm, Mayank Kumar (mayankum)  
wrote:

> Thanks all for the useful inputs. One more question I have is can the 1034 
> error code ever mean that disk is full or nvram is full and hence it cannot 
> fsync ?

The error for 'disk full' should never happen at fsync().  It should happen at 
the operation which requests the additional disk space, which is probably 
fwrite().

Simon.

[sqlite] sqlite error code 1034

[Mayank Kumar (mayankum)]

Thanks all for the useful inputs. One more question I have is can the 1034 
error code ever mean that disk is full or nvram is full and hence it cannot 
fsync ?depending on different nvram drivers, may be some nvram drivers don't 
implement the interface well and hence if disk is fullm fsync fails .
Can that happen ?

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Scott Doctor
Sent: Tuesday, June 23, 2015 3:33 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] sqlite error code 1034


An fsync error with nvram may be caused by a timeout during the write cycle. 
write cycles in nvram can be many times longer than a read cycle in this type 
of memory. Trying to write a large buffer of data may take multiple seconds 
especially if adresses are non-linear depending on size and technology of the 
nvram. Try more frequent fsyncs, or extending system timeouts as a test. Errors 
happen for a reason and should not ever be ignored without knowing the cause. 
Another problem with nvram is limited number of write cycles. if a particular 
memory location is constantly in read/write cycles it is possible that the 
memory is near the end of its life and needs to be replaced.


Scott Doctor
scott at scottdoctor.com

On 6/23/2015 1:09 AM, Simon Slavin wrote:
> On 23 Jun 2015, at 6:01am, Mayank Kumar (mayankum)  
> wrote:
>
>> Do you think if sqlite3_step or sqlite3_open fail with this error, we should 
>> treat this as transaction committed and ignore the fsync error and it will 
>> be retried with the next commit or read and eventually will sync unless I 
>> see this error continuously in which case we can treat this as catastrophic.
>>
>> Also I wanted to know if fsync requires space on the filesystem since in our 
>> case nvram has little space(200mb), do you think fsync could fail because of 
>> that as well?
> You can't ignore fsync() since it indicates that future changes to the file 
> may not be valid, with no errors reporting the problems (because an error has 
> already been reported).  So yes your program should crash.
>
> But I've never seen fsync() in real life except when it indicated a hardware 
> problem of some kind -- either a badly connected cable or a bug in the mass 
> storage driver.  Software problems which might cause fsync() to fail are 
> beyond my competence.  Sorry.
>
> 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] What's the best way to pass function information to virtual table?

[Mike Nicolino]

I'll summarize what I've done; note that I don't consider myself an 'expert' in 
this area.  What I have works, but others may have suggestions on improvements. 
 (I'd actually be happy to hear any suggestions).

For my virtual tables I generate an additional 'special' column called 
'_MatchFilter' (the leading underscore is my convention identifying it as 
'special').  It's a string column that returns either empty string or the 
specified 'filter' from the where clause if specified.  This column is a 
placeholder and not useful, except when used in a where clause with 'match' or 
'='.  In these cases, my BestIndex prefers queries including _MatchFilter with 
'match' or '=' over all others and Filter takes the match/= compare value and 
passes it as a custom query to my 'table' so it can do things more efficiently. 
 Example:

select * from MyTable where _MatchFilter match "my custom query here"
select * from MyTable where _MatchFilter = "my custom query here"

You still have to handle cases where _MatchFilter gets 'and'ed with additional 
clauses of course in your BestIndex and Filter, though at worst you should be 
able to run your custom query and then in memory filter the results on the 
other clause (or vice versa).  But you can get really crazy with what you allow 
for the filter in this manner, passing it directly to your virtual table 
implementation.

What you want to do with Average is somewhat different; you really don't need a 
custom query, but a way to avoid having SQLite do the average for your table.  
One trick I can think of is adding your own special column to the table 
'_Average', that returns the calculated average.  Its somewhat strange in that 
ever 'row' would contain the average of course.  If it's still expensive to 
calculate, you might require something similar to my _MatchFilter' arrangement 
to cause '_Average' to generate and return '0' for it if not present in the 
where clause.

This is all somewhat hacky of course and non-standard if you're exposing SQL to 
users.

MikeN


-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Jerry
Sent: Monday, June 22, 2015 2:47 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] What's the best way to pass function information to 
virtual table?

Hi, MikeN.

Assuming I want to compute the average value of all keys, how to write the 
query for this using match?

For example, if I write SQL in this way

> SELECT avg(key) From table WHERE key MATCH('avg');

with MATCH info, the virtual table is able to know the query is looking for 
average value for the keys.
Now, assume the virtual table is able to compute the average faster than 
SQLite, after I get the average value, how can I return the result immediately 
without SQLite going through the aggregator avg()?

What are your queries like when you "pass arbitrary specialized queries 
directly to my virtual table"?

Thanks,
-C.Lin

2015-06-17 8:48 GMT-07:00 Mike Nicolino :

> The override of match() trick works pretty well for cases like this.  
> I've overridden match in my virtual table implementation to allow me 
> to pass arbitrary specialized queries directly to my virtual table 
> modules for cases that I know the virtual table can do a better job 
> that SQLite on that query.  Downside is if you're exposing the SQL to 
> users of course as using match in such a manner is non-standard.
>
> MikeN
>
>
> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:
> sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Clemens 
> Ladisch
> Sent: Wednesday, June 17, 2015 1:23 AM
> To: sqlite-users at mailinglists.sqlite.org
> Subject: Re: [sqlite] What's the best way to pass function information 
> to virtual table?
>
> Jerry wrote:
> > With xBestIndex and xFilter, we can pass the constraint information 
> > (e.g., those from WHERE clause) to virtual table (through struct 
> > sqlite3_index_info), so that we can locate the cursor to narrow the 
> > search space.
> > However, it does not provide information about functions used in SQL 
> > queries.
> >
> > For example,
> >
> >> SELECT MAX(key) from Table tab;
> >
> > The virtual table has no way to know the function MAX is called
>
> SQLite has a special optimization for this particular query, and 
> rewrites it as "SELECT key FROM tab ORDER BY key DESC LIMIT 1".
>
> > The virtual table provides xFindFunction to override functions 
> > (maybe this can be used to pass some information).
>
> For example, the full-text search module overrides the match() 
> function; you could do something similar:
>   SELECT key FROM tab WHERE tab MATCH 'max(key)'
>
> > But it seems only general functions can be override -- it has not 
> > effect on aggregate functions.
>
> The virtual table interface does not allow access to all the internals 
> of the query optimizer.
>
> When there is an 

[sqlite] SQLite PENDING lock before SHARED lock with WAL enabled

[Dan Kennedy]

On 06/23/2015 03:49 PM, Harmen de Jong - CoachR Group B.V. wrote:
> Is there anyone who can confirm that the first read transaction with WAL 
> enabled locks the database because it triggers the SchemaLoaded?

When a database in wal mode is first queried, SQLite takes a SHARED lock 
on the file. This is the same SHARED lock that is used when reading in 
rollback mode - except in wal mode it is held until the connection is 
closed.

And acquiring a SHARED lock requires temporarily obtaining a PENDING lock:

   http://www.sqlite.org/src/artifact/27cc135e2d0b8?ln=3030-3032

So yes, I think your analysis is correct.

Dan.




>
> Does anybody have a better suggestion to circumvent this locking issue than 
> serializing the first read transaction?
>
> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org 
> [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Harmen 
> de Jong - CoachR Group B.V.
> Sent: donderdag 18 juni 2015 15:02
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] SQLite PENDING lock before SHARED lock with WAL enabled
>
> Thanks for your feedback. We can confirm that the database is in WAL mode. 
> After some further digging, we have found out It only happens with the very 
> first query. SQLite calls sqlite3InitOne when DbHasProperty(db, 1, 
> DB_SchemaLoaded) is false. Apparently, the schema is loaded into memory via 
> the common SHARED locking mechanism. So when multiple threads perform their 
> first query at the very same moment, they lock each other out.
>
> We assume that the locking of the PENDING byte during initialization does not 
> lock out a writer on another already initialized connection. Can your confirm 
> this?
>
> We can solve the issue by using our own locking mechanism around the first 
> query, so that these queries never occur simultaneously. However, it would be 
> great if there would be a more neat solution, because some of our databases 
> have a lot of tables and therefore loading the database schema can cost a lot 
> of time (relatively). Therefore, we would like to be able to open multiple 
> parallel connections and initialize them at the same time. Do you have any 
> suggestions for this?
>
> Best regards,
>
> Harmen de Jong
>
> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org 
> [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Richard 
> Hipp
> Sent: donderdag 18 juni 2015 13:00
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] SQLite PENDING lock before SHARED lock with WAL enabled
>
> On 6/18/15, Harmen de Jong - CoachR Group B.V.  wrote:
>> A SELECT query obtains a SHARED lock on the database file. Doing so,
>> there can be many simultaneous readers according to the documentation
>> at https://www.sqlite.org/lockingv3.html. However, before the SHARED
>> lock is obtained in winLock(sqlite3_file *id, int locktype), a
>> temporary PENDING lock is requested.
>>
>> So when multiple threads start a SELECT query on different sqlite3*
>> objects at the exact same time, some of them will result in a
>> SQLITE_BUSY error, because they all request the exclusive PENDING
>> lock. Is there a way to work around this, so that I can execute SELECT 
>> queries really simultaneously?
>>
>> We have WAL enabled,
> Please double-check that you really have WAL mode enabled.  From the 
> sqlite3.exe command-line shell run commands like:
>
>   PRAGMA journal_mode;
>   .dbinfo
>
> In the second command, the "write format" and "read format" should both be 
> "2".
>
> I do not think you are really in WAL mode because a SHARED lock should never 
> happen in WAL mode.
>
>
>
>> so we were under the assumption that it should be possible to have
>> multiple readers at the same time, however the UNLOCKED -> PENDING ->
>> SHARED transition seems to intervene with the principle of having
>> multiple readers at the same time.
>>
>> This is the callstack of one of the threads:
>>
>>>  SQLService.exe!winLock(sqlite3_file * id=0x0e42aab0, int
>>> locktype=1) Line 35956
>> SQLService.exe!sqlite3OsLock(sqlite3_file *
>> id=0x0e42aab0, int lockType=1) Line 16294
>> SQLService.exe!pagerLockDb(Pager * pPager=0x0e42a998,
>> int
>> eLock=1) Line 42356
>> SQLService.exe!pager_wait_on_lock(Pager *
>> pPager=0x0e42a998, int locktype=1) Line 45027
>> SQLService.exe!sqlite3PagerSharedLock(Pager *
>> pPager=0x0e42a998) Line 46249
>> SQLService.exe!lockBtree(BtShared * pBt=0x0e429ea0)
>> Line
>> 55204
>> SQLService.exe!sqlite3BtreeBeginTrans(Btree *
>> p=0x0e428250, int wrflag=0) Line 55562
>> SQLService.exe!sqlite3InitOne(sqlite3 * db=0x0e427e38,
>> int iDb=0, char * * pzErrMsg=0x0d861654) Line 104635
>> SQLService.exe!sqlite3Init(sqlite3 * db=0x0e427e38,
>> char * *
>> pzErrMsg=0x0d861654) Line 104814
>> 

[sqlite] sqlite error code 1034

[Nico Williams]

On Tue, Jun 23, 2015 at 09:12:31PM +0100, Simon Slavin wrote:
> On 23 Jun 2015, at 8:49pm, Mayank Kumar (mayankum)  
> wrote:
> > Thanks all for the useful inputs. One more question I have is can
> > the 1034 error code ever mean that disk is full or nvram is full and
> > hence it cannot fsync ?
> 
> The error for 'disk full' should never happen at fsync().  It should
> happen at the operation which requests the additional disk space,
> which is probably fwrite().

It could happen as a result of a bug in a COW-style filesystem.  The
space needed to write metadata needs to be acquired at write()-time, not
fsync()-time, but it wouldn't surprise me to see filesystems that don't
check until sync time.

As others have pointed out, fsync() errors are essentially like EIO.

Except for "oops, someone tripped over the cable" or "the user removed
the removable storage device before we were done" type errors, these are
not recoverable.  You can't really know if the error is recoverable
though.  Simply retrying is not appropriate.  If the application is
running on a modern phone, say, then the error is probably not retriable
(most new phones don't have micro-SDcard slots anymore, right?).  If the
application is running on a server, then the error is not retriable.  If
it's running on a desktop, it might be retriable.

Nico
-- 

[sqlite] sqlite error code 1034

[Dan Kennedy]

On 06/23/2015 12:01 PM, Mayank Kumar (mayankum) wrote:
> Thanks Simon. My application has synchronous set to FULL. It seems sqlite 
> tries to do fsync for read operations as well since I also got this error 
> when executing "select * from db".
> My application currently treat this error 1034(disk io error) as catastrophic 
> error and dies.
>
> Do you think if sqlite3_step or sqlite3_open fail with this error, we should 
> treat this as transaction committed and ignore the fsync error and it will be 
> retried with the next commit or read and eventually will sync unless I see 
> this error continuously in which case we can treat this as catastrophic.

The fsync() within the SELECT statement is likely happening as part of 
rolling back a hot-journal. i.e. it is attempting to restore the 
database to a sane state following a transaction that failed due to an 
earlier IO error.

On the platform in question, is there any reason to think the system 
will recover once fsync() starts failing? Treating this as a 
catastrophic error would usually be the right way to go.

Dan




>
> Also I wanted to know if fsync requires space on the filesystem since in our 
> case nvram has little space(200mb), do you think fsync could fail because of 
> that as well?
>
> Thanks
> mayank
>
>
> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org 
> [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Simon 
> Slavin
> Sent: Monday, June 22, 2015 6:11 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] sqlite error code 1034
>
>
> On 23 Jun 2015, at 1:33am, Mayank Kumar (mayankum)  
> wrote:
>
>> We once in a while see the following errors returns by sqlite for a database 
>> created on nvram. I am trying to understand, how are other users handling 
>> this error ? if fsync fails and sqlite api fails because of this issue, does 
>> sqlite internally retry before declaring it cannot commit a transaction 
>> because of this and should the application retry or this is a non 
>> recoverable error ?
>> I know if the os fsync fails, we cannot guarantee the db remains sane and 
>> uncorrupted, in case of os crash or shutdown, but just trying to see what 
>> others are doing for their applications in this scenario.
> There's no easy way to retry the operation which failed, but you probably 
> don't have to.
>
> I have not encountered fsync() failure as part of SQLite, but to a unix user 
> it is the same as 'IO Error'.  There is no point in retrying it since it will 
> effectively be retried on the next operation of the file, including when the 
> file handle is closed.  So there is a good chance that the file will 
> eventually be synchronised anyway.  Problems may, however, occur if you have 
> multiple apps/users trying to access the file at once.
>
> 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 PENDING lock before SHARED lock with WAL enabled

[Simon Slavin]

On 23 Jun 2015, at 9:49am, Harmen de Jong - CoachR Group B.V.  wrote:

> Is there anyone who can confirm that the first read transaction with WAL 
> enabled locks the database because it triggers the SchemaLoaded?

The sqlite3_open() commands do not actually open the file.  They don't even 
make sure it exists on disk.  They just make a note in memory about which file 
to open.

The first command which actually accesses the file for reading or writing opens 
the file and reads the schema.  It is the reading of the schema, not the 
transaction, which requires the locking.  So modifying the transaction won't 
help with your problem.  The problem occurs because the file is being opened.

If, in your program, your 'open' command occurs a long time before your first 
'read' command, then perhaps you can put a dummy 'read' command into your 
program so that it happens immediately after the file is opened.  But I do not 
know if this would help in your place.

I think that this confirms your question.  Please don't hesitate to post more 
if I got it wrong or we can help.

Simon.

[sqlite] What's the best way to pass function information to virtual table?

[Jerry]

Thanks, Mike and Clemens. I got your solutions.

Seems there is no graceful way to do this.
It would be better if virtual table can provide more flexibilities,
although it might introduce some issues.

2015-06-23 9:19 GMT-07:00 Mike Nicolino :

> I'll summarize what I've done; note that I don't consider myself an
> 'expert' in this area.  What I have works, but others may have suggestions
> on improvements.  (I'd actually be happy to hear any suggestions).
>
> For my virtual tables I generate an additional 'special' column called
> '_MatchFilter' (the leading underscore is my convention identifying it as
> 'special').  It's a string column that returns either empty string or the
> specified 'filter' from the where clause if specified.  This column is a
> placeholder and not useful, except when used in a where clause with 'match'
> or '='.  In these cases, my BestIndex prefers queries including
> _MatchFilter with 'match' or '=' over all others and Filter takes the
> match/= compare value and passes it as a custom query to my 'table' so it
> can do things more efficiently.  Example:
>
> select * from MyTable where _MatchFilter match "my custom query
> here"
> select * from MyTable where _MatchFilter = "my custom query here"
>
> You still have to handle cases where _MatchFilter gets 'and'ed with
> additional clauses of course in your BestIndex and Filter, though at worst
> you should be able to run your custom query and then in memory filter the
> results on the other clause (or vice versa).  But you can get really crazy
> with what you allow for the filter in this manner, passing it directly to
> your virtual table implementation.
>
> What you want to do with Average is somewhat different; you really don't
> need a custom query, but a way to avoid having SQLite do the average for
> your table.  One trick I can think of is adding your own special column to
> the table '_Average', that returns the calculated average.  Its somewhat
> strange in that ever 'row' would contain the average of course.  If it's
> still expensive to calculate, you might require something similar to my
> _MatchFilter' arrangement to cause '_Average' to generate and return '0'
> for it if not present in the where clause.
>
> This is all somewhat hacky of course and non-standard if you're exposing
> SQL to users.
>
> MikeN
>
>
> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:
> sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Jerry
> Sent: Monday, June 22, 2015 2:47 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] What's the best way to pass function information to
> virtual table?
>
> Hi, MikeN.
>
> Assuming I want to compute the average value of all keys, how to write the
> query for this using match?
>
> For example, if I write SQL in this way
>
> > SELECT avg(key) From table WHERE key MATCH('avg');
>
> with MATCH info, the virtual table is able to know the query is looking
> for average value for the keys.
> Now, assume the virtual table is able to compute the average faster than
> SQLite, after I get the average value, how can I return the result
> immediately without SQLite going through the aggregator avg()?
>
> What are your queries like when you "pass arbitrary specialized queries
> directly to my virtual table"?
>
> Thanks,
> -C.Lin
>
> 2015-06-17 8:48 GMT-07:00 Mike Nicolino :
>
> > The override of match() trick works pretty well for cases like this.
> > I've overridden match in my virtual table implementation to allow me
> > to pass arbitrary specialized queries directly to my virtual table
> > modules for cases that I know the virtual table can do a better job
> > that SQLite on that query.  Downside is if you're exposing the SQL to
> > users of course as using match in such a manner is non-standard.
> >
> > MikeN
> >
> >
> > -Original Message-
> > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:
> > sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Clemens
> > Ladisch
> > Sent: Wednesday, June 17, 2015 1:23 AM
> > To: sqlite-users at mailinglists.sqlite.org
> > Subject: Re: [sqlite] What's the best way to pass function information
> > to virtual table?
> >
> > Jerry wrote:
> > > With xBestIndex and xFilter, we can pass the constraint information
> > > (e.g., those from WHERE clause) to virtual table (through struct
> > > sqlite3_index_info), so that we can locate the cursor to narrow the
> > > search space.
> > > However, it does not provide information about functions used in SQL
> > > queries.
> > >
> > > For example,
> > >
> > >> SELECT MAX(key) from Table tab;
> > >
> > > The virtual table has no way to know the function MAX is called
> >
> > SQLite has a special optimization for this particular query, and
> > rewrites it as "SELECT key FROM tab ORDER BY key DESC LIMIT 1".
> >
> > > The virtual table provides xFindFunction to override functions
> > > (maybe this can be used to pass some 

[sqlite] What's the best way to pass function information to virtual table?

[Clemens Ladisch]

Jerry wrote:
> Assuming I want to compute the average value of all keys, how to write the
> query for this using match?
>
> For example, if I write SQL in this way
>
>> SELECT avg(key) From table WHERE key MATCH('avg');
>
> with MATCH info, the virtual table is able to know the query is looking for
> average value for the keys.
> Now, assume the virtual table is able to compute the average faster than
> SQLite, after I get the average value, how can I return the result
> immediately without SQLite going through the aggregator avg()?

You could return a single row that already contains the average value.

> What are your queries like when you "pass arbitrary specialized queries
> directly to my virtual table"?

You would have to put everything into the MATCH operand.


The virtual table interface is not really designed for such things;
it works best when you have some custom index that merely reduces
the number of rows to be returned.


Regards,
Clemens

[sqlite] sqlite error code 1034

[Simon Slavin]

On 23 Jun 2015, at 6:01am, Mayank Kumar (mayankum)  
wrote:

> Do you think if sqlite3_step or sqlite3_open fail with this error, we should 
> treat this as transaction committed and ignore the fsync error and it will be 
> retried with the next commit or read and eventually will sync unless I see 
> this error continuously in which case we can treat this as catastrophic.
> 
> Also I wanted to know if fsync requires space on the filesystem since in our 
> case nvram has little space(200mb), do you think fsync could fail because of 
> that as well?

You can't ignore fsync() since it indicates that future changes to the file may 
not be valid, with no errors reporting the problems (because an error has 
already been reported).  So yes your program should crash.

But I've never seen fsync() in real life except when it indicated a hardware 
problem of some kind -- either a badly connected cable or a bug in the mass 
storage driver.  Software problems which might cause fsync() to fail are beyond 
my competence.  Sorry.

Simon.

[sqlite] SQLite PENDING lock before SHARED lock with WAL enabled

[Harmen de Jong - CoachR Group B.V.]

Is there anyone who can confirm that the first read transaction with WAL 
enabled locks the database because it triggers the SchemaLoaded?

Does anybody have a better suggestion to circumvent this locking issue than 
serializing the first read transaction?

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Harmen de 
Jong - CoachR Group B.V.
Sent: donderdag 18 juni 2015 15:02
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQLite PENDING lock before SHARED lock with WAL enabled

Thanks for your feedback. We can confirm that the database is in WAL mode. 
After some further digging, we have found out It only happens with the very 
first query. SQLite calls sqlite3InitOne when DbHasProperty(db, 1, 
DB_SchemaLoaded) is false. Apparently, the schema is loaded into memory via the 
common SHARED locking mechanism. So when multiple threads perform their first 
query at the very same moment, they lock each other out. 

We assume that the locking of the PENDING byte during initialization does not 
lock out a writer on another already initialized connection. Can your confirm 
this? 

We can solve the issue by using our own locking mechanism around the first 
query, so that these queries never occur simultaneously. However, it would be 
great if there would be a more neat solution, because some of our databases 
have a lot of tables and therefore loading the database schema can cost a lot 
of time (relatively). Therefore, we would like to be able to open multiple 
parallel connections and initialize them at the same time. Do you have any 
suggestions for this?

Best regards,

Harmen de Jong

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp
Sent: donderdag 18 juni 2015 13:00
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQLite PENDING lock before SHARED lock with WAL enabled

On 6/18/15, Harmen de Jong - CoachR Group B.V.  wrote:
> A SELECT query obtains a SHARED lock on the database file. Doing so, 
> there can be many simultaneous readers according to the documentation 
> at https://www.sqlite.org/lockingv3.html. However, before the SHARED 
> lock is obtained in winLock(sqlite3_file *id, int locktype), a 
> temporary PENDING lock is requested.
>
> So when multiple threads start a SELECT query on different sqlite3* 
> objects at the exact same time, some of them will result in a 
> SQLITE_BUSY error, because they all request the exclusive PENDING 
> lock. Is there a way to work around this, so that I can execute SELECT 
> queries really simultaneously?
>
> We have WAL enabled,

Please double-check that you really have WAL mode enabled.  From the 
sqlite3.exe command-line shell run commands like:

 PRAGMA journal_mode;
 .dbinfo

In the second command, the "write format" and "read format" should both be "2".

I do not think you are really in WAL mode because a SHARED lock should never 
happen in WAL mode.



> so we were under the assumption that it should be possible to have 
> multiple readers at the same time, however the UNLOCKED -> PENDING -> 
> SHARED transition seems to intervene with the principle of having 
> multiple readers at the same time.
>
> This is the callstack of one of the threads:
>
>> SQLService.exe!winLock(sqlite3_file * id=0x0e42aab0, int
>> locktype=1) Line 35956
>SQLService.exe!sqlite3OsLock(sqlite3_file * 
> id=0x0e42aab0, int lockType=1) Line 16294
>SQLService.exe!pagerLockDb(Pager * pPager=0x0e42a998, 
> int
> eLock=1) Line 42356
>SQLService.exe!pager_wait_on_lock(Pager * 
> pPager=0x0e42a998, int locktype=1) Line 45027
>SQLService.exe!sqlite3PagerSharedLock(Pager *
> pPager=0x0e42a998) Line 46249
>SQLService.exe!lockBtree(BtShared * pBt=0x0e429ea0) 
> Line
> 55204
>SQLService.exe!sqlite3BtreeBeginTrans(Btree * 
> p=0x0e428250, int wrflag=0) Line 55562
>SQLService.exe!sqlite3InitOne(sqlite3 * db=0x0e427e38, 
> int iDb=0, char * * pzErrMsg=0x0d861654) Line 104635
>SQLService.exe!sqlite3Init(sqlite3 * db=0x0e427e38, 
> char * *
> pzErrMsg=0x0d861654) Line 104814
>SQLService.exe!sqlite3ReadSchema(Parse *
> pParse=0x0d861650) Line 104851
>SQLService.exe!sqlite3LocateTable(Parse * 
> pParse=0x0d861650, int isView=0, const char * zName=0x0dd4ad38, const 
> char * zDbase=0x) Line 90098
>SQLService.exe!sqlite3LocateTableItem(Parse * 
> pParse=0x0d861650, int isView=0, SrcList_item * p=0x0dd4adc0) Line 90143
>SQLService.exe!selectExpander(Walker * 
> pWalker=0x0cd6ec58, Select * p=0x0dd4acb8) Line 109480
>SQLService.exe!sqlite3WalkSelect(Walker * 
> pWalker=0x0cd6ec58, Select * p=0x0dd4acb8) Line 80316
>

[sqlite] sqlite error code 1034

[Mayank Kumar (mayankum)]

Thanks Simon. My application has synchronous set to FULL. It seems sqlite tries 
to do fsync for read operations as well since I also got this error when 
executing "select * from db".
My application currently treat this error 1034(disk io error) as catastrophic 
error and dies.

Do you think if sqlite3_step or sqlite3_open fail with this error, we should 
treat this as transaction committed and ignore the fsync error and it will be 
retried with the next commit or read and eventually will sync unless I see this 
error continuously in which case we can treat this as catastrophic.

Also I wanted to know if fsync requires space on the filesystem since in our 
case nvram has little space(200mb), do you think fsync could fail because of 
that as well?

Thanks
mayank


-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin
Sent: Monday, June 22, 2015 6:11 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] sqlite error code 1034


On 23 Jun 2015, at 1:33am, Mayank Kumar (mayankum)  
wrote:

> We once in a while see the following errors returns by sqlite for a database 
> created on nvram. I am trying to understand, how are other users handling 
> this error ? if fsync fails and sqlite api fails because of this issue, does 
> sqlite internally retry before declaring it cannot commit a transaction 
> because of this and should the application retry or this is a non recoverable 
> error ?
> I know if the os fsync fails, we cannot guarantee the db remains sane and 
> uncorrupted, in case of os crash or shutdown, but just trying to see what 
> others are doing for their applications in this scenario.

There's no easy way to retry the operation which failed, but you probably don't 
have to.

I have not encountered fsync() failure as part of SQLite, but to a unix user it 
is the same as 'IO Error'.  There is no point in retrying it since it will 
effectively be retried on the next operation of the file, including when the 
file handle is closed.  So there is a good chance that the file will eventually 
be synchronised anyway.  Problems may, however, occur if you have multiple 
apps/users trying to access the file at once.

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

[sqlite] sqlite error code 1034

[Scott Doctor]

An fsync error with nvram may be caused by a timeout during the write 
cycle. write cycles in nvram can be many times longer than a read cycle 
in this type of memory. Trying to write a large buffer of data may take 
multiple seconds especially if adresses are non-linear depending on size 
and technology of the nvram. Try more frequent fsyncs, or extending 
system timeouts as a test. Errors happen for a reason and should not 
ever be ignored without knowing the cause. Another problem with nvram is 
limited number of write cycles. if a particular memory location is 
constantly in read/write cycles it is possible that the memory is near 
the end of its life and needs to be replaced.


Scott Doctor
scott at scottdoctor.com

On 6/23/2015 1:09 AM, Simon Slavin wrote:
> On 23 Jun 2015, at 6:01am, Mayank Kumar (mayankum)  
> wrote:
>
>> Do you think if sqlite3_step or sqlite3_open fail with this error, we should 
>> treat this as transaction committed and ignore the fsync error and it will 
>> be retried with the next commit or read and eventually will sync unless I 
>> see this error continuously in which case we can treat this as catastrophic.
>>
>> Also I wanted to know if fsync requires space on the filesystem since in our 
>> case nvram has little space(200mb), do you think fsync could fail because of 
>> that as well?
> You can't ignore fsync() since it indicates that future changes to the file 
> may not be valid, with no errors reporting the problems (because an error has 
> already been reported).  So yes your program should crash.
>
> But I've never seen fsync() in real life except when it indicated a hardware 
> problem of some kind -- either a badly connected cable or a bug in the mass 
> storage driver.  Software problems which might cause fsync() to fail are 
> beyond my competence.  Sorry.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>

[sqlite] sqlite error code 1034

[Simon Slavin]

On 23 Jun 2015, at 1:33am, Mayank Kumar (mayankum)  
wrote:

> We once in a while see the following errors returns by sqlite for a database 
> created on nvram. I am trying to understand, how are other users handling 
> this error ? if fsync fails and sqlite api fails because of this issue, does 
> sqlite internally retry before declaring it cannot commit a transaction 
> because of this and should the application retry or this is a non recoverable 
> error ?
> I know if the os fsync fails, we cannot guarantee the db remains sane and 
> uncorrupted, in case of os crash or shutdown, but just trying to see what 
> others are doing for their applications in this scenario.

There's no easy way to retry the operation which failed, but you probably don't 
have to.

I have not encountered fsync() failure as part of SQLite, but to a unix user it 
is the same as 'IO Error'.  There is no point in retrying it since it will 
effectively be retried on the next operation of the file, including when the 
file handle is closed.  So there is a good chance that the file will eventually 
be synchronised anyway.  Problems may, however, occur if you have multiple 
apps/users trying to access the file at once.

Simon.

[sqlite] sqlite error code 1034

[Mayank Kumar (mayankum)]

Hi Sqlite users
We once in a while see the following errors returns by sqlite for a database 
created on nvram. I am trying to understand, how are other users handling this 
error ? if fsync fails and sqlite api fails because of this issue, does sqlite 
internally retry before declaring it cannot commit a transaction because of 
this and should the application retry or this is a non recoverable error ?
I know if the os fsync fails, we cannot guarantee the db remains sane and 
uncorrupted, in case of os crash or shutdown, but just trying to see what 
others are doing for their applications in this scenario.
-Mayank

(1034) SQLITE_IOERR_FSYNC
The SQLITE_IOERR_FSYNC error code is an extended error 
code for 
SQLITE_IOERR indicating an I/O error 
in the VFS layer while trying to flush 
previously written content out of OS and/or disk-control buffers and into 
persistent storage. In other words, this code indicates a problem with the 
fsync() system call in unix or the FlushFileBuffers() system call in windows.