Cannot delete a Database file?

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

Cannot delete a Database file?

domonic
After calling sqlite3_close(handle) I try then to use the 'remove' method in C++ to delete my .db file.  For some reason it just won't delete.


It's hard to show you the whole project because it's large.


I believe this has something to do with sqlite3_close only de-allocating the connection to the database.


Would anyone know how to go about making sure that the database file is not busy and can be deleted.  Is there a specific function for this.


There's a few hints round the web mentioning Garbage Collection, and deleting the query but I don't understand how to do either.


Any help would be appreciated.


Thanks
_______________________________________________
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: Cannot delete a Database file?

Bart Smissaert
What is the return value of sqlite3_close?

RBS

On Wed, Jun 28, 2017 at 10:37 AM, Domonic Tom <[hidden email]> wrote:

> After calling sqlite3_close(handle) I try then to use the 'remove' method
> in C++ to delete my .db file.  For some reason it just won't delete.
>
>
> It's hard to show you the whole project because it's large.
>
>
> I believe this has something to do with sqlite3_close only de-allocating
> the connection to the database.
>
>
> Would anyone know how to go about making sure that the database file is
> not busy and can be deleted.  Is there a specific function for this.
>
>
> There's a few hints round the web mentioning Garbage Collection, and
> deleting the query but I don't understand how to do either.
>
>
> Any help would be appreciated.
>
>
> Thanks
> _______________________________________________
> 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: Cannot delete a Database file?

R Smith
In reply to this post by domonic
It ain't SQLite keeping your file open.

Make sure the connection is closed properly (Typically calling the Close
command from your wrapper for SQLite) and then it should be free. Make
sure there is no other program with lingering connections to it, a
different project maybe, or a different instance of your project, or
perhaps a database manager program used alongside your project, of y our
IDE linking to the DB with its own DB inspection facility... or last but
not least, an anti-virus program holding a lock to "check" the file
didn't become a virus after it has been written to.


You can inspect the exact file handles on Windows with some command-line
tools too, but I'm willing to bet it's one of the above situations.

Cheers!
Ryan


On 2017/06/28 11:37 AM, Domonic Tom wrote:

> After calling sqlite3_close(handle) I try then to use the 'remove' method in C++ to delete my .db file.  For some reason it just won't delete.
>
>
> It's hard to show you the whole project because it's large.
>
>
> I believe this has something to do with sqlite3_close only de-allocating the connection to the database.
>
>
> Would anyone know how to go about making sure that the database file is not busy and can be deleted.  Is there a specific function for this.
>
>
> There's a few hints round the web mentioning Garbage Collection, and deleting the query but I don't understand how to do either.
>
>
> Any help would be appreciated.
>
>
> Thanks
> _______________________________________________
> 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: Cannot delete a Database file?

Richard Hipp-3
In reply to this post by domonic
On 6/28/17, Domonic Tom <[hidden email]> wrote:
>
> Would anyone know how to go about making sure that the database file is not
> busy and can be deleted.  Is there a specific function for this.
>

Call sqlite3_finalize() on all prepared statements before calling
sqlite3_close().  Probably you are leaking prepare statements - in
other words there are one or more prepared statements that you have
failed to call sqlite3_finalize() for.

