#28804 closed Bug (fixed)
MariaDB compatibility broken: "Unknown system variable 'transaction_isolation'"
Reported by: | Gene Sem | Owned by: | nobody |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | 2.0 |
Severity: | Release blocker | Keywords: | mysqldb, error, transaction_isolation, database, backend |
Cc: | Adam Johnson, Sergey Fedoseev | Triage Stage: | Ready for checkin |
Has patch: | yes | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
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'")
Change History (15)
comment:1 by , 7 years ago
Type: | Uncategorized → Bug |
---|
comment:2 by , 7 years ago
comment:3 by , 7 years ago
Cc: | added |
---|---|
Summary: | mysql backend error: 1193, "Unknown system variable 'transaction_isolation'" → MariaDB compatibility broken: "Unknown system variable 'transaction_isolation'" |
Triage Stage: | Unreviewed → Accepted |
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.
comment:4 by , 7 years ago
The bug exists with MariaDB versions: 10.0.31, 10.0.33 (latest), 10.1.28, 10.1.29 (latest).
comment:5 by , 7 years ago
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'
comment:6 by , 7 years ago
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.
Dirty workaround:
File django/db/backends/mysql/base.py
lines 234-236
@cached_property def transaction_isolation_variable(self): return 'tx_isolation' if self.mysql_version < (5, 7, 20) or self.mysql_version > (10, 0, 10) else 'transaction_isolation'
comment:7 by , 7 years ago
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
comment:8 by , 7 years ago
Cc: | added |
---|
follow-up: 10 comment:9 by , 7 years ago
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?
comment:10 by , 7 years ago
Replying to 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?
That's was the reason why I didn't use SET TRANSACTION ISOLATION LEVEL
, but do we really need to test it?
comment:11 by , 7 years ago
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.
comment:13 by , 7 years ago
Triage Stage: | Accepted → Ready for checkin |
---|
This bug dont exist in Django 2.0b1.