Invalid syntax error "type= MyISAM" in DDL generated by Hibernate
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.
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.MariaDBDialectorg.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.MySQL5Dialectorg.hibernate.dialect.MySQL55Dialectorg.hibernate.dialect.MySQL57Dialect
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.
Its a Dialect related issue, instead of org.hibernate.dialect.MySQLDialect you can go with org.hibernate.dialect.MySQL5Dialect. It will work happily.