かみぽわーる

kamipo's blog

Rails 6.1で `created_at > ?` みたいなクエリをいい感じに生成する

Rails 6.1の目玉機能として以下のように書けるwhere拡張を入れてたんですが、いろいろあって6.1からはrevertされてしまいました🥲

posts = Post.order(:id)

posts.where("id >": 9).pluck(:id)  # => [10, 11]
posts.where("id >=": 9).pluck(:id) # => [9, 10, 11]
posts.where("id <": 3).pluck(:id)  # => [1, 2]
posts.where("id <=": 3).pluck(:id) # => [1, 2, 3]

github.com

github.com

なんですが、そんなことで引き下がる僕ではないので、6.1ではpredicate生成に干渉できる拡張ポイントを用意しており、以下のようなコードを適当に読み込まれるところにしたためておけば、いともたやすくwhere拡張を実現することができます。

ActiveSupport.on_load(:active_record) do
  ActiveRecord::PredicateBuilder.prepend Module.new {
    def [](attr_name, value, operator = nil)
      if !operator && attr_name.end_with?(">", ">=", "<", "<=")
        /\A(?<attr_name>.+?)\s*(?<operator>>|>=|<|<=)\z/ =~ attr_name
        operator = OPERATORS[operator]
      end

      super
    end

    OPERATORS = { ">" => :gt, ">=" => :gteq, "<" => :lt, "<=" => :lteq }.freeze
  }
end

ぜひ活用してくださいね😉

activerecord-importを利用して無効なデータを無理やりINSERTする

activerecord-importと:on_duplicate_key_ignoreオプションを組み合わせるとカラム定義の範囲外の値であっても無理やりINSERTすることができます。

# frozen_string_literal: true

require "bundler/inline"

gemfile(true) do
  source "https://rubygems.org"

  git_source(:github) { |repo| "https://github.com/#{repo}.git" }

  gem "activerecord", "6.1.0"
  gem "activerecord-import"
  gem "mysql2"
end

require "active_record"
require "logger"

ActiveRecord::Base.establish_connection(adapter: "mysql2", database: "test", username: "root")
ActiveRecord::Base.logger = Logger.new(STDOUT)

ActiveRecord::Schema.define do
  create_table :users, force: true do |t|
    t.string :name, index: { unique: true }
    t.decimal :money, precision: 10
  end
end

class User < ActiveRecord::Base
end

attributes = [
  { name: "foo", money: "10000000000" },
  { name: "foo", money: "20000000000" },
]

User.import(attributes, on_duplicate_key_ignore: true)

# User.insert_all(attributes)

