I am trying to deploy a Spring Boot app to Heroku. It uses a remote database which is provided by RemoteMysl. However, when the app is building, it creates all tables in the database except one table. I am getting the following error on Heroku Application Log.
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 'groups (id) on delete cascade' at line 1
Group model
package com.itsfive.back.model;
import java.util.HashSet;
import java.util.Optional;
import java.util.Set;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.JoinTable;
import javax.persistence.ManyToMany;
import javax.persistence.ManyToOne;
import javax.persistence.OneToMany;
import javax.persistence.OneToOne;
import javax.persistence.Table;
import javax.persistence.UniqueConstraint;
import javax.validation.constraints.Email;
import javax.validation.constraints.NotBlank;
import javax.validation.constraints.Size;
import org.springframework.web.multipart.MultipartFile;
import com.itsfive.back.model.audit.DateAudit;
@Entity
@Table(name = "groups")
public class Group extends DateAudit{
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@NotBlank
@Size(max = 30)
private String name;
@Size(max = 160)
private String description;
@OneToOne
@JoinColumn(name = "created_by_id", nullable = false)
private User created_by;
private String coverPhoto;
public User getCreated_by() {
return created_by;
}
public void setCreated_by(User created_by) {
this.created_by = created_by;
}
public String getCoverPhoto() {
return coverPhoto;
}
public void setCoverPhoto(String coverPhoto) {
this.coverPhoto = coverPhoto;
}
public Group(@NotBlank @Size(max = 30) String name, @Size(max = 160) String description,User created_by) {
super();
this.name = name;
this.description = description;
this.created_by = created_by;
}
public Group() {
}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String groupName) {
this.name = groupName;
}
public String getDescription() {
return description;
}
public void setDescription(String description) {
this.description = description;
}
public User getCreatedBy() {
return created_by;
}
public void setCreatedBy(User user) {
this.created_by = user;
}
}
Well, I think you are using Mysql 8.0 then this issue is due you are using Mysql
reserved keywords like is groups
on your table name.
You can take a look SQL reserved words for Mysql 8.0 then you have two options
1.- Rename groups
table to users_group
or table name that you want.
@Table(name = "users_group")
public class Group extends XXXXX { ... }
2.- Force to use groups
table name.
If you are using JPA, you can escape with double quotes:
@Table(name = "\"groups\"")
public class Group extends XXXXX { ... }
If you're using Hibernate native API, then you can escape them using backticks:
@Table(name = "`groups`")
public class Group extends XXXXX { ... }
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments