Failure to run second script as intended in @SQLGroup with multiple data sources and transaction managers on a JUnit test class [SPR-16153]

[spring-projects-issues]

Eduardo Beja Martins opened SPR-16153 and commented

On a JUnit Test configuration, with two data sources of different transaction managers, applied to the test class, the second is rerunned for each test.

Example:

@RunWith(SpringJUnit4ClassRunner.class)
@SqlGroup({
    @Sql(scripts = "script1.sql", config = @SqlConfig(dataSource = "dataSource1", transactionManager = "txMgr1")),
    @Sql(scripts = "script2.sql", config = @SqlConfig(dataSource = "dataSource2", transactionManager = "txMgr2"))
})
public class ExampleTest{
    @Test
    public test1() {
        // Some or no code here
    }

    @Test
    public test2() {
        // Some or no code here
    }
}

Between test1 and test2, errors will occur because script2.sql has insert statements, that will try to run for each test, and for the second time, registers with the same primary key will aready exist.

If the order of the '@Sql' statements is inverted, same problem will occur but for script1.sql.

The need for the persistence comes from the heavy load of JUnit tests for code pieces that only read and do not write to database..

---

No further details from SPR-16153

[spring-projects-issues]

Sam Brannen commented

On a JUnit Test configuration, with two data sources of different transaction managers, applied to the test class, the second is rerunned for each test.

All scripts are supposed to be executed for each test method in such a configuration.

That's the expected behavior.

[spring-projects-issues]

Sam Brannen commented

Did I perhaps misunderstand you?

[spring-projects-issues]

Eduardo Beja Martins commented

Maybe it is my misunderstanding, but to execute the scripts for each test individually you'd either use something like

@DirtiesContext(classMode = ClassMode.BEFORE_EACH_TEST_METHOD)

or

@RunWith(SpringJUnit4ClassRunner.class)
public class ExampleTest{

    @Test
    @SqlGroup({
        @Sql(scripts = "script1.sql", config = @SqlConfig(dataSource = "dataSource1", transactionManager = "txMgr1")),
        @Sql(scripts = "script2.sql", config = @SqlConfig(dataSource = "dataSource2", transactionManager = "txMgr2"))
    })
    public test1() {
        // Some or no code here
    }

    @Test
    @SqlGroup({
        @Sql(scripts = "script1.sql", config = @SqlConfig(dataSource = "dataSource1", transactionManager = "txMgr1")),
        @Sql(scripts = "script2.sql", config = @SqlConfig(dataSource = "dataSource2", transactionManager = "txMgr2"))
    })
    public test2() {
        // Some or no code here
    }
}

In the case in the description, is my understanding the the scripts should be loaded once and applied to all tests in the ExampleTest class.

Even if I am wrong, howhever, as is my example, what happens to me is that between tests, the database created is not destroyed and it tries to execute the inserts of the second script again over the existing database. Since there are already registers with the same primary key, because they where inserted in the first test, an exception is thrown and the test fails, so as any after that.

[sbrannen]

This is the expected behavior. @Sql scripts and statements configured at the class level will be executed for each test method in the test class -- unless a given test method is itself annotated with @Sql in which case the method-level configuration overrides the class-level configuration.

Issue gh-18929 will introduce support for class-level only configuration.

[sbrannen]

If you configure SQL scripts or statements that insert data into the database with predefined primary keys -- and if those SQL scripts or statements are executed outside of a test-managed transaction (i.e., if the changes are committed to the database), you will then have to clean up the changes after each test method. Otherwise you run into primary key constraint violations in the database as you have pointed out.

Luckily, @Sql provides support for executing "clean-up" scripts or statements after each test method.

I documented the available options in detail in this StackOverflow question.

Here's an excerpt:

@Test
@SqlGroup({
    @Sql("create-tables.sql"),
    @Sql(scripts = "clean-up.sql", executionPhase = AFTER_TEST_METHOD)
})
public void test() { /* ... */ }

If your tests are @Transactional and you'd like to clean up committed database state, you can instruct Spring to execute your clean-up SQL script in a new transaction like this:

@Test
@Sql("insert-test-data.sql")
@Sql(
  scripts = "clean-up.sql",
  executionPhase = AFTER_TEST_METHOD,
  config = @SqlConfig(transactionMode = ISOLATED)
)
public void test() { /* ... */ }

[sbrannen]

In light of the above, I am closing this issue.