Re: [sqlite] Problem with many connections

[Dominique Pellé]

Grzegorz Sikorski  wrote:

> Hi Hick,
>
> You were right I was not finalizing statements and this prevented close to
> complete. When I modified my code to finalize all statements before close it
> works fine.

This implies that you have not checked your application with
valgrind which would have found leaks. Even if you fixed the
missing sqlite3_finalize(...), I would still advise to run with
valgrind which may find other hidden issues.

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

Re: [sqlite] Problem with many connections

[James K. Lowden]

On Wed, 2 Jul 2014 17:18:23 +0100
Simon Slavin  wrote:

> I hope SQLite4 changes this and if there are statements still open
> either returns an error code or automatically closes any open
> statements.  Or both.

Me too.  I can't think of any other application I use that doesn't free
all resources associated with a handle.  The analog in the OS might be
write(2) working after close(2) is called on the descriptor, instead of
EBADF.  

> It would also be nice if there was an API call you could make on a
> database handle which would return the number of statements which
> were open on that database.

Yes, and a list of statement handles.  I would represent them as a
table, perhaps MASTER_STATEMENTS.  

> I have no idea what to do about open transactions if their database
> connection is closed.

Rollback, no?  Same as if kill -9 were called on the application.  

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

Re: [sqlite] Sqlite in dead lock state when deleting records from the same table from different threads

[Igor Tandetnik]

On 7/2/2014 2:06 PM, Clemens Ladisch wrote:

Igor Tandetnik wrote:

On 7/2/2014 3:04 AM, Clemens Ladisch wrote:

If all else fails, one could try and simulate BEGIN IMMEDIATE by running
a dummy modifying statement right after BEGIN - e.g.

delete from table1 where 0;


Would this be atomic?


It would be part of the same transaction, wouldn't it?


In shared-cache mode, accesses from all threads are part of the same
transaction (as far as the database file is concerned).
SQL transactions are implemented on top of that.


File-level locks, yes - but not table-level locks, which is what appears 
to be at issue here. Each connection to the shared cache maintains a 
separate set of table-level locks.


In the OP's scenario, two connections to the same shared cache are 
locking each other out - that can't possibly be due of file-level 
locking (as you note, they both use the same underlying connection to 
the file). Besides, a contention on file-level lock is reported as 
SQLITE_BUSY, not SQLITE_LOCKED.


The hope is that issuing a "delete * from table1 where 0;" right after 
BEGIN on a connection to a shared cache will cause that connection to 
acquire a table-level write lock on table1 right from the start (or be 
blocked trying), thus avoiding a deadlock scenario.

--
Igor Tandetnik

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

Re: [sqlite] Sqlite in dead lock state when deleting records from the same table from different threads

[Simon Slavin]

On 2 Jul 2014, at 7:06pm, Clemens Ladisch  wrote:

> There is no documented way to start a transaction with a write lock,
> thus I see no mechanism that could reliably prevent deadlocks.

Right.  Sharing your cache means you're sharing your transactions and sharing 
your locks.  If you need to lock something away from you, don't share your 
cache with it.

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

Re: [sqlite] Sqlite in dead lock state when deleting records from the same table from different threads

[Clemens Ladisch]

Igor Tandetnik wrote:
> On 7/2/2014 3:04 AM, Clemens Ladisch wrote:
>>> If all else fails, one could try and simulate BEGIN IMMEDIATE by running
>>> a dummy modifying statement right after BEGIN - e.g.
>>>
>>> delete from table1 where 0;
>>
>> Would this be atomic?
>
> It would be part of the same transaction, wouldn't it?

In shared-cache mode, accesses from all threads are part of the same
transaction (as far as the database file is concerned).
SQL transactions are implemented on top of that.

> However, I have no reason to believe, other than your word, that BEGIN
> IMMEDIATE would not just work in this case. What makes you think it
> wouldn't?

