１. はじめに

この記事を目にかけてくださりありがとうございます。株式会社SHIFT サービスプラットフォームグループ 村上です。この記事では、「10年間、1600顧客のテスト活動を支えるプロダクト」CATをMySQL8.0に移行した話をご紹介します。

CAT (Computer Aided Test) は、弊社のソフトウェア品質保証事業を長らく支えてきました。Gitリポジトリの履歴を追いかけると、初コミットは 2012-10-27 とあります。当時はSubversionだったはずです。登録された環境も、過去にさかのぼると2013年5月という記録が出ています。

2015年に製品化され皆様のお手元に届けられるようになりました。 CAT は、お客様や社内のニーズに応えつつ成長を続けています。

現在皆様には、クラウド版（AWS）とダウンロード版の2種類を提供しています。

• クラウド版（AWS）：社外向け、社内向け、旧フリー版

• ダウンロード版：CAT Installer

クラウド版の方が利用者は多いものの、テスト管理ツールをインターネットに接続したくないニーズがあり、ダウンロード版も重要です。

テストビジネスを止めるわけにはいきません。CATは、取得したテストデータをその場で集計してサマリ化できるのが、メリットの一つです。メンテナンス時には、深夜と土日には停止しますが、それ以外は無停止で運用しています。

本記事では、2023年夏～秋に実施した、MySQL 8.0への移行についてご紹介いたします。MySQL 5.7のEOLが2023年10月に迫る中、MySQL 8.0への移行を実施した話になります。

現在もMySQL 5.7で運用されていて、アップデートしなければ、、、という皆様も多くいらっしゃるかと思います。そのような皆様への参考になれば幸いです。

2. 移行の流れ：検討

2-1. 検討：MySQL本体の移行方式

データベースの移行には、主に dump/restore 方式と inline 方式が挙げられます。概要やメリット・デメリットをまとめました。

■ dump/restore方式

■ inline方式

■ inline方式を採用した

inline移行の問題として、エラー停止するとデータを壊す可能性があります。

ここでは、AWSのスナップショット機能を活用します。インスタンスやボリュームのスナップショットを何度も取って何度も試せます。

今回の移行では、inline方式を採用しました。

2-2. 検討：アプリの修正

データベースはアプリの根幹をなすミドルウェアです。アップデートするとアプリ全体に多大な影響を及ぼします。

MySQLには移行に関する資料が豊富にありますので、事前にバグの発生可能性を調査し、網羅的に修正することが望ましいです。

2-3. 検討：アプリの修正：アップデートチェッカーユーティリティ

MySQLでは、アップデートチェッカーユーティリティが提供されています。5.7 → 8.0 に限らず、MySQL アップデートの際に発生しうる問題を検査し、提示してくれます。

今回の移行ではこのツールが大いに役に立ちました。

アップデートチェッカーユーティリティが提示した問題は、主に下記の3点でした。

• 新しく加わった予約語row

• デフォルトの文字セット変更 utf8mb3 → utf8mb4

• 古いバージョンのデータスキーマの残骸：旧式 datetime

各々にどう対処したかは、次節「3. 問題への対処」で示します。

2-4. 検討：ライブラリのアップデート

MySQLを8.0にアップデートすることで、それに関連するライブラリのアップデートを検討します。

• 必須：JDBCドライバ

• 要確認：データベースミドルウェア（Hibernate, MyBatis 等）
  MySQL 8.0 に対応しているかを確認し、必要ならばアップデート

MySQL移行時にはテストを入念に実施する必要があるので、普段の運用でアップデートを躊躇しているライブラリが他にもあれば、ここでのアップデートは検討に値します。

但し、エラー発生時に原因調査が困難にならないよう、注意が必要です

3. 移行の流れ：問題への対処

検討して判明した問題には、事前に対処しておきます。これによりinline updateの作業をスムーズに実施できます。

3-1. 新しく加わった予約語row

MySQL 8.0より、単語rowが予約語となりました。よって、SQL内でそのまま使うことができません。

CATでは、rowをテーブルの列名として使っていたので、これが厄介な問題となりました。

SQL内のテーブルの列名として使っているrowを、バッククオート `` で囲う必要があるのですが、

• SQL が書かれている箇所はモジュールやリソースにまとめられているものの、網羅的に洗い出すのは容易ではない

• rowが、Javaコード内に含まれている場合がある

• ResultSetの列指定では `` で囲ってはならない

という状況が発生していました。

下記のようなスクリプトを書き、対象となりうるファイルを事前に洗い出すことで、過不足なく対応できました。

