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.
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.
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.