How to fix "Error executing DDL "alter table events drop foreign key FKg0mkvgsqn8584qoql6a2rxheq" via JDBC Statement"

Question

I'm trying to start spring boot project with MySQL database, but I have some problem with database. I try to start my application that, and server is running but hibernate don't create Tables etc.

This is my code:

User Entity

 @Entity
   public class User {
      @Id
      @GeneratedValue(strategy = IDENTITY)
      private Long id;

      private String firstName;
      private String lastName;

      private String email;

      private String password;
      private String description;
      private String profile_photo;

      private LocalDate create;
      private LocalDate update;

      @OneToMany(mappedBy = "eventOwner")
      private List<Event> ownedEvents;

           public Long getId() {
    return id;
}

public void setId(Long id) {
    this.id = id;
}

public String getFirstName() {
    return firstName;
}

public void setFirstName(String firstName) {
    this.firstName = firstName;
}

public String getLastName() {
    return lastName;
}

public void setLastName(String lastName) {
    this.lastName = lastName;
}

public String getEmail() {
    return email;
}

public void setEmail(String email) {
    this.email = email;
}

public String getPassword() {
    return password;
}

public void setPassword(String password) {
    this.password = password;
}

public String getDescription() {
    return description;
}

public void setDescription(String description) {
    this.description = description;
}

public String getProfile_photo() {
    return profile_photo;
}

public void setProfile_photo(String profile_photo) {
    this.profile_photo = profile_photo;
}

public LocalDate getCreate() {
    return create;
}

public void setCreate(LocalDate create) {
    this.create = create;
}

public LocalDate getUpdate() {
    return update;
}

public void setUpdate(LocalDate update) {
    this.update = update;
}

public List<Event> getOwnedEvents() {
    return ownedEvents;
}

public void setOwnedEvents(List<Event> ownedEvents) {
    this.ownedEvents = ownedEvents;
}}

Event Entity

   @Entity
   @Table(name = "events")
   public class Event {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;

private Double longitude;
private Double latitude;

private String description;
private String header;

private LocalDate startData;
private LocalDate endData;
private LocalDate creat;
private LocalDate update;
private Filters filters;

@ManyToOne
@JoinColumn(name = "owner_id")
private User eventOwner;

public Long getId() {
    return id;
}

public void setId(Long id) {
    this.id = id;
}

public Double getLongitude() {
    return longitude;
}

public void setLongitude(Double longitude) {
    this.longitude = longitude;
}

public Double getLatitude() {
    return latitude;
}

public void setLatitude(Double latitude) {
    this.latitude = latitude;
}

public String getDescription() {
    return description;
}

public void setDescription(String description) {
    this.description = description;
}

public String getHeader() {
    return header;
}

public void setHeader(String header) {
    this.header = header;
}

public LocalDate getStartData() {
    return startData;
}

public void setStartData(LocalDate startData) {
    this.startData = startData;
}

public LocalDate getEndData() {
    return endData;
}

public void setEndData(LocalDate endData) {
    this.endData = endData;
}

public LocalDate getCreat() {
    return creat;
}

public void setCreat(LocalDate creat) {
    this.creat = creat;
}

public LocalDate getUpdate() {
    return update;
}

public void setUpdate(LocalDate update) {
    this.update = update;
}

public Filters getFilters() {
    return filters;
}

public void setFilters(Filters filters) {
    this.filters = filters;
}

public User getEventOwner() {
    return eventOwner;
}

public void setEventOwner(User eventOwner) {
    this.eventOwner = eventOwner;
}

}

And this is my properties:

spring.datasource.url= jdbc:mysql://localhost:3306/some_database? 
requireSSL=false&useSSL=false
spring.datasource.username= user
spring.datasource.password= passw 
logging.level.org.hibernate.SQL= DEBUG
spring.jpa.hibernate.ddl-auto = create-drop
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL55Dialect

This is error I get

org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing 
DDL "alter table events drop foreign key FKg0mkvgsqn8584qoql6a2rxheq" via 
JDBC Statement

and

org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing 
DDL "create table events (id bigint not null auto_increment, creat date, 
description varchar(255), end_data date, event_type integer, max_age 
integer not null, min_age integer not null, open_to_changes bit not null, 
pets_allowed bit not null, price_range integer, smoking_allowed bit not 
null, header varchar(255), latitude double precision, longitude double 
precision, start_data date, update date, owner_id bigint, primary key (id)) 
engine=InnoDB" via JDBC Statement
at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:67) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
at org.hibernate.tool.schema.internal.SchemaCreatorImpl.applySqlString(SchemaCreatorImpl.java:440) [hibernate-core-5.3.7.Final.jar:5.3.7.Final]
at org.hibernate.tool.schema.internal.SchemaCreatorImpl.applySqlStrings(SchemaCreatorImpl.java:424) [hibernate-core-5.3.7.Final.jar:5.3.7.Final]
at org.hibernate.tool.schema.internal.SchemaCreatorImpl.createFromMetadata(SchemaCreatorImpl.java:315) [hibernate-core-5.3.7.Final.jar:5.3.7.Final]
at org.hibernate.tool.schema.internal.SchemaCreatorImpl.performCreation(SchemaCreatorImpl.java:166) [hibernate-core-5.3.7.Final.jar:5.3.7.Final]
at org.hibernate.tool.schema.internal.SchemaCreatorImpl.doCreation(SchemaCreatorImpl.java:135) [hibernate-core-5.3.7.Final.jar:5.3.7.Final]
at org.hibernate.tool.schema.internal.SchemaCreatorImpl.doCreation(SchemaCreatorImpl.java:121) [hibernate-core-5.3.7.Final.jar:5.3.7.Final]
at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:155) [hibernate-core-5.3.7.Final.jar:5.3.7.Final]
at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:72) [hibernate-core-5.3.7.Final.jar:5.3.7.Final]
at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:310) [hibernate-core-5.3.7.Final.jar:5.3.7.Final]
at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:467) [hibernate-core-5.3.7.Final.jar:5.3.7.Final]
at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:939) [hibernate-core-5.3.7.Final.jar:5.3.7.Final]
at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:57) [spring-orm-5.1.4.RELEASE.jar:5.1.4.RELEASE]
at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:365) [spring-orm-5.1.4.RELEASE.jar:5.1.4.RELEASE]
at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:390) [spring-orm-5.1.4.RELEASE.jar:5.1.4.RELEASE]
at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.afterPropertiesSet(AbstractEntityManagerFactoryBean.java:377) [spring-orm-5.1.4.RELEASE.jar:5.1.4.RELEASE]
at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.afterPropertiesSet(LocalContainerEntityManagerFactoryBean.java:341) [spring-orm-5.1.4.RELEASE.jar:5.1.4.RELEASE]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1804) [spring-beans-5.1.4.RELEASE.jar:5.1.4.RELEASE]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1741) [spring-beans-5.1.4.RELEASE.jar:5.1.4.RELEASE]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:576) [spring-beans-5.1.4.RELEASE.jar:5.1.4.RELEASE]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:498) [spring-beans-5.1.4.RELEASE.jar:5.1.4.RELEASE]
at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:320) [spring-beans-5.1.4.RELEASE.jar:5.1.4.RELEASE]
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222) ~[spring-beans-5.1.4.RELEASE.jar:5.1.4.RELEASE]
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:318) [spring-beans-5.1.4.RELEASE.jar:5.1.4.RELEASE]
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:199) [spring-beans-5.1.4.RELEASE.jar:5.1.4.RELEASE]
at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1083) ~[spring-context-5.1.4.RELEASE.jar:5.1.4.RELEASE]
at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:853) ~[spring-context-5.1.4.RELEASE.jar:5.1.4.RELEASE]
at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:546) ~[spring-context-5.1.4.RELEASE.jar:5.1.4.RELEASE]
at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:142) ~[spring-boot-2.1.2.RELEASE.jar:2.1.2.RELEASE]
at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:775) ~[spring-boot-2.1.2.RELEASE.jar:2.1.2.RELEASE]
at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:397) ~[spring-boot-2.1.2.RELEASE.jar:2.1.2.RELEASE]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:316) ~[spring-boot-2.1.2.RELEASE.jar:2.1.2.RELEASE]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1260) ~[spring-boot-2.1.2.RELEASE.jar:2.1.2.RELEASE]

