Why does Laravel 8 migration's file fails SQL query which works in SQL client?

Question

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.

@MarcinOrlowski I thought I was clear about this - I ran the same update table command that it failed to execute in SQL client, and it ran without errors! Why do I have to update those extra columns for Laravel to run the migrations? — Carmageddon
– Carmageddon, Commented Oct 16, 2022 at 5:58

Hefaz · Accepted Answer · 2022-10-16 06:02:39Z

2

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.

answered Oct 16, 2022 at 6:02

Hefaz

6711 gold badge11 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Carmageddon Over a year ago

Thank you! interesting, is there a way to do this only for the migrations instead of enabling for the entire application?

Carmageddon Over a year ago

Preferably without doing like config()->set('database.connections.mysql.strict', false); specifically on every migration file (and actually hoping no one on the team ever forgets when schema is changed...)

Collectives™ on Stack Overflow

Why does Laravel 8 migration's file fails SQL query which works in SQL client?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related