find . -type d \( -name build -o -name catExt -o -name catExt2 -o -path ./.git \) \
         -prune -o \( -type f  \) -print0 | while IFS= read -r -d '' F ; do
  echo $F | grep -qP \
    "(\.(css|scssjs_|js|class|svg|jar|pkg|exe|zip|png|jpg|map|bin)|phantomjs|swagger)$"
  if [ $? -eq 0 ] ; then
    continue
  fi
#  grep -wiln row "$F"
  grep -wHin row "$F"
done

3-2. デフォルトの文字セット変更 utf8mb3 → utf8mb4

MySQL 8.0より、デフォルトの文字セットがutf8mb3からutf8mb4に変わりました。utf8mb4に対応すると、4バイトのUTF-8文字を扱えるようになります。

Unicodeの全ての文字を扱えるようになり、いわゆる絵文字や特殊文字など、扱える文字の種類が格段に増えます。

変更が必要になる箇所は、下記のとおりです。

テーブルの変更

すべてのテーブルに対して、下記のSQLを発行します。なお ${TABLE_NAME} はテーブル名です。

ALTER TABLE ${TABLE_NAME} CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_ja_0900_as_cs_ks;

このSQLは、テーブルとカラムの文字セットを変換します。テーブルの再作成を伴うことがあり、テーブルのサイズによっては時間がかかるので注意が必要です。

また、Indexも再作成します。これに伴い外部キー制約に不整合が生じ、変換に失敗する場合があります。その際は、Index削除 → テーブル変換 → Index再作成の手順を踏む必要があります。

COLLATEは、文字セットの照合順序を示します。大文字小文字の区別有無、かなカナの区別有無などを制御できます。[MySQL 8.0のCharset utf8mb4での日本語環境で使うCollationで文字比較をしてみる] の記事に詳しいです。COLLATEは、MySQL8.0以前と以降でデフォルト値が異なるなど、複雑な状況がありますので、明示的に指定するのをお勧めします。

DATABASEの変更

すべての DATABASE に対して、下記のSQLを発行します。なお ${DATABASE_NAME} はデータベース名です。

ALTER DATABASE ${DATABASE_NAME} CHARACTER SET utf8mb4 COLLATE utf8mb4_ja_0900_as_cs_ks;

my.cnfの変更

MySQL のデフォルト文字セットは utf8mb4 です。

my.cnfでcharacter-set-serverやdefault-character-setを指定している場合はコメントアウトするか、明示的にutf8mb4を指定します。

また、collation-server は明示的に指定するのをお勧めします。

[mysqld]
character-set-server = utf8mb4  # なしでもOK
collation-server = utf8mb4_ja_0900_as_cs_ks

[client]
default-character-set = utf8mb4  # なしでもOK

[mysql]
default-character-set = utf8mb4  # なしでもOK

3-3. 古いバージョンのデータスキーマの残骸：旧式 datetime

MySQLの日付や時刻を扱う型（date, datetime, timestamp）で、MySQL 5.7.8より、ゼロ値（0000-00-00 00:00:00）を扱えなくなりました。

以前のアップデートに何らか失敗して、レコード内の値やデフォルト値設定にて、この残骸が残るケースがあるようです。

今回の移行では、開発用環境にこのような残骸が残っていました。アップデートチェッカーユーティリティが指摘した内容について、これを機に修正しました。

3-4. ドライバの問題

MySQLの移行に伴って、MySQL用のJDBC driverも移行が必要です（5.1→8.0）。

今回、driver側の内部動作に頼った実装が１箇所あり、この内部動作が変わったことから、わかりにくいバグを引き起こしていました。

内部動作の変更は、通常リリースノートに記載されることはありません。

3-5. exist 句の実行計画変更

MySQL8.0.16以降では、exist句の使用でセミジョインが動作することとなりました。

これは多くの場合、性能向上につながります。しかし、このようなクエリ実行計画の変更が障害を引き起こす可能性もあります。

CATでは、一部のクエリの性能が劇的に低下したため、下記の対処を行いました。

• 各DBに「SET GLOBAL optimizer_switch='semijoin=off'」を実行して、問題を回避

• my.cnfに「optimizer_switch='semijoin=off'」行を追加して、ひとまず運用化

そののちに、該当クエリのシンプル化を実施しました。機を見て「'semijoin=on'」に戻す予定です。

3-6. 該当するならば厄介な問題たち

CATでは幸いにも該当しなかったものの、該当するならば修正に手間がかかるであろう問題を、いくつか挙げておきます。

■ 外部キー制約をもつテーブルの DDL 操作によるメタデータロックの発生

これは、MySQL8.0で行われた仕様変更です。

テーブル操作時の整合性を保つための適切な仕様変更ですが、ロック発生により問題が発生しえます。

[ 外部キー制約をもつテーブルの DDL 操作によるメタデータロックの発生 ] などを参考に、該当する可能性について調査する必要があります。

■ データソート時のメモリ不足に起因するエラー

