[python-sqlite] apsw: cache corrupted?

[Nikolaus Rath]

Hello,

Can someone explain to me what the hell is going on here?

> newbindings
[871684]

> cur.execute('DELETE FROM inodes WHERE id=?', newbindings)
Traceback (most recent call last):
File "/home/nikratio/.local/eclipse/plugins/org.python.pydev.debug_1.5.6.2010033101/pysrc/pydevd_comm.py", line 664, in doIt
result = pydevd_vars.evaluateExpression(self.thread_id, self.frame_id, self.expression, self.doExec)
File "/home/nikratio/.local/eclipse/plugins/org.python.pydev.debug_1.5.6.2010033101/pysrc/pydevd_vars.py", line 333, in evaluateExpression
result = eval(compiled, updated_globals, frame.f_locals)
File "<string>", line 1, in <module>
File "src/cursor.c", line 230, in resetcursor
ConstraintError: ConstraintError: foreign key constraint failed

> cur.execute('delete from inodes where id=?', newbindings)
<apsw.Cursor object at 0x8a7cd40>

> cur.execute('DELETE FROM inodes WHERE id=?', newbindings)
Traceback (most recent call last):
File "/home/nikratio/.local/eclipse/plugins/org.python.pydev.debug_1.5.6.2010033101/pysrc/pydevd_comm.py", line 664, in doIt
result = pydevd_vars.evaluateExpression(self.thread_id, self.frame_id, self.expression, self.doExec)
File "/home/nikratio/.local/eclipse/plugins/org.python.pydev.debug_1.5.6.2010033101/pysrc/pydevd_vars.py", line 333, in evaluateExpression
result = eval(compiled, updated_globals, frame.f_locals)
File "<string>", line 1, in <module>
File "src/cursor.c", line 230, in resetcursor
ConstraintError: ConstraintError: foreign key constraint failed


The only thing I can come with is that the statement cache got corrupted
and apsw tries to execute a wrong statement.

I can reproduce this at will, but I haven't been able to come up with a
small test case (it seems that I need to execute a lot of other db
transactions first).

But even with a tracer:

> def dump(cursor, sql, bindings):
> log.info('SQL: %r, bindings: %r', sql, bindings)
> return True
> cur.setexectrace(dump)
> cur.execute('DELETE FROM inodes WHERE id=?', newbindings)

SQL: u'DELETE FROM inodes WHERE id=?', bindings: [871684]
Traceback (most recent call last):
File "/home/nikratio/.local/eclipse/plugins/org.python.pydev.debug_1.5.6.2010033101/pysrc/pydevd_comm.py", line 664, in doIt
result = pydevd_vars.evaluateExpression(self.thread_id, self.frame_id, self.expression, self.doExec)
File "/home/nikratio/.local/eclipse/plugins/org.python.pydev.debug_1.5.6.2010033101/pysrc/pydevd_vars.py", line 333, in evaluateExpression
result = eval(compiled, updated_globals, frame.f_locals)
File "<string>", line 1, in <module>
File "src/cursor.c", line 230, in resetcursor
ConstraintError: ConstraintError: foreign key constraint failed

> cur.execute('DELETE from inodes WHERE id=?', newbindings)

<apsw.Cursor object at 0x8a7cd40>
SQL: u'DELETE from inodes WHERE id=?', bindings: [871684]


> apsw.apswversion()

3.6.23.1-r1



Confused,

-Nikolaus

--
»Time flies like an arrow, fruit flies like a Banana.«

PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6 02CF A9AD B7F8 AE4E 425C

[Roger Binns]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 04/29/2010 07:35 AM, Nikolaus Rath wrote:
> Can someone explain to me what the hell is going on here?

For a start your tracebacks do not resemble reality :-)

>> cur.execute('DELETE FROM inodes WHERE id=?', newbindings)
> Traceback (most recent call last):
> File "/home/nikratio/.local/eclipse/plugins/org.python.pydev.debug_1.5.6.2010033101/pysrc/pydevd_comm.py", line 664, in doIt
> result = pydevd_vars.evaluateExpression(self.thread_id, self.frame_id, self.expression, self.doExec)
> File "/home/nikratio/.local/eclipse/plugins/org.python.pydev.debug_1.5.6.2010033101/pysrc/pydevd_vars.py", line 333, in evaluateExpression
> result = eval(compiled, updated_globals, frame.f_locals)
> File "<string>", line 1, in <module>
> File "src/cursor.c", line 230, in resetcursor
> ConstraintError: ConstraintError: foreign key constraint failed

resetcursor is not an exposed method. The traceback should have other APSW
code before it such as the execute method.

> The only thing I can come with is that the statement cache got corrupted
> and apsw tries to execute a wrong statement.

