1641 文字
8 分
末尾の半角スペースで困惑：ActiveModelとMySQLの連携問題
2023-04-14
Ruby on Rails
MySQL
/
rspec
/
Ruby on Rails

概要#

  • 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のオフィシャルドキュメントではここに記載してあります。

https://dev.mysql.com/doc/refman/8.0/ja/charset-binary-collations.html

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

末尾の半角スペースで困惑：ActiveModelとMySQLの連携問題
https://blog.teraren.com/posts/mysql-pad-space-and-active-record/
作者
Yuki Matsukura
公開日
2023-04-14
ライセンス
CC BY-NC-SA 4.0
AtlassianをSP、Google WorkspaceをIdPとしてSAMLを設定する方法
Raspberry PiのWiFiと有線LANに静的IPを振る

関連記事

FakerやGimeiのseedをrspecのseedと一致させる
2022-06-02Ruby on Rails
Rails + MySQL 8でngramを用いたfulltext index
2021-10-19Ruby on Rails
Rail7の推奨になったimportmap+ dartsassを使ってサービスを構築してみました
2022-12-14Ruby on Rails

コメント

© 2026 Yuki Matsukura. All Rights Reserved. / RSS / Sitemap / プライバシーポリシー