org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL

Question

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

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

`TYPE` has been replaced with `ENGINE`. Use `ENGINE = Innodb` instead of `type=InnoDB`. Replace `org.hibernate.dialect.MySQLInnoDBDialect ` with `org.hibernate.dialect.MySQL5InnoDBDialect` — Romil Patel, Jan 09 '20 at 16:41

akhil · Answer 1 · 2022-10-16T13:06:59.840

We get this error on using reserved keywords of MYSQL. example for reserved keywords: order Don't name entities with reserved keywords. here is the link for reference

The solution to this can be renaming your entity or add this property

spring.jpa.properties.hibernate.globally_quoted_identifiers=true

in your application.properties file

or

spring
  jpa:
    properties:
      hibernate:
        globally_quoted_identifiers: true

to the application.yaml file

score 8 · Accepted Answer · answered Jan 09 '20 at 21:38

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?

Changing dialect to MySql5 was sufficient to avoid the exception, Thanks for your reply. — , Jan 11 '20 at 14:45
You won't get the exception after using MySQL5InnoDBDialect, but still table won't remain created in the database using create-drop. — itsSKP, Jan 13 '20 at 09:02

Romil Patel · Answer 3 · 2020-01-09T16:50:45.793

3

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

edited Jan 09 '20 at 16:50

answered Jan 09 '20 at 16:44

Romil Patel

12,879
7
47
76

How can i change Type to Engine? – Jan 09 '20 at 16:51
Hi Tushar, Does the error is the same or something new is there? Have you created a database? – Romil Patel Jan 09 '20 at 16:51
yeah Db is already created, error after the change is same, thanks – Jan 09 '20 at 16:56
Please update the question with latest console logs. Do have any SQL script file and which version of spring boot and MySQL are you using? – Romil Patel Jan 09 '20 at 17:09
Console logs are the same Spring boot version: (v2.2.2.RELEASE) Mysql Version: 8.0 – Jan 09 '20 at 17:20
I 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. – pixel Oct 13 '21 at 16:40

score 1 · Answer 4 · answered Oct 21 '21 at 12:47

1

enter application.properties file

spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5InnoDBDialect hibernate.dialect.storage_engine=innodb spring.jpa.database-platform: org.hibernate.dialect.MySQL5InnoDBDialect

then run, it will work.

answered Oct 21 '21 at 12:47

Dilusha Amarasekara

79
2

score 1 · Answer 5 · edited May 08 '23 at 02:03

1

We get this error on using reserved keywords of MYSQL. example for reserved keywords: order Don't name entities with reserved keywords. here is the link for reference..

simply add this into your properties file.

spring.jpa.properties.hibernate.globally_quoted_identifiers=true

edited May 08 '23 at 02:03

desertnaut

57,590
26
140
166

answered May 04 '23 at 15:43

Prashanth69116

11
2

score 0 · Answer 6 · answered Jan 29 '21 at 10:43

0

In my case, I have a string field in Entity class like

@Column(name = "text", length = 65535)
private String text;

After changing it like below issue is resolved.

@Lob
@Column(name = "text")
private String text;

answered Jan 29 '21 at 10:43

Shivani

51
1
9

score 0 · Answer 7 · answered Mar 16 '22 at 14:54

0

I realized the exception will be the same if the mysql engine type is different from the engine type of the table you want to connect to!

answered Mar 16 '22 at 14:54

anzek informatika kft

1

1

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](/help/how-to-answer). – Community Mar 17 '22 at 22:13

score 0 · Answer 8 · answered Mar 02 '23 at 05:06

0

if you have Graddle Project or Maven. Try to refresh or update the project.

answered Mar 02 '23 at 05:06

Krishn Kanhaiya Garg

1

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](/help/how-to-answer). – Community Mar 09 '23 at 02:28

score 0 · Answer 9 · answered Aug 26 '23 at 21:32

0

I have solved by changing the entity name from "Order" to "OrderEntity" in spring boot. in mysql 8 version Order represents some db specific

answered Aug 26 '23 at 21:32

user22452786

1

org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL

9 Answers9