SQL Error: 1064, SQLState: 42000 while creating new entity

Question

I am working on a Spring mvc web application in which I need to create a town using Hibernate. Following is my town model.

@Entity
@Table(name="town")
public class TownModel {

    @Id
    @Column(name="townid")
    @GeneratedValue 
    private Integer townId;

    @Column(name="name")
    private String townName;

    @Column(name="desc")
    private String townDesc;

    @ManyToOne(optional = true)
    @JoinColumn(name="districtid")
    private DistrictModel districtModel;
}

I have another entity named district. Town is a part of district and a district can have multiple towns. Following is my district model:

@Entity
@Table(name="district")
public class DistrictModel {

    @Id
    @Column(name="districtid")
    @GeneratedValue
    private Integer districtId;

    @Column(name="name")
    private String name;

    @Column(name="desc")
    private String description;

    @OneToMany(mappedBy = "districtModel", fetch = FetchType.EAGER)
    @Fetch(value = FetchMode.SUBSELECT)
    private List<TownModel> townModelList;
}

I am using following Hibernate code for saving town:

Session session = sessionFactory.getCurrentSession();
session.save(townModel);

But it shows error:

21:22:08,104 WARN  [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (http--127.0.0.1-9090-1) SQL Error: 1064, SQLState: 42000
21:22:08,112 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (http--127.0.0.1-9090-1) 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 'desc, name) values (2, 'Test town desc.', 'Test town')' at line 1

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 'desc, name) values (2, 'Test town desc.', 'Test town')' at line 1
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.7.0_45]
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57) ~[na:1.7.0_45]
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.7.0_45]
    at java.lang.reflect.Constructor.newInstance(Constructor.java:526) ~[na:1.7.0_45]
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:377) ~[com.mysql.jdbc_5.1.5.jar:na]
    at com.mysql.jdbc.Util.getInstance(Util.java:360) ~[com.mysql.jdbc_5.1.5.jar:na]
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:978) ~[com.mysql.jdbc_5.1.5.jar:na]
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3887) ~[com.mysql.jdbc_5.1.5.jar:na]
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3823) ~[com.mysql.jdbc_5.1.5.jar:na]
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435) ~[com.mysql.jdbc_5.1.5.jar:na]
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582) ~[com.mysql.jdbc_5.1.5.jar:na]
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2530) ~[com.mysql.jdbc_5.1.5.jar:na]
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1907) ~[com.mysql.jdbc_5.1.5.jar:na]
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2141) ~[com.mysql.jdbc_5.1.5.jar:na]
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2077) ~[com.mysql.jdbc_5.1.5.jar:na]
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2062) ~[com.mysql.jdbc_5.1.5.jar:na]
    at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:493) ~[na:na]
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:186) ~[hibernate-core-4.2.16.Final.jar:4.2.16.Final]
    ... 64 common frames omitted

score 48 · Accepted Answer · edited May 23 '17 at 12:24

The error messages states:

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 'desc, name) values (2, 'Test town desc.', 'Test town')' at line 1

The problem is in the generated query, due to the usage of desc since it's a reserved word in MySQL.

Possible solutions:

Change the name of your column to description. Do similar with name.
Change the configuration in MySQL to support these kind of names for columns in queries.
Change the name of the column in the fields to append ` character (referenced from Creating field with reserved word name with JPA):
```
@Column(name="`desc`")
```

IMO while option 3 is a quick and dirty solution, I find option 1 as the best solution for future usage of the database.

Excellent! I had this issue as well, for me the reserved word was 'index'. Pretty hard to debug that one. Thanks! — Jelle Blaauw, May 31 '19 at 16:45

score 9 · Answer 2 · answered Mar 09 '15 at 16:14

9

You can also use square brackets or double quotes to escape column name.

@Column(name="[desc]")
private String townDesc;

or

@Column(name="\"desc\"")
private String townDesc;

answered Mar 09 '15 at 16:14

Maroš Tyrpák

305
1
6

singhakash · Answer 3 · 2015-03-09T16:23:31.370

4

desc is a mysql reserve word.Check

Solution:

Do

 @Column(name="[desc]")
               ^    ^

Add square brackets [] .

OR

Change the name of column

Source

edited Mar 09 '15 at 16:23

answered Mar 09 '15 at 16:10

singhakash

7,891
6
31
65

score 3 · Answer 4 · answered May 21 '18 at 06:34

3

I had the same problem but I'd named one of my columns 'order'. Of course, this is also a reserved word. Easy mistake to make.

answered May 21 '18 at 06:34

Daisy Day

652
7
19

I had the same issue (column called 'order'). Yes, easy mistake to make, took awhile to realize it! – Jack Straw Sep 24 '20 at 19:47
So did I. In my case the reserved word was "character" and it was the name of my table. So the change of this name solved the issue. The most annoying fact is that H2 database I used in my test told me nothing about it and I faced the problem only with real MySQL. – kolyaiks Nov 04 '21 at 02:11

score 2 · Answer 5 · edited May 26 '19 at 12:23

2

I add a table named 'order' in MySQL Workbench, it doesn't give me an error.
But when I execute the query by Spring JPA It gives out the error.
That's because my table name "order" is a reserved word.
Just make a little change and the error has gone. change table name from "order" to "orders"

edited May 26 '19 at 12:23

Amit Kumar Lal

5,537
3
19
37

answered May 26 '19 at 07:45

Ma Zhiyuan

21
1

SQL Error: 1064, SQLState: 42000 while creating new entity

5 Answers5

Linked