Can not change field definition to JSON with default value.

[taron96]

Bug Report

Q	A
MySQL	8.0.27
DBAL	3.4.4
Laravel	9.31.0

Summary

Can not change field definition to JSON with default value.

Current behaviour

In Laravel migration file, when trying to change field definition to JSON and set default value to (JSON_OBJECT()) generated SQL statement is wrong and can not be executed. Following is the MySQL error.

SQLSTATE[42000]: Syntax error or access violation: 1101 BLOB, TEXT, GEOMETRY or JSON column 'json' can't have a default value (SQL: ALTER TABLE test CHANGE json json JSON DEFAULT '(JSON_OBJECT())' NOT NULL)

How to reproduce

Create migration file to change some column definition in Laravel. Replace up method of migration with this (this code assumes that you already have table called test with the field called json).

use Illuminate\Database\Query\Expression;

…

public function up(): void
{
    Schema::table('test', function (Blueprint $table) {
        $table->json('json')->default(new Expression('(JSON_OBJECT())'))->change();
    });
}

Run migration and you will see the error.

Expected behaviour

As you can see in the generated error, default value of JSON field is surrounded with single quotes, which is the cause of the issue. If you will try to run following query (which is without quotes) then you will see that there would not be an error.

ALTER TABLE test CHANGE json json JSON DEFAULT (JSON_OBJECT()) NOT NULL

After a lot of time of debugging DBAL code I have found the cause of the issue.

File: src/Platforms/AbstractPlatform.php Method: getDefaultValueDeclarationSQL

Right before the last return statement there should be added something like this (of course you will add some extra sanitization, because you better know how to do that).

if ($type instanceof Types\JsonType) {
    return " DEFAULT $default";
}

[morozov]

@taron96 please make sure you reproduce the issue using the DBAL API, not Laravel. Otherwise, how do you know that that problem is in the DBAL, not Laravel?

[taron96]

@morozov thanks for the reply. I'm pretty sure the problem isn't with Laravel because Laravel uses DBAL to change the field definition. When I try to create the specified field using Laravel it generates the request correctly. Also, I debugged the issue and found the problematic part of code I mentioned in the last part of my bug report.

[morozov]

The DBAL doesn't support default expressions for columns, it supports default values. https://github.com/doctrine/dbal/pull/2960 is one of the changes that clarify the distinction between the values and expressions.

The API you are using in Laravel (default(new Expression(...))) cannot be implemented via the DBAL APIs.

There are some hacks that allow the expressions like CURRENT_TIMESTAMP to not be interpreted as values but those are exceptions. You can find more context on this matter by the Default Values tag.

[taron96]

Is there any chances that in near future DBAL will support expressions as default values?

[morozov]

This is unlikely as it's not being actively worked on.

[lupinitylabs]

The DBAL doesn't support default expressions for columns, it supports default values. #2960 is one of the changes that clarify the distinction between the values and expressions.

The API you are using in Laravel (default(new Expression(...))) cannot be implemented via the DBAL APIs.

There are some hacks that allow the expressions like CURRENT_TIMESTAMP to not be interpreted as values but those are exceptions. You can find more context on this matter by the Default Values tag.

Laravel in fact uses DBAL for the process, but it passes a Illuminate\Database\Query\Expression object on to getAlterTableSQL() including the following column definition in the Doctrine\DBAL\Schema\TableDiff parameter:

      +column: Doctrine\DBAL\Schema\Column^ {#2286
        #_name: "role"
        #_namespace: null
        #_quoted: false
        #_type: Doctrine\DBAL\Types\JsonType^ {#1437}
        #_length: 0
        #_precision: 10
        #_scale: 0
        #_unsigned: false
        #_fixed: false
        #_notnull: false
        #_default: Illuminate\Database\Query\Expression^ {#2253
          #value: "(JSON_ARRAY())"
        }
        #_autoincrement: false
        #_platformOptions: []
        #_columnDefinition: null
        #_comment: null
        #_customSchemaOptions: array:3 [
          "collation" => ""
          "charset" => ""
          "change" => true
        ]
      }

Now this seems odd, because DBAL does not support this type in getDefaultValueDeclarationSQL(), so it will be cast as string, which in turn leads to this issue.

The real issue here, as I see it, is that DBAL does not support any type to set a JSON_ARRAY() or JSON_OBJECT() (or even JSON_QUOTE(...) as a default value, so it's just a (4 years old!) MySQL feature that is unsupported by DBAL. Laravel correctly sets the column type to Doctrine\DBAL\Types\JsonType, so a naïve working fix in Platforms/AbstractMySQLPlatform would be to add something like...

if ($type instanceof Types\JsonType && in_array(strtoupper($default), ['(JSON_ARRAY())', '(JSON_OBJECT())'])) {
    return ' DEFAULT ' . $default;
}

You may have a more sophisticated way to do this in mind. However, the above would fix the issue while keeping it restricted to those particular functions and not breaking null or string (on MariaDB) defaults.

What do you think? I'll be happy to provide a PR for this.

[felipyamorim]

@morozov Does https://github.com/doctrine/dbal/pull/3626 not resolve this issue? I couldn't see what was missing. I can get ready to make a new one.

[derrabus]

That PR was a step in the right direction, but it was far from being ready. If you want to pick up the work, feel free to give it a try, but beware that this is not a trivial feature to implement.

[felipyamorim]

@derrabus , thank you for the reply, i will analyse and try to make a new pr.

[bilogic]

I was able to do this now in L10

$table->json('settings')
                ->default(new Expression('(JSON_ARRAY())'));

Did someone fix this?

[amorphine]

I was able to do this now in L10

$table->json('settings')
                ->default(new Expression('(JSON_ARRAY())'));

Did someone fix this?

The problem is about ->change(). General ->default(new Expression(... works in Laravel 9 too

[Oskar-Mikael]

Can confirm this is still an issue. My code is identical to @taron96

Laravel Version 10.34.2

[aravael]

@felipyamorim , did you make new pr or any updates on this. Still has this behavior on L10.35.0

[lupinitylabs]

I would probably just switch to L11 to be honest, which doesn't use DBAL anymore at all.

[hafezdivandari]

On Laravel 10.x, you may call Schema::useNativeSchemaOperationsIfPossible() method within the boot method of your App\Providers\AppServiceProvider class to force using native schema operations instead of Doctrine DBAL. Check PR laravel/framework#45487.