V11 reload and rename view log - test if empty

Helio_Jesus · June 7, 2020, 10:36am

Hi all,
Just a tip when running this patch of upgrading from v10 to v12… The patch should check if empty table and not run or simply update the changes if any… This because in most of the upgrades I have done this table is empty and return as error.

clarkej · June 7, 2020, 2:03pm

You refer to this patch:

github.com

frappe/frappe/blob/develop/frappe/patches/v11_0/reload_and_rename_view_log.py

import frappe


def execute():
	if frappe.db.table_exists("View log"):
		# for mac users direct renaming would not work since mysql for mac saves table name in lower case
		# so while renaming `tabView log` to `tabView Log` we get "Table 'tabView Log' already exists" error
		# more info https://stackoverflow.com/a/44753093/5955589 ,
		# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_lower_case_table_names

		# here we are creating a temp table to store view log data
		frappe.db.sql("CREATE TABLE `ViewLogTemp` AS SELECT * FROM `tabView log`")

		# deleting old View log table
		frappe.db.sql("DROP table `tabView log`")
		frappe.delete_doc("DocType", "View log")

		# reloading view log doctype to create `tabView Log` table
		frappe.reload_doc("core", "doctype", "view_log")

This file has been truncated. show original

Next time the error occurs post the traceback here?

You might try say this change and submit a PR if that fixes the error?

