MySQLSyntaxErrorException – Fix SQL Syntax Error

database-designhibernateMySQL

This is the issue I am trying to fix since hours, almost a day. I am unable to get an understanding of what might be wrong.

I have a SQL table notification

enter image description here

enter image description here

I am trying to persist a java object via hibernate

public void persistNotification(Notification notify){
        sessionFactory.getCurrentSession().save(notify);
    }

notify object passed to the method looks fine to me with all the values

Notification [notificationId=null, notificationMessage=ethan is now following you, notificationType=follow, notificationRead=false, actor=ethan, subject=diggle, notificationCreationTime=2015-12-09T19:13:23.002, userId=1]

But I am encountering a SyntaxErrorException.

2015-12-09 18:52:05.0550 DEBUG http-bio-8080-exec-10 org.hibernate.SQL – insert into notification (actor, creation_time, notification, read, type, subject, user_id) values (?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into notification (actor, creation_time, notification, read, type, subject, user_id) values (?, ?, ?, ?, ?, ?, ?)
2015-12-09 18:52:05.0579 DEBUG http-bio-8080-exec-10 org.hibernate.engine.jdbc.spi.SqlExceptionHelper – 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 'read, type, subject, user_id) values ('ethan', '2015-12-09 18:51:54', 'ethan is ' at line 1 [n/a]
com.mysql.jdbc.exceptions.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 'read, type, subject, user_id) values ('ethan', '2015-12-09 18:51:54', 'ethan is ' at line 1
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2941)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1623)

2015-12-09 19:00:08.0027 DEBUG http-bio-8080-exec-10 org.springframework.web.servlet.mvc.support.DefaultHandlerExceptionResolver – Resolving exception from handler [public org.springframework.http.ResponseEntity<java.lang.String> com.mycricket.controller.UserController.followUnfollowUser(java.lang.String)]: org.hibernate.exception.SQLGrammarException: 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 'read, type, subject, user_id) values ('ethan', '2015-12-09 19:00:07', 'ethan is ' at line 1
2015-12-09 19:00:08.0028 DEBUG http-bio-8080-exec-10 org.springframework.web.servlet.DispatcherServlet – Could not complete request
org.hibernate.exception.SQLGrammarException: 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 'read, type, subject, user_id) values ('ethan', '2015-12-09 19:00:07', 'ethan is ' at line 1
    at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:122)

Can anyone help me out what might be the issue? Any clue, guesses would be appreciated.

I also dropped & recreated the table but no luck. I don't I am using any reserved SQL words.

Best Answer

The word read is a reserved keyword in MySQL.

https://dev.mysql.com/doc/refman/5.5/en/keywords.html

You need to use a more appropriate column name.