[sqlite] fts5aux?

[Philip Bennefall]

Hi all,

Are there any plans in fts5 to add support for something equivalent to 
fts4aux? This would be of great use to me.

Thanks!

Kind regards,

Philip Bennefall

[sqlite] [sqlite-announce] SQLite version 3.9.0

[Jan Nijtmans]

2015-10-14 21:31 GMT+02:00 D. Richard Hipp:
> Please report any problems to the sqlite-users at mailinglists.sqlite.org 
> mailing list, or directly to me.  Thanks.

Why does sqlite.h #include itself?

Although it doesn't actually create a problem, as far as I can tell.
Sorry for not noticing this before.

Regards,
 Jan Nijtmans

[sqlite] sqlite window functions extension?

[Igor Tandetnik]

On 10/14/2015 9:29 PM, Don V Nielsen wrote:
> But I am having problems with the LIMIT statement.  It throws an exception
> no matter what table alias is used: X or x2.  It says "no such column".

Ah, interesting. LIMIT clause doesn't appear to allow correlated 
subqueries; only self-contained expressions. Here goes that idea.

Something like this should work:

select X.*
from addresses X join crrt_net_non_pieces using (zip, crrt)
where net_non_pieces > (
   select count(*) from addresses x2
   where X.zip=x2.zip and X.crrt=x2.crrt and
   (x2.version_id < X.version_id or (x2.version_id = X.version_id and 
x2.rowid < X.rowid))
);

I took the liberty to simplify the ordering expression, for purposes of 
exposition (it's very long and would need to be repeated four times). 
Replace all occurrences of T.version_id with your CASE clause, 
calculated against table T. Also, I'm breaking ties by rowid; your 
original problem statement is underspecified unless there's a total 
order on Addresses.
-- 
Igor Tandetnik

[sqlite] SQLITE_CANTOPEN after days of operation

[Andrew Miles]

Yes I can see it now with lsof on the process.  Ok non-sqlite issue,
now to find where I'm not closing the file properly.  Thanks for
assistance all!


Andy

> errno 24 is EMFILE "Too many open files".  You almost certainly have a
? file-descriptor leak.
>
> -scott



On 14 October 2015 at 08:52, Andrew Miles  wrote:

> Fully opening the directory failed to fix the issue.  So in summary the
> program works for days then dies with this in the log:
>
> (14) cannot open file at line 28488 of [f5b5a13f73]
> (14) os_unix.c:28488: (24) open(/usr/share/cm160) -
> (14) cannot open file at line 28488 of [f5b5a13f73]
> (14) os_unix.c:28488: (24) open(/usr/share/cm160) -
> (14) cannot open file at line 30285 of [f5b5a13f73]
> (14) os_unix.c:30285: (24) open(/usr/share/cm160/eagleowl_stat.db-journal)
> -
> (14) statement aborts at 29: [UPDATE energy_hour_stat SET status = 1 WHERE
> record_count = 60]
>
> Any advice on what could be causing this?
>
> Andy
>
> On 11 October 2015 at 20:56, Andrew Miles  wrote:
>
>> Thanks
>>
>> Log showed it unable to open the directory and then unable to write the
>> journal file.  The directory is root writable and the process is run as
>> root so I didn't expect a problem here.  I've now modified the directory
>> access to be writable by all to see if that changes things.
>>
>> Andy
>>
>> On 5 October 2015 at 11:39, Richard Hipp  wrote:
>>
>>> On 10/5/15, Andrew Miles  wrote:
>>> > Hi
>>> >
>>> > I have a strange issue with a piece of C code using sqlite3.  The code
>>> runs
>>> > for days and then stops on an SQL insert into db with the error code
>>> > SQLITE_CANTOPEN.
>>> >
>>> > Further info:
>>> >
>>> > 1) The program is a status monitoring app - it writes values into the
>>> SQL
>>> > db once every 60 seconds.  Average duration is about 2 days before
>>> dying so
>>> > roughly 3000 successful writes before failure.
>>> > 2) The SQL db is kept open for the entire time i.e. I don't open/close
>>> on
>>> > every transaction.
>>> > 3) There is another process accessing the same db but read only
>>> > 4) I tried waiting 5 secs on the first CANTOPEN and trying again in
>>> case
>>> > the other process had locked the db but same message.  Even closing
>>> the db
>>> > and reopening won't free the error.  The only method is to stop the
>>> program
>>> > and restart, then it works again every time.
>>> > 5) I ran lsof on the db, only one process (this one) had the file open
>>> > 6) The extended error code reported is 14 i.e. no further information
>>> > 7) Disk has 5GB free
>>> >
>>> > Any advice on how to debug further?
>>> >
>>>
>>> Activate the error and warning log.  https://www.sqlite.org/errlog.html
>>>
>>> --
>>> D. Richard Hipp
>>> drh at sqlite.org
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users at mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
>>
>

