Zenn
🕌

rails mysql5.7から8.0アップグレード時の注意点📝

2024/02/05に公開
SQL
tech

railsが参照しているrdsをmysql5.7から8.0にアップグレードした時の注意点メモ

utf8mb4を指定した場合は以下のパラメーターグループをutf8mb4にする必要がある

  • character_set_database

    • データベースの文字セットを定義します。文字セットは、文字を数値で表現する方法を定義するデータエンコーディングの一種です。
      この設定は、新しく作成されるテーブルとそのカラムに使用されるデフォルトの文字セットを指定します。特定のテーブルやカラムに対して異なる文字セットを設定することも可能ですが、character_set_databaseはデフォルトの設定で、特定でない指定がない場合はこれが適用されます。

  • character_set_client

    • ユーザーからのSQLステートメントがエンコードされている方法を定義します。クライアントからデータベースサーバーに送信される全てのクエリと、そのクエリで使用されるリテラル文字列は、character_set_clientで指定された文字セットによってエンコードされます。
      これは、クライアント（ユーザーが利用しているアプリケーションやインターフェース）とデータベースサーバー間で文字データが適切に伝えられ、理解されることを確保します。この設定が適切でないと、特定の文字セットでエンコードされたデータが正しく理解および表示されない可能性があります。

  • character_set_connection

    • MySQLやMariaDBなどのAmazon RDSにおける設定の一つで、クライアントとサーバの間での通信時に用いられる文字セットを制定します。
      正確には、サーバが受け取ったクエリを解釈するときの文字セットがこのcharacter_set_connectionで決まります。つまり、クライアントから送信されたSQLステートメントがサーバでどのように解釈されるかを制御します。
      この設定は、異なるロケーションや異なる文字セットを使用するクライアントとデータベースサーバとの間で、テキストデータが適切に解釈と表示が行われるのを確実にします。設定が不適切な場合、データベースサーバは特定の文字セットでエンコードされたデータを正しく解釈できないかもしれません

  • character_set_results

    • Amazon RDSのパラメータグループ設定の一つで、データベースからクライアントへの結果セットの文字セットを定義します。
      具体的には、データベースサーバーからクライアントへ送り返される結果（例えばSELECTステートメントの結果など）がどのようにエンコードされるかを制御します。
      この設定は、データベースから返される文字列データがクライアントに適切に表示されることを確保します。設定が適切でないと、クライアントは特定の文字セットでエンコードされた結果を正しく解釈できない可能性があります。逆に正しく設定されていれば、多様な文字データが正しくエンコード・デコードできます。

照合順序をmysql5.7で使われていたutf8mb4_general_ci使う指定方法

  • collation-server

    • Amazon RDSのMySQLやMariaDB設定の一つで、文字列の比較規則（順序やソート順）を定義するための設定です。
      コレーションは、特定の文字セット内で文字の順序（大文字小文字の区別やアクセント記号の扱いなど）をどのように解釈するかを定義します。
      具体的には、collation-serverパラメータは、新しく作成されるテーブルとそのカラムに適用されるデフォルトのコレーションを指定します。テーブルやカラムに個別にコレーションを指定することも可能ですが、何も指定しない場合はこのcollation-serverの設定が適用されます。
      この設定は、文字列データの検索やソートが予想通りの順序で行われることを確認するために重要です。適切なコレーションを設定しないと、特定の文字列の比較結果が不正確になる可能性があります。
      一般的なコレーションの例としてはlatin1_swedish_ciやutf8_general_ciなどがあります。
      ciはCase Insensitive（大文字小文字を区別しない）を表し、大文字小文字の違いを無視した比較が行われます。一方、csが付与されたコレーションはCase Sensitive（大文字小文字を区別する）を示します。
      • 例:
        • latin1_swedish_ci: Latin1文字セットでスウェーデンの順序規則を使用して、大文字小文字を区別しない比較を行う
        • utf8_general_ci: UTF8文字セットで一般的な順序規則を使用して、大文字小文字を区別しない比較を行う
        • utf8_bin: UTF8文字セットでバイナリベースの順序規則を使用して、大文字小文字を区別する比較を行う

  • collation_database

    • 変更不可
    • globalパラメータとしての利用は、5.7及び8.0ではdeprecatedなパラメータ
    • Amazon RDSのMySQLやMariaDBなどの設定の一つで、新しく作成されるデータベースに使用されるデフォルトのコレーション（文字の比較規則）を設定します。
      コレーションは、特定の文字セットに従って文字の順序付けと比較方法を決定します。これには大文字／小文字の区別やアクセント記号の処理などが含まれます。
      collation_databaseは特に、データベースレベルでのデフォルトコレーションを指定するための設定です。この設定は新しく作成されるすべてのテーブルに対して適用されますが、各テーブルやカラムで個別のコレーション設定を指定することも可能です。
      適切なコレーションが設定されていれば、データを正確にソートし、検索結果を期待通りの順序で表示できます。逆に、設定が不適切な場合は、文字列の比較結果が誤ったり、期待される結果を返さない可能性があります。

  • collation_connection

    • Amazon RDSのMySQLやMariaDBなどの設定の一つで、クライアントとサーバ間の接続で用いるコレーション（文字列の比較規則）を設定します。
      具体的には、この設定はクライアントからサーバへ送られたSQLステートメント内の文字列リテラルがどのように比較・ソートされるかを指定します。
      コレーション設定によって、大文字と小文字の区別やアクセントの扱い、特殊文字の並び等、文字列の比較とソートの動作が決まります。

