Bench migrate fails with pymysql.err.ProgrammingError (it doesn't print what is the error)

prob.erp · October 6, 2019, 10:48am

I restored my previous database and then used bench migrate, but it fails with pymysql.err.ProgrammingError.

Here’s the full log:

Migrating probatus
Executing erpnext.patches.v12_0.set_produced_qty_field_in_sales_order_for_work_order in probatus (_69bfcd6a9d117eae)
Traceback (most recent call last):
  File "/usr/lib/python3.6/runpy.py", line 193, in _run_module_as_main
    "__main__", mod_spec)
  File "/usr/lib/python3.6/runpy.py", line 85, in _run_code
    exec(code, run_globals)
  File "/home/anil/frappe-bench/apps/frappe/frappe/utils/bench_helper.py", line 97, in <module>
    main()
  File "/home/anil/frappe-bench/apps/frappe/frappe/utils/bench_helper.py", line 18, in main
    click.Group(commands=commands)(prog_name='bench')
  File "/home/anil/frappe-bench/env/lib/python3.6/site-packages/click/core.py", line 764, in __call__
    return self.main(*args, **kwargs)
  File "/home/anil/frappe-bench/env/lib/python3.6/site-packages/click/core.py", line 717, in main
    rv = self.invoke(ctx)
  File "/home/anil/frappe-bench/env/lib/python3.6/site-packages/click/core.py", line 1137, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/home/anil/frappe-bench/env/lib/python3.6/site-packages/click/core.py", line 1137, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/home/anil/frappe-bench/env/lib/python3.6/site-packages/click/core.py", line 956, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/home/anil/frappe-bench/env/lib/python3.6/site-packages/click/core.py", line 555, in invoke
    return callback(*args, **kwargs)
  File "/home/anil/frappe-bench/env/lib/python3.6/site-packages/click/decorators.py", line 17, in new_func
    return f(get_current_context(), *args, **kwargs)
  File "/home/anil/frappe-bench/apps/frappe/frappe/commands/__init__.py", line 25, in _func
    ret = f(frappe._dict(ctx.obj), *args, **kwargs)
  File "/home/anil/frappe-bench/apps/frappe/frappe/commands/site.py", line 233, in migrate
    migrate(context.verbose, rebuild_website=rebuild_website, skip_failing=skip_failing)
  File "/home/anil/frappe-bench/apps/frappe/frappe/migrate.py", line 48, in migrate
    frappe.modules.patch_handler.run_all(skip_failing)
  File "/home/anil/frappe-bench/apps/frappe/frappe/modules/patch_handler.py", line 41, in run_all
    run_patch(patch)
  File "/home/anil/frappe-bench/apps/frappe/frappe/modules/patch_handler.py", line 30, in run_patch
    if not run_single(patchmodule = patch):
  File "/home/anil/frappe-bench/apps/frappe/frappe/modules/patch_handler.py", line 71, in run_single
    return execute_patch(patchmodule, method, methodargs)
  File "/home/anil/frappe-bench/apps/frappe/frappe/modules/patch_handler.py", line 91, in execute_patch
    frappe.get_attr(patchmodule.split()[0] + ".execute")()
  File "/home/anil/frappe-bench/apps/erpnext/erpnext/patches/v12_0/set_produced_qty_field_in_sales_order_for_work_order.py", line 10, in execute
    update_produced_qty_in_so_item(so_item.get('name'))
  File "/home/anil/frappe-bench/apps/erpnext/erpnext/selling/doctype/sales_order/sales_order.py", line 1031, in update_produced_qty_in_so_item
    'docstatus': 1
  File "/home/anil/frappe-bench/apps/frappe/frappe/database/database.py", line 527, in get_all
    return frappe.get_all(*args, **kwargs)
  File "/home/anil/frappe-bench/apps/frappe/frappe/__init__.py", line 1299, in get_all
    return get_list(doctype, *args, **kwargs)
  File "/home/anil/frappe-bench/apps/frappe/frappe/__init__.py", line 1272, in get_list
    return frappe.model.db_query.DatabaseQuery(doctype).execute(None, *args, **kwargs)
  File "/home/anil/frappe-bench/apps/frappe/frappe/model/db_query.py", line 95, in execute
    result = self.build_and_run()
  File "/home/anil/frappe-bench/apps/frappe/frappe/model/db_query.py", line 109, in build_and_run
    args = self.prepare_args()
  File "/home/anil/frappe-bench/apps/frappe/frappe/model/db_query.py", line 135, in prepare_args
    self.set_optional_columns()
  File "/home/anil/frappe-bench/apps/frappe/frappe/model/db_query.py", line 293, in set_optional_columns
    columns = frappe.db.get_table_columns(self.doctype)
  File "/home/anil/frappe-bench/apps/frappe/frappe/database/database.py", line 857, in get_table_columns
    raise self.TableMissingError
