Spring Boot H2 db for testing throws "Table not found" and "SQL syntax error"

Question

I develop simple Store app with Spring boot. My app uses MySQL database deployed in the docker container and it works fine. Now I want to test my app and I came to the conclusion that H2 in-memory db would be enough for testing. As I'm fresh to testing spring apps, I encountered errors associated with creating tables in the H2 test database. Those are errors which I came across

First error:

    alter table store_order_items 
       drop 
       foreign key FKikqpbj6xmmyoblsolyhk250tq" via JDBC Statement
    at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:67) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
    at org.hibernate.tool.schema.internal.SchemaDropperImpl.applySqlString(SchemaDropperImpl.java 
[...]
Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "STORE_ORDER_ITEMS" not found (this database is empty); SQL statement:

Second error:

org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "
    create table store_order_items (
       Id bigint not null auto_increment,
        quantity bigint,
        order_Id bigint,
        product_Id bigint,
        primary key (Id)
    ) engine=InnoDB" via JDBC Statement
    at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:67) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
    at org.hibernate.tool.schema.internal.SchemaCreatorImpl.applySqlString(SchemaCreatorImpl.java:458) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
[...]
Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "\000a    create table store_order_items (\000a       Id bigint not null auto_increment,\000a        quantity bigint,\000a        order_Id bigint,\000a        product_Id bigint,\000a        primary key (Id)\000a    ) engine[*]=InnoDB"; expected "identifier"; SQL statement:

src/test/resources/application.properties:

spring.datasource.url=jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DATABASE_TO_UPPER=false;

spring.datasource.username=sa
spring.datasource.password=
spring.datasource.driver-class-name=org.h2.Driver
spring.jpa.database-platform=org.hibernate.dialect.MySQL5InnoDBDialect
spring.h2.console.enabled=true


logging.level.org.hibernate.SQL=DEBUG
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true

spring.jpa.hibernate.ddl-auto=create
spring.jpa.generate-ddl=true
spring.jpa.defer-datasource-initialization=true

spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

Test class:

@ExtendWith(SpringExtension.class)
@SpringBootTest
@Transactional
public class UserRepositoryTests {

    private static final Logger LOGGER = Logger.getLogger(UserRepositoryTests.class.getName());

    @Autowired
    private UserRepositoryImpl userRepository;

    @Autowired
    private PasswordEncoder encoder;


    @Test
    void contextLoads() {
        System.out.println(encoder.encode("123"));
// dummy test just to test if tables are built properly
    }

model/OrderItem.java:

@Entity
@Table(name = "store_order_items")
@NamedQueries({
        @NamedQuery(name=OrderItem.ORDER_ITEM_FIND_ALL, query=OrderItem.ORDER_ITEM_FIND_ALL_JPQL)
})
public class OrderItem {

    // get list of all orders
    public static final String ORDER_ITEM_FIND_ALL = "orderItemAll";
    public static final String ORDER_ITEM_FIND_ALL_JPQL = "SELECT oi FROM OrderItem oi";


    // id will be replaced with two foreign keys
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long Id;

    private Long quantity;

    @JsonBackReference
    @ManyToOne
    private Order order;

    @JsonBackReference
    @ManyToOne
    private Product product;

// getters and setters omitted
}

model/Order.java:

@Entity
@Table(name = "store_orders")
@NamedQueries({
        @NamedQuery(name=Order.ORDER_FIND_ALL, query=Order.ORDER_FIND_ALL_JPQL),
        @NamedQuery(name=Order.ORDER_SUMMARIES, query=Order.ORDER_SUMMARIES_JPQL)
})
public class Order {

// named queries omitted

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long Id;

    private String orderDate;

    @JsonBackReference
    @ManyToOne
    private User user;

    @JsonManagedReference
    @OneToMany(mappedBy = "order", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    private List<OrderItem> orderItems = new ArrayList<>();

// getters and setters omitted
}

I tried changing column names or using different spring.datasource.url's but id didn't work. I can upload more source code if needed.

Answer 1

Based on our conversation on the comments, using the parameter MODE worked for OP. So the solution was to change this configuration from:

spring.datasource.url=jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DATABASE_TO_UPPER=false;

To

spring.datasource.url=jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DATABASE_TO_UPPER=false;MODE=MySQL

Note the MODE=MySQL added to the end. This will make H2 database implementation to ignore or replace certain MySQL reserved words with its (H2) equivalent ones.

Op also had to change another configuration for it to solve all problems and the other one as setting the ddl-auto setting from:

spring.jpa.hibernate.ddl-auto=create

To

spring.jpa.hibernate.ddl-auto=update

Please also note comment from Evgenij Ryazanov so check the version you are in and correct it accordingly:

DATABASE_TO_UPPER=FALSE should only be used with H2 1.4.197 and older versions, for H2 1.4.198 Beta and newer versions DATABASE_TO_LOWER=TRUE should be specified with MySQL compatibility mode, otherwise all unquoted identifiers will be case-sensitive. By