dbの照合順序を確認する方法

  • 現在設定されている文字コードと照合順序を確認したい
select * from information_schema.schemata; ----- スキーマ
select * from information_schema.tables where table_schema = スキーマ名; ----- テーブル
select * from information_schema.columns where table_schema = スキーマ名 and table_name = テーブル名; ----- カラム
show create schema(database) スキーマ名; ----- スキーマ
show create table スキーマ名.テーブル名; ----- テーブル情報全体
  • 現在のセッションで有効な文字コードのパラメータを確認したい
show variables like '%character_set\_%';
  • 現在のセッションで有効な照合順序のパラメータを確認したい
show variables like '%collation\_%'
show variables like "default_collation_%";
  • データベース側のパラメータを確認したい
show global variables like %hogehoge\_%

これは、select @@global.パラメータ名がglobal variables、select @@パラメータ名がvariablesの照会結果と等価

例えば以下のよう

SELECT @@character_set_database, @@collation_database;
SELECT @@default_collation_for_utf8mb4;
  • テーブル単位
show table status from データベース名 ;
  • カラム単位
use information_schema;
select table_name, column_name, collation_name from columns  where table_schema="データベース名" and collation_name is not null

use データベース名
ALTER TABLE テーブル名 MODIFY COLUMN カラム名 varchar (255) COLLATE 'utf8_general_ci';

or

show full columns from テーブル名
  • 利用できる文字コード一覧が知りたい
SHOW CHARACTER SET
  • 利用できる照合順序一覧が知りたい
SHOW COLLATION
  • スキーマの照合順序を変更したい
----- 照合順序のみ
alter database スキーマ名 collate utf8mb4_general_ci;
----- 文字コードも
alter database スキーマ名 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
  • テーブルの照合順序を変更したい
----- 照合順序のみ
alter table テーブル名 COLLATE utf8mb4_general_ci;
----- 文字コードも
alter table テーブル名 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
  • カラムの照合順序を変更したい
----- 照合順序のみ
alter table テーブル名 MODIFY COLUMN カラム名 カラム定義 COLLATE utf8mb4_general_ci;
----- 文字コードも
alter table テーブル名 MODIFY COLUMN カラム名 カラム定義 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

utf8mb4のデフォルトのutf8mb4が変更されている

以下を見た方が早い
https://techblog.hacomono.jp/entry/2023/12/20/070000

簡単にまとめると
今後mysql8.0以降 string型のカラムを含むテーブルを定義する際に、文字コードをutf8mb4を明示的に指定した場合collationの設定を明示的にmigrationファイルに含む必要があります

理由としては、

  • MySQL8においてのcollationは、以下の優先度で決まる

逆に言うとrailsなどのマイグレーションファイルで、文字コードを指定しなければ、collation_databaseの値がcollationとして適応されます(通常railsの場合collationは指定しないので、基本はcollation_databaseの値となる)
collation_databaseの値は基本変更できません

検証

前提として、設定は以下の通りです。

グローバルレベルのcharset… utf8mb4
グローバルレベルのcollation… utf8mb4_bin

mysql> show global variables like 'collation_server';
+------------------+-------------+
| Variable_name    | Value       |
+------------------+-------------+
| collation_server | utf8mb4_bin |
+------------------+-------------+

データベースレベルのcharset… utf8mb4
データベースレベルのcollation(collation_database)… utf8mb4_unicode_ci

mysql> CREATE DATABASE `db` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
1 row in set (0.00 sec)

↑によりdbのデータベースではcollation_databaseがutf8mb4_unicode_ciで設定されるs
dbデータベース内に以下の3つタイプのCREATE TABLEを実行すると、それぞれcollationには何が設定されるでしょうか。

