• From: Indonesia, Japan
  • Registered: 01/08/2014
  • Posts: 190

Topic: Sales group query error in mysql 8

In FA, sales group table name is "TB_PREFIX.groups".
When I setup FA without prefix, the table name is only "groups".
In mysql 8, groups is a reserved keyword so all query with table groups would throw an error.
It can be solved by adding backtick in the table name part in the query but it would change a lot in FA.

  • From: India, TN, Karur
  • Registered: 05/16/2014
  • Posts: 1,182

Re: Sales group query error in mysql 8

Yes. This will be one future coming issue for FA. @joe, before many people experience the issue rename the table to use it better way.

Subscription service based on FA
HRM CRM POS batch Themes

kvvaradha's Website

  • From: USA, IL, Plainfield
  • Registered: 12/22/2020
  • Posts: 28

Re: Sales group query error in mysql 8

When can we get this fixed?  I do not have prefixes.  Cannot edit groups and the customer branch edit/create is not functional as well

  • joe
  • Administrator
  • Offline
  • Registered: 03/15/2007
  • Posts: 5,087

Re: Sales group query error in mysql 8

We will have to backtick the group's table in SQL statements, I guess. Let me have a look at this.

Joe

  • joe
  • Administrator
  • Offline
  • Registered: 03/15/2007
  • Posts: 5,087

Re: Sales group query error in mysql 8

Hello again.

@barbarian

I have tried to fix this with backticks where ever the table is included in an sql statement.
It is ok on a table prefix, I have tested.
But what about without table prefix.

Will you replace the two downloaded files /sales/includes/db/sales_groups_db.inc and /includes/ui/ui_lists.inc and see if it works in Mysql 8 without table prefix.

Thanks in advance.

/Joe

Post's attachments

sales_groups_db.inc 5.2 kb, 2 downloads since 2022-09-06 

ui_lists.inc 88.3 kb, 1 downloads since 2022-09-06 

You don't have the permssions to download the attachments of this post.
  • joe
  • Administrator
  • Offline
  • Registered: 03/15/2007
  • Posts: 5,087

Re: Sales group query error in mysql 8

@bradlanier

You are of course also welcome to test this. Please replace the files on your server and report back. Thanks in advance.

Joe

  • joe
  • Administrator
  • Offline
  • Registered: 03/15/2007
  • Posts: 5,087

Re: Sales group query error in mysql 8

I have now installed Mysql 8.0.27 and tested and everything works fine with the two fixed files. Both without table pref and with.

I will now push the files to stable repository

/Joe

  • From: USA, IL, Plainfield
  • Registered: 12/22/2020
  • Posts: 28

Re: Sales group query error in mysql 8

Tested and it works on MySQL 8.0.30 Ubuntu. Thank you!