List view broken - ok on report view

ldl · December 5, 2022, 5:14pm

Hi guys,

I’m getting the following when trying to access the List View from Stock/Item:

App Versions

{
	"erpnext": "14.9.0",
	"frappe": "14.17.1"
}

Route

List/Item/List

Trackeback

Traceback (most recent call last):
  File "apps/frappe/frappe/app.py", line 69, in application
    response = frappe.api.handle()
  File "apps/frappe/frappe/api.py", line 54, in handle
    return frappe.handler.handle()
  File "apps/frappe/frappe/handler.py", line 45, in handle
    data = execute_cmd(cmd)
  File "apps/frappe/frappe/handler.py", line 83, in execute_cmd
    return frappe.call(method, **frappe.form_dict)
  File "apps/frappe/frappe/__init__.py", line 1587, in call
    return fn(*args, **newargs)
  File "apps/frappe/frappe/__init__.py", line 781, in wrapper_fn
    retval = fn(*args, **get_newargs(fn, kwargs))
  File "apps/frappe/frappe/desk/reportview.py", line 29, in get
    data = compress(execute(**args), args=args)
  File "apps/frappe/frappe/desk/reportview.py", line 65, in execute
    return DatabaseQuery(doctype).execute(*args, **kwargs)
  File "apps/frappe/frappe/model/db_query.py", line 172, in execute
    result = self.build_and_run()
  File "apps/frappe/frappe/model/db_query.py", line 212, in build_and_run
    return frappe.db.sql(
  File "apps/frappe/frappe/database/database.py", line 218, in sql
    self._cursor.execute(query, values)
  File "env/lib/python3.10/site-packages/pymysql/cursors.py", line 148, in execute
    result = self._query(query)
  File "env/lib/python3.10/site-packages/pymysql/cursors.py", line 310, in _query
    conn.query(q)
  File "env/lib/python3.10/site-packages/pymysql/connections.py", line 548, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "env/lib/python3.10/site-packages/pymysql/connections.py", line 775, in _read_query_result
    result.read()
  File "env/lib/python3.10/site-packages/pymysql/connections.py", line 1156, in read
    first_packet = self.connection._read_packet()
  File "env/lib/python3.10/site-packages/pymysql/connections.py", line 725, 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: (1066, "Not unique table/alias: 'tabItem'")

Request Data

{
	"type": "POST",
	"args": {
		"doctype": "Item",
		"fields": "[\"`tabItem`.`name`\",\"`tabItem`.`owner`\",\"`tabItem`.`creation`\",\"`tabItem`.`modified`\",\"`tabItem`.`modified_by`\",\"`tabItem`.`_user_tags`\",\"`tabItem`.`_comments`\",\"`tabItem`.`_assign`\",\"`tabItem`.`_liked_by`\",\"`tabItem`.`docstatus`\",\"`tabItem`.`idx`\",\"`tabItem`.`item_group`\",\"`tabItem`.`item_name`\",\"`tabItem`.`image`\",\"`tabItem`.`stock_uom`\",\"`tabItem`.`variant_of`\",\"`tabItem`.`has_variants`\",\"`tabItem`.`end_of_life`\",\"`tabItem`.`disabled`\",\"variant_of.item_name as variant_of_item_name\"]",
		"filters": "[[\"Item\",\"name\",\"like\",\"%-%\"]]",
		"order_by": "`tabItem`.`modified` desc",
		"start": 0,
		"page_length": 20,
		"view": "List",
		"group_by": "`tabItem`.`name`",
		"with_comment_count": true
	},
	"freeze": false,
	"freeze_message": "Loading...",
	"headers": {},
	"error_handlers": {},
	"url": "/api/method/frappe.desk.reportview.get"
}

Response Data

{
	"exception": "pymysql.err.OperationalError: (1066, \"Not unique table/alias: 'tabItem'\")"
}

I had a custom doctype names item_project. Tried deleting it, deleted this item_project table from the db, bench migrate … no luck.

Any ideas?

Thank you so much!

NCP · December 6, 2022, 7:36am

Please try it.
Make sure, first, to take a backup.

bench setup requirements
bench update --reset
bench update --patch
bench build
bench clear-cache
bench migrate

ldl · December 6, 2022, 9:35am

Hi! Thanks for the reply. I got some tracebacks by the end of the scripts when starting supervisorctl. The application is still accessible, but the error persists.

The tracebacks are as follows (example from bench build):

bench build
Assets for Release v14.17.1 don't exist
✔ Application Assets Linked                                                                                                                                                                                                                                                                                             


yarn run v1.22.19
$ node esbuild --production --run-build-command
clean: postcss.plugin was deprecated. Migration guide:
https://evilmartians.com/chronicles/postcss-8-plugin-migration
Browserslist: caniuse-lite is outdated. Please run:
  npx browserslist@latest --update-db
  Why you should do it regularly: https://github.com/browserslist/browserslist#browsers-data-updating
clean: postcss.plugin was deprecated. Migration guide:
https://evilmartians.com/chronicles/postcss-8-plugin-migration
clean: postcss.plugin was deprecated. Migration guide:
https://evilmartians.com/chronicles/postcss-8-plugin-migration
clean: postcss.plugin was deprecated. Migration guide:
https://evilmartians.com/chronicles/postcss-8-plugin-migration
File                                                        Size

frappe/dist/js/
├─ bootstrap-4-web.bundle.22U72DEL.js                       1.73 Kb
├─ controls.bundle.43F2CODC.js                              1226.26 Kb
├─ data_import_tools.bundle.ZHGGYJ52.js                     106.10 Kb
├─ desk.bundle.KST4NQMY.js                                  1315.73 Kb
├─ dialog.bundle.IBOC573P.js                                52.67 Kb
├─ form.bundle.G6RG5UD5.js                                  153.74 Kb
├─ frappe-web.bundle.IVNKNLJV.js                            823.06 Kb
├─ libs.bundle.YZMCKPNH.js                                  574.13 Kb
├─ list.bundle.DL4LWF3X.js                                  185.22 Kb
├─ logtypes.bundle.7STJ7YLS.js                              0.73 Kb
├─ recorder.bundle.5AOEUOFD.js                              179.54 Kb
├─ report.bundle.5NRPBM7D.js                                170.93 Kb
├─ user_profile_controller.bundle.YR6XHZRM.js               11.35 Kb
├─ video_player.bundle.UO3KNN5D.js                          120.59 Kb
├─ web_form.bundle.QC3HHUKD.js                              1560.13 Kb
├─ print_format_builder.bundle.KP4FAW42.js                  170.39 Kb
├─ build_events.bundle.L2HAVD4K.js                          11.62 Kb
└─ kanban_board.bundle.VCVKXCPT.js                          27.42 Kb

erpnext/dist/js/
├─ bank-reconciliation-tool.bundle.M2IJU46J.js              15.49 Kb
├─ erpnext-web.bundle.SJSJGLAQ.js                           33.34 Kb
├─ erpnext.bundle.6G5WMRBG.js                               171.45 Kb
├─ hierarchy-chart.bundle.PM5NQB36.js                       197.67 Kb
├─ item-dashboard.bundle.WFJGHSJT.js                        10.01 Kb
└─ point-of-sale.bundle.7A5YL4MV.js                         92.73 Kb

frappe/dist/css/
├─ desk.bundle.MQ3GIDD6.css                                 543.58 Kb
├─ email.bundle.ALODULOZ.css                                4.02 Kb
├─ login.bundle.SEIFO2IY.css                                23.56 Kb
├─ print.bundle.II46L3NP.css                                194.76 Kb
├─ print_format.bundle.APHHNZLQ.css                         177.61 Kb
├─ report.bundle.HZZXNN3S.css                               5.36 Kb
├─ web_form.bundle.WD4SCYFB.css                             14.73 Kb
└─ website.bundle.YCVBYPKQ.css                              420.31 Kb

erpnext/dist/css/
├─ erpnext-web.bundle.BEPAC7NZ.css                          25.63 Kb
├─ erpnext.bundle.QSUJCZQT.css                              45.29 Kb
└─ erpnext_email.bundle.XYDXLD2J.css                        0.56 Kb

frappe/dist/css-rtl/
├─ desk.bundle.A773VAQX.css                                 543.86 Kb
├─ email.bundle.B44CST34.css                                4.02 Kb
├─ login.bundle.4VNI6FSW.css                                23.56 Kb
├─ print.bundle.MNTOFTBF.css                                194.91 Kb
├─ print_format.bundle.5FMDDVW6.css                         177.72 Kb
├─ report.bundle.QF37K3QC.css                               5.35 Kb
├─ web_form.bundle.KBDYYOZF.css                             14.72 Kb
└─ website.bundle.FACCDMUV.css                              420.50 Kb

erpnext/dist/css-rtl/
├─ erpnext-web.bundle.ZGAC3II7.css                          25.62 Kb
├─ erpnext.bundle.GEI3I3TK.css                              45.28 Kb
└─ erpnext_email.bundle.YWPBNHCQ.css                        0.56 Kb

 DONE  Total Build Time: 17.196s

Done in 17.71s.
frappe@erpnext-v14:~/frappe-bench$ supervisorctl start all
frappe-bench-web:frappe-bench-node-socketio: ERROR (spawn error)
frappe@erpnext-v14:~/frappe-bench$ bench setup redis
frappe@erpnext-v14:~/frappe-bench$ bench setup socketio
frappe@erpnext-v14:~/frappe-bench$ bench setup config
frappe@erpnext-v14:~/frappe-bench$ bench restart
$ supervisorctl restart frappe-bench-workers: frappe-bench-web:
frappe-bench-workers:frappe-bench-frappe-schedule: stopped
frappe-bench-workers:frappe-bench-frappe-default-worker-0: stopped
frappe-bench-workers:frappe-bench-frappe-short-worker-0: stopped
frappe-bench-workers:frappe-bench-frappe-long-worker-0: stopped
frappe-bench-web:frappe-bench-node-socketio: stopped
frappe-bench-web:frappe-bench-frappe-web: stopped
frappe-bench-workers:frappe-bench-frappe-schedule: started
frappe-bench-workers:frappe-bench-frappe-default-worker-0: started
frappe-bench-workers:frappe-bench-frappe-short-worker-0: started
frappe-bench-workers:frappe-bench-frappe-long-worker-0: started
frappe-bench-web:frappe-bench-frappe-web: started
frappe-bench-web:frappe-bench-node-socketio: ERROR (spawn error)
ERROR: 
Traceback (most recent call last):
  File "/usr/local/bin/bench", line 8, in <module>
    sys.exit(cli())
  File "/usr/local/lib/python3.10/dist-packages/bench/cli.py", line 127, in cli
    bench_command()
  File "/usr/lib/python3/dist-packages/click/core.py", line 1128, in __call__
    return self.main(*args, **kwargs)
  File "/usr/lib/python3/dist-packages/click/core.py", line 1053, in main
    rv = self.invoke(ctx)
  File "/usr/lib/python3/dist-packages/click/core.py", line 1659, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/usr/lib/python3/dist-packages/click/core.py", line 1395, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/usr/lib/python3/dist-packages/click/core.py", line 754, in invoke
    return __callback(*args, **kwargs)
  File "/usr/local/lib/python3.10/dist-packages/bench/commands/utils.py", line 41, in restart
    Bench(".").reload(web, supervisor, systemd)
  File "/usr/local/lib/python3.10/dist-packages/bench/utils/render.py", line 126, in wrapper_fn
    return fn(*args, **kwargs)
  File "/usr/local/lib/python3.10/dist-packages/bench/bench.py", line 151, in reload
    restart_supervisor_processes(bench_path=self.name, web_workers=web)
  File "/usr/local/lib/python3.10/dist-packages/bench/utils/bench.py", line 315, in restart_supervisor_processes
    bench.run(f"{sudo}supervisorctl restart {group}")
  File "/usr/local/lib/python3.10/dist-packages/bench/bench.py", line 48, in run
    return exec_cmd(cmd, cwd=cwd or self.cwd)
  File "/usr/local/lib/python3.10/dist-packages/bench/utils/__init__.py", line 158, in exec_cmd
    raise CommandFailedError
bench.exceptions.CommandFailedError

Thanks again!

ldl · December 8, 2022, 4:23pm

Solution found.

Had to disable the following field:

peterg · December 8, 2022, 4:56pm

This is a regression brought about by a recent PR.

github.com/frappe/frappe

Item list error when Show Title in Link Fields

opened 11:22AM - 25 Nov 22 UTC

this-gavagai

bug

(observed in version-14 and develop) I'm getting an unusual error on my Item …list view: ``` Traceback (most recent call last): File "apps/frappe/frappe/app.py", line 56, in application response = frappe.api.handle() File "apps/frappe/frappe/api.py", line 53, in handle return _RESTAPIHandler(call, doctype, name).get_response() File "apps/frappe/frappe/api.py", line 69, in get_response return self.handle_method() File "apps/frappe/frappe/api.py", line 79, in handle_method return frappe.handler.handle() File "apps/frappe/frappe/handler.py", line 45, in handle data = execute_cmd(cmd) File "apps/frappe/frappe/handler.py", line 83, in execute_cmd return frappe.call(method, **frappe.form_dict) File "apps/frappe/frappe/__init__.py", line 1586, in call return fn(*args, **newargs) File "apps/frappe/frappe/__init__.py", line 780, in wrapper_fn retval = fn(*args, **get_newargs(fn, kwargs)) File "apps/frappe/frappe/desk/reportview.py", line 29, in get data = compress(execute(**args), args=args) File "apps/frappe/frappe/desk/reportview.py", line 65, in execute return DatabaseQuery(doctype).execute(*args, **kwargs) File "apps/frappe/frappe/model/db_query.py", line 172, in execute result = self.build_and_run() File "apps/frappe/frappe/model/db_query.py", line 212, in build_and_run return frappe.db.sql( File "apps/frappe/frappe/database/database.py", line 219, in sql self._cursor.execute(query, values) File "env/lib/python3.10/site-packages/pymysql/cursors.py", line 148, in execute result = self._query(query) File "env/lib/python3.10/site-packages/pymysql/cursors.py", line 310, in _query conn.query(q) File "env/lib/python3.10/site-packages/pymysql/connections.py", line 548, in query self._affected_rows = self._read_query_result(unbuffered=unbuffered) File "env/lib/python3.10/site-packages/pymysql/connections.py", line 775, in _read_query_result result.read() File "env/lib/python3.10/site-packages/pymysql/connections.py", line 1156, in read first_packet = self.connection._read_packet() File "env/lib/python3.10/site-packages/pymysql/connections.py", line 725, 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: (1066, "Not unique table/alias: 'tabItem'") ``` What's strange is that this exception appears only when "Show Title in Link Fields" is true. The problem is the query being run: ```sql select `tabItem`.`name`, `tabItem`.`owner`, `tabItem`.`creation`, `tabItem`.`modified`, `tabItem`.`modified_by`, `tabItem`.`_user_tags`, `tabItem`.`_comments`, `tabItem`.`_assign`, `tabItem`.`_liked_by`, `tabItem`.`docstatus`, `tabItem`.`idx`, `tabItem`.`item_group`, `tabItem`.`item_name`, `tabItem`.`image`, `tabItem`.`stock_uom`, `tabItem`.`variant_of`, `tabItem`.`has_variants`, `tabItem`.`end_of_life`, `tabItem`.`disabled`, `tabItem`.`item_name` as variant_of_item_name from `tabItem` left join `tabItem` on (`tabItem`.`name` = `tabItem`.`variant_of`) where `tabItem`.`disabled` = 0.0 group by `tabItem`.`name` order by `tabItem`.`modified` desc limit 20 offset 0 ``` The problem is the "left join `tabItem` on (`tabItem`.`name` = `tabItem`.`variant_of`)" bit. When "Show Title in Link Fields" is unchecked, the left join isn't included. I don't understand db_query or item variants well enough to understand why this is happening, but I am able to reproduce the issue on multiple systems.

Until this gets fixed, the other option you have is to remove the variant_of link field from the list view.

anhlevfx · February 15, 2023, 1:37pm

You can try my pull request to fix this error.

github.com/frappe/frappe

fix: query SQL error not unique table/alias when have a link field reference to the same doctype

frappe:version-14-hotfix ← anhlevfx:version-14-hotfix

opened 04:35AM - 02 Feb 23 UTC

anhlevfx

+16 -3

> Please provide enough information so that others can review your pull request: The issue only happen if you have a link field same doctype. Access list view will show error popup. > Explain the **details** for making this change. What existing problem does the pull request solve? I create an alias for join table by concat index to current table name. So, it make sure all alias table will unique. Base on current source code, I tried to apply change in last steps generate query string, all logic functions about check permission, access field of doctype... still working. > Screenshots/GIFs