All of a sudden I must commit after every single change and I don't know what happened.

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

All of a sudden I must commit after every single change and I don't know what happened.

bensonbear
I have an sqlite3 database I use for a web app that I am mostly the sole user
of.
 
It has been working fine for years, but all of a sudden today, I find that
the app cannot insert and delete items from the database when it needs to.

This is an app with a single thread, and for each command the web client
sends to the server written in python, the server opens a new database
connection/cursor (using module sqlite3) does the queries required, commits,
and closes the connection.  
 
That has always worked.

But today, I found that a sequence of actions done in one function with one
database connection/cursor will not work because the later ones do not see
the changes of the earlier ones.   Is my understanding correct that as long
as it is the same connection/cursor, these changes should be seen?  And, if
not, how is it that it worked in the past?  And, in either case, what could
have happened all of a sudden for it to stop working?

For the time being, I am changing now to commit after every query that makes
any change to the database and things work.   But I don't want to do that if
it is not necessary.




--
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: All of a sudden I must commit after every single change and I don't know what happened.

Richard Hipp-3
On 9/24/17, bensonbear <[hidden email]> wrote:
>
> But today, I found that a sequence of actions done in one function with one
> database connection/cursor will not work because the later ones do not see
> the changes of the earlier ones.   Is my understanding correct that as long
> as it is the same connection/cursor, these changes should be seen?

Correct

> And, in either case, what could
> have happened all of a sudden for it to stop working?

Good question.  I suspect that whatever is going wrong has little or
nothing to do with SQLite, though.  What other debugging steps have
you taken?

--
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
|

Re: All of a sudden I must commit after every single change and I don't know what happened.

Simon Slavin-3
In reply to this post by bensonbear
Are the changes actually making it to the file on disk ?  In other words, if you do _open(), INSERT, _close(), does the disk file get updated ?

What PRAGMAs are you using ?

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
|

Re: All of a sudden I must commit after every single change and I don't know what happened.

R Smith
In reply to this post by bensonbear
Web App you say....

I imagine you are using some wrapper, possibly a JAVA one, PHP or a
PEARL one, I think your web service may have updated so the supported
wrapper updated with possibly a new default setting or such. Those
wrappers usually have a setting/property called "AutoCommit" which
/might/ be the thing that is messing with you. Try searching for it on
the forums for whatever platform you are using, you are bound to find a
lot of mentions and info on it.

If that doesn't solve your problem, could you hook into the SQLite
logging? Or debug the process (I realize this can be difficult via a
wrapper, but the wrapper itself may have some tricks to accommodate it).