In shared-cache mode,
| a transaction is implicitly a read-transaction until it first writes
| to a database table, at which point it becomes a write-transaction.
(http://www.sqlite.org/sharedcache.html)

There is no documented way to start a transaction with a write lock,
thus I see no mechanism that could reliably prevent deadlocks.


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

Re: [sqlite] Problem with many connections

[Eduardo Morras]

On Wed, 02 Jul 2014 15:50:58 +0100
Grzegorz Sikorski  wrote:

> > 1020 simultaneus connections? Perhaps iOS exhausted del max. file
> > descriptor per process.
> 1020 is the number of open operations. Number of connections opened
> at the same time is much lower (2-3).

If your connections use ORDER BY then you need another temporal file. If you 
don't define an INDEX and Sqlite decides it's needed, add one fd more. Same for 
temporal tables and others constructions. Add it to db and wal fd for each 
connection and you reach the limit.

When you say connections, do you mean a call to sqlite3_openv2? Or cycle of 
sqlite3_prepare_v2/step/finalize?

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

Re: [sqlite] Problem with many connections

[Simon Slavin]

On 2 Jul 2014, at 5:03pm, Grzegorz Sikorski  wrote:

> I admit I had missed this sentence, but to be honest, it seems to be very 
> confusing behaviour.

I'm glad you have found the problem.

It's not good and I don't think this is a good way to handle things.  I hope 
SQLite4 changes this and if there are statements still open either returns an 
error code or automatically closes any open statements.  Or both.

It would also be nice if there was an API call you could make on a database 
handle which would return the number of statements which were open on that 
database.

I have no idea what to do about open transactions if their database connection 
is closed.

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

Re: [sqlite] Porting SQLite to Nurit OS ver

[Stephan Beal]

On Wed, Jul 2, 2014 at 2:41 PM, Clemens Ladisch  wrote:

> > |Prototype causes non-standard conversion from `int' to
> `sqlite3_int64'
>
> This is just an overzealous and absurdly useless warning.
> Try to reduce the warning level.
>

AFAIK, any conversion from a less precise type to a more precise type is
does not constitute a warning. Only when precision "could" be lost
(int64==>int, or signedness differences) is a warning in order. Maybe
sqlite3_int64 on that platform is of a type smaller than int, or is
unsigned? That'd be weird, but i've seen weirder (8-byte booleans!).

> warning 572: Potentially dangerous pointer cast:
> > |sizeof `struct FuncDef' ("../sqlite/sqlite3.c",L8668/C24) differs
> from
> > |sizeof `struct FuncDef[8]' and
> > |accessing an lvalue through this pointer alias may violate
> assumptions
> > |in the ANSI C Language Reference used by the optimizer.
> > |(See (X3.159-1989 p.39,L18) on object access for more details.)
>
> I'm quite sure that this interpretation of the standard is plain wrong,
>

+1, and unless i'm misunderstanding the problem (haven't got the code on
this machine and am too lazy to google it up), it's simple to deduce that
this is a compiler bug: in C, for any array of type X, any element of that
array is guaranteed to be exactly sizeof(X). C guarantees this, or
conventional array traversal (via ptr++ resp. ++ptr) could not work.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with many connections

[Grzegorz Sikorski]

Hi Hick,

You were right I was not finalizing statements and this prevented close 
to complete. When I modified my code to finalize all statements before 
close it works fine.


However I was always checking return status from sqlite_close_v2 call 
and it was always 0. This is fine according to the documentation:


"If sqlite3_close_v2() is called on a database connection 
 that still has outstanding 
prepared statements , BLOB 
handles , and/or sqlite3_backup 
 objects then it returns 
SQLITE_OK but the deallocation of resources is deferred until all 
prepared statements , BLOB 
handles , and sqlite3_backup 
 objects are also destroyed."


I admit I had missed this sentence, but to be honest, it seems to be 
very confusing behaviour.


Regards,
Greg

On 02/07/14 16:22, Hick Gunter wrote:

What is your sequence of calls?

What do you mean by "one connection for the application lifetime" and "others on 
demand for each transaction"?

A "connection" is created/destroyed (aka opened/closed) with sqlite3_open resp. 
sqlite3_close calls. This opens/closes the underlying file handles.

A "statement" is created/destroyed with sqlite3_prepare resp. sqlite3_finalize 
calls. Unfinalized statements will prevent sqlite3_close from working and return an error 
code.

My guess is you are not finalizing your statements and not checking 
sqlite3_close return status, thus missing SQLite having a lot of open files. Is 
there a lsof command or a /proc filesystem or equivalent?

-Ursprüngliche Nachricht-
Von: Grzegorz Sikorski [mailto:g.sikor...@kelvatek.com]
Gesendet: Mittwoch, 02. Juli 2014 13:02
An: sqlite-users@sqlite.org
Betreff: [sqlite] Problem with many connections

Hi,

I am not sure if my previous email had reached the list, so I just repeat it:

I am developing sharding database using SQLite3 for embedded application. My code works 
fine up to about 1020 connections to the database. After around this number, I get an 
error "unable to open database file". I double checked, permissions are OK and 
I think I always properly close all connections and never open the same file twice (I 
normally keep one connection opened for whole application lifetime and open others on 
demand for each transaction). I found this 
topic:http://stackoverflow.com/questions/22801987/sqlite3-unable-to-open-database-file-ios
  and I am not sure if there is any reason why keeping opened connection in whole 
application lifetime is really something I should do? I would prefer to open database 
only when it is needed, to avoid risk of file corruption on power loss. Is there any 
known issue with multiple open/close operations?

Regards,
Greg


--
ExchangeDefender Message Security: Click below to verify authenticity 
https://admin.exchangedefender.com/verify.php?id=s62B2QLa005874=g.sikor...@camlintechnologies.com


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


---
Gunter Hick
Software Engineer

Scientific Games International GmbH
Klitschgasse 2 – 4, A - 1130 Vienna,
Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This e-mail is confidential and may well also be legally privileged. If you 
have received it in error, you are on notice as to its status and accordingly 
please notify us immediately by reply e-mail and then
delete this message from your system. Please do not copy it or use it for any 
purposes, or disclose its contents to any person as to do so could be a breach 
of confidence. Thank you for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



--
ExchangeDefender Message Security: Click below to verify authenticity
https://admin.exchangedefender.com/verify.php?id=s62G3fnB025594=g.sikor...@camlintechnologies.com


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

Re: [sqlite] Problem with many connections

[Hick Gunter]

What is your sequence of calls?

What do you mean by "one connection for the application lifetime" and "others 
on demand for each transaction"?

A "connection" is created/destroyed (aka opened/closed) with sqlite3_open resp. 
sqlite3_close calls. This opens/closes the underlying file handles.

A "statement" is created/destroyed with sqlite3_prepare resp. sqlite3_finalize 
calls. Unfinalized statements will prevent sqlite3_close from working and 
return an error code.

My guess is you are not finalizing your statements and not checking 
sqlite3_close return status, thus missing SQLite having a lot of open files. Is 
there a lsof command or a /proc filesystem or equivalent?

-Ursprüngliche Nachricht-
Von: Grzegorz Sikorski [mailto:g.sikor...@kelvatek.com]
Gesendet: Mittwoch, 02. Juli 2014 13:02
An: sqlite-users@sqlite.org
Betreff: [sqlite] Problem with many connections

Hi,

I am not sure if my previous email had reached the list, so I just repeat it:

I am developing sharding database using SQLite3 for embedded application. My 
code works fine up to about 1020 connections to the database. After around this 
number, I get an error "unable to open database file". I double checked, 
permissions are OK and I think I always properly close all connections and 
never open the same file twice (I normally keep one connection opened for whole 
application lifetime and open others on demand for each transaction). I found 
this 
topic:http://stackoverflow.com/questions/22801987/sqlite3-unable-to-open-database-file-ios
  and I am not sure if there is any reason why keeping opened connection in 
