MySQL 8.0 にしたら datetime カラムが truncated されて人生に悩んだ

背景

MySQL 8.0にしてしばらく、おかしいデータが有ることに気付いた。
INSERT文を見てみたら成功しているがwarningが出ている。失敗ではないのでエラーを上げておらず気づかなかった。

'Data truncated for column x at row 1'

よくあるのは、varchar(255) のカラムに 256文字以上のデータを突っ込んだときだ。この場合最初に255文字だけがinsertされて、後ろは切り捨てられる。
そう思って見ると、そのカラムは datetime 型だった。
datetimeでtruncatedってなんやろな。

Insertしようとしてるデータは 2020-10-02T12:34:56.000+0900 だった。ああ・・・。
MySQL 5.7の頃はそれでもtruncatedされて　2020-10-02 12:34:56 がinsertされていたが、目の前のMySQL 8.0では truncate された結果 0000-00-00 00:00:00 が入っていた。なるほど・・・

@@sql_mode

0000-00-00 00:00:00 はそのdatetime columnのデフォルト値だ。warningのためにそのfieldに当てたデータはすべて切り捨てられデフォルト値が入った。この動きはあれだ。sql_modeだ。移行前後の環境を比較した。

mysql 5.7

select @@sql_mode;
ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

mysql 8.0

select @@sql_mode;
'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'

STRICT_TRANS_TABLES が原因でいいと思うのだが、Manualを読んでも自分が経験した事象と合わなくてよくわからなかった。が、真面目に読み進めたらあっさり原因が書いていた。

If strict mode is enabled, '0000-00-00' is not permitted and inserts produce an error, unless IGNORE is given as well. For INSERT IGNORE and UPDATE IGNORE, '0000-00-00' is permitted and inserts produce a warning.
https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sql-mode-strict

strict mode というのは either STRICT_ALL_TABLES or STRICT_TRANS_TABLES is enabled のときだ。今回は STRICT_TRANS_TABLES が有効だったので strict mode is enabled だ。

その状態では本来 '0000-00-00' はエラーになるが、 INSERT IGNORE するとwarningは出るが成功する（値が入る）。これが原因だった。

解決法

STRICT_TRANS_TABLES を外すというのは最高最短の解決法だ。しかし一歩上を目指すならやっちゃいけない。いいエンジニアになるには遠回りする勇気が必要らしい。

1. INSERT文から IGNORE を外す。

とりあえず、で作ったコードにはIGNOREが入りまくっている。しかしいつの間にかそのサービスの運用は義務になり止まると怒られが発生される。いいエンジニアは最初からIGNOREを使わないでスパパっと完璧なコードが書けるものだ。そうなりたいものだ。ひとつずつでいいから IGNORE は消していこう。STRICT_TRANS_TABLES な時代には似合わないstatementだ。

2. truncateしない文字数のデータをinsertする

今回は Jira API から返ってきた date っぽい値をそのまんま INSERT していた。事前に必要な文字数だけに短くして INSERT した。

2020-10-02T12:34:56.000+0900 → 2020-10-02T12:34:56

最初はやっつけでよかったコードだ。APIをそのまま渡したら入った。そのまま運用した。この判断が悪かったのだろうか。これまで一年動き続けた価値はあると思っていたが。最近こういう結果がよかったのかどうか、よくわからない。その時の僕の開発スタイルはこうだ。API結果はできるだけ残したいので、必要そうな値をまとめてINSERT IGNOREで投げてみる。warningが出ようが、重要なカラムさえ入っていればok. あとは問題になってから直せばいい。そんなこんなで全部NULLとか0000なカラムが僕のテーブルにはよくある。それが僕をハメることもしばしばだ。最初からwarningを見ていれば今回の問題は防げたのだろう。しかしsmall startにそのコストが見合うのだろうか？　答えは、一発で完璧なことができるスーパーエンジニアになるしかないような気がする。

まとめ

型を意識するのがいいエンジニアの条件らしい。php/jsから入った自分にはその意識がまったくもって希薄だ。

warningもエラーと見なす運用体制が必要だ。まずは IGNORE を消していこう。がんばろう。

で、そもそもなんでこのIGNOREがあったかというと INSERT ON DUPLICATE KEY UPDATE 的な話があってそこはそこで改めて勉強し直さないとな、というところ

我々は何故2015年にもなってSELECTして無かったらINSERTする場合のベストパターンを確立できていないのか。

— songmu (@songmu) July 16, 2015

https://songmu.jp/riji/entry/2015-07-20-insert-ignore.html

おしまい