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

ERPNext
, , , ,
1 
(env) localhost-2:frappe-bench abdo$ bench new-site frappe1.local
MySQL root password: 

Installing frappe...
Updating DocTypes for frappe        : [=====                                   ] 12%Syntax error in query:
create sequence if not exists access_log_id_seq cache 50 nocycle None


There was an issue while migrating the DocType: Access Log

Traceback (most recent call last):
  File "/Library/Frameworks/Python.framework/Versions/3.10/lib/python3.10/runpy.py", line 196, in _run_module_as_main
    return _run_code(code, main_globals, None,
  File "/Library/Frameworks/Python.framework/Versions/3.10/lib/python3.10/runpy.py", line 86, in _run_code
    exec(code, run_globals)
  File "/Users/abdo/frappe/frappe-bench/apps/frappe/frappe/utils/bench_helper.py", line 109, in <module>
    main()
  File "/Users/abdo/frappe/frappe-bench/apps/frappe/frappe/utils/bench_helper.py", line 18, in main
    click.Group(commands=commands)(prog_name="bench")
  File "/Users/abdo/frappe/frappe-bench/env/lib/python3.10/site-packages/click/core.py", line 829, in __call__
    return self.main(*args, **kwargs)
  File "/Users/abdo/frappe/frappe-bench/env/lib/python3.10/site-packages/click/core.py", line 782, in main
    rv = self.invoke(ctx)
  File "/Users/abdo/frappe/frappe-bench/env/lib/python3.10/site-packages/click/core.py", line 1259, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/Users/abdo/frappe/frappe-bench/env/lib/python3.10/site-packages/click/core.py", line 1259, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/Users/abdo/frappe/frappe-bench/env/lib/python3.10/site-packages/click/core.py", line 1066, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/Users/abdo/frappe/frappe-bench/env/lib/python3.10/site-packages/click/core.py", line 610, in invoke
    return callback(*args, **kwargs)
  File "/Users/abdo/frappe/frappe-bench/apps/frappe/frappe/commands/site.py", line 74, in new_site
    _new_site(
  File "/Users/abdo/frappe/frappe-bench/apps/frappe/frappe/installer.py", line 101, in _new_site
    install_app(app, verbose=verbose, set_as_patched=not source_sql, force=False)
  File "/Users/abdo/frappe/frappe-bench/apps/frappe/frappe/installer.py", line 289, in install_app
    sync_for(name, force=force, reset_permissions=True)
  File "/Users/abdo/frappe/frappe-bench/apps/frappe/frappe/model/sync.py", line 80, in sync_for
    import_file_by_path(
  File "/Users/abdo/frappe/frappe-bench/apps/frappe/frappe/modules/import_file.py", line 145, in import_file_by_path
    import_doc(
  File "/Users/abdo/frappe/frappe-bench/apps/frappe/frappe/modules/import_file.py", line 242, in import_doc
    doc.insert()
  File "/Users/abdo/frappe/frappe-bench/apps/frappe/frappe/model/document.py", line 280, in insert
    self.run_post_save_methods()
  File "/Users/abdo/frappe/frappe-bench/apps/frappe/frappe/model/document.py", line 1086, in run_post_save_methods
    self.run_method("on_update")
  File "/Users/abdo/frappe/frappe-bench/apps/frappe/frappe/model/document.py", line 927, in run_method
    out = Document.hook(fn)(self, *args, **kwargs)
  File "/Users/abdo/frappe/frappe-bench/apps/frappe/frappe/model/document.py", line 1267, in composer
    return composed(self, method, *args, **kwargs)
  File "/Users/abdo/frappe/frappe-bench/apps/frappe/frappe/model/document.py", line 1249, in runner
    add_to_return_value(self, fn(self, *args, **kwargs))
  File "/Users/abdo/frappe/frappe-bench/apps/frappe/frappe/model/document.py", line 924, in fn
    return method_object(*args, **kwargs)
  File "/Users/abdo/frappe/frappe-bench/apps/frappe/frappe/core/doctype/doctype/doctype.py", line 385, in on_update
    raise e
  File "/Users/abdo/frappe/frappe-bench/apps/frappe/frappe/core/doctype/doctype/doctype.py", line 382, in on_update
    frappe.db.updatedb(self.name, Meta(self))
  File "/Users/abdo/frappe/frappe-bench/apps/frappe/frappe/database/mariadb/database.py", line 374, in updatedb
    db_table.sync()
  File "/Users/abdo/frappe/frappe-bench/apps/frappe/frappe/database/schema.py", line 41, in sync
    self.create()
  File "/Users/abdo/frappe/frappe-bench/apps/frappe/frappe/database/mariadb/schema.py", line 43, in create
    frappe.db.create_sequence(self.doctype, check_not_exists=True, cache=frappe.db.SEQUENCE_CACHE)
  File "/Users/abdo/frappe/frappe-bench/apps/frappe/frappe/database/database.py", line 1283, in create_sequence
    return create_sequence(*args, **kwargs)
  File "/Users/abdo/frappe/frappe-bench/apps/frappe/frappe/database/sequence.py", line 54, in create_sequence
    db.sql_ddl(query)
  File "/Users/abdo/frappe/frappe-bench/apps/frappe/frappe/database/database.py", line 326, in sql_ddl
    self.sql(query, debug=debug)
  File "/Users/abdo/frappe/frappe-bench/apps/frappe/frappe/database/database.py", line 192, in sql
    self._cursor.execute(query, values)
  File "/Users/abdo/frappe/frappe-bench/env/lib/python3.10/site-packages/pymysql/cursors.py", line 148, in execute
    result = self._query(query)
  File "/Users/abdo/frappe/frappe-bench/env/lib/python3.10/site-packages/pymysql/cursors.py", line 310, in _query
    conn.query(q)
  File "/Users/abdo/frappe/frappe-bench/env/lib/python3.10/site-packages/pymysql/connections.py", line 548, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/Users/abdo/frappe/frappe-bench/env/lib/python3.10/site-packages/pymysql/connections.py", line 775, in _read_query_result
    result.read()
  File "/Users/abdo/frappe/frappe-bench/env/lib/python3.10/site-packages/pymysql/connections.py", line 1156, in read
    first_packet = self.connection._read_packet()
  File "/Users/abdo/frappe/frappe-bench/env/lib/python3.10/site-packages/pymysql/connections.py", line 725, in _read_packet
    packet.raise_for_error()
  File "/Users/abdo/frappe/frappe-bench/env/lib/python3.10/site-packages/pymysql/protocol.py", line 221, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "/Users/abdo/frappe/frappe-bench/env/lib/python3.10/site-packages/pymysql/err.py", line 143, in raise_mysql_exception
    raise errorclass(errno, errval)
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 'sequence if not exists access_log_id_seq cache 50 nocycle' at line 1")

Output of bench version

erpnext 14.x.x-develop
frappe 14.x.x-develop

Additional information

My OS is Mac OS X , 10.15.7

2

Hi @Abdullah_Yassin, I’m facing a similar issue, have you found a fix please.

3 
create sequence if not exists access_log_id_seq cache 50 nocycle None

Is the keyword “None” part of Create Sequence syntax?

4

@Abdullah_Yassin Have you got any solution for this issue

5

I solve this issue by downgrade to ERPnext v13

Frappe & ERPnext is not stable on MacOS

but i found A LOT of errors on site at runtime

I finally gave up , and buy VPS Ubuntu 20. it’s work good.

6

@Abdullah_Yassin Thanks

7

this issue still exists on macOS, is there any solution at all?

8

not fixing this issue makes frappe-based app development impossible for Mac-based devs, this is not at all acceptable, there must be a solution for this.
I’ve been stuck on this for several days, any help would be greatly appreciated!

9

macOS is not standard linux, so it WILL experience compatibility problems. (I am obviously biased - but putting open source apps onto a closed source OS is not ideal since you have no control on how things get structured)

10

Is there any solution to it?
I can confirm that this is the exact issue (since stopping MariaDB and creating a site still works whereas stopping MySQL doesn’t work) where the frappe is pointing to MySQL instead of MariaDB but IDK how to make Frappe point to MariaDB, any reference on that?

11

Hi,

What does mysql -V return?

What does bench mariadb open up to?

1 Like
12

mysql -V

mysql Ver 15.1 Distrib 10.3.38-MariaDB, for osx10.18 (x86_64) using EditLine wrapper

bench mariadb

Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 2808
Server version: 8.0.32 Homebrew
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
MySQL [_60fab1a8717e3089]>

13

It looks like there are two versions of Mariadb running? Is there a MacOs equivalent of systemctl status ? Or maybe netstat -tlpn | grep mariadb will show something.

14

It’s been almost two years now I’m facing the same issue, Is there a fix available?