org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL
19,825
Solution 1
You need to just change
spring.jpa.hibernate.ddl-auto property to update or create
and
spring.jpa.properties.hibernate.dialect to org.hibernate.dialect.MySQL5InnoDBDialect (as suggested by Patel Romil
) and everything will work fine.
The create-drop is generally used for testing purpose, when you want to create a table on application startup, perform some db transactions in your tests and then drop the table on test case cleanup. The table won't exist in database after the test case execution gets completed.
Now coming to spring.jpa.properties.hibernate.dialect , using org.hibernate.dialect.MySQL5InnoDBDialect instead of org.hibernate.dialect.MySQLInnoDBDialect makes Hibernate to append engine=InnoDB instead of type=InnoDB to the query, so your queries will be syntactically correct ( FYI, type= InnoDB was deprecated in MySQL 5.0 and was removed in My SQL 5.1)
For more details about spring.jpa.hibernate.ddl-auto property and it's value, here is an accepted answer How does spring.jpa.hibernate.ddl-auto property exactly work in Spring?
Solution 2
Replace
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQLInnoDBDialect
To
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5InnoDBDialect
hibernate.dialect.storage_engine=innodb
spring.jpa.database-platform: org.hibernate.dialect.MySQL5InnoDBDialect
Author by
Admin
Updated on August 23, 2022Comments
-
Admin over 1 year
On trying to create the above entity I am getting CommandAcceptanceException and the table is not able to get created in the database, please tell me how to fix it.
application.properties file
spring.datasource.url= jdbc:mysql://localhost:3307/mapping spring.datasource.username=root spring.datasource.password=admin spring.jpa.hibernate.ddl-auto=create-drop spring.jpa.show-sql=true spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQLInnoDBDialectJava Class:
@Entity @Table(name="BOOKS") public class Book { @Id @Column(name="bookId") private Long id; @Column(name="TITLE") private String title; @Column(name="AUTHOR") private String author; public Book(Long id, String title, String author) { super(); this.id = id; this.title = title; this.author = author; } public Long getId() { return id; } public void setId(Long id) { this.id = id; } public String getTitle() { return title; } public void setTitle(String title) { this.title = title; } public String getAuthor() { return author; } public void setAuthor(String author) { this.author = author; } public Book() { super(); // TODO Auto-generated constructor stub } }build.gradle file:
plugins { id 'org.springframework.boot' version '2.2.2.RELEASE' id 'io.spring.dependency-management' version '1.0.8.RELEASE' id 'java' } group = 'org.springframework.hib' version = '0.0.1-SNAPSHOT' sourceCompatibility = '1.8' repositories { mavenCentral() } dependencies { implementation 'org.springframework.boot:spring-boot-starter-data-jpa' implementation 'org.springframework.boot:spring-boot-starter-web' testCompile group: 'com.h2database', name: 'h2', version: '1.4.200' runtimeOnly 'mysql:mysql-connector-java' testImplementation('org.springframework.boot:spring-boot-starter-test') { exclude group: 'org.junit.vintage', module: 'junit-vintage-engine' } } test { useJUnitPlatform() }Console: . ____ _ __ _ _ /\ / ' __ _ ()_ __ __ _ \ \ \ \ ( ( )_ | '_ | '| | ' / ` | \ \ \ \ \/ )| |)| | | | | || (| | ) ) ) ) ' |____| .|| ||| |__, | / / / / =========|_|==============|___/=///_/ :: Spring Boot :: (v2.2.2.RELEASE)
2020-01-09 21:52:36.380 INFO 14108 --- [ main] com.learn.hib.LearnHibernateApplication : Starting LearnHibernateApplication on LAPTOP-B759SS03 with PID 14108 (E:\learn-space\learn-hibernate\bin\main started by Lenovo in E:\learn-space\learn-hibernate) 2020-01-09 21:52:36.380 INFO 14108 --- [ main] com.learn.hib.LearnHibernateApplication : No active profile set, falling back to default profiles: default 2020-01-09 21:52:36.974 INFO 14108 --- [ main] .s.d.r.c.RepositoryConfigurationDelegate : Bootstrapping Spring Data JPA repositories in DEFAULT mode. 2020-01-09 21:52:37.037 INFO 14108 --- [ main] .s.d.r.c.RepositoryConfigurationDelegate : Finished Spring Data repository scanning in 46ms. Found 1 JPA repository interfaces. 2020-01-09 21:52:37.302 INFO 14108 --- [ main] trationDelegate$BeanPostProcessorChecker : Bean 'org.springframework.transaction.annotation.ProxyTransactionManagementConfiguration' of type [org.springframework.transaction.annotation.ProxyTransactionManagementConfiguration] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying) 2020-01-09 21:52:37.521 INFO 14108 --- [ main] o.s.b.w.embedded.tomcat.TomcatWebServer : Tomcat initialized with port(s): 8080 (http) 2020-01-09 21:52:37.535 INFO 14108 --- [ main] o.apache.catalina.core.StandardService : Starting service [Tomcat] 2020-01-09 21:52:37.535 INFO 14108 --- [ main] org.apache.catalina.core.StandardEngine : Starting Servlet engine: [Apache Tomcat/9.0.29] 2020-01-09 21:52:37.644 INFO 14108 --- [ main] o.a.c.c.C.[Tomcat].[localhost].[/] : Initializing Spring embedded WebApplicationContext 2020-01-09 21:52:37.644 INFO 14108 --- [ main] o.s.web.context.ContextLoader : Root WebApplicationContext: initialization completed in 1217 ms 2020-01-09 21:52:37.816 INFO 14108 --- [ main] o.hibernate.jpa.internal.util.LogHelper : HHH000204: Processing PersistenceUnitInfo [name: default] 2020-01-09 21:52:37.878 INFO 14108 --- [ main] org.hibernate.Version : HHH000412: Hibernate Core {5.4.9.Final} 2020-01-09 21:52:38.003 INFO 14108 --- [ main] o.hibernate.annotations.common.Version : HCANN000001: Hibernate Commons Annotations {5.1.0.Final} 2020-01-09 21:52:38.097 INFO 14108 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting... 2020-01-09 21:52:39.206 INFO 14108 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed. 2020-01-09 21:52:39.216 INFO 14108 --- [ main] org.hibernate.dialect.Dialect : HHH000400: Using dialect: org.hibernate.dialect.MySQLInnoDBDialect Hibernate: create table books (book_id bigint not null, author varchar(255), title varchar(255), primary key (book_id)) type=InnoDB 2020-01-09 21:52:39.935 WARN 14108 --- [ main] o.h.t.s.i.ExceptionHandlerLoggedImpl : GenerationTarget encountered exception accepting command : Error executing DDL "create table books (book_id bigint not null, author varchar(255), title varchar(255), primary key (book_id)) type=InnoDB" via JDBC Statement org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "create table books (book_id bigint not null, author varchar(255), title varchar(255), primary key (book_id)) type=InnoDB" via JDBC Statement at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:67) ~[hibernate-core-5.4.9.Final.jar:5.4.9.Final] at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applySqlString(AbstractSchemaMigrator.java:559) [hibernate-core-5.4.9.Final.jar:5.4.9.Final] at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applySqlStrings(AbstractSchemaMigrator.java:504) [hibernate-core-5.4.9.Final.jar:5.4.9.Final] at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.createTable(AbstractSchemaMigrator.java:277) [hibernate-core-5.4.9.Final.jar:5.4.9.Final] at org.hibernate.tool.schema.internal.GroupedSchemaMigratorImpl.performTablesMigration(GroupedSchemaMigratorImpl.java:71) [hibernate-core-5.4.9.Final.jar:5.4.9.Final] at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.performMigration(AbstractSchemaMigrator.java:207) [hibernate-core-5.4.9.Final.jar:5.4.9.Final] at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.doMigration(AbstractSchemaMigrator.java:114) [hibernate-core-5.4.9.Final.jar:5.4.9.Final] at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:184) [hibernate-core-5.4.9.Final.jar:5.4.9.Final] at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:73) [hibernate-core-5.4.9.Final.jar:5.4.9.Final] at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:320) [hibernate-core-5.4.9.Final.jar:5.4.9.Final] at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:462) [hibernate-core-5.4.9.Final.jar:5.4.9.Final] at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:1237) [hibernate-core-5.4.9.Final.jar:5.4.9.Final] at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:58) [spring-orm-5.2.2.RELEASE.jar:5.2.2.RELEASE] at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:365) [spring-orm-5.2.2.RELEASE.jar:5.2.2.RELEASE] at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:391) [spring-orm-5.2.2.RELEASE.jar:5.2.2.RELEASE] at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.afterPropertiesSet(AbstractEntityManagerFactoryBean.java:378) [spring-orm-5.2.2.RELEASE.jar:5.2.2.RELEASE] at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.afterPropertiesSet(LocalContainerEntityManagerFactoryBean.java:341) [spring-orm-5.2.2.RELEASE.jar:5.2.2.RELEASE] at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1855) [spring-beans-5.2.2.RELEASE.jar:5.2.2.RELEASE] at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1792) [spring-beans-5.2.2.RELEASE.jar:5.2.2.RELEASE] at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:595) [spring-beans-5.2.2.RELEASE.jar:5.2.2.RELEASE] at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:517) [spring-beans-5.2.2.RELEASE.jar:5.2.2.RELEASE] at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:323) [spring-beans-5.2.2.RELEASE.jar:5.2.2.RELEASE] at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222) ~[spring-beans-5.2.2.RELEASE.jar:5.2.2.RELEASE] at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:321) [spring-beans-5.2.2.RELEASE.jar:5.2.2.RELEASE] at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:202) [spring-beans-5.2.2.RELEASE.jar:5.2.2.RELEASE] at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1108) ~[spring-context-5.2.2.RELEASE.jar:5.2.2.RELEASE] at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:868) ~[spring-context-5.2.2.RELEASE.jar:5.2.2.RELEASE] at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:550) ~[spring-context-5.2.2.RELEASE.jar:5.2.2.RELEASE] at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:141) ~[spring-boot-2.2.2.RELEASE.jar:2.2.2.RELEASE] at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:747) ~[spring-boot-2.2.2.RELEASE.jar:2.2.2.RELEASE] at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:397) ~[spring-boot-2.2.2.RELEASE.jar:2.2.2.RELEASE] at org.springframework.boot.SpringApplication.run(SpringApplication.java:315) ~[spring-boot-2.2.2.RELEASE.jar:2.2.2.RELEASE] at org.springframework.boot.SpringApplication.run(SpringApplication.java:1226) ~[spring-boot-2.2.2.RELEASE.jar:2.2.2.RELEASE] at org.springframework.boot.SpringApplication.run(SpringApplication.java:1215) ~[spring-boot-2.2.2.RELEASE.jar:2.2.2.RELEASE] at com.learn.hib.LearnHibernateApplication.main(LearnHibernateApplication.java:10) ~[main/:na] Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'type=InnoDB' at line 1 at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) ~[mysql-connector-java-8.0.18.jar:8.0.18] at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[mysql-connector-java-8.0.18.jar:8.0.18] at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.18.jar:8.0.18] at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:764) ~[mysql-connector-java-8.0.18.jar:8.0.18] at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:648) ~[mysql-connector-java-8.0.18.jar:8.0.18] at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:95) ~[HikariCP-3.4.1.jar:na] at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java) ~[HikariCP-3.4.1.jar:na] at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:54) ~[hibernate-core-5.4.9.Final.jar:5.4.9.Final] ... 34 common frames omitted 2020-01-09 21:52:39.950 INFO 14108 --- [ main] o.h.e.t.j.p.i.JtaPlatformInitiator : HHH000490: Using JtaPlatform implementation: [org.hibernate.engine.transaction.jta.platform.internal.NoJtaPlatform] 2020-01-09 21:52:39.950 INFO 14108 --- [ main] j.LocalContainerEntityManagerFactoryBean : Initialized JPA EntityManagerFactory for persistence unit 'default' 2020-01-09 21:52:40.028 WARN 14108 --- [ main] JpaBaseConfiguration$JpaWebConfiguration : spring.jpa.open-in-view is enabled by default. Therefore, database queries may be performed during view rendering. Explicitly configure spring.jpa.open-in-view to disable this warning 2020-01-09 21:52:40.528 INFO 14108 --- [ main] o.s.s.concurrent.ThreadPoolTaskExecutor : Initializing ExecutorService 'applicationTaskExecutor' 2020-01-09 21:52:40.837 INFO 14108 --- [ main] o.s.b.w.embedded.tomcat.TomcatWebServer : Tomcat started on port(s): 8080 (http) with context path '' 2020-01-09 21:52:40.837 INFO 14108 --- [ main] com.learn.hib.LearnHibernateApplication : Started LearnHibernateApplication in 4.831 seconds (JVM running for 6.101) -
Admin over 4 yearsHow can i change Type to Engine?
-
Romil Patel over 4 yearsHi Tushar, Does the error is the same or something new is there? Have you created a database? -
Admin over 4 yearsyeah Db is already created, error after the change is same, thanks
-
Romil Patel over 4 yearsPlease update the question with latest console logs. Do have any SQL script file and which version of spring boot and MySQL are you using?
-
Admin over 4 yearsConsole logs are the same Spring boot version: (v2.2.2.RELEASE) Mysql Version: 8.0
-
Admin over 4 yearsChanging dialect to MySql5 was sufficient to avoid the exception, Thanks for your reply.
-
itsSKP over 4 yearsYou won't get the exception after using MySQL5InnoDBDialect, but still table won't remain created in the database using create-drop.
-
pixel over 2 yearsI am getting the same error on H2 database if I add schema to my @Table annotation on my entity class. The problem is that DB2 database I use in production requires schema so I have to add it in@Table(name="CAR" schema="MYSCHEMA")annotation on my Car entity. However, in H2 database, this errors with same error. if I remove schema, then it works on H2 but not on DB2.