framework: [Bug] Schema builder - renameColumn fails on table with enum columns

Workaround, during development (if there exists no important data): Drop the column and add a new one.

public function up()
{
    Schema::table('users', function(Blueprint $table)
    {
        $table->dropColumn('name');
    });

    Schema::table('users', function(Blueprint $table)
    {
        $table->text('username');
    });
}

This worked for me in laravel 5.2 with doctrine/dbal@^2.5 . When you have an enum on your table and you want to change any of the columns on the table you will have to:

 public function up()
    {
       Schema::getConnection()->getDoctrineSchemaManager()->getDatabasePlatform()->registerDoctrineTypeMapping('enum', 'string');

        Schema::table('jobs', function(Blueprint $table)
        {
            $table->decimal('latitude', 10, 6)->nullable()->change();
        });
    }

The docs aren’t correct, they say renaming enum columns isn’t support, but this actually happens when renaming any column on a table that contains an enum column.

It looks like the “workaround” for this is to just tell Doctrine an enum is a string: http://wildlyinaccurate.com/doctrine-2-resolving-unknown-database-type-enum-requested

I’ve attempted to do this in Laravel by doing the following, but the issue still occurs:

$platform = Schema::getConnection()->getDoctrineSchemaManager()->getDatabasePlatform();
$platform->registerDoctrineTypeMapping('enum', 'string');

I’m not entirely sure how Laravel interacts with Doctrine so I might grabbing the wrong platform instance (or adding it too late?). Any insight on how to make this work inside of Laravel?

3 years later and Doctrine hasn’t fix this issue which is a shame.

Still hitting this issue 3 years later, having to use the manual DB statement solution which is a shame.

This is something I could not believe at first. But thanks to Paul Bill I did it as following

public function up()
{
    Schema::table('projects', function(Blueprint $table)
    {
           DB::statement('ALTER TABLE projects CHANGE slug url VARCHAR(200)');
    });
}

public function down()
{
    Schema::table('projects', function(Blueprint $table)
    {
           DB::statement('ALTER TABLE projects CHANGE url slug VARCHAR(200)');
    });
}

Can someone managing this GitHub repository please summarize the current state of this issue?

My own opinion is that sure, monkey patching isn’t desirable, but it’s quite often necessary in our field. Newcomers to Laravel are hitting this problem in large numbers. This is bad for the reputation of the product.

Yes Doctrine should fix it, but it has been three years. Now it falls on the Laravel framework to create an intuitive development experience for its users.

(PS - this is an amazing framework and thank you everyone for your contributions to it. I’m sure we can find a solution for this simple problem.)

As @shnhrrsn pointed out, the docs are wrong and still haven’t changed (7 months after being reported, and it’s a fairly common issue for anyone new to laravel). The issue is renaming ANY column on a table that has an enum.

Please reopen issue! Laravel v5.4.25 problem still exists

I think doctrine is gone in 4.1 😃

On 22 Nov 2013, at 2:01 am, Eric Junker notifications@github.com wrote:

+1 just ran into this issue. I know it is a Doctrine DBAL issue but I wonder if there is something Laravel can do to provide a workaround.

— Reply to this email directly or view it on GitHub.

Gotta say there are some silly comments on this thread.

If you’re not using the framework anymore why are you bothering to write a tirade on here about why it doesn’t work for you?

Also, these are free, open source projects. That means 2 things:

1. The developers don’t get an immediate financial reward/incentive to help contribute towards ongoing development. Any funds they do get usually barely covers the investment they’ve already made and is likely going towards more big-hitting features that will benefit many more people (you included).

2. There’s nothing and no one stopping you from writing your own alternative based on DBAL or Laravel that works the way you want it to. Heck you don’t even have to take the time or trouble to share it back with the community. It literally only takes some time and a little effort.

So it really is pointless coming on here just to vent about that thing you want for those occasional projects that need it that clearly many folks can go without.

And it’s a bit childish to slam a perfectly good framework that is working very well in a lot of cases for a lot of people because you have a little issue that’s been around for a few years.