[sqlite] sqlite window functions extension?

[Don V Nielsen]

The only reason NUM exists in the result set is so I could use it to limit
the outer most select.

But I am having problems with the LIMIT statement.  It throws an exception
no matter what table alias is used: X or x2.  It says "no such column".

Thanks for your time, by the way.

On Wed, Oct 14, 2015 at 6:08 PM, Igor Tandetnik  wrote:

> On 10/14/2015 6:24 PM, Don V Nielsen wrote:
>
>> zip crrt   version_id   NUM   segment
>> [truncated num 1..38]
>> 53001   R501   0060 39xx
>> 53001   R501   0060 40xx
>> 53001   R501   0060 41xx
>> 53001   R501   0060 42xx
>> [truncated num 1..24]
>> 53001   R502   0060 21xx
>> 53001   R502   0060 22xx
>> 53001   R502   0060 23xx
>> 53001   R502   0060 24xx
>>
>
> select * from addresses X where rowid in (
>   select x2.rowid from addresses x2 where X.zip=x2.zip and X.crrt=x2.crrt
>   order by (CASE ...)
>   limit ifnull( (
>  select net_non_pieces from crrt_net_non net
>  where X.zip=net.zip and X.crrt=net.crrt
>   ), 0)
> );
>
> Do you actually need NUM column in the resultset? That one would be tricky
> to pull off.
> --
> Igor Tandetnik
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] printf with thousands separator

[Bruce Hohl]

I create quite a few ad hoc queries from data tables with large numbers and
the default floating output is a pain to read.

I can get the output format I desire with something elaborate like the
following but this is too much SQL for ad hoc queries.

select
  substr(
replace(
  substr(' 9123456.00',-15,3)||
  substr(' 9123456.00',-12,3)||","||
  substr(' 9123456.00',-9,3) ||","||
  substr(' 9123456.00',-6,6)
," ,","  ")
  ,-15,15);
Returns 15 characters as follows:
   9,123,456.00

The bash shell can do this as follows - first without & then with thousand
separator.
$ printf "%15.2f\n" -23456789.12789
   -23456789.13

$ printf "%'15.2f\n" -23456789.12789
 -23,456,789.13

With SQLite the half quote character seems not recognized and no output is
returned:
sqlite> .version
SQLite 3.8.11.1 2015-07-29 20:00:57 cf538e2783e468bbc25e7cb2a9ee64d3e0e80b2f

sqlite> select printf("%15.2f",-23456789.12789);
   -23456789.13

sqlite> select printf("%'15.2f",-23456789.12789);

sqlite>

A search for an answer suggested the locale setting might be at fault. My
bash output for locale is as follows but I don't if/how this relates to
SQLite.
$ locale
LANG=en_US.UTF-8
LANGUAGE=en_US
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"

Is printf with thousands separator working for anyone?  If so, how?  Any
suggestions?  Thanks for any comments.

[sqlite] sqlite window functions extension?

[Igor Tandetnik]

