Consider updating the default Hibernate dialect for MySQL databases

[larsgrefer]

Please consider updating the default Hibernate dialect for MySQL databases.

org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter#determineDatabaseDialectClass currently returns org.hibernate.dialect.MySQL5Dialect for MySQL databases, while the following newer versions are available:

• org.hibernate.dialect.MySQL55Dialect (MySQL 5.5 was first released in 2010)
• org.hibernate.dialect.MySQL57Dialect (MySQL 5.7 was first released in 2014)
• org.hibernate.dialect.MySQL8Dialect (MySQL 8.0 was first released in 2018)

As you can see, the default dialect selected by Spring Framework 5.1.3 targets a MySQL version which is over 9 years old. Therefore I propose to update the default dialect for MySQL.

Background

We're running a Spring Boot 2.1.2 application on MySQL 8 which leads to SQL errors when using the default dialect selected by Spring. Manually updating the dialect to at least MySQL55Dialect resolves these issues.

This is the line in question:

spring-framework/spring-orm/src/main/java/org/springframework/orm/jpa/vendor/HibernateJpaVendorAdapter.java

Line 192 in 7a77e83

case MYSQL: return MySQL5Dialect.class;

[larsgrefer]

According to what @jhoeller said in #19820 (comment) I propose to update the default dialect to org.hibernate.dialect.MySQL8Dialect

[jhoeller]

I'm afraid we can't enforce a newer dialect variant at this point since MySQL55Dialect and higher have only been introduced in Hibernate ORM 5.2/5.3, whereas our JPA support needs to remain compatible with Hibernate ORM 5.1 as well (since that used to be the version in JBoss EAP for several years). Once we raise the baseline to JPA 2.2+, we can easily require Hibernate ORM 5.3+ and MySQL 5.7+.

As for MySQL55Dialect specifically, it seems that all it changes is InnoDB over MyISAM by default. That's a viable assumption in recent years, of course, but quite a significant change that we can't easily sneak into a minor release. That said, there's nothing wrong with selecting a specific Hibernate dialect in an application setup; Spring's database enum is only meant as a starting point to begin with.

[larsgrefer]

I don't expect this to be changed in 5.1.x, but something like this could go into 5.2:

try {
   return Class.forName("org.hibernate.dialect.MySQL8Dialect");
catch (ClassNotFoundException e) {
    return MySQL5Dialect.class;
}

It's perfectly fine for us to just put spring.jpa.database-platform=org.hibernate.dialect.MySQL8Dialect into our application.properties files, but from a Spring Boot point of view it seemed strange to me to get a 9 years old dialect by default.

[jhoeller]

The problem there is the upgrade experience: just by updating Hibernate ORM e.g. from 5.2 to 5.3, you'll get a different MySQL dialect against the InnoDB storage engine, against the very same Spring Framework version and the same default settings. Such an effect would be easier to swallow if we could require Hibernate ORM 5.3+ to begin with... so that the experience would be consistent against the same framework version at least, i.e. for all supported Hibernate versions against a particular Spring version.

That said, I wonder whether explicitly specifying a database dialect is a good idea in general. Hibernate's default dialect resolution usually gets it right, including version-specific detection. Simply not specifying a database type to begin with would be a sane default strategy for modern-day Hibernate setups.

[jhoeller]

@snicoll, @wilkinsona, any thoughts from the Boot side about this? It seems like a rather unfortunate maintenance headache to have to select version-specific Hibernate dialects for the Database enum. IIRC we even meant to deprecate the Database enum to begin with but decided against it (mostly for Boot).

Hibernate's default runtime resolution is smart enough to select the right dialect version these days, but once a dialect has been configured, all of this is overridden. As far as I can see, we can't easily say "MySQL" and get runtime resolution between the MySQL dialect versions; this only kicks in if we don't specify any dialect at all, even letting it detect MySQL itself. See Hibernate's StandardDialectResolver which iterates all database entries in their enum, detecting database vendors and versions in one pass.

[snicoll]

IIRC we even meant to deprecate the Database enum to begin with but decided against it (mostly for Boot).

What was the rationale behind this decision. As far as I can see, the Database property in Spring Boot was added when the feature was introduced in Spring Boot and wasn't really touched since then.

If a Database is not specified by the user, we detect it based on the DataSource via our DatabaseLookup utility. I've seen several contradictory reports over the past couple years where some users where expecting us to detect the Database so that the proper dialect would be set by default and others complaining that things could be smarter.

If the general assumption is that we shouldn't attempt to set a database and/or a database platform we can see how we can deprecate these elements and remove them ultimately but I want to make sure we've considered all side effects before doing so.

[snicoll]

See also spring-projects/spring-boot#15342

[snicoll]

For the record, I've created an issue in Spring Boot to let Hibernate drives the dialect: spring-projects/spring-boot#16172

[scottyan19]

I wonder why SpringBoot 1.5.x will select innodb as its default storage engine, but SpringBoot 2.x select MyISAM as default, what's the point?

[trajano]

The issue @snicoll brought up is closed but I found that it does not work as of yet and wrote it in the comment spring-projects/spring-boot#16172 (comment) though it should be there for 2.2, for the time being I have added my workaround in the issue as well

[NathanQingyangXu]

Not sure whether it is relevant, but recently Hibernate fixed a related issue: https://hibernate.atlassian.net/browse/HHH-13910