Laravel, MySQL, migrations and timestamps

I was working on a weird issue related to a timestamp field on an Entity.

I have an entity that has a timestamp field with the name date (super creative name, yes I know). The issue was that on updates on this entity, any update that did not modifies the date value, the date field was updated in the database to the current date, modifying the value anyways with a not desired change.

I was browsing the code to find something wrong in the input parameter handling, I found nothing.

After that I was looking for customization on the update process on the model, nothing, all standard.

So went to look up the database, I found this doing a describe on the table.

Whaaaaat I said, I went to look the migration of that table creation and I haven’t saw something explicit related to that extra constraint

So, googling I found this post , “Why does Laravel 5 auto update my date field?” and this answer explained the issue very well.

In the answer says

According to the reference manual MySQL 5.6
TIMESTAMP and DATETIME columns have no automatic properties unless they are specified explicitly, with this exception: If the explicit_defaults_for_timestamp system variable is disabled, the first TIMESTAMP column has both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP if neither is specified explicitly.

So the solution is also in the anwser, I needed to do a new migration to add the nullable attribute to the field ($table->timestamp(‘date’)->nullable()), The migration to make this alter will look like this.

However, we I ran the migration I got this

Doctrine\DBAL\Exception
Unknown column type "timestamp" requested. Any Doctrine type that you use has to be registered with \Doctrine\DBAL\Types\Type::addType().

Looking for the error I have found this note on the documentation

The following column types can be modified: bigInteger, binary, boolean, char, date, dateTime, dateTimeTz, decimal, integer, json, longText, mediumText, smallInteger, string, text, time, unsignedBigInteger, unsignedInteger, unsignedSmallInteger, and uuid. To modify a timestamp column type a Doctrine type must be registered.

So, to allow timestamp fields modifications in a migration I needed to add this to the config/database.php

And with these changes all worked well and the issue was solved. I wanted to write this post just to keep a log of all these small details because are very important and I know that I’ll have these same issues in the future. Keep in mind that the timestamp type have some special considerations like theses.

That’s all, happy hacking!

Originally posted on: https://casivaagustin.com.ar/index.php/laravel-mysql-migrations-and-timestamps/

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.