Rails 6 added an option to the method which will not create the table if it already exists.  This option is handy when we have added a table directly in production but now want to add it through a migration so that it is available in other environments as well. if_not_exists create_table Check out how the option works for creating tables . if_not_exists here Let's look at a migration to understand this feature. create_table , t.integer t.string , , t.datetime t.integer t.timestamps < ActiveRecord::Migration[6.0] class CreateIssues def change :issues if_not_exists: true do |t| :comment_count :url :repo_name :user_name :last_touched_at :number end end end This migration will not create the table if it exists already, as we have passed to the create_table method. Let's change the migration a little bit. We will now add a reference to table in issues table because we want to create a reference to the table as well. issues if_not_exists: true users users create_table , t.integer t.string , , t.datetime t.integer t.references t.timestamps < ActiveRecord::Migration[6.0] class AddIssuesAgainAgainAgain def change :issues if_not_exists: true do |t| :comment_count :url :repo_name :user_name :last_touched_at :number :user end end end If this migration is run twice, it results into following error. ➜  codetriage git:(master) ✗ be rake db:migrate
== 20200401140530 AddIssuesAgainAgainAgain: migrating =========================
-- create_table(:issues, {:if_not_exists=> })
rake aborted!
StandardError: An error has occurred, this and all later migrations canceled:

Index name on table already exists
/Users/prathamesh/Projects/sources/codetriage/db/migrate/20200401140530_add_issues_again_again_again.rb:3: `change /Users/prathamesh/.rbenv/versions/2.6.5/bin/bundle:23: `<main> index_issues_on_user_id issues /Users/prathamesh/.rbenv/versions/2.6.5/bin/bundle:23: `load Tasks: TOP => db:migrate
(See full trace by running task with --trace) true 'index_issues_on_user_id' 'issues' in '
/Users/prathamesh/.rbenv/versions/2.6.5/bin/bundle:23:in `load' in '

Caused by:
ArgumentError: Index name ' ' on table ' ' already exists
/Users/prathamesh/Projects/sources/codetriage/db/migrate/20200401140530_add_issues_again_again_again.rb:3:in `change' in '
/Users/prathamesh/.rbenv/versions/2.6.5/bin/bundle:23:in `<main>' We can see that it did not cause an error related to creating the table. The error was raised for existing index. But we do know that the index may exist. That's why we added the if_not_exists: true flag. Then why is Rails trying to create the index when we clearly told it check if the index exists or not. Well, turns out the flag was not getting passed to the query that Rails was generating for adding index. To understand this properly, let's take a look at queries generated by above migration. if_not_exists CREATE TABLE IF NOT EXISTS `issues` CREATE INDEX `index_issues_on_user_id` on `issues` The query that was generated by the above migration was not having the clause so Rails was trying to create the index again even if the was passed. INDEX IF NOT EXISTS if_not_exists: true Let's look at the code of method in Rails to understand why this is happening. We will only see the relevant code related to adding indexes as the method is very big. create_table td = create_table_definition(table_name, **options.extract!( , , , , ))
  ...
  
  result = execute schema_creation.accept td
  ... supports_indexes_in_create?
    td.indexes.each add_index(table_name, column_name, index_options) ...
  
  result def create_table :temporary :if_not_exists :options :as :comment unless do |column_name, index_options| end end end This code if translated to plain English looks like this. Generate SQL CREATE TABLE
  result = Execute SQL CREATE TABLE
  
  For each of the indexes -
   - Generate SQL ADD INDEX
   - Execute SQL ADD INDEX
  End result def create_table for for for for return end Looking at the error that we got related to existing index, it is pretty evident that option was not used while generating the SQL for index whereas it was used while generating the SQL for creating table. if_not_exists Now the solution is straightforward. We need to make sure that Rails uses the option while generating the SQL for index. if_not_exists The generation of SQL for indexes and its execution is handled by the method. It looks like this. add_index index_name, index_type, index_columns, index_options = add_index_options(table_name, column_name, **options)
  execute def add_index (table_name, column_name, options = {}) "CREATE INDEX ON ( ) " #{index_type} #{quote_column_name(index_name)} #{quote_table_name(table_name)} #{index_columns} #{index_options} end So if we pass the option from to which in turn passes it to and which generates the proper SQL that we want, then our problem is solved! if_not_exists create_table add_index add_index_options That's what I did in this pull request and this issue is now fixed on Rails master. https://github.com/rails/rails/pull/38555 How to handle this issue with Rails 6.0.2 and below Support for was added in Rails 6.0.0. This change for supporting it for indexes is merged in Rails master but it is not yet released. If you are running into similar issue while using Rails 6.0.2 or below, you can simply skip the migration based on an environment check. if_not_exists Rails.env.production?
    
    create_table , t.integer t.string , , t.datetime t.integer t.references t.timestamps < ActiveRecord::Migration[6.0] class AddIssuesAgainAgainAgain def change return if :issues if_not_exists: true do |t| :comment_count :url :repo_name :user_name :last_touched_at :number :user end end end In this way, if you are creating any indexes in the migration, you will not see errors about their presence if the migration is already run. One last thing While working on this fix, I realized that MySQL does not support clause for indexes. Bummer! To support this feature for MySQL adapter, we have to actually check if the index exists or not before trying to add it. IF NOT EXISTS options[ ] && index_exists?(table_name, column_name, options)
  ..
  execute .. # activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb def add_index (table_name, column_name, options = {}) #:nodoc: return if :if_not_exists end One more thing! The support for is also available to method now. So you can just pass it as follows when trying to create standalone indexes. if_not_exists add_index add_index , , :issues :user_id if_not_exists: true If you are interested in more such articles about Ruby on Rails, then subscribe to my newsletter.

Trace

Too Long; Didn't Read

Making security fun one episode at a time

Do Not Add Database Index If It Already Exists in Rails

Too Long; Didn't Read

Company Mentioned

Prathamesh

About Author

TOPICS

THIS ARTICLE WAS FEATURED IN...

Do Not Add Database Index If It Already Exists in Rails

Too Long; Didn't Read

Company Mentioned

Prathamesh

About Author

TOPICS

THIS ARTICLE WAS FEATURED IN...

RELATED STORIES