データソートの際のソートキーにindexを設定していない場合、従来はソートキーをソートしてから必要な列を読み込んでいたものを、MySQL8.0.20より、必要列を１度ですべて読み込むことになりました。

これは性能向上につながるが、ソート時のメモリ不足を引き起こす可能性があります。

大容量データをMySQLにソートしている場合は、[ データソート時のメモリ不足に起因するエラー ] などを参考に、対策が必要になります。

4. 移行の流れ：作業

問題への対処方針が出そろい、各々に対処したところで、アップデートを実際にかけます。ここでは

• アップデート試行用の環境を構築し、スナップショットを活用して何度も試行します

• 試行する中で、本番環境に適用する移行方式の手順書を書きます （できるだけクリーンに移行したい）

4-1. 作業：アップデートの実施とテスト

ここまで準備をしておけば、MySQL5.7 → 8.0のアップデート作業そのものはシンプルです。

RHEL/CentOS 7.9 におけるアップデート作業の一例を示しておきます（古いCentOS も上げなきゃ...）

################################################################
# /etc/my.cnf 修正
・コメントアウト
## character-set-server = utf8          ## for MySQL 8.0 ##
## collation-server = utf8_general_ci ## for MySQL 8.0 ##


################################################################
# データの一部が壊れている場合、アップデートに失敗することがある。
# そこで、事前に修復作業かけておくのが望ましい。
#    sudo mysql_upgrade -uroot -p --force
#    sudo mysqlcheck -uroot -p --all-databases --check-upgrade --auto-repair


################################################################
# バージョン確認
cat /etc/redhat-release ; rpm -qa | grep -i mysql80
##  CentOS Linux release 7.9.XXXX
##  mysql80-community-release-el7-3.noarch
# 古いパッケージが入っている場合は消す（上記の例だと、7.3 用のパッケージが入っていた）
sudo yum remove mysql80-community-*

################################################################
# GPG 鍵を更新し、mysql8.0 のリポジトリを有効化
sudo rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022
sudo yum install https://dev.mysql.com/get/mysql80-community-release-el7-9.noarch.rpm
sudo yum-config-manager --disable mysql57-community
sudo yum-config-manager --enable mysql80-community
sudo yum repolist enabled | grep -i mysql

################################################################
# アップデート実行
sudo systemctl stop mysqld
sudo yum update mysql-community-server
sudo yum-config-manager --disable mysql80-community
date ; sudo systemctl start mysqld ; date  # ここでデータの変換が発生し、時間かかる場合がある

4-2. 作業：replication構成時のアップデート方法

MySQLのReplicationを有効化している場合は、source/replicaのアップデート順序を検討する必要があります。

いずれにせよ、作業前に「show slave status」コマンドを実施して、replicaが健全かつ同期済みであることを確認しておく必要があります。

やり方は３通り考えられます。

• source/replica 同時にアップデート

• replica を先にアップデート、実施後にsourceをアップデート

• source をアップデートし、replicationは再構成する

なお、3方式あったbinlog_formatが8.0.34より非推奨となり、将来的に廃止されることが発表されました。

これを機に、binlog_format＝‘ROW’ に設定しておくのが良さそうです。

source/replicaのロギング形式を合わせる必要があることを考えると、binlog_format＝‘ROW’ でない場合は、replicationは再構成するのが無難です。

再構成なしの変更も可能ですが、source/replicaの状態遷移を考えて慎重な作業が必要になります。

5. まとめ

CATは10年にわたり運用を続け、ありがたいことに、社外のお客様からも一定の評価を頂いております。

• 株式会社日立製作所 公共システム事業部 導入事例【前編】

• 株式会社日立製作所 公共システム事業部 導入事例【後編】

このようなプロダクトのデータベース大型アップデート、無事に終えることができてほっとしております。

MySQL 8.0移行後も、大きな問題は発生せず、運用に大きな影響なくCATは平穏に稼働しています（個別の問題はございまして、ご迷惑おかけした皆様には申し訳ありません）。

アップデート後に発生する問題を事前に見通して手を打ったことと、テストを複数回実施できたのが功を奏したと考えています。

CATは、SHIFT ASIAと協力して開発しています。テスト過程でのバグ出しでは大いに貢献していただきました。

また、アップデートにより、古くから内在していたバグを検出し、解決できたことも、今回の成果と言えます。

お問合せはお気軽に

SHIFTについて（コーポレートサイト）
https://www.shiftinc.jp/

SHIFTのサービスについて（サービスサイト）
https://service.shiftinc.jp/

SHIFTの導入事例
https://service.shiftinc.jp/case/

お役立ち資料はこちら
https://service.shiftinc.jp/resources/

SHIFTの採用情報はこちら

PHOTO：UnsplashのLuca Bravo