-
-
Notifications
You must be signed in to change notification settings - Fork 29.2k
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
sqlite3.InterfaceError after commit #54722
Comments
With version 2.7 (and 2.7.1rc1), the following sequence (see attached test): c = cursor.execute(' select k from t where k == ?;', (1,)) Traceback (most recent call last):
File "/bugs/sqlite_bug.py", line 22, in <module>
c = cursor.execute(' select k from t where k == ?;', (2,))
sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type. The program works with 2.6.2 |
Also fails with 3.2 as in 2.7 and works in 3.1 as in 2.6. |
The culprit seems to be 'pysqlite_do_all_statements(self, ACTION_RESET, 0)' in pysqlite_connection_commit, which resets all active statements, but subsequent fetch/fetchall seems to trash the sqlite3 state in the statements. Removing the ACTION_RESET seems to bring back old behaviour (if it's the correct fix is, however, beyond me). Slightly modified testprogram that shows more wierdness; output from: c = cursor.execute(' select k from t where k == ?;', (0,))
conn.commit()
print c.fetchall() is:
which is not what I would expect with a primary key... |
Hi, I encountered the same regression with python 2.7. for item in TableA.findall():
TableB.new_item(name=item.name)
connection.commit() As you can see in the result: Inside the for loop, there is a reset, but only one time. So entries returned by "for" are inconsistents as there could be some duplicated results. After studying the code, I understood that this regression is caused by the addition of function call: Removing this line, fixes the problem and bring back the old correct behavior. For the explanation of my case, I understood that: I think, that the first intention of this modification was more to use the following call: But as for using it in rollback, I'm not sure that this behavior is really useful as sqlite3 looks to be able to correctly handle modifications and cursor return values. results = cursor.execute(' select id from first;')
while True:
print "-- One iter --"
results = cursor.fetchmany()
if not results:
break
for result in results:
print str(result)
cursorBis.execute(' delete from first where id == ?;', (3,))
conn.commit() That correctly printed: So my suggestion is to remove in "pysql_connection_commit" the call to : And also eventually to remove in "pysqlite_connection_rollback" the call to : What do you think about it? |
Would be really nice to not have to fix this in ech new release :-) |
Here's a patch that removes the It also adds the sqlite error code to one of the exceptions raised, as the error message is misleading in case the ACTION_RESET is left in (I forgot what sqlite error is actually raised in that case). I've been using this patch for some while now with 2.7.3 and it fixes similar problems as noted in this thread. |
Just a bit more info on the patch. When running stock Python 2.7.4 the attached test script bug-binding_parameter_0.py returns: module: 2.6.0
sqlite: 3.7.9
Archives
Archives/2011
Archives/2012
Traceback (most recent call last):
File "bug-binding_parameter_0.py", line 45, in <module>
cur = dbconn.execute('select uidvalidity from folders where name=?', (folder,))
sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type. The error suggests a misuse of the sqlite3 API, but the actual SQLite error is masked. After altering _sqlite/statement.c to include the SQLite error code (as in the patch), we get: module: 2.6.0
sqlite: 3.7.9
Archives
Archives/2011
Archives/2012
Traceback (most recent call last):
File "bug-binding_parameter_0.py", line 45, in <module>
cur = dbconn.execute('select uidvalidity from folders where name=?', (folder,))
sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type. (sqlite error 21) Error 21 = SQLITE_MISUSE, suggesting something is definitely wrong in the way the SQLite API is used (for this test case). Commenting out the ACTION_RESET all works fine again: module: 2.6.0 |
I believe http://bugs.python.org/issue23129 is a dupe of this. The patch here has been in "patch review" for 9 months. That seems fairly long for something that's a regression that potentially silently produces the wrong data. |
New changeset 81f614dd8136 by Berker Peksag in branch '3.5': New changeset 685f32972c11 by Berker Peksag in branch 'default': |
New changeset 030e100f048a by Berker Peksag in branch '2.7': |
This is now fixed in 2.7 and 3.5+. Thank you all for your patience! |
New changeset dd13098a5dc2 by Benjamin Peterson in branch '2.7': |
New changeset 74a68d86569c by Benjamin Peterson in branch '2.7': |
I cannot reproduce this with Python 3.8, 3.9, nor 3.10 (macOS builds from python.org). Suggesting to close this. |
Closing as fixed. If someone disagrees; please re-open. |
Note: these values reflect the state of the issue at the time it was migrated and might not reflect the current state.
Show more details
GitHub fields:
bugs.python.org fields:
The text was updated successfully, but these errors were encountered: