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

Job van der Voort ·
Dec 30, 2014 · 1 min read

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

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]

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

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 on every release.

Turns out, we tested the migrations on an empty database: there were simply no duplicate indices to run into.

For future releases, we make sure that upgrades are tested on a pre-seeded database, and recommend that migrations do only one thing.

Simple mistake, simple fix. The trick is in trying to not repeat it.

Edit this page View source