Every now and then I run into database migration error using Rails, so I write this blogpost to dissect this problem.
When I work on my development branch, and I have some migrations to run, it works fine if I run
rake db:migrate. However, when I rebase a remote branch with outstanding migrations, if I run
rake db:migrate, I may get the following error:
SQLException: duplicate column name: some_column_name
We work in a mulit-developer environment, and new database migrations are being developed separately. We are also committing to
db/schema.rb into our git, which makes it possible to have conflicting timestamp on the schema entry.
Specifically, the problem arises if my local migrations are not in sync with the database schema. This happens when a remote branch is rebased into a local branch and a rebase conflict needs to be resolved by manually modifying
rake db:migrate is run, Rails rely on
schema_migrations tables to recognize which migrations have been run.
Follow these 3 steps to troubleshoot the problem
schema_migrationson remote and local database
rails db_console on local and remote database, and run
SELECT * FROM schema_migrations;
Compare the entries side by side to detect the difference.
The source of truth for schema timestamp should be the master branch of the application cdode:
schema_migrationsentry on remote
Add the missing entry to the remote table, but ** don’t remove any entry on remote . Follow a **only add, never remove strategy. In the example above, add 20161001032849 entry to the remote.
INSERT INTO schema_migrations VALUES ('20161001032849');
rake db:migrate should work fine on the remote server!
Two things we can do better to prevent the schema error:
schema.rbfile, pay attention to the timestamp and identify which timestamp number to use.
rake db:migratefails locally, don’t run rake db:reset, instead looking into
schema.rbfile and correct the timestamp version within