Transactional test wont rollback after completion

Question

I'm using Spring Boot 2 with JUnit4 and MySQL 5.7.

After executing a test where JDBCTemplate inserts a record in a database, this newly inserted record remains in the customers table. I tried different variations (for example, moving @Transactional and @Rollback to a method) but it was all the same.

Code for the test class:

import static org.junit.Assert.*;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.annotation.Rollback;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringRunner;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.Transactional;


@RunWith(SpringRunner.class)
@ContextConfiguration(classes = {TestConfig.class})
@Transactional
@Rollback
public class TestJDBCTemplate
    {

    @Autowired
    PlatformTransactionManager transactionManager;

    @Autowired
    JdbcTemplate jdbcTemplate;

    @Autowired
    ApplicationContext applicationContext;


    @Test
    public void testInsert()
        {
        jdbcTemplate.execute("INSERT INTO customers (first_name, last_name) VALUES (\"Jeff\", \"Johnson\")");

        // This condition is irrelevant
        assertEquals(1, 1);
        }

    }

Code for the ContextCOnfiguration: package com.example.demo.test;

import javax.sql.DataSource;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.transaction.PlatformTransactionManager;

@Configuration
public class TestConfig
    {

    @Bean
    DataSource dataSource()
        {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();

        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://localhost:3306/thdb");
        dataSource.setUsername("root");
        dataSource.setPassword("root");

        return dataSource;
        }

    @Bean
    PlatformTransactionManager transactionManager()
        {
        return new DataSourceTransactionManager(dataSource());
        }

    @Bean
    JdbcTemplate jdbcTemplate(DataSource dataSource)
        {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

        return jdbcTemplate;
        }
    }

Customers table was created using this line of code:

jdbcTemplate.execute("CREATE TABLE customers(" + "id SERIAL, first_name VARCHAR(255), last_name VARCHAR(255))");

Could you please tell me what is preventing the rollback after test completion?

Answer 1

Generally when using MySQL and the fact that @Rollback (or rolling back a transaction in general) isn't working this is because the usage of the wrong table type in MySQL.

Newer versions use the InnoDB storage engine by default however older versions (or using the wrong dialect for your JPA provider) will use the MyISAM storage engine.

The InnoDB engine has support for transactions whereas the MyISAM type doesn't. Hence doing a rollback on a MyISAM based table won't do anything.

To fix either set the default storage engine in MySQL to InnoDB or when creating the table specify the storage engine to use.

CREATE TABLE customers(
  id SERIAL, 
  first_name VARCHAR(255), 
  last_name VARCHAR(255))
ENGINE = InnoDB;