Sugar 12.0: php, sql and elastic search

Hi Emelie Garton

from a customer perspective, there are usually announcements in the "Product Updates and Releases" side of the Club that will point you to Release Notes, there you will also find Supported platforms. v11 already supports MySQL v8 so at my organization  we're working to upgrade to that prior to the release of v12.

 https://sugarclub.sugarcrm.com/explore/p/releases

The Developer Builds section of the Club also provides partners with pre-releases that you can install on your development instance prior to the actual release for those who want a head start. Note that you cannot upgrade from a pre-release to a full release, you'll have to start over with a fresh copy of your production system to run your upgrade, but it gives you a lot of insight as to what to expect - caveat of course is that there may still be some bugs that need to be ironed out, so not everyone likes that...

https://sugarclub.sugarcrm.com/dev-club/developer-builds/

FrancescaS

Hi Francesca Shiekh,

if it works for you, we would be very interested on your experiences regarding the upgrade to MySQL 8, since we were having some problems that prevented us from going through with this, although 11.0.x was already also supposed to support it.

We experienced this yet unresolved bug and ended up not doing the upgrade for the time being: https://portal.sugarondemand.com/#supp_Bugs/87739

Thanks in advance!

Julia

My sysadmins are working on new servers with MySQL8 and we've not tested with the application yet, it's a work in process. I'll let you know what we find. 

Thank you for pointing out that bug, I'll be on the lookout.

WRT Bug 87739 we came across the same bug.

As you know MySQL >=8.0.19 no longer supports setting of integer display width

It turns out that the "length" of an integer column doesn't mean anything in MySQL.
A column of int(1) is the same as int(255) or int(50). They are all a fixed-size, 32-bit integer data type.
They support the same minimum and maximum value.

The mismatch we are seeing is not due to any issues with the Database, but rather with the fieldDef in the sugar application. When the comparison between the DB and the fieldDef is performed the system expects the len to match but there is no corresponding setting in MySQL >=8.0.19 at the DB level.

Executing the resulting alter table is therefore useless, as all it is doing is trying to match the DB to the fieldDef when the problem, in the first place, is the fieldDef not the DB table.

The ideal solution is to remove the len parameter from the fieldDef of all int field types.
Changing the len parameter to nothing in Studio does nothing, because as soon as you save it defaults it back to 255

A very cheeky workaround is to modify the "massageFieldDef" in /include/database/DBManager.php to set the len to an empty string for fields of type int.

Just to test my theory, at the very end of the function I added:

//custom
if($fieldDef['type'] === 'int'){
  $fieldDef['len']='';
}
//end custom

This eliminates the Mismatch message from the QRR screen, but does NOT fix the fieldDef.

I'm not 100% comfortable putting this "hack" in my production server but I thought I would pass this along just as an FYI.

Francesca

WRT Bug 87739 we came across the same bug.

As you know MySQL >=8.0.19 no longer supports setting of integer display width

It turns out that the "length" of an integer column doesn't mean anything in MySQL.
A column of int(1) is the same as int(255) or int(50). They are all a fixed-size, 32-bit integer data type.
They support the same minimum and maximum value.

The mismatch we are seeing is not due to any issues with the Database, but rather with the fieldDef in the sugar application. When the comparison between the DB and the fieldDef is performed the system expects the len to match but there is no corresponding setting in MySQL >=8.0.19 at the DB level.

Executing the resulting alter table is therefore useless, as all it is doing is trying to match the DB to the fieldDef when the problem, in the first place, is the fieldDef not the DB table.

The ideal solution is to remove the len parameter from the fieldDef of all int field types.
Changing the len parameter to nothing in Studio does nothing, because as soon as you save it defaults it back to 255

A very cheeky workaround is to modify the "massageFieldDef" in /include/database/DBManager.php to set the len to an empty string for fields of type int.

Just to test my theory, at the very end of the function I added:

//custom
if($fieldDef['type'] === 'int'){
  $fieldDef['len']='';
}
//end custom

This eliminates the Mismatch message from the QRR screen, but does NOT fix the fieldDef.

I'm not 100% comfortable putting this "hack" in my production server but I thought I would pass this along just as an FYI.

Francesca

Alex Nassi any information on this discrepancy with MySQL v8?
Is it resolved in v12?

And if so, is there a patch for v11 so we can upgrade the DB before the conversion to Ent and upgrade to v12?

thanks,
Francesca

Julia Weinhold

There's a Hot Fix for theMySQL v8 Int issue!

HotFix_87739_11.0.2-11.0.3_(ALL)_v1.0

Francesca Shiekh - It looks like we have a fix for this planned for an upcoming release. I checked in with Support and it looks like they already sent a hotfix package over to you via your support case open for this issue. 

Julia Weinhold - I'll have the person assigned your case (filed by Matthias Wolle) look into this as well to make sure we can send you the fix for #87739. 

Hi Alex Nassi, thanks a lot, that would be highly appreciated :)

Ask support for the hotfix, it works perfectly for us.

Francesca Shiekh said:

HotFix_87739_11.0.2-11.0.3_(ALL)_v1.0

Thank you Alex Nassi the hotifx works great!

Julia Weinhold I wanted to confirm that I received a message from one of our Support leads letting me know that the hotfix was supplied to you via your case. I hope it helps!