Tables with timestamps() that were created in migration before v5.2, cannot be altered in migration in v5.3

[Nikita240]

• Laravel Version: 5.3.29
• PHP Version:7.1.0
• Database Driver & Version: mysql, v5.6.27

Description:

I have project that started out in 5.1, and has since been upgraded to 5.3.

Most of the tables were created in 5.1, so they have the old "not nullable" timestamps(). I also have some tables which were created in 5.3, and they have the new "nullable" timestamps(), and they work fine. (If you don't know what I'm referring to, see #11518)

Table that was migrated in 5.1:

Table that was migrated in 5.3:

The problem is, when I try to run a migration to ALTER, the OLD pre-5.2 tables (i.e. add or rename an unrelated column):

                                                                                    
  [Illuminate\Database\QueryException]                                              
  SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value fo  
  r 'created_at' (SQL: alter table `products` add `brand_id` int unsigned null)     
                                                                                    

                                                                                    
  [Doctrine\DBAL\Driver\PDOException]                                               
  SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value fo  
  r 'created_at'                                                                    
                                                                                    

                                                                                    
  [PDOException]                                                                    
  SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value fo  
  r 'created_at'  

Even though the fields I'm changing have nothing to do with timestamps, I'm unable to alter the table.

Additionally, I cannot migrate the timestamps (without deleting all of the timestamp data) to the new "nullable" ones because:

Steps To Reproduce:

Step 1: Create a new project in laravel v5.1
Step 2: Run a migration to create a table with timestamps.

<?php

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

class CreateTable extends Migration
{
    public function up()
    {
        Schema::table('test', function (Blueprint $table) {
            $table->increments('id');
            $table->timestamps();
        });
    }
	
    public function down()
    {
        Schema::drop('test');
    }
}

Step 3: Upgrade laravel to v5.3
Step 4: Run a migration to alter the table.

<?php

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

class AlterTable extends Migration
{
    public function up()
    {
        Schema::table('test', function (Blueprint $table) {
            $table->string('name');
        });
    }
    public function down()
    {
        Schema::table('test', function ($table) {
            $table->dropColumn('name');
        });
    }
}

Step 5: You should get:

                                                                                    
  [Illuminate\Database\QueryException]                                              
  SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value fo  
  r 'created_at' (SQL: alter table `test` add `name` varchar (255))     
                                                                                    

                                                                                    
  [Doctrine\DBAL\Driver\PDOException]                                               
  SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value fo  
  r 'created_at'                                                                    
                                                                                    

                                                                                    
  [PDOException]                                                                    
  SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value fo  
  r 'created_at'                                                                    

[themsaid]

That table products, can you share a sql query that creates it? I need to be able to create it and run a sql alter command to try to replicate the issue with pure sql.

[Nikita240]

@themsaid

This is the migration that originally created the table in v5.1 that was run in 2015:

<?php

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

class CreateProductsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('products', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name')->unique();
            $table->string('slug')->unique();
            $table->text('text')->nullable();
            $table->timestamps();
        });
    }

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

This is the exact migration that I tried running in v5.3 yesterday when I discovered the issue:

<?php

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

class AddBrandsToProducts extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('products', function (Blueprint $table) {
            $table->integer('brand_id')->unsigned()->index()->nullable();
            $table->foreign('brand_id')->references('id')->on('brands');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('products', function ($table) {
            $table->dropForeign('products_brand_id_foreign');
            $table->dropColumn('brand_id');
        });
    }
}

[Nikita240]

@themsaid oh my bad, just realized you wanted the query.

Here, I copied the create query from mysql workbench. Might just want to remove the foreign key's so that you can run it.

CREATE TABLE `products` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `slug` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `text` text COLLATE utf8_unicode_ci,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `category_id` int(10) unsigned DEFAULT NULL,
  `rating` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `products_name_unique` (`name`),
  UNIQUE KEY `products_slug_unique` (`slug`),
  KEY `products_category_id_index` (`category_id`),
  KEY `products_rating_id_index` (`rating`),
  CONSTRAINT `products_category_id_foreign` FOREIGN KEY (`category_id`) REFERENCES `product_categories` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=304 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

[smenzer]

any updates on the best way to update a legacy table using timestamps() to nullableTimestamps()?

[Nikita240]

@smenzer This is my work around.

I put the following at the beginning of my up() function in the migration.

DB::statement("ALTER TABLE `products`
            CHANGE COLUMN `created_at` `created_at` TIMESTAMP NULL ,
            CHANGE COLUMN `updated_at` `updated_at` TIMESTAMP NULL ;");

So this is how the whole "test" migration looks like:

<?php

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

class AlterTable extends Migration
{
    public function up()
    {
	DB::statement("ALTER TABLE `test`
	    CHANGE COLUMN `created_at` `created_at` TIMESTAMP NULL ,
	    CHANGE COLUMN `updated_at` `updated_at` TIMESTAMP NULL ;");
	
        Schema::table('test', function (Blueprint $table) {
            $table->string('name');
        });
    }
    public function down()
    {
        Schema::table('test', function ($table) {
            $table->dropColumn('name');
        });
    }
}

[themsaid]

I do believe this issue is more related to how MySQL works, there's nothing we can do about that, you'll need to run a script like this one:

https://gist.github.com/wayneashleyberry/9fef63e6845f4375b8f19c8068a40f2b

I think this will help prepare all your tables for strict mode.

[smenzer]

Thank you both. I know it's not Laravel-related, but I was just looking for the best way to do the migrations of my existing tables...both of the suggested solutions seem great so I'll implement one of them. Thanks!