Help us understand the problem. What is going on with this article?

ERROR 1067 (42000): Invalid default value for 'created_at'

More than 1 year has passed since last update.

問題

  SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default val
  ue for 'updated_at' (SQL: ALTER TABLE notices CHANGE created_at created_at
  timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP;)
SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default val
  ue for 'updated_at'

対策

/etc/my.cnfを変更

[mysqld]
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

※SET GLOBAL sql_mode = 'xxxx' でも変更可能だが、再起動するとシステム変数が戻ってしまうので、設定ファイルから変更

原因

 0000-00-00 00:00:00 を許容しないため?

背景

タイムスタンプのデフォルト値に実行日付を設定しようとしたら、
マイグレーションが完了しない。

環境

mysql 5.7.20
EC2 Linux (x86_64)

◆参考

https://stackoverflow.com/questions/36882149/error-1067-42000-invalid-default-value-for-created-at

システム変数の表示と変更
http://www.ksknet.net/mysql/post_102.html

harukingo
2013~2016 新卒でSIer 2017~   社内コンテンツを作るWebエンジニアに転職。勉強中
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away