A lot of things have been failing for a lot longer than DBAL hasn’t been able to edit tables with enum columns. I mean my toaster still can’t toast 2 pieces of bread consistently on the same setting. That’s been at least 30 years. Let’s keep some perspective.

I ended up using @ISaidHey 's suggestion, this is my take:

config/database.php

//....
   'types_mapping' => [
        'ltree' => 'string',
    ],

app/Providers/AppServiceProvider.php

class AppServiceProvider extends ServiceProvider
{
    public function boot()
    {
        // add DBAL Types
        $this->addDBALTypes();
    }
    public function addDBALTypes()
    {
        $connection = DB::connection();
        if (App::runningInConsole()) {
            //workaround for php artisan migrate --database=
            if (isset($_SERVER['argv'][2]) && strpos($_SERVER['argv'][2], '--database=') !== false) {
                $connection = DB::connection(str_replace('--database=', '', $_SERVER['argv'][2]));
            }
        }
        $doctrineConnection = $connection->getDoctrineConnection();
        $dbPlatform = $doctrineConnection->getSchemaManager()->getDatabasePlatform();
        foreach (config('database.types_mapping', []) as $dbType => $doctrineType) {
            $dbPlatform->registerDoctrineTypeMapping($dbType, $doctrineType);
        }
    }
}

I believe it wouldn’t hurt if laravel could parse the types_mapping array by default in the migrate command

@zschuessler feel free to make a PR to fix the issue if you can do it.

Heck here’s an even more temporary one

    $map = [
        'enum' => 'string',
    ];
    $connection = DB::connection();
    $doctrineConnection = $connection->getDoctrineConnection();
    // New code here
    $dbPlatform = $doctrineConnection->getSchemaManager()->getDatabasePlatform();
    foreach ($map as $dbType => $doctrineType) {
        $dbPlatform->registerDoctrineTypeMapping($dbType, $doctrineType);
    }
    

This still exists, just downloaded Laravel 5.5.18. I think the bug is bad enough but the misleading error message is worse, given that i’m not even trying to rename the enum column in my table.

[Doctrine\DBAL\DBALException]
Unknown database type enum requested, Doctrine\DBAL\Platforms\MySQL57Platform may not support it.

For those who are looking for an workaround, I have a better solution. Instead of using Schema, use Query Builder and write SQL Query for those rename operations.

I don’t get why everyone suggests just register the mapping as string. Wouldn’t that ruin your enum column when you rename another column on the table?

When I tried to use change got same issue on Laravel 5 and doctrine/dbal ~2.5.0. But I solved the problem with using raw query like this:

DB::statement("ALTER TABLE table_name MODIFY COLUMN column_name ENUM('Here','is','choices')");

I see that in Laravel 6 that has been released recently, there is still this non-sense in the documentation.

I think that 1 viable solution for those who have data in their tables would be:

• create a new varchar column enum_col_2
• copy the data from enum_col
• drop the enum_col
• rename your column
• recreate the old enum_col
• copy the data from enum_col_2 to enum_col
• drop the enum_col_2

If you come across this comment, I think this will help you.

You must have same question as I and other think about this problem and don’t want to use DB statement. I’m in same boat as you so I search all in google and found solution that I think it is best practise right now. I use Laravel 5.3. Current DBAL hash some update and depend on @henritoivar solution only is not work. DBAL will throw error that you need to define type in DBAL. They need sql processor.

First. Please read all this page. http://doctrine-orm.readthedocs.io/projects/doctrine-orm/en/latest/cookbook/mysql-enums.html This is doc that talk about why doctrine don’t implement about enum on it.

Second. I will keep it short. This is my implement.

<?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
use Doctrine\DBAL\Types\Type;

