Symfony, Doctrine and MariaDB

Jun 12, 2018 00:00 · 566 words · 3 minutes read symfony

Recently a trainee I have been mentoring stumbled over an issue within Symfony, Doctrine, and MariaDB. Usually, when one person has a problem with it, several people do.

The issue arose when we implemented a project using MariaDB (the free, Open Source successor to MySQL). As it turns out, the drop-in replacement behaves different.

Where all things are NULL and not NULL

When using Symfony with Doctrine, Maria DB, and migrations, things start to behave strangely.

Let’s assume the a User entity which can have a nullable password property:

<?php

namespace App\Entity;

use Doctrine\ORM\Mapping as ORM;
use Gedmo\Mapping\Annotation as Gedmo;
use Symfony\Component\Security\Core\User\EquatableInterface;
use Symfony\Component\Security\Core\User\UserInterface;
use Symfony\Component\Validator\Constraints as Assert;

class User implements UserInterface, EquatableInterface, \Serializable
{
...

    /**
     * @var string
     *
     * @ORM\Column(type="string", nullable=true)
     */
    protected $password;

...
}

I’ve omitted some properties in order to make this example clearer. Generating a migration using make:migration we will find ourselves with the expected result.

final class Version20180612115042 extends AbstractMigration
{
    public function up(Schema $schema): void
    {
        // this up() migration is auto-generated, please modify it to your needs
        $this->abortIf(
            'mysql' !== $this->connection->getDatabasePlatform()->getName(),
            'Migration can only be executed safely on \'mysql\'.'
        );

        $this->addSql(
            'CREATE TABLE users (id INT UNSIGNED AUTO_INCREMENT NOT NULL, username VARCHAR(255) NOT NULL, password VARCHAR(255) DEFAULT NULL, email VARCHAR(255) NOT NULL, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB'
        );
    }

    ...
}

This may look and work perfectly fine. Assuming you blatantly ignore the migration or that we could use the DBAL Schema abstractions instead of dumping the whole SQL. More on that later. In the meantime, I suggest reading the Doctrine Schema documentation if you use a code sniffer or prefer readable code.

Now we continue on with our work building new entities and creating another migration using: make:migration

final class Version20180612201802 extends AbstractMigration
{
    public function up(Schema $schema): void
    {
        ...

        $this->addSql(
            'ALTER TABLE users CHANGE password password DEFAULT NULL'
        );
    }

    ...
}

Wait! What just happened here? We previously saw the password column being defined with a NULL default.

Fix it, Felix

MariaDB decided it was time to represent NULL values in the information schema as 'NULL'. Whenever schema changes are inspected, Doctrine DBAL will see your schema defined as an unquoted null value, but Maria DB will report back with the quoted variant.

The Doctrine DBAL project has an interesting pull request detailing the cause.

Fortunately, fixing this issue is quite simple.

  1. make sure you have upgraded the doctrine/dbal package to any version of 2.7.
  2. make sure you use MariaDB >= 10.2.7
  3. fix your Doctrine configuration

The last step is what led to my trainee to start cursing. By default the Symfony Flex recipe for the Doctrine Bundle assumes we use MySQL (see Doctrine default recipe).

doctrine:
    dbal:
        # say "hi" to a friend!
        server_version: '5.7'

This forces Doctrine to use MySQL 5.7 grammer and will repeatedly create new migrations. The fix is easy:

doctrine:
    dbal:
        # ooops, really?!
        server_version: 'mariadb-10.2.14'

We either have to specify the database version with a mariadb- prefix or just completely remove / comment the server_version setting.

TL;DR

Sometimes small things in Symfony can cause some hairs to turn grey. The incredible flexibility we gain by using Symfony framework and its components also means we have to pay attention to tiny details.

My trainee is a bit wiser than I, yet even I was irked quite a bit about this nuance.

Comments powered by Disqus