Laravel migration return SQL error but SQL is valid and it works

I have migration which works locally but fails on production with error:

Migrating: 2024_03_19_145356_tasks

   Illuminate\Database\QueryException 

  SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'send_before' (SQL: create table `tasks` (`id` bigint unsigned not null auto_increment primary key, `subscription_id` bigint unsigned not null, `send_after` timestamp not null, `send_before` timestamp not null, `sent_at` timestamp null comment 'null = not sent', `failed_at` timestamp null, `delivered` tinyint(1) null default '0' comment '0 - not delivered, 1 - delivered, null - unknown', `created_at` timestamp null, `updated_at` timestamp null) default character set utf8mb4 collate 'utf8mb4_unicode_ci')

I'm failing miserably to debug because SQL is valid and in fact works fine on production when run via phpMyAdmin or terminal:

create table `tasks` (`id` bigint unsigned not null auto_increment primary key, `subscription_id` bigint unsigned not null, `send_after` timestamp not null, `send_before` timestamp not null, `sent_at` timestamp null comment 'null = not sent', `failed_at` timestamp null, `delivered` tinyint(1) null default '0' comment '0 - not delivered, 1 - delivered, null - unknown', `created_at` timestamp null, `updated_at` timestamp null) default character set utf8mb4 collate 'utf8mb4_unicode_ci'

2024_03_19_145356_tasks.php

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class Tasks extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('tasks', function (Blueprint $table) {
            $table->id();
            $table->foreignId('subscription_id')->constrained('subscriptions')->cascadeOnDelete();
            $table->timestamp('send_after')->index();
            $table->timestamp('send_before')->index();
            $table->timestamp('sent_at')->nullable()->index()->comment('null = not sent');
            $table->timestamp('failed_at')->nullable();
            $table->boolean('delivered')->default(0)->comment('0 - not delivered, 1 - delivered, null - unknown')->nullable();
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('tasks');
    }
}

Server version: 5.5.60-MariaDB MariaDB Server

PHP 7.4.33 (cli) (built: Dec 12 2023 14:45:16) ( NTS )

Laravel Framework 8.83.27

UPDATED 1

Added dump(\DB::select("SELECT @@version, @@sql_mode")); into migration and I found out that sql_mode during migration has this sql_mode settings:

array:1 [
  0 => {#1958
    +"@@version": "5.5.60-MariaDB"
    +"@@sql_mode": "ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
  }
]

UPDATED (SOLVED?)

I located the issue. I don't want to pretend like I have a clue what is happening, so I'll say it as it is: The problem hits when migration has more then one column type timestamp which is not nullable. Here are some example:

Works:

Schema::create('tasks', function (Blueprint $table) {
    $table->id();
    $table->timestamp('test1');
    $table->timestamp('test2')->nullable();
    $table->timestamp('test3')->nullable();
});

Error 1067 Invalid default value for 'test2':

Schema::create('tasks', function (Blueprint $table) {
    $table->id();
    $table->timestamp('test1');
    $table->timestamp('test2');
    $table->timestamp('test3')->nullable();
});

Works:

Schema::create('tasks', function (Blueprint $table) {
    $table->id();
    $table->timestamp('test1')->nullable();
    $table->timestamp('test2')->nullable();
    $table->timestamp('test3');
});

Error 1067 Invalid default value for 'test2':

Schema::create('tasks', function (Blueprint $table) {
    $table->id();
    $table->timestamp('test1');
    $table->timestamp('test2');
});

Works:

Schema::create('tasks', function (Blueprint $table) {
    $table->id();
    $table->timestamp('test1')->nullable();
    $table->timestamp('test2');
    $table->timestamp('test3')->nullable();
});

And... if I use dateTime it always works. So... The issue is kind of solved although I have no clue what is going on and I'd like to know

Solution

The implicit default for timestamp columns (that is, the default when you don't define a default explicitly) is complex. And it behaves differently after the first timestamp column of a table. It's super weird.

The first timestamp column is automatically given the DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP options, if you don't specify any default, and explicit_defaults_for_timestamp is false.

Subsequent timestamp columns don't get these options automatically. The implicit default for a NOT NULL timestamp is the "default default" timestamp value, which is 0.

But Laravel apparently sets a session sql_mode that includes NO_ZERO_IN_DATE,NO_ZERO_DATE, which makes the 0 value of timestamp invalid.

So your options are:

Allow your timestamp column to be nullable.
Declare an explicit DEFAULT for the column.
Figure out how to change Laravel's idea of the best sql_mode to allow zeroes in dates. I don't recommend this, because dates with zeroes aren't real dates. It's a good idea to disallow them.