Framework: Migrate:reset for json column

[thetispro]

• Laravel Version: 5.3.10
• PHP Version: 7.2
• Database Driver & Version: mysql 5.7.12
  

  Description:

_migrate_ works fine for a json column but reseting the migration throws

[Doctrine\DBAL\DBALException]
Unknown database type json requested, Doctrine\DBAL\Platforms\MySQL57Platform may not support it.

Steps To Reproduce:

Create a new migration

class UpdateUsers extends Migration
{

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('users', function (Blueprint $table) {
             $table->json('data')->after('email')->nullable()->comment('Various information json encoded');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */ 
    public function down()
    {
        Schema::table('users',function (Blueprint $table) {
            $table->dropColumn('data');
        });
    }
}

php artisan migrate -> ok (a 'data' column with a json type is create)
php artisan migrate:reset ->fail

[themsaid]

Can't regenerate using your exact migration, could it be another migration file that causes the error?

[thetispro]

OK Here is the true migration.

class AddGuardianToUsers extends Migration
{

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table(config('guardian.users_table'), function (Blueprint $table) {
            $table->renameColumn('name', 'username');
        });


        Schema::table(config('guardian.users_table'), function (Blueprint $table) {

            $table->string('username', 81)->change();
            $table->string('email', 80)->change();
            $table->json('data')->after('email')->nullable()->comment('Various information json encoded');
            $table->string('language', 6);
            $table->text('abilities')->after('password')->nullable()->comment('Abilities granted to the user');
            $table->text('parameters')->after('abilities')->nullable()->comment('Parameters granted to the user');
            $table->string('activation_token',100)->after('remember_token')->nullable();
            $table->timestamp('activated_at')->after('created_at')->nullable();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */ 
    public function down()
    {
        Schema::table(config('guardian.users_table'),function (Blueprint $table) {
            $table->string('username', 255)->change();
            $table->string('email', 255)->change();
            $table->dropColumn('abilities','parameters', 'data','activation_token','activated_at');
        });

        Schema::table(config('guardian.users_table'),function (Blueprint $table) {
            $table->renameColumn('username', 'name');
        });
    }
}

[thetispro]

Note : you have to replace config('guardian.users_table') with 'users'

[djtarazona]

Using renameColumn on a table that already has a json or jsonb column has been known to create issues as Doctrine isn't aware of this column type for some reason.

See:
https://github.com/laravel/framework/issues/14356
https://github.com/laravel/framework/issues/12376

It looks like this issue is supposed to be fixed?

[engAhmad]

public function __construct()
{
    DB::getDoctrineSchemaManager()->getDatabasePlatform()->registerDoctrineTypeMapping('json');
}

[thetispro]

 public function __construct()
{
     DB::getDoctrineSchemaManager()->getDatabasePlatform()>registerDoctrineTypeMapping('json');
 }

[ErrorException]
Missing argument 2 for Doctrine\DBAL\Platforms\AbstractPlatform::registerDoctrineTypeMapping()

[engAhmad]

public function __construct()
{
    DB::getDoctrineSchemaManager()->getDatabasePlatform()->registerDoctrineTypeMapping('json', 'string');
}

[thetispro]

@engAhmad that works for me. Thanks.

[sebastiaanluca]

This has been fixed, right? First time I'm getting this exception. Trying to make a column nullable in a table that also has a JSON column.

$this->schema->table('table', function (Blueprint $table) {
    $table->integer('sender_id')->unsigned()->nullable()->change();
});

[Doctrine\DBAL\DBALException]
Unknown database type json requested, Doctrine\DBAL\Platforms\MySQL57Platform may not support it.

The following fixes it temporarily, but that's highly undesired.

public function __construct()
{
    DB::getDoctrineSchemaManager()->getDatabasePlatform()->registerDoctrineTypeMapping('json', 'string');
}

Seems like it hasn't been released yet in doctrine/dbal? (https://github.com/doctrine/dbal/releases/tag/v2.5.12)

References: https://github.com/laravel/framework/issues/14356, https://github.com/doctrine/dbal/pull/2266, https://github.com/laravel/framework/pull/14363.

[Afnisse]

The right fix for this problem is to add a server version on config/database.php under the MySQL section.

'server_version' => "5.7"

because if you don't, Doctrine will use Doctrine\DBAL\Platforms\MySqlPlatform which supports all versions of MYSQL since MYSQL 5.0 which does not support JSON fields, and by adding the server_version doctrine will load MySQL57Platform that have support for JSON instead of MySqlPlatform.

example of database MySQL config:

'mysql' => [
            'driver' => 'mysql',
            'url' => env('DATABASE_URL'),
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'prefix_indexes' => true,
            'strict' => true,
            'engine' => null,
            'server_version' => "5.7",
            'options' => extension_loaded('pdo_mysql') ? array_filter([
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
            ]) : [],
        ],