You can have no statement cache or make it considerably smaller (default
100). Note that there is heavy testing of the statement cache code.

http://apsw.googlecode.com/svn/publish/connection.html#apsw.Connection

I also recommend you use a custom routine to print out the exception to
include the (faked) local variables which will show more detail of what is
going on in the C code.

http://apsw.googlecode.com/svn/publish/exceptions.html#augmented-stack-traces

> File "/home/nikratio/.local/eclipse/plugins/org.python.pydev.debug_1.5.6.2010033101/pysrc/pydevd_comm.py", line 664, in doIt
> result = pydevd_vars.evaluateExpression(self.thread_id, self.frame_id, self.expression, self.doExec)

Can you reproduce it using just plain old CPYthon - ie no Eclipse and any
stunts it may be doing?

>> apsw.apswversion()
>
> 3.6.23.1-r1

Is this the as distributed version or one that you have renamed, compiled
SQLite with 32 bit rowids etc?

Roger
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkvZo/wACgkQmOOfHg372QSxlwCeIWBXB7jMieJhdGuEYZBGE//Y
tywAn2iUeRYqF40rzNKVqzlMqEfJQhTb
=RAmB
-----END PGP SIGNATURE-----

[Nikolaus Rath]

Roger Binns <rogerb-JFdGOZ7...@public.gmane.org> writes:
> On 04/29/2010 07:35 AM, Nikolaus Rath wrote:
>> Can someone explain to me what the hell is going on here?
>
> For a start your tracebacks do not resemble reality :-)

I'm doing the best I can :-)

>
>>> cur.execute('DELETE FROM inodes WHERE id=?', newbindings)
>> Traceback (most recent call last):
>> File "/home/nikratio/.local/eclipse/plugins/org.python.pydev.debug_1.5.6.2010033101/pysrc/pydevd_comm.py", line 664, in doIt
>> result = pydevd_vars.evaluateExpression(self.thread_id, self.frame_id, self.expression, self.doExec)
>> File "/home/nikratio/.local/eclipse/plugins/org.python.pydev.debug_1.5.6.2010033101/pysrc/pydevd_vars.py", line 333, in evaluateExpression
>> result = eval(compiled, updated_globals, frame.f_locals)
>> File "<string>", line 1, in <module>
>> File "src/cursor.c", line 230, in resetcursor
>> ConstraintError: ConstraintError: foreign key constraint failed
>
> resetcursor is not an exposed method. The traceback should have other APSW
> code before it such as the execute method.
>
>> The only thing I can come with is that the statement cache got corrupted
>> and apsw tries to execute a wrong statement.
>
> You can have no statement cache or make it considerably smaller (default
> 100). Note that there is heavy testing of the statement cache code.
>
> http://apsw.googlecode.com/svn/publish/connection.html#apsw.Connection
>
> I also recommend you use a custom routine to print out the exception to
> include the (faked) local variables which will show more detail of what is
> going on in the C code.
>
> http://apsw.googlecode.com/svn/publish/exceptions.html#augmented-stack-traces
>
>> File "/home/nikratio/.local/eclipse/plugins/org.python.pydev.debug_1.5.6.2010033101/pysrc/pydevd_comm.py", line 664, in doIt
>> result = pydevd_vars.evaluateExpression(self.thread_id, self.frame_id, self.expression, self.doExec)
>
> Can you reproduce it using just plain old CPYthon - ie no Eclipse and any
> stunts it may be doing?

Here is the augmented exception, printed from pdb using the
print_exc_plus function:

Constraint error when executing 'DELETE FROM inodes WHERE id=?' with bindings (680,)
> /home/nikratio/projekte/s3ql/src/s3ql/database.py(319)_execute()
-> raise
(Pdb) p print_exc_plus()

Traceback (most recent call last):

File "/home/nikratio/projekte/s3ql/src/s3ql/database.py", line 312, in _execute
return cur.execute(statement, newbindings)

File "src/cursor.c", line 230, in resetcursor
ConstraintError: ConstraintError: foreign key constraint failed

Locals by frame, innermost last

Frame _execute in /home/nikratio/projekte/s3ql/src/s3ql/database.py at line 319
cur = <apsw.Cursor object at 0x9c8f090>
val = 680
self = <s3ql.database.WrappedConnection object at 0x9ca8ccc>
newbindings = [680]
statement = DELETE FROM inodes WHERE id=?
bindings = (680,)

Frame resetcursor in src/cursor.c at line 230
res = 19
None

This time, the problem looks a little bit different though. Now I always
get the constraint error:

(Pdb) p cur.execute('DELETE from inodes where id=?', newbindings)
*** ConstraintError: ConstraintError('ConstraintError: foreign key constraint failed',)

But there are no problematic references to this key. These are all the
foreign key definitions:

(Pdb) p list(cur.execute('pragma foreign_key_list(blocks)'))
[(0, 0, u'objects', u'obj_id', u'id', u'NO ACTION', u'NO ACTION', u'NONE'),
(1, 0, u'inodes', u'inode', u'id', u'NO ACTION', u'NO ACTION', u'NONE')]

(Pdb) p list(cur.execute('pragma foreign_key_list(contents)'))
[(0, 0, u'inodes', u'parent_inode', u'id', u'NO ACTION', u'NO ACTION', u'NONE'),
(1, 0, u'inodes', u'inode', u'id', u'NO ACTION', u'NO ACTION', u'NONE')]

(Pdb) p list(cur.execute('pragma foreign_key_list(ext_attributes)'))
[(0, 0, u'inodes', u'inode', u'id', u'NO ACTION', u'NO ACTION', u'NONE')]

(Pdb) p list(cur.execute('pragma foreign_key_list(inodes)'))
[]
(Pdb) p list(cur.execute('pragma foreign_key_list(objects)'))
[]

And there are no references to inode 680:

(Pdb) p list(cur.execute('SELECT * from blocks where inode=?', newbindings))
[]
(Pdb) p list(cur.execute('SELECT * from ext_attributes where inode=?', newbindings))
[]
(Pdb) p list(cur.execute('SELECT * from contents where parent_inode=?', newbindings))
[]
(Pdb) p list(cur.execute('SELECT * from contents where inode=?', newbindings))
[]


Also, when I now start a separate APSW shell on the same database file:

sqlite> .tables
blocks
contents
ext_attributes
inodes
objects

(so there really are no other foreign keys)

sqlite> pragma foreign_keys = on;
sqlite> pragma foreign_keys;
1
sqlite> delete from inodes where id=680;
sqlite>

>>> apsw.apswversion()
>>
>> 3.6.23.1-r1
>
> Is this the as distributed version or one that you have renamed, compiled
> SQLite with 32 bit rowids etc?

No, this is the clone from hg with the faster sql dump, but without 32
bit rowids. I don't remember which revision it is exactly though (I only
kept the compiled version, not the hg clone).


Best,

[Roger Binns]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 04/29/2010 09:24 AM, Nikolaus Rath wrote:
> I'm doing the best I can :-)

This should also help:

http://www.chiark.greenend.org.uk/~sgtatham/bugs.html

> Frame _execute in /home/nikratio/projekte/s3ql/src/s3ql/database.py at line 319
> cur = <apsw.Cursor object at 0x9c8f090>
> val = 680
> self = <s3ql.database.WrappedConnection object at 0x9ca8ccc>
> newbindings = [680]
> statement = DELETE FROM inodes WHERE id=?
> bindings = (680,)
>
> Frame resetcursor in src/cursor.c at line 230
> res = 19

This still doesn't make sense. resetcursor is a static function. There
should be at least one frame inbetween s3ql and resetcursor. That
intermediary frame would also show the sql statement which would confirm or
disprove your statement cache hypothesis.

> This time, the problem looks a little bit different though. Now I always
> get the constraint error:

That Eclipse versus raw CPython give different results is disconcerting.

> (Pdb) p cur.execute('DELETE from inodes where id=?', newbindings)
> *** ConstraintError: ConstraintError('ConstraintError: foreign key constraint failed',)

APSW itself never generates the SQL execution errors. There are effectively
two possibilities. One is a bug in SQLite. The second is a bug in APSW
where somehow the error from a prior execution of the statement is hanging
around (or SQLite doing that). Disabling the statement cache will prevent
reuse of prepared statements.

> No, this is the clone from hg with the faster sql dump, but without 32
> bit rowids. I don't remember which revision it is exactly though (I only
> kept the compiled version, not the hg clone).

Try compiling (ie setup.py invocation) giving --debug to the build part.
This turns on assertions in both APSW and SQLite (also making things at
least 30% slower).

Roger
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkvZ9h4ACgkQmOOfHg372QQXSQCdEFnB1435KtoedSrNYfve7mCO
zooAoL4A/aMBmVS4qmti/FksxYd1iMeO
=NHH3
-----END PGP SIGNATURE-----

[Nikolaus Rath]

Roger Binns <rogerb-JFdGOZ7...@public.gmane.org> writes:
>> Frame _execute in /home/nikratio/projekte/s3ql/src/s3ql/database.py at line 319
>> cur = <apsw.Cursor object at 0x9c8f090>
>> val = 680
>> self = <s3ql.database.WrappedConnection object at 0x9ca8ccc>
>> newbindings = [680]
>> statement = DELETE FROM inodes WHERE id=?
>> bindings = (680,)
>>
>> Frame resetcursor in src/cursor.c at line 230
>> res = 19
>
> This still doesn't make sense. resetcursor is a static function. There
> should be at least one frame inbetween s3ql and resetcursor. That
> intermediary frame would also show the sql statement which would confirm or
> disprove your statement cache hypothesis.

Please believe me, I did not omit anything from the output. I copied and
pasted it completely. This is all I get.

>> This time, the problem looks a little bit different though. Now I always
>> get the constraint error:
>
> That Eclipse versus raw CPython give different results is disconcerting.

Yes... on the other hand, this is with a different inode, so the
situation is not exactly the same. The problem is that I have to run a
couple of hundred transactions before it fails, and apparently it
doesn't always fail with the same inode.

>> (Pdb) p cur.execute('DELETE from inodes where id=?', newbindings)
>> *** ConstraintError: ConstraintError('ConstraintError: foreign key constraint failed',)
>
> APSW itself never generates the SQL execution errors. There are effectively
> two possibilities. One is a bug in SQLite. The second is a bug in APSW
> where somehow the error from a prior execution of the statement is hanging
> around (or SQLite doing that). Disabling the statement cache will prevent
> reuse of prepared statements.

I tried setting the statement cache to zero. This considerably reduced
the number of failed transactions. Usually I have about 50 in 800
transactions, now I had exactly 1.

>> No, this is the clone from hg with the faster sql dump, but without 32
>> bit rowids. I don't remember which revision it is exactly though (I only
>> kept the compiled version, not the hg clone).
>
> Try compiling (ie setup.py invocation) giving --debug to the build part.
> This turns on assertions in both APSW and SQLite (also making things at
> least 30% slower).

Will do.


Since you say that it might be an APSW or SQLite bug: are you interested
in looking into this yourself? We could agree on a time and I'll open a
VNC server, so that you can work directly on this machine.

[Nikolaus Rath]

Roger Binns <rogerb-JFdGOZ7...@public.gmane.org> writes:
>> Frame _execute in /home/nikratio/projekte/s3ql/src/s3ql/database.py at line 319
>> cur = <apsw.Cursor object at 0x9c8f090>
>> val = 680
>> self = <s3ql.database.WrappedConnection object at 0x9ca8ccc>
>> newbindings = [680]
>> statement = DELETE FROM inodes WHERE id=?
>> bindings = (680,)
>>
>> Frame resetcursor in src/cursor.c at line 230
>> res = 19
>
> This still doesn't make sense. resetcursor is a static function. There
> should be at least one frame inbetween s3ql and resetcursor.

Maybe I'm using the wrong function to print the extended stack trace?
I followed the instructions on
http://apsw.googlecode.com/svn/publish/exceptions.html#augmented-stack-traces
and defined it as follows:


## {{{ http://code.activestate.com/recipes/52215/ (r1)
import sys, traceback

def print_exc_plus():
"""
Print the usual traceback information, followed by a listing of all the
local variables in each frame.
"""
tb = sys.exc_info()[2]
stack = []
while tb:
stack.append(tb.tb_frame)
tb = tb.tb_next

traceback.print_exc()
print("Locals by frame, innermost last")
for frame in stack:
print()
print("Frame %s in %s at line %s" % (frame.f_code.co_name,
frame.f_code.co_filename,
frame.f_lineno))
for key, value in frame.f_locals.items():
print("\t%20s = " % key, end='')
#We have to be careful not to cause a new error in our error
#printer! Calling str() on an unknown object could cause an
#error we don't want.
try: print(value)
except:
print("<ERROR WHILE PRINTING VALUE>")
## end of http://code.activestate.com/recipes/52215/ }}}

[Nikolaus Rath]

Hi,

Okay, I have some news. I tried everything with the current apsw release
from the homepage (compiled with ./setup.py fetch --sqlite build --debug
install --user), and with- and without statementcachesize=0.

- The problem does not occur if statementcachesize=0

- The key error that I previously observed when I disabled the
statement cache is actually a genuine, valid error.

- When I prevented this key error from occurring, none of the other
bogus errors appeared even after enabling the statement cache.

So, it seems that
- If the statement cache is enabled
- and a foreign key error is encountered with a statement
- then subsequent deletions with the same statement yield bogus key
errors.

This is the backtrace of the first, genuine foreign key error:

> /home/nikratio/projekte/s3ql/src/s3ql/database.py(319)_execute()
-> raise
(Pdb) p print_exc_plus()
Traceback (most recent call last):
File "/home/nikratio/projekte/s3ql/src/s3ql/database.py", line 312, in _execute
return cur.execute(statement, newbindings)
File "src/cursor.c", line 230, in resetcursor
ConstraintError: ConstraintError: foreign key constraint failed
Locals by frame, innermost last

Frame _execute in /home/nikratio/projekte/s3ql/src/s3ql/database.py at line 319

