Symfony, Doctrine and MariaDB

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

Let’s talk about Symfony, Doctrine and MariaDB today. This is about an issue a trainee I’m mentoring stumbled over recently.

So what is going on here apart from the discussion about MySQL being mainly run by Oracle and MariaDB being “true” to the spirit of Open Source software?

Where all things are NULL and not NULL

When using Symfony with Doctrine and MariaDB and migrations, things suddenly got wacky.

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 other properties to make the example a little nicer. Generating a migration using make:migration we will find ourself 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'
        );
    }

    ...
}

That looks perfectly fine, does it? Yes, it does. Apart from the migration blatantly ignoring that we could use the DBAL Schema abstractions instead of dumping the whole SQL. More on that late, for now I suggest reading the Doctrine Schema documentation. That is if you e.g. use a code sniffer or prefer readable code in general.

Now we continue our work, build new entities and create 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', thus whenever schema changes are inspected Doctrine DBAL will see your schema defined as unquoted NULL value but MariaDB will report back the quoted variant.

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

Fixing the issue at hand is quite simple but not very much advertised.

  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 lead to my trainee to throw around curses. 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 thus 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 grey hairs. The incredible flexibility we gain by using Symfony framework and components also means we have to pay attention to tiny details.

My trainee is a bit wiser and I am smirked quite a lot about this one.

Comments powered by Disqus