Blog

Laravel - Updating Timestamp Fields for MySQL 5.6 to MySQL 5.7

The old MySQL 5.6 was very generous with what it was allowing by default. This has led to somewhat a bad reputation that MySQL can cause unintended data loss. The latest 5.7 and 8 releases fix this, by adding more strict rules in place.

If you have to move your Laravel installation to the latest MySQL version, the first issue you will have is the data entry timestamps, which in the past defaulted to "0000-00-00 00:00:00", which according to SQL is invalid date. To upgrade these you will have to default these to NULL.

Here is the code how to make the required change:

SET sql_mode = '';
ALTER TABLE mytable
    MODIFY created_at TIMESTAMP NULL DEFAULT NULL,
    MODIFY updated_at TIMESTAMP NULL DEFAULT NULL

 

If you want to use in a migration wrap it up in SQL statements and execute directly

$sql = "SET sql_mode = '';";
DB::statement($sql);
        
$sql = " ALTER TABLE mytable";
$sql .= " MODIFY created_at TIMESTAMP NULL DEFAULT NULL,";
$sql .= " MODIFY updated_at TIMESTAMP NULL DEFAULT NULL";
DB::statement($sql);

 

Hope it helps. Any questions let me know.