Anyone know how to fix that?

have you tried by deleting all tables? – Ryuzaki L Feb 03 '19 at 15:29 — Ryuzaki L, Feb 03 '19 at 15:29
@Deadpool yes, and I steel have this problem – FilipW Feb 03 '19 at 15:32 — FilipW, Feb 03 '19 at 15:32

score 86 · Answer 1 · answered Feb 04 '19 at 10:16

86

Change spring.jpa.hibernate.ddl-auto = create-drop to update. It is dropping the database at start so wont find the required events table to alter anything.

answered Feb 04 '19 at 10:16

Vivek Patel

969
5
4

6

Changed from create-drop to create and it worked for me. – raksheetbhat Jun 29 '20 at 13:39
for more info on ddl-auto, I found this answer helpful: https://stackoverflow.com/a/1689769/4265546 – raksheetbhat Jun 29 '20 at 13:40
hey, can you please explain how this worked for such an exception? – Asma Rahim Ali Jafri Nov 03 '20 at 07:14
Hibernate is playing. Solution: temporary set the field `spring.jpa.hibernate.ddl-auto=update`, reload the app so hibernates updates the DB, and then set it back to `create-drop`. – José Cabo Nov 26 '22 at 19:02

SupaMario · Accepted Answer · 2019-10-11T08:39:15.053

68

In my case the problem why i got this exception was, that some tables had names which are reserved for postgreSQL. eg. "Like" or "User". Changed name with:

@Table(name="likes")

and it worked fine. Perhaps someone has the same problem.

edited Oct 11 '19 at 08:39

answered May 19 '19 at 10:16

SupaMario

998
9
14

2

Had the same issue - that fixed it perfectly thanks! – Come.T Oct 22 '20 at 08:02
2

Thanks! that did solve my issue as well. Oh the trouble of trying to interpret what the ORM wants to do. Things always seem to be "lost in translation" – Amir Apr 03 '21 at 14:15
Is this annotation coming from `Hibernate` or from `Javax.Persistence`? I assume `Javax.Persistence`, as all others come from there, as well. – Akito Jun 13 '21 at 17:02
Had the same problem using MariaDB, fixed with this too – Juan Diego Lozano Nov 17 '22 at 18:06

score 9 · Answer 3 · answered Nov 05 '20 at 02:50

9