cur = <apsw.Cursor object at 0x9bd3800>
val = 2779947
self = <s3ql.database.WrappedConnection object at 0x9be93cc>
newbindings = [2779947]

statement = DELETE FROM inodes WHERE id=?

bindings = (2779947,)

Frame resetcursor in src/cursor.c at line 230
res = 19

None
(Pdb) p list(cur.execute('select * from blocks where inode=?', newbindings))
[(2779947, 1, 62785)]
(Pdb) c



This is the backtrace of the first bogus foreign key error afterwards:

> /home/nikratio/projekte/s3ql/src/s3ql/database.py(319)_execute()
-> raise
(Pdb) p print_exc_plus()
Traceback (most recent call last):
File "/home/nikratio/projekte/s3ql/src/s3ql/database.py", line 312, in _execute
return cur.execute(statement, newbindings)
File "src/cursor.c", line 230, in resetcursor
ConstraintError: ConstraintError: foreign key constraint failed
Locals by frame, innermost last

Frame _execute in /home/nikratio/projekte/s3ql/src/s3ql/database.py at line 319

cur = <apsw.Cursor object at 0x9bd3800>
val = 2779948
self = <s3ql.database.WrappedConnection object at 0x9be3d8c>
newbindings = [2779948]

statement = DELETE FROM inodes WHERE id=?

bindings = (2779948,)

Frame resetcursor in src/cursor.c at line 230
res = 19

None
(Pdb) p list(cur.execute('select * from blocks where inode=?', newbindings))
[]
(Pdb) p list(cur.execute('select * from contents where inode=?', newbindings))
[]
(Pdb) p list(cur.execute('select * from ext_attributes where inode=?', newbindings))
[]
(Pdb) p list(cur.execute('select * from contents where parent_inode=?', newbindings))[]

Observe that the statement fails:

(Pdb) p cur.execute('DELETE FROM inodes WHERE id=?', newbindings)

*** ConstraintError: ConstraintError('ConstraintError: foreign key constraint failed',)

But if we modify it so that the statement cache cannot be used, then it
succeeds:

(Pdb) p cur.execute('DELETE FROM inodes WHERE id=? AND 1=1', newbindings)
<apsw.Cursor object at 0x9bd3800>


I guess now I just need to find a way to generate a proper backtrace
with the missing frame, so that we can find out what exactly is going on
inside apsw.

[Roger Binns]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 04/29/2010 03:52 PM, Nikolaus Rath wrote:
> Since you say that it might be an APSW or SQLite bug: are you interested
> in looking into this yourself?

Once you are certain it is not a bug in your code.

> We could agree on a time and I'll open a
> VNC server, so that you can work directly on this machine.

The next few hours are good. I am on Californian time. Send an email to me
to coordinate.

Roger

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkvaKpgACgkQmOOfHg372QSAHgCgvIQmR3ityrJt8ytP+i6z/nHo
8UYAoNg9G4B1wPlaJrK9FTzJHfnxTsap
=K7Fd
-----END PGP SIGNATURE-----

[Roger Binns]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 04/29/2010 05:41 PM, Nikolaus Rath wrote:
> So, it seems that
> - If the statement cache is enabled
> - and a foreign key error is encountered with a statement
> - then subsequent deletions with the same statement yield bogus key
> errors.

It looks like the constraint error is somehow "sticky" to the prepared
statement. I see you have now got a nice small test case so I'll work on
that. Thank you.

Roger
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkvaK1IACgkQmOOfHg372QQlMQCfa4QaMTPesX/vqcy6AZvKG9IW
gd0AmQFFMG5lulqEsJBqTMOzV5IKlMao
=PJg3
-----END PGP SIGNATURE-----

[Nikolaus Rath]

On 04/29/2010 08:41 PM, Nikolaus Rath wrote:
> So, it seems that
> - If the statement cache is enabled
> - and a foreign key error is encountered with a statement
> - then subsequent deletions with the same statement yield bogus key
> errors.

Nailed it!

$ cat bug.py
#!/usr/bin/env python
import apsw
print apsw.apswversion()
print apsw.sqlitelibversion()
#conn = apsw.Connection('', statementcachesize=0)
conn = apsw.Connection('')
cur = conn.cursor()
cur.execute('PRAGMA FOREIGN_KEYS = ON')
cur.execute('''
CREATE TABLE main (
id INTEGER PRIMARY KEY
)''')
cur.execute('''
CREATE TABLE sub (
id INT NOT NULL REFERENCES main(id)
)''')
cur.execute('INSERT INTO main VALUES(?)', (1,))
cur.execute('INSERT INTO main VALUES(?)', (2,))
cur.execute('INSERT INTO sub VALUES(?)', (1,))
try:
cur.execute('DELETE FROM main WHERE id=?', (1,))
except apsw.ConstraintError:
pass
cur.execute('DELETE FROM main WHERE id=?', (2,))


$ ./bug.py
3.6.23.1-r1
3.6.23.1

Traceback (most recent call last):

File "./bug.py", line 24, in <module>
cur.execute('DELETE FROM main WHERE id=?', (2,))

File "src/cursor.c", line 230, in resetcursor

apsw.ConstraintError: ConstraintError: foreign key constraint failed


I reported this as http://code.google.com/p/apsw/issues/detail?id=99/. I just hope that I haven't overlooked anything and it's not an apsw error after all. But after spending so much time on figuring this out, I really needed the satisfaction to file it as somebody else's bug ;-).

[Roger Binns]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 04/29/2010 05:57 PM, Nikolaus Rath wrote:
> Nailed it!

It is a bug in SQLite and I have reported it together with C code to reproduce.

Your short term workaround is to disable the statement cache. It is also
possible to work around in the APSW code by treating the SQLITE_CONSTRAINT
error code very similar to SQLITE_SCHEMA (which forces it to be reprepared),
but this would have to be coded fairly carefully.

Thanks for tracking this down.

Roger
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkvaOU4ACgkQmOOfHg372QTYxgCfU1OMuqYFCAcswqcOdUNtZdH/
vLUAni6tLIJmf0ddN6ZxjwdG1XJSn1XJ
=XqZa
-----END PGP SIGNATURE-----

[Nikolaus Rath]

Roger Binns <rogerb-JFdGOZ7...@public.gmane.org> writes:
> On 04/29/2010 05:41 PM, Nikolaus Rath wrote:
>> So, it seems that
>> - If the statement cache is enabled
>> - and a foreign key error is encountered with a statement
>> - then subsequent deletions with the same statement yield bogus key
>> errors.
>
> It looks like the constraint error is somehow "sticky" to the prepared
> statement. I see you have now got a nice small test case so I'll work on
> that. Thank you.

No problem; I'm happy to help. Could you still look at the issue with
the backtrace though? Am I using the correct function to print it?

[Roger Binns]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 04/29/2010 08:15 PM, Nikolaus Rath wrote:
> No problem; I'm happy to help. Could you still look at the issue with
> the backtrace though? Am I using the correct function to print it?

You are. The exact code path followed to get this error doesn't have any
additional augmentation. It would be quite difficult to add. I've decided
not to add any as it would mainly only be useful for tracking bugs in the
statement cache (of which there are none :-) and the exectracer gives the
correct information anyway.

Roger
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkvaVeEACgkQmOOfHg372QTBRgCgliRowfJtTOsWTWLe1tPN9Kpz
zMkAni0lc+f8R9/6TznSKMksLPBAvr+a
=khF8
-----END PGP SIGNATURE-----

[Nikolaus Rath]

Roger Binns <rogerb-JFdGOZ7...@public.gmane.org> writes:
> On 04/29/2010 05:57 PM, Nikolaus Rath wrote:
>> Nailed it!
>
> It is a bug in SQLite and I have reported it together with C code to reproduce.
>
> Your short term workaround is to disable the statement cache. It is also
> possible to work around in the APSW code by treating the SQLITE_CONSTRAINT
> error code very similar to SQLITE_SCHEMA (which forces it to be reprepared),
> but this would have to be coded fairly carefully.

I've chosen a third alternative. Since I don't like to disable the cache
entirely, I'm simply "flush" it whenever I encounter a constraint
error (which supposedly is really the exception):

try:
return cur.execute(statement, newbindings)
except apsw.ConstraintError:
log.error('Constraint error when executing %r with bindings %r',
statement, bindings)
for i in range(100):
cur.execute('SELECT %d FROM inodes LIMIT 1' % i)
raise

This was easy to implement, since all my sql statements go through the
same wrapper anyway (which converts str to buffer and back).

[Roger Binns]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 04/30/2010 07:08 AM, Nikolaus Rath wrote:
> cur.execute('SELECT %d FROM inodes LIMIT 1' % i)

BTW that leaves actively executing statements around until garbage
collection happens. (Unless they have no results.)

> This was easy to implement, since all my sql statements go through the
> same wrapper anyway (which converts str to buffer and back).

A better alternative is to use whitespace.

for i in range(100):
cur.execute('SELECT null'+ ' '*i)

In any event it has now been fixed in SQLite.

http://www.sqlite.org/src/ci/f660be615a

You can easily get the latest SQLite development code by doing:

python setup.py fetch --sqlite --version=fossil

Roger
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkva8WAACgkQmOOfHg372QSF4wCeI6vMxHv3nhBY6eBG5IIIIkQv
0DsAn3E0X9PSDCEoamf414Dv/KYASq6p
=UHIl
-----END PGP SIGNATURE-----

