Project

General

Profile

Bug #9411

SQL syntax errors for MariaDB

Added by Vadim Mutilin 5 months ago. Updated 3 months ago.

Status:
New
Priority:
Urgent
Category:
Bridge
Target version:
-
Start date:
12/11/2018
Due date:
% Done:

0%

Estimated time:
Detected in build:
svn
Platform:
Published in build:

Description

We highly need support for MariaDB and previously it was supported.
In Klever 2.0 we have the following exceptions

(1064, 'You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near \'."id", "report_attrs"."attr_id" as "a_id",\n       "attr"."value" as "a_val", "at\' at line 1')
Traceback (most recent call last):
  File "/usr/local/lib/python3.5/dist-packages/django/db/backends/utils.py", line 85, in _execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python3.5/dist-packages/django/db/backends/mysql/base.py", line 71, in execute
    return self.cursor.execute(query, args)
  File "/usr/local/lib/python3.5/dist-packages/MySQLdb/cursors.py", line 255, in execute
    self.errorhandler(self, exc, value)
  File "/usr/local/lib/python3.5/dist-packages/MySQLdb/connections.py", line 50, in defaulterrorhandler
    raise errorvalue
  File "/usr/local/lib/python3.5/dist-packages/MySQLdb/cursors.py", line 252, in execute
    res = self._query(query)
  File "/usr/local/lib/python3.5/dist-packages/MySQLdb/cursors.py", line 378, in _query
    db.query(q)
  File "/usr/local/lib/python3.5/dist-packages/MySQLdb/connections.py", line 280, in query
    _mysql.connection.query(self, query)
_mysql_exceptions.ProgrammingError: (1064, 'You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near \'."id", "report_attrs"."attr_id" as "a_id",\n       "attr"."value" as "a_val", "at\' at line 1')
The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/debian/klever/bridge/jobs/ViewJobData.py", line 360, in __safes_attrs_statistic
    return self.__attr_statistic('safe')
  File "/home/debian/klever/bridge/jobs/ViewJobData.py", line 401, in __attr_statistic
    for ra in queryset:
  File "/usr/local/lib/python3.5/dist-packages/django/db/models/query.py", line 1339, in __iter__
    self._fetch_all()
  File "/usr/local/lib/python3.5/dist-packages/django/db/models/query.py", line 1326, in _fetch_all
    self._result_cache = list(self.iterator())
  File "/usr/local/lib/python3.5/dist-packages/django/db/models/query.py", line 1349, in iterator
    query = iter(self.query)
  File "/usr/local/lib/python3.5/dist-packages/django/db/models/sql/query.py", line 96, in __iter__
    self._execute_query()
  File "/usr/local/lib/python3.5/dist-packages/django/db/models/sql/query.py", line 130, in _execute_query
    self.cursor.execute(self.sql, params)
  File "/usr/local/lib/python3.5/dist-packages/django/db/backends/utils.py", line 100, in execute
    return super().execute(sql, params)
  File "/home/debian/klever/bridge/bridge/__init__.py", line 32, in execute_wrapper
    return original(*args, **kwargs)
  File "/usr/local/lib/python3.5/dist-packages/django/db/backends/utils.py", line 68, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/usr/local/lib/python3.5/dist-packages/django/db/backends/utils.py", line 77, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/usr/local/lib/python3.5/dist-packages/django/db/backends/utils.py", line 85, in _execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python3.5/dist-packages/django/db/utils.py", line 89, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/usr/local/lib/python3.5/dist-packages/django/db/backends/utils.py", line 85, in _execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python3.5/dist-packages/django/db/backends/mysql/base.py", line 71, in execute
    return self.cursor.execute(query, args)
  File "/usr/local/lib/python3.5/dist-packages/MySQLdb/cursors.py", line 255, in execute
    self.errorhandler(self, exc, value)
  File "/usr/local/lib/python3.5/dist-packages/MySQLdb/connections.py", line 50, in defaulterrorhandler
    raise errorvalue
  File "/usr/local/lib/python3.5/dist-packages/MySQLdb/cursors.py", line 252, in execute
    res = self._query(query)
  File "/usr/local/lib/python3.5/dist-packages/MySQLdb/cursors.py", line 378, in _query
    db.query(q)
  File "/usr/local/lib/python3.5/dist-packages/MySQLdb/connections.py", line 280, in query
    _mysql.connection.query(self, query)