It seems that it is an error related to reserved PostgreSQL words. Similar to the top answer (SupaMario's), the error went away after changing one of my column names from

@Column(name = "name", nullable = false)

to

@Column(name = "employee_name", nullable = false)

answered Nov 05 '20 at 02:50

kyle_aoki

247
5
6

@JoinColumn(name = "from") private Employee from; I had the same problem, "form" was a reserved keyword, after modifying to @JoinColumn(name = "cr_from") it worked fine. – Sayed Hussainullah Sadat Apr 17 '23 at 05:55

score 8 · Answer 4 · answered Nov 09 '21 at 00:14

8

In my case the problem why i got this exception was, that some tables had names which are reserved for mySQL. eg. "order" Changed name with:

@Column(name="orders")

or

for example

@ManyToOne(cascade = {CascadeType.ALL})
@JoinColumn(name = "fk_order",nullable = false)
private Order order;

worked for me.

answered Nov 09 '21 at 00:14

davidrichardo

81
1
1

Same for me in PostgreSQL with table name @Table(name = "user") – AkuLink1 Sep 29 '22 at 12:24

score 3 · Answer 5 · answered Dec 17 '21 at 08:55

Owner is a reserved word for MySQL. Either change it or use annotation to use some different name for forgeinkey instead of default

Reference: https://dev.mysql.com/doc/refman/8.0/en/keywords.html#keywords-8-0-detailed-O

Forcing JPA to use custom name

@ManyToOne
@MapsId("ownerId")
@JoinColumn(
        name = "owner_id",
        foreignKey = @ForeignKey(
                name = "fkey_own_id"
                )
        )

score 2 · Answer 6 · answered Apr 18 '20 at 22:20

2

Just specify the dialect in your application.properties. For example, for MySQL 8:

hibernate.dialect=org.hibernate.dialect.MySQL8Dialect

answered Apr 18 '20 at 22:20

ACV

9,964
5
76
81

score 1 · Answer 7 · answered Jan 15 '20 at 06:13

1

Change The dialect of hibernate.cfg.xml Dialect :- org.hibernate.dialect.MySQL5InnoDBDialect OR org.hibernate.dialect.MySQL55Dialect OR org.hibernate.dialect.MySQL55InnoDBDialect OR org.hibernate.dialect.MySQL57InnoDBDialect

answered Jan 15 '20 at 06:13

Akash Aggarwal

11
1

Maryam Maleki · Answer 8 · 2021-11-16T10:04:49.297

1

Change Line code in properties file:

form:

spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL55Dialect

To:

spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect

Because MySQL55Dialect not creating FK . and MySQL5InnoDBDialect creating FK

edited Nov 16 '21 at 10:04

answered Nov 14 '21 at 13:55

Maryam Maleki

21
3

score 1 · Answer 9 · answered Jul 07 '22 at 12:21

I encountered that problem too with the MySQL running on Docker and here's solution which worked for me.

Delete .data directory if you have one in your project directory.
Turn off running Docker container.
Delete existing Docker container.
Create new Docker container by simply typing in docker-compose up -d in the directory where docker-compose.yml file is located.
Rerun Spring Boot app.

After hours of trying to find solution this solved all my problems with the errors thrown by the Hibernate in the Spring Boot app.

(PS. I have in the application.properties file this line: spring.jpa.hibernate.ddl-auto=update - it might play some factor too)

score 0 · Answer 10 · answered Sep 21 '19 at 03:29

Hmm I also encountered this issue but either of those solutions could not solve my issue. I am using MySQL database packaging with XAMPP. What I tried out to solve but failed

I deleted my Database located in "xampp\mysql\data"
I renamed my conflicted tables
Although I kept this "spring.jpa.hibernate.ddl-auto=update" setting in at start.

So what I did to solve this issue. Might be yours

I completely uninstalled the XAMPP package and Installed it again.
I created new database name
RUN the application

Friends it actually my circumstances, might be help your issue like me. If you have any question please post comment. Thanks

score 0 · Answer 11 · edited Oct 14 '20 at 20:19

0

Ran into similar issue and found SupaMario's answer here.

In my case I had a table named orders but MySql threw the error as it seems to be a reserve table / keyword.

I changed it to customer_order and it all worked fine.

edited Oct 14 '20 at 20:19

Ahmed Ashour

5,179
10
35
56

answered Jul 19 '20 at 03:52

Janakiram Sampalli

1
1

score 0 · Answer 12 · answered Nov 16 '20 at 23:39

0

if you see this problem, please double check your class properties for reserved keywords like key(in my case).

I changed the key to mkey and everything goes fine.

answered Nov 16 '20 at 23:39

Setmax

946
6
10

score 0 · Answer 13 · answered May 03 '21 at 11:58

For anyone working with PostgreSQL and has the same problem even though your table names are not key words, place the line below in your application.properties. It worked for me.

spring.jpa.properties.hibernate.jdbc.lob.non_contextual_creation=true

score 0 · Answer 14 · edited Jun 24 '21 at 13:17

0

In my case I had the error when I did this in a specific entity property

@Column(name ="condition", nullable = false)

I changed this to

@Column(name ="condition_of_house", nullable = false)

And it worked well

edited Jun 24 '21 at 13:17

RubioRic

2,442
4
28
35

answered Jun 24 '21 at 12:08

Maurice Omosh

1
2

score 0 · Answer 15 · answered Jun 24 '21 at 19:46

In my case, the error occurred because mysql user does not have REFERENCES privilege.

To solve this;

Log into mysql with root user
Grant all privileges for 'your_user'@'localhost'

or you just need to grant REFERENCES privileges to 'your_user'@'localhost'

See the documentation: https://dev.mysql.com/doc/refman/8.0/en/grant.html

score 0 · Answer 16 · answered Jan 22 '22 at 15:50

0

Add this setting properties line code in application.yml or properties.properties file:

spring.jpa.hibernate.ddl-auto=update

answered Jan 22 '22 at 15:50

harun ugur

1,718
18
18

score 0 · Answer 17 · edited Mar 07 '22 at 18:08

After checking my hibernate.dialect in application.properties, and changing my table's names (to make them less likely to interfere with reserved MSQL keywords), I was still having this error with create-and-drop, when Hibernate does its ALTER TABLE to add foreign keys.

Wanting to solve this I decided to look at MySQL logs in MySQL Workbench.

But then I was facing another issue: I couldn't access server logs (some errors regarding PATH variable, but PATH variable was ok). See here for how to solve that.

The real issue came from character encoding working on a European environment (France).

Issue was fixed by activating "Beta: Use UTF-8 for worldwide language support", under Control Panel > Region > Administrative > Change system locale.

After reboot, I could access my MySQL logs and guess what? I was no longer having errors with Hibernate either.

RG Fick · Answer 18 · 2023-03-03T14:17:05.307

0

I was getting this error because I was using: private "Long" id; rather than: private "int" id; Hibernate was adding "BIGINT" in the DB and then didn't recognise it.

I changed my entity id's to "int" and that solved my problem. Hope this helps.

edited Mar 03 '23 at 14:17

answered Mar 03 '23 at 14:16

RG Fick

11
2

score 0 · Answer 19 · answered May 02 '23 at 16:28

0

Adding space between column name also leads to this error.

eg.

 @Column(name = "PRODUCT ID")

change this to:

 @Column(name = "PRODUCT_ID")

answered May 02 '23 at 16:28

Sharayu

1
1

score 0 · Answer 20 · edited May 09 '23 at 12:09

0

Odd solution that eventually worked for me: After trying some of the suggested options for spring.jpa.hibernate.ddl-auto, which did not work, I ran it once with none and then with create and it worked.

I can't really explain it but it's quick to try, and it worked for me. I was using a h2 database.

edited May 09 '23 at 12:09

halfer

19,824
17
99
186

answered May 05 '23 at 12:56

Benjamin Basmaci

2,247
2
25
46

score 0 · Answer 21 · answered Aug 25 '23 at 11:39

0

Check your main method.to find any error regarding return methods you called

answered Aug 25 '23 at 11:39

MLone

1
1

I think you need to familiarize yourself with how this website works. Have you taken the [tour]? Have you visited the [help]? I believe that your "answer" is a suggestion on how to find the cause of the error and not a solution. As such it is more of a comment than an answer and you should not post comments as answers. Yes, you don't have enough reputation to post comments - so build up your reputation. – Abra Aug 26 '23 at 07:03

score -1 · Answer 22 · answered Mar 03 '20 at 13:53

Ok, had the same issue. Adding prefixes fixed the problem for me:

from:

`
@Entity
@Table(name = "result_man")
@Proxy(lazy = false)
public class ResultManEntity {
@Id
@GeneratedValue
@Column(name = "id", unique = true, length = 128)
private long id;
private String key;
private int score;
private int index;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "result_id")
private ResultEntity result;
}`

to:

`@Entity
@Table(name = "result_man")
@Proxy(lazy = false)
public class ResultManEntity {
@Id
@GeneratedValue
@Column(name = "id", unique = true, length = 128)
private long id;
private String _key;
private int _score;
private int _index;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "result_id")
private ResultEntity result;`

score -4 · Answer 23 · answered Jul 05 '21 at 09:36

-4

In my case, deleting tables from the database helped. BUT, ATTENTION, THIS WAS A TRAINING PROJECT :)

answered Jul 05 '21 at 09:36

Никита Мельников

103
1
2

How to fix "Error executing DDL "alter table events drop foreign key FKg0mkvgsqn8584qoql6a2rxheq" via JDBC Statement"

23 Answers23

Linked