Introduction

In your Integration Tests (IT) you often try to use the H2 in-memory database, to improve the speed of your integration tests. But on the other hand you want to mimic the production database as much as possible in your integration-tests.

Setting H2 in MySQL database compatibility mode tries to emulate MySQL as much as possible, but only a small subset of the differences are implemented. What for example not works correctly in H2 for JSON fields is that it escapes strings with "". For that reason you usually want to switch to for example starting a Docker database testcontainer in your IT tests, which uses a real MySQL database. With the Java-specific version in https://github.com/testcontainers/testcontainers-java.

Configuration

There are several good-to-know tips when configuring the testcontainers in your ITs.

The simplest configuration is using a datasource URL in the Spring Boot properties file. This has the disadvantage that whatever database name you specify, the testcontainers library still creates a DB named 'test'. So below it will be named 'integration_test_db' you'd think, but it is still named 'test' when the IT runs:

spring.datasource.url=jdbc:tc:mysql:5.7.32:///integration_test_db?sessionVariables=sql_mode='STRICT_TRANS_TABLES'&TC_MY_CNF=mysql&TC_INITSCRIPT=mysql/init_mysql_integration_tests.sql

To be able to do everything on the started database, including giving it the name you want, use this URL (or see below the Java version). Notice the user 'root' in the URL:
spring.datasource.url=jdbc:tc:mysql:5.7.32:///integration_test_db?user=root&password=&sessionVariables=sql_mode='STRICT_TRANS_TABLES'&TC_MY_CNF=mysql&TC_INITSCRIPT=mysql/init_mysql_integration_tests.sql

Via: https://github.com/testcontainers/testcontainers-java/issues/932
To initialize your database, specify the script via this extra datasource URL variable:

TC_INITSCRIPT=mysql/init_mysql_integration_tests.sql

Note the default directory it looks in is ..../resources for the scripts. So the full path is ..../resources/mysql/
An example to prevent GROUP BY error from strict mode, add this in your TC_INITSCRIPT:

SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES';
SET SESSION sql_mode = 'STRICT_TRANS_TABLES';
To configure it in the IT Java class itself:

@RunWith(SpringRunner.class)
@SpringBootTest(classes = { SomeClassA.class, SomeClassB.class, ApplicationConfiguration.class}, webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
@TestPropertySource(locations = {
        "classpath:/application-test-mysql.properties" })
@ContextConfiguration(initializers = {ThisITClass.Initializer.class})

static class Initializer implements ApplicationContextInitializer<ConfigurableApplicationContext> {
        public void initialize(ConfigurableApplicationContext configurableApplicationContext) {
            TestPropertyValues.of(
                    "spring.datasource.url=" + mySQLContainer.getJdbcUrl(),
                    "spring.datasource.username=" + mySQLContainer.getUsername(),
                    "spring.datasource.password=" + mySQLContainer.getPassword()
            ).applyTo(configurableApplicationContext.getEnvironment());
        }
    }

@ClassRule
public static MySQLContainer mySQLContainer = new MySQLContainer<>("mysql:5.7.31")
            .withUsername("root") // So now you can do a GRANT too for example
            .withPassword("") // Only possible for user 'root'
            .withEnv("MYSQL_ROOT_HOST", "%")
            .withDatabaseName("integration_test_db") // So this name will now be used, not 'test'
            .withInitScript("mysql/init_mysql_integration_tests.sql")
            ;
The MySQL docker image used in the tests is retrieved from DockerHub https://hub.docker.com/_/mysql