Quickly fix the MySQL MyISAM type SQLSyntaxErrorException for Hibernate

There’s nothing worse for a developer than an SQLException in Hibernate and JPA. And there’s nothing more annoying than when the SQLException happens before any of the persistence code even runs. But that’s exactly what happens if a developer sees the ‘type=MyISAM’ SQLSyntaxErrorException when you ask the JPA framework to build a database for you. The whole MySQL database creation script runs out of steam before it even starts.

The main issue at hand in this scenario is an old dialect class that’s incompatible with the MySQL installation. A developer can fix the ‘type=MyISAM’ SQLSyntaxErrorException issue with a dialect update. If a developer has a reference to the MySQLDialect and the MySQL database is newer than version 5, this SQLSyntaxErrorException problem will occur.

Update the persistence.xml with MySQL8Dialect

If a developer uses the persistence.xml file, the dialect is set as one of the properties:

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.2" >
  <persistence-unit name="jpa-tutorial">
    <properties>
      <property name="javax.persistence.jdbc.driver" 
         value="com.mysql.jdbc.Driver"/>
      <property name="javax.persistence.jdbc.url" 
         value="jdbc:mysql://localhost/hibernate_examples"/>
      <property name="hibernate.dialect" 
         value="org.hibernate.dialect.MySQL8Dialect" />
      <property name = "javax.persistence.schema-generation.database.action" value = "drop-and-create" />
    </properties>
  </persistence-unit>
</persistence>

The error will go away if you update the persistence.xml file and use the correct MySQL dialect for the installation.

Fix the Hibernate MySQL MyISAM type error with the correct dialect.

The MySQL dialect and JDBC code

If a developer uses the Hibernate SchemaExport class, or even just writes MySQL JDBC code, they’ll need to dig into the Java code to properly update the dialect:

    Map<String, String> settings = new HashMap<>();
    settings.put("connection.driver_class", 
       "com.mysql.jdbc.Driver");   
    settings.put("dialect", 
       "org.hibernate.dialect.MySQL8Dialect");
    settings.put("hibernate.connection.url",
       "jdbc:mysql://localhost/hibernate_examples");

Hibernate MySQLDialect classes

In the Hibernate 5.4 version distribution, developers can choose from the following list of MySQL dialects:

• MySQL55Dialect
• MySQL57Dialect
• MySQL57InnoDBDialect
• MySQL5Dialect
• MySQL8Dialect
• MySQLDialect
• MySQLInnoDBDialect
• MySQLISAMDialect

Choose the right dialect for your database, and the MySQL ‘type=MyISAM’ SQLSyntaxErrorException will disappear for good.