[issue10740] sqlite3 module breaks transactions and potentially corrupts data

Géry added the comment: @R. David Murray > Please open a new issue for this request. Done here: https://bugs.python.org/issue39457 -- ___ Python tracker ___

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

Géry added the comment: @Aymeric Augustin > While you're there, it would be cool to provide "connection.autocommit = > True" as an API to enable autocommit, because "connection.isolation_level = > None" isn't a good API at all -- it's very obscure and has nothing to do with > isolation

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

R. David Murray added the comment: Please open a new issue for this question. -- ___ Python tracker ___ ___ Python-bugs-list

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

Géry added the comment: With Berker Peksag's patch merged in 2016, in the default non auto-commit mode, sqlite3 implicitly issues a BEGIN statement before any non SELECT statement not already in a transaction, EXCEPT DDL statements, for backwards compatibility reasons: +/* For backwards

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

R. David Murray added the comment: Please open a new issue for this request. -- ___ Python tracker ___ ___

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

Carl George added the comment: While attempting to build a Python 3.6 RPM for RHEL/CentOS 6, I noticed the following warning. *** WARNING: renaming "_sqlite3" since importing it failed: build/lib.linux-i686-3.6-pydebug/_sqlite3.cpython-36dm-i386-linux-gnu.so: undefined symbol:

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

Ma Lin added the comment: #29003 created -- ___ Python tracker ___ ___ Python-bugs-list mailing list

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

R. David Murray added the comment: It is an unexpected change in behavior and is therefore probably a bug. It will work if you set isolation_level=None. Because of that I don't think it is a release critical bug, though one could wish we'd discovered it earlier. Please open a new issue for

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

Ma Lin added the comment: I'm using Python 3.6.0 RC2, I don't know if it's a bug. When I try to run VACUUM command, an exception raised: conn.execute('begin') # <- remove this line get the same result conn.execute('VACUUM') sqlite3.OperationalError: cannot VACUUM from within a transaction

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

Changes by Berker Peksag : -- resolution: -> fixed stage: patch review -> resolved status: open -> closed ___ Python tracker

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

Roundup Robot added the comment: New changeset 284676cf2ac8 by Berker Peksag in branch 'default': Issue #10740: sqlite3 no longer implicitly commit an open transaction before DDL statements https://hg.python.org/cpython/rev/284676cf2ac8 -- nosy: +python-dev

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

Steve Dower added the comment: I'm in favor of merging Berker's patch, once the string comparison is made a little more robust. Right now there's a risk of matching a prefix rather than a whole word, and potentially overrunning the buffer (unless I've forgotten how stricmp works). --

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

Aymeric Augustin added the comment: The latest patch removes the current statement parsing and unexpected implicit commits. It looks good to me. Unfortunately it also introduces a new kind of statement parsing that detects DDL statements and doesn't open a transaction in that case, while it

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

Berker Peksag added the comment: Here is an updated patch. I'd like to get this in before 3.6 beta 1. -- Added file: http://bugs.python.org/file44437/issue10740_upstream_v2.diff ___ Python tracker

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

Berker Peksag added the comment: Here is a patch. It contains the following commits: * https://github.com/ghaering/pysqlite/commit/f254c534948c41c0ceb8cbabf0d4a2f547754739 * https://github.com/ghaering/pysqlite/commit/796b3afe38cfdac5d7d5ec260826b0a596554631 *

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

Changes by Rian Hunter : -- nosy: -rhunter ___ Python tracker ___ ___ Python-bugs-list

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

mike bayer added the comment: @rian - your issue for this is http://bugs.python.org/issue9924. The implicit BEGIN in all cases will probably never be the default but we do need an option for this to be the case, in order to support SERIALIZABLE isolation. --

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

Rian Hunter added the comment: @mike Yes you're right, This bug report is different, my mistake. DB-API may require implicit transactions but pysqlite should open a transaction before *any* non-DDL statement, including "SELECT", not just DML statements. Currently one must issue a dummy DML

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

mike bayer added the comment: @Rian - implicit transactions are part of the DBAPI spec. Looking at the original description, the purpose of this bug is to not *end* the transaction when DDL is received. So there's no solution for "database is locked" here, other than pysqlite's usual

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

Gerhard Häring added the comment: Isn't this issue at least partly about the statement parsing code in the sqlite module? I've fixed this upstream a while ago in https://github.com/ghaering/pysqlite/commit/94eae5002967a51782f36ce9b7b81bba5b4379db Could somebody perhaps bring this to the

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

Changes by Rian Hunter : Added file: http://bugs.python.org/file42251/test_sqlite.py ___ Python tracker ___

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

Changes by Rian Hunter : Removed file: http://bugs.python.org/file42250/test_sqlite.py ___ Python tracker ___

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

Changes by Rian Hunter : Added file: http://bugs.python.org/file42250/test_sqlite.py ___ Python tracker ___

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

Changes by Rian Hunter : Removed file: http://bugs.python.org/file42249/test_sqlite.py ___ Python tracker ___

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

