[Profiling] Rercorder makes queries from my custom apps to fail

Hello there!

I’m experiencing pymysql exceptions on the queries from my custom apps, this happens every time I enable the Recorder (activaded as administrator via /app/recorder). If recorder is off, i have no problem.
The query:

query = f"""
            SELECT link.parent parent FROM `tabDynamic Link` link
            WHERE link.parenttype='Contact' and link.link_doctype='Lead' and link.link_name='{lead_name}';
        """
        contact_link = db.sql(query, as_dict=1)

The exception contains the following:


22:33:44 web.1            |   File "/workspace/development/frappe-bench/apps/frappe/frappe/recorder.py", line 39, in sql
22:33:44 web.1            |     explain_result = frappe.db._sql("EXPLAIN {}".format(query), as_dict=True)
22:33:44 web.1            |   File "/workspace/development/frappe-bench/apps/frappe/frappe/database/database.py", line 152, in sql
22:33:44 web.1            |     self._cursor.execute(query)
22:33:44 web.1            |   File "/workspace/development/frappe-bench/env/lib/python3.9/site-packages/pymysql/cursors.py", line 148, in execute
22:33:44 web.1            |     result = self._query(query)
22:33:44 web.1            |   File "/workspace/development/frappe-bench/env/lib/python3.9/site-packages/pymysql/cursors.py", line 310, in _query
22:33:44 web.1            |     conn.query(q)
22:33:44 web.1            |   File "/workspace/development/frappe-bench/env/lib/python3.9/site-packages/pymysql/connections.py", line 548, in query
22:33:44 web.1            |     self._affected_rows = self._read_query_result(unbuffered=unbuffered)
22:33:44 web.1            |   File "/workspace/development/frappe-bench/env/lib/python3.9/site-packages/pymysql/connections.py", line 775, in _read_query_result
22:33:44 web.1            |     result.read()
22:33:44 web.1            |   File "/workspace/development/frappe-bench/env/lib/python3.9/site-packages/pymysql/connections.py", line 1163, in read
22:33:44 web.1            |     self._read_result_packet(first_packet)
22:33:44 web.1            |   File "/workspace/development/frappe-bench/env/lib/python3.9/site-packages/pymysql/connections.py", line 1236, in _read_result_packet
22:33:44 web.1            |     self._read_rowdata_packet()
22:33:44 web.1            |   File "/workspace/development/frappe-bench/env/lib/python3.9/site-packages/pymysql/connections.py", line 1270, in _read_rowdata_packet
22:33:44 web.1            |     packet = self.connection._read_packet()
22:33:44 web.1            |   File "/workspace/development/frappe-bench/env/lib/python3.9/site-packages/pymysql/connections.py", line 725, in _read_packet
22:33:44 web.1            |     packet.raise_for_error()
22:33:44 web.1            |   File "/workspace/development/frappe-bench/env/lib/python3.9/site-packages/pymysql/protocol.py", line 221, in raise_for_error
22:33:44 web.1            |     err.raise_mysql_exception(self._data)
22:33:44 web.1            |   File "/workspace/development/frappe-bench/env/lib/python3.9/site-packages/pymysql/err.py", line 143, in raise_mysql_exception
22:33:44 web.1            |     raise errorclass(errno, errval)
22:33:44 web.1            | pymysql.err.OperationalError: (1054, "Unknown column 'link.parent' in 'field list'")

Any idea what is going on?
Thanks

1 Like

It fails because the following line:

query = sqlparse.format(query.strip(), keyword_case="upper", reindent=True)

(Line 34 in recorder.py)
Makes the word link in the FROM to be uppercase, so it ends something like this:

SELECT link.parent parent FROM `tabDynamic Link` LINK WHERE link.parenttype='Contact' AND link.link_doctype='Lead' AND link.link_name='{lead_name}';

To solve this maybe you can set the lower_case_table_names variable of MariaDB to 1.

This solution has not been tested.

EDIT: If this happen to a query made by you, you can use somethin like

FROM `tabDynamic Link` `link`

And it will not be converted to uppercase and still work

1 Like