pymysql.err.ProgrammingError

I have already tried both the bench restore way and the mysql < sql_file way. I get the same error.

I can’t bench update since it will execute bench migrate.

raise self.TableMissingError
Seems like some table is missing?

I don’t want to lose my sales invoices and orders.

clarkej · October 6, 2019, 1:45pm

Somehow you have a DocType with no corresponding table, and the exception gives no details.

Please report this issue on github

What you might try is to set a breakpoint or to change the code here on this line:

github.com

frappe/frappe/blob/develop/frappe/database/database.py#L857


      
          				"Singles", filters={"field": ("in", tuple(to_update)), "doctype": dt}, debug=debug
          			)
          
          
			singles_data = ((dt, key, sbool(value)) for key, value in to_update.items())
          			query = (
          				frappe.qb.into("Singles").columns("doctype", "field", "value").insert(*singles_data)
          			).run(debug=debug)
          			frappe.clear_document_cache(dt, dt)
          
          
		else:
          			query = frappe.qb.engine.build_conditions(table=dt, filters=dn, update=True)
          
          
			if isinstance(dn, str):
          				frappe.clear_document_cache(dt, dn)
          			else:
          				# TODO: Fix this; doesn't work rn - gavin@frappe.io
          				# frappe.cache().hdel_keys(dt, "document_cache")
          				# Workaround: clear all document caches
          				frappe.cache().delete_value("document_cache")
          
          
			for column, value in to_update.items():

To this:
raise self.TableMissingError(“DocType”, doctype)

prob.erp · October 6, 2019, 3:52pm

So it boiled down to

  File "/home/anil/frappe-bench/apps/frappe/frappe/database/database.py", line 857, in get_table_columns
    raise self.TableMissingError("DocType", doctype)
pymysql.err.ProgrammingError: ('DocType', 'Work Order')

trentmu · October 6, 2019, 4:00pm

You can reload the “Work Order” doctype via bench

bench reload-doctype “Work Order”

prob.erp · October 6, 2019, 4:13pm

Thank you. It proceeded. The quotes-like character copied from here needed to be changed though.

I now have another error, again regarding some table not existing.