whole application lifetime is really something I should do? I would prefer to 
open database only when it is needed, to avoid risk of file corruption on power 
loss. Is there any known issue with multiple open/close operations?

Regards,
Greg


--
ExchangeDefender Message Security: Click below to verify authenticity 
https://admin.exchangedefender.com/verify.php?id=s62B2QLa005874=g.sikor...@camlintechnologies.com


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


---
Gunter Hick
Software Engineer

Scientific Games International GmbH
Klitschgasse 2 – 4, A - 1130 Vienna,
Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This e-mail is confidential and may well also be legally privileged. If you 
have received it in error, you are on notice as to its status and accordingly 
please notify us immediately by reply e-mail and then
delete this message from your system. Please do not copy it or use it for any 
purposes, or disclose its contents to any person as to do so could be a breach 
of confidence. Thank you for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with many connections

[Dominique Pellé]

Eduardo Morras  wrote:

> On Wed, 02 Jul 2014 12:02:27 +0100
> Grzegorz Sikorski  wrote:
>
>> Hi,
>>
>> I am not sure if my previous email had reached the list, so I just
>> repeat it:
>>
>> I am developing sharding database using SQLite3 for embedded
>> application. My code works fine up to about 1020 connections to the
>> database. After around this number, I get an error "unable to open
>> database file". I double checked, permissions are OK and I think I
>> always properly close all connections and never open the same file
>> twice (I normally keep one connection opened for whole application
>> lifetime and open others on demand for each transaction). I found
>> this
>> topic:http://stackoverflow.com/questions/22801987/sqlite3-unable-to-open-database-file-ios
>> and I am not sure if there is any reason why keeping opened
>> connection in whole application lifetime is really something I should
>> do? I would prefer to open database only when it is needed, to avoid
>> risk of file corruption on power loss. Is there any known issue with
>> multiple open/close operations?
>
> 1020 simultaneus connections? Perhaps iOS exhausted del max. file descriptor 
> per process.

That's probably the problem.
On my Linux machine, I see that the limit for number of opened
file descriptors is 1024 by default:

$ ulimit -n
1024

But you can increase the limit. Ex:

$ ulimit -n 4096
$ ulimit -n
4096

$ help ulimit
ulimit: ulimit [-SHacdefilmnpqrstuvx] [limit]
Modify shell resource limits.
...snip...
  -nthe maximum number of open file descriptors
...snip...

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

Re: [sqlite] Problem with many connections

[Grzegorz Sikorski]

On 02/07/14 15:41, Eduardo Morras wrote:

On Wed, 02 Jul 2014 12:02:27 +0100
Grzegorz Sikorski  wrote:


Hi,

I am not sure if my previous email had reached the list, so I just
repeat it:

I am developing sharding database using SQLite3 for embedded
application. My code works fine up to about 1020 connections to the
database. After around this number, I get an error "unable to open
database file". I double checked, permissions are OK and I think I
always properly close all connections and never open the same file
twice (I normally keep one connection opened for whole application
lifetime and open others on demand for each transaction). I found
this
topic:http://stackoverflow.com/questions/22801987/sqlite3-unable-to-open-database-file-ios
and I am not sure if there is any reason why keeping opened
connection in whole application lifetime is really something I should
do? I would prefer to open database only when it is needed, to avoid
risk of file corruption on power loss. Is there any known issue with
multiple open/close operations?

1020 simultaneus connections? Perhaps iOS exhausted del max. file descriptor 
per process.
1020 is the number of open operations. Number of connections opened at 
the same time is much lower (2-3).

Regards,
Greg


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



--
ExchangeDefender Message Security: Click below to verify authenticity
https://admin.exchangedefender.com/verify.php?id=s62EouOn028830=g.sikor...@camlintechnologies.com


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

Re: [sqlite] Problem with many connections

[Simon Slavin]

On 2 Jul 2014, at 1:00pm, Grzegorz Sikorski  wrote:

