Malformed SQL generated for MySQL when changing column type.

[acelaya]

Bug Report

Q	A
BC Break	no
Version	2.2.0

Summary

I have a table called visit_locations on a MySQL 5.7 database, with a latitude column I want to change from STRING to FLOAT.

When the migration is run, I always get this error:

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 DEFAULT NULL COLLATE utf8_unicode_ci` at line 1

Current behavior

The error is thrown.

How to reproduce

I have reproduced it on MySQL 5.7, with a migration like this one:

<?php

declare(strict_types=1);

namespace Migrations;

use Doctrine\DBAL\Schema\Schema;
use Doctrine\DBAL\Types\Type;
use Doctrine\DBAL\Types\Types;
use Doctrine\Migrations\AbstractMigration;

final class Version20200105165647 extends AbstractMigration
{
    public function up(Schema $schema): void
    {
        $visitLocations = $schema->getTable('visit_locations');
        $visitLocations->getColumn('latitude')->setType(Type::getType(Types::FLOAT));
    }

    public function down(Schema $schema): void
    {
        $visitLocations = $schema->getTable('visit_locations');
        $visitLocations->getColumn('latitude')->setType(Type::getType(Types::STRING));
    }
}

I have also seen that the SQL generated is this:

ALTER TABLE visit_locations CHANGE latitude latitude DOUBLE PRECISION CHARACTER SET utf8 DEFAULT NULL COLLATE `utf8_unicode_ci`

If instead I remove the CHARACTER SET utf8 part, then the instruction works, changing the column type as expected.

Maybe this part is not getting placed in the proper position?

Expected behavior

The migration is executed without throwing an error.

[TomHAnderson]

Before running the migration and getting the error you've reported, what is the character set for the table and column you are trying to change?

And, utf8mb4 is the correct character set you should be using.

[acelaya]

Before running the migration, it was utf8

[TomHAnderson]

CHARACTER SET utf8 DEFAULT NULL COLLATE utf8_unicode_ci

I think you should not include default null after the character set.

[acelaya]

Yes, that's also my guess, but I'm not the one including it. Doctrine is generating that query based on the migration I provided in the issue.

[gms8994]

I'm experiencing this issue as well in 2.10.1 - 2.9.3 did not experience this issue.

[ghost]

Hi, I'm experiencing too this issue.
My environment are

doctrine/dbal:2.10.0
MySQL: 5.6

but not occured this problem in doctrine 2.9.3

Print source here.

{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('hoge', function (Blueprint $table) {
            $table->smallInteger('char_type')->unsigned()->nullable()->change();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('hoge', function (Blueprint $table) {
            $table->string('char_type', 5)->nullable()->change();
        });
    }
}

[gms8994]

It looks like the issue came from 73f38e7#diff-f8e1d5c54371540dc546d17c426c36e6 - commenting out the $column->setPlatformOption('charset', $tableColumn['characterset']); seems to work correctly.

[ghost]

Thx, this problems exactly happen on this code.

[roy-bongers]

We experience the same problem using MySQL 5.7 and doctrine/dbal v2.10.1

Schema::table('some_table_name', function (Blueprint $table) {
    $table->unsignedSmallInteger('some_column_name')->change()->default(100);
});

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
DEFAULT 100 COLLATE `utf8_unicode_ci`' at line 1 (SQL: ALTER TABLE some_table_name CHANGE
some_column_name some_column_name SMALLINT UNSIGNED CHARACTER SET utf8 DEFAULT 100
COLLATE `utf8_unicode_ci`)

[morozov]

Closing as a duplicate of #3714.

[github-actions]

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.