pymysql.err.InternalError: (1054, u"Unknown column 'department' in 'field list'")

hello guys
when I update or migrate I get the following error

bench@MMERP1:~/erpnext$ bench --site site1.local migrate
Migrating site1.local
Executing erpnext.patches.v11_0.create_department_records_for_each_company in site1.local (1bd3e0294da19198)
Traceback (most recent call last):
  File "/usr/lib/python2.7/runpy.py", line 174, in _run_module_as_main
    "__main__", fname, loader, pkg_name)
  File "/usr/lib/python2.7/runpy.py", line 72, in _run_code
    exec code in run_globals
  File "/opt/bench/erpnext/apps/frappe/frappe/utils/bench_helper.py", line 97, in <module>
    main()
  File "/opt/bench/erpnext/apps/frappe/frappe/utils/bench_helper.py", line 18, in main
    click.Group(commands=commands)(prog_name='bench')
  File "/opt/bench/erpnext/env/local/lib/python2.7/site-packages/click/core.py", line 722, in __call__
    return self.main(*args, **kwargs)
  File "/opt/bench/erpnext/env/local/lib/python2.7/site-packages/click/core.py", line 697, in main
    rv = self.invoke(ctx)
  File "/opt/bench/erpnext/env/local/lib/python2.7/site-packages/click/core.py", line 1066, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/opt/bench/erpnext/env/local/lib/python2.7/site-packages/click/core.py", line 1066, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/opt/bench/erpnext/env/local/lib/python2.7/site-packages/click/core.py", line 895, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/opt/bench/erpnext/env/local/lib/python2.7/site-packages/click/core.py", line 535, in invoke
    return callback(*args, **kwargs)
  File "/opt/bench/erpnext/env/local/lib/python2.7/site-packages/click/decorators.py", line 17, in new_func
    return f(get_current_context(), *args, **kwargs)
  File "/opt/bench/erpnext/apps/frappe/frappe/commands/__init__.py", line 25, in _func
    ret = f(frappe._dict(ctx.obj), *args, **kwargs)
  File "/opt/bench/erpnext/apps/frappe/frappe/commands/site.py", line 222, in migrate
    migrate(context.verbose, rebuild_website=rebuild_website)
  File "/opt/bench/erpnext/apps/frappe/frappe/migrate.py", line 39, in migrate
    frappe.modules.patch_handler.run_all()
  File "/opt/bench/erpnext/apps/frappe/frappe/modules/patch_handler.py", line 29, in run_all
    if not run_single(patchmodule = patch):
  File "/opt/bench/erpnext/apps/frappe/frappe/modules/patch_handler.py", line 63, in run_single
    return execute_patch(patchmodule, method, methodargs)
  File "/opt/bench/erpnext/apps/frappe/frappe/modules/patch_handler.py", line 83, in execute_patch
    frappe.get_attr(patchmodule.split()[0] + ".execute")()
  File "/opt/bench/erpnext/apps/erpnext/erpnext/patches/v11_0/create_department_records_for_each_company.py", line 36, in execute
    update_records(d, comp_dict)
  File "/opt/bench/erpnext/apps/erpnext/erpnext/patches/v11_0/create_department_records_for_each_company.py", line 59, in update_records
    """%(doctype, " ".join(when_then)))
  File "/opt/bench/erpnext/apps/frappe/frappe/database.py", line 209, in sql
    self._cursor.execute(query)
  File "/opt/bench/erpnext/env/local/lib/python2.7/site-packages/pymysql/cursors.py", line 170, in execute
    result = self._query(query)
  File "/opt/bench/erpnext/env/local/lib/python2.7/site-packages/pymysql/cursors.py", line 328, in _query
    conn.query(q)
  File "/opt/bench/erpnext/env/local/lib/python2.7/site-packages/pymysql/connections.py", line 516, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/opt/bench/erpnext/env/local/lib/python2.7/site-packages/pymysql/connections.py", line 727, in _read_query_result
    result.read()
  File "/opt/bench/erpnext/env/local/lib/python2.7/site-packages/pymysql/connections.py", line 1066, in read
    first_packet = self.connection._read_packet()
  File "/opt/bench/erpnext/env/local/lib/python2.7/site-packages/pymysql/connections.py", line 683, in _read_packet
    packet.check_error()
  File "/opt/bench/erpnext/env/local/lib/python2.7/site-packages/pymysql/protocol.py", line 220, in check_error
    err.raise_mysql_exception(self._data)
  File "/opt/bench/erpnext/env/local/lib/python2.7/site-packages/pymysql/err.py", line 109, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.InternalError: (1054, u"Unknown column 'department' in 'field list'")

there is a github issue resoved this problem, but I did not understand
https://github.com/frappe/erpnext/issues/14923

the solution in github says; make sure the table mentioned in below file has department column
apps/erpnext/erpnext/patchs/v11_0/set_department_for_doctypes.py

def execute():

        doctypes_to_update = {
                'hr': ['Appraisal', 'Leave Allocation', 'Expense Claim', 'Salary Slip',
                        'Attendance', 'Training Feedback', 'Training Result Employee','Leave Application',
                        'Employee Advance', 'Training Event Employee', 'Payroll Employee Detail'],
                'education': ['Instructor'],
                'projects': ['Activity Cost', 'Timesheet'],
                'setup': ['Sales Person']
        }

        for module, doctypes in doctypes_to_update.items():
                for doctype in doctypes:
                        if frappe.db.table_exists(doctype):
                                frappe.reload_doc(module, 'doctype', frappe.scrub(doctype))
                                frappe.db.sql("""
                                        update `tab%s` dt
                                        set department=(select department from `tabEmployee` where name=dt.employee)
                                """ % doctype)

in my database; the table named tabEmployee contain department column.
this is my table
Capture

hello @Zia_Akbari. I didn’t solve this yet. I have another problem and last update solve me that. Now i have this error also.

1 Like

pull now, the problem of column department doesn’t exist is solved.

On update to v11 (from v10) this error is again raising it’s head on this system.

pymysql.err.InternalError: (1054, u"Unknown column 'department' in 'field list'")

from

Executing erpnext.patches.v11_0.set_department_for_doctypes in ...

To attempt a fix, tried to comment out the following patch line in /opt/bench/erpnext/apps/erpnext/erpnext/patches.txt -

erpnext.patches.v11_0.set_department_for_doctypes

Remainder of patches run …

Now we try and bench update again with the patches.txt restored to its original form - with bench update --reset. This will not work because patches.txt is replaced once bench update with --reset flag is run. We end up with “Cannot proceed with update: You have local changes in app “erpnext” that are not committed.”

The result is that the database is brought up to date but with the exclusion of the “erpnext.patches.v11_0.set_department_for_doctypes” patch.

So far no way to update to v11 and I’m stumped.

Hello, thanks for reporting. Can you please post the entire error log?

This will help us better debug the issue.

Sure,
frappe@ubuntu:~/frappe-bench$ bench update
INFO:bench.utils:updating bench
INFO:bench.utils:git pull
Already up-to-date.
INFO:bench.utils:./env/bin/pip install Pillow
DEPRECATION: Python 2.7 will reach the end of its life on January 1st, 2020. Please upgrade your Python as Python 2.7 won’t be maintained after that date. A future version of pip will drop support for Python 2.7.
Requirement already satisfied: Pillow in ./env/lib/python2.7/site-packages (5.3.0)
INFO:bench.app:pulling frappe
INFO:bench.utils:git pull upstream master
From GitHub - frappe/frappe: Low code web framework for real world applications, in Python and Javascript

  • branch master → FETCH_HEAD
    Already up-to-date.
    INFO:bench.utils:find . -name “*.pyc” -delete
    INFO:bench.app:pulling erpnext
    INFO:bench.utils:git pull upstream master
    From GitHub - frappe/erpnext: Free and Open Source Enterprise Resource Planning (ERP)
  • branch master → FETCH_HEAD
    Already up-to-date.
    INFO:bench.utils:find . -name “*.pyc” -delete
    Updating Python libraries…
    INFO:bench.utils:./env/bin/pip install --upgrade pip
    DEPRECATION: Python 2.7 will reach the end of its life on January 1st, 2020. Please upgrade your Python as Python 2.7 won’t be maintained after that date. A future version of pip will drop support for Python 2.7.
    Requirement already up-to-date: pip in ./env/lib/python2.7/site-packages (19.0.1)
    INFO:bench.utils:./env/bin/pip install -q -r /home/frappe/.bench/requirements.txt
    DEPRECATION: Python 2.7 will reach the end of its life on January 1st, 2020. Please upgrade your Python as Python 2.7 won’t be maintained after that date. A future version of pip will drop support for Python 2.7.
    INFO:bench.app:installing frappe
    INFO:bench.utils:./env/bin/pip install -q -e ./apps/frappe
    DEPRECATION: Python 2.7 will reach the end of its life on January 1st, 2020. Please upgrade your Python as Python 2.7 won’t be maintained after that date. A future version of pip will drop support for Python 2.7.
    INFO:bench.app:installing erpnext
    INFO:bench.utils:./env/bin/pip install -q -e ./apps/erpnext
    DEPRECATION: Python 2.7 will reach the end of its life on January 1st, 2020. Please upgrade your Python as Python 2.7 won’t be maintained after that date. A future version of pip will drop support for Python 2.7.
    Updating node packages…
    INFO:bench.utils:yarn install
    yarn install v1.13.0
    [1/4] Resolving packages…
    success Already up-to-date.
    Done in 1.19s.
    Backing up sites…
    Patching sites…
    Migrating site1.local
    Executing erpnext.patches.v11_0.set_department_for_doctypes in site1.local (1bd3e0294da19198)
    Traceback (most recent call last):
    File “/usr/lib/python2.7/runpy.py”, line 174, in _run_module_as_main
    main”, fname, loader, pkg_name)
    File “/usr/lib/python2.7/runpy.py”, line 72, in _run_code
    exec code in run_globals
    File “/home/frappe/frappe-bench/apps/frappe/frappe/utils/bench_helper.py”, line 97, in
    main()
    File “/home/frappe/frappe-bench/apps/frappe/frappe/utils/bench_helper.py”, line 18, in main
    click.Group(commands=commands)(prog_name=‘bench’)
    File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py”, line 764, in call
    return self.main(*args, **kwargs)
    File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py”, line 717, in main
    rv = self.invoke(ctx)
    File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py”, line 1137, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
    File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py”, line 1137, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
    File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py”, line 956, in invoke
    return ctx.invoke(self.callback, **ctx.params)
    File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py”, line 555, in invoke
    return callback(*args, **kwargs)
    File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/click/decorators.py”, line 17, in new_func
    return f(get_current_context(), *args, **kwargs)
    File “/home/frappe/frappe-bench/apps/frappe/frappe/commands/init.py”, line 25, in _func
    ret = f(frappe._dict(ctx.obj), *args, **kwargs)
    File “/home/frappe/frappe-bench/apps/frappe/frappe/commands/site.py”, line 227, in migrate
    migrate(context.verbose, rebuild_website=rebuild_website)
    File “/home/frappe/frappe-bench/apps/frappe/frappe/migrate.py”, line 38, in migrate
    frappe.modules.patch_handler.run_all()
    File “/home/frappe/frappe-bench/apps/frappe/frappe/modules/patch_handler.py”, line 29, in run_all
    if not run_single(patchmodule = patch):
    File “/home/frappe/frappe-bench/apps/frappe/frappe/modules/patch_handler.py”, line 63, in run_single
    return execute_patch(patchmodule, method, methodargs)
    File “/home/frappe/frappe-bench/apps/frappe/frappe/modules/patch_handler.py”, line 83, in execute_patch
    frappe.get_attr(patchmodule.split()[0] + “.execute”)()
    File “/home/frappe/frappe-bench/apps/erpnext/erpnext/patches/v11_0/set_department_for_doctypes.py”, line 24, in execute
    “”" % doctype)
    File “/home/frappe/frappe-bench/apps/frappe/frappe/database.py”, line 210, in sql
    self._cursor.execute(query)
    File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/cursors.py”, line 170, in execute
    result = self._query(query)
    File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/cursors.py”, line 328, in _query
    conn.query(q)
    File “/home/frappe/frappe-bench/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/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/connections.py”, line 727, in _read_query_result
    result.read()
    File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/connections.py”, line 1066, in read
    first_packet = self.connection._read_packet()
    File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/connections.py”, line 683, in _read_packet
    packet.check_error()
    File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/protocol.py”, line 220, in check_error
    err.raise_mysql_exception(self._data)
    File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/err.py”, line 109, in raise_mysql_exception
    raise errorclass(errno, errval)
    pymysql.err.InternalError: (1054, u"Unknown column ‘department’ in ‘field list’")

Can you try changing this line:

with this:

frappe.reload_doc(module, 'doctype', frappe.scrub(doctype), force=True)

Getting this message
Cannot proceed with update: You have local changes in app “erpnext” that are not committed.

Here are your choices:

  1. Merge the erpnext app manually with “git pull” / “git pull --rebase” and fix conflicts.
  2. Temporarily remove your changes with “git stash” or discard them completely
    with “bench update --reset” or for individual repositries “git reset --hard”
  3. If your changes are helpful for others, send in a pull request via GitHub and
    wait for them to be merged in the core.

There is there a way to proceed without the above choices to see if it works…

bench migrate

That worked like charm.

Thanks
Do i need to do anything with that change i made? Submit anything?

1 Like

Thank you, I will make the necessary changes to the core repository tomorrow.

For now you can run git reset --hard in erpnext.

The above change to core got rid of the error and system was upgraded without further error. System complained about yarn:

yarn run v1.12.3
$ FRAPPE_ENV=production node rollup/build.js
module.js:550
throw err

Error: Cannot find module ‘rollup’
Your bench was upgraded to version 11
supervisor.conf already exists and this will overwrite it. Do you want to continue? [y/N]: y
nginx.conf already exists and this will overwrite it. Do you want to continue? [y/N]: y
As you have setup your bench for production, you will have to reload configuration for nginx and supervisor
To complete the migration, please run the following commands
()

This is an OS error. Fix was

sudo apt-get update && sudo apt-get install yarn

Further errors on opening invoices. Fix was

bench clear-cache

and

bench clear-website-cache

Edit: Be very careful with sudo commands on linux. It may potentially break your ErpNext implementation. The moderator may need to remove my entry above. Please remember do always do a proper backup for a rollback.