How to setup Acumatica 2020R2 + MySQL 8.0?

Hi @harishd 

Did you get an answer for this in the end? I’ve the same issue, but mine appeared after upgrading my DB from v5.7 to a v8.x (v8.0.26) as a result of a notification from AWS regarding a security patch that needed applying.

Had a few additional tweaks to make to the Parameter file as well (set information_schema_stats_expiry
and explicit_defaults_for_timestamp to 0), but still no joy …

Not sure this is a rights thing as the database is accessible using MySQL Workbench from the same instance running the EC2 and using the same credentials it had before (when it was a v5.7 database). Didn’t want to raise a support ticket on it if you had an answer.

Thanks now,

Graham

@harishd I’ve traced out some more information on this - the Acumatica install script tries to SET the information_schema_stats_expiry to 0 and the log file shows this is where the issue lies. The AWS RDS user created for the database doesn’t have SUPER rights, but you can’t set this because the RDSADMIN root user for the database server doesn’t allow you to. My guess is that the install script doesn’t check whether parameter information_schema_stats_expiry is on or off, and just assumes that it has to SET it which causes the error we see. It is entirely possible under AWS to copy the default mysql8 parameter group and edit this setting, then set the MySQL 8 DB instance to load with the copy (which I’ve done). However the fact that this makes no difference leads me to the conclusion above - the Acumatica install script isn’t checking if the parameter needs setting, its simply assuming it will.

If this was a wholly owned RDS server (i.e. one you’d setup yourself), it would be possible to add the SUPER flag to the account the Acumatica script is using, but as its run by AWS the DBA is AWS and is locked - we as customers don’t have access (which makes perfect sense to me as we “as customers” are asking AWS to do the DBA heavy lifting for us).

Would you mind looking in your install log file and checking if you see the same behaviour? You’ll find it at C:\Program Files\Acumatica ERP\log.txt (or maybe C:\Program Files (x86)\Acumatica ERP\log.txt)

If anyone from Acumatica reads this ( @Aliya ?) , it would be good to know if SaaS uses MSSQL or MySQL (I’m guessing its MSSQL) as, if I’m on the right track, they would have seen the same issue on their MySQL v8 install.

If I am on the right track I’ll raise a support ticket asking for the install script to be modified to first check if information_schema_stats_expiry and explicit_defaults_for_timestamp are already SET to ‘0’ (as required), in which case don’t run the SET commands in the script. 

All the best,

Graham