Flyway "Migration ... failed" SQL statement error in CI

Flyway executed a migration and the database rejected one of its statements. Flyway reports the failing migration, the SQL State, and the database error, and (without transactional DDL) records the migration as failed in the history table.

What this error means

flyway migrate fails with "Migration V3__add_index.sql failed" followed by a SQL State and message such as a syntax error or a constraint violation.

flyway output

ERROR: Migration V3__add_index.sql failed
SQL State  : 42601
Error Code : 0
Message    : ERROR: syntax error at or near "INDExX"
Location   : sql/V3__add_index.sql

Common causes

Invalid or environment-specific SQL

A syntax error, a missing referenced object, or SQL that only works on another database engine causes the statement to fail.

A failed migration left a partial state

On databases without transactional DDL, a mid-migration failure leaves the history marked failed and blocks further migration until repaired.

How to fix it

Fix the failing SQL

Read the SQL State and message to find the exact failing statement.
Correct the SQL in the migration file.
For a non-transactional engine, run flyway repair to clear the failed entry, then migrate again.

Terminal

flyway repair
flyway migrate

Test migrations on a production-like database

Apply migrations to a clone before deploy so a failing statement is caught in CI rather than in production.

How to prevent it

Lint and test migration SQL against the target engine.
Run migrations on a clean clone before deploying.
Use flyway repair to recover a failed entry, then re-migrate.