dbal: [2.10.0] Do not add CHARACTER SET for some column types

I solved my errors in the migration files adding ->charset(null).

My new line now looks like this: $table->json('column_name')->charset(null)->nullable()->change();. Finally the errors are gone 😌

Our ci also broke this day with the same issue metioned above while executing: $table->dateTime('date_of_birth')->change()->nullable() which was not nullable before mirgrating. Maybe this issue affects more types than just numeric columns. We worked around with explicit version set for dbal to 2.9.2 in composer.json.

Taking feedback from:

• https://github.com/doctrine/dbal/issues/3714#issuecomment-550979894
• https://github.com/doctrine/dbal/issues/3714#issuecomment-558212113

$table->addColumn('foo', 'string')->setPlatformOption('charset', 'ascii'); should also be reverted when changing column type (by downstream tooling) by calling ->setPlatformOption('charset', null);.

Closing here as invalid, since, while it is indeed true that DBAL is creating INT UNSIGNED CHARACTER SET utf8mb4, it is merely following instructions about the given Column definition.

@nicolasbuch IMHO the problem is that the tool you’re using is sending wrong information to DBAL. That should be fixed by such tool.

Folks, Laravel is using DBAL in the wrong way and relying on a bug. Once the bug got fixed this problem started to happen, hence it being invalid.

I really understand your frustration but the message we’re trying to convey here is that it’s Laravel’s responsibility to generate the correct object to be sent to DBAL.

It seems like https://github.com/laravel/framework/blob/1bbe5528568555d597582fdbec73e31f8a818dbc/src/Illuminate/Database/Schema/Grammars/ChangeColumn.php#L125 is the culprit.

So, please send a PR there to solve this once and for all 😁

I dont think this issue is invalid.

I have Laravel migration from text to json. It works perfectly in 2.9.3 and 2.10.0 fails. This means that I am stuck with older dbal version.

Schema::table('products', function (Blueprint $table) {
            $table->json("name")->change();            
        });

Doctrine\DBAL\Driver\PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CHARACTER SET utf8mb4 NOT NULL, CHANGE name name JSON CHARACTER SET ut' at line 1

I’m just going to happily link these two threads together, so both sides are informed and we can move further. https://github.com/laravel/framework/issues/30539

Sorry I’m not going to contribute directly by merge request, I’m afraid this would result into a terrible mess.

Cheers and thank you!

Same issue with us - Laravel 5.8 & mysql 5.7 and we have a migration from months ago changing a column type to JSON. On DBAL v2.9.2, it was working fine, but upgrading to 2.10, breaks at the loading of this migration (and during phpunit runs since the entire DB is reconstructed)

Experiencing the same issue when trying to change a column data type. Works with doctrine/dbal 2.9.3 but breaks with 2.10.0

Schema::table('users', function (Blueprint $table) {
    $table->string('avatar');
});
Schema::table('users', function (Blueprint $table) {
    $table->integer('avatar')->change();
});

@strtz would you please report that to Laravel?

https://github.com/laravel/framework/issues/30539 added, just leaving info in both places in case someone is looking for workaround that do not involve raw statements

As mentioned above, we need this to be laid out in DBAL API only (no laravel) to identify the issue.

I solved my errors in the migration files adding ->charset(null).

My new line now looks like this: $table->json('column_name')->charset(null)->nullable()->change();. Finally the errors are gone

By using ->charset(null), my error is not solved; but after using ->charset('')->collation(''), the error is gone. For example: $table->integer('resource_type')->default(0)->charset('')->collation('')->change();

FYI this appears to have been resolved in laravel 6 and 7 https://github.com/laravel/framework/issues/30539#issuecomment-622082778

I’m in the same situation as @anususmi (Laravel 5.7.22 & doctrine/dbal ^2.10) and the issue is still present even with the ->charset(null) workaround 😢

Guessing the only way to resolve this is to downgrade?

I do confirm the issue still exists in Laravel 6.x and that is pretty confusing 😕 :

When I was trying to refresh my migrations I got stuck with the following error:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘CHARACTER SET utf8 NOT NULL COLLAT
E utf8_unicode_ci’ at line 1 (SQL: ALTER TABLE foo CHANGE foo_type verifiable_type INT UNSIGNED CHARACTER SET utf8 NOT NULL COLLATE utf8_unicode_ci)

which was caused by the following piece of code:

    public function down()
    {
        Schema::connection(env('DB_CONNECTION_FOO'))
            ->table('foo', function (Blueprint $table) {
                $table->unsignedInteger('foo_type')->change();
            });
    }

Previously I did not have any issues and I haven’t made any changes to migrations since the last time they successfully run

The workaround suggested by @stevenbuehner ->charset(null) solved the problem and I was able to run my migrations again

Laravel […] I am stuck with older dbal version

Technically, not really. You can always use unprepared to work this around if you don’t want to get stuck.

Thank you @AlterTable for the explanation.

I think there are two ways to fix this, one is add extra checks in \Doctrine\DBAL\Schema\Table::changeColumn(), another is modify Laravel migrate, add checks after changed attributes are set. I prefer the second one.

In order to make the proper decision, we need a scenario that reproduces the issue using only the DBAL API.

When generating alter table SQL by Laravel migration, the column diff is calculated as Laravel Blueprint describes:

1. get changed column (return \Doctrine\DBAL\Schema\Column), this step only return a type-changed column

https://github.com/illuminate/database/blob/e0d5ca153f0204c5312465109246bcf03b7fa603/Schema/Grammars/ChangeColumn.php#L105-L107

1. set changed attributes one by one

https://github.com/illuminate/database/blob/e0d5ca153f0204c5312465109246bcf03b7fa603/Schema/Grammars/ChangeColumn.php#L81-L90

Now see the bugged migration, The Blueprint $table->integer('mycolName')->unsigned()->nullable()->change(); said one column named ‘mycolName’ should be changed to unsigned integer, that’s OK. But when it’s original type was varchar/text, there is a implicit change: the charset definition should be removed. Neither Laravel nor Doctrine handled this.

I think there are two ways to fix this, one is add extra checks in \Doctrine\DBAL\Schema\Table::changeColumn, another is modify Laravel migrate, add checks after changed attributes are set. I prefer the second one.

Not really a BC break, but rather a regression.

still present in: “php”: “^7.2”, “laravel/framework”: “^6.0”, “doctrine/dbal”: “^2.10”,

error while trying to change column from text to blob

workaround for me was to make a intermediary migration that runs above the alter and drop the column and recreate it for both fwd and reverse migration

$table->json(‘column_name’)->charset(null)->nullable()->change(); is not working for me…!!! I want to add nullable to already existing column $table->string(‘phone_number’); My laravel version 5.7 and doctrine/dbal ^2.10 Please help me…!! Thanks in advance 😃