はじめに
MSPチームのまっちゃんです。
RDS for MySQL 5.7から8.0へのアップグレードを行う際、ゼロ値の日付（DATE）、日時（DATETIME）、タイムスタンプ（TIMESTAMP）の値に関するエラーが発生することがあります。
MySQL 8.0では、これらの値のデフォルト値に対するチェックが厳格化されました。
本記事では、ゼロ値の日付（DATE）、日時（DATETIME）、タイムスタンプ（TIMESTAMP）の値に関するエラーが発生する原因と解決策について解説します。
原因
DATA_TYPEがゼロ値の日付（DATE）、日時（DATETIME）、タイムスタンプ（TIMESTAMP）がサポートされなくなりました。
0000-00-00 00:00:00のような文字列
エラー文
データベース名.テーブル名.カラム名 - column has zero default value: 0000-00-00
上記の場合は、対象のカラムのDATA_TYPE(DATE)が全て0の値となっていることからエラーとなっています。
解決策
- sql_mode設定に
NO_ZERO_IN_DATEおよびNO_ZERO_DATEモードを追加する。 - ゼロ値を含む日付（DATE）、日時（DATETIME）、タイムスタンプ（TIMESTAMP）のカラムを以下のどちらかで修正する。
①デフォルト値(CURRENT_TIMESTAMPなど)
②null値
今回は「①デフォルト値(CURRENT_TIMESTAMPなど)」の対応で記載します。
「②null値」で対応する際はサーバ内の設定変更にて、1970-01-01やCURRENT_TIMESTAMPの部分を「null」に変更してください。
date型とdatetime型・timestamp型で修正内容が異なります。
- date型
※変更後を例として、1970-01-01としておりますが、適宜ご自身が対応する値に変更をしてください。
0000-00-00 → 1970-01-01
- datetime型・timestamp型
※変更後を例として、CURRENT_TIMESTAMPとしておりますが、適宜ご自身が対応する値に変更をしてください。
0000-00-00 00:00:00 → CURRENT_TIMESTAMP
解決方法
- パラメータの設定変更
まずは、RDSのパラメータの設定を変更します。
1.RDSにて対象のパラメータグループを開いて、「編集」を押下
2.検索窓にsql_modeと入力
3.値の欄にNO_ZERO_DATEとNO_ZERO_IN_DATEを入力して「変更を保存」を押下
- サーバー内にて設定変更
続いて、サーバ内の設定を変更します。
1.対象のデータベースを選択
USE [datebase_name];
※[datebase_name]は該当のデータベース名に変更して実施をしてください。
2.選択されていることを確認
SELECT database();
3.date型の修正
ALTER TABLE `[table_name]` CHANGE `[column_name]` `[column_name]` DATE NOT NULL DEFAULT '1970-01-01';
※[table_name]は該当のデータベース名に変更して実施をしてください。
※[column_name]は該当のデータベース名に変更して実施をしてください。
また、同じ[column_name]を入力してください。
4.datetime型・timestamp型
ALTER TABLE `[table_name]` CHANGE `[column_name1]` `[column_name1]` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;
※[table_name]は該当のデータベース名に変更して実施をしてください。
※[column_name]は該当のデータベース名に変更して実施をしてください。
また、同じ[column_name]を入力してください。
2つ以上修正する際には、以下の文を追記し、適宜[column_name]を変更することで複数修正できます！
, CHANGE `[column_name2]` `[column_name2]` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
まとめ
一つひとつのカラムに対して対応するのは時間を要します。
変更箇所が多い場合には、phpmyadminなどのツールを利用したり、ストアドプロシージャを組んだりして対応することで効率よくエラー解消ができるかと思います。
私は初めての対応であったためストアドプロシージャを組むことができませんでした。
次回、対応する機会があった際にはストアドプロシージャにも挑戦していきたいと思います！
簡単30秒