java - Spring Boot HIbernate 问题 : Error executing DDL via JDBC Statement with ddl-auto=create-drop

Question

我是 Spring 和 Hibernate 的新手，我已经尝试了所有我能找到的方法来解决这个问题，但没有成功。任何帮助将不胜感激。我目前正在关注一本书中的一个示例项目，并且收到一条错误消息“GenerationTarget 遇到异常接受命令:通过 JDBC 语句执行 DDL 时出错。”我正在使用 MariaDB 数据库，我的 hibernate 方言是 org.hibernate.dialect.MySQL5InnoDBDialect。

导致这个问题的查询是

alter table car drop foreign key FK2mqqwvxtowv4vddvtsmvtiqa2;

如果我尝试直接在数据库中运行此查询，它工作正常。我将 ddl-auto 属性设置为 create-drop，所以当 car 表已被删除但不存在时，它可能会尝试执行此查询？我不确定为什么它会这样做，或者如果这是问题的话如何关闭这种行为。

如果我将 ddl-auto 更改为验证或更新，应用程序根本无法运行。如果我将其更改为创建，它工作正常并且我没有收到任何错误。

使用 create-drop，应用程序确实连接到数据库并且创建了我的所有表。表仍按应有的方式定义，但错误仍然存​​在。

控制台输出:

2018-07-25 16:56:24.229  INFO 12414 --- [  restartedMain] c.p.cardatabase.CardatabaseApplication   : Starting CardatabaseApplication on Northstar-2.local with PID 12414 2018-07-25 16:56:24.230  INFO 12414 --- [  restartedMain] c.p.cardatabase.CardatabaseApplication   : No active profile set, falling back to default profiles: default
2018-07-25 16:56:24.278  INFO 12414 --- [  restartedMain] ConfigServletWebServerApplicationContext : Refreshing org.springframework.boot.web.servlet.context.AnnotationConfigServletWebServerApplicationContext@42e3f007: startup date [Wed Jul 25 16:56:24 AKDT 2018]; root of context hierarchy
2018-07-25 16:56:25.134  INFO 12414 --- [  restartedMain] o.s.b.f.s.DefaultListableBeanFactory     : Overriding bean definition for bean 'httpRequestHandlerAdapter' with a different definition: replacing [Root bean: class [null]; scope=; abstract=false; lazyInit=false; autowireMode=3; dependencyCheck=0; autowireCandidate=true; primary=false; factoryBeanName=org.springframework.boot.autoconfigure.web.servlet.WebMvcAutoConfiguration$EnableWebMvcConfiguration; factoryMethodName=httpRequestHandlerAdapter; initMethodName=null; destroyMethodName=(inferred); defined in class path resource [org/springframework/boot/autoconfigure/web/servlet/WebMvcAutoConfiguration$EnableWebMvcConfiguration.class]] with [Root bean: class [null]; scope=; abstract=false; lazyInit=false; autowireMode=3; dependencyCheck=0; autowireCandidate=true; primary=false; factoryBeanName=org.springframework.data.rest.webmvc.config.RepositoryRestMvcConfiguration; factoryMethodName=httpRequestHandlerAdapter; initMethodName=null; destroyMethodName=(inferred); defined in class path resource [org/springframework/data/rest/webmvc/config/RepositoryRestMvcConfiguration.class]]
2018-07-25 16:56:25.535  INFO 12414 --- [  restartedMain] trationDelegate$BeanPostProcessorChecker : Bean 'org.springframework.transaction.annotation.ProxyTransactionManagementConfiguration' of type [org.springframework.transaction.annotation.ProxyTransactionManagementConfiguration$$EnhancerBySpringCGLIB$$e5303d26] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
2018-07-25 16:56:25.850  INFO 12414 --- [  restartedMain] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat initialized with port(s): 8080 (http)
2018-07-25 16:56:25.875  INFO 12414 --- [  restartedMain] o.apache.catalina.core.StandardService   : Starting service [Tomcat]
2018-07-25 16:56:25.875  INFO 12414 --- [  restartedMain] org.apache.catalina.core.StandardEngine  : Starting Servlet Engine: Apache Tomcat/8.5.29
2018-07-25 16:56:25.883  INFO 12414 --- [ost-startStop-1] o.a.catalina.core.AprLifecycleListener   : The APR based Apache Tomcat Native library which allows optimal performance in production environments was not found on the java.library.path: [/Users/tatiana/Library/Java/Extensions:/Library/Java/Extensions:/Network/Library/Java/Extensions:/System/Library/Java/Extensions:/usr/lib/java:.]
2018-07-25 16:56:26.025  INFO 12414 --- [ost-startStop-1] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring embedded WebApplicationContext
2018-07-25 16:56:26.025  INFO 12414 --- [ost-startStop-1] o.s.web.context.ContextLoader            : Root WebApplicationContext: initialization completed in 1750 ms
2018-07-25 16:56:26.155  INFO 12414 --- [ost-startStop-1] o.s.b.w.servlet.FilterRegistrationBean   : Mapping filter: 'characterEncodingFilter' to: [/*]
2018-07-25 16:56:26.156  INFO 12414 --- [ost-startStop-1] o.s.b.w.servlet.FilterRegistrationBean   : Mapping filter: 'hiddenHttpMethodFilter' to: [/*]
2018-07-25 16:56:26.156  INFO 12414 --- [ost-startStop-1] o.s.b.w.servlet.FilterRegistrationBean   : Mapping filter: 'httpPutFormContentFilter' to: [/*]
2018-07-25 16:56:26.156  INFO 12414 --- [ost-startStop-1] o.s.b.w.servlet.FilterRegistrationBean   : Mapping filter: 'requestContextFilter' to: [/*]
2018-07-25 16:56:26.156  INFO 12414 --- [ost-startStop-1] .s.DelegatingFilterProxyRegistrationBean : Mapping filter: 'springSecurityFilterChain' to: [/*]
2018-07-25 16:56:26.156  INFO 12414 --- [ost-startStop-1] o.s.b.w.servlet.ServletRegistrationBean  : Servlet dispatcherServlet mapped to [/]
2018-07-25 16:56:26.288  INFO 12414 --- [  restartedMain] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2018-07-25 16:56:26.354  INFO 12414 --- [  restartedMain] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
2018-07-25 16:56:26.385  INFO 12414 --- [  restartedMain] j.LocalContainerEntityManagerFactoryBean : Building JPA container EntityManagerFactory for persistence unit 'default'
2018-07-25 16:56:26.401  INFO 12414 --- [  restartedMain] o.hibernate.jpa.internal.util.LogHelper  : HHH000204: Processing PersistenceUnitInfo [
    name: default
    ...]
2018-07-25 16:56:26.467  INFO 12414 --- [  restartedMain] org.hibernate.Version                    : HHH000412: Hibernate Core {5.2.16.Final}
2018-07-25 16:56:26.469  INFO 12414 --- [  restartedMain] org.hibernate.cfg.Environment            : HHH000206: hibernate.properties not found
2018-07-25 16:56:26.504  INFO 12414 --- [  restartedMain] o.hibernate.annotations.common.Version   : HCANN000001: Hibernate Commons Annotations {5.0.1.Final}
2018-07-25 16:56:26.602  INFO 12414 --- [  restartedMain] org.hibernate.dialect.Dialect            : HHH000400: Using dialect: org.hibernate.dialect.MySQL5InnoDBDialect
2018-07-25 16:56:27.062  WARN 12414 --- [  restartedMain] o.h.t.s.i.ExceptionHandlerLoggedImpl     : GenerationTarget encountered exception accepting command : Error executing DDL via JDBC Statement

org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL via JDBC Statement
    at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:67) ~[hibernate-core-5.2.16.Final.jar:5.2.16.Final]
    at org.hibernate.tool.schema.internal.SchemaDropperImpl.applySqlString(SchemaDropperImpl.java:375) [hibernate-core-5.2.16.Final.jar:5.2.16.Final]
    at org.hibernate.tool.schema.internal.SchemaDropperImpl.applySqlStrings(SchemaDropperImpl.java:359) [hibernate-core-5.2.16.Final.jar:5.2.16.Final]
    at org.hibernate.tool.schema.internal.SchemaDropperImpl.applyConstraintDropping(SchemaDropperImpl.java:331) [hibernate-core-5.2.16.Final.jar:5.2.16.Final]
    at org.hibernate.tool.schema.internal.SchemaDropperImpl.dropFromMetadata(SchemaDropperImpl.java:230) [hibernate-core-5.2.16.Final.jar:5.2.16.Final]
    at org.hibernate.tool.schema.internal.SchemaDropperImpl.performDrop(SchemaDropperImpl.java:154) [hibernate-core-5.2.16.Final.jar:5.2.16.Final]
    at org.hibernate.tool.schema.internal.SchemaDropperImpl.doDrop(SchemaDropperImpl.java:126) [hibernate-core-5.2.16.Final.jar:5.2.16.Final]
    at org.hibernate.tool.schema.internal.SchemaDropperImpl.doDrop(SchemaDropperImpl.java:112) [hibernate-core-5.2.16.Final.jar:5.2.16.Final]
    at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:144) [hibernate-core-5.2.16.Final.jar:5.2.16.Final]
    at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:72) [hibernate-core-5.2.16.Final.jar:5.2.16.Final]
    at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:312) [hibernate-core-5.2.16.Final.jar:5.2.16.Final]
    at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:460) [hibernate-core-5.2.16.Final.jar:5.2.16.Final]
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:892) [hibernate-core-5.2.16.Final.jar:5.2.16.Final]
    at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:57) [spring-orm-5.0.5.RELEASE.jar:5.0.5.RELEASE]
    at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:365) [spring-orm-5.0.5.RELEASE.jar:5.0.5.RELEASE]
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:390) [spring-orm-5.0.5.RELEASE.jar:5.0.5.RELEASE]
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.afterPropertiesSet(AbstractEntityManagerFactoryBean.java:377) [spring-orm-5.0.5.RELEASE.jar:5.0.5.RELEASE]
    at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.afterPropertiesSet(LocalContainerEntityManagerFactoryBean.java:341) [spring-orm-5.0.5.RELEASE.jar:5.0.5.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1761) [spring-beans-5.0.5.RELEASE.jar:5.0.5.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1698) [spring-beans-5.0.5.RELEASE.jar:5.0.5.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:579) [spring-beans-5.0.5.RELEASE.jar:5.0.5.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:501) [spring-beans-5.0.5.RELEASE.jar:5.0.5.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:317) [spring-beans-5.0.5.RELEASE.jar:5.0.5.RELEASE]
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:228) ~[spring-beans-5.0.5.RELEASE.jar:5.0.5.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:315) [spring-beans-5.0.5.RELEASE.jar:5.0.5.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:199) [spring-beans-5.0.5.RELEASE.jar:5.0.5.RELEASE]
    at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1089) ~[spring-context-5.0.5.RELEASE.jar:5.0.5.RELEASE]
    at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:859) ~[spring-context-5.0.5.RELEASE.jar:5.0.5.RELEASE]
    at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:550) ~[spring-context-5.0.5.RELEASE.jar:5.0.5.RELEASE]
    at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:140) ~[spring-boot-2.0.1.RELEASE.jar:2.0.1.RELEASE]
    at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:759) ~[spring-boot-2.0.1.RELEASE.jar:2.0.1.RELEASE]
    at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:395) ~[spring-boot-2.0.1.RELEASE.jar:2.0.1.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:327) ~[spring-boot-2.0.1.RELEASE.jar:2.0.1.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1255) ~[spring-boot-2.0.1.RELEASE.jar:2.0.1.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1243) ~[spring-boot-2.0.1.RELEASE.jar:2.0.1.RELEASE]
    at com.packt.cardatabase.CardatabaseApplication.main(CardatabaseApplication.java:28) ~[classes/:na]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_161]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_161]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_161]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_161]
    at org.springframework.boot.devtools.restart.RestartLauncher.run(RestartLauncher.java:49) ~[spring-boot-devtools-2.0.1.RELEASE.jar:2.0.1.RELEASE]
Caused by: java.sql.SQLSyntaxErrorException: (conn=342) Table 'cardb.car' doesn't exist
    at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.get(ExceptionMapper.java:177) ~[mariadb-java-client-2.2.3.jar:na]
    at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.getException(ExceptionMapper.java:110) ~[mariadb-java-client-2.2.3.jar:na]
    at org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue(MariaDbStatement.java:228) ~[mariadb-java-client-2.2.3.jar:na]
    at org.mariadb.jdbc.MariaDbStatement.executeInternal(MariaDbStatement.java:334) ~[mariadb-java-client-2.2.3.jar:na]
    at org.mariadb.jdbc.MariaDbStatement.execute(MariaDbStatement.java:386) ~[mariadb-java-client-2.2.3.jar:na]
    at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:95) ~[HikariCP-2.7.8.jar:na]
    at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java) ~[HikariCP-2.7.8.jar:na]
    at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:54) ~[hibernate-core-5.2.16.Final.jar:5.2.16.Final]
    ... 40 common frames omitted
Caused by: java.sql.SQLException: Table 'cardb.car' doesn't exist
Query is: alter table car drop foreign key FK2mqqwvxtowv4vddvtsmvtiqa2
    at org.mariadb.jdbc.internal.util.LogQueryTool.exceptionWithQuery(LogQueryTool.java:119) ~[mariadb-java-client-2.2.3.jar:na]
    at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:199) ~[mariadb-java-client-2.2.3.jar:na]
    at org.mariadb.jdbc.MariaDbStatement.executeInternal(MariaDbStatement.java:328) ~[mariadb-java-client-2.2.3.jar:na]
    ... 44 common frames omitted

[ . . . Mapping details here, omitted]

2018-07-25 16:56:27.118  INFO 12414 --- [  restartedMain] o.h.t.schema.internal.SchemaCreatorImpl  : HHH000476: Executing import script 'org.hibernate.tool.schema.internal.exec.ScriptSourceInputNonExistentImpl@23352c6f'
2018-07-25 16:56:27.121  INFO 12414 --- [  restartedMain] j.LocalContainerEntityManagerFactoryBean : Initialized JPA EntityManagerFactory for persistence unit 'default'
2018-07-25 16:56:28.047  INFO 12414 --- [  restartedMain] o.s.s.web.DefaultSecurityFilterChain     : Creating filter chain: org.springframework.security.web.util.matcher.AnyRequestMatcher@1, [org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter@7ec73b7e, org.springframework.security.web.context.SecurityContextPersistenceFilter@6f233313, org.springframework.security.web.header.HeaderWriterFilter@1e641fa3, org.springframework.security.web.csrf.CsrfFilter@7c5a0312, org.springframework.security.web.authentication.logout.LogoutFilter@176619be, org.springframework.security.web.authentication.UsernamePasswordAuthenticationFilter@26add7c1, org.springframework.security.web.authentication.ui.DefaultLoginPageGeneratingFilter@4030f929, org.springframework.security.web.authentication.www.BasicAuthenticationFilter@2ea06fad, org.springframework.security.web.savedrequest.RequestCacheAwareFilter@6ce594ae, org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter@6159e3c1, org.springframework.security.web.authentication.AnonymousAuthenticationFilter@51157300, org.springframework.security.web.session.SessionManagementFilter@5be9c504, org.springframework.security.web.access.ExceptionTranslationFilter@4506e3d3, org.springframework.security.web.access.intercept.FilterSecurityInterceptor@1f44843c]
org.springframework.http.HttpEntity<org.springframework.hateoas.ResourceSupport> org.springframework.data.rest.webmvc.ProfileController.listAllFormsOfMetadata()
2018-07-25 16:56:28.632  INFO 12414 --- [  restartedMain] o.s.b.d.a.OptionalLiveReloadServer       : LiveReload server is running on port 35729
2018-07-25 16:56:28.668  INFO 12414 --- [  restartedMain] o.s.j.e.a.AnnotationMBeanExporter        : Registering beans for JMX exposure on startup
2018-07-25 16:56:28.670  INFO 12414 --- [  restartedMain] o.s.j.e.a.AnnotationMBeanExporter        : Bean with name 'dataSource' has been autodetected for JMX exposure
2018-07-25 16:56:28.676  INFO 12414 --- [  restartedMain] o.s.j.e.a.AnnotationMBeanExporter        : Located MBean 'dataSource': registering with JMX server as MBean [com.zaxxer.hikari:name=dataSource,type=HikariDataSource]
2018-07-25 16:56:28.712  INFO 12414 --- [  restartedMain] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat started on port(s): 8080 (http) with context path ''
2018-07-25 16:56:28.715  INFO 12414 --- [  restartedMain] c.p.cardatabase.CardatabaseApplication   : Started CardatabaseApplication in 4.726 seconds (JVM running for 5.165)

这是我的仓库中的代码: https://github.com/Kallaste/spring_login_server_example_so

Answer 1

如果您的应用程序仍在运行，我不会担心。 Hibernate 在启动时会抛出很多“信息性”错误。它只是试图弄清楚它必须做什么才能使数据库处于您的应用程序可以运行的状态。

如果您使用 create-drop 或仅使用 create 应用程序运行正常，则尝试使用 create 运行它一次，这样一切都正常生成，然后将其更改为 validate，这样下次它只会验证您的数据库架构。

在生产环境中，您通常不会让 Hibernate 自行更新数据库模式，而是手动应用更改并迁移数据，除非您非常确定这些更改会被正确应用。