if (frappe.db.exists(‘DocType’, ‘View log’) and (frappe.db.a_row_exists(‘View log’)):

Helio_Jesus · June 7, 2020, 2:31pm

Correct. Maybe the title needs to be changed but that was the point thanks.

clarkej · June 7, 2020, 4:02pm

How about on your next update migrate from v10 - before you run that, check for the problem table, so you can test the revised patch?

Something like this query below to identify the misnamed table?

In this instance here the renamed table is empty

frappe@ubuntu1804lts:~/frappe-bench$ bench mariadb
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 95927
Server version: 10.2.31-MariaDB-1:10.2.31+maria~bionic-log mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [1bd3e0294da19198]> select NULL from `tabView log`;
ERROR 1146 (42S02): Table '1bd3e0294da19198.tabView log' doesn't exist
MariaDB [1bd3e0294da19198]> select NULL from `tabView Log`;
+------+
| NULL |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

Helio_Jesus · June 13, 2020, 5:32am

Traceback (most recent call last):
File “/usr/lib/python3.5/runpy.py”, line 184, in _run_module_as_main
“main”, mod_spec)
File “/usr/lib/python3.5/runpy.py”, line 85, in _run_code
exec(code, 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/lib/python3.5/site-packages/click/core.py”, line 764, in call
return self.main(*args, **kwargs)
File “/home/frappe/frappe-bench/env/lib/python3.5/site-packages/click/core.py”, line 717, in main
rv = self.invoke(ctx)
File “/home/frappe/frappe-bench/env/lib/python3.5/site-packages/click/core.py”, line 1137, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File “/home/frappe/frappe-bench/env/lib/python3.5/site-packages/click/core.py”, line 1137, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File “/home/frappe/frappe-bench/env/lib/python3.5/site-packages/click/core.py”, line 956, in invoke
return ctx.invoke(self.callback, **ctx.params)
File “/home/frappe/frappe-bench/env/lib/python3.5/site-packages/click/core.py”, line 555, in invoke
return callback(*args, **kwargs)
File “/home/frappe/frappe-bench/env/lib/python3.5/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 243, in migrate
migrate(context.verbose, rebuild_website=rebuild_website, skip_failing=skip_failing)
File “/home/frappe/frappe-bench/apps/frappe/frappe/migrate.py”, line 48, in migrate
frappe.modules.patch_handler.run_all(skip_failing)
File “/home/frappe/frappe-bench/apps/frappe/frappe/modules/patch_handler.py”, line 41, in run_all
run_patch(patch)
File “/home/frappe/frappe-bench/apps/frappe/frappe/modules/patch_handler.py”, line 30, in run_patch
if not run_single(patchmodule = patch):
File “/home/frappe/frappe-bench/apps/frappe/frappe/modules/patch_handler.py”, line 71, in run_single
return execute_patch(patchmodule, method, methodargs)
File “/home/frappe/frappe-bench/apps/frappe/frappe/modules/patch_handler.py”, line 89, in execute_patch
exec(patchmodule.split(“execute:”)[1],globals())
File “”, line 1, in
File “/home/frappe/frappe-bench/apps/frappe/frappe/init.py”, line 796, in delete_doc
ignore_permissions, flags, ignore_on_trash, ignore_missing)
File “/home/frappe/frappe-bench/apps/frappe/frappe/model/delete_doc.py”, line 116, in delete_doc
is_async=False if frappe.flags.in_test else True)
File “/home/frappe/frappe-bench/apps/frappe/frappe/init.py”, line 1492, in enqueue
return frappe.utils.background_jobs.enqueue(*args, **kwargs)
File “/home/frappe/frappe-bench/apps/frappe/frappe/utils/background_jobs.py”, line 44, in enqueue
return frappe.call(method, **kwargs)
File “/home/frappe/frappe-bench/apps/frappe/frappe/init.py”, line 1055, in call
return fn(*args, **newargs)
File “/home/frappe/frappe-bench/apps/frappe/frappe/model/delete_doc.py”, line 307, in delete_dynamic_links
delete_references(‘View Log’, doctype, name)
File “/home/frappe/frappe-bench/apps/frappe/frappe/model/delete_doc.py”, line 322, in delete_references
(reference_doctype, reference_name))
File “/home/frappe/frappe-bench/apps/frappe/frappe/database/database.py”, line 156, in sql
self._cursor.execute(query, values)
File “/home/frappe/frappe-bench/env/lib/python3.5/site-packages/pymysql/cursors.py”, line 170, in execute
result = self._query(query)
File “/home/frappe/frappe-bench/env/lib/python3.5/site-packages/pymysql/cursors.py”, line 328, in _query
conn.query(q)
File “/home/frappe/frappe-bench/env/lib/python3.5/site-packages/pymysql/connections.py”, line 517, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
File “/home/frappe/frappe-bench/env/lib/python3.5/site-packages/pymysql/connections.py”, line 732, in _read_query_result
result.read()
File “/home/frappe/frappe-bench/env/lib/python3.5/site-packages/pymysql/connections.py”, line 1075, in read
first_packet = self.connection._read_packet()
File “/home/frappe/frappe-bench/env/lib/python3.5/site-packages/pymysql/connections.py”, line 684, in _read_packet
packet.check_error()
File “/home/frappe/frappe-bench/env/lib/python3.5/site-packages/pymysql/protocol.py”, line 220, in check_error
err.raise_mysql_exception(self._data)
File “/home/frappe/frappe-bench/env/lib/python3.5/site-packages/pymysql/err.py”, line 109, in raise_mysql_exception
raise errorclass(errno, errval)
pymysql.err.ProgrammingError: (1146, “Table ‘_51bc3d3dcff50930.tabView Log’ doesn’t exist”)

Helio_Jesus · June 13, 2020, 6:09am

I thought it was only V11 reload and rename view log but turns out is also when running
“execute:frappe.delete_doc(“Page”, “chat”)” if DT (doctypes) not yet migrated as was suggested in one of the chats here that to migrate we should first remove patches.txt so doctypes are migrated and after restore the patches.txt and re-run migrate

clarkej · June 13, 2020, 1:35pm

Well, certainly to play with or dismiss even one patch can spell garbage data. Each patch - and their sequence order that make up patches.txt - directs precisely what migrates when, from table definition and refactoring schema changes. Hence the order of changes is critical to preserve data integrity and meaning of what the tables contain…

Helio_Jesus · July 1, 2020, 9:53am

Hi all,
Just for information that i have created a PR for this based on tests done locally.
https://github.com/frappe/frappe/pull/10890