> On 02/07/14 12:20, Simon Slavin wrote:
>> On 2 Jul 2014, at 12:02pm, Grzegorz Sikorski  wrote:
>> 
>>> My code works fine up to about 1020 connections to the database. After 
>>> around this number, I get an error "unable to open database file". [snip]
>> This should not happen.  But I don't know why it would happen.  I have seen 
>> individual apps  make more than 5000 connections to SQLite databases (not 
>> concurrently, but closing one before opening another) without problems.  
>> Mind you, that was on a desktop computer, not under iOS.
> My application has to deal with concurrent connections to different database 
> files and sometimes do ATTACH/DETACH to another file (due to sharding).

It should be working.  I can understand a problem with 1020 connections at the 
same time: you run out of file handles.  And SQLite usually opens at least two 
files per connection: database file and journal file, so you have half the file 
handles you expect.  But if you're correctly closing files and never have more 
than a few open at the same time this shouldn't be a problem.

>> Please make sure you are checking the results returned by /all/ sqlite3_ 
>> calls, including _close(), and reporting all errors.  Don't check just the 
>> results returned by operations which make changes to the database.  But if 
>> everything returns SQLITE_OK then you shouldn't be having those problems.  
>> Please post more info, perhaps with the extended error code.
> 
> I double checked, I always have SQLITE_OK from all calls up to one which 
> gives me this error (note it is not always sqlite_open_v2, sometimes it can 
> happen on step or close calls). Is there any simple way to get extended error 
> codes you mentioned form C/C++ level? I haven't seen any API (I use 
> sqlite3_errmsg, sqlite3_errstr and sqlite3_errcode).

Sorry, I should have pointed at the pages:







>>>  I am not sure if there is any reason why keeping opened connection in 
>>> whole application lifetime is really something I should do? I would prefer 
>>> to open database only when it is needed, to avoid risk of file corruption 
>>> on power loss.
>> If your app uses one database during one 'run', and need frequent access to 
>> it, there is no need to keep closing and reopening your database.  As long 
>> as you use transactions properly and do not disable proper journal handing, 
>> you will find that SQLite is rock solid and you do not need to keep closing 
>> a database to make sure it is updated.
> This is probably true for SQLite3, however there is not many file systems 
> which guarantee the same level of confidence on power loss. We especially 
> observe frequent corruptions on most file systems (VFAT, EXT3, EXT4, UBIFS) 
> on FLASH type devices like microSD and raw NAND FLASH (even up to 30% cases). 
> EXT4 on microSD with the safest mounting options gives us best results 
> (corruption chance less that 10%, depending on SD card type).

I would guess that your device drivers are not enforcing the order in which 
write commands are executed.  Unfortunately this is basic to the ACID abilities 
of SQLite and lacking it means that even closing the database and reopening it 
again is not going to guarantee uncorrupted data.  There is nothing you can do 
about this if you lack in-order execution.

If your file system or device driver has an operation which enforces in-order 
execution, you should pick it.  For hysterical reasons, this option or command 
is often called 'EIEIO'.

As for the rest, I'm sorry I somehow decided you were using iOS.  Sorry.

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

Re: [sqlite] Problem with many connections

[Eduardo Morras]

On Wed, 02 Jul 2014 12:02:27 +0100
Grzegorz Sikorski  wrote:

> Hi,
> 
> I am not sure if my previous email had reached the list, so I just
> repeat it:
> 
> I am developing sharding database using SQLite3 for embedded
> application. My code works fine up to about 1020 connections to the
> database. After around this number, I get an error "unable to open
> database file". I double checked, permissions are OK and I think I
> always properly close all connections and never open the same file
> twice (I normally keep one connection opened for whole application
> lifetime and open others on demand for each transaction). I found
> this
> topic:http://stackoverflow.com/questions/22801987/sqlite3-unable-to-open-database-file-ios
> and I am not sure if there is any reason why keeping opened
> connection in whole application lifetime is really something I should
> do? I would prefer to open database only when it is needed, to avoid
> risk of file corruption on power loss. Is there any known issue with
> multiple open/close operations?