[Nikolaus Rath]

Roger Binns <rogerb-JFdGOZ7...@public.gmane.org> writes:
> On 04/30/2010 07:08 AM, Nikolaus Rath wrote:
>> cur.execute('SELECT %d FROM inodes LIMIT 1' % i)
>
> BTW that leaves actively executing statements around until garbage
> collection happens. (Unless they have no results.)

Why is that? I am using the same cursor for every statement, so I
thought that first 99 statements are discarded right away and the 100th
statement is discarded as soon as the cursor is used again for something
else...

[Roger Binns]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 04/30/2010 02:00 PM, Nikolaus Rath wrote:
> Why is that? I am using the same cursor for every statement, so I
> thought that first 99 statements are discarded right away and the 100th
> statement is discarded as soon as the cursor is used again for something
> else...

Yes you are correct. I spend a lot of time convincing people to keep
creating new cursors rather than trying to repeatedly reuse an old one so
that they don't stomp on existing use that hasn't returned all values yet.
But at the same time to not leave cursors lying around without all the
results read since that can lead to busy errors.

Roger
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkvbtGoACgkQmOOfHg372QT1cACglxV7ChsZxiY/PwOB07JozJ1O
WsoAoMSvMehE6znou+niBByVJbZyeEaQ
=D9sk
-----END PGP SIGNATURE-----

[E.Pasma]

Hello,

I have some tools that may be interesting for others and put these in
a package in the cheese shop: http://pypi.python.org/pypi/sqmediumlite/2.1.0
There is documentation in there. I may revert to this If issues turn
up in this list where it can be applied.
I think a recent issue about 'strict transactions' is resolved with
apswdbapi2.

Best regards, Edzard Pasma

[Roger Binns]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 09/12/11 09:26, E.Pasma wrote:
> http://pypi.python.org/pypi/sqmediumlite/2.1.0

Is there anything I can do to apsw to make things easier?

For example you really shouldn't need to include a complete copy of the
APSW shell. There is also a C accelerated version of format_sql_value.
If you want a pure python version that is complete then look for the
source version just before the C version was introduced.

For the row factory stuff the approach of using a wrapped dict for each
row is very memory intensive. A better approach is to create a class
using slots and then uses instances for each row. The difficulty of doing
that from C code is why I haven't done it myself yet, but perhaps I should
revisit. (This is what the standard library named tuple implementation does.)

How complete is the DBAPI wrapper? If it is "perfect" then I'd be happy
to point to it from the apsw documentation.

Roger
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk7pjpEACgkQmOOfHg372QT4qQCeMbVaH5BaavvFnVOpoxN2obLP
U5MAnR1XS9vf/k17UxwH7fgZn1q92QfM
=Gd5F
-----END PGP SIGNATURE-----

[E.Pasma]

Hello Roger, thanks for your mild comments. I was afraid apswdbapi2
would be considered a disobedience to the mission of APSW. I'm
relieved. Further answer is in the text. Best regards, Edzard Pasma

On 15 dec 2011, om 07:07 Roger Binns has written:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On 09/12/11 09:26, E.Pasma wrote:
>> http://pypi.python.org/pypi/sqmediumlite/2.1.0
>
> Is there anything I can do to apsw to make things easier?

> For example you really shouldn't need to include a complete copy of
> the
> APSW shell. There is also a C accelerated version of
> format_sql_value.
> If you want a pure python version that is complete then look for the
> source version just before the C version was introduced.
>

Thanks for this tip. That will fix the dump of binary values.

The package contains multiple tools and the included copy of the shell
is used only for the network connection tool, not for apswdbapi2.
Apswdbapi2 uses the unmodified, true APSW shell.

> For the row factory stuff the approach of using a wrapped dict for
> each
> row is very memory intensive. A better approach is to create a class
> using slots and then uses instances for each row. The difficulty of
> doing
> that from C code is why I haven't done it myself yet, but perhaps I
> should
> revisit. (This is what the standard library named tuple
> implementation does.)
>

It is not clear to me yet how you can use slots here. As you say, you
need a class definition to map the column names to the slots. This
class differs per query and I do not see when/where to define it.
Possibly once in a pre-fetch stage?

> How complete is the DBAPI wrapper? If it is "perfect" then I'd be
> happy
> to point to it from the apsw documentation.
>

Wiith respect to the DB-API2, it lacks the Date/Time constructors:
Date, DateFromTicks, DateTime, The DB-API invites programmers to use
these contructors to pass DATE values into queries. Having no DATE
type in the database that is useles. So I think it is better to leave
them out.
(This may be different in the standard Python sqlite3 module which has
the feature of adapters and converters)

Apswdbap2 also lacks the Binary constructor. This is a different case
as its instances can be stored in the database as BLOB. It is not
essential but should be added.

Finally the data type objects (STRING, NUMBER etc) are omitted. These
are not in the standard sqlite3 module either. The DB-API2 expects
these types in cursor descriptions. But column data types are not
available in SQLite.

I am afraid that the module does not yet pass the criteria for a place
in the APSW documentation. Could we otherwise add a page to the WIKI
where to mention tools like this?

Edzard

[Roger Binns]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 15/12/11 16:25, E.Pasma wrote:
> I was afraid apswdbapi2 would be considered a disobedience to the
> mission of APSW. I'm relieved.

I have no intention of making APSW dbapi compatible, but I'm happy to make
pieces where it doesn't matter do things the dbapi way. For example
method names are the same as dbapi2.

And pointing folks to a wrapper that makes it dbapi compatible is just fine.

>> There is also a C accelerated version of format_sql_value.
>>

> Thanks for this tip. That will fix the dump of binary values.

It will also fix strings that have embedded nulls in them.

> It is not clear to me yet how you can use slots here. As you say, you
> need a class definition to map the column names to the slots. This
> class differs per query and I do not see when/where to define it.
> Possibly once in a pre-fetch stage?

It would be best defined on demand on the first result row of a query.

> So I think it is better to leave them out.

At which point you end up back with APSW again - expose SQLite exactly as
it is, rather than pretending it is something different.

> Could we otherwise add a page to the WIKI where to mention tools like
> this?

No one reads the wiki! I don't get any email queries about it either.

Roger
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk7qmNcACgkQmOOfHg372QS9yQCgh4FTaRjYKM8L2ePwdl5Fo9fq
vkgAnRdV7VK3ycN3pvlUZdHZARsIzxwG
=9obH
-----END PGP SIGNATURE-----

[E.Pasma]

On 16 dec 2011, at 02:03, Roger Binns has written:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 15/12/11 16:25, E.Pasma wrote:

I was afraid apswdbapi2 would be considered a disobedience to the

mission of APSW. I'm relieved.

I have no intention of making APSW dbapi compatible, but I'm happy to make
pieces where it doesn't matter do things the dbapi way.  For example
method names are the same as dbapi2.

Indeed that also keeos an apswdbapi2 tool simple. Only the error handling is not straightforward. At each place where an SQLIte error may occur, this is handled and translated to a DBAPI2 exception. The SQLite errors become either ProgrammingError or DatabaseError (apart from two singletons). For instance:

    def interrupt (self):

        try: apsw.Connection.interrupt (self)

        except apsw.Error as e: raise _maperr (e)

A brutal question is if that could not be facilitated within APSW. What I am after is an extra level in the error hierarchy,  between the top level error and the SQLite errors. This would allow to catch for instance a BusyError as an OperationalError, without changing anything to the error. Apswdbapi2 is then relieved from the error mapping.

  

It is not clear to me yet how you can use slots here. As you say, you

need a class definition to map the column names to the slots. This

class differs per query and I do not see when/where to define it.

Possibly once in a pre-fetch stage?

It would be best defined on demand on the first result row of a query.

Then you need a reference to the current row class, probably as a cursor attribute.  And if that would be reset before each new query, then things become easy. 

 

 

So I think it is better to leave them out.

At which point you end up back with APSW again - expose SQLite exactly as
it is, rather than pretending it is something different.

I changed my mind. Also because of a test that I envisage. That is to run the Pysqlite test suite with apswdbapi2 as target. Having these attribute will improve the score.

Could we otherwise add a page to the WIKI where to mention tools like

this?

No one reads the wiki!  I don't get any email queries about it either.

The page "Setting up a Windows 7 64 bit build environment" must become relevant though.

-- Edzard Pasma

[Roger Binns]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 16/12/11 05:43, E.Pasma wrote:
> What I am after is an extra level in the error hierarchy, between the
> top level error and the SQLite errors. This would allow to catch for
> instance a BusyError as an OperationalError,

Let me think about it.

http://code.google.com/p/apsw/issues/detail?id=125

> Then you need a reference to the current row class, probably as a
> cursor attribute. And if that would be reset before each new query,
> then things become easy.

This is trivial to do in the C code. Or by changing the row tracer api.
More thought required.

> The page "/Setting up a Windows 7 64 bit build environment" /must
> become relevant though.

That is actually for my benefit. It was ridiculously difficult to get
everything working correctly.

Roger
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk7tVLAACgkQmOOfHg372QT+TgCgwKoS2mI8ko/C/p5CGIgqxzux
50gAnjYeJJGoAcQr7ITzpfLNF565P3gV
=WfMn
-----END PGP SIGNATURE-----