ERPNext.com Frappe Cloud Support Partners Foundation Frappe School

Error when searching for string

In the global search box, Users can search for existing doctypes and select them from the dropdown without issue. However, if they just type a string (e.g. ‘test’) and hit enter, the following error is thrown…

pymysql.err.ProgrammingError: (1064, “You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'rank\n\t\t\t\tFROM __global_search\n\t\t\t\tWHERE doctype IN (‘Customer’,‘Supplier’,‘I’ at line 1”)

I’ve tried the ‘Reset’ button in Global Search Settings but that didn’t help. I seem to recall seeing somewhere that Global Search depends upon a mysql/mariadb feature being enabled but I can’t find reference to that now. This issue is happening consistently across multiple sites in a helm deployment.

Thanks in advance for any insight.

Friendly bump. Still scratching my head here.

It may help to say the operating environment, version of ERPNext , if in production or development mode and if this happens on a fresh install, before modification or customization.

This is erpnext v13.7.0 in production mode and the issue definitely happens on fresh installs. A newly deployed site exhibits this behavior. I think it’s something with the database configuration.

Mysql is v8.0.25

No problem here on a upgrade 12 to 13 virtualbox image:

erpnext 13.7.1
frappe 13.7.0
mysql Ver 15.1 Distrib 10.2.39-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

How are you installing frappe etc?

To install frappe/erpnext, I’m using the helm chart here: https://helm.erpnext.com/. To install mysql, I’m using the bitnami helm chart: https://bitnami.com/stack/mysql/helm

I don’t see database problems otherwise. It’s only the global search that’s giving an issue. It sounds like some versions of mysql 8.0 don’t support ‘rank’ but I’ve now upgraded to 8.0.26 and it should be supported there.

Would the mariadb chart be worth a try? https://artifacthub.io/packages/helm/bitnami/mariadb