1020 simultaneus connections? Perhaps iOS exhausted del max. file descriptor 
per process.

> 
> Regards,
> Greg


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

[sqlite] Documentation question

[Simon Slavin]

Why does a page with this URL:



actually feature just a list of extended result codes when the URL suggests it 
should be something else ?  And is that list better or worse than the list on



so it should replace it ?

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

Re: [sqlite] Sqlite in dead lock state when deleting records from the same table from different threads

[Igor Tandetnik]

On 7/2/2014 3:04 AM, Clemens Ladisch wrote:

If all else fails, one could try and simulate BEGIN IMMEDIATE by running
a dummy modifying statement right after BEGIN - e.g.

delete from table1 where 0;


Would this be atomic?


It would be part of the same transaction, wouldn't it? The idea is to 
make the first statement a writer, so the transaction acquires write 
locks from the start.


However, I have no reason to believe, other than your word, that BEGIN 
IMMEDIATE would not just work in this case. What makes you think it 
wouldn't?

--
Igor Tandetnik

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

[sqlite] v3.8.4.3 download

[Dave Wellman]

Hi folks,

Where can I download older versions of sqlite3, specifically v3.8.4.3 (or at
least v3.8.4.x). I don't need the source code, just the windows binaries.
I'll also need the docs. 

 

I looked at the download page but that only seems to  have 3.8.5 and I
couldn't see a link to older version. My apologies if it's starting me in
the face.

 

Many thanks,

Dave

 

 

Ward Analytics Ltd - information in motion

Tel: +44 (0) 118 9740191

Fax: +44 (0) 118 9740192

www:   http://www.ward-analytics.com

 

Registered office address: The Oriel, Sydenham Road, Guildford, Surrey,
United Kingdom, GU1 3SR

Registered company number: 3917021 Registered in England and Wales.

 

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

Re: [sqlite] Porting SQLite to Nurit OS ver

[Clemens Ladisch]

Paulo Roberto wrote:
> I'm trying to port SQLite (3.08.04.03) to the Nurit 8400 POS device that
> has an ARM 7 processor and is running Nurit OS 7.50.13a
>
> The error appears when the compiler is building the sqlite.c file (the same
> flags are used).

> |Prototype causes non-standard conversion from `int' to `sqlite3_int64'

This is just an overzealous and absurdly useless warning.
Try to reduce the warning level.

> warning 572: Potentially dangerous pointer cast:
> |sizeof `struct FuncDef' ("../sqlite/sqlite3.c",L8668/C24) differs from
> |sizeof `struct FuncDef[8]' and
> |accessing an lvalue through this pointer alias may violate assumptions
> |in the ANSI C Language Reference used by the optimizer.
> |(See (X3.159-1989 p.39,L18) on object access for more details.)

I'm quite sure that this interpretation of the standard is plain wrong,
but I don't have that particular edition.  (If I were suspicious, I'd
assume that this is why they specified the page number instead of the
section.)

> [color] Max attempts exhausted at 2 with 12 regs in contention. All registers 
> spilled!!
> ...

Anyway, that compiler is just buggy.  You could try to reduce or disable
optimizations, but I wouldn't trust the compiler output anyway.

If possible, use gcc.


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

Re: [sqlite] Problem with many connections

[Grzegorz Sikorski]

On 02/07/14 12:20, Simon Slavin wrote:

On 2 Jul 2014, at 12:02pm, Grzegorz Sikorski  wrote:


My code works fine up to about 1020 connections to the database. After around this 
number, I get an error "unable to open database file". [snip]

This should not happen.  But I don't know why it would happen.  I have seen 
individual apps  make more than 5000 connections to SQLite databases (not 
concurrently, but closing one before opening another) without problems.  Mind 
you, that was on a desktop computer, not under iOS.
My application has to deal with concurrent connections to different 
database files and sometimes do ATTACH/DETACH to another file (due to 
sharding).

