Fix Flyway Migration Failed

What's Actually Happening

Flyway database migration fails during execution. Schema changes are not applied successfully.

The Error You'll See

```bash $ flyway migrate

ERROR: Validation failed for migration: checksum mismatch for migration 1.2 -> Applied to database : 123456789 -> Resolved locally : 987654321 ```

SQL error:

ERROR: Migration V1_3__create_table.sql failed
SQL State  : 42P01
Error Code : 0
Message    : relation "users" already exists

Version conflict:

ERROR: Found versioned migration 1.5, but only 1.4 is allowed

Connection error:

ERROR: Unable to obtain connection from database

Why This Happens

1. 1.Checksum mismatch - Migration file modified after execution
2. 2.SQL syntax error - Invalid SQL in migration script
3. 3.Duplicate migration - Version already applied
4. 4.Missing dependency - Required object doesn't exist
5. 5.Connection issue - Cannot connect to database
6. 6.Permission denied - User lacks necessary permissions

Step 1: Check Migration Status

```bash # View migration history: flyway info

# Show applied migrations: flyway info -url=jdbc:postgresql://localhost:5432/mydb -user=user -password=pass

# Check specific migration: flyway info -cleanDisabled=false

# View in database: psql -c "SELECT version, description, checksum, installed_on FROM flyway_schema_history ORDER BY installed_rank;"

# Check pending migrations: flyway info | grep Pending

# View detailed info: flyway info -outputType=json | jq .

# Check baseline: flyway baselineInfo ```

Step 2: Fix Checksum Mismatch

```bash # Checksum mismatch means file was modified after execution

# Option 1: Revert file to original version git checkout V1_2__create_users_table.sql

# Option 2: Clear checksum and re-validate: flyway clean flyway migrate # WARNING: clean deletes all data!

# Option 3: Repair checksums: flyway repair

# Option 4: Add new migration instead of modifying: # Don't change V1_2__create_table.sql # Create V1_3__alter_table.sql instead

# Option 5: Manual checksum update: psql -c "UPDATE flyway_schema_history SET checksum = 987654321 WHERE version = '1.2';"

# Option 6: Ignore checksum validation: flyway migrate -ignoreIgnoredMigrations=true -ignorePendingMigrations=true ```

Step 3: Fix SQL Errors

```bash # View the failing migration: cat db/migration/V1_3__create_table.sql

# Test SQL manually: psql -f db/migration/V1_3__create_table.sql

# Common SQL errors:

# 1. Object already exists: # Error: relation "users" already exists # Fix: Add IF NOT EXISTS CREATE TABLE IF NOT EXISTS users (...);

# 2. Syntax error: # Error: syntax error at or near "..." # Fix: Review SQL syntax, check quotes, semicolons

# 3. Foreign key constraint: # Error: relation "other_table" does not exist # Fix: Create referenced table first or defer constraint

# 4. Data type mismatch: # Error: column "col" cannot be cast to type integer # Fix: Add USING clause: ALTER TABLE t ALTER COLUMN col TYPE integer USING col::integer;

# 5. Not null violation: # Error: column "col" contains null values # Fix: Update data first or add DEFAULT

# Validate SQL before running: flyway validate ```

Step 4: Handle Version Conflicts

```bash # Check version numbers: flyway info

# Migration version rules: # - Must be unique # - Must be sequential # - Cannot have gaps in versioned migrations

# If version conflict: # Check applied versions: psql -c "SELECT version FROM flyway_schema_history ORDER BY version;"

# Common issues:

# 1. Duplicate version numbers: # V1_2__file_a.sql # V1_2__file_b.sql # ERROR: duplicate version

# Fix: Rename to V1_3

# 2. Version out of order: # Applied: V1_3 # Pending: V1_2 # ERROR: version 1.2 less than 1.3

# Fix: Renumber or delete

# 3. Missing version: # Applied: V1_1, V1_3 # Missing 1.2

# Fix: Create missing migration or use outOfOrder

# Allow out of order: flyway migrate -outOfOrder=true ```

Step 5: Repair Failed Migrations

```bash # If migration failed during execution:

# Check failed migration: flyway info # Shows: "Failed" status

# Option 1: Fix the SQL and retry: flyway migrate

# Option 2: Repair and retry: flyway repair flyway migrate

# Option 3: Manual cleanup: # Delete failed migration record: psql -c "DELETE FROM flyway_schema_history WHERE success = false;"

# Then fix and rerun: flyway migrate

# Option 4: Rollback manually: psql -c "DROP TABLE IF EXISTS partially_created_table;" flyway repair flyway migrate

# Option 5: Baseline at current state: flyway baseline -baselineVersion=1.5 flyway migrate ```

Step 6: Fix Connection Issues

```bash # Check database connectivity:

# Test connection: psql -h localhost -U user -d mydb -c "SELECT 1"

# Check JDBC URL format: flyway -url=jdbc:postgresql://localhost:5432/mydb \ -user=user \ -password=pass \ info

# Common connection issues:

# 1. Wrong URL format: # jdbc:postgresql://host:port/database

# 2. Authentication failed: # Check username and password

# 3. Database not found: # CREATE DATABASE mydb;

# 4. Host unreachable: ping postgres-server telnet postgres-server 5432

# 5. SSL required: flyway -url="jdbc:postgresql://host/db?ssl=true" ...

# 6. Connection timeout: flyway -connectRetries=10 ...

# Configure in flyway.conf: flyway.url=jdbc:postgresql://localhost:5432/mydb flyway.user=myuser flyway.password=mypassword flyway.schemas=public ```

