Amazon RDS から TiDB 移行時のしくじり集
はじめに
先日、レバテックで最も歴史のあるDBでかつ最も容量が大きいDBをTiDBに移行しました!
Xの投稿でもある通り、無事だったかというとそうではないので、その理由をまとめたいと思います!
前提条件
- 移行元DB
- Amazon RDS
- エンジン:MySQL Community
- エンジンバージョン:8.0.33
- グローバルシステム変数(影響があった部分をピックアップ)
@@global.time_zone = Asia/Tokyo
@@GLOBAL.sql_mode = NO_ENGINE_SUBSTITUTION
- Amazon RDS
- 移行方法
- AWS Database Migration Serviceを利用
- 雑な選定理由
- Terraformで実装できる
- 移行時にスキーマ名を変更できる
- 移行タイプ
- フルロード、継続的なレプリケーション
- 雑な選定理由
- AWS Database Migration Serviceを利用
しくじり集
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 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で連携が実行されてエラーとなっていた。
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にするのを断念した
- (諦めずに立ち向かうべきだった)
- 移行元DB(RDS)の
- MySQLにおいて、データを守るという観点でも厳密モードにすべきだとわかった
- 上記のしくじり以外にもいくつか不具合が発生しました
-
delete_flag=0
だとsql_modeの違いによりエラーになるためdelete_flag=FALSE
に変更するなど
-
- '0000-00-00 00:00:00.000000'みたいなデータは確実に不正なデータであり、このデータを作成するのを許容してはいけないと思う
- 上記のしくじり以外にもいくつか不具合が発生しました
さいごに
今回のDB移行を経験して思いましたが、まだまだ経験や知識も足りないし、事前準備や検証段階での動作確認でやれることもたくさんあるとわかりました。
まだまだ、TiDB移行が必要なDBはたくさんあるので、次回のTiDB移行は完全試合を目指して頑張りたいと思います。
※ この不具合と戦ってくれているメンバーにめちゃ感謝です。
※ PingCAPの人たちも全力でサポートしてくれました。めちゃ感謝です。
Discussion