🐙

末尾の半角スペースで困惑：ActiveModelとMySQLの連携問題

Matsukura Yuki

2023/04/14に公開

概要

ActiveModelのunique制約があるアトリビュートにおいて、末尾に半角スペースがあると挙動がおかしくなる問題についての話です。

問題

以下のようにcodeがユニークなアトリビュートを定義してあるとします。

class Product < ApplicationRecord
  validates :code, presence: true
  validates :code, uniqueness: true
end

上記の定義によれば、codeが aa と aa (末尾に半角スペースが2つ) のオブジェクトが存在できますが、実際には登録できませんでした。

以下に実行結果を貼り付けます。
コードは正しく動くはずなのに、2つ目のオブジェクトがvalidになりません。

> p1 = FactoryBot.create(:product)
> p1.code = 'aa '
=> "aa "
> p1.save
=> true
> p2 = FactoryBot.create(:product)
> p2.code = 'aa   '
=> "aa   "
> p2.save
=> false
> p2.valid?
=> false
> 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つしか存在しないレコードがヒットしてしまいます。

> 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  "
> Product.where(code: 'test0410  ').count
  Product Load (1.0ms)  SELECT COUNT(*) FROM `products` WHERE `products`.`code` = 'test0410  '
=> 1
> Product.where(code: 'test0410 ').count
  Product load (0.6ms)  SELECT COUNT(*) FROM `products` WHERE `products`.`code` = 'test0410 '
=> 1
> Product.where(code: 'test0410').count
  Product Load (0.5ms)  SELECT COUNT(*) FROM `products` WHERE `products`.`code` = 'test0410'
=> 1

発行されているSQLのクエリーは意図したとおりになっています。
文字列に対して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に従属している値です。

code カラムで利用している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のオフィシャルドキュメントではここに記載があります。

そもそも、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 が該当しそうです。

こちらの比較表からしても、用途としては基本的にすべて区別したいので utf8mb4_0900_bin を選択して良さそうです。

collationを変更するためにRailsのmigrationを書きます。

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だと的確な原因を指摘するところまではやってくれませんでした。

株式会社マインディアテックブログPublication

株式会社マインディアのテックブログになります。サービスやシステム、カルチャーの話題はリンクの「採用」のスライドをご覧ください！

概要

問題

アプローチ

テストコード

修正

影響度調査

まとめ

Discussion