Step 7: Handle Permission Issues

```bash # Check user permissions: psql -c "\du myuser"

# Required permissions for Flyway: # - CREATE on schema # - SELECT, INSERT, UPDATE, DELETE on flyway_schema_history # - Permissions for migration operations

# Grant permissions: GRANT ALL ON SCHEMA public TO myuser; GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO myuser;

# For flyway_schema_history: GRANT SELECT, INSERT, UPDATE, DELETE ON flyway_schema_history TO myuser;

# If table doesn't exist, Flyway creates it: # Needs CREATE TABLE permission

# For specific migrations: GRANT CREATE ON SCHEMA public TO myuser; GRANT USAGE, CREATE ON SCHEMA public TO myuser;

# PostgreSQL default privileges: ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO myuser; ```

Step 8: Configure Proper Environment

```bash # Flyway configuration options:

# In flyway.conf: flyway.driver=org.postgresql.Driver flyway.url=jdbc:postgresql://localhost:5432/mydb flyway.user=user flyway.password=password flyway.locations=classpath:db/migration,filesystem:/opt/migrations flyway.table=flyway_schema_history flyway.baselineVersion=0 flyway.baselineOnMigrate=true flyway.outOfOrder=false flyway.validateOnMigrate=true flyway.cleanDisabled=true

# Environment variables: export FLYWAY_URL=jdbc:postgresql://localhost:5432/mydb export FLYWAY_USER=user export FLYWAY_PASSWORD=password

# Command line override: flyway migrate -url=jdbc:postgresql://localhost:5432/mydb

# Multiple locations: flyway migrate -locations=classpath:db/migration,filesystem:/opt/sql

# Placeholder replacement: flyway migrate -placeholders.app_name=MyApp -placeholderReplacement=true ```

Step 9: Work with Team Conflicts

```bash # When multiple developers work on migrations:

# 1. Always pull before creating new migration: git pull

# 2. Use sequential versioning: # V1_0_1__add_column.sql (patch) # V1_1_0__new_table.sql (minor) # V2_0_0__major_change.sql (major)

# 3. Timestamp-based versioning: # V2024_01_15_120000__migration.sql

# 4. Handle merge conflicts: # If two developers create same version: # Developer A: V1_5__feature_a.sql # Developer B: V1_5__feature_b.sql

# Solution: Renumber one # Developer B: V1_6__feature_b.sql

# 5. Use repeatable migrations for views/procedures: # R__create_view.sql (no version, runs if checksum changes)

# 6. Review before merging: flyway validate flyway info ```

Step 10: Flyway Verification Script

```bash # Create verification script: cat << 'EOF' > /usr/local/bin/check-flyway.sh #!/bin/bash

DB_URL=${1:-"jdbc:postgresql://localhost:5432/mydb"} DB_USER=${2:-"postgres"} DB_PASS=${3:-"password"}

echo "=== Flyway Info ===" flyway info -url=$DB_URL -user=$DB_USER -password=$DB_PASS

echo "" echo "=== Migration History ===" psql $DB_URL -U $DB_USER -c "SELECT version, description, type, checksum, installed_on, success FROM flyway_schema_history ORDER BY installed_rank;" 2>/dev/null || echo "Cannot query history"

echo "" echo "=== Pending Migrations ===" flyway info -url=$DB_URL -user=$DB_USER -password=$DB_PASS | grep -A100 "Pending"

echo "" echo "=== Failed Migrations ===" flyway info -url=$DB_URL -user=$DB_USER -password=$DB_PASS | grep -A100 "Failed"

echo "" echo "=== Migration Files ===" ls -la db/migration/ 2>/dev/null || ls -la src/main/resources/db/migration/ 2>/dev/null || echo "No migrations directory"

echo "" echo "=== Database Connection ===" psql $DB_URL -U $DB_USER -c "SELECT version();" 2>/dev/null || echo "Cannot connect"

echo "" echo "=== Validate ===" flyway validate -url=$DB_URL -user=$DB_USER -password=$DB_PASS 2>&1

echo "" echo "=== Recommendations ===" echo "1. Check for checksum mismatches with flyway repair" echo "2. Verify SQL syntax in migration files" echo "3. Ensure version numbers are sequential" echo "4. Check database permissions" echo "5. Verify connection parameters" echo "6. Review failed migrations in history" echo "7. Use outOfOrder=true if needed" EOF

chmod +x /usr/local/bin/check-flyway.sh

# Usage: /usr/local/bin/check-flyway.sh "jdbc:postgresql://localhost:5432/mydb" user password ```

Flyway Migration Checklist

Verify the Fix

```bash # After fixing Flyway migration issues

# 1. Run migrate flyway migrate // Success

# 2. Check info flyway info // All migrations applied

# 3. Validate flyway validate // No errors

# 4. Query database psql -c "SELECT * FROM flyway_schema_history WHERE success = true;" // All migrations successful

# 5. Check schema psql -c "\dt" // Tables created

# 6. Run application // Application starts with correct schema ```

Related Issues

• [Fix Liquibase Migration Failed](/articles/fix-liquibase-migration-failed)
• [Fix PostgreSQL Permission Denied](/articles/fix-postgresql-permission-denied)
• [Fix MySQL Table Already Exists](/articles/fix-mysql-foreign-key-constraint)