12.3 on local vagrant instance 500 error

Robert Thomas, did you install your own box instead of using vagrant box?


Right now I have this 500 Internal Server Error in the account module if I am creating or updating a record. But it works fine in the other module.

Logs

Tue Apr 25 01:09:47 2023 [1862][8a99dcd0-4aad-11e9-8b20-06f590b7be66][FATAL] An exception occurred while executing a query: 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 = ?, first_name = ?, last_name = ? WHERE (id = ?) AND (deleted = ?)' at line 1
Tue Apr 25 01:09:47 2023 [1862][8a99dcd0-4aad-11e9-8b20-06f590b7be66][FATAL] An exception happened: (500: db_error) Database failure. Please refer to sugarcrm.log for details.

Regards,

Clinton

Hi Clinton Canarias,

We did fix a compatibility issue with MySQL 8.0.31 which has updated how they (MySQL) deal with parenthesis. It has been rolled out to all our cloud customers and backported to onpremise releases.

In the vagrant box  sugarcrm/php80es716 we are running MySQL 8.0.28 which doesn't have any issues with parenthesis as for sugarcrm/php82es84 that particular fix is required due to the fact we run MySQL 8.0.32. 

Can you check if you have this code in <sugar>/data/SugarBean.php.

$final_query_rows = '(' . $parentbean->create_list_count_query($tmp_final_query, $parameters) . ')';
if (self::canApplyParentheses($GLOBALS['db'])) {
    $final_query = '(' . $tmp_final_query . ')';
} else {
    $final_query = $tmp_final_query;
}

Hi Rafael Fernandes,

Yes, that line of code is available on my SugarCRM.

By the way, correct me if I`m wrong. This issue should not appear on vagrant sugarcrm/php80es716 since there is no issue with the parenthesis? If yes, I am currently using the sugarcrm/php80es716 in my local instance. But I got that issue on updating or Creating records in Account Module.

Hi Clinton Canarias,

You are correct, you should not have any issues with that vagrant version (if you run mysql --version) you'll see the version you are running on.

It doesn't seem to be a compatibility issue. That error is coming from MySQL itself which we can try to focus on at this point.. can you double check if that table exists (did the DDL fail somehow? permissions are ok? connections?)

Hello Rafael Fernandes, I think the permissions and connection are ok because I can create, update, and do delete records in Leads, Contact, and Opportunity Modules except in the Account Module (Unable to create and Update but it can delete records)

This is your mysql and php version in sugarcrm/php80es716 vagrant instance, see bellow screenshot:

Hi Rafael Fernandes,

It is working now. I installed a new instance using sugarcrm/php74es70 from your website.

Regards,

Clinton

Actually, that is incorrect, you are showing the PHP CLI version, but actually Apache is using PHP 8.0 not 8.1. The way you get the PHP version that Apache uses is to check phpinfo by going to http://localhost:8080/phpinfo.php on the Vagrant.

Hi Robert Thomas, btw I don`t have any problem if I am using the vagrant ugarcrm/php74es70. I only using the sugar vagrant instance provided by the Sugar for my local machine. I don`t manually install the LAMP Stack since this is for development purposes only.

Ok just wanted to make you aware that PHP-CLI and PHP-Apache can be separate version and if so you can have problem with Sugar Scheduler not working correctly.

Hi Robert Thomas, no worries. there is no issue with my cron scheduler and it's working fine.

Anyway, I appreciate your help.

Regards,

Clinton