MySQL Testing with Testcontainers

Part 6 of 20

May 23, 2026 Abhay 6 min read

MySQL Testing with Testcontainers

MySQL is still the most widely deployed relational database in Java applications. Its SQL dialect, strict mode behavior, and character set handling differ from both H2 and PostgreSQL in ways that matter for real applications. This article covers MySQL testing with Testcontainers — from basic setup to MySQL-specific features and the STRICT_TRANS_TABLES mode that catches data truncation bugs H2 silently ignores.

What You’ll Learn

MySQLContainer setup and configuration
@ServiceConnection with MySQL
MySQL strict mode and why it matters for tests
Character set and collation configuration
Testing MySQL-specific SQL functions
MariaDB as an alternative

Dependencies

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-testcontainers</artifactId>
    <scope>test</scope>
</dependency>
<dependency>
    <groupId>org.testcontainers</groupId>
    <artifactId>junit-jupiter</artifactId>
    <scope>test</scope>
</dependency>
<dependency>
    <groupId>org.testcontainers</groupId>
    <artifactId>mysql</artifactId>
    <scope>test</scope>
</dependency>

Also add the MySQL JDBC driver as a main dependency (if not already present):

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <scope>runtime</scope>
</dependency>

Basic MySQLContainer Setup

@SpringBootTest
@Testcontainers
class OrderRepositoryMySQLTest {

    @Container
    @ServiceConnection
    static MySQLContainer<?> mysql =
        new MySQLContainer<>("mysql:8.0")
            .withDatabaseName("orders_test")
            .withUsername("orders")
            .withPassword("orders123");

    @Autowired
    private OrderRepository orderRepository;

    @BeforeEach
    void cleanup() {
        orderRepository.deleteAll();
    }

    @Test
    void shouldSaveAndFindOrder() {
        Order order = new Order("customer-1", OrderStatus.PENDING, BigDecimal.valueOf(99.99));
        Order saved = orderRepository.save(order);

        assertThat(saved.getId()).isNotNull();
        assertThat(orderRepository.findById(saved.getId())).isPresent();
    }
}

@ServiceConnection on MySQLContainer automatically configures:

spring.datasource.url
spring.datasource.username
spring.datasource.password
spring.datasource.driver-class-name

MySQL Strict Mode

By default, MySQL 8.0 runs with STRICT_TRANS_TABLES mode enabled. This causes MySQL to throw an error when you insert data that violates column definitions — for example, a string that is too long for a VARCHAR(50) column, or an integer that is out of range.

H2 in default mode silently truncates or coerces such data. This means tests that pass against H2 may fail against MySQL when data does not fit column constraints.

This is actually a feature, not a problem. MySQL strict mode catching data truncation is correct behavior. Tests that fail against MySQL strict mode are telling you about real bugs.

@Test
void shouldThrowWhenCustomerIdExceedsColumnLength() {
    // VARCHAR(50) column — 51 characters should fail
    String longCustomerId = "c".repeat(51);
    Order order = new Order(longCustomerId, OrderStatus.PENDING, BigDecimal.valueOf(50.00));

    // With MySQL strict mode: DataIntegrityViolationException
    // With H2 default mode: silently truncates to 50 chars
    assertThatThrownBy(() -> orderRepository.saveAndFlush(order))
        .isInstanceOf(DataIntegrityViolationException.class);
}

If you need to disable strict mode for compatibility testing:

static MySQLContainer<?> mysql = new MySQLContainer<>("mysql:8.0")
    .withCommand("--sql-mode=NO_ENGINE_SUBSTITUTION");

Character Set Configuration

MySQL’s default character set affects how string data is stored and compared. For applications that store international text, configure utf8mb4:

@Container
@ServiceConnection
static MySQLContainer<?> mysql = new MySQLContainer<>("mysql:8.0")
    .withDatabaseName("orders_test")
    .withUsername("orders")
    .withPassword("orders123")
    .withCommand(
        "--character-set-server=utf8mb4",
        "--collation-server=utf8mb4_unicode_ci"
    );

In application-test.yml, configure the JDBC URL to use utf8mb4:

# application-test.yml
spring:
  datasource:
    hikari:
      connection-init-sql: "SET NAMES utf8mb4"

Test that Unicode data roundtrips correctly:

@Test
void shouldStoreAndRetrieveUnicodeCustomerId() {
    // Japanese characters in customer ID
    String unicodeCustomerId = "顧客-001";
    Order order = new Order(unicodeCustomerId, OrderStatus.PENDING, BigDecimal.valueOf(50.00));
    orderRepository.save(order);

    List<Order> found = orderRepository.findByCustomerId(unicodeCustomerId);
    assertThat(found).hasSize(1);
    assertThat(found.get(0).getCustomerId()).isEqualTo(unicodeCustomerId);
}

MySQL-Specific SQL Functions

MySQL has functions not available in other databases. Testing with a real MySQL container catches these before production.

JSON Functions

@Entity
@Table(name = "order_metadata")
public class OrderMetadata {

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

    @Column(columnDefinition = "JSON")
    private String attributes;
}

public interface OrderMetadataRepository extends JpaRepository<OrderMetadata, Long> {

    @Query(value = "SELECT * FROM order_metadata WHERE JSON_EXTRACT(attributes, '$.priority') = :priority",
           nativeQuery = true)
    List<OrderMetadata> findByPriority(@Param("priority") String priority);
}

@Test
void shouldQueryByJsonAttribute() {
    // JSON_EXTRACT is MySQL-specific — fails against H2
    OrderMetadata highPriority = new OrderMetadata();
    highPriority.setAttributes("{\"priority\": \"high\", \"region\": \"us-east\"}");
    metadataRepository.save(highPriority);

    OrderMetadata lowPriority = new OrderMetadata();
    lowPriority.setAttributes("{\"priority\": \"low\", \"region\": \"eu-west\"}");
    metadataRepository.save(lowPriority);

    List<OrderMetadata> highPriorityItems = metadataRepository.findByPriority("high");
    assertThat(highPriorityItems).hasSize(1);
}

Full-Text Search

public interface ProductRepository extends JpaRepository<Product, Long> {

    @Query(value = "SELECT * FROM products WHERE MATCH(name, description) AGAINST (:searchTerm IN BOOLEAN MODE)",
           nativeQuery = true)
    List<Product> fullTextSearch(@Param("searchTerm") String searchTerm);
}

@Test
void shouldPerformFullTextSearch() {
    // MATCH...AGAINST is MySQL-specific
    productRepository.save(new Product("SKU-001", "Wireless Keyboard", "Bluetooth mechanical keyboard"));
    productRepository.save(new Product("SKU-002", "USB Mouse", "Ergonomic wired mouse"));

    // Flush to ensure full-text indexes are updated
    entityManager.flush();

    List<Product> results = productRepository.fullTextSearch("keyboard");
    assertThat(results).hasSize(1);
    assertThat(results.get(0).getName()).isEqualTo("Wireless Keyboard");
}

Upsert with ON DUPLICATE KEY UPDATE

MySQL’s ON DUPLICATE KEY UPDATE syntax is a common pattern not available in standard SQL:

public interface ProductRepository extends JpaRepository<Product, Long> {

    @Modifying
    @Query(value = """
        INSERT INTO products (sku, name, stock)
        VALUES (:sku, :name, :stock)
        ON DUPLICATE KEY UPDATE
            stock = stock + :stock,
            name = VALUES(name)
        """,
        nativeQuery = true)
    void upsertProduct(
        @Param("sku") String sku,
        @Param("name") String name,
        @Param("stock") int stock
    );
}

@Test
void shouldInsertOrUpdateProductStock() {
    productRepository.upsertProduct("SKU-001", "Widget", 100);
    assertThat(productRepository.findBySku("SKU-001").getStock()).isEqualTo(100);

    // Second call should increment stock, not replace it
    productRepository.upsertProduct("SKU-001", "Widget Updated", 50);
    assertThat(productRepository.findBySku("SKU-001").getStock()).isEqualTo(150);
    assertThat(productRepository.findBySku("SKU-001").getName()).isEqualTo("Widget Updated");
}

Testing with MariaDB

MariaDB is a MySQL fork with largely compatible syntax. Use MariaDBContainer for MariaDB-specific testing:

<dependency>
    <groupId>org.testcontainers</groupId>
    <artifactId>mariadb</artifactId>
    <scope>test</scope>
</dependency>

@Container
@ServiceConnection
static MariaDBContainer<?> mariadb =
    new MariaDBContainer<>("mariadb:11.2")
        .withDatabaseName("orders_test");

MariaDB has some features PostgreSQL and MySQL do not — notably SEQUENCE objects and WITH clause improvements. If you deploy on MariaDB, test against MariaDB.

Switching Between MySQL and PostgreSQL

If your codebase supports both databases (for example, MySQL for most deployments, PostgreSQL for a specific enterprise customer), parameterize your tests:

// Base class for database-agnostic tests
abstract class AbstractOrderRepositoryTest {

    @Autowired
    protected OrderRepository orderRepository;

    @BeforeEach
    void cleanup() {
        orderRepository.deleteAll();
    }

    @Test
    void shouldSaveAndRetrieveOrder() {
        Order order = new Order("customer-1", OrderStatus.PENDING, BigDecimal.valueOf(99.99));
        Order saved = orderRepository.save(order);
        assertThat(orderRepository.findById(saved.getId())).isPresent();
    }

    @Test
    void shouldFindByStatus() {
        orderRepository.save(new Order("c1", OrderStatus.PENDING, BigDecimal.valueOf(50.00)));
        orderRepository.save(new Order("c2", OrderStatus.CONFIRMED, BigDecimal.valueOf(75.00)));

        assertThat(orderRepository.findByStatus(OrderStatus.PENDING)).hasSize(1);
    }
}

@SpringBootTest
@Testcontainers
class OrderRepositoryPostgresTest extends AbstractOrderRepositoryTest {

    @Container
    @ServiceConnection
    static PostgreSQLContainer<?> postgres =
        new PostgreSQLContainer<>("postgres:16-alpine");
}

@SpringBootTest
@Testcontainers
class OrderRepositoryMySQLTest extends AbstractOrderRepositoryTest {

    @Container
    @ServiceConnection
    static MySQLContainer<?> mysql =
        new MySQLContainer<>("mysql:8.0");
}

The same test logic runs against both databases. Differences in behavior between the two databases surface as test failures.

Common Pitfalls

Using mysql:latest. Pin the version: mysql:8.0 or mysql:8.4. MySQL’s behavior changes between major versions.

MySQL slow startup. MySQL is slower to start than PostgreSQL. The MySQLContainer default wait strategy (Wait.forLogMessage(".*ready for connections.*\\n", 2)) waits for two occurrences of the readiness message, which accounts for MySQL’s two-phase startup. Do not lower the timeout.

Case-sensitive table names on Linux. MySQL on Linux is case-sensitive for table names by default. On macOS, it is case-insensitive. If your migrations create table names in lowercase but your JPA entities map to mixed-case names, tests may fail in CI (Linux) but pass locally (macOS). Use consistent lowercase table names in both migrations and @Table annotations.

Summary

MySQLContainer gives you a real MySQL instance for integration tests. MySQL strict mode, character set handling, JSON functions, full-text search, and ON DUPLICATE KEY UPDATE are all MySQL-specific features that H2 either does not support or handles differently. Testing against real MySQL surfaces these issues before production.

The next article covers MongoDB and document database testing with MongoDBContainer.

Next: MongoDB and NoSQL Testing