Invalid syntax error "type= MyISAM" in DDL generated by Hibernate

Question

I am getting this error for my Java code

   Caused by :`com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException`: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB` server version for the right syntax to use near 'type = `MyISAM`' at line 1

This is the query passed by hibernate:

Hibernate: create table EMPLOYEE (emp_id integer not null, FNAME varchar(255), LNAME varchar(255), primary key (emp_id)) type=MyISAM

I have looked at all questions related to this error. But in all that questions the user itself is passing query "type = MyISAM" so they can change "type" to "engine", but here hibernate is responsible for creating table, so I don't understand where the mistake is, and how I can fix it.

This is my configuration file:

<hibernate-configuration>
 <session-factory >
 <property name="hibernate.hbm2ddl.auto">create</property>
  <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
  <property name="hibernate.connection.url">jdbc:mysql://localhost/servletcheck</property>
  <property name="hibernate.connection.username">root</property>
  <property name="hibernate.connection.password"> </property>
  <property name="hibernate.dialect"> org.hibernate.dialect.MySQLDialect</property>
  <property name="hibernate.show_sql">true</property>
  <mapping resource="Employee.hbm.xml"/>
 </session-factory>
</hibernate-configuration>

This is my mapping file:

<hibernate-mapping>
    <class name="first.Employee" table="EMPLOYEE">
        <id name="id" type="int">
            <column name="emp_id" />
            <generator class="assigned" />
        </id>
        <property name="fname" type="java.lang.String">
            <column name="FNAME" />
        </property>
        <property name="lname" type="java.lang.String">
            <column name="LNAME" />
        </property>
    </class>
</hibernate-mapping>

This is my class file:

public class StoreData {
    public static void main(String[] args) {
        Configuration cfg=new Configuration();
        cfg.configure("hibernate.cfg.xml");
        SessionFactory factory=cfg.buildSessionFactory();
        Session session=factory.openSession();
        org.hibernate.Transaction t=session.beginTransaction();
        Employee e=new Employee();
        e.setId(1);
        e.setFname("yogesh");
        e.setLname("Meghnani");
        session.persist(e);
        t.commit();

    }
}

Answer 1

The problem is that - in Hibernate 5.x and earlier - the dialect org.hibernate.dialect.MySQLDialect is for MySQL 4.x or earlier. The fragment TYPE=MYISAM that is generated by this dialect was deprecated in MySQL 4.0 and removed in 5.5.

Given that you use MariaDB, you need to use (depending on the version of MariaDB and - maybe - the version of Hibernate) one of:

• org.hibernate.dialect.MariaDBDialect
• org.hibernate.dialect.MariaDB53Dialect
• or higher versions (e.g. org.hibernate.dialect.MariaDB106Dialect)

If you are using MySQL, or if the above two dialects for MariaDB don't exist in your version of Hibernate:

• org.hibernate.dialect.MySQL5Dialect
• org.hibernate.dialect.MySQL55Dialect
• org.hibernate.dialect.MySQL57Dialect
• org.hibernate.dialect.MySQL8Dialect
• or variants of these dialects (e.g. org.hibernate.dialect.MySQL57InnoDBDialect)

NOTE: With Hibernate 6, you should once again use MySQLDialect or MariaDBDialect, as Hibernate 6 dialects will configure themselves based on the actual connected version.

Answer 2

It's a Dialect related issue, instead of org.hibernate.dialect.MySQLDialect you can go with org.hibernate.dialect.MySQL5Dialect. It will work happily.

Answer 3

This error may arise with increasing frequency as more organizations move to install MySQL 8.x while their Hibernate code is still using the old version 5 Dialect.

The latest Hibernate 5.4 distribution as of 6/6/2020 includes the following MySQL dialects:

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

Current Hibernate 5.4 MySQL dialects

Here's the dialects in the core jar file filtered for MySQL. Use the right one and that type=MyISAM MySQL exception will be a thing of the past.

Answer 4

Before MySQL 4.x.x version,TYPE MYISAM engine is used to store tables but in MySQL 5.x.x or later version MySQL is used ENGINE = MYISAM to store tables. e.g

In MySQL 4.x.x or < 4.x.x

CREATE TABLE t (i INT) TYPE = MYISAM;

In MySQL 5.x.x or > 5.x.x

CREATE TABLE t (i INT) ENGINE = MYISAM;

Now lets talk about hibernate,

In hibernate you must use given below dialect

MySQL <= 4.x.x

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

MySQL>=5.x.x.

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

It will work fine.

Answer 5

I know this is a old post but I am sharing a different experience. I was also seeing the same exact exception but i had MySQL 8 with Springboot+ JPA+ Hibernate .

My dialect looks like

spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL8Dialect

But I was still seeing the issue. After some troubleshooting I noticed that the issue was in the schema sql where I had the table creation script in all Caps and it was expected to have it in small. For example

CREATE TABLE  item_catalog

instead of

CREATE TABLE  ITEM_CATALOG

After changing the case in the schema sql to lower case table name, It worked fine.

Answer 6

MySQL dropped MyISAM as the default engine. Now it uses InnoDB. You can verify this using SequelPro by clicking on any table, and then looking in the bottom quadrant for the Table Information:

And at the top of your window to see the MySQL Version:

Using the above information, I know that I need the dialect:

org.hibernate.dialect.MySQL5InnoDBDialect

I can set the dialect in my application.properties:

Answer 7

It is possible to find the available dialects, no matter which database you need a dialect that works on your system.

> Project> Libraries> hibernate-core> org.hibernate.dialect

Then you will see a package of several "dialect" classes for different databases and you can test one that works. This case is for hibernate.