Dbal: Unknown database type enum, MySQL57Platform may not support it

[andrey-helldar]

Bug Report

| Q | A
|------------ | ------
| Database | MySQL 5.7.20
| Doctrine/DBAL | 2.7.1
| Framework | Laravel 5.6.22

Summary

I get an error when I try to start the migration to a field change.

Current behavior

> art migrate

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

  at /home/vagrant/code/example.loc/vendor/doctrine/dbal/lib/Doctrine/DBAL/Platforms/AbstractPlatform.php:461
    457| 
    458|         $dbType = strtolower($dbType);
    459| 
    460|         if (!isset($this->doctrineTypeMapping[$dbType])) {
  > 461|             throw new \Doctrine\DBAL\DBALException("Unknown database type ".$dbType." requested, " . get_class($this) . " may not support it.");
    462|         }
    463| 
    464|         return $this->doctrineTypeMapping[$dbType];
    465|     }

  Exception trace:

  1   Doctrine\DBAL\Platforms\AbstractPlatform::getDoctrineTypeMapping("enum")
      /home/vagrant/code/example.loc/vendor/doctrine/dbal/lib/Doctrine/DBAL/Schema/MySqlSchemaManager.php:135

  2   Doctrine\DBAL\Schema\MySqlSchemaManager::_getPortableTableColumnDefinition()
      /home/vagrant/code/example.loc/vendor/doctrine/dbal/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php:831

How to reproduce

Seeder:

public function up()
{
    Schema::table('menus', function (Blueprint $table) {
        $table->unsignedSmallInteger('order')->default(50)->change();
    });
}

The structure of the database I am trying to modify:

CREATE TABLE `menus` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `menu_id` int(10) unsigned DEFAULT NULL,
  `domain` enum('base','sales','promo') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'sales',
  `position` enum('navbar','sidebar') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'navbar',
  `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `route` json DEFAULT NULL,
  `order` smallint(5) unsigned NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

BUT if you migrate to add or remove columns, then there is no error. The error occurs when the column is changed.

[Ocramius]

Closing as duplicate. See:

• https://stackoverflow.com/a/9057352/347063
• https://www.doctrine-project.org/projects/doctrine-orm/en/2.6/cookbook/mysql-enums.html

The database platform needs to be configured to detect any custom types, and ENUMs are indeed custom.

[sgilberg]

In case anyone else comes across this and is similarly dismayed by the complexity of the solutions offered: I had a similar situation trying to do a field change in a Laravel migration (on a field that was not an enum, but in a table that had an enum in a different field). I found that adding this line to the top of the migration's up() method was sufficient to get the migration to run smoothly:

DB::connection()->getDoctrineSchemaManager()->getDatabasePlatform()->registerDoctrineTypeMapping('enum', 'string');

[skyrpex]

In case anyone else comes across this and is similarly dismayed by the complexity of the solutions offered: I had a similar situation trying to do a field change in a Laravel migration (on a field that was not an enum, but in a table that had an enum in a different field). I found that adding this line to the top of the migration's up() method was sufficient to get the migration to run smoothly:

DB::connection()->getDoctrineSchemaManager()->getDatabasePlatform()->registerDoctrineTypeMapping('enum', 'string');

Interesting... wonder if this could be done automatically by the Laravel's Schema/Connection.

[jtpdev]

I solved it, dropping the enum column, doing the change and adding the enum column again. But i'm not in production environment.

public function up()
        {
        Schema::table('m_table', function (Blueprint $table) {
            $table->dropColumn('type'); // enum column
        });

        Schema::table('m_table', function (Blueprint $table) {
            $table->integer('idstate')->nullable()->unsigned()->change();
            $table->integer('idcity')->nullable()->unsigned()->change();
        });

        Schema::table('m_table', function (Blueprint $table) {
            $table->enum('type', ['national', 'state', 'municipal'])->after('subtitle');
        });


    }

[4n70w4]

I use DB::connection()->getDoctrineSchemaManager()->getDatabasePlatform()->registerDoctrineTypeMapping('enum', 'string'); and get exception:

Illuminate\Database\QueryException  : 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 DEFAULT NULL COLLATE `utf8_unicode_ci`' at line 1 (SQL: ALTER TABLE
applications CHANGE phone phone BIGINT UNSIGNED CHARACTER SET utf8 DEFAULT NULL
COLLATE `utf8_unicode_ci`)

[sgilberg]

@4n70w4 Hm, that looks to me like an unrelated exception. I don't think BIGINT fields can have character sets (or collations) so that's probably the issue.

[radoos]

I solved it, dropping the enum column, doing the change and adding the enum column again. But i'm not in production environment.

@jtpdev Terrible idea. You'll lost the data.
Sollution provided by @sgilberg works, however there is slight chance to use wrong class :)
My PHPStorm has imported Illuminate\Support\Facades\DB by default and that's wrong.

@4n70w4 try to use following code

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

[HenryRuiz332]

In case anyone else comes across this and is similarly dismayed by the complexity of the solutions offered: I had a similar situation trying to do a field change in a Laravel migration (on a field that was not an enum, but in a table that had an enum in a different field). I found that adding this line to the top of the migration's up() method was sufficient to get the migration to run smoothly:

DB::connection()->getDoctrineSchemaManager()->getDatabasePlatform()->registerDoctrineTypeMapping('enum', 'string');

thanks, you are my hero

[andrei-dascalu]

@HenryRuiz332 @radoos Not sure if anyone is still paying attention to this, but I've ran into this in an unrelated scenario:

1. Using doctrine/migrations standalone, no framework
2. Registering the connectio in an cli-config.php file:

use Symfony\Component\Console\Helper\HelperSet;
use Doctrine\DBAL\Tools\Console\Helper\ConnectionHelper;
use Doctrine\ORM\Tools\Console\Helper\EntityManagerHelper;

$entityManager = get_entity_manager(); //my own function, just building an entity manager here
$conn = $entityManager->getConnection();
$platform = $conn->getDatabasePlatform();

$platform->registerDoctrineTypeMapping('enum', 'string');

return new HelperSet([
    'em' => new EntityManagerHelper($entityManager),
    'db' => new ConnectionHelper($conn),
]);

1. If I dump the platform (an Doctrine\DBAL\Platforms\MySQL57Platform instance), it has an 'enum' =>
   string(6) "string" entry in the mappings array.

However, I still get the error.

As a side-note, I am not using any enum in my own models, however the DB has been modified by PMA when I dumped the configuration table it needs and it has an enum field somewhere. If I remove those tables, all is ok.

[mklef121]

I solve my problem by doing this in the constructor of the migration file
//This two has to be together to fix the enum issue

```
DB::getDoctrineSchemaManager()->getDatabasePlatform()->registerDoctrineTypeMapping('enum', 'string');

