Hi everyone,
I am receiving
pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'if not exists migration_hash varchar(140)' at line 1")
this error when I try to bench migrate.
I’m using external MySQL Database from Azure.
if I don’t use External DB then there is no error regarding migration.
Here’s the error code:
Migrating synoris-ats
'Syntax error in query:\nalter table `tabDocType` add column if not exists migration_hash varchar(140) '
Traceback with variables (most recent call last):
File "/usr/lib/python3.10/runpy.py", line 196, in _run_module_as_main
return _run_code(code, main_globals, None,
mod_name = 'frappe.utils.bench_helper'
alter_argv = True
mod_spec = ModuleSpec(name='frappe.utils.bench_helper', loader=<_frozen_importlib_external.SourceFileLoader object at 0x7f965a65e5f0>, origin='/home/harsh/test/frappe-bench/apps/frappe/frappe/utils/bench_helper.py')
code = <code object <module> at 0x7f9658ea4920, file "/home/harsh/test/frappe-bench/apps/frappe/frappe/utils/bench_helper.py", line 1>
main_globals = {'__name__': '__main__', '__doc__': None, '__package__': 'frappe.utils', '__loader__': <_frozen_importlib_external.SourceFileLoader object at 0x7f965a65e5f0>, '__spec__': ModuleSpec(name='frappe.utils.bench_helper', loader=<_frozen_importlib_external.SourceFileLoader object at 0x7f965a65e5f0>, origin='/home/harsh/test/frappe-bench/apps/frappe/frappe/utils/bench_helper.py'), '__annotations__': {}, '__builtins__': <module 'builtins' (built-in)>, '__file__': '/home/harsh/test/frappe-bench/apps/frappe/frappe/utils/bench_helper.py', '__cached__': '/home/harsh/test/frappe-bench/apps/frappe/frappe/utils/__pycache__/bench_helper.cpython-310.pyc', 'importlib': <module 'importlib' from '/usr/lib/python3.10/importlib/__init__.py'>, 'json': <module 'json' from '/usr/lib/python3.10/json/__init__.py'>, 'os': <module 'os' from '/usr/lib/python3.10/os.py'>, 'traceback': <module 'traceback' from '/usr/lib/python3.10/traceback.py'>, 'warnings': <module 'warnings' from '/usr/lib/python3.10/warnings.py'>,...
File "/usr/lib/python3.10/runpy.py", line 86, in _run_code
exec(code, run_globals)
code = <code object <module> at 0x7f9658ea4920, file "/home/harsh/test/frappe-bench/apps/frappe/frappe/utils/bench_helper.py", line 1>
run_globals = {'__name__': '__main__', '__doc__': None, '__package__': 'frappe.utils', '__loader__': <_frozen_importlib_external.SourceFileLoader object at 0x7f965a65e5f0>, '__spec__': ModuleSpec(name='frappe.utils.bench_helper', loader=<_frozen_importlib_external.SourceFileLoader object at 0x7f965a65e5f0>, origin='/home/harsh/test/frappe-bench/apps/frappe/frappe/utils/bench_helper.py'), '__annotations__': {}, '__builtins__': <module 'builtins' (built-in)>, '__file__': '/home/harsh/test/frappe-bench/apps/frappe/frappe/utils/bench_helper.py', '__cached__': '/home/harsh/test/frappe-bench/apps/frappe/frappe/utils/__pycache__/bench_helper.cpython-310.pyc', 'importlib': <module 'importlib' from '/usr/lib/python3.10/importlib/__init__.py'>, 'json': <module 'json' from '/usr/lib/python3.10/json/__init__.py'>, 'os': <module 'os' from '/usr/lib/python3.10/os.py'>, 'traceback': <module 'traceback' from '/usr/lib/python3.10/traceback.py'>, 'warnings': <module 'warnings' from '/usr/lib/python3.10/warnings.py'>,...
init_globals = None
mod_name = '__main__'
mod_spec = ModuleSpec(name='frappe.utils.bench_helper', loader=<_frozen_importlib_external.SourceFileLoader object at 0x7f965a65e5f0>, origin='/home/harsh/test/frappe-bench/apps/frappe/frappe/utils/bench_helper.py')
pkg_name = 'frappe.utils'
script_name = None
loader = <_frozen_importlib_external.SourceFileLoader object at 0x7f965a65e5f0>
fname = '/home/harsh/test/frappe-bench/apps/frappe/frappe/utils/bench_helper.py'
cached = '/home/harsh/test/frappe-bench/apps/frappe/frappe/utils/__pycache__/bench_helper.cpython-310.pyc'
File "/home/harsh/test/frappe-bench/apps/frappe/frappe/utils/bench_helper.py", line 114, in <module>
main()
...skipped... 27 vars
File "/home/harsh/test/frappe-bench/apps/frappe/frappe/utils/bench_helper.py", line 20, in main
click.Group(commands=commands)(prog_name="bench")
commands = {'frappe': <Group frappe>, 'get-frappe-commands': <Command get-frappe-commands>, 'get-frappe-help': <Command get-frappe-help>}
File "/home/harsh/test/frappe-bench/env/lib/python3.10/site-packages/click/core.py", line 1157, in __call__
return self.main(*args, **kwargs)
self = <Group None>
args = ()
kwargs = {'prog_name': 'bench'}
File "/home/harsh/test/frappe-bench/env/lib/python3.10/site-packages/click/core.py", line 1078, in main
rv = self.invoke(ctx)
self = <Group None>
args = ['frappe', 'migrate']
prog_name = 'bench'
complete_var = None
standalone_mode = True
windows_expand_args = True
extra = {}
ctx = <click.core.Context object at 0x7f9658e98a60>
File "/home/harsh/test/frappe-bench/env/lib/python3.10/site-packages/click/core.py", line 1688, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
_process_result = <function MultiCommand.invoke.<locals>._process_result at 0x7f9658ea1a20>
args = ['migrate']
cmd_name = 'frappe'
cmd = <Group frappe>
sub_ctx = <click.core.Context object at 0x7f9658f32350>
ctx = <click.core.Context object at 0x7f9658e98a60>
self = <Group None>
__class__ = <class 'click.core.MultiCommand'>
File "/home/harsh/test/frappe-bench/env/lib/python3.10/site-packages/click/core.py", line 1688, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
_process_result = <function MultiCommand.invoke.<locals>._process_result at 0x7f9658d409d0>
args = []
cmd_name = 'migrate'
cmd = <Command migrate>
sub_ctx = <click.core.Context object at 0x7f9658f327a0>
ctx = <click.core.Context object at 0x7f9658f32350>
self = <Group frappe>
__class__ = <class 'click.core.MultiCommand'>
File "/home/harsh/test/frappe-bench/env/lib/python3.10/site-packages/click/core.py", line 1434, in invoke
return ctx.invoke(self.callback, **ctx.params)
self = <Command migrate>
ctx = <click.core.Context object at 0x7f9658f327a0>
File "/home/harsh/test/frappe-bench/env/lib/python3.10/site-packages/click/core.py", line 783, in invoke
return __callback(*args, **kwargs)
_Context__self = <click.core.Context object at 0x7f9658f327a0>
_Context__callback = <function migrate at 0x7f9658eed240>
args = ()
kwargs = {'skip_failing': False, 'skip_search_index': False}
ctx = <click.core.Context object at 0x7f9658f327a0>
File "/home/harsh/test/frappe-bench/env/lib/python3.10/site-packages/click/decorators.py", line 33, in new_func
return f(get_current_context(), *args, **kwargs)
args = ()
kwargs = {'skip_failing': False, 'skip_search_index': False}
f = <function migrate at 0x7f9658eed000>
File "/home/harsh/test/frappe-bench/apps/frappe/frappe/commands/__init__.py", line 29, in _func
ret = f(frappe._dict(ctx.obj), *args, **kwargs)
ctx = <click.core.Context object at 0x7f9658f327a0>
args = ()
kwargs = {'skip_failing': False, 'skip_search_index': False}
profile = False
f = <function migrate at 0x7f9658eecf70>
File "/home/harsh/test/frappe-bench/apps/frappe/frappe/commands/site.py", line 708, in migrate
SiteMigration(
context = {'sites': ['synoris-ats'], 'force': False, 'verbose': False, 'profile': False}
skip_failing = False
skip_search_index = False
activate_by_import = <module 'traceback_with_variables.activate_by_import' from '/home/harsh/test/frappe-bench/env/lib/python3.10/site-packages/traceback_with_variables/activate_by_import.py'>
SiteMigration = <class 'frappe.migrate.SiteMigration'>
site = 'synoris-ats'
File "/home/harsh/test/frappe-bench/apps/frappe/frappe/migrate.py", line 183, in run
self.setUp()
self = <frappe.migrate.SiteMigration object at 0x7f9658f325c0>
site = 'synoris-ats'
filelock = <function filelock at 0x7f9655fe1bd0>
File "/home/harsh/test/frappe-bench/apps/frappe/frappe/migrate.py", line 80, in setUp
add_column(doctype="DocType", column_name="migration_hash", fieldtype="Data")
self = <frappe.migrate.SiteMigration object at 0x7f9658f325c0>
File "/home/harsh/test/frappe-bench/apps/frappe/frappe/database/schema.py", line 366, in add_column
frappe.db.sql(query)
doctype = 'DocType'
column_name = 'migration_hash'
fieldtype = 'Data'
precision = None
length = None
default = None
not_null = False
query = 'alter table `tabDocType` add column if not exists migration_hash varchar(140)'
File "/home/harsh/test/frappe-bench/apps/frappe/frappe/database/database.py", line 234, in sql
self._cursor.execute(query, values)
self = <frappe.database.mariadb.database.MariaDBDatabase object at 0x7f96588cd870>
query = 'alter table `tabDocType` add column if not exists migration_hash varchar(140)'
values = None
as_dict = 0
as_list = 0
debug = False
ignore_ddl = 0
auto_commit = 0
update = None
explain = False
run = True
pluck = False
as_iterator = False
trace_id = None
File "/home/harsh/test/frappe-bench/env/lib/python3.10/site-packages/pymysql/cursors.py", line 153, in execute
result = self._query(query)
self = <pymysql.cursors.Cursor object at 0x7f9655ff53f0>
query = 'alter table `tabDocType` add column if not exists migration_hash varchar(140)'
args = None
File "/home/harsh/test/frappe-bench/env/lib/python3.10/site-packages/pymysql/cursors.py", line 322, in _query
conn.query(q)
self = <pymysql.cursors.Cursor object at 0x7f9655ff53f0>
q = 'alter table `tabDocType` add column if not exists migration_hash varchar(140)'
conn = <pymysql.connections.Connection object at 0x7f9655ff5330>
File "/home/harsh/test/frappe-bench/env/lib/python3.10/site-packages/pymysql/connections.py", line 558, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
self = <pymysql.connections.Connection object at 0x7f9655ff5330>
sql = b'alter table `tabDocType` add column if not exists migration_hash varchar(140)'
unbuffered = False
File "/home/harsh/test/frappe-bench/env/lib/python3.10/site-packages/pymysql/connections.py", line 822, in _read_query_result
result.read()
self = <pymysql.connections.Connection object at 0x7f9655ff5330>
unbuffered = False
result = <pymysql.connections.MySQLResult object at 0x7f9655ff52d0>
File "/home/harsh/test/frappe-bench/env/lib/python3.10/site-packages/pymysql/connections.py", line 1200, in read
first_packet = self.connection._read_packet()
self = <pymysql.connections.MySQLResult object at 0x7f9655ff52d0>
File "/home/harsh/test/frappe-bench/env/lib/python3.10/site-packages/pymysql/connections.py", line 772, in _read_packet
packet.raise_for_error()
self = <pymysql.connections.Connection object at 0x7f9655ff5330>
packet_type = <class 'pymysql.protocol.MysqlPacket'>
buff = bytearray(b"\xff(\x04#42000You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \'if not exists migration_hash varchar(140)\' at line 1")
packet_header = b'\xc4\x00\x00\x01'
btrl = 196
btrh = 0
packet_number = 1
bytes_to_read = 196
recv_data = b"\xff(\x04#42000You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'if not exists migration_hash varchar(140)' at line 1"
packet = <pymysql.protocol.MysqlPacket object at 0x7f9655ff5540>
File "/home/harsh/test/frappe-bench/env/lib/python3.10/site-packages/pymysql/protocol.py", line 221, in raise_for_error
err.raise_mysql_exception(self._data)
self = <pymysql.protocol.MysqlPacket object at 0x7f9655ff5540>
errno = 1064
File "/home/harsh/test/frappe-bench/env/lib/python3.10/site-packages/pymysql/err.py", line 143, in raise_mysql_exception
raise errorclass(errno, errval)
data = b"\xff(\x04#42000You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'if not exists migration_hash varchar(140)' at line 1"
errno = 1064
errval = "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'if not exists migration_hash varchar(140)' at line 1"
errorclass = <class 'pymysql.err.ProgrammingError'>
pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'if not exists migration_hash varchar(140)' at line 1")
`