Test and review/fix issues with 3scale deployment with MySQL database 8.x upgrade

Hi,

In the https://issues.redhat.com/browse/THREESCALE-6790 issue ongoing work on the deployment side of 3scale (with 3scale-operator) has been being done about upgrading MySQL database version from 5.7 to 8.x.

At the moment of writing this, it seems that from the deployment point of view/side it seems like it should be feasible (with some changes at operator level that have to still be implemented) and tests upgrading the MySQL server using an internal database have been performed and the results look generally look, with some warnings/errors that have been observed that should be reviewed/addressed/fixed first.

To fully conclude that we can go forward with the upgrade of MySQL database version from 5.7 to 8.x we think that:

• We (operator team) should test the upgrade together with the System team so verifications at application level are performed. Ideally we should test it with a real dataset and/or at least exercise some relevant functionalities that end up modifying/using the database so we verify that the functionality at application level looks good.
• System team should review the observed warnings and errors when the database was upgraded in the tests made by the operator team. You can see them in the following comment: https://issues.redhat.com/browse/THREESCALE-6790?focusedCommentId=15885577&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-15885577 . There's a specific observed error "2021-03-03T10:37:17.830156Z 2 [ERROR] [MY-010520] [Server] Invalid (old?) table or database name 'lost+found' " that we think is not really an issue as the server starts correctly so don't worry about that specific one.
• System team should review the following error that prevents 3scale from being deployed successfully with the new MySQL version https://issues.redhat.com/browse/THREESCALE-6790?focusedCommentId=15888216&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-15888216. This seems a client-level issue

I also recommend reading the comments on the related Jira issue

Please contact me if you have any doubts and to advance the work on this (do the testing together, etc.)

Dev Notes

• The caching_sha2_password is incompatible with RHEL 7. We need to set --default-authentication-plugin=mysql_native_password on the server

Concerns

Supporting another Database version without removing support of the former one is putting a toll on System team. Lack of resources

We already support:

• Oracle 19c (needs to be removed) 18c
• Postgres 10.14
• Mysql 5.7

Adding 8.0 without removing 5.7 will overwhelm our current development workflow. Tests will need to be run on both versions of mysql for each commit to master. Also, this requires that developers know both 8.0 and 5.7.

We need to have already migrated the SaaS database to 8.0 first, and this is estimated as hours of downtime and days of preparations so that we don't have more work supporting 5.7 in SaaS and 8.0 in on-prem. Best to move this to 2.12.