[Migrations] Rename column changes default from NULL to 'NULL'

[KKSzymanowski]

• Laravel Version: 5.5.20
• doctrine/dbal Version: 2.6.2
• PHP Version: 7.1.11
• Database Driver & Version: mysql Ver 15.1 Distrib 10.2.10-MariaDB

Description:

When renaming a nullable string column the default value changes from the database NULL to a literal string 'NULL'.

This itself isn't the whole issue. When trying to rollback the last migration:

php artisan migrate:rollback --step=1

the following exception is thrown:

[Illuminate\Database\QueryException (42000)]
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '''' at line 1 (SQL: ALTER TABLE products CHANGE description name VARCHAR(255) DEFAULT ''NULL'')                                                                                                                                                                                                                                                                                       

Exception trace:
 () at /Users/kubaszymanowski/Code/popc/vendor/laravel/framework/src/Illuminate/Database/Connection.php:664
 Illuminate\Database\Connection->runQueryCallback() at /Users/kubaszymanowski/Code/popc/vendor/laravel/framework/src/Illuminate/Database/Connection.php:624
 Illuminate\Database\Connection->run() at /Users/kubaszymanowski/Code/popc/vendor/laravel/framework/src/Illuminate/Database/Connection.php:459
 Illuminate\Database\Connection->statement() at /Users/kubaszymanowski/Code/popc/vendor/laravel/framework/src/Illuminate/Database/Schema/Blueprint.php:86
 Illuminate\Database\Schema\Blueprint->build() at /Users/kubaszymanowski/Code/popc/vendor/laravel/framework/src/Illuminate/Database/Schema/Builder.php:252
 Illuminate\Database\Schema\Builder->build() at /Users/kubaszymanowski/Code/popc/vendor/laravel/framework/src/Illuminate/Database/Schema/Builder.php:149
 Illuminate\Database\Schema\Builder->table() at /Users/kubaszymanowski/Code/popc/vendor/laravel/framework/src/Illuminate/Support/Facades/Facade.php:221
 Illuminate\Support\Facades\Facade::__callStatic() at /Users/kubaszymanowski/Code/popc/database/migrations/2017_11_12_150924_rename_old_columns_in_clients.php:22
 RenameOldColumnsInClients->down() at /Users/kubaszymanowski/Code/popc/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:359
 ...

Steps To Reproduce:

1. Run

   composer require doctrine/dbal
   php artisan make:model Product -m
   

2. To the up method of the created migration class add:

   $table->string('name')->nullable();

3. Run

   php artisan migrate
   

4. In the Tinker run:

   Product::create();

5. Run

   php artisan make:migration --table=products rename_product_name_column
   

6. In the new migration class add:

   public function up()
   {
       Schema::table('products', function (Blueprint $table) {
           $table->renameColumn('name', 'description');
       });
   }
   
   public function down()
   {
       Schema::table('products', function (Blueprint $table) {
           $table->renameColumn('description', 'name');
       });
   }

7. Run

   php artisan migrate
   

8. In the Tinker run:

   Product::create();
   Product::get();

The output from the last command should show, that the default value for the column in question has been changed from the database NULL to a string 'NULL'.

>>> Product::get();
=> Illuminate\Database\Eloquent\Collection {#743
     all: [
       App\Product {#738
         id: 1,
         description: null,
         created_at: "2017-11-12 22:50:24",
         updated_at: "2017-11-12 22:50:24",
       },
       App\Product {#742
         id: 2,
         description: "NULL",
         created_at: "2017-11-12 22:59:19",
         updated_at: "2017-11-12 22:59:19",
       },
     ],
   }

The first product was created in the third of these steps and its description property has the correct null value. The second one was created in the last step and has the new, incorrect 'NULL' value for the description property.

[KKSzymanowski]

A possible workaround would be to reset the default column value before and after each migration operation.

public function up()
{
    Schema::table('products', function (Blueprint $table) {
        $table->string('name')->default(null)->change();
    });
    Schema::table('products', function (Blueprint $table) {
        $table->renameColumn('name', 'description');
    });
    Schema::table('products', function (Blueprint $table) {
        $table->string('description')->default(null)->change();
    });
}

public function down()
{
    Schema::table('products', function (Blueprint $table) {
        $table->string('description')->default(null)->change();
    });
    Schema::table('products', function (Blueprint $table) {
        $table->renameColumn('description', 'name');
    });
    Schema::table('products', function (Blueprint $table) {
        $table->string('name')->default(null)->change();
    });
}

This makes it possible to roll this migration back and forth with no errors.

[sisve]

It's a known issue with MariaDB 10.2.7 that broke stuff. See #21140 and doctrine/dbal#2825

Note that MariaDB is missing in the list of supported databases.

We don't officially support mariadb, [...]

Source: #11518 (comment)

[devcircus]

Concerning the mariadb support, see Taylor's follow up comment. #11518 (comment)

[sisve]

The linked comment does not at all mentioned MariaDB, only MySQL 5.7. The context is changes in MySQL over time, not MariaDB support.

[devcircus]

Ah true. I've misinterpreted that comment for the past 2 years. I guess this tweet threw me off concerning Maria support, and I was simply responding to the idea that Laravel doesn't officially support Maria.

[jamesgraham]

Installing the latest Homestead causes this problem to manifest itself if you specify mariadb: true in Homestead.yaml.

[staudenmeir]

This has been fixed in doctrine/dbal 2.7.0.