Java 如何通过 JDBC 语句修复“执行 DDL 时出错”更改表事件删除外键 FKg0mkvgsqn8584qoql6a2rxheq”

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

提问by FilipW

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.

我正在尝试使用 MySQL 数据库启动 spring boot 项目，但是我的数据库有一些问题。我尝试启动我的应用程序，并且服务器正在运行但休眠不创建表等。

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
@Table(name="likes") 
(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?

有谁知道如何解决这个问题？

采纳答案by SupaMario

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:

在我的情况下，为什么我得到这个异常的问题是，一些表的名称是为 postgreSQL 保留的。例如。“喜欢”或“用户”。更名：

`
@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;
}`

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

它工作正常。也许有人有同样的问题。

回答by Vivek Patel

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

将spring.jpa.hibernate.ddl-auto = create-drop更改为update。它在开始时删除数据库，因此不会找到所需的事件表来更改任何内容。

回答by Subarata Talukder

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

嗯，我也遇到了这个问题，但这些解决方案中的任何一个都无法解决我的问题。我在 XAMPP 中使用 MySQL 数据库打包。我试图解决但失败的问题

• 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.

• 我删除了位于“xampp\mysql\data”中的数据库
• 我重命名了我的冲突表
• 虽然我在开始时保留了这个“spring.jpa.hibernate.ddl-auto=update”设置。

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

• 我完全卸载了 XAMPP 包并重新安装了它。
• 我创建了新的数据库名称
• 运行应用程序

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

朋友，这实际上是我的情况，可能会像我一样帮助您解决问题。如果您有任何问题，请发表评论。谢谢

回答by Akash Aggarwal

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

更改 hibernate.cfg.xml 方言的方言：- org.hibernate.dialect.MySQL5InnoDBDialect OR org.hibernate.dialect.MySQL55Dialect OR org.hibernate.dialect.MySQL55InnoDBDialect OR org.hibernate.dialect.MySQL57InnoDBDialect

回答by Simas Pa?kauskas

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:

到：

hibernate.dialect=org.hibernate.dialect.MySQL8Dialect

回答by ACV

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

只需在您的application.properties. 例如，对于 MySQL 8：

##代码##