Invalid syntax error "type= MyISAM" in DDL generated by Hibernate
hibernate, java, mariadb, mysql
Solution
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.
Problem
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(); } } ```