Blog Company GitLab got bit: MySQL fails at Rails migrations that work in PostgreSQL
Published on: December 30, 2014
2 min read

GitLab got bit: MySQL fails at Rails migrations that work in PostgreSQL

One day after releasing GitLab 7.6 we had to release a patch. This is how we got bit by a failing migration and why it was our own fault.

Blog fallback hero

One day after releasing GitLab 7.6 we had to release a patch. This is how we got bit by a failing migration and why it was our own fault.

What happened

GitLab supports two databases: MySQL and PostgreSQL.

To update our database models for identities in GitLab, we wanted to remove two columns from the users table: extern_uid and provider. These had a composite index, so that uniqueness was checked for any combination of extern_uid + provider.

add_index :users, [:extern_uid, :provider]

So to remove the columns, we wrote a simple migration:

def up
  remove_column :users, :extern_uid
  remove_column :users, :provider
end

Run the migration on PostgreSQL and it works without a hitch. Try to run it on MySQL and:

Mysql2::Error: Duplicate entry 'example' for key 'index_users_on_extern_uid_and_provider': ALTER TABLE `users` DROP `extern_uid`

It seems that when removing the extern_uid column, MySQL decided to rebuild the index using only the provider column, creating duplicate indices (which is not allowed).

How we fixed it

To fix this, all we had to do is check whether the index exists and remove it if it does.

def up
  if index_exists?(:users, [:extern_uid, :provider])
    remove_index :users, [:extern_uid, :provider]
  end

  remove_column :users, :extern_uid
  remove_column :users, :provider
end

We quickly released a new GitLab version that included the new migration.

Why we'll probably not repeat this mistake

So how did we miss such a simple mistake? GitLab has a pretty hefty test-suite that tests almost every line of code. On top of that, we do [QA testing](http://doc.gitlab.com/ee/release/monthly.html#workdays-before-release

We want to hear from you

Enjoyed reading this blog post or have questions or feedback? Share your thoughts by creating a new topic in the GitLab community forum. Share your feedback

Ready to get started?

See what your team could do with a unified DevSecOps Platform.

Get free trial

Find out which plan works best for your team

Learn about pricing

Learn about what GitLab can do for your team

Talk to an expert