MariaDB compatibility broken: "Unknown system variable 'transaction_isolation'"

[Gene Sem]

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'")

[Gene Sem]

This bug dont exist in Django 2.0b1.

[Tim Graham]

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.

[Gene Sem]

The bug exists with MariaDB versions: 10.0.31, 10.0.33 (latest), 10.1.28, 10.1.29 (latest).

[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'

[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.

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'

[Adam 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

[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?

[Sergey Fedoseev]

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?

[Adam 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.

[Tim Graham]

​PR

[GitHub <noreply@…>]

In e3c852cb:

Fixed #28804 -- Fixed "Unknown system variable 'transaction_isolation'" on MariaDB.

Regression in 967450a3bf940c43db891fe1e2ef3bcf73456ff8.

[Tim Graham <timograham@…>]

In 205290f5:

[2.0.x] Fixed #28804 -- Fixed "Unknown system variable 'transaction_isolation'" on MariaDB.

Regression in 967450a3bf940c43db891fe1e2ef3bcf73456ff8.

Backport of e3c852cbd609484b272f563f3c21066fb12ef7f8 from master