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?

Vivek Patel · Accepted Answer

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.

SupaMario · Answer

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.

kyle_aoki · Answer

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)

davidrichardo · Answer

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.

ACV · Answer

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

hibernate.dialect=org.hibernate.dialect.MySQL8Dialect

TRO · Answer

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

Akash Aggarwal · Answer

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

YgLK · Answer

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)

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

Tags:

java

mysql

hibernate

spring-boot

FilipW

8 Answers

Vivek Patel

SupaMario

kyle_aoki

davidrichardo

ACV

TRO

Akash Aggarwal

YgLK

Recent Activity

Donate For Us

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

Tags:

java

mysql

hibernate

spring-boot

FilipW

8 Answers

Vivek Patel

SupaMario

kyle_aoki

davidrichardo

ACV

TRO

Akash Aggarwal

YgLK

Related questions

Recent Activity

Donate For Us