🥲

Amazon RDS から TiDB 移行時のしくじり集

かわうそ

2024/07/25に公開

MySQL

RDS

Database Migration Service

TiDB

しくじり

tech

はじめに

先日、レバテックで最も歴史のあるDBでかつ最も容量が大きいDBをTiDBに移行しました！

Xの投稿でもある通り、無事だったかというとそうではないので、その理由をまとめたいと思います！

前提条件

移行元DB
- Amazon RDS
  - エンジン：MySQL Community
  - エンジンバージョン：8.0.33
- グローバルシステム変数（影響があった部分をピックアップ）
  - @@global.time_zone = Asia/Tokyo
  - @@GLOBAL.sql_mode = NO_ENGINE_SUBSTITUTION
移行方法
- AWS Database Migration Serviceを利用
  - 雑な選定理由
    - Terraformで実装できる
    - 移行時にスキーマ名を変更できる
  - 移行タイプ
    - フルロード、継続的なレプリケーション

しくじり集

TiDBのAUTO INCREMENTのキャッシュが更新されない

事象

AWS DMSによるデータ移行の同期が完了後、対象システムの接続先をTiDBに切り替えたが、各システムでDuplicate Key Errorが発生した。

具体例

TiDB Aのauto_inc_idは190065
TiDB Bのauto_inc_idは220045
データは190271まで存在
TiDB Aに接続したアプリケーションにてDuplicate Key Errorが発生

原因

TiDBのAUTO_INCREMENTはそれぞれのノードごとに採番帯を持ち、デフォルトでは30000となっている。([1,30000],[30001,60000])
INSERTが行われる際、AUTO_INCREMENTの採番列に値を指定した場合はそのままINSERTが完了するが、この値が採番帯に含まれる場合は、採番帯の下限が更新され、INSERTされた値+1を下限とするようにする。

ただし、この挙動はINSERTが行われたTiDBノードのキャッシュのみであるため、INSERTが行われなかったノードの採番帯はそのままとなる。

今回の事象はDMSで定期的にINSERT（LOAD DATA）が行われたノードと、移行時にアプリケーションがアクセスしたノードが異なったために発生したものと考える。

具体的には、上記で記載したTiDB B側でINSERTが行われ、190271までの番号を持つデータをロードしたが、TiDB A側の採番キャッシュは更新されず、190065のままとなっていたものと思われる。

対処

ALTER TABLE <table> AUTO_INCREMENT = ? にて、AUTO_INCREMENTの採番帯キャッシュをクリアし、番号を飛ばすことを行った。
番号は現在採番している番号より大きければ何でも良いが、安全に行くなら現行採番帯の最大値より大きくすることを推奨しており、現在採番している番号より(+1)大きくする方針で進めた。

対策

移行前にALTER TABLE <table> AUTO_INCREMENT = ?を実装して採番キャッシュをリセットする
AWS DMS実行前のCREATE TABLE時に、移行が完了するまでのレコード量を想定して、それを上回るAUTO_INCREMENTを設定する

BLOB型のデータが連携されていない

事象

AWS DMSによるデータ移行の同期が完了したが、BLOB型のカラムになっているデータが32KiBまでしかデータが入っていない。

原因

AWS DMSのデータベース移行タスクの設定において、LimitedSizeLobModeをTrueにしており、LobMaxSizeがデフォルト値である32（KiB）のままになっていた。

これにより、32KiBを超えるデータのカラムは切り捨てられて連携されていた。

対処

移行元DB（RDS）から対象のテーブルのDumpファイルを取得
- Dumpファイルの作成時にBlob→Hexに変換
- SQLのテーブル名を〇〇_tmpに置換
移行先DB（TiDB）にDumpファイルを取り込む
対象のテーブルにおけるをBLOB型のカラムをUPDATE文で更新する

UPDATE文の例

UPDATE original_table o
JOIN temp_table t ON o.id = t.id
SET o.column_to_update = t.column_to_update;

対策

今回と同様に制限付きLOBモードで行う場合、移行タスクを実行する前に移行元DB（RDS）におけるデータのLobMaxSizeを特定して設定する
完全LOBモードもしくはインラインLOBモードでタスクを実行する

NO_ZERO_DATEの影響で移行できていないデータが存在

事象

AWS DMSによるデータ移行の同期が完了したが、連携されていないデータが存在した。

具体例

awsdms_apply_exceptionsにINSERTやUPDATEに失敗しているテーブルが存在していた。

エラー例

Incorrect datetime value: '0000-00-00 00:00:00.000000' for column 'hogehoge'

原因

移行元DB（RDS）において厳密モードが無効となっており、NO_ZERO_DATEが有効となっておらず、無効な日付のデータやデフォルト値として'0000-00-00 00:00:00.000000'のようなデータのINSERTやUPDATEが可能となっていた。

これにより、移行元DBには'0000-00-00 00:00:00.000000'のようなデータが作成されており、AWS DMSによるデータ連携時に以下のようなSQLで連携が実行されてエラーとなっていた。

エラーとなるSQL

INSERT INTO `target_db`.`target_tables`(
    ...,
    'hogehoge',
    ...,
)
VALUES(
    ...,
    '0000-00-00 00:00:00',
    ...,
)

対処

awsdms_apply_exceptionsからINSERTやUPDATEがされていないデータを確認して、データリペアを実施。
また、データリペアを実施する際に、移行先DB（TiDB）に対してNO_ZERO_DATEを無効にして対応。

対策

AWS DMSのEndpoint SettingsにあるAfterConnectScriptにsql_modeを指定してNO_ZERO_IN_DATE,NO_ZERO_DATEを無効にする
AWS DMSによるデータ移行時のみ、TiDBのsql_modeからNO_ZERO_IN_DATE,NO_ZERO_DATEをグローバルスコープから外す
移行元のデータにおける‘0000-00-00 00:00:00.000000’をNULLに置き換えてからデータを移行

※ TiDB DMでは、なるべく元データをTiDBに漏れなく移行させるためにデフォルトのsql_modeよりも制限を緩めており、そのままNULLなどに変換されることはなく、初期移行、差分連携どちらにおいても移行が可能。

データ移行後のTIMESTAMP型のデータが9時間ずれてしまう

事象

AWS DMSによるデータ移行の同期が完了したが、TIMESTAMP型のデータがすべて9時間ずれてしまっている。

具体例

移行元DB（RDS）のデータ
- 2024-07-24 09:00:00
移行先DB（TiDB）のデータ
- 2024-07-24 00:00:00

原因

移行元DB（RDS）の@@global.time_zoneがAsia/Tokyoとなっていたため、移行先DB（TiDB）も同様に@@global.time_zoneがAsia/Tokyoを変更していた。

また、MySQLはTIMESTAMP型の列はUTCとして保存しており、現在のタイムゾーンからUTCに変換して保存する。また、取得時にはTIMESTAMP型の値を現在のセッションのタイムゾーンに変換する。

そのため、以下のような変換が発生していた。

対象	タイムゾーン	事象	例
移行元DB（RDS）	Asia/Tokyo	----	2024-07-24 09:00:00
DMS	UTC	Asia/Tokyoの値をUTCとして扱ってしまった（Asia/Tokyoの値をUTCの値としてキャプチャ）	2024-07-24 09:00:00
移行先DB（TiDB）	Asia/Tokyo	Asia/Tokyoの値をUTCとして扱ったまま保存（UTCとして保存するため-9時間）	2024-07-24 00:00:00

対処

移行先DB（TiDB）のTIMESTAMP型に該当するカラムのデータをすべて+9時間にする対応を行った。
ただし、すべてのデータではなくAWS DMSのフルロードで連携されたデータのみが対象となった（ここが謎なポイント🤔）

対策

AWS DMSにおけるソースエンドポイントのserverTimezoneを設定する
- これによりDMS側で正しくタイムゾーンを読み取り、UTCに変換し連携ができる

謎なポイント🤔

今回はAWS DMSのフルロード、継続的なレプリケーションのモードで移行を行ったが、CDC（継続的なレプリケーション）で連携されたデータについては9時間ずれていなかった。正確に言うと、すべて-1秒ずれていた。

CDCは、binlogを読んで操作をリプレイするため、INSERTで指定された値がそのまま入るものだと考えていた。

原因の詳細は不明ですが、以下の仮説を立てています。

Now()の評価タイミングがINSERT時とbinlogで異なる？
TIMESTAMP WITH TIME ZONEが9桁未満の場合は末尾が0埋めされるから-1秒ずれる？

（もし、わかる方いらしたらコメントいただけると嬉しいです😂）

データ移行後に全角検索がヒットしなくなった

事象

AWS DMSによるデータ移行の同期が完了後、対象システムの接続先をTiDBに切り替えたが、検索の機能において全角検索がヒットしなくなった。

具体例

過去
- 姓[全角スペース]名で検索可能
- 姓[半角スペース]名で検索可能
現在
- 姓[半角スペース]名で検索可能

原因

スキーマの移行時にAWS DMSによって適用されるDDLをそのまま反映してしまい、
Collationがutf8mb3_unicode_ciとなっているテーブル・カラムに対してutf8_unicode_ciに変更してしまった。

対処

対象テーブル・カラムのCollationをutf8mb4_unicode_ciに変更

対策

AWS DMSによって適用されるDDLを利用せずに、移行元DB（RDS）から出力したDDLを利用する
- その際、移行先で利用できないCollationがある場合は確認して置換する

所感

AWS DMSにおける進行状況はあくまで進行状況であって成功率ではない
- （進行状況の100%を信じすぎた...）
上記を踏まえて差分チェックの仕組みを作っておくべきだった
- sync-diff-inspectorというTiDBに差分チェックできる仕組みもあったが、レバテックで最も歴史のあるDBのCollationに対応しておらず断念した
- （先にCollationを変更して差分チェックできるようにしておけばよかった）
やっぱりタイムゾーンはUTCが正義だと思う
- 移行元DB（RDS）の@@global.time_zoneがAsia/Tokyoだからといってそこに合わせなければよかった
- DBのタイムゾーンは、ORMだったりアプリケーションのロジックにも絡んでくるところもあり、UTCにするのを断念した
- （諦めずに立ち向かうべきだった）
MySQLにおいて、データを守るという観点でも厳密モードにすべきだとわかった
- 上記のしくじり以外にもいくつか不具合が発生しました
  - delete_flag=0だとsql_modeの違いによりエラーになるためdelete_flag=FALSEに変更するなど
- '0000-00-00 00:00:00.000000'みたいなデータは確実に不正なデータであり、このデータを作成するのを許容してはいけないと思う

さいごに

今回のDB移行を経験して思いましたが、まだまだ経験や知識も足りないし、事前準備や検証段階での動作確認でやれることもたくさんあるとわかりました。

まだまだ、TiDB移行が必要なDBはたくさんあるので、次回のTiDB移行は完全試合を目指して頑張りたいと思います。

※ この不具合と戦ってくれているメンバーにめちゃ感謝です。
※ PingCAPの人たちも全力でサポートしてくれました。めちゃ感謝です。

レバテック開発部

レバテック開発部の公式テックブログです！レバテック開発部 Advent Calendar 2024 実施中: qiita.com/advent-calendar/2024/levtech