Please make sure you are checking the results returned by /all/ sqlite3_ calls, 
including _close(), and reporting all errors.  Don't check just the results 
returned by operations which make changes to the database.  But if everything 
returns SQLITE_OK then you shouldn't be having those problems.  Please post 
more info, perhaps with the extended error code.

I double checked, I always have SQLITE_OK from all calls up to one which 
gives me this error (note it is not always sqlite_open_v2, sometimes it 
can happen on step or close calls). Is there any simple way to get 
extended error codes you mentioned form C/C++ level? I haven't seen any 
API (I use sqlite3_errmsg, sqlite3_errstr and sqlite3_errcode).

  I am not sure if there is any reason why keeping opened connection in whole 
application lifetime is really something I should do? I would prefer to open 
database only when it is needed, to avoid risk of file corruption on power loss.

If your app uses one database during one 'run', and need frequent access to it, 
there is no need to keep closing and reopening your database.  As long as you 
use transactions properly and do not disable proper journal handing, you will 
find that SQLite is rock solid and you do not need to keep closing a database 
to make sure it is updated.
This is probably true for SQLite3, however there is not many file 
systems which guarantee the same level of confidence on power loss. We 
especially observe frequent corruptions on most file systems (VFAT, 
EXT3, EXT4, UBIFS) on FLASH type devices like microSD and raw NAND FLASH 
(even up to 30% cases). EXT4 on microSD with the safest mounting options 
gives us best results (corruption chance less that 10%, depending on SD 
card type). On HDD over SATA however, chances are extremely low (less 
than 3% of cases). We performed tests on several kernel versions with 
different drivers and have confirmation from EXT4 team that this is 
something they are aware, because they could not deal with all of 
devices and optimized the file system for most common HDDs.


Under iOS you should, of course, close the database when your app quits.  And 
you may also want to close it for the following notifications:

applicationWillResignActive:
applicationDidEnterBackground:

and reopen it for

applicationDidBecomeActive:
applicationWillEnterForeground:

It's possible that your app might want to do processing while in the background 
so you might not want to close in response to 'applicationDidEnterBackground:'.

I am using PC with Ubuntu 14.04.

Alternatively instead of opening the database on 'start' and 'wake' you can 
keep track of its 'open' status in a boolean variable and check it before each 
database operation.  This would allow an application to open a database 
connection only when needed, then keep it open until the next suspend, 
background, or quit.
I modified my code just to "fake" close for higher level layer and keep 
the connection open if there is no need for closing. With this small 
modification, the same application is able to perform more than 2 
operations with no errors.

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



--
ExchangeDefender Message Security: Click below to verify authenticity
https://admin.exchangedefender.com/verify.php?id=s62C0tBj025421=g.sikor...@camlintechnologies.com


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

Re: [sqlite] Porting SQLite to Nurit OS ver

[Kees Nuyt]

On Wed, 2 Jul 2014 12:02:01 +0100, Simon Slavin 
wrote:

>
> Thank you for your precise and useful description of what is happening.
>
> Unfortunately this list does not allow attachments, but can you paste
> a copy of the final error, the one about the register map,
> to one of your posts ?  Make sure we can see both the full
> error text and which line of code it's complaining about.

The error report was included, you just have to scroll down a bit more.

-- 
Groet, Cordialement, Pozdrawiam, Regards,

Kees Nuyt

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

Re: [sqlite] Problem with many connections

[Simon Slavin]

On 2 Jul 2014, at 12:02pm, Grzegorz Sikorski  wrote:

> My code works fine up to about 1020 connections to the database. After around 
> this number, I get an error "unable to open database file". [snip]

This should not happen.  But I don't know why it would happen.  I have seen 
individual apps  make more than 5000 connections to SQLite databases (not 
concurrently, but closing one before opening another) without problems.  Mind 
you, that was on a desktop computer, not under iOS.

