What is the MariaDB dialect class name for Hibernate?

Question

In Hibenate I am using MariaDB but I couldn't find the dialect class name of MariaDB .

In Hibernate, MySQL5 dialect's name is

<property name="hibernate.dialect">org.hibernate.dialect.MySQL5Dialect</property>

For Oracle 10g

<property name="hibernate.dialect">org.hibernate.dialect.Oracle10gDialect</property>

What is the dialect class name for MariaDB?

Answer 1

Very short answer

The current dialects as of this writing are:

• org.hibernate.dialect.MariaDB102Dialect for MariaDB server 10.2
• org.hibernate.dialect.MariaDB103Dialect for MariaDB server 10.3 and later, provides sequence support.
• org.hibernate.dialect.MariaDB10Dialect for MariaDB server 10.0 and 10.1
• org.hibernate.dialect.MariaDB53Dialect for MariaDB server 5.3, and later 5.x versions.
• org.hibernate.dialect.MariaDBDialect for MariaDB server 5.1 and 5.2.

Short answer

When using a MariaDB server, you should use MariaDB Connector/J and MariaDB Hibernate dialects, not the MySQL ones. Even though MariaDB was created as a drop-in replacement and even though basic features will likely work when using the MySQL versions of those, subtle problems may occur or you may miss certain features.

A complete list of available MariaDB dialects are currently not mentioned in the Hibernate User Guide, but in the Hibernate JavaDoc. Depending on your MariaDB server version, you should select the corresponding dialect version. The current dialects as of this writing are:

• org.hibernate.dialect.MariaDB102Dialect for MariaDB server 10.2
• org.hibernate.dialect.MariaDB103Dialect for MariaDB server 10.3 and later, provides sequence support.
• org.hibernate.dialect.MariaDB10Dialect for MariaDB server 10.0 and 10.1
• org.hibernate.dialect.MariaDB53Dialect for MariaDB server 5.3, and later 5.x versions.
• org.hibernate.dialect.MariaDBDialect for MariaDB server 5.1 and 5.2.

Note that for detailed usage information, you'll sometimes have to look in dialect source codes. (There are non-JavaDoc usage information comments in some dialect sources.)

If you want to change or explicitly mention the storage engine for the MariaDB dialect, you can use the storage_engine Hibernate variable. For example: hibernate.dialect.storage_engine = innodb. IMO, you should do this explicitly, because the default can change when switching to a different MariaDB server version.

If you're using a MariaDB server older than 10.1.2 (which doesn't support fractional seconds), then you may want to provide the parameter useFractionalSeconds=false to the JDBC URL, otherwise MariaDB Connector/J will not truncate timestamps internally, which can cause time comparison problem when those values are using in comparison queries (even when using plain JDBC), which can cause Hibernate versioning problems and optimistic locking problems for temporal types.

Long answer

The MariaDB dialect for Hibernate (5.3 as of this writing) is mentioned in the Hibernate User Guide. The mentioned dialect "short names" followed by remarks are:

• MariaDB: Support for the MariadB database. May work with newer versions

• MariaDB53: Support for the MariadB database, version 5.3 and newer.

However, a complete list of the available official MariaDB dialects can be found in the Hibernate JavaDoc. Which currently lists:

• org.hibernate.dialect.MariaDB102Dialect for MariaDB server 10.2
• org.hibernate.dialect.MariaDB103Dialect for MariaDB server 10.3 and later, provides sequence support.
• org.hibernate.dialect.MariaDB10Dialect for MariaDB server 10.0 and 10.1
• org.hibernate.dialect.MariaDB53Dialect for MariaDB server 5.3, and later 5.x versions.
• org.hibernate.dialect.MariaDBDialect for MariaDB server 5.1 and 5.2.

Each dialect successor inherits the settings from the previous dialect version. So the inheritance hierachy for MariaDB is: MariaDB103Dialect > MariaDB102Dialect > MariaDB10Dialect > MariaDB53Dialect > MariaDBDialect > MySQL5Dialect > MySQLDialect > Dialect

MariaDB was designed as a drop-in replacement for MySQL. But the databases are likely going to diverge as time goes by. Most basic features probably work without problems, allowing you to swap Connector/J clients (MariaDB client on MySQL server and vice versa), and allow you to swap dialects (MySQL dialect on MariaDB client and vice versa). But there are subtle differences that may cause unexpected problems. For example, the MySQL Connector/J client contains hardcoded checks for the server version, which will fail when using a MariaDB server, causing some features to be disabled in the client, such as the MySQL sendFractionalSeconds client parameter. This will cause fractional seconds to be disabled, so then the fractions will be truncated in the MySQL client but not in the MariaDB client. (This may even lead to optimistic locking problems when using versioning with date/time types in combination with non-max precision SQL date/time types. In these cases, use the max precision of 6.)

Also, the MariaDB dialect are expected to provide specific functionality for MariaDB: http://in.relation.to/2017/02/16/mariadb-dialects/

In time, we will add new Dialects based on newer capabilities introduced by MariaDB.

...

If you are using MariaDB, it’s best to use the MariaDB-specific Dialects from now on since it’s much easier to match the MariaDB version with its appropriate Hibernate Dialect.

And https://hibernate.atlassian.net/browse/HHH-11457 says:

since MySQL and MariaDB have gone in different directions, we might want to provide MariaDB Dialects as well.

For instance, it's not very intuitive for a Hibernate user to figure out that they need to use the MySQLInnoDb57Dialect to handle Timestamps with microsecond precision which have been available since MariaDB 5.3:

The Hibernate User Guide doesn't provide all usage information about how to use the dialects. Even the User Guide combines with the API docs may not be enough. Sometimes you'll have to look in the source codes for usage information. For example, MariaDB53Dialect.java contains hidden non-JavaDoc comments that may be useful.

Previously, to select a MySQL storage engine, such as MyISAM or InnoDB or default, you could switch between for example MySQL57InnoDBDialect and MySQL57Dialect. But they refactored the MySQL dialect hierarchy starting from Hibernate 5.2.8, as mentioned in a Hibernate blog post. Note that to select a storage engine, you should use a Environment Variable or System Property: hibernate.dialect.storage_engine. For example: hibernate.dialect.storage_engine = innodb.

XtraDB was the default MariaDB storage engine for MariaDB 10.1 and earlier, but since 10.2 it's InnoDB. So there may be cases that you want to explicitly mention the storage engine that Hibernate selects, so then you'll have to use the storage_engine variable. Info about the storage_engine variable (which isn't mentioned in the User Guide), can be found in the source of AvailableSettings.java.

If you're using a MariaDB server older than 10.1.2 (which doesn't support fractional seconds), then you may want to provide the parameter useFractionalSeconds=false to the JDBC URL, otherwise MariaDB Connector/J will not truncate timestamps internally, which can cause time comparison problem, which can cause Hibernate versioning problems and optimistic locking problems for temporal types.