Invalid syntax error "type= MyISAM" in DDL generated by Hibernate
JavaMysqlHibernateMariadbJava Problem Overview
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();
}
}
Java Solutions
Solution 1 - Java
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 - Java
Its a Dialect related issue, instead of org.hibernate.dialect.MySQLDialect
you can go with org.hibernate.dialect.MySQL5Dialect
. It will work happily.
Solution 3 - Java
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.
Solution 4 - Java
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.
Solution 5 - Java
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
Solution 6 - Java
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.
Solution 7 - Java
As dialect change is not working for me when I am using MySql database. Easiest way is to download and use standard version of Sql connector. Worked absolutely fine.
http://www.java2s.com/Code/Jar/m/Downloadmysqlconnectorjar.htm
Solution 8 - Java
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.