How to reset auto-increment in Testcontainers MySQL integration tests

Question

I use Testcontainers to run integration tests in a MySQL container. I have an entity Listing which has a OneToMany mapping to a child entity, Variations. I have a method in my service layer which updates the variations for an associated listing (note it removes all existing variations beforehand):

public ListingDTO updateListingVariations(String listingId, List<ListingVariationDTO> variations) {
    listingVariationRepository.deleteAllByListingId(listingId);

    return listingRepository.findByListingId(listingId)
            .map(listing -> {
                List<ListingVariation> newVariations = variations.stream()
                        .map(this::toListingVariation)
                        .toList();
                listing.setVariations(newVariations);
                return this.toListingDTO(listingRepository.save(listing));
            })
            .orElseThrow(() -> new ListingNotFoundException(listingId));
}

Now I perform a test on this method (assume the database already contains a Listing with some associated ListingVariation's):

@Test
void shouldUpdateListingVariationsIfListingIdExists() {
    // builders for ListingVariation objects

    assertThat(listingService.findByListingId(validListingId).getVariations())
            .isEqualTo(listingVariations);
    assertThat(listingService.updateListingVariations(validListingId, newVariations).getVariations())
            .isEqualTo(newVariations);
}

However, the test is failing with the below error:

expected: [ListingVariationDTO{id=1, listingId='listingId', ...}]
 but was: [ListingVariationDTO{id=4, listingId='listingId', ...}]

I realise this is due to the auto-increment on the table not resetting when the previous variation records are deleted. I could modify my test to build a new ListingVariation object and hardcode the ID as 4, but I would like to avoid doing that for the simplicity of my test code. Can I reset the auto-increment on my table through my MySQL container?

Answer 1

You can do this by executing a SQL statement.

ALTER TABLE tablename AUTO_INCREMENT = 1

Which you probably would do using a JdbcTemplate.

But I'd argue you shouldn't check the value of the id at all in your test. Just check that the id is not null. The reason this is the correct approach is that (hopefully) nobody cares about the id. It is just an id and it doesn't matter if it is 1, 2, 4, or 314159265, so your test shouldn't care either.