Migrationsmedium5-8 years

A migration `V5__add_index_on_orders.sql` has already run in production. A follow-up PR notices the index is on the wrong column and fixes the column name inside `V5`. The next deploy fails before the application starts. What exactly did Flyway check, and what should the PR have done instead? When is `flyway repair` the right tool?

Flyway records every applied migration in flyway_schema_history, including a checksum of the file's contents. On every run it recomputes the checksum of each already-applied file on disk and compares it with the stored one, for every version in the history, not just the newest. Editing V5 changed its checksum, so validation failed with a checksum mismatch and nothing ran: in a Spring Boot app, the Flyway initialiser fails and the context never starts. That is deliberate: the database was built by the old contents of that file, and Flyway refuses to guess whether the difference matters. The PR should have added V6__fix_orders_index_column.sql that drops the wrong index and creates the right one. repair rewrites stored checksums to match the files, which is only right when the edited migration has run nowhere but your own laptop.

The lesson behind it →

Previous`Category.products` is a `@OneToMany(mappedBy = "category")`, and someone adds `orphanRemoval = true` "for consistency" with `Order.lines`, which has it. The next day, an admin un-categorises a product (removes it from the category's collection and clears `product.category`), and the product row is deleted from the database. No `remove()` was called anywhere. Explain the difference between `CascadeType.REMOVE` and `orphanRemoval`, and why only one of them fired here.Next `placeOrder()` is `@Transactional` (REQUIRED) and calls `audit.logAttempt()` on another bean, which is `@Transactional(propagation = REQUIRES_NEW)` so the audit row survives a failed order. Under load, with a pool of 10 connections, requests start hanging for 30 seconds and then failing with a connection-timeout error, while the database is nearly idle. Walk through what REQUIRES_NEW actually does and why this happens.