Hibernate 7.1 with MySQLDialect broke TiDB use

[plevart]

Since Hibernate 7.1 (a java ORM library) the MySQLDialect (a pluggable mechanism to support various databases) started using the syntax SELECT ... FOR UPDATE OF alias. Previously it has been using SELECT ... FOR UPDATE OF table instead. MySQL supports the new usage but TiDB doesn't. So applications that migrate to Hibernate 7 and use MySQLDialect have a problem. For example, Keycloak which doesn't officially support TiDB has been running just fine with its MySQL support by using MySQLDialect for Hibernate. At least during runtime. There were a few quirks when upgrading the database schema during application upgrades which required manual intervention now and then, but during runtime, there were no problems. Until now:

keycloak/keycloak#41897

There is a TiDBDialect (subclass of MySQLDialect) in pre Hibernate 7, but in Hibernate 7 It has been moved to a community maintained package hibernate-community-dialects which means it is not officially supported any more by Hibernate developers. The TiDBDialect was created on 7/20/21 at 6:29 PM at which time, the latest release of TiDB was v5.0.3:

https://github.com/pingcap/tidb/releases/tag/v5.0.3

The TiDBDialect announces itself as supporting version 5.7 of MySQL. This has some repercussions on in the way the TiDBDialect deals with differences between TiDB 5.0.3 and MySQL 5.7. In the meantime, TiDB version has grown to 8.5.3 which supports many more MySQL 8 features than at the time TiDBDialect was written, while MySQLDialect has dropped support for MySQL < 8. So it makes sense to use MySQLDialect directly with latest TiDB versions. Well, at least until Hibernate 7.1 which broke this practice.

There are multiple options in how to solve this for applications:

• application can use community-maintained TiDBDialect - this works but the dialect seems to be outdated. It works in the TiDB 5.0.3 mode. This was probably the reason why it was moved to community-maintained package. Maybe it is time to create new modern TiDBDialect based on MySQLDialect which would announce the support for MySQL version 8 in the first place and add or modify features that are still different from MySQL 8 in current versions of TiDB. Is there any interest from TiDB community or PingCAP to maintain such Dialect? It would be nice if it was re-introduced to core Hibernate module as such while the community-maintained dialect would be renamed to a Legacy variant.

• short term solution may be for applications to create a self-maintained subclass of MySQLDialect which would patch just current incompatibilities between MySQL 8 and newer TiDB for the purpose of usage in the application only. In case of Keycloak, this would currently amount in overriding a single method with:

	protected boolean supportsAliasLocks() {
		return false;
	}

• TiDB itself could add support for SELECT ... FOR UPDATE OF alias

So what does TiDB community think of this situation and does PingCAP have anything to say?