On 10/14/2015 6:24 PM, Don V Nielsen wrote:
> zip crrt   version_id   NUM   segment
> [truncated num 1..38]
> 53001   R501   0060 39xx
> 53001   R501   0060 40xx
> 53001   R501   0060 41xx
> 53001   R501   0060 42xx
> [truncated num 1..24]
> 53001   R502   0060 21xx
> 53001   R502   0060 22xx
> 53001   R502   0060 23xx
> 53001   R502   0060 24xx

select * from addresses X where rowid in (
   select x2.rowid from addresses x2 where X.zip=x2.zip and X.crrt=x2.crrt
   order by (CASE ...)
   limit ifnull( (
  select net_non_pieces from crrt_net_non net
  where X.zip=net.zip and X.crrt=net.crrt
   ), 0)
);

Do you actually need NUM column in the resultset? That one would be 
tricky to pull off.
-- 
Igor Tandetnik

[sqlite] Problems with v3.9.0: entry point sqlite3_finalize could not be located

[jose i cabrera]

Thanks.  Works great!  Thanks.




From: Richard Hipp
Sent: Wednesday, October 14, 2015 5:37 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Problems with v3.9.0: entry point sqlite3_finalize could 
not be located


On 10/14/15, jose isaias cabrera  wrote:
> -Original Message-
> From: Random Coder
> Sent: Wednesday, October 14, 2015 2:17 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Problems with v3.9.0: entry point sqlite3_finalize
> could not be located
>
> On Wed, Oct 14, 2015 at 9:55 AM, jose isaias cabrera
>  wrote:
>> "The procedure entry point sqlite3_finalize could not be located in the
>> dynamic link library SQLITE3.DLL."
>>
>> and the program aborts.  I had to go back to v3.8.11.1 to make the
>> program
>> work again.  Any thoughts?  I thought that this had happened before and
>> it
>> was some compiler issue.  Thoughts?
>
> There appears to be a problem with the officially provided DLL.  It
> only has sqlite3_json_init exported in the DLL.
> ___
>
> When can we expect a new DLL?

It's on the website now.

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

[sqlite] sqlite window functions extension?

[Igor Tandetnik]

On 10/14/2015 5:20 PM, Don V Nielsen wrote:
> X has columns zip & crrt, just like crrt_net_non.  These form a composite
> key identifying groups within x.  A value "53001.R501" would be an
> example...53001 being the zip code and R501 being the carrier route.  There
> are 52 rows in X that have the key 53001.R501.  A calculation determined
> that I need 42 rows from that key and saved the result in crrt_net_non, the
> row looking like "53001.R501.52.6.46.42".  What I need is a sql function
> that can iterate over crrt_net_non, then grab the rows from X, "53001.R501"
> being first key, sort them into an internal group sequence, then update a
> code of the first 42 rows of that sorted group, and then doing this until
> crrt_net_non is exhausted.

Are you looking for something like this?

UPDATE X set code=whatever where rowid in (
   select x2.rowid from X x2 where X.zip=x2.zip and X.crrt=x2.crrt
   order by someOrder
   limit ifnull( (
  select net_non_pieces from crrt_net_non net
  where X.zip=net.zip and X.crrt=net.crrt
   ), 0)
);

-- 
Igor Tandetnik

[sqlite] Problems with v3.9.0: entry point sqlite3_finalize could not be located

[Richard Hipp]

On 10/14/15, jose isaias cabrera  wrote:
> -Original Message-
> From: Random Coder
> Sent: Wednesday, October 14, 2015 2:17 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Problems with v3.9.0: entry point sqlite3_finalize
> could not be located
>
> On Wed, Oct 14, 2015 at 9:55 AM, jose isaias cabrera
>  wrote:
>> "The procedure entry point sqlite3_finalize could not be located in the
>> dynamic link library SQLITE3.DLL."
>>
>> and the program aborts.  I had to go back to v3.8.11.1 to make the
>> program
>> work again.  Any thoughts?  I thought that this had happened before and
>> it
>> was some compiler issue.  Thoughts?
>
> There appears to be a problem with the officially provided DLL.  It
> only has sqlite3_json_init exported in the DLL.
> ___
>
> When can we expect a new DLL?