CREATE TABLE t1 (col1 varchar(10));
CREATE TABLE t2 (col1 varchar(10)) DEFAULT CHARSET=utf8mb4;
CREATE TABLE t3 (col1 varchar(10)) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
結果は以下の通りです。

Table	collation
t1	utf8mb4_unicode_ci
t2	utf8mb4_0900_ai_ci
t3	utf8mb4_general_ci

t1については、データベースレベルのcolationが引き継がれています。t3については、テーブルレベルで指定しているため希望通りの結果です。ところが、t2はutf8mb4_0900_ai_ciという、どこにも設定していないcollationが登録されました。これはMySQL 8.0のutf8mb4のデフォルトcollationになります。

つまり、CREATE TABLE文にDEFAULT CHARSET=utf8mb4のみ記述すると、collation_serverシステム変数に設定されているサーバーのデフォルトcollationでもデータベースレベルのcollationでもない、MySQLのデフォルトcollationが設定されてしまうのです。CREATE TABLE文を実行するときは予期せぬトラブルを避けるため、DEFAULT CHARSET=xx COLLATE=xxを省略せずに記述するのが良いでしょう。

また、MySQLのデフォルトcollationを確認するにはSHOW COLLATION文を実行します。DefaultカラムがYesになっているものが、MySQLのデフォルトcollationになります。

ちなみに、CREATE DATABASE文についても同様の動きをします。DEFAULT CHARACTER SET xx COLLATE xxを省略すると、グローバルレベルの設定が引き継がれます。

CREATE DATABASE db1 ;
CREATE DATABASE db2 DEFAULT CHARACTER SET utf8mb4;
CREATE DATABASE db3 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

結果は以下の通りです。

Database	collation
db1	utf8mb4_bin
db2	utf8mb4_0900_ai_ci
db3	utf8mb4_general_ci

railsで考えると
① CHARSETとCOLLATIONの指定がないので、databaseのcollation_database > collation_serverの優先順位でcollationが利用される(通常の書き方)

create_table "book_tables", force: :cascade do |t|
    t.string   "title"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
end

② CHARSET=utf8mb4のみの指定なので、default_collation_for_utf8mb4のcollationが利用される

create_table "book_tables", force: :cascade, options: "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4" do |t|
    t.string   "title"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
end

③ CHARSET=utf8mb4 COLLATE=utf8mb4_binなので、指定しているCOLLATE　がcollationとして利用される

options: "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin"
    t.string   "title"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
end

https://gihyo.jp/dev/serial/01/mysql-road-construction-news/0157

ユーザー権限まわりの書き方が変わっている

mysql5.7までは以下のようにユーザーの生成とパスワード、権限の設定を同時に行なっていた

GRANT ALL ON *.* TO 'ユーザー名'@'%' IDENTIFIED BY 'パスワード';

しかし、mysql8.0からはこのやり方ができないので以下のようにする必要がある

CREATE USER 'user_name'@'%' IDENTIFIED BY 'password';
GRANT ALL ON *.* TO 'user_name'@'%';

https://dev.mysql.com/doc/refman/8.0/ja/creating-accounts.html
https://qiita.com/namari/items/53c462840b802f10a0cd
https://urashita.com/archives/35970

開発環境の変更

① docker-composeなどでmysqlを起動している場合は以下のようにcollation-serverなどを指定する必要がある

db:
    image: mysql:8.0.28
    command: mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci

② utf8はutf8mb3のエイリアスなのでmysql8.0にするとスキーマが全てutf8からutf8mb3に変わる
https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8.html

③ mysql8.0に上げるとboolean型でかつunsigned: trueがついているカラムがinteger型にスキーマが変わってしまうバグがある、今までfalseを期待していた箇所に0が入るようになり、unlessなどで条件分岐しているロジックはロジックが壊れる

https://bugs.mysql.com/bug.php?id=100309

④ group byので暗黙的ソートを行っている箇所は、ソート順が変わり, テストが落ちるようになる

https://dev.mysql.com/blog-archive/removal-of-implicit-and-explicit-sorting-for-group-by/

⑤ seedデータなどでLOAD DATA LOCAL INFILEを使ってデータ作成している箇所は、mysql8.0からはサーバーとクライアント両方でlocal-infileを許可しないとseed実行時にエラーになる

参考 https://dev.mysql.com/doc/refman/8.0/ja/load-data-local-security.html

参考

https://yoneyore.hatenablog.com/entry/2020/11/26/232128
https://qiita.com/TakenoriHirao/items/8bd6ec487f5e7320a263
https://superhahnah.com/mysql8-collation/
https://blog.cybozu.io/entry/2021/05/24/175000#utf8mb4-の照合順序のデフォルト値の変更

Discussion