Don't add database index if it already exists in Rails

栏目: IT技术 · 发布时间: 4年前

内容简介:Rails 6 added an optionCheck out how theLet's look at a migration to understand this feature.

Rails 6 added an option if_not_exists to the create_table 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.

Check out how the if_not_exists option works for creating tables here .

Let's look at a migration to understand this feature.

class CreateIssues < ActiveRecord::Migration[6.0]
  def change
    create_table :issues, if_not_exists: true do |t|
      t.integer   :comment_count
      t.string    :url, :repo_name, :user_name
      t.datetime  :last_touched_at
      t.integer   :number

      t.timestamps
    end
  end
end

This migration will not create the issues table if it exists already, as we have passed if_not_exists: true to the create_table method. Let's change the migration a little bit. We will now add a reference to users table in issues table because we want to create a reference to the users table as well.

class AddIssuesAgainAgainAgain < ActiveRecord::Migration[6.0]
  def change
    create_table :issues, if_not_exists: true do |t|
      t.integer   :comment_count
      t.string    :url, :repo_name, :user_name
      t.datetime  :last_touched_at
      t.integer   :number

      t.references :user
      t.timestamps
    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=>true})
rake aborted!
StandardError: An error has occurred, this and all later migrations canceled:

Index name 'index_issues_on_user_id' on table 'issues' already exists
/Users/prathamesh/Projects/sources/codetriage/db/migrate/20200401140530_add_issues_again_again_again.rb:3:in `change'
/Users/prathamesh/.rbenv/versions/2.6.5/bin/bundle:23:in `load'
/Users/prathamesh/.rbenv/versions/2.6.5/bin/bundle:23:in `<main>'

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

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 if_not_exists 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.

CREATE TABLE IF NOT EXISTS `issues`
CREATE INDEX `index_issues_on_user_id` on `issues`

The INDEX query that was generated by the above migration was not having the IF NOT EXISTS clause so Rails was trying to create the index again even if the if_not_exists: true was passed.

Let's look at the code of create_table 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.

def create_table
  td = create_table_definition(table_name, **options.extract!(:temporary, :if_not_exists, :options, :as, :comment))
  ...
  
  result = execute schema_creation.accept td
  ...
  
  unless supports_indexes_in_create?
    td.indexes.each do |column_name, index_options|
      add_index(table_name, column_name, index_options)
    end
  end
  ...
  
  result    
end

This code if translated to plain English looks like this.

def create_table
  Generate SQL for CREATE TABLE
  result = Execute SQL for CREATE TABLE
  
  For each of the indexes -
   - Generate SQL for ADD INDEX
   - Execute SQL for ADD INDEX
  End
   
  return result
end

Looking at the error that we got related to existing index, it is pretty evident that if_not_exists option was not used while generating the SQL for index whereas it was used while generating the SQL for creating table.

Now the solution is straightforward. We need to make sure that Rails uses the if_not_exists option while generating the SQL for index.

The generation of SQL for indexes and its execution is handled by the add_index method. It looks like this.

def add_index(table_name, column_name, options = {})
  index_name, index_type, index_columns, index_options = add_index_options(table_name, column_name, **options)
  execute "CREATE #{index_type} INDEX #{quote_column_name(index_name)} ON #{quote_table_name(table_name)} (#{index_columns})#{index_options}"
end

So if we pass the if_not_exists option from create_table to add_index which in turn passes it to

add_index_options and which generates the proper SQL that we want, then our problem is solved!

That's what I did in this pull request https://github.com/rails/rails/pull/38555 and this issue is now fixed on Rails master.

How to handle this issue with Rails 6.0.2 and below

Support for if_not_exists 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.

class AddIssuesAgainAgainAgain < ActiveRecord::Migration[6.0]
  def change
    return if Rails.env.production?
    
    create_table :issues, if_not_exists: true do |t|
      t.integer   :comment_count
      t.string    :url, :repo_name, :user_name
      t.datetime  :last_touched_at
      t.integer   :number

      t.references :user
      t.timestamps
    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 IF NOT EXISTS 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.

# activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb 

def add_index(table_name, column_name, options = {}) #:nodoc:
  return if options[:if_not_exists] && index_exists?(table_name, column_name, options)
  ..
  execute ..
end

One more thing!

The support for if_not_exists is also available to add_index method now. So you can just pass it as follows when trying to create standalone indexes.

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.


以上所述就是小编给大家介绍的《Don't add database index if it already exists in Rails》,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对 码农网 的支持!

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

笨办法学Python 3

笨办法学Python 3

[美]泽德 A. 肖 / 王巍巍 / 人民邮电出版社 / 2018-6-1 / CNY 59.00

本书是一本Python入门书,适合对计算机了解不多,没有学过编程,但对编程感兴趣的读者学习使用。这本书以习题的方式引导读者一步一步学习编程,从简单的打印一直讲到完整项目的实现,让初学者从基础的编程技术入手,最终体验到软件开发的基本过程。本书是基于Python 3.6版本编写的。 本书结构非常简单,除“准备工作”之外,还包括52个习题,其中26个覆盖了输入/输出、变量和函数3个主题,另外26个......一起来看看 《笨办法学Python 3》 这本书的介绍吧!

在线进制转换器
在线进制转换器

各进制数互转换器

随机密码生成器
随机密码生成器

多种字符组合密码

html转js在线工具
html转js在线工具

html转js在线工具