Type::hasType('enum') ? Type::hasType('enum') : Type::addType('enum', StringType::class);
```

Do well to import all class types

[AlterwebStudio]

I also came to this issue when I needed to add new column to table that previously contained "enum" column.
I solved this by removing collation for every column from default to empty string:

public function up()
{
    Schema::table('education_activity_type', function (Blueprint $table) {
        $table->smallInteger('column_name')->collation('')->nullable()->change();
    });
}

[nuryagdym]

I faced the same issue after defining custom enum type as described in doctrine 2.x documentation (I implemented the second solution in the documentation) on Symfony 4.4 .
After defining the enum type and migration worked ok. However, I faced this error with consecutive migrations.
To solve I created an empty migration file:

final class Version20200508000000 extends AbstractMigration
{
    public function __construct(Version $version)
    {
        parent::__construct($version);
        //this lines solves error, solution provided by @sgilberg 
        $this->connection->getSchemaManager()->getDatabasePlatform()->registerDoctrineTypeMapping('enum', 'string');
    }

    public function getDescription() : string
    {
        return '';
    }

    public function up(Schema $schema) : void
    {
        // this up() migration is auto-generated, please modify it to your needs
    }

    public function down(Schema $schema) : void
    {
        // this down() migration is auto-generated, please modify it to your needs
    }
}

You don't have to add
$this->connection->getSchemaManager()->getDatabasePlatform()->registerDoctrineTypeMapping('enum', 'string');
this line to every migration file. Single migration file containing this line is enough.

_There should be a better solution for this problem_

[BoGnY]

On Laravel this fix the issue:

use Doctrine\DBAL\Types\Types;
use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;

class YourEditClass extends Migration
{
    public function __construct()
    {
        DB::getDoctrineSchemaManager()->getDatabasePlatform()->registerDoctrineTypeMapping('enum', Types::STRING);
    }
}