'Getting following error when creating tables via JPA org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL
Not sure why I am getting this error. I have tried to change the property file with dilects none is working.
I am trying to create DB tables via JPA, totally new to Springboot and java can someone please take a look?
Am I doing something wrong with relationship (one to many or many to one ) why I am getting this error?
Error:
2022-03-27 21:44:40.523 INFO 13500 --- [ restartedMain] o.s.b.w.embedded.tomcat.TomcatWebServer : Tomcat initialized with port(s): 8080 (http)
2022-03-27 21:44:40.544 INFO 13500 --- [ restartedMain] o.apache.catalina.core.StandardService : Starting service [Tomcat]
2022-03-27 21:44:40.544 INFO 13500 --- [ restartedMain] org.apache.catalina.core.StandardEngine : Starting Servlet engine: [Apache Tomcat/9.0.58]
2022-03-27 21:44:40.737 INFO 13500 --- [ restartedMain] o.a.c.c.C.[Tomcat].[localhost].[/] : Initializing Spring embedded WebApplicationContext
2022-03-27 21:44:40.738 INFO 13500 --- [ restartedMain] w.s.c.ServletWebServerApplicationContext : Root WebApplicationContext: initialization completed in 3613 ms
2022-03-27 21:44:41.209 INFO 13500 --- [ restartedMain] o.hibernate.jpa.internal.util.LogHelper : HHH000204: Processing PersistenceUnitInfo [name: default]
2022-03-27 21:44:41.334 INFO 13500 --- [ restartedMain] org.hibernate.Version : HHH000412: Hibernate ORM core version 5.6.5.Final
2022-03-27 21:44:41.738 INFO 13500 --- [ restartedMain] o.hibernate.annotations.common.Version : HCANN000001: Hibernate Commons Annotations {5.1.2.Final}
2022-03-27 21:44:41.967 INFO 13500 --- [ restartedMain] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...
2022-03-27 21:44:42.238 INFO 13500 --- [ restartedMain] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.
2022-03-27 21:44:42.260 INFO 13500 --- [ restartedMain] org.hibernate.dialect.Dialect : HHH000400: Using dialect: org.hibernate.dialect.MySQL5InnoDBDialect
Hibernate: create table `book` (`book_id` integer not null, `available` `TINYINT(1)` not null, `isbn` varchar(255), `price` decimal(19,2), `title` varchar(255), `publisher_id` integer not null, primary key (`book_id`)) engine=InnoDB
2022-03-27 21:44:43.520 WARN 13500 --- [ restartedMain] o.h.t.s.i.ExceptionHandlerLoggedImpl : GenerationTarget encountered exception accepting command : Error executing DDL "create table `book` (`book_id` integer not null, `available` `TINYINT(1)` not null, `isbn` varchar(255), `price` decimal(19,2), `title` varchar(255), `publisher_id` integer not null, primary key (`book_id`)) engine=InnoDB" via JDBC Statement
org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "create table `book` (`book_id` integer not null, `available` `TINYINT(1)` not null, `isbn` varchar(255), `price` decimal(19,2), `title` varchar(255), `publisher_id` integer not null, primary key (`book_id`)) engine=InnoDB" via JDBC Statement
at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:67) ~[hibernate-core-5.6.5.Final.jar:5.6.5.Final]
at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applySqlString(AbstractSchemaMigrator.java:581) ~[hibernate-core-5.6.5.Final.jar:5.6.5.Final]
at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applySqlStrings(AbstractSchemaMigrator.java:526) ~[hibernate-core-5.6.5.Final.jar:5.6.5.Final]
at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.createTable(AbstractSchemaMigrator.java:293) ~[hibernate-core-5.6.5.Final.jar:5.6.5.Final]
at org.hibernate.tool.schema.internal.GroupedSchemaMigratorImpl.performTablesMigration(GroupedSchemaMigratorImpl.java:74) ~[hibernate-core-5.6.5.Final.jar:5.6.5.Final]
at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.performMigration(AbstractSchemaMigrator.java:220) ~[hibernate-core-5.6.5.Final.jar:5.6.5.Final]
at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.doMigration(AbstractSchemaMigrator.java:123) ~[hibernate-core-5.6.5.Final.jar:5.6.5.Final]
at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:192) ~[hibernate-core-5.6.5.Final.jar:5.6.5.Final]
at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:81) ~[hibernate-core-5.6.5.Final.jar:5.6.5.Final]
at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:335) ~[hibernate-core-5.6.5.Final.jar:5.6.5.Final]
at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:471) ~[hibernate-core-5.6.5.Final.jar:5.6.5.Final]
at
at java.base/java.lang.reflect.Method.invoke(Method.java:567) ~[na:na]
at org.springframework.boot.devtools.restart.RestartLauncher.run(RestartLauncher.java:49) ~[spring-boot-devtools-2.6.4.jar:2.6.4]
Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'not null, `isbn` varchar(255), `price` decimal(19,2), `title` varchar(255), `...' at line 1
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) ~[mysql-connector-java-8.0.28.jar:8.0.28]
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.28.jar:8.0.28]
at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:763) ~[mysql-connector-java-8.0.28.jar:8.0.28]
at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:648) ~[mysql-connector-java-8.0.28.jar:8.0.28]
at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:94) ~[HikariCP-4.0.3.jar:na]
at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java) ~[HikariCP-4.0.3.jar:na]
at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:54) ~[hibernate-core-5.6.5.Final.jar:5.6.5.Final]
... 39 common frames omitted
Book.java
package com.example.jpademo2.models;
import java.math.BigDecimal;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.Table;
import com.fasterxml.jackson.annotation.*;
import lombok.Getter;
import lombok.Setter;
@Entity
@Table(name = "book")
@Getter
@Setter
public class Book {
@Id
@GeneratedValue(strategy=GenerationType.AUTO)
private Integer bookId;
private String isbn;
private String title;
private BigDecimal price;
@Column(nullable = false, columnDefinition = "TINYINT(1)")
private boolean available;
@ManyToOne(fetch = FetchType.LAZY, optional = false)
@JoinColumn(name = "publisherId", nullable = false)
@JsonIgnore
private Publisher publisher;
public Book(String isbn, String title, BigDecimal price, boolean available, Publisher publisher) {
this.isbn = isbn;
this.title = title;
this.price = price;
this.available = available;
this.publisher = publisher;
}
public Book() {
}
@Override
public int hashCode() {
final int prime = 31;
int result = 1;
result = prime * result + ((bookId == null) ? 0 : bookId.hashCode());
return result;
}
@Override
public boolean equals(Object obj) {
if (this == obj)
return true;
if (obj == null)
return false;
if (getClass() != obj.getClass())
return false;
Book other = (Book) obj;
if (bookId == null) {
if (other.bookId != null)
return false;
} else if (!bookId.equals(other.bookId))
return false;
return true;
}
}
Publisher.java
package com.example.jpademo2.models;
import java.util.HashSet;
import java.util.Set;
import javax.persistence.CascadeType;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.Table;
import lombok.EqualsAndHashCode;
import lombok.Getter;
import lombok.Setter;
@Entity
@Table(name = "publisher")
@Getter
@Setter
@EqualsAndHashCode
public class Publisher {
@Id
@GeneratedValue(strategy=GenerationType.AUTO)
@EqualsAndHashCode.Include
private Integer publisherId;
private String publisherName;
@OneToMany(fetch = FetchType.LAZY, cascade = CascadeType.ALL)
private Set<Book> books;
public Publisher(String publisherName) {
this.publisherName = publisherName;
this.books = new HashSet<Book>();
}
public Publisher() {
this("");
}
}
application.properties
spring.datasource.url=jdbc:mysql://localhost/mydb3?useSSL=false
spring.datasource.username=root
spring.datasource.password=
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql: true
spring.jpa.properties.hibernate.globally_quoted_identifiers=true
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect
tried these as well
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5InnoDBDialect hibernate.dialect.storage_engine=innodb spring.jpa.database-platform: org.hibernate.dialect.MySQL57InnoDB
Solution 1:[1]
Looks like the columnDefinition for the "available" field isn't escaped properly, the backticks "`" around tinyint(1) are causing the problem. Maybe you should try to swap to the MariaDB hibernate dialect, see https://docs.jboss.org/hibernate/orm/5.6/userguide/html_single/Hibernate_User_Guide.html#database-dialect.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 |