Consider adding the "stmts" virtual table to your project
(https://www.sqlite.org/src/file/ext/misc/stmts.c) and then run the
query "SELECT sql FROM stmts" and print out the results just prior to
calling sqlite3_close().  That will clearly show you which prepared
statements you have forgotten to finalize.
--
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: Cannot delete a Database file?

domonic
Thanks everyone for your help.  I was getting the error in some cases SQLITE_MISUSE .  That's error code 21.


Throughout my functions I may have called sqlite_close too much and this has caused some issues when going to delete the file.  At this point removing all the excess sqlite_close(handle) calls it seems to have ended the issue.


If I try to close a database using the  sqlite_close(handle) function and I get a return code of SQLITE_MISUSE, will this prevent me from deleting the database file that 'handle' connects to?


Thanks


________________________________
From: sqlite-users <[hidden email]> on behalf of Richard Hipp <[hidden email]>
Sent: Wednesday, 28 June 2017 7:55 PM
To: SQLite mailing list
Subject: Re: [sqlite] Cannot delete a Database file?

On 6/28/17, Domonic Tom <[hidden email]> wrote:
>
> Would anyone know how to go about making sure that the database file is not
> busy and can be deleted.  Is there a specific function for this.
>

Call sqlite3_finalize() on all prepared statements before calling
sqlite3_close().  Probably you are leaking prepare statements - in
other words there are one or more prepared statements that you have
failed to call sqlite3_finalize() for.

Consider adding the "stmts" virtual table to your project
(https://www.sqlite.org/src/file/ext/misc/stmts.c) and then run the
query "SELECT sql FROM stmts" and print out the results just prior to
calling sqlite3_close().  That will clearly show you which prepared
statements you have forgotten to finalize.
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
sqlite-users Info Page<http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users>
mailinglists.sqlite.org
To see the collection of prior postings to the list, visit the sqlite-users Archives. (The current archive is only available to the list ...



_______________________________________________
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: Cannot delete a Database file?

domonic
In reply to this post by Richard Hipp-3
Woops, I spoke too soon.  Still can't delete the file.
I don't have any prepared statements, just simple sqlite3_open_v2 and sqlite3_close statements.  But, something along the lines of what you are saying could be the issue.  

I'm checking it out now.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Cannot delete a Database file?

Richard Hipp-3
In reply to this post by domonic
On 6/28/17, Domonic Tom <[hidden email]> wrote:
> I was getting the error in some cases
> SQLITE_MISUSE .

An SQLITE_MISUSE error code means you have done something wrong that
could potentially cause a segfault or other application crash.  You
need to fix that.  It's important.

And, yes, an SQLITE_MISUSE error code could possibly prevent your file
from being deleted later on.


--
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: Cannot delete a Database file?

Simon Slavin-3
In reply to this post by domonic
On 28 Jun 2017, at 10:37am, Domonic Tom <[hidden email]> wrote:

> After calling sqlite3_close(handle) I try then to use the 'remove' method in C++ to delete my .db file.  For some reason it just won't delete.

Are you testing the result returned by sqlite3_close() to see if it’s SQLITE_OK ?  This doesn’t definitely mean you’re doing nothing wrong, but it helps.

> It's hard to show you the whole project because it's large.
>
>
> I believe this has something to do with sqlite3_close only de-allocating the connection to the database.
>
>
> Would anyone know how to go about making sure that the database file is not busy and can be deleted.  Is there a specific function for this.

Purely for testing, call sqlite3_shutdown() before you delete the file.  Does it work now ?  Then your call to sqlite3_close() didn’t work properly because you still had some open handles to 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: Cannot delete a Database file?

Dan Kennedy-4
On 06/28/2017 09:07 PM, Simon Slavin wrote:

> On 28 Jun 2017, at 10:37am, Domonic Tom <[hidden email]> wrote:
>
>> After calling sqlite3_close(handle) I try then to use the 'remove' method in C++ to delete my .db file.  For some reason it just won't delete.
> Are you testing the result returned by sqlite3_close() to see if it’s SQLITE_OK ?  This doesn’t definitely mean you’re doing nothing wrong, but it helps.
>
>> It's hard to show you the whole project because it's large.
>>
>>
>> I believe this has something to do with sqlite3_close only de-allocating the connection to the database.
>>
>>
>> Would anyone know how to go about making sure that the database file is not busy and can be deleted.  Is there a specific function for this.
> Purely for testing, call sqlite3_shutdown() before you delete the file.  Does it work now ?  Then your call to sqlite3_close() didn’t work properly because you still had some open handles to the file.

I don't think that will work.

sqlite3_shutdown() will not close any open file descriptors or free any
other leaked resources. It just calls shutdown hooks for things like
custom malloc or mutex operations. Leaked resources will stay leaked.

Dan.

_______________________________________________
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: Cannot delete a Database file?

Simon Slavin-3


On 28 Jun 2017, at 3:24pm, Dan Kennedy <[hidden email]> wrote:

> sqlite3_shutdown() will not close any open file descriptors or free any other leaked resources.

Ah.  I read that it calls sqlite3_os_end() and the documentation says

"The sqlite3_os_init() routine does operating-system specific initialization of the SQLite library. The sqlite3_os_end() routine undoes the effect of sqlite3_os_init(). Typical tasks performed by these routines include allocation or deallocation of static resources […]"

I guess I missed the word "static" there.  So if something unpredicted happened SQLite there’s no way to tell it to tidy things up the best it can ?  You just have to quit the process ?

That’s two wrong things I’ve posted today.  Must try harder.  Sorry about that.

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: Cannot delete a Database file?

Stephen Chrzanowski
Its called learning.  I've been shot down for this kind of err before. ;)

On Wed, Jun 28, 2017 at 10:33 AM, Simon Slavin <[hidden email]> wrote:

>
>
> On 28 Jun 2017, at 3:24pm, Dan Kennedy <[hidden email]> wrote:
>
> That’s two wrong things I’ve posted today.  Must try harder.  Sorry about
> that.
>
> 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: Cannot delete a Database file?

R Smith
In reply to this post by Simon Slavin-3

On 2017/06/28 4:33 PM, Simon Slavin wrote:
> That’s two wrong things I’ve posted today. Must try harder. Sorry
> about that.

I don't think that's a problem, we've seen wrong answers by near
everyone here - this is the beauty of a forum like this:
Once you are wrong, not only are there numerous people who might post a
corrected answer or further a discussion enlightening us all, but often
those replies are what clears up beliefs by others who may have been
under the very same wrong impression as the person who posted - and that
is a valuable educational principle and facility.

Case-in-point: Dan's reply to your "wrong" post taught me more about
what sqlite3_shutdown() does than the documentation did.

Keep up the good work!
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
|  
Report Content as Inappropriate

Re: Cannot delete a Database file?

Warren Young
On Jun 28, 2017, at 8:53 AM, R Smith <[hidden email]> wrote:
>
>
> On 2017/06/28 4:33 PM, Simon Slavin wrote:
>> That’s two wrong things I’ve posted today. Must try harder. Sorry about that.
>
> I don't think that's a problem…

It’s a well-known phenomenon, called by some Cunningham’s Law: “The best way to get the right answer on the internet is not to ask a question; it's to post the wrong answer.”

Also: https://www.xkcd.com/386/
_______________________________________________
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: Cannot delete a Database file?

domonic
The database file I am trying to delete is in use when I try to delete it, so it's obviously still open.
This is what I've done.

I have made my connection to the database (sqlite3 *handle) global.
If this is the case then just before I delete the file I could close the connection (sqlite3_close(handle))

But this returns the error SQLITE_MISUSE.

What conditions would incur such an error.  This is obviously the reason why I can't close the database but why would I not be able to close it and receive SQLITE_MISUSE as a return value?
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Cannot delete a Database file?

Simon Slavin-3


On 2 Jul 2017, at 5:06am, domonic <[hidden email]> wrote:

> The database file I am trying to delete is in use when I try to delete it, so
> it's obviously still open.

From whqt you wrote so far, you might have an anti-virus package installed which is still checking the database when you try to delete it.  Anti-virus packages are meant to uderstand this situation and cope with it by hooking the delete routine, but sometimes you can fool them.

> This is what I've done.
>
> I have made my connection to the database (sqlite3 *handle) global.
> If this is the case then just before I delete the file I could close the
> connection (sqlite3_close(handle))
>
> But this returns the error SQLITE_MISUSE.
>
> What conditions would incur such an error.

Use debugging commands to check that the handle you’re passing to _close() is the same value you got from opening the file.  Something may have changed the value of your global variable.

Check your code to make sure you have run _finalize() on all statements associated with that handle.  And check the result returned by each one to make sure it worked.

If you execute _close() immediately after _open(), do you still get SQLITE_MISUSE ?  If not, then gradually move the _close() further and further down your code until it stops working.

If neither of the above are the problem, you’re probably stomping on memory which SQLite is using for its own purposes.  This could be the result of any misused pointers or arrays in your code.

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: Cannot delete a Database file?

domonic
Thanks for the help.  I eventually figured this out given the points above.
I simply output the open address and close address of each database I opened and closed to see if something wasn't being closed.

It turned out to be a far more simple problem in the end.  A silly one at that.

I was testing the closed state of a database like this.  After it closed on the first instance it would try to close the database two more times which is why the SQLITE_MISUSE error kept appearing.  Once that started I couldn't delete anything.  

Just a stupid programming error as I suspected.   No I just test the state of the database once and then test the output in my if statement.

if(sqlite3_close(.......))
{

}
else if(sqlite3_close(......))
{

}
else if(sqlite3_close(......))
{

}
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Cannot delete a Database file?

Keith Medcalf

Why not set the sqlite3* variable to NULL after a successful close.  Then you will always know if it is open or closed ... you can only "open" a database if it is currently closed (NULL) and only close a database (or do anything to it, really) if it is open (!=NULL).  You should do the same for statement pointers as well ... initialize them to 0, only prepare them if they are NULL, and only finalize (or do anything else to them) if they are != NULL.

Then you will always know if a pointer to a structure is valid or not.

--
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı


> -----Original Message-----
> From: sqlite-users [mailto:[hidden email]]
> On Behalf Of domonic
> Sent: Friday, 7 July, 2017 20:34
> To: [hidden email]
> Subject: Re: [sqlite] Cannot delete a Database file?
>
> Thanks for the help.  I eventually figured this out given the points
> above.
> I simply output the open address and close address of each database I
> opened
> and closed to see if something wasn't being closed.
>
> It turned out to be a far more simple problem in the end.  A silly one at
> that.
>
> I was testing the closed state of a database like this.  After it closed
> on
> the first instance it would try to close the database two more times which
> is why the SQLITE_MISUSE error kept appearing.  Once that started I
> couldn't
> delete anything.
>
> Just a stupid programming error as I suspected.   No I just test the state
> of the database once and then test the output in my if statement.
>
> if(sqlite3_close(.......))
> {
>
> }
> else if(sqlite3_close(......))
> {
>
> }
> else if(sqlite3_close(......))
> {
>
> }
>
>
>
> --
> View this message in context: http://sqlite.1065341.n5.nabble.com/Cannot-
> delete-a-Database-file-tp96397p96540.html
> Sent from the SQLite mailing list archive at Nabble.com.
> _______________________________________________
> 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: Cannot delete a Database file?

Simon Slavin-3
In reply to this post by domonic
On 8 Jul 2017, at 3:33am, domonic <[hidden email]> wrote:

> No I just test the state
> of the database once and then test the output in my if statement.
>
> if(sqlite3_close(.......))
> {
>
> }
> else if(sqlite3_close(......))
> {
>
> }
> else if(sqlite3_close(......))
> {
>
> }

Glad you found a solution.  I recommend you put a one second delay between those attempts to give any anti-virus or indexing utility time to finish 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: Cannot delete a Database file?

Rowan Worth-2
On 8 July 2017 at 10:48, Simon Slavin <[hidden email]> wrote:

> On 8 Jul 2017, at 3:33am, domonic <[hidden email]> wrote:
>
> > No I just test the state
> > of the database once and then test the output in my if statement.
> >
> > if(sqlite3_close(.......))
> > {
> >
> > }
> > else if(sqlite3_close(......))
> > {
> >
> > }
> > else if(sqlite3_close(......))
> > {
> >
> > }
>
> Glad you found a solution.  I recommend you put a one second delay between
> those attempts to give any anti-virus or indexing utility time to finish
> with the file.
>

Note that sqlite3_close on windows already tries to close the file three
times times (with a 100ms delay between each attempt). Which is not to say
that waiting a second is a bad idea if that does fail, but are you actually
hitting the second sqlite3_close?

-Rowan
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...