It's on the website now.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Problems with v3.9.0: entry point sqlite3_finalize could not be located

[jose isaias cabrera]

-Original Message- 
From: Random Coder
Sent: Wednesday, October 14, 2015 2:17 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Problems with v3.9.0: entry point sqlite3_finalize 
could not be located

On Wed, Oct 14, 2015 at 9:55 AM, jose isaias cabrera
 wrote:
> "The procedure entry point sqlite3_finalize could not be located in the
> dynamic link library SQLITE3.DLL."
>
> and the program aborts.  I had to go back to v3.8.11.1 to make the program
> work again.  Any thoughts?  I thought that this had happened before and it
> was some compiler issue.  Thoughts?

There appears to be a problem with the officially provided DLL.  It
only has sqlite3_json_init exported in the DLL.
___

When can we expect a new DLL?  Or is there going to be one?  Or are we going 
to have to download a compiler and prepare our own DLL?  Thanks.

[sqlite] sqlite window functions extension?

[Don V Nielsen]

Table crrt_net_non_pieces:

zip crrt   pkg_pieces   sel_pieces   non_pieces   net_non_pieces
53001   R501   52   646   42
53001   R502   34   727   24


Addresses (as I have been referring to as X) is going to have too many rows
(86 for this example, 52 rows for 53001.R501 and 34 rows for 53001.R502.
The calculation result says I need 42 of the 52 X rows from 53001.R501 to
be recoded and 24 of the 34 X rows from 53001.R502.

Addresses table:

zip crrt   segment   version_id
53001   R501   060060
53001   R501   060060
53001   R501   060060
53001   R501   060060
53001   R501   060060

In sql server, I would be doing something like this:

  select x.* ,'xx' as segment from ( select -- count(1) a.zip ,a.crrt
,a.version_id ,ROW_NUMBER() OVER ( PARTITION BY a.zip,a.crrt ORDER BY CASE WHEN
a.version_id = '0060' THEN 0 WHEN a.version_id = '0064' THEN 2 WHEN
a.version_id = '0061' THEN 3 ELSE 99 END ASC ) AS NUM from
dbwork..addresses a )x join dbwork..crrt_net_non y on y.zip = x.zip and
y.crrt = x.crrt where x.num <= y.net_non_pieces ;
And the results would come out something like the following:  I truncate
the rows with num values 1..whatever.  Notice NUM is the sequential value
applied to the input (1..52 in the case of R501 and 1..34 in the case of
R502).  I then only kept the number of rows as identified by the
corresponding y.net_non_pieces value.

zip crrt   version_id   NUM   segment
[truncated num 1..38]
53001   R501   0060 39xx
53001   R501   0060 40xx
53001   R501   0060 41xx
53001   R501   0060 42xx
[truncated num 1..24]
53001   R502   0060 21xx
53001   R502   0060 22xx
53001   R502   0060 23xx
53001   R502   0060 24xx


Is this better?  Sorry that I imagined/assumed that you guys just picture
this stuff in your head.  I get that impression reading this list.

dvn


These window functions seem to be some sort of Holy Grail that I just can't
imagine duplicating without it.


On Wed, Oct 14, 2015 at 4:27 PM, R.Smith  wrote:

>
>
> On 2015-10-14 11:20 PM, Don V Nielsen wrote:
>
>> X has columns zip & crrt, just like crrt_net_non.  These form a composite
>> key identifying groups within x.  A value "53001.R501" would be an
>> example...53001 being the zip code and R501 being the carrier route.
>> There
>> are 52 rows in X that have the key 53001.R501.  A calculation determined
>> that I need 42 rows from that key and saved the result in crrt_net_non,
>> the
>> row looking like "53001.R501.52.6.46.42".  What I need is a sql function
>> that can iterate over crrt_net_non, then grab the rows from X,
>> "53001.R501"
>> being first key, sort them into an internal group sequence, then update a
>> code of the first 42 rows of that sorted group, and then doing this until
>> crrt_net_non is exhausted.
>>
>
> Hi Don, usually a CTE can be pressed into service to make things work.
> Maybe others know exactly what you mean here but it's a bit Greek to me.
>
> As Igor suggested - perhaps some data to accompany this schema and an
> example result-set (or resulting updated table) from it will make it clear
> what you want to achieve with the query. Feel free to simplify so the
> essence of what is needed remain, but be sure to give an example that
> cannot be achieved by other arbitrary means.
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] sqlite window functions extension?

