How to select InnoDB or XtraDB as storage engine in MariaDB in Spring Boot 2 JPA application

user_2211478 0
Viewed 6609

I am developing a new application using Spring Boot 2.0.0.M6 and Spring Data JPA. I am using MariaDB v10. Below is my dev properties file.

spring.jpa.hibernate.ddl-auto=create-drop
spring.datasource.url=jdbc:mariadb://localhost:3306/testdb
spring.datasource.username=user
spring.datasource.password=
spring.jpa.show-sql=true
spring.datasource.driver-class-name=org.mariadb.jdbc.Driver
org.hibernate.dialect.Dialect=MariaDB53Dialect
spring.jooq.sql-dialect=MariaDB53Dialect

I get output:

Hibernate: create table hibernate_sequence (next_val bigint) engine=MyISAM

I am not able to change the storage engine. All the tables are being created using storage engine MyISAM.

I am able to create tables manually using other storage engines. But for some reason Spring or Hibernate falls back to MyISAM engine only.

With pure Hibernate-Java application, Hibernate uses InnoDB as default.

INFO: HHH000412: Hibernate Core {5.2.11.Final}

Hibernate: create table hibernate_sequence (next_val bigint) engine=InnoDB

Is there any way to override Database storage engine from the Spring Boot properties?

spring-data-jpa mariadb spring-boot hibernate spring
2 Answers

As described in Spring Boot's documentation, all properties prefixed with spring.jpa.properties are passed through to the underlying JPA provider (Hibernate in this case) with the prefix removed.

The Hibernate property to configure the dialect is hibernate.dialect and its value should be the fully qualified class name of the dialect that you want to use. In this case that's org.hibernate.dialect.MariaDB53Dialect.

Putting the above together, you could set the following property in your application.properties:

 spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MariaDB53Dialect

With this in place your Spring Boot-based application uses the MariaDB dialect:

2017-11-09 14:18:17.557  INFO 69955 --- [ost-startStop-1] org.hibernate.dialect.Dialect            : HHH000400: Using dialect: org.hibernate.dialect.MariaDB53Dialect
user_13842970

With Hibernate 5.2.12, if I run the MySQLStoredProcedureTest while setting the dialect to MariaDB:

@RequiresDialect(MariaDB53Dialect.class)
public class MySQLStoredProcedureTest 
        extends BaseEntityManagerFunctionalTestCase {

    @Override
    protected Class<?>[] getAnnotatedClasses() {
        return new Class<?>[] {
            Person.class,
            Phone.class,
        };
    }
    
    ...
    
}

The Post entity is mapped as follows:

@Entity
public class Person {

    @Id
    @GeneratedValue
    private Long id;

    private String name;

    private String nickName;

    private String address;

    @Temporal(TemporalType.TIMESTAMP )
    private Date createdOn;

    @OneToMany(mappedBy = "person", cascade = CascadeType.ALL)
    @OrderColumn(name = "order_id")
    private List<Phone> phones = new ArrayList<>();

    @Version
    private int version;

    //Getters and setter omitted for brevity
}

And, when I run the test on MariaDB, Hibernate generates the following schema:

create table Person (
   id bigint not null,
    address varchar(255),
    createdOn datetime(6),
    name varchar(255),
    nickName varchar(255),
    version integer not null,
    primary key (id)
) engine=InnoDB

That's because MariaDB53Dialect extends the MariaDBDialect which uses the InnoDBStorageEngine:

public class MariaDBDialect extends MySQL5Dialect {
    public MariaDBDialect() {
        super();
    }

    public boolean supportsRowValueConstructorSyntaxInInList() {
        return true;
    }

    @Override
    protected MySQLStorageEngine getDefaultMySQLStorageEngine() {
        return InnoDBStorageEngine.INSTANCE;
    }
}

So, it's impossible to get MyISAM with MariaDB53Dialect when generating the schema with hbm2ddl.

However, you should only be using hbm2ddl to generate the initial script. In a production environment, you should use a tool like FlywayDB.

We actually wrote this in the Hibernate User Guide:

Although the automatic schema generation is very useful for testing and prototyping purposes, in a production environment, it’s much more flexible to manage the schema using incremental migration scripts.

We changed this in Hibernate 5.2.8, so I suppose you are using an older version instead, otherwise, there is no explanation why you'd see MyISAM in your hbm2ddl auto-generated schema.

Check the dependencies using:

mvn dependency:tree

and make sure you are really using Hibernate 5.2.12.

user_10251180
Related