Quantcast

Continuous recovery of journal

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
24 messages Options
12
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Continuous recovery of journal

J Decker
I get this ... from sqlite error log callback

Sqlite3 Err: (283) recovered 6942 frames from WAL file
C:\eQube-Tools\flashboard\server\option.db-wal

pretty much every time I restart the program now (espcially if it
segfaults).
There are no frames to recover... really... I mean it should have recovered
them any of the previous 10 times, no?

I'm pretty sure that I'm not even doing a modification now; previously I
was possibly updating a table's value to the same value.


If my program segfaults I don't get a chance to graceful close all
outstanding statements and connections; so the -wal and -shm file's are
never deleted.

I can understand keeping them open; but when they're done; can't you at
least; I dunno truncate them?  in my VFS that's not a very expensive
operation... other than of course re-expanding when you need the space; but
at least you're not doing a directory search to get a handle to it.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Continuous recovery of journal

Simon Slavin-3

On 1 Apr 2017, at 5:04am, J Decker <[hidden email]> wrote:

> Sqlite3 Err: (283) recovered 6942 frames from WAL file
> C:\eQube-Tools\flashboard\server\option.db-wal
>
> pretty much every time I restart the program now (espcially if it
> segfaults).
> There are no frames to recover... really... I mean it should have recovered
> them any of the previous 10 times, no?

If the database is corrupt then it’s possible that the journal file will get corrupted every time the database is closed.  Use the SQLite shell tool to run

PRAGMA integrity_check;

on that database.  Tell us whether it reports problems.

Whether or not corruption is reported, quit the shell tool then run flashboard again and see whether it reports the same problem with the file.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Continuous recovery of journal

Richard Hipp-3
In reply to this post by J Decker
On 4/1/17, J Decker <[hidden email]> wrote:
> I get this ... from sqlite error log callback
>
> Sqlite3 Err: (283) recovered 6942 frames from WAL file
> C:\eQube-Tools\flashboard\server\option.db-wal
>
> pretty much every time I restart the program now (espcially if it
> segfaults).

This is because the previous process to access the database did not
call sqlite3_close() prior to exiting, and so the WAL file was not
cleaned up properly.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Continuous recovery of journal

J Decker
On Sat, Apr 1, 2017 at 2:38 AM, Richard Hipp <[hidden email]> wrote:

> On 4/1/17, J Decker <[hidden email]> wrote:
> > I get this ... from sqlite error log callback
> >
> > Sqlite3 Err: (283) recovered 6942 frames from WAL file
> > C:\eQube-Tools\flashboard\server\option.db-wal
> >
> > pretty much every time I restart the program now (espcially if it
> > segfaults).
>
> This is because the previous process to access the database did not
> call sqlite3_close() prior to exiting, and so the WAL file was not
> cleaned up properly.
>

Right; I understand that; but on the next open, couldn't the wal get
cleaned after recovery?   or something...


> --
> D. Richard Hipp
> [hidden email]
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Continuous recovery of journal

Richard Hipp-3
On 4/1/17, J Decker <[hidden email]> wrote:

> On Sat, Apr 1, 2017 at 2:38 AM, Richard Hipp <[hidden email]> wrote:
>
>> On 4/1/17, J Decker <[hidden email]> wrote:
>> > I get this ... from sqlite error log callback
>> >
>> > Sqlite3 Err: (283) recovered 6942 frames from WAL file
>> > C:\eQube-Tools\flashboard\server\option.db-wal
>> >
>> > pretty much every time I restart the program now (espcially if it
>> > segfaults).
>>
>> This is because the previous process to access the database did not
>> call sqlite3_close() prior to exiting, and so the WAL file was not
>> cleaned up properly.
>>
>
> Right; I understand that; but on the next open, couldn't the wal get
> cleaned after recovery?   or something...
>

"Cleanup" and "recovery" are the same thing.  That is exactly what it
happening, and that is what is generating the warning in the log.
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Continuous recovery of journal

J Decker
In reply to this post by Simon Slavin-3
On Fri, Mar 31, 2017 at 11:32 PM, Simon Slavin <[hidden email]> wrote:

>
> On 1 Apr 2017, at 5:04am, J Decker <[hidden email]> wrote:
>
> > Sqlite3 Err: (283) recovered 6942 frames from WAL file
> > C:\eQube-Tools\flashboard\server\option.db-wal
> >
> > pretty much every time I restart the program now (espcially if it
> > segfaults).
> > There are no frames to recover... really... I mean it should have
> recovered
> > them any of the previous 10 times, no?
>
> If the database is corrupt then it’s possible that the journal file will
> get corrupted every time the database is closed.  Use the SQLite shell tool
> to run
>
> PRAGMA integrity_check;
>
>
C:\eQube-Tools\flashboard\server>tools\sqlite3.exe option.db
SQLite version 3.18.0 2017-03-28 18:48:43
Enter ".help" for usage hints.
sqlite> pragma integrity_check
   ...> ;
ok
sqlite>

current execution started yesterday; I ended up clearing the older logs...
18:10:58.868|7D6000012684~Launching user process...
18:11:01.183|7D600000E478~18:11:01.182|031C00009270~Sqlite3 Err: (283)
recovered 6942 frames from WAL file
C:\eQube-Tools\flashboard\server\option.db-wal

there's only 3 tables in use
select count(*) from option4_map union select count(*) from option4_name
union select count(*) from option4_values;
50
66
74

---
On a different process....

09:47:26.720~Sqlite3 (283) recovered 2 frames from WAL file
M:\eqube\Vegas\trunk\javascript\eqube\flashboard\server\option.db-wal
09:47:29.893~Sqlite3 Err: (283) recovered 4 frames from WAL file
M:\eqube\Vegas\trunk\javascript\eqube\flashboard\server\option.db-wal
09:47:34.251~Sqlite3 Err: (283) recovered 6 frames from WAL file
M:\eqube\Vegas\trunk\javascript\eqube\flashboard\server\option.db-wal
09:47:38.201~Sqlite3 Err: (283) recovered 8 frames from WAL file
M:\eqube\Vegas\trunk\javascript\eqube\flashboard\server\option.db-wal
09:47:41.615~Sqlite3 Err: (283) recovered 10 frames from WAL file
M:\eqube\Vegas\trunk\javascript\eqube\flashboard\server\option.db-wal

integrity is OK.

The update statements are these...

09:47:41.617~Do Command[00E8CC9C:./option.db]: BEGIN TRANSACTION
09:47:41.617~Do Command[00E8CC9C:./option.db]: replace into option4_values
(`option_id`,`string`,`segment` ) values
('c9e60551-fd35-11e6-a38c-b0c09031be57','172.31.0.200',0)
09:47:41.618~Do Command[00E8CC9C:./option.db]: delete from option4_values
where `option_id`='c9e60551-fd35-11e6-a38c-b0c09031be57' and segment > 0
09:47:41.618~Do Command[00E8CC9C:./option.db]: COMMIT

(2 frames per run?)  There are never segments greater than 0 to delete; I
just don't know if I'm replacing a very very long option with a shorter
one; and the code is MySQL compatible that doesn't have inifite varchar
fields so options poentially have multiple 256 character segments....

So really - after frames are recovered, can't they be cleared?
I could understand if it was always 2 frames.... but 2, 4, 6, 8, 10 on
subsequent runs (with terminate process inbetween) seems wrong somehow.

It's more like 'NOT recovering 2 frames.'



> on that database.  Tell us whether it reports problems.
>
> Whether or not corruption is reported, quit the shell tool then run
> flashboard again and see whether it reports the same problem with the file.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Continuous recovery of journal

J Decker
On Sat, Apr 1, 2017 at 9:54 AM, J Decker <[hidden email]> wrote:

>
> On Fri, Mar 31, 2017 at 11:32 PM, Simon Slavin <[hidden email]>
> wrote:
>
>>
>> On 1 Apr 2017, at 5:04am, J Decker <[hidden email]> wrote:
>>
>> > Sqlite3 Err: (283) recovered 6942 frames from WAL file
>> > C:\eQube-Tools\flashboard\server\option.db-wal
>> >
>> > pretty much every time I restart the program now (espcially if it
>> > segfaults).
>> > There are no frames to recover... really... I mean it should have
>> recovered
>> > them any of the previous 10 times, no?
>>
>> If the database is corrupt then it’s possible that the journal file will
>> get corrupted every time the database is closed.  Use the SQLite shell tool
>> to run
>>
>> PRAGMA integrity_check;
>>
>>
> C:\eQube-Tools\flashboard\server>tools\sqlite3.exe option.db
> SQLite version 3.18.0 2017-03-28 18:48:43
> Enter ".help" for usage hints.
> sqlite> pragma integrity_check
>    ...> ;
> ok
> sqlite>
>
> current execution started yesterday; I ended up clearing the older logs...
> 18:10:58.868|7D6000012684~Launching user process...
> 18:11:01.183|7D600000E478~18:11:01.182|031C00009270~Sqlite3 Err: (283)
> recovered 6942 frames from WAL file C:\eQube-Tools\flashboard\
> server\option.db-wal
>
> there's only 3 tables in use
> select count(*) from option4_map union select count(*) from option4_name
> union select count(*) from option4_values;
> 50
> 66
> 74
>
> ---
> On a different process....
>
> 09:47:26.720~Sqlite3 (283) recovered 2 frames from WAL file
> M:\eqube\Vegas\trunk\javascript\eqube\flashboard\server\option.db-wal
> 09:47:29.893~Sqlite3 Err: (283) recovered 4 frames from WAL file
> M:\eqube\Vegas\trunk\javascript\eqube\flashboard\server\option.db-wal
> 09:47:34.251~Sqlite3 Err: (283) recovered 6 frames from WAL file
> M:\eqube\Vegas\trunk\javascript\eqube\flashboard\server\option.db-wal
> 09:47:38.201~Sqlite3 Err: (283) recovered 8 frames from WAL file
> M:\eqube\Vegas\trunk\javascript\eqube\flashboard\server\option.db-wal
> 09:47:41.615~Sqlite3 Err: (283) recovered 10 frames from WAL file
> M:\eqube\Vegas\trunk\javascript\eqube\flashboard\server\option.db-wal
>
> integrity is OK.
>
> The update statements are these...
>
> 09:47:41.617~Do Command[00E8CC9C:./option.db]: BEGIN TRANSACTION
> 09:47:41.617~Do Command[00E8CC9C:./option.db]: replace into option4_values
> (`option_id`,`string`,`segment` ) values ('c9e60551-fd35-11e6-a38c-
> b0c09031be57','172.31.0.200',0)
> 09:47:41.618~Do Command[00E8CC9C:./option.db]: delete from option4_values
> where `option_id`='c9e60551-fd35-11e6-a38c-b0c09031be57' and segment > 0
> 09:47:41.618~Do Command[00E8CC9C:./option.db]: COMMIT
>
> (2 frames per run?)  There are never segments greater than 0 to delete; I
> just don't know if I'm replacing a very very long option with a shorter
> one; and the code is MySQL compatible that doesn't have inifite varchar
> fields so options poentially have multiple 256 character segments....
>
> So really - after frames are recovered, can't they be cleared?
> I could understand if it was always 2 frames.... but 2, 4, 6, 8, 10 on
> subsequent runs (with terminate process inbetween) seems wrong somehow.
>
> It's more like 'NOT recovering 2 frames.'
>
>
Oh maybe it is recovering 2 frames,b ut the act of recovery creates 2
frames again?  so then it has the new 2 from the recovery before the next
one to get 4?



>
>
>> on that database.  Tell us whether it reports problems.
>>
>> Whether or not corruption is reported, quit the shell tool then run
>> flashboard again and see whether it reports the same problem with the file.
>>
>> Simon.
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Continuous recovery of journal

Richard Hipp-3
In reply to this post by J Decker
On 4/1/17, J Decker <[hidden email]> wrote:
>
> So really - after frames are recovered, can't they be cleared?

Is see.  You want a "checkpoint".  That will happen automatically when
the size of the WAL file reaches 1000 frames.  But you can force it to
happen sooner by running "PRAGMA wal_checkpoint".

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Continuous recovery of journal

J Decker
On Sat, Apr 1, 2017 at 9:59 AM, Richard Hipp <[hidden email]> wrote:

> On 4/1/17, J Decker <[hidden email]> wrote:
> >
> > So really - after frames are recovered, can't they be cleared?
>
> Is see.  You want a "checkpoint".  That will happen automatically when
> the size of the WAL file reaches 1000 frames.  But you can force it to
> happen sooner by running "PRAGMA wal_checkpoint".
>
>
the other wall is up to 6000 frames (I think I had an error in the script
for a bit and the process kept restarting very very quickly but... it was
probably just 2 frames per time to get to 6000 )


> --
> D. Richard Hipp
> [hidden email]
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Continuous recovery of journal

J Decker
On Sat, Apr 1, 2017 at 10:04 AM, J Decker <[hidden email]> wrote:

>
>
> On Sat, Apr 1, 2017 at 9:59 AM, Richard Hipp <[hidden email]> wrote:
>
>> On 4/1/17, J Decker <[hidden email]> wrote:
>> >
>> > So really - after frames are recovered, can't they be cleared?
>>
>> Is see.  You want a "checkpoint".  That will happen automatically when
>> the size of the WAL file reaches 1000 frames.  But you can force it to
>> happen sooner by running "PRAGMA wal_checkpoint".
>>
>>
> the other wall is up to 6000 frames (I think I had an error in the script
> for a bit and the process kept restarting very very quickly but... it was
> probably just 2 frames per time to get to 6000 )
>

I added a wal_checkpoint as part of connection, so now the wal stays at 2
frames... recovered into WAL state I guess? not actually recovered... it
was commited.

>
>
>> --
>> D. Richard Hipp
>> [hidden email]
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Continuous recovery of journal

J Decker
In reply to this post by J Decker
On Sat, Apr 1, 2017 at 9:54 AM, J Decker <[hidden email]> wrote:

>
> On Fri, Mar 31, 2017 at 11:32 PM, Simon Slavin <[hidden email]>
> wrote:
>
>>
>> On 1 Apr 2017, at 5:04am, J Decker <[hidden email]> wrote:
>>
>> > Sqlite3 Err: (283) recovered 6942 frames from WAL file
>> > C:\eQube-Tools\flashboard\server\option.db-wal
>> >
>> > pretty much every time I restart the program now (espcially if it
>> > segfaults).
>> > There are no frames to recover... really... I mean it should have
>> recovered
>> > them any of the previous 10 times, no?
>>
>> If the database is corrupt then it’s possible that the journal file will
>> get corrupted every time the database is closed.  Use the SQLite shell tool
>> to run
>>
>> PRAGMA integrity_check;
>>
>>
> C:\eQube-Tools\flashboard\server>tools\sqlite3.exe option.db
> SQLite version 3.18.0 2017-03-28 18:48:43
> Enter ".help" for usage hints.
> sqlite> pragma integrity_check
>    ...> ;
> ok
> sqlite>
>
> current execution started yesterday; I ended up clearing the older logs...
> 18:10:58.868|7D6000012684~Launching user process...
> 18:11:01.183|7D600000E478~18:11:01.182|031C00009270~Sqlite3 Err: (283)
> recovered 6942 frames from WAL file C:\eQube-Tools\flashboard\
> server\option.db-wal
>
> there's only 3 tables in use
> select count(*) from option4_map union select count(*) from option4_name
> union select count(*) from option4_values;
> 50
> 66
> 74
>
> ---
> On a different process....
>
> 09:47:26.720~Sqlite3 (283) recovered 2 frames from WAL file
> M:\eqube\Vegas\trunk\javascript\eqube\flashboard\server\option.db-wal
> 09:47:29.893~Sqlite3 Err: (283) recovered 4 frames from WAL file
> M:\eqube\Vegas\trunk\javascript\eqube\flashboard\server\option.db-wal
> 09:47:34.251~Sqlite3 Err: (283) recovered 6 frames from WAL file
> M:\eqube\Vegas\trunk\javascript\eqube\flashboard\server\option.db-wal
> 09:47:38.201~Sqlite3 Err: (283) recovered 8 frames from WAL file
> M:\eqube\Vegas\trunk\javascript\eqube\flashboard\server\option.db-wal
> 09:47:41.615~Sqlite3 Err: (283) recovered 10 frames from WAL file
> M:\eqube\Vegas\trunk\javascript\eqube\flashboard\server\option.db-wal
>
> integrity is OK.
>
> just a final note...

It finally hit its crash point and restarted....

11:02:30.850|~Sqlite3 Err: (283) recovered 6942 frames from WAL file
C:\eQube-Tools\flashboard\server\option.db-wal

I changed the setting from 'set option' to 'default option' so it doesn't
re-write if it's already there; so I didn't get 2 new frames, just same
6942 frames....

I just wouldn't expect a journal to continuously recover itself when
there's really nothing to do anyway?  I guess maybe I don't expect
'recovered' to me 'reloaded' or 'continuing with existing'


> The update statements are these...
>
> 09:47:41.617~Do Command[00E8CC9C:./option.db]: BEGIN TRANSACTION
> 09:47:41.617~Do Command[00E8CC9C:./option.db]: replace into option4_values
> (`option_id`,`string`,`segment` ) values ('c9e60551-fd35-11e6-a38c-
> b0c09031be57','172.31.0.200',0)
> 09:47:41.618~Do Command[00E8CC9C:./option.db]: delete from option4_values
> where `option_id`='c9e60551-fd35-11e6-a38c-b0c09031be57' and segment > 0
> 09:47:41.618~Do Command[00E8CC9C:./option.db]: COMMIT
>
> (2 frames per run?)  There are never segments greater than 0 to delete; I
> just don't know if I'm replacing a very very long option with a shorter
> one; and the code is MySQL compatible that doesn't have inifite varchar
> fields so options poentially have multiple 256 character segments....
>
> So really - after frames are recovered, can't they be cleared?
> I could understand if it was always 2 frames.... but 2, 4, 6, 8, 10 on
> subsequent runs (with terminate process inbetween) seems wrong somehow.
>
> It's more like 'NOT recovering 2 frames.'
>
>
>
>> on that database.  Tell us whether it reports problems.
>>
>> Whether or not corruption is reported, quit the shell tool then run
>> flashboard again and see whether it reports the same problem with the file.
>>
>> Simon.
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Continuous recovery of journal

Simon Slavin-3

On 1 Apr 2017, at 8:11pm, J Decker <[hidden email]> wrote:

> It finally hit its crash point and restarted....
>
> 11:02:30.850|~Sqlite3 Err: (283) recovered 6942 frames from WAL file
> C:\eQube-Tools\flashboard\server\option.db-wal
>
> I changed the setting from 'set option' to 'default option' so it doesn't
> re-write if it's already there; so I didn't get 2 new frames, just same
> 6942 frames....
>
> I just wouldn't expect a journal to continuously recover itself when
> there's really nothing to do anyway?  I guess maybe I don't expect
> 'recovered' to me 'reloaded' or 'continuing with existing'

You are not seeing what SQLite does.  You are seeing what that application does, not using SQLite correctly.  The application is not closing the database correctly when it quits.  Therefore corruption is not being fixed in the version saved to disk.  So the next time you open the database file, it’s still corrupt.

You have two options.  The preferred one is to recover a backup of the database from before the Flashboard tool started issuing those error messages.  However, if you’re willing to work with possible corruption introduced into the database then ...

1. Take a backup copy of the database in case anything goes wrong.
2. Open the database in the SQLite shell tool.
3. Execute the VACUUM command.
4. Quit the SQLite shell tool using the ".quit" command.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Continuous recovery of journal

J Decker
On Sat, Apr 1, 2017 at 3:53 PM, Simon Slavin <[hidden email]> wrote:

>
> On 1 Apr 2017, at 8:11pm, J Decker <[hidden email]> wrote:
>
> > It finally hit its crash point and restarted....
> >
> > 11:02:30.850|~Sqlite3 Err: (283) recovered 6942 frames from WAL file
> > C:\eQube-Tools\flashboard\server\option.db-wal
> >
> > I changed the setting from 'set option' to 'default option' so it doesn't
> > re-write if it's already there; so I didn't get 2 new frames, just same
> > 6942 frames....
> >
> > I just wouldn't expect a journal to continuously recover itself when
> > there's really nothing to do anyway?  I guess maybe I don't expect
> > 'recovered' to me 'reloaded' or 'continuing with existing'
>
> You are not seeing what SQLite does.  You are seeing what that application
> does, not using SQLite correctly.  The application is not closing the
> database correctly when it quits.  Therefore corruption is not being fixed
> in the version saved to disk.  So the next time you open the database file,
> it’s still corrupt.
>
> You have two options.  The preferred one is to recover a backup of the
> database from before the Flashboard tool started issuing those error
> messages.  However, if you’re willing to work with possible corruption
> introduced into the database then ...
>
> 1. Take a backup copy of the database in case anything goes wrong.
> 2. Open the database in the SQLite shell tool.
> 3. Execute the VACUUM command.
> 4. Quit the SQLite shell tool using the ".quit" command.
>
>
there's no corruption.
> I added a wal_checkpoint as part of connection, so now the wal stays at 2
frames..

Under kill -9 or TerminateProcess (taskmgr, end process) , the application
has no chance to do anything, it's not that the application is doing
something incorrectly.

but the operations happen in the first 120ms of the program and never
later, so if the program runs for more than 1 second sqlite is stable and
has 0 active statements.




> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Continuous recovery of journal

Simon Slavin-3

On 2 Apr 2017, at 2:16am, J Decker <[hidden email]> wrote:

> there's no corruption.

There was corruption.  The message you reported:

> Sqlite3 Err: (283) recovered 6942 frames from WAL file

is exactly a message reporting that the database was corrupt.  This corruption appears, in this case, to be the result of the database not having been closed correctly.

> Under kill -9 or TerminateProcess (taskmgr, end process) , the application
> has no chance to do anything, it's not that the application is doing
> something incorrectly.

Are you routinely using "kill -9" to terminate that application ?  In other words, is that what the application should expect, rather than receiving a "term" message ?

If so, the application should have the SQLite database open only while it’s being accessed.  That’s the best way, though not perfect, to ensure that this problem does not happen again.

> but the operations happen in the first 120ms of the program and never
> later, so if the program runs for more than 1 second sqlite is stable and
> has 0 active statements.

SQLite depends on having its database closed correctly using sqlite3_close().  If that’s not happening, you may experience more problems like the one you reported.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Continuous recovery of journal

Richard Hipp-3
On 4/1/17, Simon Slavin <[hidden email]> wrote:
>
> SQLite depends on having its database closed correctly using
> sqlite3_close().  If that’s not happening, you may experience more problems
> like the one you reported.
>

I think the key point is that the observed behavior is not a problem.
SQLite is working correctly.  The log message has an error code of
SQLITE_WARNING.  It is merely letting you know that the last
application to use the database did not shutdown cleanly, so the
current process is having to spend a few milliseconds to reread the
whole WAL file and reconstruct a corresponding -shm file.

The OP seems really concerned that SQLites does not go ahead and run a
checkpoint after recovering the WAL.  I say that if running a
checkpoint is important to you after a recovery, then have your
application do it.  Running recovery and running a checkpoint are kind
of unrelated things and I don't see why one should imply the other.
But if the OP want a recovery operation to always checkpoint, that is
really easy to implement in the application.
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Continuous recovery of journal

J Decker
In reply to this post by Simon Slavin-3
On Sat, Apr 1, 2017 at 6:42 PM, Simon Slavin <[hidden email]> wrote:

>
> On 2 Apr 2017, at 2:16am, J Decker <[hidden email]> wrote:
>
> > there's no corruption.
>
> There was corruption.  The message you reported:
>
> > Sqlite3 Err: (283) recovered 6942 frames from WAL file
>
> is exactly a message reporting that the database was corrupt.  This
> corruption appears, in this case, to be the result of the database not
> having been closed correctly.
>
> Sorry to argue that; the integrity check was Ok and that's what I was
basing my statement on.


> > Under kill -9 or TerminateProcess (taskmgr, end process) , the
> application
> > has no chance to do anything, it's not that the application is doing
> > something incorrectly.
>
> Are you routinely using "kill -9" to terminate that application ?  In
> other words, is that what the application should expect, rather than
> receiving a "term" message ?
>
>
No I don't expect that; yes in this app because it's ending up with an
abort and signal handlers aren't catching it either... and noticed it
causes an anomaly in sqlite; so I thought I would report it.
I'm glad there is a workaround;  I could even just on getting the 283
message setup to later commit the commited transactions.  (probably
shouldn't do it in the warning message handler?)
Though it does end up doing a little bit of nothing that causes a lot of to
do about nothing progressively even...


> If so, the application should have the SQLite database open only while
> it’s being accessed.  That’s the best way, though not perfect, to ensure
> that this problem does not happen again.
>
> > but the operations happen in the first 120ms of the program and never
> > later, so if the program runs for more than 1 second sqlite is stable and
> > has 0 active statements.
>
> SQLite depends on having its database closed correctly using
> sqlite3_close().  If that’s not happening, you may experience more problems
> like the one you reported.
>
> having this stressed as 'must implement close in order to actually commit
transactions(?)' [I don't know; that's what it is to me] but anyway; I can
add an idle sweep to close connections when nothing has been in progress
for a while.... but only on sqlite connections which complicates things...


> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Continuous recovery of journal

Jeffrey Mattox
>> On Apr 1, 2017, at 10:43 PM, J Decker <[hidden email]> wrote:
>
> I can add an idle sweep to close connections when nothing has been in progress for a while.... but only on sqlite connections which complicates things...

Why don't you do as Simon suggested ("the application should have the SQLite database open only while it’s being accessed.")?   That is, open a connection to the DB when you need to access it and then immediately close the connection?

Jeff

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Continuous recovery of journal

J Decker
On Sat, Apr 1, 2017 at 10:22 PM, Jeffrey Mattox <[hidden email]> wrote:

> >> On Apr 1, 2017, at 10:43 PM, J Decker <[hidden email]> wrote:
> >
> > I can add an idle sweep to close connections when nothing has been in
> progress for a while.... but only on sqlite connections which complicates
> things...
>
> Why don't you do as Simon suggested ("the application should have the
> SQLite database open only while it’s being accessed.")?   That is, open a
> connection to the DB when you need to access it and then immediately close
> the connection?
>
> have you never watched the hoops operating systems go through to open
files?  That's a LOT of work that takes a million inserts a second to a few
10k...
oh - you mean on a higher level?  Oh because my api just allows doing sql
commands without specifying a connection; so typically I don't know when
it's 'done'... the connection is either availalble or it's not... but then
again most normal programs just gracefully exit and don't just cease
functioning.
I actually already had that avaiable, I just needed to enable it.


> Jeff
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Continuous recovery of journal

Ron Barnes
In reply to this post by J Decker
Hi All,

I am new to this mailing list.

Is anyone working with Visual Studio 2015 or belter and using reportviewer?  If yes, what was the procedure?
I'm having the devils of a time trying to generate reports using SQLite.

Would anyone be able to provide a TuT or maybe a link to one tha can help?

Regards,

-Ron

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Continuous recovery of journal

Bob Friesenhahn
In reply to this post by Richard Hipp-3
On Sat, 1 Apr 2017, Richard Hipp wrote:

> On 4/1/17, J Decker <[hidden email]> wrote:
>> I get this ... from sqlite error log callback
>>
>> Sqlite3 Err: (283) recovered 6942 frames from WAL file
>> C:\eQube-Tools\flashboard\server\option.db-wal
>>
>> pretty much every time I restart the program now (espcially if it
>> segfaults).
>
> This is because the previous process to access the database did not
> call sqlite3_close() prior to exiting, and so the WAL file was not
> cleaned up properly.

Is calling sqlite3_close() prior to exiting a requirement in general
for sqlite?  I was not aware of this requirement.

It is not uncommon for programs to quit without explicitly releasing
all resources (e.g. calling sqlite3_close()) when a problem occurs.

I do not see any admonishments about a requirement to call
sqlite3_close() on the documentation page at
https://sqlite.org/c3ref/close.html.  I only see admonishments about
problems which may occur in sqlite3_close() if other requests have not
been completed properly.

Bob
--
Bob Friesenhahn
[hidden email], http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,    http://www.GraphicsMagick.org/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
12
Loading...