Either way, next time you write in, please mention exactly which
platform, which wrapper (if any) and perhaps the extract of the actual
code that does the calling. Even if it is not an SQLite problem
directly, there are likely many people here who use the same
platform/wrapper that you do (perhaps even the wrapper's author).

Cheers,
Ryan


On 2017/09/24 11:58 PM, bensonbear wrote:

> I have an sqlite3 database I use for a web app that I am mostly the sole user
> of.
>  
> It has been working fine for years, but all of a sudden today, I find that
> the app cannot insert and delete items from the database when it needs to.
>
> This is an app with a single thread, and for each command the web client
> sends to the server written in python, the server opens a new database
> connection/cursor (using module sqlite3) does the queries required, commits,
> and closes the connection.
>  
> That has always worked.
>
> But today, I found that a sequence of actions done in one function with one
> database connection/cursor will not work because the later ones do not see
> the changes of the earlier ones.   Is my understanding correct that as long
> as it is the same connection/cursor, these changes should be seen?  And, if
> not, how is it that it worked in the past?  And, in either case, what could
> have happened all of a sudden for it to stop working?
>
> For the time being, I am changing now to commit after every query that makes
> any change to the database and things work.   But I don't want to do that if
> it is not necessary.
>
>

_______________________________________________
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: All of a sudden I must commit after every single change and I don't know what happened.

bensonbear
In reply to this post by Richard Hipp-3
D.Richard Hipp writes:

| Good question.  I suspect that whatever is going wrong has little or
| nothing to do with SQLite, though.  What other debugging steps have
| you taken?

I could not think of anything very useful to try, but in any case I
found pretty quickly that the problem  was the fact that activity
elsewhere had nearly filled up my root partition.   I don't know
exactly how this would explain the observed behavior
but I would speculate that uncommitted changes were
lost because of some temporary file cache, but committed
changes made it to the actual database file on another partition?

(In any case my code should find these problems; it does not
have adequate error checking in it to see if each statement that
changes the database has been successful)

Simon writes:
| Are the changes actually making it to the file on disk ?  
| In other words, if you do _open(), INSERT, _close(), does the disk file
get updated ?
| What PRAGMAs are you using ?

Moot questions now, but yes the changes made it to disk if
I did a commit after each statement that changed the database.
Very simple database, no "pragma"s at all.

Ryan writes:

| I imagine you are using some wrapper, possibly a JAVA one, PHP or
| a PEARL one, I think your web service may have updated so the
| supported wrapper updated with possibly a new default setting or such

Excellent guess given what I reported, but I neglected to say there is
no web service -- the server is just my own home computer, everything
updated would be by me, but nothing relevant was updated
at the time the problem arose (I chased a few red herrings along these
lines).

| Either way, next time you write in, please mention exactly which
| platform, which wrapper (if any) and perhaps the extract of the actual
| code that does the calling. Even if it is not an SQLite problem
| directly, there are likely many people here who use the same
| platform/wrapper that you do (perhaps even the wrapper's author).

I mentioned I was using python and its sqlite3 module, but did
not specify I was using it in simplest possible default mode, just
opening the connection with no arguments.  

I didn't need to know more before -- with a simple single-threaded
program  I thought (and still think) it should be fine to  
just  open a connection, do stuff, commit and close.
This appears to be true, but reading more about the sqlite3
module, what it does in the default mode is a little tricky with respect
to when commits are done.  

I didn't even know that outside transactions, SQLite by
default itself commits automatically after each statement,  
which I would not have wanted.  However, the  python
module by default implicitly inserts "begin"s so that
statements are grouped into a transaction that one then has to
explicitly commit  Thus, that the commit I did after them was necessary
for that reason, and not the reason I assumed, which was that
SQLite itself needed the commit even outside transactions.
(I think I would prefer the module's option where it keeps SQLite
in autocommit mode and one then has to make one's own
transactions  explicitly).

None of this, as far as I can see, should cause what I
observed, however, as it was all within the same
connection.  It was very mysterious but as
Dr Hipp correctly speculated, had nothing to do with
SQLite.

Thanks for all the replies everyone.




--
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: All of a sudden I must commit after every single change and I don't know what happened.

R Smith

On 2017/09/27 8:35 AM, bensonbear wrote:
> I didn't even know that outside transactions, SQLite by
> default itself commits automatically after each statement,
> which I would not have wanted.  However, the  python
> module by default implicitly inserts "begin"s so that
> statements are grouped into a transaction that one then has to
> explicitly commit  Thus, that the commit I did after them was necessary
> for that reason, and not the reason I assumed, which was that
> SQLite itself needed the commit even outside transactions.

You already seem to have found the correct behaviour, but just to be
clear: It's not that SQLite commits after every statement - In SQLite,
unless you have explicitly opened a transaction (using BEGIN), every
statement is (and must be) in and of itself a transaction. I imagine
this is needed to ensure Atomicity in a DB where locking is dependent on
an external mechanism (File system in this case).  I could be wrong
about the reason, but the behaviour remains.

Glad you figured out the culprit!
Cheers,
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: All of a sudden I must commit after every single change and I don't know what happened.

Simon Slavin-3
In reply to this post by bensonbear


On 27 Sep 2017, at 7:35am, bensonbear <[hidden email]> wrote:

> I could not think of anything very useful to try, but in any case I
> found pretty quickly that the problem  was the fact that activity
> elsewhere had nearly filled up my root partition.   I don't know
> exactly how this would explain the observed behavior
> but I would speculate that uncommitted changes were
> lost because of some temporary file cache, but committed
> changes made it to the actual database file on another partition?

That sounds plausible.  SQLite would have run out of space while fiddling with a journal file.  Were you checking the result codes returned for each sqlite_ function call ?

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
|

Re: All of a sudden I must commit after every single change and I don't know what happened.

Keith Medcalf
In reply to this post by bensonbear
On Wednesday, 27 September, 2017 00:35, bensonbear <[hidden email]> wrote:

>I mentioned I was using python and its sqlite3 module, but did
>not specify I was using it in simplest possible default mode, just
>opening the connection with no arguments.

>I didn't need to know more before -- with a simple single-threaded
>program  I thought (and still think) it should be fine to
>just  open a connection, do stuff, commit and close.
>This appears to be true, but reading more about the sqlite3
>module, what it does in the default mode is a little tricky with
>respect to when commits are done.

>I didn't even know that outside transactions, SQLite by
>default itself commits automatically after each statement,
>which I would not have wanted.  However, the  python
>module by default implicitly inserts "begin"s so that
>statements are grouped into a transaction that one then has to
>explicitly commit  Thus, that the commit I did after them was
>necessary
>for that reason, and not the reason I assumed, which was that
>SQLite itself needed the commit even outside transactions.
>(I think I would prefer the module's option where it keeps SQLite
>in autocommit mode and one then has to make one's own
>transactions  explicitly).

Yeah, the default sqlite3 interface (pysqlite2) in python is weird.  I switched to APSW a long time ago as it is less magical and better designed (and faster and more efficient as well).  For simple use it is almost no more complicated than changing the name of the module and the options on the open call.  You lose the data adapter stuff that is in the sqlite3 wrapper, but you gain a lot of other stuff since it is truly a "wrapper" for the Sqlite3 library and not a "standardized interface designed to be compatible with every RDBMS on the market" -- so you have access to just about all of the SQLite3 API functions via Python, not just the minimalized set that are supported by "everyone and their grandmother" (aka the DB-API).

see the main page at https://github.com/rogerbinns/apsw 
and https://rogerbinns.github.io/apsw/pysqlite.html for an overview of the differences between the pysqlite2 wrapper and APSW.

The best way to deal with transactions in the pysqlite2 wrapper is to set isolation=None on the open.  This means that the magic is turned off in the wrapper and that you must explicitly begin or end transactions.  If you do not, then the SQLite library (not the wrapper) runs in its default "autocommit" mode.  This is especially important if you either format your SQL statements or use some that are not handled appropriately by the wrapper.  Plus, your observed behaviour will exactly match the results obtained by issuing the same sequence of statements using the sqlite3 command line tool with no possible interference by magic occurring behind the scene.

About the only (real) drawback of APSW is that it only works with standard CPython.  It does not work with IronPython, PyPy, or many other Python interpreters.  If this is important to you then you might want to stick with the pysqlite2 wrapper included as part of the Python standard library and ported to almost all other Python interpreters.

Of course, if you use some of the APSW features that are inaccessible in other wrappers like pysqlite2 such as the execution hooks, authorizer hooks, logging and detailed statement hooks, you cannot backport to the DB-API which simply does not support all these interfaces.




_______________________________________________
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: All of a sudden I must commit after every single change and I don't know what happened.

bensonbear
In reply to this post by R Smith

Ryan writes:

| just to be clear: It's not that SQLite commits after every
| statement - In SQLite, unless you have explicitly opened a
| transaction (using BEGIN), every statement is (and must be)
| in and of itself a transaction.

Right, I didn't say it committed after every statement, just
after every statement outside of a transaction (by which I
meant an explicit transaction -- perhaps that was not clear
enough).   So as I understand it the net effect is, when
outside a(n explicit)  transaction, a  commit is  done after each
statement.

Simon writes:

| Were you checking the result codes returned for each sqlite_ function call
?

Alas, as I sheepishly admitted earlier, no.   I am not sure one can get
all of the explicit result codes using the python module, but one can at
least
get a more generic exception, and I will add that because it should have
been there all along (lame excuse: quickly done program primarily for
personal use at current).

Keith Medcalf writes:

| Yeah, the default sqlite3 interface (pysqlite2) in python is
| weird.  I switched to APSW a long time ago as it is less
| magical and better designed

Thanks, I had not noticed this other possibility.   I think I
will stick with sqlite3 module (as it is now called) for now
since it does everything I need, but may switch later once
I start making my program do more and determine
that APSW has net better functionality for that.

| The best way to deal with transactions in the pysqlite2 wrapper is to
| set isolation=None on the open.

Right, this is the mode that I mentioned preferring once
I saw what the default mode was, and probably will alter
my code to employ.  Glad to see others think the default
mode is a little "weird".

Sorry if there are some formatting anomalies here:  I
am posting by the web access at nabble.  I thought
I joined the mailing list itself but apparently it keeps insisting
I have not.






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