[Django] #28804: mysql backend error: 1193, "Unknown system variable 'transaction_isolation'"
Django
'transaction_isolation'"
-------------------------------------+-------------------------------------
Reporter: Gene Sem | Owner: nobody
Type: | Status: new
Uncategorized |
Component: Database | Version: 2.0
layer (models, ORM) | Keywords: mysqldb, error,
Severity: Release | transaction_isolation, database,
blocker | backend
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
got this error: _mysql_exceptions.OperationalError
(1193, "Unknown system variable 'transaction_isolation'")
OS: Windows, mysql 10.1.28 MariaDB,
The codebase is fully tested and working fine under django 1.11
Python 3.6.3 installed in C:\Py3\
Full listing of error:
{{{
Traceback (most recent call last):
File "C:\Py3\lib\site-packages\django\db\backends\utils.py", line 83, in
_execute
return self.cursor.execute(sql)
File "C:\Py3\lib\site-packages\django\db\backends\mysql\base.py", line
71, in execute
return self.cursor.execute(query, args)
File "C:\Py3\lib\site-packages\MySQLdb\cursors.py", line 250, in execute
self.errorhandler(self, exc, value)
File "C:\Py3\lib\site-packages\MySQLdb\connections.py", line 50, in
defaulterrorhandler
raise errorvalue
File "C:\Py3\lib\site-packages\MySQLdb\cursors.py", line 247, in execute
res = self._query(query)
File "C:\Py3\lib\site-packages\MySQLdb\cursors.py", line 411, in _query
rowcount = self._do_query(q)
File "C:\Py3\lib\site-packages\MySQLdb\cursors.py", line 374, in
_do_query
db.query(q)
File "C:\Py3\lib\site-packages\MySQLdb\connections.py", line 277, in
query
_mysql.connection.query(self, query)
_mysql_exceptions.OperationalError: (1193, "Unknown system variable
'transaction_isolation'")
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "core.py", line 9, in <module>
execute_from_command_line(sys.argv)
File "C:\Py3\lib\site-packages\django\core\management\__init__.py", line
371, in execute_from_command_line
utility.execute()
File "C:\Py3\lib\site-packages\django\core\management\__init__.py", line
365, in execute
self.fetch_command(subcommand).run_from_argv(self.argv)
File "C:\Py3\lib\site-packages\django\core\management\base.py", line
288, in run_from_argv
self.execute(*args, **cmd_options)
File "C:\Py3\lib\site-packages\django\core\management\base.py", line
332, in execute
self.check()
File "C:\Py3\lib\site-packages\django\core\management\base.py", line
364, in check
include_deployment_checks=include_deployment_checks,
File "C:\Py3\lib\site-packages\django\core\management\base.py", line
351, in _run_checks
return checks.run_checks(**kwargs)
File "C:\Py3\lib\site-packages\django\core\checks\registry.py", line 73,
in run_checks
new_errors = check(app_configs=app_configs)
File "C:\Py3\lib\site-packages\django\core\checks\model_checks.py", line
27, in check_all_models
errors.extend(model.check(**kwargs))
File "C:\Py3\lib\site-packages\django\db\models\base.py", line 1200, in
check
errors.extend(cls._check_fields(**kwargs))
File "C:\Py3\lib\site-packages\django\db\models\base.py", line 1272, in
_check_fields
errors.extend(field.check(**kwargs))
File "C:\Py3\lib\site-packages\django\db\models\fields\__init__.py",
line 894, in check
errors = super().check(**kwargs)
File "C:\Py3\lib\site-packages\django\db\models\fields\__init__.py",
line 206, in check
errors.extend(self._check_backend_specific_checks(**kwargs))
File "C:\Py3\lib\site-packages\django\db\models\fields\__init__.py",
line 303, in _check_backend_specific_checks
return connections[db].validation.check_field(self, **kwargs)
File "C:\Py3\lib\site-packages\django\db\backends\base\validation.py",
line 21, in check_field
field_type = field.db_type(self.connection)
File "C:\Py3\lib\site-packages\django\db\models\fields\__init__.py",
line 648, in db_type
return connection.data_types[self.get_internal_type()] % data
File "C:\Py3\lib\site-packages\django\utils\functional.py", line 36, in
__get__
res = instance.__dict__[self.name] = self.func(instance)
File "C:\Py3\lib\site-packages\django\db\backends\mysql\base.py", line
133, in data_types
if self.features.supports_microsecond_precision:
File "C:\Py3\lib\site-packages\django\utils\functional.py", line 36, in
__get__
res = instance.__dict__[self.name] = self.func(instance)
File "C:\Py3\lib\site-packages\django\db\backends\mysql\features.py",
line 65, in supports_microsecond_precision
return self.connection.mysql_version >= (5, 6, 4)
File "C:\Py3\lib\site-packages\django\utils\functional.py", line 36, in
__get__
res = instance.__dict__[self.name] = self.func(instance)
File "C:\Py3\lib\site-packages\django\db\backends\mysql\base.py", line
352, in mysql_version
with self.temporary_connection() as cursor:
File "C:\Py3\lib\contextlib.py", line 81, in __enter__
return next(self.gen)
File "C:\Py3\lib\site-packages\django\db\backends\base\base.py", line
576, in temporary_connection
cursor = self.cursor()
File "C:\Py3\lib\site-packages\django\db\backends\base\base.py", line
255, in cursor
return self._cursor()
File "C:\Py3\lib\site-packages\django\db\backends\base\base.py", line
232, in _cursor
self.ensure_connection()
File "C:\Py3\lib\site-packages\django\db\backends\base\base.py", line
216, in ensure_connection
self.connect()
File "C:\Py3\lib\site-packages\django\db\backends\base\base.py", line
196, in connect
self.init_connection_state()
File "C:\Py3\lib\site-packages\django\db\backends\mysql\base.py", line
259, in init_connection_state
cursor.execute('SET ' + ', '.join(assignments))
File "C:\Py3\lib\site-packages\django\db\backends\utils.py", line 100,
in execute
return super().execute(sql, params)
File "C:\Py3\lib\site-packages\django\db\backends\utils.py", line 68, in
execute
return self._execute_with_wrappers(sql, params, many=False,
executor=self._execute)
File "C:\Py3\lib\site-packages\django\db\backends\utils.py", line 77, in
_execute_with_wrappers
return executor(sql, params, many, context)
File "C:\Py3\lib\site-packages\django\db\backends\utils.py", line 85, in
_execute
return self.cursor.execute(sql, params)
File "C:\Py3\lib\site-packages\django\db\utils.py", line 89, in __exit__
raise dj_exc_value.with_traceback(traceback) from exc_value
File "C:\Py3\lib\site-packages\django\db\backends\utils.py", line 83, in
_execute
return self.cursor.execute(sql)
File "C:\Py3\lib\site-packages\django\db\backends\mysql\base.py", line
71, in execute
return self.cursor.execute(query, args)
File "C:\Py3\lib\site-packages\MySQLdb\cursors.py", line 250, in execute
self.errorhandler(self, exc, value)
File "C:\Py3\lib\site-packages\MySQLdb\connections.py", line 50, in
defaulterrorhandler
raise errorvalue
File "C:\Py3\lib\site-packages\MySQLdb\cursors.py", line 247, in execute
res = self._query(query)
File "C:\Py3\lib\site-packages\MySQLdb\cursors.py", line 411, in _query
rowcount = self._do_query(q)
File "C:\Py3\lib\site-packages\MySQLdb\cursors.py", line 374, in
_do_query
db.query(q)
File "C:\Py3\lib\site-packages\MySQLdb\connections.py", line 277, in
query
_mysql.connection.query(self, query)
django.db.utils.OperationalError: (1193, "Unknown system variable
'transaction_isolation'")
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/28804>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
'transaction_isolation'"
-------------------------------------+-------------------------------------
Reporter: Gene Sem | Owner: nobody
Component: Database layer | Version: 2.0
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: mysqldb, error, | Triage Stage:
transaction_isolation, database, | Unreviewed
backend |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* type: Uncategorized => Bug
--
Ticket URL: <https://code.djangoproject.com/ticket/28804#comment:1>
Django
'transaction_isolation'"
-------------------------------------+-------------------------------------
Reporter: Gene Sem | Owner: nobody
Component: Database layer | Version: 2.0
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: mysqldb, error, | Triage Stage:
transaction_isolation, database, | Unreviewed
backend |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Gene Sem):
This bug dont exist in Django 2.0b1.
--
Ticket URL: <https://code.djangoproject.com/ticket/28804#comment:2>
Django
'transaction_isolation'"
-------------------------------------+-------------------------------------
Reporter: Gene Sem | Owner: nobody
Component: Database layer | Version: 2.0
(models, ORM) |
Severity: Release blocker | Resolution:
transaction_isolation, database, |
backend |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: Adam (Chainz) Johnson (added)
* stage: Unreviewed => Accepted
Comment:
The fix from #28794 fixes a deprecation warning in MySQL 5.7. It seems
that MariaDB didn't make a similar change, or at least the version you're
using didn't. Django doesn't officially support MariaDB but it likely
practical to add a workaround here since it otherwise mostly works.
--
Ticket URL: <https://code.djangoproject.com/ticket/28804#comment:3>
Django
'transaction_isolation'"
-------------------------------------+-------------------------------------
Reporter: Gene Sem | Owner: nobody
Component: Database layer | Version: 2.0
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: mysqldb, error, | Triage Stage: Accepted
transaction_isolation, database, |
backend |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Gene Sem):
The bug exists with MariaDB versions: 10.0.31, 10.0.33 (latest), 10.1.28,
10.1.29 (latest).
--
Ticket URL: <https://code.djangoproject.com/ticket/28804#comment:4>
Django
'transaction_isolation'"
-------------------------------------+-------------------------------------
Reporter: Gene Sem | Owner: nobody
Component: Database layer | Version: 2.0
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: mysqldb, error, | Triage Stage: Accepted
transaction_isolation, database, |
backend |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Gene Sem):
Where error is located:
WORKING file `django/db/backends/mysql/base.py` line 241 (django 2.0b1):
{{{
def init_connection_state(self):
assignments = []
if self.features.is_sql_auto_is_null_enabled:
# SQL_AUTO_IS_NULL controls whether an AUTO_INCREMENT column
on
# a recently inserted row will return when the field is tested
# for NULL. Disabling this brings this aspect of MySQL in line
# with SQL standards.
assignments.append('SQL_AUTO_IS_NULL = 0')
if self.isolation_level:
assignments.append("TX_ISOLATION = '%s'" %
self.isolation_level)
}}}
DONT WORKING file `django/db/backends/mysql/base.py` line 238 (django2.0
rc1):
{{{
def init_connection_state(self):
assignments = []
if self.features.is_sql_auto_is_null_enabled:
# SQL_AUTO_IS_NULL controls whether an AUTO_INCREMENT column
on
# a recently inserted row will return when the field is tested
# for NULL. Disabling this brings this aspect of MySQL in line
# with SQL standards.
assignments.append('SQL_AUTO_IS_NULL = 0')
if self.isolation_level:
err-> assignments.append("%s = '%s'" %
(self.transaction_isolation_variable, self.isolation_level))
}}}
....
where `self.transaction_isolation_variable`:
{{{
@cached_property
def transaction_isolation_variable(self):
return 'tx_isolation' if self.mysql_version < (5, 7, 20) else
'transaction_isolation'
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/28804#comment:5>
Django
'transaction_isolation'"
-------------------------------------+-------------------------------------
Reporter: Gene Sem | Owner: nobody
Component: Database layer | Version: 2.0
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: mysqldb, error, | Triage Stage: Accepted
transaction_isolation, database, |
backend |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Gene Sem):
After some debugging with MariaDB:
Function `mysql_version()` returned value is: 10.0.31 for MariaDB 10.0.31,
but actually it is equivalent of original mySQL 5.7.0
So `transaction_isolation_variable()` should return `'tx_isolation'`
but returning `'transaction_isolation'` which is error value for mariadb.
--
Ticket URL: <https://code.djangoproject.com/ticket/28804#comment:6>
Django
'transaction_isolation'"
-------------------------------------+-------------------------------------
Reporter: Gene Sem | Owner: nobody
Component: Database layer | Version: 2.0
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: mysqldb, error, | Triage Stage: Accepted
transaction_isolation, database, |
backend |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Adam (Chainz) Johnson):
We can use the `SET TRANSACTION ISOLATION LEVEL` statement rather than the
variable
https://mariadb.com/kb/en/library/set-transaction/
https://dev.mysql.com/doc/refman/5.5/en/set-transaction.html
--
Ticket URL: <https://code.djangoproject.com/ticket/28804#comment:7>
Django
'transaction_isolation'"
-------------------------------------+-------------------------------------
Reporter: Gene Sem | Owner: nobody
Component: Database layer | Version: 2.0
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: mysqldb, error, | Triage Stage: Accepted
transaction_isolation, database, |
backend |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: Sergey Fedoseev (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/28804#comment:8>
Django
'transaction_isolation'"
-------------------------------------+-------------------------------------
Reporter: Gene Sem | Owner: nobody
Component: Database layer | Version: 2.0
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: mysqldb, error, | Triage Stage: Accepted
transaction_isolation, database, |
backend |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Tim Graham):
As far as I see, we'd still need transaction_isolation/tx_isolation for
the test which queries `SELECT @@transaction_isolation`. Did I miss
something?
--
Ticket URL: <https://code.djangoproject.com/ticket/28804#comment:9>
Django
'transaction_isolation'"
-------------------------------------+-------------------------------------
Reporter: Gene Sem | Owner: nobody
Component: Database layer | Version: 2.0
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: mysqldb, error, | Triage Stage: Accepted
transaction_isolation, database, |
backend |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Sergey Fedoseev):
Replying to [comment:9 Tim Graham]:
> As far as I see, we'd still need transaction_isolation/tx_isolation for
the test which queries `SELECT @@transaction_isolation`. Did I miss
something?
What's was the reason why I didn't use `SET TRANSACTION ISOLATION LEVEL`,
but do we really need to test it?
--
Ticket URL: <https://code.djangoproject.com/ticket/28804#comment:10>
Django
'transaction_isolation'"
-------------------------------------+-------------------------------------
Reporter: Gene Sem | Owner: nobody
Component: Database layer | Version: 2.0
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: mysqldb, error, | Triage Stage: Accepted
transaction_isolation, database, |
backend |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Adam (Chainz) Johnson):
We can test with `show variables like 't%_isolation';`, which on MySQL 5.7
will show:
{{{
+-----------------------+-----------------+
| Variable_name | Value |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
| tx_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
2 rows in set (0.01 sec)
}}}
And on earlier MySQL / MariaDB versions will return just the
`tx_isolation` row.
--
Ticket URL: <https://code.djangoproject.com/ticket/28804#comment:11>
Django
'transaction_isolation'"
-------------------------------------+-------------------------------------
Reporter: Gene Sem | Owner: nobody
Component: Database layer | Version: 2.0
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: mysqldb, error, | Triage Stage: Accepted
transaction_isolation, database, |
backend |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* has_patch: 0 => 1
Comment:
[https://github.com/django/django/pull/9364 PR]
--
Ticket URL: <https://code.djangoproject.com/ticket/28804#comment:12>
Django
'transaction_isolation'"
-------------------------------------+-------------------------------------
Reporter: Gene Sem | Owner: nobody
Component: Database layer | Version: 2.0
(models, ORM) |
Severity: Release blocker | Resolution:
transaction_isolation, database, | checkin
backend |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* stage: Accepted => Ready for checkin
--
Ticket URL: <https://code.djangoproject.com/ticket/28804#comment:13>
Django
'transaction_isolation'"
-------------------------------------+-------------------------------------
Reporter: Gene Sem | Owner: nobody
Component: Database layer | Version: 2.0
(models, ORM) |
Keywords: mysqldb, error, | Triage Stage: Ready for
transaction_isolation, database, | checkin
backend |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => fixed
Comment:
In [changeset:"e3c852cbd609484b272f563f3c21066fb12ef7f8" e3c852cb]:
{{{
#!CommitTicketReference repository=""
revision="e3c852cbd609484b272f563f3c21066fb12ef7f8"
Fixed #28804 -- Fixed "Unknown system variable 'transaction_isolation'" on
MariaDB.
Regression in 967450a3bf940c43db891fe1e2ef3bcf73456ff8.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/28804#comment:14>
Django
'transaction_isolation'"
-------------------------------------+-------------------------------------
Reporter: Gene Sem | Owner: nobody
Component: Database layer | Version: 2.0
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: mysqldb, error, | Triage Stage: Ready for
transaction_isolation, database, | checkin
backend |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Tim Graham <timograham@…>):
In [changeset:"205290f5105e429a84c299f2fc19124df838f3c0" 205290f5]:
{{{
#!CommitTicketReference repository=""
revision="205290f5105e429a84c299f2fc19124df838f3c0"
[2.0.x] Fixed #28804 -- Fixed "Unknown system variable
'transaction_isolation'" on MariaDB.
Regression in 967450a3bf940c43db891fe1e2ef3bcf73456ff8.
Backport of e3c852cbd609484b272f563f3c21066fb12ef7f8 from master
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/28804#comment:15>