Updating DocTypes for frappe        : [=============                           ]Traceback (most recent call last):
  File "/usr/lib/python3.6/runpy.py", line 193, in _run_module_as_main
    "__main__", mod_spec)
  File "/usr/lib/python3.6/runpy.py", line 85, in _run_code
    exec(code, run_globals)
  File "/home/anil/frappe-bench/apps/frappe/frappe/utils/bench_helper.py", line 97, in <module>
    main()
  File "/home/anil/frappe-bench/apps/frappe/frappe/utils/bench_helper.py", line 18, in main
    click.Group(commands=commands)(prog_name='bench')
  File "/home/anil/frappe-bench/env/lib/python3.6/site-packages/click/core.py", line 764, in __call__
    return self.main(*args, **kwargs)
  File "/home/anil/frappe-bench/env/lib/python3.6/site-packages/click/core.py", line 717, in main
    rv = self.invoke(ctx)
  File "/home/anil/frappe-bench/env/lib/python3.6/site-packages/click/core.py", line 1137, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/home/anil/frappe-bench/env/lib/python3.6/site-packages/click/core.py", line 1137, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/home/anil/frappe-bench/env/lib/python3.6/site-packages/click/core.py", line 956, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/home/anil/frappe-bench/env/lib/python3.6/site-packages/click/core.py", line 555, in invoke
    return callback(*args, **kwargs)
  File "/home/anil/frappe-bench/env/lib/python3.6/site-packages/click/decorators.py", line 17, in new_func
    return f(get_current_context(), *args, **kwargs)
  File "/home/anil/frappe-bench/apps/frappe/frappe/commands/__init__.py", line 25, in _func
    ret = f(frappe._dict(ctx.obj), *args, **kwargs)
  File "/home/anil/frappe-bench/apps/frappe/frappe/commands/site.py", line 233, in migrate
    migrate(context.verbose, rebuild_website=rebuild_website, skip_failing=skip_failing)
  File "/home/anil/frappe-bench/apps/frappe/frappe/migrate.py", line 50, in migrate
    frappe.model.sync.sync_all(verbose=verbose)
  File "/home/anil/frappe-bench/apps/frappe/frappe/model/sync.py", line 19, in sync_all
    sync_for(app, force, verbose=verbose, reset_permissions=reset_permissions)
  File "/home/anil/frappe-bench/apps/frappe/frappe/model/sync.py", line 56, in sync_for
    reset_permissions=reset_permissions, for_sync=True)
  File "/home/anil/frappe-bench/apps/frappe/frappe/modules/import_file.py", line 66, in import_file_by_path
    ignore_version=ignore_version, reset_permissions=reset_permissions)
  File "/home/anil/frappe-bench/apps/frappe/frappe/modules/import_file.py", line 141, in import_doc
    doc.insert()
  File "/home/anil/frappe-bench/apps/frappe/frappe/model/document.py", line 243, in insert
    self.db_insert()
  File "/home/anil/frappe-bench/apps/frappe/frappe/model/base_document.py", line 324, in db_insert
    ), list(d.values()))
  File "/home/anil/frappe-bench/apps/frappe/frappe/database/database.py", line 156, in sql
    self._cursor.execute(query, values)
  File "/home/anil/frappe-bench/env/lib/python3.6/site-packages/pymysql/cursors.py", line 170, in execute
    result = self._query(query)
  File "/home/anil/frappe-bench/env/lib/python3.6/site-packages/pymysql/cursors.py", line 328, in _query
    conn.query(q)
  File "/home/anil/frappe-bench/env/lib/python3.6/site-packages/pymysql/connections.py", line 517, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/home/anil/frappe-bench/env/lib/python3.6/site-packages/pymysql/connections.py", line 732, in _read_query_result
    result.read()
  File "/home/anil/frappe-bench/env/lib/python3.6/site-packages/pymysql/connections.py", line 1075, in read
    first_packet = self.connection._read_packet()
  File "/home/anil/frappe-bench/env/lib/python3.6/site-packages/pymysql/connections.py", line 684, in _read_packet
    packet.check_error()
  File "/home/anil/frappe-bench/env/lib/python3.6/site-packages/pymysql/protocol.py", line 220, in check_error
    err.raise_mysql_exception(self._data)
  File "/home/anil/frappe-bench/env/lib/python3.6/site-packages/pymysql/err.py", line 109, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.ProgrammingError: (1146, "Table '_69bfcd6a9d117eae.tabWeb Form' doesn't exist")

Also, do I need to open a github issue as the original query is solved?

clarkej · October 6, 2019, 4:22pm

Probably no need thanks, this PR may do fix: report the DocType's name when its associated table is missing by clarkejj · Pull Request #8557 · frappe/frappe · GitHub

prob.erp · October 6, 2019, 5:40pm

I have combined the following answers with minimal programming:

to get a list of mysql statements (with IF NOT EXISTS) to execute and copied tables not in my site’s db from another site’s db (from bench manager’s site).

Former query was resolved by:

prob.erp · October 6, 2019, 6:35pm

Some errors are still cropping up after running, like tabWarehouse not exisiting…

Edit: Installed erpnext on the second site and redid the above mentioned process.

Now it shows no warehouses in warehouse but stock balance is shown as was there earlier.

prob.erp · October 6, 2019, 7:04pm

please see the above edit.

trentmu · October 7, 2019, 7:30am

It appears that your DB schemas are not matched for the versions you are using. Maybe run a patch/migrate

cd ~/frappe-bench
bench update --patch
bench --site all migrate
bench --site all bench clear-cache
bench --site all bench clear-website-cache

prob.erp · October 23, 2019, 10:02am

Thank you, but I had already recreated the warehouses and everything was working as normal.

Still, I ran your commands (removed the erroneous extra bench in the last two commands though). Thank you.