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

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();

    }
}

Solution 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

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

Solution 2:

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