Unable to Save New Doctype - MySQL error

Frappe Framework Bug
1

I am trying to save a new doctype with a single table in it. I got the following error:

Traceback (most recent call last):
  File "/home/frappe/ap/apps/frappe/frappe/desk/form/save.py", line 22, in savedocs
    doc.save()
  File "/home/frappe/ap/apps/frappe/frappe/model/document.py", line 259, in save
    return self._save(*args, **kwargs)
  File "/home/frappe/ap/apps/frappe/frappe/model/document.py", line 310, in _save
    self.run_post_save_methods()
  File "/home/frappe/ap/apps/frappe/frappe/model/document.py", line 889, in run_post_save_methods
    self.run_method("on_update")
  File "/home/frappe/ap/apps/frappe/frappe/model/document.py", line 757, in run_method
    out = Document.hook(fn)(self, *args, **kwargs)
  File "/home/frappe/ap/apps/frappe/frappe/model/document.py", line 1026, in composer
    return composed(self, method, *args, **kwargs)
  File "/home/frappe/ap/apps/frappe/frappe/model/document.py", line 1009, in runner
    add_to_return_value(self, fn(self, *args, **kwargs))
  File "/home/frappe/ap/apps/frappe/frappe/model/document.py", line 751, in <lambda>
    fn = lambda self, *args, **kwargs: getattr(self, method)(*args, **kwargs)
  File "/home/frappe/ap/apps/frappe/frappe/core/doctype/doctype/doctype.py", line 220, in on_update
    self.delete_duplicate_custom_fields()
  File "/home/frappe/ap/apps/frappe/frappe/core/doctype/doctype/doctype.py", line 261, in delete_duplicate_custom_fields
    '''.format('%s', ', '.join(['%s'] * len(fields))), tuple([self.name] + fields), as_dict=True)
  File "/home/frappe/ap/apps/frappe/frappe/database.py", line 166, in sql
    self._cursor.execute(query, values)
  File "/home/frappe/ap/env/local/lib/python2.7/site-packages/pymysql/cursors.py", line 170, in execute
    result = self._query(query)
  File "/home/frappe/ap/env/local/lib/python2.7/site-packages/pymysql/cursors.py", line 328, in _query
    conn.query(q)
  File "/home/frappe/ap/env/local/lib/python2.7/site-packages/pymysql/connections.py", line 516, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/home/frappe/ap/env/local/lib/python2.7/site-packages/pymysql/connections.py", line 727, in _read_query_result
    result.read()
  File "/home/frappe/ap/env/local/lib/python2.7/site-packages/pymysql/connections.py", line 1066, in read
    first_packet = self.connection._read_packet()
  File "/home/frappe/ap/env/local/lib/python2.7/site-packages/pymysql/connections.py", line 683, in _read_packet
    packet.check_error()
  File "/home/frappe/ap/env/local/lib/python2.7/site-packages/pymysql/protocol.py", line 220, in check_error
    err.raise_mysql_exception(self._data)
  File "/home/frappe/ap/env/local/lib/python2.7/site-packages/pymysql/err.py", line 109, in raise_mysql_exception
    raise errorclass(errno, errval)
ProgrammingError: (1064, u"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 ')' at line 4")

Traceback (most recent call last):
  File "/home/frappe/ap/apps/frappe/frappe/app.py", line 62, in application
    response = frappe.handler.handle()
  File "/home/frappe/ap/apps/frappe/frappe/handler.py", line 22, in handle
    data = execute_cmd(cmd)
  File "/home/frappe/ap/apps/frappe/frappe/handler.py", line 53, in execute_cmd
    return frappe.call(method, **frappe.form_dict)
  File "/home/frappe/ap/apps/frappe/frappe/__init__.py", line 939, in call
    return fn(*args, **newargs)
  File "/home/frappe/ap/apps/frappe/frappe/desk/form/save.py", line 22, in savedocs
    doc.save()
  File "/home/frappe/ap/apps/frappe/frappe/model/document.py", line 259, in save
    return self._save(*args, **kwargs)
  File "/home/frappe/ap/apps/frappe/frappe/model/document.py", line 310, in _save
    self.run_post_save_methods()
  File "/home/frappe/ap/apps/frappe/frappe/model/document.py", line 889, in run_post_save_methods
    self.run_method("on_update")
  File "/home/frappe/ap/apps/frappe/frappe/model/document.py", line 757, in run_method
    out = Document.hook(fn)(self, *args, **kwargs)
  File "/home/frappe/ap/apps/frappe/frappe/model/document.py", line 1026, in composer
    return composed(self, method, *args, **kwargs)
  File "/home/frappe/ap/apps/frappe/frappe/model/document.py", line 1009, in runner
    add_to_return_value(self, fn(self, *args, **kwargs))
  File "/home/frappe/ap/apps/frappe/frappe/model/document.py", line 751, in <lambda>
    fn = lambda self, *args, **kwargs: getattr(self, method)(*args, **kwargs)
  File "/home/frappe/ap/apps/frappe/frappe/core/doctype/doctype/doctype.py", line 220, in on_update
    self.delete_duplicate_custom_fields()
  File "/home/frappe/ap/apps/frappe/frappe/core/doctype/doctype/doctype.py", line 261, in delete_duplicate_custom_fields
    '''.format('%s', ', '.join(['%s'] * len(fields))), tuple([self.name] + fields), as_dict=True)
  File "/home/frappe/ap/apps/frappe/frappe/database.py", line 166, in sql
    self._cursor.execute(query, values)
  File "/home/frappe/ap/env/local/lib/python2.7/site-packages/pymysql/cursors.py", line 170, in execute
    result = self._query(query)
  File "/home/frappe/ap/env/local/lib/python2.7/site-packages/pymysql/cursors.py", line 328, in _query
    conn.query(q)
  File "/home/frappe/ap/env/local/lib/python2.7/site-packages/pymysql/connections.py", line 516, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/home/frappe/ap/env/local/lib/python2.7/site-packages/pymysql/connections.py", line 727, in _read_query_result
    result.read()
  File "/home/frappe/ap/env/local/lib/python2.7/site-packages/pymysql/connections.py", line 1066, in read
    first_packet = self.connection._read_packet()
  File "/home/frappe/ap/env/local/lib/python2.7/site-packages/pymysql/connections.py", line 683, in _read_packet
    packet.check_error()
  File "/home/frappe/ap/env/local/lib/python2.7/site-packages/pymysql/protocol.py", line 220, in check_error
    err.raise_mysql_exception(self._data)
  File "/home/frappe/ap/env/local/lib/python2.7/site-packages/pymysql/err.py", line 109, in raise_mysql_exception
    raise errorclass(errno, errval)
ProgrammingError: (1064, u"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 ')' at line 4")

Now I am unable to create any new doctypes.

2

Hi, sorry could you please give more details? which version do you use? maybe some screenshots, thanks

1 Like
3

@Sibidharan_Nandhakum

Welcome to ERPNext forums !

Please include your version in your post so that it can become easier for people to help you out.

you can find your version via these two ways :

UI Method

Versions of all the installed apps can be found out in Help button, in About section:

Terminal method

Open your frappe-bench or whatever name you gave to the folder in bench init command.

And execute command bench version which will throw up the versions of all installed apps. This is the expected output.

user@PC:~/frappe-bench$ bench version
erpnext 10.1.58
foundation 0.0.1
frappe 10.1.51
user@PC:~/frappe-bench$
4

Hey, I have no idea what went wrong, but analysing the error message, I was able to find that while saving doctype, it was checking if there were duplicate custom fields. So somewhere in the stack, I saw self.delete_duplicate_custom_fields() being called.

I was analysing the parameters and found that there were no duplicate fields, and that is causing the SQL syntax error.

This was the original code located at /apps/frappe/frappe/core/doctype/doctype/doctype.py on line 261:

 def delete_duplicate_custom_fields(self):
                if not (frappe.db.table_exists(self.name) and frappe.db.table_exists("Custom Field")):
                        return
                fields = [d.fieldname for d in self.fields if d.fieldtype in type_map]
                frappe.db.sql('''delete from
                                `tabCustom Field`
                        where
                                 dt = {0} and fieldname in ({1})
                '''.format('%s', ', '.join(['%s'] * len(fields))), tuple([self.name] + fields), as_dict=True)

This looks like, even if the fields are empty, it is trying to execute the query, which caused the issue. So I added a check, and modified the function into the following:

 def delete_duplicate_custom_fields(self):
                if not (frappe.db.table_exists(self.name) and frappe.db.table_exists("Custom Field")):
                        return
                fields = [d.fieldname for d in self.fields if d.fieldtype in type_map]
                if len(fields) < 0:
                        frappe.db.sql('''delete from
                                        `tabCustom Field`
                                where
                                         dt = {0} and fieldname in ({1})
                        '''.format('%s', ', '.join(['%s'] * len(fields))), tuple([self.name] + fields), as_dict=True)

Now if the fields are empty, it won’t cause any troubles, while migrating or saving doctype.

I don’t know if this is a valid solution, but this solved my issue. Should I make a merge commit for this one? @rmehta @root13F

1 Like
5

Did you check if by chance you missed something while defining the parent doctype or the child doctype ?

Check both the schemas.

6

I triple checked everything. I never used custom fields, I was designing a new app.

All I created was a Doctype and a Child Table related to it. It started throwing this error, out of no where. I thought I messed up with frappe, so I committed the app and then reinstalled it, still same error. So it’s definitely not me.

FYI, It’s my 8th complex app using Frappe, so I know my way around Frappe.

1 Like
7

Perfect. Then I think this calls for a GitHub issue

8

Pull request created Fix #6341 by sibidharan · Pull Request #6391 · frappe/frappe · GitHub

9

@root13F - The CI throwed error

Traceback (most recent call last):
  File "/opt/python/2.7.14/lib/python2.7/runpy.py", line 174, in _run_module_as_main
    "__main__", fname, loader, pkg_name)
  File "/opt/python/2.7.14/lib/python2.7/runpy.py", line 72, in _run_code
    exec code in run_globals
  File "/home/travis/frappe-bench/apps/frappe/frappe/utils/bench_helper.py", line 97, in <module>
    main()
  File "/home/travis/frappe-bench/apps/frappe/frappe/utils/bench_helper.py", line 18, in main
    click.Group(commands=commands)(prog_name='bench')
  File "/home/travis/frappe-bench/env/lib/python2.7/site-packages/click/core.py", line 764, in __call__
    return self.main(*args, **kwargs)
  File "/home/travis/frappe-bench/env/lib/python2.7/site-packages/click/core.py", line 717, in main
    rv = self.invoke(ctx)
  File "/home/travis/frappe-bench/env/lib/python2.7/site-packages/click/core.py", line 1137, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/home/travis/frappe-bench/env/lib/python2.7/site-packages/click/core.py", line 1137, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/home/travis/frappe-bench/env/lib/python2.7/site-packages/click/core.py", line 956, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/home/travis/frappe-bench/env/lib/python2.7/site-packages/click/core.py", line 555, in invoke
    return callback(*args, **kwargs)
  File "/home/travis/frappe-bench/env/lib/python2.7/site-packages/click/decorators.py", line 17, in new_func
    return f(get_current_context(), *args, **kwargs)
  File "/home/travis/frappe-bench/apps/frappe/frappe/commands/__init__.py", line 25, in _func
    ret = f(frappe._dict(ctx.obj), *args, **kwargs)
  File "/home/travis/frappe-bench/apps/frappe/frappe/commands/site.py", line 134, in reinstall
    _reinstall(site, admin_password, yes, verbose=context.verbose)
  File "/home/travis/frappe-bench/apps/frappe/frappe/commands/site.py", line 154, in _reinstall
    install_apps=installed, admin_password=admin_password)
  File "/home/travis/frappe-bench/apps/frappe/frappe/commands/site.py", line 70, in _new_site
    _install_app(app, verbose=verbose, set_as_patched=not source_sql)
  File "/home/travis/frappe-bench/apps/frappe/frappe/installer.py", line 78, in install_app
    out = frappe.get_attr(before_install)()
  File "/home/travis/frappe-bench/apps/frappe/frappe/utils/install.py", line 11, in before_install
    frappe.reload_doc("core", "doctype", "docfield")
  File "/home/travis/frappe-bench/apps/frappe/frappe/__init__.py", line 774, in reload_doc
    return frappe.modules.reload_doc(module, dt, dn, force=force, reset_permissions=reset_permissions)
  File "/home/travis/frappe-bench/apps/frappe/frappe/modules/utils.py", line 154, in reload_doc
    return import_files(module, dt, dn, force=force, reset_permissions=reset_permissions)
  File "/home/travis/frappe-bench/apps/frappe/frappe/modules/import_file.py", line 26, in import_files
    reset_permissions=reset_permissions)
  File "/home/travis/frappe-bench/apps/frappe/frappe/modules/import_file.py", line 31, in import_file
    ret = import_file_by_path(path, force, pre_process=pre_process, reset_permissions=reset_permissions)
  File "/home/travis/frappe-bench/apps/frappe/frappe/modules/import_file.py", line 65, in import_file_by_path
    ignore_version=ignore_version, reset_permissions=reset_permissions)
  File "/home/travis/frappe-bench/apps/frappe/frappe/modules/import_file.py", line 100, in import_doc
    doc = frappe.get_doc(docdict)
  File "/home/travis/frappe-bench/apps/frappe/frappe/__init__.py", line 709, in get_doc
    doc = frappe.model.document.get_doc(*args, **kwargs)
  File "/home/travis/frappe-bench/apps/frappe/frappe/model/document.py", line 66, in get_doc
    controller = get_controller(doctype)
  File "/home/travis/frappe-bench/apps/frappe/frappe/model/base_document.py", line 36, in get_controller
    module = load_doctype_module(doctype, module_name)
  File "/home/travis/frappe-bench/apps/frappe/frappe/modules/utils.py", line 186, in load_doctype_module
    raise ImportError('Module import failed for {0} ({1})'.format(doctype, module_name + ' Error: ' + str(e)))
ImportError: Module import failed for DocType (frappe.core.doctype.doctype.doctype Error: cannot import name type_map)

It says unable to import type_map - how to approach?

10

It says unable to import type_map - how to approach?

I think you should check if type_map is available on that path. Your branch stems from develop.

11 
from frappe.model.db_schema import validate_column_name, validate_column_length, type_map

has been replaced with

from frappe.database.schema import validate_column_name, validate_column_length

so I thought that `type_map_ will also be in the same path. I don’t know if old one exists! What to do?

I am on frappe 10.1.56.