class AlterCartCustomersMakeGenderNullable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::getConnection()->getDoctrineSchemaManager()->getDatabasePlatform()->registerDoctrineTypeMapping('enum', 'string');

        Type::addType('enum', \Extended\Doctrine\DBAL\Types\GenderEnumType::class);

        Schema::table('cart_customers', function(Blueprint $table) {
            // add nullable
            // must call change to tell we change field attribute
            $table->enum('gender', ['male', 'female'])->nullable()->change();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::getConnection()->getDoctrineSchemaManager()->getDatabasePlatform()->registerDoctrineTypeMapping('enum', 'string');

        Type::addType('enum', \Extended\Doctrine\DBAL\Types\GenderEnumType::class);

        Schema::table('cart_customers', function(Blueprint $table) {
            // revert nullable
            $table->enum('gender', ['male', 'female'])->nullable(false)->change();
        });
    }
}

<?php

namespace Extended\Doctrine\DBAL\Types;

use Doctrine\DBAL\Types\Type;
use Doctrine\DBAL\Platforms\AbstractPlatform;

class EnumType extends Type
{
    protected $name;
    protected $values = array();

    public function getSQLDeclaration(array $fieldDeclaration, AbstractPlatform $platform)
    {
        $values = array_map(function($val) { return "'".$val."'"; }, $this->values);

        return "ENUM(".implode(", ", $values).")";
    }

    public function convertToPHPValue($value, AbstractPlatform $platform)
    {
        return $value;
    }

    public function convertToDatabaseValue($value, AbstractPlatform $platform)
    {
        if (!in_array($value, $this->values)) {
            throw new \InvalidArgumentException("Invalid '".$this->name."' value.");
        }
        return $value;
    }

    public function getName()
    {
        return $this->name;
    }

    public function requiresSQLCommentHint(AbstractPlatform $platform)
    {
        return true;
    }
}

<?php

namespace Extended\Doctrine\DBAL\Types;

class GenderEnumType extends EnumType
{
    protected $values = array('male', 'female');
}

After you saw these files. You may found it look stupid but this is solution right now. Problem is DBAL don’t receive another attribute so I cannot pass enum values to it.

@peppy if you have a fix in mind you can open a PR.

Hey hey here is my approach:

\Doctrine\DBAL\Types\Type::addType('enum', \Doctrine\DBAL\Types\StringType::class);

Beware this changes the column to varchar! Use your own enum class implementation or the one provided by doctrine: https://www.doctrine-project.org/projects/doctrine-orm/en/2.6/cookbook/mysql-enums.html

Either put it into a ServiceProvider or into your migrations or into your bootstrap/app.php Whatever floats your boat I used the following approach:

App/Providers/DatabaseServiceProvider.php

<?php

namespace App\Providers;

use Doctrine\DBAL\Types\StringType;
use Doctrine\DBAL\Types\Type;
use Illuminate\Support\ServiceProvider;

class DatabaseServiceProvider extends ServiceProvider
{
    public function register()
    {
        Type::addType('enum', StringType::class);
    }
}

bootstrap/app.php

$app->register(App\Providers\DatabaseServiceProvider::class);

Hope this helps someone and saves precious time I wasted reading thru a lot of discussion…

In the end none of the suggested solutions worked and doctrines error message pointed me into the right direction

FYI in 5.8 you can use something like this: Schema::registerCustomDoctrineType(Doctrine\DBAL\Types\StringType::class, 'enum', 'ENUM');

Alternatively and for sure the simplest solution is to use a raw statement which preserves your data:

DB::statement("ALTER TABLE `table` CHANGE `from` `to` ENUM('item1', 'item2') default NULL;");

This raw statements can rename the column and add change the types.

Another solution could be to set integers and map those in a config in your code!

In databases it is never a good idea to encode multiple pieces of information in one field because it can’t be indexed and is slower/more complicated to query, and makes life harder for future developers that work on it.

running into the same issue with a json blob on the table 😦

My simple solution was to use raw SQL.

public function up()
{
    DB::statement('ALTER TABLE kois ALTER doitsu SET DEFAULT 0');
    DB::statement('ALTER TABLE kois ALTER male SET DEFAULT 0');
}

Updated documentation.

not sure why people keep commenting on the laravel version. it is not a laravel problem it is a DBAL problem which is an entirely different package.

