Migrating a TEXT field to JSON incorrectly sets COLLATE and MySQL throws an error

[poisa]

• Laravel Version: 5.6.29
• PHP Version: 7.1.20
• Database Driver & Version: MySQL 5.7.23
• doctrine/dbal Version: 2.8.0

Description:

I expect the generated SQL to change a column to json to include a valid collation for a JSON field (such as binary) or no collation at all:

ALTER TABLE mytable CHANGE mycolumn mycolumn JSON DEFAULT NULL

The actual SQL generated is:

ALTER TABLE mytable CHANGE mycolumn mycolumn JSON DEFAULT NULL COLLATE utf8mb4_unicode_ci

This results in the following error:

SQLSTATE[42000]: Syntax error or access violation: 1253 COLLATION 'utf8mb4_unicode_ci' is not valid for CHARACTER SET 'binary'

Steps To Reproduce:

While creating a table set a field to TEXT:

$table->text('mycolumn')->nullable();

Then create a migration that attempts to change the field type to JSON:

$table->json('mycolumn')->nullable()->change();

[staudenmeir]

Please move this to https://github.com/doctrine/dbal/issues.

[poisa]

Done: doctrine/dbal#3295

[staudenmeir]

Please close the issue.

[poisa]

@staudenmeir The DBAL maintainers say "[...] you are responsible for setting the collation. Nothing is guessed or done automatically". So it looks like this falls under Laravel's umbrella.

When changing into a JSON field, the collation should be set to an empty string and this issue will be solved.

Here's a PR with a fix to this: #25741