An update like this running from SQL client such as SQLPlus works fine:

alter table `questions` add constraint `questions_question_display_format_id_foreign` foreign key (`question_display_format_id`) references `question_display_formats` (`id`);

However while the migration is running from Laravel during deployment, the following error has been observed:

SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '0  
  000-00-00 00:00:00' for column 'updated_at' at row 6446 (SQL: alter table `  
  questions` add constraint `questions_question_display_format_id_foreign` fo  
  reign key (`question_display_format_id`) references `question_display_forma  
  ts` (`id`)) 

I would like to understand the cause, and how to avoid such as surprise when doing a deployment. This example works after mass updating entries to not have created_at and updated_at columns with zeroes in them - then it worked.

Thanks.

CodePudding user response：

A quicker solution is to disable "strict" mode on your database connection.

Strict mode controls how MySQL handles invalid or missing values in data-change statements such as INSERT or UPDATE.

you can do this by opening your config/database.php file, and make sure your database connection shows 'strict' => false.

But, The ideal solution would be to fix all the data in the database. That is, go through your database and update your datetime/timestamp fields so that they are nullable, and convert their data from '0000-00-00 00:00:00' to NULL. You can also do this by modifying your migrations.