H2 database used as volatile for Spring test: cannot create table

Question

I'm using Spring 2.7.7.

I have several entities, e.g.

@Table(name = "users")
public class UsersEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", columnDefinition = "INT(11) UNSIGNED")
    private Long id;

    @Column(name = "username", columnDefinition = "VARCHAR(256)")
    private String username;

    @Column(name = "password", columnDefinition = "VARCHAR(256)")
    private String password;

    @ManyToMany(fetch = FetchType.LAZY)
    @JoinTable(name = "users_roles", joinColumns = @JoinColumn(name = "users_id"), inverseJoinColumns = @JoinColumn(name = "roles_id"))
    @Builder.Default
    private Set<RolesEntity> rolesEntity = new HashSet<>();

}

This is the application.properties used in tests:

spring.profiles.active=test
spring.datasource.driver-class-name=org.h2.Driver
spring.datasource.url=jdbc:h2:mem:db;DB_CLOSE_DELAY=-1
spring.datasource.username=sa
spring.datasource.password=sa
spring.jpa.hibernate.ddl-auto=create-drop
spring.jpa.properties.hibernate.jdbc.time_zone=UTC

Until Spring 2.6.14 it worked, now I got errors in creation table as following:

org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "create table users (id INT(11) UNSIGNED not null auto_increment, password VARCHAR(256), username VARCHAR(256), primary key (id)) engine=InnoDB" via JDBC Statement

and, after some line:

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "create table users (id INT[*](11) UNSIGNED not null auto_increment, password VARCHAR(256), username VARCHAR(256), primary key (id)) engine=InnoDB"; expected "ARRAY, INVISIBLE, VISIBLE, NOT NULL, NULL, AS, DEFAULT, GENERATED, ON UPDATE, NOT NULL, NULL, AUTO_INCREMENT, DEFAULT ON NULL, NULL_TO_DEFAULT, SEQUENCE, SELECTIVITY, COMMENT, CONSTRAINT, COMMENT, PRIMARY KEY, UNIQUE, NOT NULL, NULL, CHECK, REFERENCES, AUTO_INCREMENT, ,, )"; SQL statement:
create table users (id INT(11) UNSIGNED not null auto_increment, password VARCHAR(256), username VARCHAR(256), primary key (id)) engine=InnoDB [42001-214]

I know the error is new version of H2 that doesn't accept anymore MySql commands, like INT(11) UNSIGNED. If I remove it from table definition, tests work.

So, I tried setting in application.properties as following:

spring.datasource.url=jdbc:h2:mem:db;DB_CLOSE_DELAY=-1;MODE=MYSQL
# also change capital of MySql
# spring.datasource.url=jdbc:h2:mem:db;DB_CLOSE_DELAY=-1;MODE=MySQL
spring.jpa.database-platform=org.hibernate.dialect.MySQL8Dialect

without luck.

Now, how can I solve, without editing totally the table definition?

Repository

The repository is here, https://github.com/sineverba/online-banking-backend/tree/develop develop branch.

If you clone it and change pom.xml as

<parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.7.7</version>
        <relativePath /> <!-- lookup parent from repository -->
    </parent>

instead of 2.6.14 you can replicate the error.

One of affected test is [src/test/java/com/bitbank/repositories/BankAccountTransactionsRepositoryTest.java][1] but generally are all repositories.

Changing application.properties under `src/test/resources

Following comment of https://stackoverflow.com/users/11731987/evgenij-ryazanov

I want to be sure that my application properties, under test, is used.

So, I tried:

1. Edit the BankAccountTransactionsRepository linked above adding @TestPropertySource("classpath:application.properties")

2. Adding the @TestPropertySource("classpath:application-notexists.properties") and really test crashed (Failed to load application and class path resource [application-notexists.properties] cannot be opened because it does not exist)

3. Renamed the application.properties in src/test/resources as application-notexists.properties and Spring started (but Unit test have errors of Mysql)

Answer 1

At the end, I solved adding

@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)

to every repository file (see also How to add the mode=mysql to embedded H2 DB in Spring Boot 1.4.1 for @DataJpaTest?)

Thank to all and every one, also EvgeniJ that pointed me on right direction.