Framework: [L5.1] Migrations - Cannot change an existing column to type char

[hskrasek]

Trying to write a migration to change a column from VARCHAR to CHAR, but when I run the migration it throws the following error:

[Doctrine\DBAL\DBALException]
  Unknown column type "char" requested. Any Doctrine type that you use has to be registered with \Doctrine\DBAL\Types\Type::addType(). You can get a list of all the known types with \Doctrine\DBAL\Types\Type::getTypesMap(). If this err
  or occurs during database introspection then you might have forgot to register all database types for a Doctrine Type. Use AbstractPlatform#registerDoctrineTypeMapping() or have your custom types implement Type#getMappedDatabaseTypes
  (). If the type name is empty you might have a problem with the cache or forgot some mapping information.

Here is what my up function looks like

 public function up()
    {
//        dd(\Doctrine\DBAL\Types\Type::getTypesMap());
        Schema::table('perk_word_search_boards', function (Blueprint $table) {
            $table->char('uuid', 36)->change();
        });
    }

Interestingly though if I add a new column of type char, the migration runs just fine.

[nicolasbeauvais]

I have the same problem when I whant to set the nullable property to an existing char field:

Schema::table('table', function (Blueprint $table) {
    $table->char('language')->nullable()->change();
});

[mikebronner]

This is possibly related, so I'll post it here (please let me know if not, and I'll create a new issue):

When trying to change a CHAR(36) column to a VARCHAR(50) column it changes the length but not the type. This column is also indexed, so it may be related to the above comment.

        Schema::table('table_name', function ($table) {
            $table->string('field_name', 50)->nullable()->change();
        });

I noticed that running a SQL statement also did not work, so it is probably a database limitation.

        DB::statement("
            ALTER TABLE `table_name`
            CHANGE `field_name` `field_name` VARCHAR(50) NOT NULL DEFAULT '0'
        ");

However, using the change() method still does not work with the indexes removed. Running the sql statement with the indexes removed did indeed change the field type to VARCHAR.

[lriggle-strib]

I am also experiencing the issue describe in https://github.com/laravel/framework/issues/9636#issuecomment-123366513. Trying to set nullable on an already existing char field produces the following error

[Doctrine\DBAL\DBALException]
Unknown column type "char" requested. Any Doctrine type that you use has to be registered with \Doctrine\DBAL\Types\Type::addType(). You can get a list of all the known types with \Doctrine\DBAL\Types\Type::getTypesMap(). If this error occurs during database introspection then you might have forgot to register all database types for a Doctrine Type. Use AbstractPlatform#registerDoctrineTypeMapping() or have your custom types implement Type#getMappedDatabaseTypes(). If the type name is empty you might have a problem with the cache or forgot some mapping information.

[phroggyy]

I'm quite certain Doctrine does not support the char type and that this would be the issue. Any confirmation from @taylorotwell would be great though.

[manogi]

For me, changing "text" columns to "mediumText" or "longText" like this
$table->mediumText('field_name')->change()
does not have an effect in migrations. Changing them to other types (like for example "string") works.

Should I make a new issue out of this or is it OK here?

[phroggyy]

@7talents again, this is no bug with Laravel but rather how Doctrine (of the Symfony framework) works. If you check the docs at http://doctrine-dbal.readthedocs.org/en/latest/reference/types.html#mapping-matrix, it's pretty clear that in doctrine, no difference is made between a mediumText or text. Rather, Doctrine relies on the size you set on the field. If you set the size <= 65535, TEXT will be used. If size <= 16777215, MEDIUMTEXT is used, and lastly, if the column length <= 4294967295 or empty, the field will be LONGTEXT.

[manogi]

@phroggyy Thank you very much for the explanation. Although, creating the the table with a migration, I just used "text" and there is nothing in the Laravel docs that says anything about text length (like for "string" or for "char"). And it does make a "text" column, while just "mediumText" makes a "mediumText" column. The problem ist, using "->change()" does not have an effect, if you want to switch between "text" and "mediumText". So maybe there is something that should/could be optimized here...

[phroggyy]

@7talents. That's because standard laravel migrations don't use Doctrine. However, to use the change command, you must have, at some point, included Doctrine DBAL. There is nothing in the docs about this because it's not part of the Laravel migrations. However, Laravel migrations do not support change. Or perhaps it was changed in 5.1, but nonetheless, it's Doctrine DBAL being used for all change operations, hence the lack of documentation from Laravel's side.

[bobbybouwmann]

@phroggyy The function change() is included: http://laravel.com/docs/5.1/migrations#modifying-columns

However the documentation also says you need to have the package doctrine/dbal installed.

[phroggyy]

@bobbybouwmann yep, I'm very well aware that the method itself is part of Laravel. However, all limitations as far as migration capability goes are part of Doctrine DBAL :)

[bobbybouwmann]

@phroggyy You said there is nothing about it in the docs, now there is ;)

[manogi]

I think I will propose a change of the docs mentioning the limitations, when I find the time...

[phroggyy]

@bobbybouwmann: what I intended to get across was that there is no docs, on Laravel, about the inability of switching between TEXT and LONGTEXT because of how Doctrine works. Sorry if that didn't come across clearly

[bobbybouwmann]

@phroggyy Fair enough! Then the documentation needs some updating ;)

[GrahamCampbell]

We're open to PRs to the documentation.

[GrahamCampbell]

Closing this as it's not an issue with laravel/framework.

[hskrasek]

@salarmehr I don't have the query handy, but in the migration I just did a raw DB::statement()

[phroggyy]

@salarmehr: for what exactly? This is a fairly old issue so I can't say I have everything readily available

[andfelzapata]

I was having this exact problem trying to change $table->string('...') to $table->longText('..'), it didn't work in Laravel 5.0.

But trying out:

$table->string('column', 4294967295)->change();

updated the field to LONGTEXT(4294967295).

[dalenguyen]

Hi, I updated from
$table->float('amount');
to
$table->decimal('amount', 10, 2)->change();

When I run php artisan migrate, it says nothing to migrate. Is there anything that I should do to make it right? Thanks,

[andfelzapata]

@dalenguyen You have to do migrate:rollback, that is if you have the down method defined in the corresponding migration. If you don't have that method setup, with care delete the corresponding row for the migration in your migrations table. Are you editing an existing migration file ? or did you create a new one ?

[dalenguyen]

Thank @andfelzapata . I am editing an existing file. I managed to change the column type with out rollback. You can read it here: http://techcater.com/update-column-types-laravel-5-2/

[Kender2]

Opened pull request 2788 to document unchangeable column types.

[subratpalhar92]

use Doctrine\DBAL\Types\StringType; use Doctrine\DBAL\Types\Type;

public function up() {
if (!Type::hasType('char')) {
Type::addType('char', StringType::class);
}

Schema::table('tablename', function (Blueprint $table) {
    $table->char('field', 1)->nullable()->change();
});

}

[ankerdev]

@subratpalhar92 That solved the issue I was having. Cheers!

[IllyaMoskvin]

@subratpalhar92 Pretty sure that results in a VARCHAR column being created, not CHAR.

[baijunyao]

@IllyaMoskvin is right, I use the following method.

DB::statement('ALTER TABLE oauth_clients CHANGE id id CHAR(36) NOT NULL');

[wturrell]

Could I just confirm this is still not possible with ->change() in Laravel 7+ too?
(I have an application still running 5.8.38 with doctrine/dbal 2.10.2)