[Igor Tandetnik]

On 10/14/2015 4:49 PM, Don V Nielsen wrote:
> What am I looking to do?  Using a table X, I've built a summary table,
> calculating a value called net_non_pieces.  net_not_pieces is a qty of rows
> from a group rows that needs to be recoded to a different value.  So what I
> want to do is for each group in X, look up the calculated value in
> crrt_net_non, then grab the first net_non_pieces (sequenced, of course) and
> apply a code to those rows, leaving the remaining rows alone.

It might help if you show the definition of X, a sample of data in it, 
and the desired result of the query over that data. I, for one, have 
difficulty following your description.
-- 
Igor Tandetnik

[sqlite] sqlite window functions extension?

[Don V Nielsen]

X has columns zip & crrt, just like crrt_net_non.  These form a composite
key identifying groups within x.  A value "53001.R501" would be an
example...53001 being the zip code and R501 being the carrier route.  There
are 52 rows in X that have the key 53001.R501.  A calculation determined
that I need 42 rows from that key and saved the result in crrt_net_non, the
row looking like "53001.R501.52.6.46.42".  What I need is a sql function
that can iterate over crrt_net_non, then grab the rows from X, "53001.R501"
being first key, sort them into an internal group sequence, then update a
code of the first 42 rows of that sorted group, and then doing this until
crrt_net_non is exhausted.

On Wed, Oct 14, 2015 at 4:03 PM, Igor Tandetnik  wrote:

> On 10/14/2015 4:49 PM, Don V Nielsen wrote:
>
>> What am I looking to do?  Using a table X, I've built a summary table,
>> calculating a value called net_non_pieces.  net_not_pieces is a qty of
>> rows
>> from a group rows that needs to be recoded to a different value.  So what
>> I
>> want to do is for each group in X, look up the calculated value in
>> crrt_net_non, then grab the first net_non_pieces (sequenced, of course)
>> and
>> apply a code to those rows, leaving the remaining rows alone.
>>
>
> It might help if you show the definition of X, a sample of data in it, and
> the desired result of the query over that data. I, for one, have difficulty
> following your description.
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] sqlite window functions extension?

[Don V Nielsen]

Is there an extension to sqlite the provide window functions such as
row_number over and partition?  There are plenty of googles seeking said
functionality.

What am I looking to do?  Using a table X, I've built a summary table,
calculating a value called net_non_pieces.  net_not_pieces is a qty of rows
from a group rows that needs to be recoded to a different value.  So what I
want to do is for each group in X, look up the calculated value in
crrt_net_non, then grab the first net_non_pieces (sequenced, of course) and
apply a code to those rows, leaving the remaining rows alone.

I'm not sure how to accomplish such a thing

CREATE TABLE crrt_net_non -- developed from table X
(
zip TEXT,   --composite key
crrt TEXT,  --composite key
pkg_pieces integer,  --total row in group
sel_pieces integer,
non_pieces integer,
net_non_pieces integer  --qty of this group to recode
);

Its a pretty innocent thing to do with window functions.  It is not trivial
to do it without.  I can't even figure out how.

Why am I not dumping Sqlite and going with prostgres or the like?  Because
sqlite is awesome.  It is a portable file, easily created as I need for
each project, that can be moved around or deleted, making it super easy to
maintenance in my production environment.  Love the thing.  But this
problem has me flummuxed.

Thanks for your time and consideration,
dvn

[sqlite] SQLite Release 3.9.0

[Craig Maynard]

