Why does Hibernate generate an 'Invalid Syntax Error' with 'type=MyISAM' when creating a MySQL table?

Invalid Syntax Error "type= MyISAM" in DDL Generated by Hibernate

Question:

When attempting to create a table in MySQL using Hibernate, I encountered the following error:

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 error occurs in the query generated by Hibernate:

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

I've searched for similar errors but found that users typically encounter this issue while passing the query manually. Here, Hibernate is generating the table creation query. Where can I find the mistake and how can I resolve it?

Answer:

This error occurs because the dialect used in your configuration is outdated. In Hibernate 5.x and earlier, org.hibernate.dialect.MySQLDialect was used for MySQL 4.x or earlier. The "TYPE=MYISAM" fragment generated by this dialect was deprecated in MySQL 4.0 and removed in 5.5.

To resolve this issue, you need to update the dialect in your Hibernate configuration. If you are using MariaDB, depending on its version and the Hibernate version, you can use one of the following dialects:

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

If you are using MySQL or the MariaDB dialects mentioned above do not exist in your Hibernate version, you can use one of the following dialects:

• org.hibernate.dialect.MySQL5Dialect
• org.hibernate.dialect.MySQL55Dialect
• org.hibernate.dialect.MySQL57Dialect
• org.hibernate.dialect.MySQL8Dialect
• org.hibernate.dialect.MySQL57InnoDBDialect (or variants)

Note: In Hibernate 6, you can use MySQLDialect or MariaDBDialect again, as Hibernate 6 dialects self-configure based on the connected version.

The above is the detailed content of Why does Hibernate generate an 'Invalid Syntax Error' with 'type=MyISAM' when creating a MySQL table?. For more information, please follow other related articles on the PHP Chinese website!