Error in recover sqlite3 database

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Error in recover sqlite3 database

bhandari_nikhil
My sqlite3 database keeps on increasing in memory (although it is limited by
memory). If I try to rebuild the database, it is giving the following error:

sqlite> INSERT INTO tbllog(tbllog) VALUES('rebuild');                                            

Error: database or disk is full

Is the solution only to remove the db file ? Or we can do something about it
? BTW, even after a reboot of the device, we land up in the same situation
after some time. Is there some problem with the journal ?



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Error in recover sqlite3 database

Hick Gunter
What is this tbllog table?

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von bhandari_nikhil
Gesendet: Donnerstag, 01. August 2019 07:39
An: [hidden email]
Betreff: [EXTERNAL] [sqlite] Error in recover sqlite3 database

My sqlite3 database keeps on increasing in memory (although it is limited by memory). If I try to rebuild the database, it is giving the following error:

sqlite> INSERT INTO tbllog(tbllog) VALUES('rebuild');

Error: database or disk is full

Is the solution only to remove the db file ? Or we can do something about it ? BTW, even after a reboot of the device, we land up in the same situation after some time. Is there some problem with the journal ?



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Error in recover sqlite3 database

bhandari_nikhil
This is my table, I just wanted to show the error that it is throwing. Is the
error due to /tmp being full on my device ? Further debugging at our end
showed that one way to replicate the problem is to remove the hot journal
file in the middle of an insert transaction but we are not sure if that is
the actual cause of the problem or not.

In the application, we have a logic to check the db file size and if it
grows beyond a certain threshold, we would take a backup of the db file. But
once we do the delete-the journal-in-the middle-of-a-transaction operation,
this size check never passes and the db size keeps on growing. Why could
that be ?



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Error in recover sqlite3 database

Hick Gunter
The error is due to a full disk. You should not be deleting files associated with an SQLite db file.

Have you tried running pragma integrity_check(); before the disk actually becomes full?

Other than corruption of the file, the two candidates are internal fragmentation (doing lots of INSERT and DELETE operations), which would be remedied by running VACUUM (NOTE: may require up to double the current file size); or just the sheer volume of data (doing lots of INSERTS and never DELETEing outdated rows), which you would need to handle in your application.

The fact that "backing up" (how? Export/import or the SQLite backup API?) the db file helps would indicate either fragmentation or corruption.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von bhandari_nikhil
Gesendet: Donnerstag, 01. August 2019 10:48
An: [hidden email]
Betreff: Re: [sqlite] [EXTERNAL] Error in recover sqlite3 database

This is my table, I just wanted to show the error that it is throwing. Is the error due to /tmp being full on my device ? Further debugging at our end showed that one way to replicate the problem is to remove the hot journal file in the middle of an insert transaction but we are not sure if that is the actual cause of the problem or not.

In the application, we have a logic to check the db file size and if it grows beyond a certain threshold, we would take a backup of the db file. But once we do the delete-the journal-in-the middle-of-a-transaction operation, this size check never passes and the db size keeps on growing. Why could that be ?



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Error in recover sqlite3 database

Simon Slavin-3
In reply to this post by bhandari_nikhil
On 1 Aug 2019, at 9:48am, bhandari_nikhil <[hidden email]> wrote:

> In the application, we have a logic to check the db file size and if it grows beyond a certain threshold, we would take a backup of the db file.

Add to this logic

fr = (size of database file + size of journal file) * 3
check to see there is at least fr space free

This ensures you have enough space for the backup to work.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Error in recover sqlite3 database

R Smith-2
On 2019/08/01 3:24 PM, Simon Slavin wrote:
> On 1 Aug 2019, at 9:48am, bhandari_nikhil <[hidden email]> wrote:
>
>> In the application, we have a logic to check the db file size and if it grows beyond a certain threshold, we would take a backup of the db file.
> Add to this logic
>
> fr = (size of database file + size of journal file) * 3
> check to see there is at least fr space free
>
> This ensures you have enough space for the backup to work.


This be good, just want to mention a few more considerations:

1 - While safer to err on the side of caution, the calculation in my
testing is sufficient at:   needed_space = (db_size + journal_size) * 2.2,

2 - If, and only if, the SQLite DB in question is the ONLY thing
utilizing the said disk, else budget higher, and

3 - If the DB sits on an external or other drive than your TEMP path is
on, you really need the extra space in the TEMP path drive,

4 - Except when you have set another custom temp file usage directive
via compiler/pragma settings.


I hope I did not miss something still.


Best of luck!
Ryan



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

Re: [EXTERNAL] Error in recover sqlite3 database

bhandari_nikhil
In reply to this post by Hick Gunter
integrity_check returned ok. The backup is taken using the .dump to a new db.
The problem is that before I manually deleted the journal during a
transaction, the size check and backup was working fine. But after I deleted
that (to deliberately cause the problem), all the size check and backup goes
for a toss. Also, the db no longer allows any transactions after that. I
thought that the journal is only for a transaction but apparently, after my
mischief, the whole db goes for a toss. Not able to explain it.

Meanwhile, I will implement the size suggestions given here.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users