Bench Update Error: Unknown column 'onboarding_status' in 'field list'

webbie · April 5, 2024, 9:30pm

Hi there,

I tried to update my ERPNext version from 14.4.0 to 14.66.3 to get the latest bug fixes, while staying on the same major release.

Old version info below:

erpnext 14.4.0
frappe 14.13.0

To do the update, I ran the command “bench update”.

The update was successful (after needing to update Node and retrying the “bench update”), afterwards I ran “bench migrate”. The ERPNext web app loads in the home screen, I can log in, etc. but when I click “Switch to Desk”, I get the following server error:

“Uncaught Server Exception”

Traceback:

Traceback (most recent call last):
  File "apps/frappe/frappe/www/app.py", line 26, in get_context
    boot = frappe.sessions.get()
  File "apps/frappe/frappe/sessions.py", line 152, in get
    bootinfo = get_bootinfo()
  File "apps/frappe/frappe/boot.py", line 39, in get_bootinfo
    get_user(bootinfo)
  File "apps/frappe/frappe/boot.py", line 277, in get_user
    bootinfo.user = frappe.get_user().load_user()
  File "apps/frappe/frappe/utils/user.py", line 214, in load_user
    d = frappe.db.get_value(
  File "apps/frappe/frappe/database/database.py", line 568, in get_value
    result = self.get_values(
  File "apps/frappe/frappe/database/database.py", line 672, in get_values
    out = self._get_values_from_table(
  File "apps/frappe/frappe/database/database.py", line 910, in _get_values_from_table
    return query.run(as_dict=as_dict, debug=debug, update=update, run=run, pluck=pluck)
  File "apps/frappe/frappe/query_builder/utils.py", line 87, in execute_query
    result = frappe.db.sql(query, params, *args, **kwargs)  # nosemgrep
  File "apps/frappe/frappe/database/database.py", line 244, in sql
    self._cursor.execute(query, values)
  File "env/lib/python3.10/site-packages/pymysql/cursors.py", line 158, in execute
    result = self._query(query)
  File "env/lib/python3.10/site-packages/pymysql/cursors.py", line 325, in _query
    conn.query(q)
  File "env/lib/python3.10/site-packages/pymysql/connections.py", line 549, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "env/lib/python3.10/site-packages/pymysql/connections.py", line 779, in _read_query_result
    result.read()
  File "env/lib/python3.10/site-packages/pymysql/connections.py", line 1157, in read
    first_packet = self.connection._read_packet()
  File "env/lib/python3.10/site-packages/pymysql/connections.py", line 729, in _read_packet
    packet.raise_for_error()
  File "env/lib/python3.10/site-packages/pymysql/protocol.py", line 221, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "env/lib/python3.10/site-packages/pymysql/err.py", line 143, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.OperationalError: (1054, "Unknown column 'onboarding_status' in 'field list'")

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

Traceback (most recent call last):
  File "apps/frappe/frappe/website/serve.py", line 18, in get_response
    response = renderer_instance.render()
  File "apps/frappe/frappe/website/page_renderers/template_page.py", line 84, in render
    html = self.get_html()
  File "apps/frappe/frappe/website/utils.py", line 517, in cache_html_decorator
    html = func(*args, **kwargs)
  File "apps/frappe/frappe/website/page_renderers/template_page.py", line 95, in get_html
    self.update_context()
  File "apps/frappe/frappe/website/page_renderers/template_page.py", line 163, in update_context
    data = self.run_pymodule_method("get_context")
  File "apps/frappe/frappe/website/page_renderers/template_page.py", line 223, in run_pymodule_method
    return method(self.context)
  File "apps/frappe/frappe/www/app.py", line 28, in get_context
    raise frappe.SessionBootFailed from e
frappe.exceptions.SessionBootFailed

Please may you help me?

gopikrishnan · April 6, 2024, 2:39am

Please check if the ‘onboarding_status’ column exists in the ‘User’ database table. You can do this by running the following command in your terminal:

bench describe-database-table --doctype User

This command will display the database schema for the ‘User’ doctype. Check if the ‘onboarding_status’ column is listed.

Alternatively, if you’re familiar with SQL, you can directly query the database to check for the column. Run a SELECT or DESCRIBE query on the ‘tabUser’ table to see if the ‘onboarding_status’ column exists.

If the column does not exist, you’ll need to add it to the ‘tabUser’ table with the appropriate data type. Once you’ve added the column, refresh your ERPNext.

For adding the column with the appropriate data type, you can refer below or above image

ALTER TABLE `tabUser` ADD COLUMN `onboarding_status` TEXT DEFAULT '{}';

This SQL query will add the ‘onboarding_status’ column to the ‘tabUser’ table with the ‘text’ data type and the default value ‘{}’

webbie · April 6, 2024, 9:14am

Thank you very much, that worked to solve the initial problem!

However, that leads to discovering the next similar problem, that the column ‘is_hidden’ isn’t found. Stack trace below.

Is there a way to tell which table I need to modify for any similar errors that crop up? If so, please may you share how you knew which table to add the field into? Alternatively, is there a way for me to modify the error message to contain the table information?

Many thanks in advance!

Traceback (most recent call last):
  File "apps/frappe/frappe/www/app.py", line 26, in get_context
    boot = frappe.sessions.get()
  File "apps/frappe/frappe/sessions.py", line 152, in get
    bootinfo = get_bootinfo()
  File "apps/frappe/frappe/boot.py", line 52, in get_bootinfo
    load_desktop_data(bootinfo)
  File "apps/frappe/frappe/boot.py", line 135, in load_desktop_data
    bootinfo.allowed_workspaces = get_workspace_sidebar_items().get("pages")
  File "apps/frappe/frappe/desk/desktop.py", line 446, in get_workspace_sidebar_items
    all_pages = frappe.get_all(
  File "apps/frappe/frappe/__init__.py", line 1930, in get_all
    return get_list(doctype, *args, **kwargs)
  File "apps/frappe/frappe/__init__.py", line 1902, in get_list
    return frappe.model.db_query.DatabaseQuery(doctype).execute(*args, **kwargs)
  File "apps/frappe/frappe/model/db_query.py", line 190, in execute
    result = self.build_and_run()
  File "apps/frappe/frappe/model/db_query.py", line 231, in build_and_run
    return frappe.db.sql(
  File "apps/frappe/frappe/database/database.py", line 244, in sql
    self._cursor.execute(query, values)
  File "env/lib/python3.10/site-packages/pymysql/cursors.py", line 158, in execute
    result = self._query(query)
  File "env/lib/python3.10/site-packages/pymysql/cursors.py", line 325, in _query
    conn.query(q)
  File "env/lib/python3.10/site-packages/pymysql/connections.py", line 549, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "env/lib/python3.10/site-packages/pymysql/connections.py", line 779, in _read_query_result
    result.read()
  File "env/lib/python3.10/site-packages/pymysql/connections.py", line 1157, in read
    first_packet = self.connection._read_packet()
  File "env/lib/python3.10/site-packages/pymysql/connections.py", line 729, in _read_packet
    packet.raise_for_error()
  File "env/lib/python3.10/site-packages/pymysql/protocol.py", line 221, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "env/lib/python3.10/site-packages/pymysql/err.py", line 143, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.OperationalError: (1054, "Unknown column 'is_hidden' in 'field list'")

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

Traceback (most recent call last):
  File "apps/frappe/frappe/website/serve.py", line 18, in get_response
    response = renderer_instance.render()
  File "apps/frappe/frappe/website/page_renderers/template_page.py", line 84, in render
    html = self.get_html()
  File "apps/frappe/frappe/website/utils.py", line 517, in cache_html_decorator
    html = func(*args, **kwargs)
  File "apps/frappe/frappe/website/page_renderers/template_page.py", line 95, in get_html
    self.update_context()
  File "apps/frappe/frappe/website/page_renderers/template_page.py", line 163, in update_context
    data = self.run_pymodule_method("get_context")
  File "apps/frappe/frappe/website/page_renderers/template_page.py", line 223, in run_pymodule_method
    return method(self.context)
  File "apps/frappe/frappe/www/app.py", line 28, in get_context
    raise frappe.SessionBootFailed from e
frappe.exceptions.SessionBootFailed

gopikrishnan · April 6, 2024, 10:24am

My pleasure!

You can check the database logs to see which queries were recently executed. This can help you identify the tables involved in the issue. Checking the database logs is a straightforward and recommended approach.

Most database calls occur when retrieving data, such as when viewing certain doctype views. If you encounter an error related to a specific doctype, you’ll likely need to check the corresponding database table.

Additionally, core database tables like user tables are accessed frequently. You can search for the column causing the error in Frappe’s GitHub repository by filtering JSON files.

For example:

webbie · April 6, 2024, 12:54pm

Thank you very much, that was helpful!

It seems there are many columns missing in my DB… so if I go and add all the columns manually, making assumptions of what the defaults and data types should be, I now run the risk that I’m not creating all the right relationships, etc. The risk of screwing up the DB completely is quite high, given my limited knowledge of SQL and Frappe. Even if I make no mistakes (which is unlikely), it will take a long time to work through all these errors, by the looks of things.

I thought the command “bench migrate” should have taken care of all the DB updates required, but clearly this didn’t work in my case. Is there any way or any tool that I can run that will update my DB as required for this update to the latest version 14 (14.66.3), or that can repair my DB?

Stack trace of the latest error below, after adding 3 more columns in the DB.

Many thanks in advance!

Traceback (most recent call last):
  File "apps/frappe/frappe/www/app.py", line 26, in get_context
    boot = frappe.sessions.get()
  File "apps/frappe/frappe/sessions.py", line 152, in get
    bootinfo = get_bootinfo()
  File "apps/frappe/frappe/boot.py", line 73, in get_bootinfo
    bootinfo.onboarding_tours = get_onboarding_ui_tours()
  File "apps/frappe/frappe/desk/doctype/form_tour/form_tour.py", line 76, in get_onboarding_ui_tours
    ui_tours = frappe.get_all("Form Tour", filters={"ui_tour": 1}, fields=["page_route", "name"])
  File "apps/frappe/frappe/__init__.py", line 1930, in get_all
    return get_list(doctype, *args, **kwargs)
  File "apps/frappe/frappe/__init__.py", line 1902, in get_list
    return frappe.model.db_query.DatabaseQuery(doctype).execute(*args, **kwargs)
  File "apps/frappe/frappe/model/db_query.py", line 190, in execute
    result = self.build_and_run()
  File "apps/frappe/frappe/model/db_query.py", line 231, in build_and_run
    return frappe.db.sql(
  File "apps/frappe/frappe/database/database.py", line 244, in sql
    self._cursor.execute(query, values)
  File "env/lib/python3.10/site-packages/pymysql/cursors.py", line 158, in execute
    result = self._query(query)
  File "env/lib/python3.10/site-packages/pymysql/cursors.py", line 325, in _query
    conn.query(q)
  File "env/lib/python3.10/site-packages/pymysql/connections.py", line 549, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "env/lib/python3.10/site-packages/pymysql/connections.py", line 779, in _read_query_result
    result.read()
  File "env/lib/python3.10/site-packages/pymysql/connections.py", line 1157, in read
    first_packet = self.connection._read_packet()
  File "env/lib/python3.10/site-packages/pymysql/connections.py", line 729, in _read_packet
    packet.raise_for_error()
  File "env/lib/python3.10/site-packages/pymysql/protocol.py", line 221, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "env/lib/python3.10/site-packages/pymysql/err.py", line 143, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.OperationalError: (1054, "Unknown column 'tabForm Tour.ui_tour' in 'where clause'")

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

Traceback (most recent call last):
  File "apps/frappe/frappe/website/serve.py", line 18, in get_response
    response = renderer_instance.render()
  File "apps/frappe/frappe/website/page_renderers/template_page.py", line 84, in render
    html = self.get_html()
  File "apps/frappe/frappe/website/utils.py", line 517, in cache_html_decorator
    html = func(*args, **kwargs)
  File "apps/frappe/frappe/website/page_renderers/template_page.py", line 95, in get_html
    self.update_context()
  File "apps/frappe/frappe/website/page_renderers/template_page.py", line 163, in update_context
    data = self.run_pymodule_method("get_context")
  File "apps/frappe/frappe/website/page_renderers/template_page.py", line 223, in run_pymodule_method
    return method(self.context)
  File "apps/frappe/frappe/www/app.py", line 28, in get_context
    raise frappe.SessionBootFailed from e
frappe.exceptions.SessionBootFailed

Mohammadali · April 6, 2024, 1:15pm

Hi @webbie
Please try this I hope it will work.
Note: Take a back-up before using this command

bench update --reset --no-backup

Thank You!

webbie · April 6, 2024, 2:31pm

Thank you @Mohammadali that was a good clue…

I read a bit more, then tried to rerun the “bench migrate” but also specifying which site to use, then carefully read the command’s output and realized that the migrate needs to be run while the bench is running (I thought specifically it should not be, which was why I stopped the bench before running “migrate”).

So after starting the bench and rerunning “bench --site migrate”, it has now worked and the DB problems are solved!

Thank you very much to you and also to @gopikrishnan for the help!