[DoctrineBridge] Ulid is not converted to correct database type in queries #39135
Description
Symfony version(s) affected: 5.2.0-RC2 (PHP 7.4.11 MySQL 8.0.22)
Description
I think this was an issue on RC1 as well but I only discovered it last night. When I run doctrine queries the Ulid field of parameters in the query are not being converted into the format of the corresponding column in the database meaning you don't get any matches.
The tldr; is that it is just passing the ulid in as a string when running queries and not converting it to the format in which it is stored in the database binary.
I have the following entities:
<?php
declare(strict_types=1);
namespace App\Domain\Entity;
use Doctrine\ORM\Mapping as ORM;
use Symfony\Bridge\Doctrine\IdGenerator\UlidGenerator;
use Symfony\Component\Uid\Ulid;
/**
* @ORM\Entity
* @ORM\Table(uniqueConstraints={
* @ORM\UniqueConstraint(name="unique_organisation_member", columns={"user_ulid", "organisation_ulid"})
* })
*/
class OrganisationMember
{
/**
* @ORM\Id
* @ORM\Column(type="ulid", unique=true)
* @ORM\GeneratedValue(strategy="CUSTOM")
* @ORM\CustomIdGenerator(class=UlidGenerator::class)
* @var Ulid|null
*/
public ?Ulid $ulid = null;
/**
* @ORM\ManyToOne(targetEntity="User", fetch="EAGER")
* @ORM\JoinColumn(name="user_ulid", referencedColumnName="ulid")
*/
public User $user;
/**
* @ORM\ManyToOne(targetEntity="Organisation", fetch="EAGER")
* @ORM\JoinColumn(name="organisation_ulid", referencedColumnName="ulid")
*/
public Organisation $organisation;
}
and
<?php
declare(strict_types=1);
namespace App\Domain\Entity;
use Doctrine\ORM\Mapping as ORM;
use Serializable;
use Symfony\Component\Uid\Ulid;
use Symfony\Bridge\Doctrine\IdGenerator\UlidGenerator;
use Symfony\Component\Security\Core\User\UserInterface;
/**
* @ORM\Entity
*/
class User implements UserInterface, Serializable
{
/**
* @ORM\Id
* @ORM\Column(type="ulid", unique=true)
* @ORM\GeneratedValue(strategy="CUSTOM")
* @ORM\CustomIdGenerator(class=UlidGenerator::class)
* @var Ulid|null
*/
public ?Ulid $ulid = null;
...
}
And this migration:
<?php
declare(strict_types=1);
namespace DoctrineMigrations;
use Doctrine\DBAL\Schema\Schema;
use Doctrine\Migrations\AbstractMigration;
/**
* Auto-generated Migration: Please modify to your needs!
*/
final class Version20201121100147 extends AbstractMigration
{
public function getDescription() : string
{
return '';
}
public function up(Schema $schema) : void
{
// this up() migration is auto-generated, please modify it to your needs
$this->addSql('CREATE TABLE organisation (ulid BINARY(16) NOT NULL COMMENT \'(DC2Type:ulid)\', name VARCHAR(140) NOT NULL, PRIMARY KEY(ulid)) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB');
$this->addSql('CREATE TABLE organisation_member (ulid BINARY(16) NOT NULL COMMENT \'(DC2Type:ulid)\', user_ulid BINARY(16) DEFAULT NULL COMMENT \'(DC2Type:ulid)\', organisation_ulid BINARY(16) DEFAULT NULL COMMENT \'(DC2Type:ulid)\', INDEX IDX_F2FF2065B8098490 (user_ulid), INDEX IDX_F2FF206580CCC841 (organisation_ulid), UNIQUE INDEX unique_organisation_member (user_ulid, organisation_ulid), PRIMARY KEY(ulid)) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB');
$this->addSql('CREATE TABLE user (ulid BINARY(16) NOT NULL COMMENT \'(DC2Type:ulid)\', email VARCHAR(254) NOT NULL, password VARCHAR(64) NOT NULL, is_active TINYINT(1) NOT NULL, UNIQUE INDEX UNIQ_8D93D649E7927C74 (email), PRIMARY KEY(ulid)) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB');
$this->addSql('ALTER TABLE organisation_member ADD CONSTRAINT FK_F2FF2065B8098490 FOREIGN KEY (user_ulid) REFERENCES user (ulid)');
$this->addSql('ALTER TABLE organisation_member ADD CONSTRAINT FK_F2FF206580CCC841 FOREIGN KEY (organisation_ulid) REFERENCES organisation (ulid)');
}
public function down(Schema $schema) : void
{
// this down() migration is auto-generated, please modify it to your needs
$this->addSql('ALTER TABLE organisation_member DROP FOREIGN KEY FK_F2FF206580CCC841');
$this->addSql('ALTER TABLE organisation_member DROP FOREIGN KEY FK_F2FF2065B8098490');
$this->addSql('DROP TABLE organisation');
$this->addSql('DROP TABLE organisation_member');
$this->addSql('DROP TABLE user');
}
}
In organisation_member I have this data:
ulid,user_ulid,organisation_ulid
0x0175EA4467C31B71469CABA912AB4D93,0x0175EA4402E2A586339ABAE13FB288B9,0x0175EA4467B0B0371DA5727C278649AD
In user I have this data:
ulid,...
0x0175EA4402E2A586339ABAE13FB288B9,...
When I run this query
/** @var User $user */
$user = $this->security->getUser();
$results = $this->entityManager->createQueryBuilder()
->select('om')
->from(OrganisationMember::class, 'om')
->where('om.user = :user')
->setParameter('user', $user)
->setMaxResults(1)
->getQuery()
->getResult();
I get back 0 results even though the data is there. When I take a look in MySQL general_log I can see the following query was executed:
SELECT o0_.ulid AS ulid_0, o0_.user_ulid AS user_ulid_1, o0_.organisation_ulid AS organisation_ulid_2 FROM organisation_member o0_ WHERE o0_.user_ulid = '01EQN480Q2MP3376NTW4ZV525S' LIMIT 1
It is just passing the ulid in as a string and not converting it to the format in which it is stored in the database (binary)
If I manually run the query but substitute the parameter that doctrine used for the correctly encoded parameter I get one result back as expected:
SELECT o0_.ulid AS ulid_0, o0_.user_ulid AS user_ulid_1, o0_.organisation_ulid AS organisation_ulid_2 FROM organisation_member o0_ WHERE o0_.user_ulid = uuid_to_bin('0175ea44-02e2-a586-339a-bae13fb288b9') LIMIT 1
How to reproduce
Detailed steps above but the short version:
- Create Entity with with ulid id field
- Create Another Entity and associate it with previous entity
- Insert a record into the table for the second entity correctly associated with a record in the table for the first entity
- Switch on query logging in mysql by running
SET GLOBAL general_log = 'ON'; SET GLOBAL log_output = 'TABLE'; - Run a DQL query filtering results of entity 1 by association with an instance of entity 2 (see above example)
- Note that 0 results are returned
- Take a look at mysql.general_log table and locate your query
- Note that it is searching by the ulid string which is not how the property is encoded in the database and hence does not return any results
Possible Solution
We need to bind uuid types by converting the value to binary and then using the binary parameter type.
Additional context
#39113 was merged for RC2 to change the way ulid is stored in the mysql from a uuid string to uuid binary however I also observed this same issue in RC1 where the data was stored in the db as uuid string but the query parameter was encoded as ulid.