Is there a reason this isn’t getting fixed? These kinds of 3-year-still-not-fixed bugs are just insane. I’m pretty sure using enum in a table is not a rare case.

I too just came into this issue 2 days ago. ENUM is widely used and I can’t, in any way shape or form, understand why someone would think it should not be supported. Nor did I realize this was an issue that clearly causing some issues and has been sat on for over 3 years… However I’ve come across a few resources that could possibly be the fix for this issue!

http://stackoverflow.com/questions/37793349/error-on-php-artisan-migrate-laravel http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/cookbook/mysql-enums.html

Hopefully one of these are helpful!

I’ve noticed a lot of these issues are specifically related to using the renameColumn() method. However, it’s also an issue when using change() (yes, even when the column being changed is not an ENUM).

Seems the most convenient way around this (but by no means the best way) is to drop the column and re-create… unless/until Laravel is updated with a work-around or Doctrine gets updated.

Ran into this today. It seems that every call of Scheme doing a renameColumn will create an entirely new DoctrineConnection(). That means we cannot simply do a registerDoctrineTypeMapping() globally for enum. So, your options are to either add 'enum' => 'string' directly to Doctrine\DBAL\Platforms\MySqlPlatform’s initializeDoctrineTypeMappings() or modify Illuminate\Database\Connection’s getDoctrineSchemaManager() to look like this:

public function getDoctrineSchemaManager()
{
    $schema = $this->getDoctrineDriver()->getSchemaManager($this->getDoctrineConnection());
    $schema->getDatabasePlatform()->registerDoctrineTypeMapping('enum', 'string');
    return $schema;
}

It seems extremely unlikely that Doctrine is going to change, so I can only hope that @taylorotwell might think about adding this change in to Laravel. The database I am using is legacy, but I think I am going to try and move it away from enum.

I’m using Laravel 5.7 with Mariadb and wrote this migration. It worked for me.

    public function up()
    {
        $options = implode("','", MediationStatus::getValues());
        $defaulf = MediationStatus::Opened;
        $raw_sql = "ALTER TABLE mediation.mediations 
                    MODIFY COLUMN status ENUM('{$options}') 
                    DEFAULT '{$defaulf}' NOT NULL";
        DB::statement($raw_sql);
    }

@simonhamp there’s no trolling going on in this thread. People are posting because it’s a genuine issue when you cannot run your migrations because Laravel isn’t providing a simple function that could work around the issue like @G3z suggested.

In Illuminate\Database\Connection would it be possible to do something like the following in the getDoctrineConnection() method?

$this->doctrineConnection = new DoctrineConnection($data, $driver);
// New code here
if(!is_null($this->getConfig('mapping_types'))) {
    $dbPlatform = $this->doctrineConnection->getSchemaManager()->getDatabasePlatform();
    foreach($this->getConfig('mapping_types') as $dbType => $doctrineType) {
        $dbPlatform->registerDoctrineTypeMapping($dbType, $doctrineType);
    }
 }

Users would then add a ‘mapping_types’ => [] parameter to their database settings in config/database.php, i.e.:

'mapping_types' => [
                'enum'  =>  'string',
                'json'  =>  'json_array',
            ]

Just trying to get some insight if that might break anything before I do a PR. Have done the changes locally on 5.3 and it allows me to update columns on tables with the json type. Thanks for the awesome framework, been a joy to work with so far.

For who found this problem, next time I think use INT field is better solution. After I try to think why DBAL don’t do it. If I’m in same boat, I will do same too because their design pattern don’t allow them to implement ENUM.

If I can forecast or see future, this problem will stay same in 4 or 5 years. LOL

GL HF.

PS. Currently I like INT field better because I can use bitwise to store more than single value in one field.

Couldn’t laravel leave in this functionality but throw an exception if Doctrine is not loaded? I personally do not need it so I would prefer to not to include doctrine for this one feature. Another suggestion is why not just extend schema to add column renaming which uses doctrine if you need it? It seems like some others would be interested and could help maintain the package?

Has anyone had any luck with a workaround on this? Causing some major headaches.