I'm not sure what semantic versioning says about this, but why not just drop 
the trailing .0 and call the release 3.9? That seems cleaner and emphasizes the 
fact that this is a new minor version.

--
Craig Maynard
Los Altos, California

[sqlite] Problems with v3.9.0: entry point sqlite3_finalize could not be located

[Richard Hipp]

On 10/14/15, Random Coder  wrote:
>
> There appears to be a problem with the officially provided DLL.  It
> only has sqlite3_json_init exported in the DLL.
>

Confirmed.  And yet the reason for that is still a complete mystery to
us SQLite developers.  We are trying to figure out why the DLL build
is not working now

Meanwhile, use the source code.  Statically link.  Your life will be
much easier that way.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Problems with v3.9.0: entry point sqlite3_finalize couldnot be located

[jose isaias cabrera]

Someone is using the mailing list posts to send SPAM to the posters.  Just 
fyi.  I just received an email from someone name Alexa including pictures. 
The email it came from was

alexa at nsadatemail.com

Just an fyi.  Thanks.


-Original Message- 
From: jose isaias cabrera
Sent: Wednesday, October 14, 2015 12:55 PM
To: sqlite-users at mailinglists.sqlite.org
Subject: [sqlite] Problems with v3.9.0: entry point sqlite3_finalize 
couldnot be located


Greetings.

I just downloaded the provided Windows DLL version from the download site,
v3.9.0, and when I run my program, I get

"The procedure entry point sqlite3_finalize could not be located in the
dynamic link library SQLITE3.DLL."

and the program aborts.  I had to go back to v3.8.11.1 to make the program
work again.  Any thoughts?  I thought that this had happened before and it
was some compiler issue.  Thoughts?

thanks.

jos?

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

[sqlite] Problems with v3.9.0: entry point sqlite3_finalize could not be located

[jose isaias cabrera]

Greetings.

I just downloaded the provided Windows DLL version from the download site, 
v3.9.0, and when I run my program, I get

"The procedure entry point sqlite3_finalize could not be located in the 
dynamic link library SQLITE3.DLL."

and the program aborts.  I had to go back to v3.8.11.1 to make the program 
work again.  Any thoughts?  I thought that this had happened before and it 
was some compiler issue.  Thoughts?

thanks.

jos? 

[sqlite] Copy, drop, create tables

[Héctor Fiandor]

I am using a SQLite tables and Lazarus with the information of the current
year.

I december 31, after finish all the current operations, we have to copy the
information in others folders for savings, and empty the former ones for the
new year.

I have tried some routines (copy, drop, create, etc) and finally, it works,

BUT

I need to do this work in differents phases, closing the application, and
starting again in the new phase, up to the last.

My question is: Exists a way to do this in only one phase?

Thanks in advance,

yours truly,



Ing. H?ctor Fiandor

hfiandor at ceniai.inf.cu

[sqlite] Problems with v3.9.0: entry point sqlite3_finalize could not be located

[Random Coder]

On Wed, Oct 14, 2015 at 9:55 AM, jose isaias cabrera
 wrote:
> "The procedure entry point sqlite3_finalize could not be located in the
> dynamic link library SQLITE3.DLL."
>
> and the program aborts.  I had to go back to v3.8.11.1 to make the program
> work again.  Any thoughts?  I thought that this had happened before and it
> was some compiler issue.  Thoughts?

There appears to be a problem with the officially provided DLL.  It
only has sqlite3_json_init exported in the DLL.

[sqlite] SQLITE_CANTOPEN after days of operation

[Andrew Miles]

Sorry didn't see previous replies before sending.  I'll try next:

1) When the error occurs to create a new file in the directory
2) Check lsof of main and monitoring process to see if either is leaking
files

Get back to you in a few days Thanks

Andy

On 14 October 2015 at 08:52, Andrew Miles  wrote:

> Fully opening the directory failed to fix the issue.  So in summary the
> program works for days then dies with this in the log:
>
> (14) cannot open file at line 28488 of [f5b5a13f73]
> (14) os_unix.c:28488: (24) open(/usr/share/cm160) -
> (14) cannot open file at line 28488 of [f5b5a13f73]
> (14) os_unix.c:28488: (24) open(/usr/share/cm160) -
> (14) cannot open file at line 30285 of [f5b5a13f73]
> (14) os_unix.c:30285: (24) open(/usr/share/cm160/eagleowl_stat.db-journal)
> -
> (14) statement aborts at 29: [UPDATE energy_hour_stat SET status = 1 WHERE
> record_count = 60]
>
> Any advice on what could be causing this?
>
> Andy
>
> On 11 October 2015 at 20:56, Andrew Miles  wrote:
>
>> Thanks
>>
>> Log showed it unable to open the directory and then unable to write the
>> journal file.  The directory is root writable and the process is run as
>> root so I didn't expect a problem here.  I've now modified the directory
>> access to be writable by all to see if that changes things.
>>
>> Andy
>>
>> On 5 October 2015 at 11:39, Richard Hipp  wrote:
>>
>>> On 10/5/15, Andrew Miles  wrote:
>>> > Hi
>>> >
>>> > I have a strange issue with a piece of C code using sqlite3.  The code
>>> runs
>>> > for days and then stops on an SQL insert into db with the error code
>>> > SQLITE_CANTOPEN.
>>> >
>>> > Further info:
>>> >
>>> > 1) The program is a status monitoring app - it writes values into the
>>> SQL
>>> > db once every 60 seconds.  Average duration is about 2 days before
>>> dying so
>>> > roughly 3000 successful writes before failure.
>>> > 2) The SQL db is kept open for the entire time i.e. I don't open/close
>>> on
>>> > every transaction.
>>> > 3) There is another process accessing the same db but read only
>>> > 4) I tried waiting 5 secs on the first CANTOPEN and trying again in
>>> case
>>> > the other process had locked the db but same message.  Even closing
>>> the db
>>> > and reopening won't free the error.  The only method is to stop the
>>> program
>>> > and restart, then it works again every time.
>>> > 5) I ran lsof on the db, only one process (this one) had the file open
>>> > 6) The extended error code reported is 14 i.e. no further information
>>> > 7) Disk has 5GB free
>>> >
>>> > Any advice on how to debug further?
>>> >
>>>
>>> Activate the error and warning log.  https://www.sqlite.org/errlog.html
>>>
>>> --
>>> D. Richard Hipp
>>> drh at sqlite.org
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users at mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
>>
>

[sqlite] SQLITE_CANTOPEN after days of operation

[Andrew Miles]

Fully opening the directory failed to fix the issue.  So in summary the
program works for days then dies with this in the log:

(14) cannot open file at line 28488 of [f5b5a13f73]
(14) os_unix.c:28488: (24) open(/usr/share/cm160) -
(14) cannot open file at line 28488 of [f5b5a13f73]
(14) os_unix.c:28488: (24) open(/usr/share/cm160) -
(14) cannot open file at line 30285 of [f5b5a13f73]
(14) os_unix.c:30285: (24) open(/usr/share/cm160/eagleowl_stat.db-journal)
-
(14) statement aborts at 29: [UPDATE energy_hour_stat SET status = 1 WHERE
record_count = 60]

Any advice on what could be causing this?

Andy

On 11 October 2015 at 20:56, Andrew Miles  wrote:

> Thanks
>
> Log showed it unable to open the directory and then unable to write the
> journal file.  The directory is root writable and the process is run as
> root so I didn't expect a problem here.  I've now modified the directory
> access to be writable by all to see if that changes things.
>
> Andy
>
> On 5 October 2015 at 11:39, Richard Hipp  wrote:
>
>> On 10/5/15, Andrew Miles  wrote:
>> > Hi
>> >
>> > I have a strange issue with a piece of C code using sqlite3.  The code
>> runs
>> > for days and then stops on an SQL insert into db with the error code
>> > SQLITE_CANTOPEN.
>> >
>> > Further info:
>> >
>> > 1) The program is a status monitoring app - it writes values into the
>> SQL
>> > db once every 60 seconds.  Average duration is about 2 days before
>> dying so
>> > roughly 3000 successful writes before failure.
>> > 2) The SQL db is kept open for the entire time i.e. I don't open/close
>> on
>> > every transaction.
>> > 3) There is another process accessing the same db but read only
>> > 4) I tried waiting 5 secs on the first CANTOPEN and trying again in case
>> > the other process had locked the db but same message.  Even closing the
>> db
>> > and reopening won't free the error.  The only method is to stop the
>> program
>> > and restart, then it works again every time.
>> > 5) I ran lsof on the db, only one process (this one) had the file open
>> > 6) The extended error code reported is 14 i.e. no further information
>> > 7) Disk has 5GB free
>> >
>> > Any advice on how to debug further?
>> >
>>
>> Activate the error and warning log.  https://www.sqlite.org/errlog.html
>>
>> --
>> D. Richard Hipp
>> drh at sqlite.org
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>

[sqlite] SQLITE_CANTOPEN after days of operation

[Scott Hess]

errno 24 is EMFILE "Too many open files".  You almost certainly have a
file-descriptor leak.

-scott


On Wed, Oct 14, 2015 at 12:52 AM, Andrew Miles  wrote:

> Fully opening the directory failed to fix the issue.  So in summary the
> program works for days then dies with this in the log:
>
> (14) cannot open file at line 28488 of [f5b5a13f73]
> (14) os_unix.c:28488: (24) open(/usr/share/cm160) -
> (14) cannot open file at line 28488 of [f5b5a13f73]
> (14) os_unix.c:28488: (24) open(/usr/share/cm160) -
> (14) cannot open file at line 30285 of [f5b5a13f73]
> (14) os_unix.c:30285: (24) open(/usr/share/cm160/eagleowl_stat.db-journal)
> -
> (14) statement aborts at 29: [UPDATE energy_hour_stat SET status = 1 WHERE
> record_count = 60]
>
> Any advice on what could be causing this?
>
> Andy
>
> On 11 October 2015 at 20:56, Andrew Miles  wrote:
>
> > Thanks
> >
> > Log showed it unable to open the directory and then unable to write the
> > journal file.  The directory is root writable and the process is run as
> > root so I didn't expect a problem here.  I've now modified the directory
> > access to be writable by all to see if that changes things.
> >
> > Andy
> >
> > On 5 October 2015 at 11:39, Richard Hipp  wrote:
> >
> >> On 10/5/15, Andrew Miles  wrote:
> >> > Hi
> >> >
> >> > I have a strange issue with a piece of C code using sqlite3.  The code
> >> runs
> >> > for days and then stops on an SQL insert into db with the error code
> >> > SQLITE_CANTOPEN.
> >> >
> >> > Further info:
> >> >
> >> > 1) The program is a status monitoring app - it writes values into the
> >> SQL
> >> > db once every 60 seconds.  Average duration is about 2 days before
> >> dying so
> >> > roughly 3000 successful writes before failure.
> >> > 2) The SQL db is kept open for the entire time i.e. I don't open/close
> >> on
> >> > every transaction.
> >> > 3) There is another process accessing the same db but read only
> >> > 4) I tried waiting 5 secs on the first CANTOPEN and trying again in
> case
> >> > the other process had locked the db but same message.  Even closing
> the
> >> db
> >> > and reopening won't free the error.  The only method is to stop the
> >> program
> >> > and restart, then it works again every time.
> >> > 5) I ran lsof on the db, only one process (this one) had the file open
> >> > 6) The extended error code reported is 14 i.e. no further information
> >> > 7) Disk has 5GB free
> >> >
> >> > Any advice on how to debug further?
> >> >
> >>
> >> Activate the error and warning log.  https://www.sqlite.org/errlog.html
> >>
> >> --
> >> D. Richard Hipp
> >> drh at sqlite.org
> >> ___
> >> 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
>