概要
- ActiveModelのuniqueがあるアトリビュートの末尾に半角スペースがあると挙動がおかしくなるケースがありました。
問題
以下のようにcodeがユニークなアトリビュートを定義してあるとします。
class Product < ApplicationRecord validates :code, presence: true validates :code, uniqueness: true end
上記の定義によれば、codeが ‘aa ‘ と ‘aa ‘ (末尾に半角スペースが2つ）のオブジェクトが存在することができますが、実際には登録できません。
以下のコードは正しく動くはずなのに、2つ目のオブジェクトがvalidになりません。
[2] pry(main)> p1 = FactoryBot.create(:product)
[3] pry(main)> p1.code = 'aa '
=> "aa "
[4] pry(main)> p1.save
=> true
[5] pry(main)> p2 = FactoryBot.create(:product)
[6] pry(main)> p2.code = 'aa '
=> "aa "
[7] pry(main)> p2.save
=> false
[8] pry(main)> p2.valid?
=> false
[9] pry(main)> p2.errors
=> #<ActiveModel::Errors:0x00007f6893d164d0
@base=
#<Product:0x00007f6893f534a0
id: 2,
code: "aa ">,
@errors=[#<ActiveModel::Error attribute=code, type=taken, options={:value=>"aa "}>]>
[10] pry(main)>
ActiveRecord上では ‘aa ‘ と ‘aa ‘ が同一に扱われているようです。
検証するために、ActiveRecordでcodeをキーに検索してみます。同時に、発行されているSQLを確認してみます。
そうすると、末尾の半角スペースの数に関わらず、半角スペースが1つしか存在しないレコードがヒットしてしまいます。
[115] pry(main)> Product.where(code: 'test0410').first.code Product Load (1.0ms) SELECT `products`.* FROM `products` WHERE `products`.`code` = 'test0410' ORDER BY `products`.`id` ASC LIMIT 1 => "test0410 " [116] pry(main)> Product.where(code: 'test0410 ').count Product Load (1.0ms) SELECT COUNT(*) FROM `products` WHERE `products`.`code` = 'test0410 ' => 1 [117] pry(main)> Product.where(code: 'test0410 ').count Product load (0.6ms) SELECT COUNT(*) FROM `products` WHERE `products`.`code` = 'test0410 ' => 1 [118] pry(main)> Product.where(code: 'test0410').count Product Load (0.5ms) SELECT COUNT(*) FROM `products` WHERE `products`.`code` = 'test0410' => 1
発行されているクエリーは正しそうな感じはします。（文字列に対してLIKEではなくてイコールで条件を書いているのが若干微妙ですが）
SQLレベルで予期した動作をしていないので、SQLで検証してみます。
MySQL > update products set code = 'aa ' where id = 2; ERROR 1062 (23000): Duplicate entry '1-aa ' for key 'products.index_products_on_code' MySQL > update products set code = 'aa ' where id = 1; Query OK, 0 rows affected (0.001 sec) Rows matched: 1 Changed: 0 Warnings: 0 MySQL > update products set code = 'aa ' where id = 2; ERROR 1062 (23000): Duplicate entry '1-aa ' for key 'products.index_products_on_code'
上記の内容により、MySQLの物理スキーマレベルで半角スペースの多さが同一に扱われてしまっています。
ということで問題はMySQLレベルにありそうです。
アプローチ
最近の私のアシスタント（GPT-4) に聞いてみました。
なるほど。PAD SPACEという概念は知りませんでした。
PAD SPACEというプロパティはcollationに従属している値です。
ということで、現在のカラムのcollationがどうなっているのかを調べてみます。
MySQL > SELECT COLUMN_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'products'; +-----------------------------+--------------------+ | COLUMN_NAME | COLLATION_NAME | +-----------------------------+--------------------+ | id | NULL | | code | utf8mb4_bin | +-----------------------------+--------------------+ 34 rows in set (0.002 sec)
utf8mb4_binになっています。
この、utf8mb4_binがPAD SPACEなのかを調べてみます。
MySQL> SELECT COLLATION_NAME, PAD_ATTRIBUTE
-> FROM INFORMATION_SCHEMA.COLLATIONS
-> WHERE COLLATION_NAME LIKE 'utf8mb4_bin';
+----------------+---------------+
| COLLATION_NAME | PAD_ATTRIBUTE |
+----------------+---------------+
| utf8mb4_bin | PAD SPACE |
+----------------+---------------+
1 row in set (0.002 sec)
PAD SPACEでした。ということで、現在利用中のcollationがPAD SPCAE属性があるcollationのため、末尾の半角スペースがいくつあっても同一に扱われてしまっていました。
MySQLのオフィシャルドキュメントではここに記載してあります。
MySQL :: MySQL 8.0 リファレンスマニュアル :: 10.8.5 バイナリ照合順序と_bin 照合順序
dev.mysql.com
そもそも、utf8mb4_binは、suffixにbinaryのbinが付いているのに、全然binaryで区別してくれなくて悲しいです。
テストコード
修正前と後でモデルが正常に動くかを検証するために、FactoryBotを使ったrspecのコードを書いておきます。
spec/models/product_spec.rb
require 'rails_helper'
RSpec.describe Product do
it 'should be valid' do
expect(create(:product, code: 'aa ')).to be_valid
expect(create(:product, code: 'aa ')).to be_valid
end
end
失敗してしまいます。
root@455cbca12654:/app# bundle exec rspec spec/models/product_spec.rb
Randomized with seed 53478
F
Failures:
1) Product should be valid
Failure/Error: p2 = create(:product, code: 'aa ')
ActiveRecord::RecordInvalid:
バリデーションに失敗しました: codeはすでに存在します
# /usr/local/bundle/gems/factory_bot-6.2.1/lib/factory_bot/evaluation.rb:18:in `create'
# /usr/local/bundle/gems/factory_bot-6.2.1/lib/factory_bot/strategy/create.rb:12:in `block in result'
# /usr/local/bundle/gems/factory_bot-6.2.1/lib/factory_bot/strategy/create.rb:9:in `result'
# /usr/local/bundle/gems/factory_bot-6.2.1/lib/factory_bot/factory.rb:43:in `run'
# /usr/local/bundle/gems/factory_bot-6.2.1/lib/factory_bot/factory_runner.rb:29:in `block in run'
# /usr/local/bundle/gems/factory_bot-6.2.1/lib/factory_bot/factory_runner.rb:28:in `run'
# /usr/local/bundle/gems/factory_bot-6.2.1/lib/factory_bot/strategy_syntax_method_registrar.rb:28:in `block in define_singular_strategy_method'
# ./spec/models/product_spec.rb:6:in `block (2 levels) in <top (required)>'
Finished in 4.73 seconds (files took 2.42 seconds to load)
1 example, 1 failure
Failed examples:
rspec ./spec/models/product_spec.rb:4 # Product should be valid
Randomized with seed 53478
修正
no padのutf8mb4のbinaryに近いcollationを探してみます。
MySQL > show collation where collation like 'utf8mb4%' and collation like '%bin'; +------------------+---------+-----+---------+----------+---------+---------------+ | Collation | Charset | Id | Default | Compiled | Sortlen | Pad_attribute | +------------------+---------+-----+---------+----------+---------+---------------+ | utf8mb4_0900_bin | utf8mb4 | 309 | | Yes | 1 | NO PAD | | utf8mb4_bin | utf8mb4 | 46 | | Yes | 1 | PAD SPACE | +------------------+---------+-----+---------+----------+---------+---------------+ 2 rows in set (0.004 sec)
こちらの比較表からしても、用途としては基本的にすべて区別したいのでutf8mb4_0900_binが良さそうです。
change_column :products, :code, :string, null: false, collation: :utf8mb4_0900_bin
migrationを実行した後にrspecを実行したら正常に終わりました。
root@6d3039d9f78e:/app# bundle exec rspec spec/models/product_spec.rb Randomized with seed 38846 . Finished in 2.95 seconds (files took 29.75 seconds to load) 1 example, 0 failures Randomized with seed 38846
影響度調査
今回のような、サロゲートキーに対してUNIQUE KEYを貼って、厳密な検索や値の保管を行うときに問題になります。
今回は、商品コード関連で気づくことができました。他にも同じようなサロゲートキーの使い方をしている箇所が10箇所以上あったのでcollationをすべて見直しました。
サロゲートキーのカラム名をつける際に、命名規則に従っていれば以下のクエリーで一括で検索できます。
MySQL > SELECT table_name,COLUMN_NAME, data_type, collation_name FROM INFORMATION_SCHEMA.COLUMNS where table_schema = '<your database name>' and column_name = 'code'; +----------------------+-------------+-----------+--------------------+ | TABLE_NAME | COLUMN_NAME | DATA_TYPE | COLLATION_NAME | +----------------------+-------------+-----------+--------------------+ | table_1 | code | varchar | utf8mb4_0900_ai_ci | | table_2 | code | varchar | utf8mb4_bin | | table_3 | code | varchar | utf8mb4_bin | | table_4 | code | varchar | utf8mb4_bin | | table_5 | code | varchar | utf8mb4_bin | | table_6 | code | varchar | utf8mb4_0900_ai_ci | | table_7 | code | varchar | utf8mb4_bin | | table_8 | code | varchar | utf8mb4_bin | | table_9 | code | varchar | utf8mb4_bin | | table_10 | code | varchar | utf8mb4_bin | | table_11 | code | varchar | utf8mb4_bin | | table_12 | code | varchar | utf8mb4_bin | | table_13 | code | varchar | utf8mb4_bin | | table_14 | code | varchar | utf8mb4_bin | +----------------------+-------------+-----------+--------------------+ 14 rows in set (0.006 sec)
まとめ
MySQLのcollationによって、カラムの値にある最後の半角スペースがいくつあっても同一に扱われる場合がある。
上記により、’aa ‘ と ‘aa ‘ が同一に扱われる。
GPT-4すごい。
ちなみに、GPT-3.5だと的確な原因を指摘するところまではやってくれませんでした。
Comments