#31632: Single queries without transaction do not follow AUTOCOMMIT
-------------------------------------+-------------------------------------
Reporter: Filipe Pina | Owner: nobody
Type: Uncategorized | Status: new
Component: Database layer | Version: 2.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Florian Apolloner):
Tested this:
{{{
#!/usr/bin/env python
import MySQLdb as Database
db1 = Database.connect(
host="127.0.0.1",
user="root",
charset="utf8",
db="mysql",
client_flag=2,
autocommit=True,
)
print(db1.get_autocommit())
db1.autocommit(False)
print(db1.get_autocommit())
db1.autocommit(True)
print(db1.get_autocommit())
db1.autocommit(False)
print(db1.get_autocommit())
}}}
against MySQL 8, it always prints `True` and the query log is like YOLO:
{{{
2020-05-28T15:07:43.224129Z 14 Connect root@127.0.0.1 on mysql
using TCP/IP
2020-05-28T15:07:43.224440Z 14 Query SET autocommit=0
2020-05-28T15:07:43.224629Z 14 Query SET autocommit=0
2020-05-28T15:07:43.225759Z 14 Quit
}}}
Related bug reports for MySQL:
* https://bugs.mysql.com/bug.php?id=66884
* https://bugs.mysql.com/bug.php?id=87813
And the code in mysqlclient which exhibits this behavior:
https://github.com/PyMySQL/mysqlclient-
python/blob/4c1950148ba10e23017bd5190f9da6bdb347e6b3/MySQLdb/_mysql.c#L755-L765
Django generally works (that is if the global autocommit is not set to
off) since it manually tracks autocommit and as such is not affected by
this bug.
mysql-connector-python exhibits the same broken behavior.
This fixes it for Django (don't ask me if that would be sane to include):
{{{
diff --git a/django/db/backends/mysql/base.py
b/django/db/backends/mysql/base.py
index eb0ba18cda..6fd4364cd6 100644
--- a/django/db/backends/mysql/base.py
+++ b/django/db/backends/mysql/base.py
@@ -260,8 +260,8 @@ class DatabaseWrapper(BaseDatabaseWrapper):
pass
def _set_autocommit(self, autocommit):
- with self.wrap_database_errors:
- self.connection.autocommit(autocommit)
+ with self.cursor() as cursor:
+ cursor.execute('SET autocommit=%s', [1 if autocommit else 0])
def disable_constraint_checking(self):
"""
}}}
And results in:
{{{
2020-05-28T15:16:10.911555Z 22 Connect root@127.0.0.1 on mysql
using TCP/IP
2020-05-28T15:16:10.911893Z 22 Query SET autocommit=0
2020-05-28T15:16:10.912164Z 22 Query SET autocommit=1
2020-05-28T15:16:10.912389Z 22 Query SELECT @@SQL_AUTO_IS_NULL
2020-05-28T15:16:10.912568Z 22 Query SET SESSION TRANSACTION
ISOLATION LEVEL READ COMMITTED
2020-05-28T15:16:10.912818Z 22 Query SET autocommit=0
2020-05-28T15:16:10.920255Z 22 Query SELECT VERSION()
2020-05-28T15:16:10.920560Z 22 Query INSERT INTO
`polls_notapoll` (`text`) VALUES (1)
2020-05-28T15:16:10.921004Z 22 Query COMMIT
2020-05-28T15:16:10.935950Z 22 Query SET autocommit=1
2020-05-28T15:16:10.936680Z 22 Query UPDATE `polls_notapoll`
SET `text` = 2 WHERE `polls_notapoll`.`id` = 3
2020-05-28T15:16:10.943920Z 22 Query SELECT
`polls_notapoll`.`id`, `polls_notapoll`.`text` FROM `polls_notapoll` WHERE
`polls_notapoll`.`id` = 3 LIMIT 21
2020-05-28T15:16:10.945997Z 22 Quit
2020-05-28T15:16:10.949059Z 23 Connect root@127.0.0.1 on mysql
using TCP/IP
2020-05-28T15:16:10.949937Z 23 Query SET autocommit=0
2020-05-28T15:16:10.950920Z 23 Query SET autocommit=1
2020-05-28T15:16:10.951961Z 23 Query SET SESSION TRANSACTION
ISOLATION LEVEL READ COMMITTED
2020-05-28T15:16:10.952983Z 23 Query SELECT
`polls_notapoll`.`id`, `polls_notapoll`.`text` FROM `polls_notapoll` WHERE
`polls_notapoll`.`id` = 3 LIMIT 21
2020-05-28T15:16:10.986958Z 23 Quit
}}}
The overhead of `set autocommit=0` is still there for every connection
because mysqldb does this to be dbapi compliant. One could remove this by
adding `autocommit=None` to the `OPTIONS` dict.
--
Ticket URL: <https://code.djangoproject.com/ticket/31632#comment:10>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/064.5339b73ad2936763f1ac6bf75a9ee653%40djangoproject.com.
