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

Question

I'm trying to start a Spring Boot project with a MySQL database, but I have some problem with the database. I try to start my application that, and server is running, but Hibernate doesn’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 these are 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 the 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]

How can I fix that?

Venegas

have you tried by deleting all tables? – Benner

@Deadpool yes, and I steel have this problem – Venegas

Another possible solution could be this: https://mcmap.net/q/355248/-hibernate-foreign-key-issue-error-executing-ddl-quot-alter-table-quot – Carricarriage

Answer 1

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

@Table(name="likes")

and it worked fine.

Casimiracasimire

Had the same issue - that fixed it perfectly thanks! – Limestone

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" – Retrograde

Is this annotation coming from Hibernate or from Javax.Persistence? I assume Javax.Persistence, as all others come from there, as well. – Legislative

Had the same problem using MariaDB, fixed with this too – Dutchman

Answer 2

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.

Warila

Changed from create-drop to create and it worked for me. – Adler

for more info on ddl-auto, I found this answer helpful: https://mcmap.net/q/45445/-what-are-the-possible-values-of-the-hibernate-hbm2ddl-auto-configuration-and-what-do-they-do – Adler

hey, can you please explain how this worked for such an exception? – Concertgoer

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. – Stubborn

Answer 3

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

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

to

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

Analogous

@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. – Boutis

Answer 4

In my case, the problem why I got this exception was that some tables had names which were reserved for MySQL. For example, "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.

Raynell

Same for me in PostgreSQL with table name @Table(name = "user") – Halimeda

Answer 5

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"
                )
        ) 

Starvation

Answer 6

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

hibernate.dialect=org.hibernate.dialect.MySQL8Dialect

Mutism

Answer 7

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

0. Delete .data directory if you have one in your project directory.
1. Turn off running Docker container.
2. Delete existing Docker container.
3. Create new Docker container by simply typing in docker-compose up -d in the directory where docker-compose.yml file is located.
4. 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)

Kilan

This solve the issue for me as well but I can't figure out why.... – Carricarriage

Answer 8

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

Gillenwater

Answer 9

Change Line code in properties file:

From:

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

To:

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

Because the MySQL55Dialect is not creating a foreign key. And the MySQL5InnoDBDialect is creating a foreign key.

Fisch

Answer 10

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

Merna

Answer 11

OK, I 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;`

Infectious

It seems to be poorly formatted. – Bettyannbettye

Answer 12

I ran into a 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.

Birk

Answer 13

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 went fine.

Conduction

Answer 14

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

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

Improvisatory

Answer 15

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.

Jhvh

Answer 16

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

To solve this;

1. Log into MySQL with the root user
2. Grant all privileges for 'your_user'@'localhost'

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

See the documentation: 13.7.1.6 GRANT Statement

Embroideress

Answer 17

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

spring.jpa.hibernate.ddl-auto=update

Shamefaced

Answer 18

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.

Gummosis

Answer 19

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.

Harbor

Answer 20

Adding space between column name also leads to this error.

eg.

 @Column(name = "PRODUCT ID")

change this to:

 @Column(name = "PRODUCT_ID")

Wilbert

Answer 21

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.

Ardellardella

Answer 22

One potential reason for this error could be that there are existing records in the tables.To resolve this issue, you may need to clean up the data in the tables

Pterosaur

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Fleam

Answer 23

If you perform DDL commands on DB tabel which is linked to Spring Application , this may get Exception.

In that case you just need to Validate Table name with @Table(name="table-name") in Spring Application AND most imp COMMIT the data from Database .

The Application will work.

Determinism

Answer 24

In my case, deleting tables from the database helped. But, attention, this was a training project :)*

Spoonfeed