As per MySQL Connector/J 8.0 Developer Guide, MySQL Connector/J 8.0 is highly recommended for use with MySQL Server 8.0 and 5.7. Please upgrade to MySQL Connector/J 8.0. https://dev.mysql.com/doc/connector-j/8.0/en/
In moqui we are having two database configuration one is for mysql8 and another one is mysql 5.7
IMO we should remove the mysql 5.7 database configuration (mysql) and rename the mysql8 to mysql.
My first thought on this is better to leave it as-is… it isn’t causing problems that I’m aware of, but making this change would break things for those using and relying on the current configuration. If it were a common use case or issues were resolved by using the MySQL 8 JDBC library with a MySQL 5 database, then I’d say let’s go for it and spread the word including the reason why the update is needed (or at least worth it).
Part of that reasoning is that MySQL 5 is older now, and certain features like lateral joins for higher performance queries on very large tables (to only get records needed, not try to join in all records unconstrained in related tables). Unless there is a very good reason to use MySQL 5, there are reasons to use MySQL 8 instead and so I don’t see much benefit in trying to muck around with the older database and newer JDBC driver.
On the other hand, I’m aware of one very large Moqui instance that until recently was using MySQL 5 (due to being a VERY large database that took some time and effort to migrate, making it a lower priority and such). For this and other instances like it a change in the default config would break updates, so it’s an example of why I would be hesitant to make this sort of change… which in a way is like any other change that is not backward compatible (which IMO means there must be a good reason, and there must be some communication effort including in ReleaseNotes.md and on the mailing list and forum to communicate about it).
On a side note, and you probably already know this Deepak so for others who might be looking at this over time:
Everything in the MoquiDefaultConf.xml file can be overridden by a MoquiConf.xml file in a component, or in the runtime conf XML file. This includes adding or modifying database configurations.