Please make sure you are checking the results returned by /all/ sqlite3_ calls, 
including _close(), and reporting all errors.  Don't check just the results 
returned by operations which make changes to the database.  But if everything 
returns SQLITE_OK then you shouldn't be having those problems.  Please post 
more info, perhaps with the extended error code.

>  I am not sure if there is any reason why keeping opened connection in whole 
> application lifetime is really something I should do? I would prefer to open 
> database only when it is needed, to avoid risk of file corruption on power 
> loss.

If your app uses one database during one 'run', and need frequent access to it, 
there is no need to keep closing and reopening your database.  As long as you 
use transactions properly and do not disable proper journal handing, you will 
find that SQLite is rock solid and you do not need to keep closing a database 
to make sure it is updated.

Under iOS you should, of course, close the database when your app quits.  And 
you may also want to close it for the following notifications:

applicationWillResignActive:
applicationDidEnterBackground:

and reopen it for

applicationDidBecomeActive: 
applicationWillEnterForeground:

It's possible that your app might want to do processing while in the background 
so you might not want to close in response to 'applicationDidEnterBackground:'.

Alternatively instead of opening the database on 'start' and 'wake' you can 
keep track of its 'open' status in a boolean variable and check it before each 
database operation.  This would allow an application to open a database 
connection only when needed, then keep it open until the next suspend, 
background, or quit.

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

[sqlite] Problem with many connections

[Grzegorz Sikorski]

Hi,

I am not sure if my previous email had reached the list, so I just repeat it:

I am developing sharding database using SQLite3 for embedded application. My code works 
fine up to about 1020 connections to the database. After around this number, I get an 
error "unable to open database file". I double checked, permissions are OK and 
I think I always properly close all connections and never open the same file twice (I 
normally keep one connection opened for whole application lifetime and open others on 
demand for each transaction). I found this 
topic:http://stackoverflow.com/questions/22801987/sqlite3-unable-to-open-database-file-ios
  and I am not sure if there is any reason why keeping opened connection in whole 
application lifetime is really something I should do? I would prefer to open database 
only when it is needed, to avoid risk of file corruption on power loss. Is there any 
known issue with multiple open/close operations?

Regards,
Greg


--
ExchangeDefender Message Security: Click below to verify authenticity
https://admin.exchangedefender.com/verify.php?id=s62B2QLa005874=g.sikor...@camlintechnologies.com


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

Re: [sqlite] Porting SQLite to Nurit OS ver

[Simon Slavin]

On 1 Jul 2014, at 10:32pm, Paulo Roberto  wrote:

> There are a lot of warnings due to data type conversion and in the end an
> error relative to register map that I didn't understand.
> 
> I would appreciate any help to solve this issue and build it correctly.
> 
> The compiler used is an hcarm 4.5a.
> The building process output is below in italic and the error output in bold.

Thank you for your precise and useful description of what is happening.

Unfortunately this list does not allow attachments, but can you paste a copy of 
the final error, the one about the register map, to one of your posts ?  Make 
sure we can see both the full error text and which line of code it's 
complaining about.

The warnings about type conversion you can ignore.  Because SQLite's source 
code has to be suitable for many different compilers, it's possible to get rid 
of warnings from every compiler that may be used and they each enforce type 
conversion differently.  But we treat errors as more serious than warnings and 
may be able to figure out what's causing your register map error.

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

Re: [sqlite] Sqlite in dead lock state when deleting records from the same table from different threads

[Clemens Ladisch]

Igor Tandetnik wrote:
> On 7/1/2014 4:55 PM, Clemens Ladisch wrote:
>> To prevent deadlocks, transactions that will modify the database should
>> be started with BEGIN IMMEDIATE.  (This kind of lock is not available
>> in shared cache mode.)
>
> Are you sure?

Sorry, I meant "locking", not "lock".  The important thing is that this
gets a write lock without holding a read lock first.

> If all else fails, one could try and simulate BEGIN IMMEDIATE by running
> a dummy modifying statement right after BEGIN - e.g.
>
> delete from table1 where 0;

Would this be atomic?


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