CODEWITHSUNDEEP
hibernatespring-bootjpa
user7024438
user7024438

Reputation:

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

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)

Upvotes: 9

Views: 67414

Answers (9)

user22452786
user22452786

Reputation: 1

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

Upvotes: 0

Prashanth69116
Prashanth69116

Reputation: 21

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

Upvotes: 2

Krishn Kanhaiya Garg
Krishn Kanhaiya Garg

Reputation: 1

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

Upvotes: 0

akhil
akhil

Reputation: 121

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

Upvotes: 11

anzek informatika kft
anzek informatika kft

Reputation: 1

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!

Upvotes: 0

Dilusha Amarasekara
Dilusha Amarasekara

Reputation: 77

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.

Upvotes: 1

Shivani
Shivani

Reputation: 76

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;

Upvotes: 0

itsSKP
itsSKP

Reputation: 134

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?

Upvotes: 11

Romil Patel
Romil Patel

Reputation: 13727

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

Upvotes: 3

Related Questions