django.db.utils.ProgrammingError: (1064, 'You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near \'."id", "report_attrs"."attr_id" as "a_id",\n       "attr"."value" as "a_val", "at\' at line 1')
(1064, 'You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near \'."id", "report_attrs"."attr_id" as "a_id",\n       "attr"."value" as "a_val", "at\' at line 1')
Traceback (most recent call last):
  File "/usr/local/lib/python3.5/dist-packages/django/db/backends/utils.py", line 85, in _execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python3.5/dist-packages/django/db/backends/mysql/base.py", line 71, in execute
    return self.cursor.execute(query, args)
  File "/usr/local/lib/python3.5/dist-packages/MySQLdb/cursors.py", line 255, in execute
    self.errorhandler(self, exc, value)
  File "/usr/local/lib/python3.5/dist-packages/MySQLdb/connections.py", line 50, in defaulterrorhandler
    raise errorvalue
  File "/usr/local/lib/python3.5/dist-packages/MySQLdb/cursors.py", line 252, in execute
    res = self._query(query)
  File "/usr/local/lib/python3.5/dist-packages/MySQLdb/cursors.py", line 378, in _query
    db.query(q)
  File "/usr/local/lib/python3.5/dist-packages/MySQLdb/connections.py", line 280, in query
    _mysql.connection.query(self, query)
_mysql_exceptions.ProgrammingError: (1064, 'You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near \'."id", "report_attrs"."attr_id" as "a_id",\n       "attr"."value" as "a_val", "at\' at line 1')

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/debian/klever/bridge/jobs/ViewJobData.py", line 367, in __unsafes_attrs_statistic
    return self.__attr_statistic('unsafe')
  File "/home/debian/klever/bridge/jobs/ViewJobData.py", line 401, in __attr_statistic
    for ra in queryset:
  File "/usr/local/lib/python3.5/dist-packages/django/db/models/query.py", line 1339, in __iter__
    self._fetch_all()
  File "/usr/local/lib/python3.5/dist-packages/django/db/models/query.py", line 1326, in _fetch_all
    self._result_cache = list(self.iterator())
  File "/usr/local/lib/python3.5/dist-packages/django/db/models/query.py", line 1349, in iterator
    query = iter(self.query)
  File "/usr/local/lib/python3.5/dist-packages/django/db/models/sql/query.py", line 96, in __iter__
    self._execute_query()
  File "/usr/local/lib/python3.5/dist-packages/django/db/models/sql/query.py", line 130, in _execute_query
    self.cursor.execute(self.sql, params)
  File "/usr/local/lib/python3.5/dist-packages/django/db/backends/utils.py", line 100, in execute
    return super().execute(sql, params)
  File "/home/debian/klever/bridge/bridge/__init__.py", line 32, in execute_wrapper
    return original(*args, **kwargs)
  File "/usr/local/lib/python3.5/dist-packages/django/db/backends/utils.py", line 68, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/usr/local/lib/python3.5/dist-packages/django/db/backends/utils.py", line 77, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/usr/local/lib/python3.5/dist-packages/django/db/backends/utils.py", line 85, in _execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python3.5/dist-packages/django/db/utils.py", line 89, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/usr/local/lib/python3.5/dist-packages/django/db/backends/utils.py", line 85, in _execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python3.5/dist-packages/django/db/backends/mysql/base.py", line 71, in execute
    return self.cursor.execute(query, args)
  File "/usr/local/lib/python3.5/dist-packages/MySQLdb/cursors.py", line 255, in execute
    self.errorhandler(self, exc, value)
  File "/usr/local/lib/python3.5/dist-packages/MySQLdb/connections.py", line 50, in defaulterrorhandler
    raise errorvalue
  File "/usr/local/lib/python3.5/dist-packages/MySQLdb/cursors.py", line 252, in execute
    res = self._query(query)
  File "/usr/local/lib/python3.5/dist-packages/MySQLdb/cursors.py", line 378, in _query
    db.query(q)
  File "/usr/local/lib/python3.5/dist-packages/MySQLdb/connections.py", line 280, in query
    _mysql.connection.query(self, query)
django.db.utils.ProgrammingError: (1064, 'You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near \'."id", "report_attrs"."attr_id" as "a_id",\n       "attr"."value" as "a_val", "at\' at line 1')
Bad Request: /jobs/5/
Bad Request: /jobs/5/
(1064, 'You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near \'."id", "report_attrs"."attr_id" as "a_id",\n       "attr"."value" as "a_val", "at\' at line 1')
Traceback (most recent call last):
  File "/usr/local/lib/python3.5/dist-packages/django/db/backends/utils.py", line 85, in _execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python3.5/dist-packages/django/db/backends/mysql/base.py", line 71, in execute
    return self.cursor.execute(query, args)
  File "/usr/local/lib/python3.5/dist-packages/MySQLdb/cursors.py", line 255, in execute
    self.errorhandler(self, exc, value)
  File "/usr/local/lib/python3.5/dist-packages/MySQLdb/connections.py", line 50, in defaulterrorhandler
    raise errorvalue
  File "/usr/local/lib/python3.5/dist-packages/MySQLdb/cursors.py", line 252, in execute
    res = self._query(query)
  File "/usr/local/lib/python3.5/dist-packages/MySQLdb/cursors.py", line 378, in _query
    db.query(q)
  File "/usr/local/lib/python3.5/dist-packages/MySQLdb/connections.py", line 280, in query
    _mysql.connection.query(self, query)
_mysql_exceptions.ProgrammingError: (1064, 'You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near \'."id", "report_attrs"."attr_id" as "a_id",\n       "attr"."value" as "a_val", "at\' at line 1')

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/debian/klever/bridge/jobs/ViewJobData.py", line 360, in __safes_attrs_statistic
    return self.__attr_statistic('safe')
  File "/home/debian/klever/bridge/jobs/ViewJobData.py", line 401, in __attr_statistic
    for ra in queryset:
  File "/usr/local/lib/python3.5/dist-packages/django/db/models/query.py", line 1339, in __iter__
    self._fetch_all()
  File "/usr/local/lib/python3.5/dist-packages/django/db/models/query.py", line 1326, in _fetch_all
    self._result_cache = list(self.iterator())
  File "/usr/local/lib/python3.5/dist-packages/django/db/models/query.py", line 1349, in iterator
    query = iter(self.query)
  File "/usr/local/lib/python3.5/dist-packages/django/db/models/sql/query.py", line 96, in __iter__
    self._execute_query()
  File "/usr/local/lib/python3.5/dist-packages/django/db/models/sql/query.py", line 130, in _execute_query
    self.cursor.execute(self.sql, params)
  File "/usr/local/lib/python3.5/dist-packages/django/db/backends/utils.py", line 100, in execute
    return super().execute(sql, params)
  File "/home/debian/klever/bridge/bridge/__init__.py", line 32, in execute_wrapper
    return original(*args, **kwargs)
  File "/usr/local/lib/python3.5/dist-packages/django/db/backends/utils.py", line 68, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/usr/local/lib/python3.5/dist-packages/django/db/backends/utils.py", line 77, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/usr/local/lib/python3.5/dist-packages/django/db/backends/utils.py", line 85, in _execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python3.5/dist-packages/django/db/utils.py", line 89, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/usr/local/lib/python3.5/dist-packages/django/db/backends/utils.py", line 85, in _execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python3.5/dist-packages/django/db/backends/mysql/base.py", line 71, in execute
    return self.cursor.execute(query, args)
  File "/usr/local/lib/python3.5/dist-packages/MySQLdb/cursors.py", line 255, in execute
    self.errorhandler(self, exc, value)
  File "/usr/local/lib/python3.5/dist-packages/MySQLdb/connections.py", line 50, in defaulterrorhandler
    raise errorvalue
  File "/usr/local/lib/python3.5/dist-packages/MySQLdb/cursors.py", line 252, in execute
    res = self._query(query)
  File "/usr/local/lib/python3.5/dist-packages/MySQLdb/cursors.py", line 378, in _query
    db.query(q)
  File "/usr/local/lib/python3.5/dist-packages/MySQLdb/connections.py", line 280, in query
    _mysql.connection.query(self, query)
