[Solved] com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘type=MyISAM’


This issue SyntaxErrorException for type MyISAM is occurred because of dialect selected for hibernate. Let’s consider my example and the issue occurred because of that.

Hibernate Configuration

<property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>

As we know in the hibernate configured dialect decide the query generation as per database.

Query Generated

create table ADDRESS (
       id integer not null auto_increment,
        street_name varchar(255),
        city_name varchar(255),
        state_name varchar(255),
        zipcode varchar(255),
        primary key (id)
    ) type=MyISAM

Exception Stack Trace

org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL via JDBC Statement
 at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:67)
 at org.hibernate.tool.schema.internal.SchemaCreatorImpl.applySqlString(SchemaCreatorImpl.java:440)
 at org.hibernate.tool.schema.internal.SchemaCreatorImpl.applySqlStrings(SchemaCreatorImpl.java:424)
 at org.hibernate.tool.schema.internal.SchemaCreatorImpl.createFromMetadata(SchemaCreatorImpl.java:315)
 at org.hibernate.tool.schema.internal.SchemaCreatorImpl.performCreation(SchemaCreatorImpl.java:166)
 at org.hibernate.tool.schema.internal.SchemaCreatorImpl.doCreation(SchemaCreatorImpl.java:135)
 at org.hibernate.tool.schema.internal.SchemaCreatorImpl.doCreation(SchemaCreatorImpl.java:121)
 at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:155)
 at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:72)
 at org.hibernate.internal.SessionFactoryImpl.(SessionFactoryImpl.java:309)
 at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:452)
 at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:710)
 at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:726)
 at com.facingissuesonit.persistent.HibernateUtil.buildSessionFactory(HibernateUtil.java:13)
 at com.facingissuesonit.persistent.HibernateUtil.(HibernateUtil.java:8)
 at com.facingissuesonit.App.main(App.java:26)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'type=MyISAM' at line 8
 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
 at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
 at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
 at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
 at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
 at com.mysql.jdbc.Util.getInstance(Util.java:381)

Solutions

As given above the problem is that the dialect org.hibernate.dialect.MySQLDialect is configured which is for MySQL 4.x or earlier version and we are pointing to MY SQL database with the newer version.

The query generated by dialect having to fragment TYPE=MyISAM that was deprecated in MySQL 4.0 and removed in 5.5. Earlier MySQL 4.x.x version use TYPE MyISAM engine used to store tables but in MySQL 5.x.x or later version MySQL is used ENGINE = MyISAM to store tables. e.g. thats make difference while hibernate generates queries.

As per the database configured for MySQL, we should use other dialects as per database configured. Here is the complete list of Hibernate dialects as per databases:

Hibernate: SQL Dialects List

For Example:
In MySQL <= 4.x.x use dialects as below

<property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>

In MySQL>=5.x.x. use dialects as below

<property name="hibernate.dialect">org.hibernate.dialect.MySQL5Dialect</property>

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s