How to resolve Dirty status of database migration in Golang

The following is a summary of how to deal with the Dirty state of migration in server development using Golang and golang-migrate. The following is a memorandum of the solution procedures.

Common causes of Dirty conditions

Dirty conditions often occur in the following cases

If other developers are adding new migrations:

• Other developers have added new migrations to the develop branch and are working on applying them.
• When I return to my branch, the state schema_migrations does not match the local migration file and I get a Dirty status.

If the migration is interrupted in the middle:

• Dirty status is a result of migration not being completed successfully due to error or manual interruption.

Migration file inconsistencies:.

• If the team is out of sync and lacks the necessary migration files.

Table schema_migrations to resolve Dirty status

To resolve the Dirty condition, directly modify the schema_migrations table where the migration information is stored.

(computer) command

Reset the Dirty status with the following SQL.

-- Dirty状態をリセット
UPDATE schema_migrations
SET dirty = FALSE
WHERE version = '';

Execution example

For example, in the Dirty state with migration number 20241102:.

UPDATE schema_migrations
SET dirty = FALSE
WHERE version = '20241102';

Example of terminal log

mysql> UPDATE schema_migrations
    -> SET dirty = FALSE
    -> WHERE version = '20241102';
Query OK, 1 row affected (0.01 sec)

Manual deletion with GUI tool

If you are using a GUI tool such as MySQL Workbench:

1. SELECT * FROM schema_migrations; to identify rows in the Dirty state (dirty = 1 ).
2. Delete rows in Dirty status.

   DELETE FROM schema_migrations WHERE version = '';

3. Confirmation that the condition has returned to normal.

How to use FORCE and what to look out for

The force command of golang-migrate can be used to record the specified version as applied in the database.

However, since FORCE does not execute the actual migration, it is considered to have reached the specified version in an unapplied state. If necessary, execute UP to and complete the migration.

FORCE andUP flow

1. Set the latest migration number in force:.
   Set the specified version as applied after the Dirty status is resolved.

   migrate -path  \
          -database "mysql://user:password@tcp(127.0.0.1:3306)/" \
          force 

Example of terminal log

2024/11/15 12:00:00 INFO  Applying force command
2024/11/15 12:00:00 INFO  Forced database to version 20241102

2. Execute any unapplied migrations with up:.
   Apply remaining migrations after force is executed.

   migrate -path  \
          -database "mysql://user:password@tcp(127.0.0.1:3306)/" \
          up

Example of terminal log

2024/11/15 12:05:00 INFO  Applying migrations
2024/11/15 12:05:01 INFO  Migration 20241103 applied successfully
2024/11/15 12:05:01 INFO  Migration 20241104 applied successfully

Execution example

For example, if the latest migration number is 20241102:.

migrate -path mia/rdb/migrations \
       -database "mysql://user:password@tcp(127.0.0.1:3306)/clocky-db" \
       force 20241102

migrate -path mia/rdb/migrations \
       -database "mysql://user:password@tcp(127.0.0.1:3306)/clocky-db" \
       up

Check current version

Check the current database migration version.

(computer) command

migrate -path  \
       -database "mysql://user:password@tcp(127.0.0.1:3306)/" \
       version

Example of terminal log

2024/11/15 12:10:00 INFO  Current database version: 20241102

Restart and check with Docker Compose

After applying the migration, restart the application using Docker Compose to verify that it is working properly.

Restart command

docker-compose down && docker-compose up -d

Example of terminal log

Stopping clocky-db ... done
Stopping clocky-api ... done
Removing clocky-db ... done
Removing clocky-api ... done
Creating clocky-db ... done
Creating clocky-api ... done

Confirmation of startup status

Check the container status with the following command.

docker-compose ps

Example of terminal log

    Name                 Command               State           Ports         
----------------------------------------------------------------------------
clocky-api   /bin/sh -c ./start-api.sh     Up      0.0.0.0:8080->8080/tcp
clocky-db    docker-entrypoint.sh mysqld   Up      3306/tcp

• Verify that the container is Up.
• Check logs as needed.

  docker-compose logs -f

This completes the resolution of the dirty state and confirms the application startup.