Why does Hibernate cause a SQL syntax error despite having the correct dialect configured?

Question

I'm stumped. I'm setting up a new spring boot project. I've created several entity classes. It is able to create half of the tables in the database fine, so I know for the most part is is able to connect to the database. I've checked and I have the dialect set up in the configuration - I'm using org.hibernate.dialect.MySQLDialect. When I try running the problem, I keep getting a set of errors like this:

Caused by: java.sql.SQLSyntaxErrorException: 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 'integer, symbol varchar(5), primary key (id)) engine=InnoDB' at line 1

I tried checking google and other stackoverflow questions to no avail. Mostly what I found guided me to making sure that I had the right dialect set up, and to avoid reserved keywords which I checked.

here are the other two errors that show up:

Caused by: java.sql.SQLSyntaxErrorException: 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 'integer, primary key (id)) type=MyISAM' at line 1

Caused by: java.sql.SQLSyntaxErrorException: 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 'varchar(255), clock_in varchar(255), clock_out varchar(255), coin varchar(255), ' at line 1

Edit: Here is the sql that hibernate is generating:

Hibernate: create table record (id integer not null, cash varchar(255), check varchar(255), clock_in varchar(255), clock_out varchar(255), coin varchar(255), primary key (id)) type=MyISAM

create table route (id integer not null, name varchar(100), color integer, driver varchar(255), index integer, primary key (id)) type=MyISAM

create table store (id integer not null, name varchar(100), address varchar(255), index integer, symbol varchar(255), primary key (id)) type=MyISAM

Edit 2 - Here is one of the entity classes that seems to have trouble with the auto table generation:

package org.tampasa.kettles.models;

import javax.persistence.Entity;

@Entity
public class Store extends AbstractEntity{

    private String address;

    private String symbol;

    private int index;

    // Constructors

    public Store() {
    }


    // Getters and Setters

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public String getSymbol() {
        return symbol;
    }

    public void setSymbol(String symbol) {
        this.symbol = symbol;
    }

    public Integer getIndex() {
        return index;
    }

    public void setIndex(Integer index) {
        this.index = index;
    }

}

And here is AbstractEntity:

package org.tampasa.kettles.models;

import org.hibernate.validator.constraints.Length;

import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.MappedSuperclass;
import javax.validation.constraints.NotBlank;
import java.util.Objects;

@MappedSuperclass
public abstract class AbstractEntity {

    @Id
    @GeneratedValue
    private int id;

    @NotBlank(message = "Name must not be blank")
    @Length(min = 1, max = 100, message = "Name must be between 1 and 100 characters")
    private String name;

    public int getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    @Override
    public String toString() {
        return name;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        AbstractEntity that = (AbstractEntity) o;
        return id == that.id;
    }

    @Override
    public int hashCode() {
        return Objects.hash(id);
    }

}

How do I find that? I tried adding spring.jpa.show-sql = true to the properties, but it doesn't show anything before the error comes up. Edit: I think I found it. See above — brendenm17, Sep 05 '20 at 19:12
I believe you use automatic table generation from entity classes. Could you please list one you have troubles with? I have almost never used it, but it'd be a great deal to be able to look at it, I think. Also, could you, please, check, you are using correct MySQL version that fits that dialect? In my experience, there may be some weird differences. At least, there are some with Postgres and Postgres95 dialect. :) — Xobotun, Sep 05 '20 at 19:31
Yes, that's correct, I'm using automatic table generation from entity classes. I'll edit the original post to show one now. — brendenm17, Sep 05 '20 at 19:35
One possible reason could be unescaped `name` column. MS documentation mentions it is a reserved word: https://dev.mysql.com/doc/refman/8.0/en/keywords.html#keywords-8-0-detailed-N. I'm sure `index` is also not a legit name. There was a property that forced Hibernate to enable escaping, I'll go find it. — Xobotun, Sep 05 '20 at 19:44
Okay, you're right - index is a reserved word. Swapping it out fixed 2/3 of my errors. And the other reserved word causing issues was check. Thank you for that reference. That will be useful in the future! — brendenm17, Sep 05 '20 at 19:55

score 3 · Accepted Answer · answered Sep 05 '20 at 19:49

3

One possible reason could be that columns name and index use SQL reserved words. At least in MySQL 8.0. And generally too. I've stumbled upon it too. :D

As per this answer you can add line hibernate.globally_quoted_identifiers=true to your .properties file. This should make Hibernate escape reserved words and maybe can fix issues you are experiencing.

answered Sep 05 '20 at 19:49

Xobotun

1,121
1
18
29

Thank you. Index and check were the words causing the problem. I was able to fix it by changing the variable names in my class. the property setting didn't work for me. Edit: Never mind the properties line does work. I also found another useful property that automatically quotes just words that are deemed keywords: hibernate.auto_quote_keyword = true – brendenm17 Sep 05 '20 at 20:04
@brendenm17 Good to hear you've got that problem solved. Also, thanks for sharing additional property, I did not know it. :D – Xobotun Sep 06 '20 at 06:26

Why does Hibernate cause a SQL syntax error despite having the correct dialect configured?

1 Answers1