Framework: Renaming nullable datetime column using MariaDB 10.2

Created on 12 Sep 2017 · 5Comments · Source: laravel/framework

Laravel Version: 5.5.3
PHP Version: 7.1.7-1+ubuntu16.04.1+deb.sury.org+1
Database Driver & Version: PDO, 10.2.8-MariaDB-10.2.8

Description:

A nullable datetime column cannot be renamed with a migration. The migration fails with the following error message:

  [Illuminate\Database\QueryException]                                                                  
  SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: 'NULL' for column 'due_date  
  ' at row 1 (SQL: ALTER TABLE test CHANGE dueDate due_date DATETIME DEFAULT 'NULL')                    



  [Doctrine\DBAL\Driver\PDOException]                                                                   
  SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: 'NULL' for column 'due_date  
  ' at row 1                                                                                            



  [PDOException]                                                                                        
  SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: 'NULL' for column 'due_date  
  ' at row 1

Steps To Reproduce:

Create a migration to create a table with a nullable datetime column:

<?php

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

class CreateTestTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('test', function (Blueprint $table) {
            $table->increments('id');
            $table->dateTime('dueDate')->nullable();
        });
    }

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

Run the migration.
Create a second migration to rename the column:

<?php

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

class RenameColumn extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('test', function(Blueprint $table) {
            $table->renameColumn('dueDate', 'due_date');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        //
    }
}

Run the migration.

Source

lbausch

Most helpful comment

The root cause is a breaking change in mariadb 10.2.7 for people using the information_schema.columns view to calculate default values. The linked doctrine issue is the fix in doctrine/dbal. This is not _caused_ by doctrine/dbal.

MDEV-13132: Literals in the COLUMN_DEFAULT column in the Information Schema COLUMNS table are now quoted to distinguish them from expressions.

Source: https://mariadb.com/kb/en/library/mariadb-1027-release-notes/

sisve on 13 Sep 2017

👍2

All 5 comments

The SQL mode seems to have changed in MariaDB 10.2, look at this:

Since MariaDB 10.2.4, SQL_MODE is by default set to NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO.
In earlier versions of MariaDB 10.2, and since MariaDB 10.1.7,
SQL_MODE is by default set to NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER.
For earlier versions of MariaDB 10.1, and MariaDB 10.0 and before, no default is set.

https://mariadb.com/kb/en/library/sql-mode/