Rian Hunter added the comment: Implicit transactions can also cause mysterious "database is locked" bugs, even when a large timeout is set on the connection. Many, many people are affected by this bug (search the web for "python sqlite database is locked"). The attached code demonstrates this

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

Aymeric Augustin added the comment: Since a better solution seems to be around the corner, I'm withdrawing my proposal. I'm attaching the current state of my patch. It isn't functional. Mostly it proves that my API proposal is very inconvenient to implement in C. That's why I kept it around

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

Jan Hudec added the comment: Oh, I see I misunderstood Gerhard's last commit. So now the problem should be only if there is a DML statement followed by DDL statement and no commit afterwards. Well, that is indeed probably stupid. -- ___ Python

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

Jan Hudec added the comment: While I agree that the current behaviour is a bug (this bug), and one that renders the package unusable for me (I used apsw or different language instead), I unfortunately have to disagree with Gerhard that the change is not a problem. It can be. The implicit

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

R. David Murray added the comment: It's not a backward compatible change, so we'll need a migration strategy if we want to apply this (and I'd certainly like to). -- versions: +Python 3.6 -Python 3.5 ___ Python tracker rep...@bugs.python.org

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

R. David Murray added the comment: Excellent. -- ___ Python tracker rep...@bugs.python.org http://bugs.python.org/issue10740 ___ ___ Python-bugs-list mailing list

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

Gerhard Häring added the comment: Please note that after the mentioned commit, I restored backwards compatibility with commit https://github.com/ghaering/pysqlite/commit/796b3afe38cfdac5d7d5ec260826b0a596554631 Now the only difference is that the implicit commits *before* DDL statements are

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

Aymeric Augustin added the comment: This bug appears to be fixed upstream: https://github.com/ghaering/pysqlite/commit/f254c534948c41c0ceb8cbabf0d4a2f547754739 -- ___ Python tracker rep...@bugs.python.org http://bugs.python.org/issue10740

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

Changes by Gerhard Häring g...@ghaering.de: -- assignee: - ghaering ___ Python tracker rep...@bugs.python.org http://bugs.python.org/issue10740 ___ ___ Python-bugs-list

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

Torsten Landschoff added the comment: Just a heads up that I am still interested in this issue. I started to write up my expectations to the sqlite module wrt. exception handling. It's not finished yet but I attached what I got so far. I hope everybody agrees that those doctests should all

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

Changes by Benjamin Kircher benjamin.kirc...@gmail.com: -- nosy: +bkircher ___ Python tracker rep...@bugs.python.org http://bugs.python.org/issue10740 ___ ___

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

Aymeric Augustin added the comment: Jim, I believe this API decision doesn't affect the patch in a major way. I'll write the rest of the patch and the committer who reviews it will decide. -- ___ Python tracker rep...@bugs.python.org

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

Aymeric Augustin added the comment: The idea was to not take away from what's there already: The sqlite3 module already has a feature to inspect a command and begin or commit automatically. Just stripping that away *removes* a feature that has been available for a long time. I'd rather

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

R. David Murray added the comment: My take is to avoid the problem entirely, and not inflict it to new users, by providing an option to start in autocommit mode and then create transactions only when you want them. If this statement is accurate, the what you are proposing is just a

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

Aymeric Augustin added the comment: If this statement is accurate, the what you are proposing is just a different (presumably clearer) spelling for 'isolation_level = None'? This statement is accurate but it doesn't cover the whole scope of what I'm attempting to fix. I'm also trying to

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

Jim Jewett added the comment: On Jun 14, 2014 4:05 AM, Aymeric Augustin preserving the same behavior by default That is a requirement, because of backwards compatibility. providing more control for users who need a different behavior, for instance people who use SQLite as an application

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

Jim Jewett added the comment: (1) The patch is just to docs, not code, so I'm not entirely sure that I understood it properly. (2) The new semantics are a huge mess to explain. This might be because the old semantics were bad, but the result is the same. I think it would be better to

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

Aymeric Augustin added the comment: Thanks for your feedback! Indeed this is a documentation patch describing what I could implement if a core dev gave the slightest hint that it stands a small chance of getting included. There's no point in writing code that Python core doesn't want. I

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

Jim Jewett added the comment: Removing the existing behavior will almost certainly not be accepted, because of backwards compatibility. Adding new functionality is generally acceptable. Doing that through a new keyword that defaults to the old behavior is fairly common, and generally better

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

Aymeric Augustin added the comment: The problem is super simple: connection = ... cursor = connection.cursor() cursor.execute(SAVEPOINT foo) cursor.execute(ROLLBACK TO SAVEPOINT foo) # will report that the savepoint doesn't exist. Please don't make it look more complicated than it is.

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

Torsten Landschoff added the comment: Hi all, let me warn you first: I have put a lot a effort in writing the patch back then. I moved on since as I don't even know when the improvement will make any difference for me. As so often, trying to contribute to an open source project turned out to

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

Aymeric Augustin added the comment: I'm attaching a documentation patch describing improvements of the transaction management APIs that would address this issue as well as three others. It's preserving the current transaction handling by default for backwards compatibility. It's introducing

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

Antoine Pitrou added the comment: For the record, I think it would be easier to get a patch accepted for this if it didn't add a rather mysterious callback-based API. Which kind of approach would work, though, I don't have any idea about :-) -- stage: - needs patch

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

Jan Hudec added the comment: Ok, David, I see. Anybody who wants to use sqlite seriously in existing releases can use apsw. It is not dbapi2 compliant, but it is complete and behaves like the underlying database. I agree with Antoine and already mentioned I didn't like the current patch. I

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

Jan Hudec added the comment: Mike, David, The bug is that sqlite module issues implicit COMMIT. SQLite never needs this, many programs need to NOT have it and they can't opt out as isolation_level affects implicit begins only. Most programs will do some changes to data after changing the

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

Changes by Jan Hudec b...@ucw.cz: -- versions: +Python 2.7, Python 3.1, Python 3.2, Python 3.3, Python 3.4 ___ Python tracker rep...@bugs.python.org http://bugs.python.org/issue10740 ___

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

Jan Hudec added the comment: This is somewhat borderline between bug and enhancement. The behaviour is described in the documentation and does not violate dbapi2 specification, but at the same time it is a serious misfeature and gratuitous restriction of perfectly good functionality of the

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

R. David Murray added the comment: And our policy is that enhancements can only go in the next release. We cannot change the default behavior in maintenance releases for backward compatibility reasons, and we cannot provide for a requested change in behavior without introducing an API

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

aaugustin added the comment: On the other hand there is absolutely nothing broken on the implicit BEGIN (which is required by dbapi2 specification) nor on the isolation_level property which controls it. Those shouldn't be touched; there is no reason to. Nothing broken... unless one

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

Chris Monsanto added the comment: This issue has been open for 4 years, last update was 2 months ago. Lack of transactional DDL is a big deal for Python programs that use SQLite heavily. We have a patch for Python 3 that applies cleanly and as far as I can tell works fine. I've been using it

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

aaugustin added the comment: Hey -- maintainer of Django's transaction support here. This ticket was brought to my attention again today. As I know a few things about this issue and I see Python core devs asking for input, I'll give my $0.02. The core of this issue is that, **by default**,

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

Chris Monsanto added the comment: Unfortunately, I don't have backwards-compatible proposal to fix this. Trying to account for a bit more syntax will help in the short term but not fix the underlying issue. aaugustin -- the patch by torsen made 3 years ago is backwards compatible. It adds

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

aaugustin added the comment: That patch solves the problem, at the cost of introducing an unwieldy API, operation_needs_transaction_callback. I'm very skeptical of the other API, in_transaction. Other database backends usually provide an autocommit attribute. autocommit is the opposite of

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

Antoine Pitrou added the comment: What is the callback thing for? The only value that's ever passed in the tests is `lambda operation: True`. -- nosy: +pitrou ___ Python tracker rep...@bugs.python.org http://bugs.python.org/issue10740

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

Changes by Florent Xicluna florent.xicl...@gmail.com: -- nosy: +flox ___ Python tracker rep...@bugs.python.org http://bugs.python.org/issue10740 ___ ___ Python-bugs-list

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

Ronny Pfannschmidt added the comment: could we please get the option to opt-out of that behaviour, as a extra connection option maybe with the normal python sqlite bindings its impossible to have database migrations work safely which IMHO makes this a potential data-loss issue --

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

R. David Murray added the comment: Opt out of what behavior? -- ___ Python tracker rep...@bugs.python.org http://bugs.python.org/issue10740 ___ ___ Python-bugs-list

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

Ronny Pfannschmidt added the comment: the sqlite binding deciding how to handle transactions -- ___ Python tracker rep...@bugs.python.org http://bugs.python.org/issue10740 ___

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

R. David Murray added the comment: As noted above you get that by setting isolation_level to None. That feature has always been available. (With isolation_level set to None, the sqlite wrapper module itself never issues any BEGIN statements.) --

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

Ronny Pfannschmidt added the comment: http://hg.python.org/releasing/3.4/file/447c758cdc26/Modules/_sqlite/cursor.c#l789 also i dont see the isolation level being taking into account in other parts of the code -- ___ Python tracker

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

Adam Tomjack added the comment: The proposed patches don't fix the problem. They may well allow DDL in transactions, but that's not the real problem. A database library must *NEVER* implicitly commit or rollback. That's completely insane. import this ... Explicit is better

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

mike bayer added the comment: well Adam, you might also be surprised to see pysqlite not doing very well on the *other* side of the equation either; that is, when it *begins* the transaction. Issue http://bugs.python.org/issue9924 refers to this and like this one, hasn't seen any activity

[issue10740] sqlite3 module breaks transactions and potentially corrupts data

R. David Murray added the comment: On Thu, 09 Jan 2014 20:29:15 +, Adam Tomjack rep...@bugs.python.org wrote: This issue is not an enhancement, it's a bug. One might argue that fixing it will break existing code, but it won't. Any affected code is already broken. It's depending on a