CODEWITHSUNDEEP
mysqlspring-boot
JoseLuis
JoseLuis

Reputation: 113

Spring boot auto create table in mysql fail

I meet an following error, can not figure out. I suppose I can use Spring boot connect to mysql db. And it can create table auto. And this is connect to a docker container, not sure if this matters

spring.datasource.poolName=spring-jpa
spring.datasource.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&allowPublicKeyRetrieval=true&useSSL=false&serverTimezone=UTC&createDatabaseIfNotExist=true
spring.datasource.username=root
spring.datasource.password=1q2w3e4R
spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver
server.session.timeout=8640000
spring.jpa.database-platform=org.hibernate.dialect.MySQLDialect

spring.jpa.hibernate.ddl-auto=create-drop
spring.jpa.hibernate.use-new-id-generator-mappings=false

The error is:

    org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "create table subscription (id bigint not null auto_increment, consent bit, email varchar(255), first_name varchar(255), gender varchar(255), newsletter_id integer, primary key (id)) type=MyISAM" via JDBC Statement
    at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:67) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
....
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=MyISAM' at line 1
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) ~[mysql-connector-java-8.0.22.jar:8.0.22]
....

This is the subscription entity class: import lombok.Getter; import lombok.Setter;

import javax.annotation.Generated;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.EnumType;
import javax.persistence.Enumerated;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Getter
@Setter
@Entity
@Table
public class Subscription {

    @Id
    @GeneratedValue(strategy= GenerationType.AUTO)
    private long id;

    @Column
    private String email;

    @Column
    private String firstName;

    @Enumerated(EnumType.STRING)
    @Column
    private Gender gender;

    @Column
    private boolean consent;

    @Column
    private int newsletterId;

}

This is the Gender class

public enum Gender {
    MALE, FEMALE
}

Upvotes: 1

Views: 1162

Answers (1)

Shreyas B
Shreyas B

Reputation: 505

This is caused because type=MyISAM was deprecated since MySQL 4.x. The property

spring.jpa.database-platform=org.hibernate.dialect.MySQLDialect

Implies hibernate to use the initial Dialect. Changing it to

spring.jpa.database-platform=org.hibernate.dialect.MySQL5Dialect

Will generate SQL for MySQL 5.0+, with engine=MyISAM instead of type=MyISAM , similarly based on your MySQL version, change your dialect.

Example if you have MySQL 7 use org.hibernate.dialect.MySQL7Dialect

Upvotes: 4

Related Questions