django.db.utils.ProgrammingError: (1064, 'You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near \'."id", "report_attrs"."attr_id" as "a_id",\n       "attr"."value" as "a_val", "at\' at line 1')
Bad Request: /jobs/5/
Bad Request: /jobs/5/

Files

klever-2.0-test.zip (107 KB) klever-2.0-test.zip Vadim Mutilin, 12/11/2018 01:19 PM

Related issues

Related to Klever - Feature #9500: Finish support of MySQL/MariaDBNew02/25/2019

Actions

History

#1

Updated by Vadim Mutilin 5 months ago

It looks like it appears on all jobs. Just for test I attached one example

#2

Updated by Vladimir Gratinskiy 5 months ago

Currently Bridge supports only PostgreSQL and there are no plans to change it.

#3

Updated by Evgeny Novikov 5 months ago

Vladimir Gratinskiy wrote:

Currently Bridge supports only PostgreSQL and there are no plans to change it.

I agree that support of several DB backends is too expensive for us. Indeed, we have much more important tasks. And likely most of users already use PostgreSQL, in particular because of our deployment scripts use just this DB backend. Moreover for most of users this is not important at all.

But I missed the moment when Bridge finishes to support MySQL/MariaDB. There are still some references to "mysql" in source code. And most likely Bridge still accepts mysql in a DB configuration. That's why one can get errors like mentioned in the issue description.

I see two ways. Either to finish support of MySQL/MariaDB officially (remove references from source code and reject "ENGINE" from a DB configuration) or fix some issues that prevent using MySQL/MariaDB. Regarding the latter, I assume that there can be much legacy data in some MySQL/MariaDB DBs that can be hardly transfered to PostgreSQL DB. If you will suggest some way to do this, we will be absolutely right when we will finish support of MySQL/MariaDB.

#4

Updated by Vladimir Gratinskiy 5 months ago

For Klever 2.1 I already use some Postgres-specific fields like JSONField() or ArrayField().

#5

Updated by Vadim Mutilin 5 months ago

Vladimir Gratinskiy wrote:

For Klever 2.1 I already use some Postgres-specific fields like JSONField() or ArrayField().

Vladimir, could you point out the places in the code where it is used?

#6

Updated by Vladimir Gratinskiy 5 months ago

Vadim Mutilin wrote:

Vladimir Gratinskiy wrote:

For Klever 2.1 I already use some Postgres-specific fields like JSONField() or ArrayField().

Vladimir, could you point out the places in the code where it is used?

Nope, this feature is not commited yet. Next bridge version will contain huge changes, but now it's in development stage.

#7

Updated by Vadim Mutilin 5 months ago

The support for mysql is important for us. If you have problems with it, please describe them.
Could we have a variant of an interface that is based on SQL without implementation-specific extensions?

#8

Updated by Evgeny Novikov 3 months ago

  • Related to Feature #9500: Finish support of MySQL/MariaDB added
#9

Updated by Evgeny Novikov 3 months ago

Does this urgent issue still matters?

Klever 3.0 will not support MySQL/MariaDB anymore (#9500), so, we likely can fix the specified issue in the master, but after that you will not be able to use new versions of Klever with your databases one day.

Vladimir Gratinskiy, is it easy to fix the issue in the way I specified?

Also available in: Atom PDF