puts
puts User.pluck(:money) # => 9999999999
puts
% ruby foo.rb
Fetching gem metadata from https://rubygems.org/..............
Fetching gem metadata from https://rubygems.org/.
Resolving dependencies...
Using concurrent-ruby 1.1.7
Using bundler 2.2.3
Using minitest 5.14.3
Using zeitwerk 2.4.2
Using mysql2 0.5.3
Using i18n 1.8.7
Using tzinfo 2.0.4
Using activesupport 6.1.0
Using activemodel 6.1.0
Using activerecord 6.1.0
Using activerecord-import 1.0.7
-- create_table(:users, {:force=>true})
D, [2021-01-06T14:04:10.296375 #81282] DEBUG -- :    (66.5ms)  DROP TABLE IF EXISTS `users`
D, [2021-01-06T14:04:10.377990 #81282] DEBUG -- :    (80.7ms)  CREATE TABLE `users` (`id` bigint NOT NULL AUTO_INCREMENT PRIMARY KEY, `name` varchar(255), `money` decimal(10), UNIQUE INDEX `index_users_on_name` (`name`))
   -> 0.1967s
D, [2021-01-06T14:04:10.397652 #81282] DEBUG -- :   ActiveRecord::InternalMetadata Load (0.5ms)  SELECT `ar_internal_metadata`.* FROM `ar_internal_metadata` WHERE `ar_internal_metadata`.`key` = 'environment' LIMIT 1
D, [2021-01-06T14:04:10.417055 #81282] DEBUG -- :    (0.3ms)  SELECT @@max_allowed_packet
D, [2021-01-06T14:04:10.424171 #81282] DEBUG -- :   User Create Many (6.8ms)  INSERT IGNORE INTO `users` (`name`,`money`) VALUES ('foo',10000000000),('foo',20000000000)

D, [2021-01-06T14:04:10.428391 #81282] DEBUG -- :    (3.4ms)  SELECT `users`.`money` FROM `users`
9999999999

https://gist.github.com/kamipo/3db82c3bb7cbcbf007b4d4367a5c5227

これはどういう原理かというと、activerecord-importではINSERTしたいけどすでに(ユニークキーが)おなじレコードがあるときはスルーしたい(i.e. on_duplicate_key_ignore)という機能を実現するのにMySQLではINSERT IGNORE構文を使っていて、INSERT IGNOREではINSERT中のすべてのエラーを無視して無効な値は可能ならもっとも近い値に調整してINSERTするという振る舞いをするため、このような挙動を引き起こすことができます。

では、INSERTしたいけどすでに(ユニークキーが)おなじレコードがあるときはスルーしたい、けど無効な値はちゃんとエラーにしてほしいときはどうしたらいいかというと、Rails 6.0から使えるinsert_allというバルクインサート用のAPIを使うことができます。RailsチームではわいがMySQLチョットデキルので、この問題についてはレビューでフィードバックして対処されており安心してご利用になることができます。

github.com

See also

songmu.jp

それでは本年も引き続きよろしくおねがいいたします。

create_or_find_byでcreateもfind_byも失敗させる

Active Recordの話です。

create_or_find_byの実装はcreateしてみてユニーク制約に引っかかったらfind_byしてみるなので、ふつうに考えるとfind_byは成功しそうに見えます。

    def create_or_find_by(attributes, &block)
      transaction(requires_new: true) { create(attributes, &block) }
    rescue ActiveRecord::RecordNotUnique
      find_by!(attributes)
    end

ですが、以下のスクリプトを実行するとcreate_or_find_byはcreateがRecordNotUnique例外を吐いたあと、find_byもRecordNotFound例外を吐いてレコードを見つけられずに死にます。

ちょっと今から会食なので原理は帰ってから書き足しますしばしお待ちを🙇‍♂️

https://github.com/rails/rails/blob/3e5d504f7828118928bfcc08e5be284775836794/activerecord/lib/active_record/relation.rb#L208-L212

# frozen_string_literal: true

require "bundler/inline"

gemfile(true) do
  source "https://rubygems.org"

  git_source(:github) { |repo| "https://github.com/#{repo}.git" }

  gem "activerecord", "6.1.0"
  gem "mysql2"
end

require "active_record"
require "logger"

ActiveRecord::Base.establish_connection(adapter: "mysql2", database: "test", username: "root")
ActiveRecord::Base.logger = Logger.new(STDOUT)

ActiveRecord::Schema.define do
  create_table :users, force: true do |t|
    t.string :name, index: { unique: true }
  end
end

class User < ActiveRecord::Base
end

t = Thread.new do
  sleep 0.1
  User.create!(name: "foo")
end

User.transaction do
  User.find_by!(name: "foo")
rescue ActiveRecord::RecordNotFound
  puts 'User<name: "foo"> not found'

  sleep 0.2

  User.create_or_find_by!(name: "foo").tap do
    puts 'User<name: "foo"> has found or created'
  end
end

t.join
% ruby foo.rb
Fetching gem metadata from https://rubygems.org/.........
Resolving dependencies...
Using concurrent-ruby 1.1.7
Using i18n 1.8.5
Using minitest 5.14.2
Using tzinfo 2.0.3
Using zeitwerk 2.4.2
Using activesupport 6.1.0
Using activemodel 6.1.0
Using activerecord 6.1.0
Using bundler 2.1.4
Using mysql2 0.5.3
-- create_table(:users, {:force=>true})
D, [2020-12-16T18:55:22.915787 #49715] DEBUG -- :    (12.2ms)  DROP TABLE IF EXISTS `users`
D, [2020-12-16T18:55:22.954341 #49715] DEBUG -- :    (37.5ms)  CREATE TABLE `users` (`id` bigint NOT NULL AUTO_INCREMENT PRIMARY KEY, `name` varchar(255), UNIQUE INDEX `index_users_on_name` (`name`))
   -> 0.0754s
D, [2020-12-16T18:55:23.132151 #49715] DEBUG -- :   ActiveRecord::InternalMetadata Load (0.7ms)  SELECT `ar_internal_metadata`.* FROM `ar_internal_metadata` WHERE `ar_internal_metadata`.`key` = 'environment' LIMIT 1
D, [2020-12-16T18:55:23.147310 #49715] DEBUG -- :   TRANSACTION (0.3ms)  BEGIN
D, [2020-12-16T18:55:23.151778 #49715] DEBUG -- :   User Load (0.8ms)  SELECT `users`.* FROM `users` WHERE `users`.`name` = 'foo' LIMIT 1
User<name: "foo"> not found
D, [2020-12-16T18:55:23.283424 #49715] DEBUG -- :   TRANSACTION (0.3ms)  BEGIN
D, [2020-12-16T18:55:23.287159 #49715] DEBUG -- :   User Create (3.5ms)  INSERT INTO `users` (`name`) VALUES ('foo')
D, [2020-12-16T18:55:23.290553 #49715] DEBUG -- :   TRANSACTION (2.8ms)  COMMIT
D, [2020-12-16T18:55:23.359126 #49715] DEBUG -- :   TRANSACTION (0.3ms)  SAVEPOINT active_record_1
D, [2020-12-16T18:55:23.360585 #49715] DEBUG -- :   User Create (1.2ms)  INSERT INTO `users` (`name`) VALUES ('foo')
D, [2020-12-16T18:55:23.361383 #49715] DEBUG -- :   TRANSACTION (0.3ms)  ROLLBACK TO SAVEPOINT active_record_1
D, [2020-12-16T18:55:23.362859 #49715] DEBUG -- :   User Load (0.5ms)  SELECT `users`.* FROM `users` WHERE `users`.`name` = 'foo' LIMIT 1
D, [2020-12-16T18:55:23.363994 #49715] DEBUG -- :   TRANSACTION (0.5ms)  ROLLBACK
Traceback (most recent call last):
    12: from foo.rb:34:in `<main>'
    11: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/transactions.rb:209:in `transaction'
    10: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/connection_adapters/abstract/database_statements.rb:320:in `transaction'
     9: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/connection_adapters/abstract/transaction.rb:308:in `within_new_transaction'
     8: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activesupport-6.1.0/lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `synchronize'
     7: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activesupport-6.1.0/lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `handle_interrupt'
     6: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activesupport-6.1.0/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `block in synchronize'
     5: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activesupport-6.1.0/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `handle_interrupt'
     4: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activesupport-6.1.0/lib/active_support/concurrency/load_interlock_aware_monitor.rb:26:in `block (2 levels) in synchronize'
     3: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/connection_adapters/abstract/transaction.rb:310:in `block in within_new_transaction'
     2: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/connection_adapters/abstract/database_statements.rb:320:in `block in transaction'
     1: from foo.rb:35:in `block in <main>'
/Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/core.rb:366:in `find_by!': Couldn't find User (ActiveRecord::RecordNotFound)
    87: from foo.rb:34:in `<main>'
    86: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/transactions.rb:209:in `transaction'
    85: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/connection_adapters/abstract/database_statements.rb:320:in `transaction'
    84: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/connection_adapters/abstract/transaction.rb:308:in `within_new_transaction'
    83: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activesupport-6.1.0/lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `synchronize'
    82: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activesupport-6.1.0/lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `handle_interrupt'
    81: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activesupport-6.1.0/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `block in synchronize'
    80: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activesupport-6.1.0/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `handle_interrupt'
    79: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activesupport-6.1.0/lib/active_support/concurrency/load_interlock_aware_monitor.rb:26:in `block (2 levels) in synchronize'
    78: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/connection_adapters/abstract/transaction.rb:310:in `block in within_new_transaction'
    77: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/connection_adapters/abstract/database_statements.rb:320:in `block in transaction'
    76: from foo.rb:35:in `block in <main>'
    75: from foo.rb:41:in `rescue in block in <main>'
    74: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/querying.rb:22:in `create_or_find_by!'
    73: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/relation.rb:218:in `create_or_find_by!'
    72: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/relation/delegation.rb:108:in `method_missing'
    71: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/relation.rb:406:in `scoping'
    70: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/relation.rb:811:in `_scoping'
    69: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/relation.rb:406:in `block in scoping'
    68: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/relation/delegation.rb:108:in `block in method_missing'
    67: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/relation/delegation.rb:108:in `public_send'
    66: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/transactions.rb:209:in `transaction'
    65: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/connection_adapters/abstract/database_statements.rb:320:in `transaction'
    64: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/connection_adapters/abstract/transaction.rb:308:in `within_new_transaction'
    63: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activesupport-6.1.0/lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `synchronize'
    62: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activesupport-6.1.0/lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `handle_interrupt'
    61: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activesupport-6.1.0/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `block in synchronize'
    60: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activesupport-6.1.0/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `handle_interrupt'
    59: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activesupport-6.1.0/lib/active_support/concurrency/load_interlock_aware_monitor.rb:26:in `block (2 levels) in synchronize'
    58: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/connection_adapters/abstract/transaction.rb:310:in `block in within_new_transaction'
    57: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/connection_adapters/abstract/database_statements.rb:320:in `block in transaction'
    56: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/relation.rb:218:in `block in create_or_find_by!'
    55: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/relation.rb:114:in `create!'
    54: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/relation.rb:406:in `scoping'
    53: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/relation.rb:811:in `_scoping'
    52: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/relation.rb:406:in `block in scoping'
    51: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/relation.rb:114:in `block in create!'
    50: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/relation.rb:806:in `_create!'
    49: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/persistence.rb:55:in `create!'
    48: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/suppressor.rb:48:in `save!'
    47: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/transactions.rb:300:in `save!'
    46: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/transactions.rb:348:in `with_transaction_returning_status'
    45: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/connection_adapters/abstract/database_statements.rb:318:in `transaction'
    44: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/transactions.rb:352:in `block in with_transaction_returning_status'
    43: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/transactions.rb:300:in `block in save!'
    42: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/validations.rb:53:in `save!'
    41: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/persistence.rb:507:in `save!'
    40: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/timestamp.rb:126:in `create_or_update'
    39: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/callbacks.rb:457:in `create_or_update'
    38: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activesupport-6.1.0/lib/active_support/callbacks.rb:824:in `_run_save_callbacks'
    37: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activesupport-6.1.0/lib/active_support/callbacks.rb:98:in `run_callbacks'
    36: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/callbacks.rb:457:in `block in create_or_update'
    35: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/persistence.rb:900:in `create_or_update'
    34: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/timestamp.rb:108:in `_create_record'
    33: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/callbacks.rb:461:in `_create_record'
    32: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activesupport-6.1.0/lib/active_support/callbacks.rb:824:in `_run_create_callbacks'
    31: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activesupport-6.1.0/lib/active_support/callbacks.rb:98:in `run_callbacks'
    30: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/callbacks.rb:461:in `block in _create_record'
    29: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/attribute_methods/dirty.rb:201:in `_create_record'
    28: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/locking/optimistic.rb:79:in `_create_record'
    27: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/counter_cache.rb:166:in `_create_record'
    26: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/persistence.rb:929:in `_create_record'
    25: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/persistence.rb:375:in `_insert_record'
    24: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/connection_adapters/abstract/query_cache.rb:22:in `insert'
    23: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/connection_adapters/abstract/database_statements.rb:171:in `insert'
    22: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/connection_adapters/abstract/database_statements.rb:136:in `exec_insert'
    21: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/connection_adapters/mysql/database_statements.rb:55:in `exec_query'
    20: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:215:in `execute_and_free'
    19: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/connection_adapters/mysql/database_statements.rb:50:in `execute'
    18: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:204:in `execute'
    17: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/connection_adapters/abstract_adapter.rb:688:in `log'
    16: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activesupport-6.1.0/lib/active_support/notifications/instrumenter.rb:24:in `instrument'
    15: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/connection_adapters/abstract_adapter.rb:696:in `block in log'
    14: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activesupport-6.1.0/lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `synchronize'
    13: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activesupport-6.1.0/lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `handle_interrupt'
    12: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activesupport-6.1.0/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `block in synchronize'
    11: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activesupport-6.1.0/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `handle_interrupt'
    10: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activesupport-6.1.0/lib/active_support/concurrency/load_interlock_aware_monitor.rb:26:in `block (2 levels) in synchronize'
     9: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/connection_adapters/abstract_adapter.rb:697:in `block (2 levels) in log'
     8: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:205:in `block in execute'
     7: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activesupport-6.1.0/lib/active_support/dependencies/interlock.rb:47:in `permit_concurrent_loads'
     6: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activesupport-6.1.0/lib/active_support/concurrency/share_lock.rb:187:in `yield_shares'
     5: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activesupport-6.1.0/lib/active_support/dependencies/interlock.rb:48:in `block in permit_concurrent_loads'
     4: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:206:in `block (2 levels) in execute'
     3: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/mysql2-0.5.3/lib/mysql2/client.rb:130:in `query'
     2: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/mysql2-0.5.3/lib/mysql2/client.rb:130:in `handle_interrupt'
     1: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/mysql2-0.5.3/lib/mysql2/client.rb:131:in `block in query'
/Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/mysql2-0.5.3/lib/mysql2/client.rb:131:in `_query': Duplicate entry 'foo' for key 'users.index_users_on_name' (Mysql2::Error)
    87: from foo.rb:34:in `<main>'
    86: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/transactions.rb:209:in `transaction'
    85: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/connection_adapters/abstract/database_statements.rb:320:in `transaction'
    84: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/connection_adapters/abstract/transaction.rb:308:in `within_new_transaction'
    83: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activesupport-6.1.0/lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `synchronize'
    82: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activesupport-6.1.0/lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `handle_interrupt'
    81: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activesupport-6.1.0/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `block in synchronize'
    80: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activesupport-6.1.0/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `handle_interrupt'
    79: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activesupport-6.1.0/lib/active_support/concurrency/load_interlock_aware_monitor.rb:26:in `block (2 levels) in synchronize'
    78: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/connection_adapters/abstract/transaction.rb:310:in `block in within_new_transaction'
    77: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/connection_adapters/abstract/database_statements.rb:320:in `block in transaction'
    76: from foo.rb:35:in `block in <main>'
    75: from foo.rb:41:in `rescue in block in <main>'
    74: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/querying.rb:22:in `create_or_find_by!'
    73: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/relation.rb:218:in `create_or_find_by!'
    72: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/relation/delegation.rb:108:in `method_missing'
    71: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/relation.rb:406:in `scoping'
    70: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/relation.rb:811:in `_scoping'
    69: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/relation.rb:406:in `block in scoping'
    68: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/relation/delegation.rb:108:in `block in method_missing'
    67: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/relation/delegation.rb:108:in `public_send'
    66: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/transactions.rb:209:in `transaction'
    65: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/connection_adapters/abstract/database_statements.rb:320:in `transaction'
    64: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/connection_adapters/abstract/transaction.rb:308:in `within_new_transaction'
    63: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activesupport-6.1.0/lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `synchronize'
    62: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activesupport-6.1.0/lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `handle_interrupt'
    61: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activesupport-6.1.0/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `block in synchronize'
    60: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activesupport-6.1.0/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `handle_interrupt'
    59: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activesupport-6.1.0/lib/active_support/concurrency/load_interlock_aware_monitor.rb:26:in `block (2 levels) in synchronize'
    58: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/connection_adapters/abstract/transaction.rb:310:in `block in within_new_transaction'
    57: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/connection_adapters/abstract/database_statements.rb:320:in `block in transaction'
    56: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/relation.rb:218:in `block in create_or_find_by!'
    55: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/relation.rb:114:in `create!'
    54: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/relation.rb:406:in `scoping'
    53: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/relation.rb:811:in `_scoping'
    52: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/relation.rb:406:in `block in scoping'
    51: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/relation.rb:114:in `block in create!'
    50: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/relation.rb:806:in `_create!'
    49: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/persistence.rb:55:in `create!'
    48: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/suppressor.rb:48:in `save!'
    47: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/transactions.rb:300:in `save!'
    46: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/transactions.rb:348:in `with_transaction_returning_status'
    45: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/connection_adapters/abstract/database_statements.rb:318:in `transaction'
    44: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/transactions.rb:352:in `block in with_transaction_returning_status'
    43: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/transactions.rb:300:in `block in save!'
    42: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/validations.rb:53:in `save!'
    41: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/persistence.rb:507:in `save!'
    40: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/timestamp.rb:126:in `create_or_update'
    39: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/callbacks.rb:457:in `create_or_update'
    38: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activesupport-6.1.0/lib/active_support/callbacks.rb:824:in `_run_save_callbacks'
    37: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activesupport-6.1.0/lib/active_support/callbacks.rb:98:in `run_callbacks'
    36: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/callbacks.rb:457:in `block in create_or_update'
    35: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/persistence.rb:900:in `create_or_update'
    34: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/timestamp.rb:108:in `_create_record'
    33: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/callbacks.rb:461:in `_create_record'
    32: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activesupport-6.1.0/lib/active_support/callbacks.rb:824:in `_run_create_callbacks'
    31: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activesupport-6.1.0/lib/active_support/callbacks.rb:98:in `run_callbacks'
    30: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/callbacks.rb:461:in `block in _create_record'
    29: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/attribute_methods/dirty.rb:201:in `_create_record'
    28: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/locking/optimistic.rb:79:in `_create_record'
    27: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/counter_cache.rb:166:in `_create_record'
    26: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/persistence.rb:929:in `_create_record'
    25: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/persistence.rb:375:in `_insert_record'
    24: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/connection_adapters/abstract/query_cache.rb:22:in `insert'
    23: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/connection_adapters/abstract/database_statements.rb:171:in `insert'
    22: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/connection_adapters/abstract/database_statements.rb:136:in `exec_insert'
    21: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/connection_adapters/mysql/database_statements.rb:55:in `exec_query'
    20: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:215:in `execute_and_free'
    19: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/connection_adapters/mysql/database_statements.rb:50:in `execute'
    18: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:204:in `execute'
    17: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/connection_adapters/abstract_adapter.rb:688:in `log'
    16: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activesupport-6.1.0/lib/active_support/notifications/instrumenter.rb:24:in `instrument'
    15: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/connection_adapters/abstract_adapter.rb:696:in `block in log'
    14: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activesupport-6.1.0/lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `synchronize'
    13: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activesupport-6.1.0/lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `handle_interrupt'
    12: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activesupport-6.1.0/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `block in synchronize'
    11: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activesupport-6.1.0/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `handle_interrupt'
    10: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activesupport-6.1.0/lib/active_support/concurrency/load_interlock_aware_monitor.rb:26:in `block (2 levels) in synchronize'
     9: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/connection_adapters/abstract_adapter.rb:697:in `block (2 levels) in log'
     8: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:205:in `block in execute'
     7: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activesupport-6.1.0/lib/active_support/dependencies/interlock.rb:47:in `permit_concurrent_loads'
     6: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activesupport-6.1.0/lib/active_support/concurrency/share_lock.rb:187:in `yield_shares'
     5: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activesupport-6.1.0/lib/active_support/dependencies/interlock.rb:48:in `block in permit_concurrent_loads'
     4: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:206:in `block (2 levels) in execute'
     3: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/mysql2-0.5.3/lib/mysql2/client.rb:130:in `query'
     2: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/mysql2-0.5.3/lib/mysql2/client.rb:130:in `handle_interrupt'
     1: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/mysql2-0.5.3/lib/mysql2/client.rb:131:in `block in query'
/Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/mysql2-0.5.3/lib/mysql2/client.rb:131:in `_query': Mysql2::Error: Duplicate entry 'foo' for key 'users.index_users_on_name' (ActiveRecord::RecordNotUnique)
    18: from foo.rb:34:in `<main>'
    17: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/transactions.rb:209:in `transaction'
    16: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/connection_adapters/abstract/database_statements.rb:320:in `transaction'
    15: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/connection_adapters/abstract/transaction.rb:308:in `within_new_transaction'
    14: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activesupport-6.1.0/lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `synchronize'
    13: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activesupport-6.1.0/lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `handle_interrupt'
    12: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activesupport-6.1.0/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `block in synchronize'
    11: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activesupport-6.1.0/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `handle_interrupt'
    10: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activesupport-6.1.0/lib/active_support/concurrency/load_interlock_aware_monitor.rb:26:in `block (2 levels) in synchronize'
     9: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/connection_adapters/abstract/transaction.rb:310:in `block in within_new_transaction'
     8: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/connection_adapters/abstract/database_statements.rb:320:in `block in transaction'
     7: from foo.rb:35:in `block in <main>'
     6: from foo.rb:41:in `rescue in block in <main>'
     5: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/querying.rb:22:in `create_or_find_by!'
     4: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/relation.rb:217:in `create_or_find_by!'
     3: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/relation.rb:220:in `rescue in create_or_find_by!'
     2: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/relation/finder_methods.rb:87:in `find_by!'
     1: from /Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/relation/finder_methods.rb:104:in `take!'
/Users/kamipo/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.1.0/lib/active_record/relation/finder_methods.rb:354:in `raise_record_not_found_exception!': Couldn't find User with [WHERE `users`.`name` = ?] (ActiveRecord::RecordNotFound)

https://gist.github.com/kamipo/480f9e6fb61bef0f36b1edbccfd30f66

SELECT ... FOR UPDATE同士でデッドロックさせる

最近SELECT ... FOR UPDATEでデッドロックする話を何度かしたので。

前職のときにUPDATE同士がデッドロックしてたときに、SELECT ... FOR UPDATEで排他ロックを取ってからUPDATEしてデッドロックを防ぎますってPRをレビューしてたときのことで、複数レコードの排他ロックは一瞬ですべてのレコードのロックを取れるわけではなく、ロックを取る順番が揃っていないと簡単にデッドロックしますよという話です。

https://gist.github.com/kamipo/0bb4e37d58ba18a8cefb8aa02f778231

# frozen_string_literal: true

require "mysql2"

def client
  Mysql2::Client.new(
    host: "localhost",
    username: "root",
    database: "test",
  )
end

c1 = client
c2 = client

c1.query("DROP TABLE IF EXISTS `user_tables`")
c1.query <<-SQL
CREATE TABLE `user_tables` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_user_tables_on_name` (`name`)
)
SQL

1000.downto(1) do |i|
  c1.query("INSERT INTO `user_tables` (`name`) VALUES ('p#{i}')")
end

t = Thread.new do
  100.times do |j|
    c2.query("BEGIN")
    #puts "c2 locking:#{j}"
    c2.query("SELECT 1 FROM `user_tables` FORCE INDEX(index_user_tables_on_name) WHERE `name` IN (#{1000.downto(1).map{|i|"'p#{i}'"}.join(",")}) FOR UPDATE")
    #puts "c2 locked:#{j}"
    c2.query("COMMIT")
  end
end

100.times do |j|
  c1.query("BEGIN")
  #puts "c1 locking:#{j}"
  c1.query("SELECT 1 FROM `user_tables` FORCE INDEX(PRIMARY) WHERE `id` IN (#{1.upto(1000).to_a.join(",")}) FOR UPDATE")
  #puts "c1 locked:#{j}"
  c1.query("COMMIT")
end

t.join
% be ruby lock.rb
/Users/kamipo/.rbenv/versions/2.7.0-dev/lib/ruby/gems/2.7.0/gems/bundler-1.17.3/lib/bundler/rubygems_integration.rb:200: warning: constant Gem::ConfigMap is deprecated
/Users/kamipo/.rbenv/versions/2.7.0-dev/lib/ruby/gems/2.7.0/gems/bundler-1.17.3/lib/bundler/rubygems_integration.rb:200: warning: constant Gem::ConfigMap is deprecated
#<Thread:0x00007fa6099b38c0@lock.rb:30 run> terminated with exception (report_on_exception is true):
Traceback (most recent call last):
    6: from lock.rb:31:in `block in <main>'
    5: from lock.rb:31:in `times'
    4: from lock.rb:34:in `block (2 levels) in <main>'
    3: from /Users/kamipo/src/github.com/brianmario/mysql2/lib/mysql2/client.rb:130:in `query'
    2: from /Users/kamipo/src/github.com/brianmario/mysql2/lib/mysql2/client.rb:130:in `handle_interrupt'
    1: from /Users/kamipo/src/github.com/brianmario/mysql2/lib/mysql2/client.rb:138:in `block in query'
/Users/kamipo/src/github.com/brianmario/mysql2/lib/mysql2/client.rb:138:in `_query': Deadlock found when trying to get lock; try restarting transaction (Mysql2::Error)
Traceback (most recent call last):
    6: from lock.rb:31:in `block in <main>'
    5: from lock.rb:31:in `times'
    4: from lock.rb:34:in `block (2 levels) in <main>'
    3: from /Users/kamipo/src/github.com/brianmario/mysql2/lib/mysql2/client.rb:130:in `query'
    2: from /Users/kamipo/src/github.com/brianmario/mysql2/lib/mysql2/client.rb:130:in `handle_interrupt'
    1: from /Users/kamipo/src/github.com/brianmario/mysql2/lib/mysql2/client.rb:138:in `block in query'
/Users/kamipo/src/github.com/brianmario/mysql2/lib/mysql2/client.rb:138:in `_query': Deadlock found when trying to get lock; try restarting transaction (Mysql2::Error)

これはどういう原理でデッドロックさせているかというと、プライマリキーとセカンダリキーで意図的に並び順が異なるようなデータを生成して、プライマリキーを使う実行計画のSELECT ... FOR UPDATEとセカンダリキーを使う実行計画のSELECT ... FOR UPDATEが真逆の順序でレコードのロックを取るように仕向けてデッドロックを引き起こさせています。

このように、複数レコードのロックは一瞬で同時に起きるわけではなく、順番に起きて途中の状態(ロックが取り終わったレコードとこれからロックを取るつもりのレコードがある状態)が存在するので、ロックを取る順番が一意になるようにクエリや実行計画を揃えるというのがこの手の問題に対する一般的な対処法になります。

ロックを取る順番って見えづらいので問題に気づきづらいですよね、かくいう僕も眼の良さを活かして気合いで対処してるので、なんかいい方法あったら教えてください。

ツイッターで見つけて直したActiveRecordの問題さらに3つ

ツイッターで見つけて直したActiveRecordの問題3つ - かみぽわーるの続き。

  • where(id: ..1) ("id" <= 1)をnotしたら"id" > 1になってほしい

github.com

  • association先のカラムをpluckしたときもちゃんとtype castされてほしい

github.com

  • rewhereでちゃんとテーブルを考慮してwhere句を上書きしてほしい

github.com

ツイッターで見つけて直したActiveRecordの問題3つ

Rails Advent Calendar 2020の3日目です。

時間がないのでとりいそぎ3つだけ。

  • enum state: {active: 0, inactive: 1}とかした時に、typecast前の0とか1を取りたい

github.com

  • belongs_to :author, class_name: 'User'したときにleft_joins(:author).where("author.id": nil)とか書きたい

github.com

  • attributeでDBの型情報を保ったままdefault値だけ定義したい

github.com

ISUCON10予選ふりかえり

ISUCON10予選おつかれさまでした。ISUUMOいい問題でしたね。過去出題側を担当したこともある身でも、参加者の完全攻略に対する怖れもあって仕様が肥大化するなか今回これだけコンパクトな仕様のアプリケーションでこれだけ楽しめる出題をしたのマジですごいと思いました。

今回の問題はMySQLかつ検索ヘヴィな問題で僕のバックグラウンドに向いてる問題にも関わらず、ずっと手を動かしていたわりに効果の高い施策に取り組めず、あらためてISUCONの難しさを痛感したしこれぞISUCONなのだなあと思います。

僕の文章読解が遅く仕様理解にとても時間を要するという性質から、これまでのISUCONでは常にアプリケーションの仕様や性質を理解できる前に時間的制約からあらゆる決断を迫られるという状況にあり、この状況で仕様や性質を理解できていたとしたらできた正しい決断をしていくのは本当に難しいと思っていて、今回ずっと手を動かしていたわりに結果が振るわなかったのもひとえにその難しさだなあと個人的には思っています。

時間を節約するために個人的にとても時間を要する仕様書を読むのを疎かにして失敗したこともあるし、DBボトルネックが支配的ではない問題でスロークエリの対処に時間を投下してアプリケーション仕様を最後までちゃんと理解しないまま失敗したこともある。僕の技術的なバックグラウンドであるMySQLボトルネックの支配的な要素ではない近年のISUCONで、チームで唯一普段Rubyを書いている僕が、自分の有限の時間を何に投下するかというのをその場その場で決断していくのはとてもハードだったなと感じた。だけれど、そのことの精度を上げていくことがISUCONでの結果に繋がっていくのではないかというのが今回ISUCON10予選を終えて感じた個人的な総括になります。

今回手元で動かせるアプリケーションでもあったので、今日起きてから予定の合間合間に予選で決断できなかったDB側の感想戦をしてみたけど、いやぁこれがその場でできないのがマジでISUCONだなあとあらためて思った。未来のいつかの自分のために今日のこの気持ちをここにしたためておきます。

DB側感想戦

感想戦: index comment by kamipo · Pull Request #12 · soudai/isucon10-qualify · GitHub

8e98179ad6 以降のスキーマ変更(index削除とか)効いてなかった by kamipo · Pull Request #13 · soudai/isucon10-qualify · GitHub

感想戦: Add descending index `popularity DESC` by kamipo · Pull Request #14 · soudai/isucon10-qualify · GitHub

不要なindex削除 by kamipo · Pull Request #15 · soudai/isucon10-qualify · GitHub

Optimize get '/api/recommended_estate/:id' by kamipo · Pull Request #16 · soudai/isucon10-qualify · GitHub

Optimize post '/api/chair/buy/:id' by kamipo · Pull Request #17 · soudai/isucon10-qualify · GitHub

Avoid N+1 in post '/api/estate/nazotte' by kamipo · Pull Request #18 · soudai/isucon10-qualify · GitHub