[issue28518] execute("begin immediate") throwing OperationalError

Changes by Donald Stufft : -- pull_requests: +976 ___ Python tracker ___ ___

[issue28518] execute("begin immediate") throwing OperationalError

Berker Peksag added the comment: New changeset 76995cab69d5ef83d31d8a5754cbad11be4038cb by Berker Peksag in branch '3.6': bpo-28518: Start a transaction implicitly before a DML statement (#245) (#318) https://github.com/python/cpython/commit/76995cab69d5ef83d31d8a5754cbad11be4038cb

[issue28518] execute("begin immediate") throwing OperationalError

Berker Peksag added the comment: New changeset 4a926caf8e5fd8af771b2c34bfb6e91c732331fe by Berker Peksag in branch 'master': bpo-28518: Start a transaction implicitly before a DML statement (#245) https://github.com/python/cpython/commit/4a926caf8e5fd8af771b2c34bfb6e91c732331fe --

[issue28518] execute("begin immediate") throwing OperationalError

Changes by Larry Hastings : -- pull_requests: +608 ___ Python tracker ___ ___

[issue28518] execute("begin immediate") throwing OperationalError

Berker Peksag added the comment: Thank you everyone! This should be fixed now. -- resolution: -> fixed stage: patch review -> resolved status: open -> closed ___ Python tracker

[issue28518] execute("begin immediate") throwing OperationalError

Changes by Berker Peksag : -- pull_requests: +279 ___ Python tracker ___ ___

[issue28518] execute("begin immediate") throwing OperationalError

Changes by Berker Peksag : -- pull_requests: +210 ___ Python tracker ___ ___

[issue28518] execute("begin immediate") throwing OperationalError

Ma Lin added the comment: Let me give a summary. Before 3.6.0 (before 284676cf2ac8): implicit begin: st in (INSERT, UPDATE, DELETE, REPLACE) implicit commit: st not in (SELECT, INSERT, UPDATE, DELETE, REPLACE) In 3.6.0 (after 284676cf2ac8): implicit begin: (not sqlite3_stmt_readonly(st)) and

[issue28518] execute("begin immediate") throwing OperationalError

Ma Lin added the comment: You can set isolation_level = None in sqlite3.connect() parameters, then sqlite3 module will not begin a transaction implicitly. Another way is disabling auto-begin-transaction temporarily: sql.isolation_level = None sql.execute('VACUUM') sql.isolation_level = '' #

[issue28518] execute("begin immediate") throwing OperationalError

Cédric Bellegarde added the comment: Adding a commit doesn't fix the issue here: sql.commit() sql.execute('VACUUM') And on run: cannot VACUUM from within a transaction -- nosy: +Cédric Bellegarde ___ Python tracker

[issue28518] execute("begin immediate") throwing OperationalError

Aviv Palivoda added the comment: Removed opening a transaction on select. I will argue for that in issue 9924 after this is resolved. -- Added file: http://bugs.python.org/file46397/sqlite-ddl-dml-3.patch ___ Python tracker

[issue28518] execute("begin immediate") throwing OperationalError

Berker Peksag added the comment: > 1. Should we add the VACUUM with a explicit commit? Maybe there should > be an implicit commit before VACUUM? VACUUM is often an expensive operation so I think people should need to explicitly handle it anyway. > 2. Should a SELECT start a transaction? I

[issue28518] execute("begin immediate") throwing OperationalError

Ma Lin added the comment: If the answer is (no, no) , the behavior strictly follows the doc changes in commit 284676cf2ac8. Anyway, I'm not a deep user of SQLite, I can't give further advices. :( -- ___ Python tracker

[issue28518] execute("begin immediate") throwing OperationalError

Aviv Palivoda added the comment: Uploading a new patch with fixes from Ma Lin comments. Two points: 1. Should we add the VACUUM with a explicit commit? Maybe there should be an implicit commit before VACUUM? 2. Should a SELECT start a transaction? I think it should according to PEP 249. There

[issue28518] execute("begin immediate") throwing OperationalError

Ma Lin added the comment: some comments on Aviv Palivoda's patch. -- ___ Python tracker ___ ___

[issue28518] execute("begin immediate") throwing OperationalError

Aviv Palivoda added the comment: Yes. If a transaction is open you will need to explicitly commit before doing vacuum. I am not sure if that was intentional or not. From quick look in the sqlite source code there are few things that cannot happen from a transaction: 1. VACUUM 2. ATTACH 3.

[issue28518] execute("begin immediate") throwing OperationalError

Ma Lin added the comment: I'm trying to catch up with you. Does it mean, if I want execute VACUUM, I have to commit manually? Like this: if conn.in_transaction: conn.commit() conn.execute("vacuum") -- ___ Python tracker

[issue28518] execute("begin immediate") throwing OperationalError

Aviv Palivoda added the comment: Adding the vacuum test can't actually happen because the change in commit 284676cf2ac8 changed the API and we don't commit before non DML statements. I opened a issue that will clarify the docs (#29121). -- ___

[issue28518] execute("begin immediate") throwing OperationalError

Berker Peksag added the comment: I only did a quick review, but Aviv's latest patch looks reasonable to me. Thanks! And yes, we can add CheckVacuum back if it's not already tested. -- ___ Python tracker

[issue28518] execute("begin immediate") throwing OperationalError

Changes by Marcus : -- nosy: +callidomus ___ Python tracker ___ ___ Python-bugs-list

[issue28518] execute("begin immediate") throwing OperationalError

Ma Lin added the comment: I have no idea about how to fix it, but I would like to suggest that add back this test which was removed in commit 284676cf2ac8 : file: /Lib/sqlite3/test/transactions.py def CheckVacuum(self): self.cur.execute("create table test(i)") self.cur.execute("insert

[issue28518] execute("begin immediate") throwing OperationalError

Aviv Palivoda added the comment: Issue #29003 seems to be related to this one. I think that they can be solved the same way as done in Serhiy patch but I would like to suggest a different approach. I suggest changing the check for DDL statement with a check for DML statement. We actually

[issue28518] execute("begin immediate") throwing OperationalError

Serhiy Storchaka added the comment: Here is a little simplified version. No need to handle a single "begin". -- Added file: http://bugs.python.org/file45923/sqlite-explicit-begin2.patch ___ Python tracker

[issue28518] execute("begin immediate") throwing OperationalError

Aviv Palivoda added the comment: I think that adding Serhiy patch in addition to the documentation improvement in issue #8145 would be the best way to proceed. This will insure no regression problems. We can add a warning when someone try to use BEGIN, ROLLBACK, SAVEPOINT, and RELEASE without

[issue28518] execute("begin immediate") throwing OperationalError

Ned Deily added the comment: Thanks everyone for investigating this, in particular Big Stone for the reference to the SQLite project and especially Richard Hipp for once again responding quickly to issues we've brought up. While it would be good to get this resolved soon one way or another

[issue28518] execute("begin immediate") throwing OperationalError

Big Stone added the comment: rewording of sqlite documentation: http://www.sqlite.org/src/info/a4205a83e4ed977a -- ___ Python tracker ___

[issue28518] execute("begin immediate") throwing OperationalError

Big Stone added the comment: Comment kindly provided by D. Richard Hipp himself: " I don't have a login for the python bug tracker so I cannot comment there. But I think I see the problem. This is as Aviv Polivoda remarks at https://bugs.python.org/issue28518#msg279808 I think this is a

[issue28518] execute("begin immediate") throwing OperationalError

Serhiy Storchaka added the comment: Here is a patch that allows executing "begin immediate" explicitly with any isolation_level. I don't know what behavior is more preferable. -- Added file: http://bugs.python.org/file45644/sqlite-explicit-begin.patch

[issue28518] execute("begin immediate") throwing OperationalError

Aviv Palivoda added the comment: There are already a few test's that start a transaction explicitly: In dbapi.SqliteOnConflictTests CheckOnConflictRollbackWithExplicitTransaction, CheckOnConflictAbortRaisesWithExplicitTransactions which set ``isolation_level = None``. On the other hand in

[issue28518] execute("begin immediate") throwing OperationalError

Berker Peksag added the comment: There is a patch in issue 8145 to improve that part of the documentation. It would be great if you could take a look at it :) -- ___ Python tracker

[issue28518] execute("begin immediate") throwing OperationalError

Florian Schulze added the comment: Ok, I reread https://docs.python.org/3/library/sqlite3.html#controlling-transactions several times now. I think it's confusing. I want explicit transaction handling, but ``isolation_level = None`` turns on "autocommit mode". Unfortunately I don't have

[issue28518] execute("begin immediate") throwing OperationalError

Berker Peksag added the comment: I haven't had a time to investigate this yet, but I don't think there is an issue with SQLite itself. Note that pysqlite behaves the same way since version 2.8.0 and I couldn't find a similar report in their issue tracker. Like Serhiy, I'm also a little

[issue28518] execute("begin immediate") throwing OperationalError

Aviv Palivoda added the comment: I searched in the sqlite tickets from something related to this issue and I couldn't find anything. I wanted to check the sqlite-users mailing list to see if anyone had already reported it but there is a problem in gmane archives. I tried to find a good way to

[issue28518] execute("begin immediate") throwing OperationalError

Ned Deily added the comment: Also, if there is indeed a suspected error in the underlying library, has the issue been reported to the SQLite project? -- ___ Python tracker

[issue28518] execute("begin immediate") throwing OperationalError

Serhiy Storchaka added the comment: Sorry, I'm not experienced with sqlite3, but in what circumstances conn.execute('begin immediate') makes sense? Could you please provide a larger example where it is needed? -- ___ Python tracker

[issue28518] execute("begin immediate") throwing OperationalError

R. David Murray added the comment: I'm going to mark this as a release blocker because it is a regression. Ned may or may not agree. What is needed is for someone to propose a patch. -- nosy: +ned.deily, r.david.murray priority: normal -> release blocker versions: +Python 3.7

[issue28518] execute("begin immediate") throwing OperationalError

Florian Schulze added the comment: How do we proceed from here? The Python 3.6.0rc1 is looming. If this is a bug in sqlite, then we need a workaround. We can't wait for a fix in sqlite, because it's not bundled with Python and without the fix Python 3.6 breaks with older sqlite versions. I'd

[issue28518] execute("begin immediate") throwing OperationalError

Aviv Palivoda added the comment: I looked into this error and I think the problem is the sqlite3_stmt_readonly check in _pysqlite_query_execute (cursor.c line 517): if (self->connection->begin_statement && !sqlite3_stmt_readonly(self->statement->st) && !self->statement->is_ddl) {

[issue28518] execute("begin immediate") throwing OperationalError

Xiang Zhang added the comment: Looks like commit 284676cf2ac8 in #10740 introduces this. Haven't read through the thread yet. -- ___ Python tracker ___

[issue28518] execute("begin immediate") throwing OperationalError

Changes by Jason R. Coombs : -- nosy: +jason.coombs ___ Python tracker ___ ___

[issue28518] execute("begin immediate") throwing OperationalError

Changes by Марк Коренберг : -- nosy: +mmarkk ___ Python tracker ___ ___

[issue28518] execute("begin immediate") throwing OperationalError

Changes by Serhiy Storchaka : -- nosy: +berker.peksag, ghaering, serhiy.storchaka, xiang.zhang ___ Python tracker ___

[issue28518] execute("begin immediate") throwing OperationalError

New submission from Florian Schulze: Using: conn = sqlite3.connect(':memory:', isolation_level='IMMEDIATE') conn.execute('begin immediate') Throws: sqlite3.OperationalError: cannot start a transaction within a transaction This didn't happen in previous versions and the conn.in_transaction