SugarCRM and MYSQL 8.0

I am facing an issue after upgrading a test instance of SugarCRM from MYSQL 5.7 to MYSQL 8.0 (in place upgrade after ubuntu server 18.0.4 to 20.0.4 upgrade)

Table 'job_queue' from one of the SELECTs cannot be used in global ORDER clause

I have configured the sql_mode as: sql_mode = "NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

Which has disabled full group by, but I have not faced the error message above before.

My previous MYSQL 5.7 sql_mode line will not allow MYSQL 8 to start so I modified it as above for testing

The instance I am testing is version 11.x, which is listed as supporting MYSQL 8.0

Have I missed a step in configuring MYSQL 8.0 to work correctly with SugarCRM?

Parents

0 Robert Telonis over 1 year ago

I should add, I did not restore the database after upgrade but allowed the upgrade process to run and perform mysql_upgrade scripts. I did stop the server before upgrade
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

Reply

0 Robert Telonis over 1 year ago

I should add, I did not restore the database after upgrade but allowed the upgrade process to run and perform mysql_upgrade scripts. I did stop the server before upgrade
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

Children

+1 Rafael Fernandes over 1 year ago in reply to Robert Telonis

Hi Robert Telonis,

If you are on MySQL 8.0.31+, you are running into a known issue with that version, apparently, MySQL has changed the way they handle parenthesis in queries.

We have fixed that and released on 12.3 and backported to 11.0.5 and 12.0.2. (see https://sugarclub.sugarcrm.com/dev-club/w/dev-tutorials/797/sugar-12-3-q1-2023-customization-guide)

Hope this helps,

SugarCRM | Principal Developer Advocate
Cancel
Vote Up +1 Vote Down

Sign in to reply

Verify Answer

Reject Answer

Cancel