Today one of my colleague’s was debugging a strange issue with Doctrine’s schema validation tool which caused our test setup to fail (we’re running app/console doctrine:schema:validate as part of our CI process). The output was the same every time:
|
1 2 3 |
$ app/console doctrine:schema:validate [Mapping] OK - The mapping files are correct. [Database] FAIL - The database schema is not in sync with the current mapping file. |
We quickly discovered the issue was caused by the custom UUID Doctrine DBAL type we introduced lately. This type was based on some gist we found on the web:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
<?php namespace BuboBox\Doctrine2\DBAL\Types; use Doctrine\DBAL\Types\Type; use Doctrine\DBAL\Platforms\AbstractPlatform; /** * Type that maps a PHP array to a clob SQL type. * * @since 2.0 */ class UuidType extends Type { const BINARY = 'binary'; public function getSqlDeclaration(array $fieldDeclaration, AbstractPlatform $platform) { return sprintf('BINARY(%d)', $fieldDeclaration['length']); } public function getName() { return self::BINARY; } public function convertToPhpValue($value, AbstractPlatform $platform) { if ($value !== null) { $value= unpack('H*', $value); $hash = array_shift($value); $uuid = substr($hash, 0, 8) . '-' . substr($hash, 8, 4) . '-' . substr($hash, 12, 4) . '-' . substr($hash, 16, 4) . '-' . substr($hash, 20, 12); return $uuid; } } public function convertToDatabaseValue($value, AbstractPlatform $platform) { if ($value !== null) { return pack('H*', str_replace('-', '',$value)); } } } |
It turned out that doing a doctrine:schema:update kept executing the same update query over and over again:
|
1 2 |
$ app/console doctrine:schema:update --dump-sql ALTER TABLE Group CHANGE uuId uuId BINARY(16) DEFAULT NULL; |
One hour of debugging later I discovered the issue originated to MySqlSchemaManager::_getPortableTableColumnDefinition where a MySQL column gets reverse-engineered into a Doctrine\DBAL\Types\Type which is used in the schema comparison tool. We’re storing the UUID in a BINARY(16) field which results in a BinaryType after reverse-engineering because Doctrine can’t tell the difference between a BinaryType and UuidType because both result in the exact same MySQL column definition. However, there is a solution to fix this.
Using DC2Type in the comment
The solution is to add a comment to the field to store the metadata in. This seems to be missing in the docs but I’ve found some JIRA issue describing the feature. We have to change our column definition so the metadata of the type doesn’t get lost:
|
1 |
ALTER TABLE Group CHANGE uuId uuId BINARY(16) COMMENT '(DC2Type:uuid)' DEFAULT NULL; |
The corrected UuidType looks as following:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 |
namespace Wb\Component\Doctrine\DBAL\Types; use Doctrine\DBAL\Types\BinaryType; use Doctrine\DBAL\Types\Type; use Doctrine\DBAL\Platforms\AbstractPlatform; /** * Type that maps a PHP array to a clob SQL type. * * @since 2.0 * @link https://gist.github.com/Sitebase/5013494 */ class UuidType extends BinaryType { /** * @param array $fieldDeclaration * @param AbstractPlatform $platform * @return string */ public function getSqlDeclaration(array $fieldDeclaration, AbstractPlatform $platform) { return sprintf('BINARY(%d) COMMENT \'(DC2Type:uuid)\'', $fieldDeclaration['length']); } /** * @return string */ public function getName() { return 'uuid'; } /** * @param mixed $value * @param AbstractPlatform $platform * @return string */ public function convertToPhpValue($value, AbstractPlatform $platform) { if ($value !== null) { $value= unpack('H*', $value); $hash = array_shift($value); $uuid = substr($hash, 0, 8) . '-' . substr($hash, 8, 4) . '-' . substr($hash, 12, 4) . '-' . substr($hash, 16, 4) . '-' . substr($hash, 20, 12); return $uuid; } } /** * @param mixed $value * @param AbstractPlatform $platform * @return string */ public function convertToDatabaseValue($value, AbstractPlatform $platform) { if ($value !== null) { return pack('H*', str_replace('-', '', $value)); } } } |
Unfortunately the binary type is always reverse-engineered with $fixed = true so you have to configure the UuidType accordingly (note the options) on your entities (haven’t found a better way yet):
|
1 2 3 4 5 6 7 8 9 10 11 |
<?php class Group { /** * @var string $uuId * * @ORM\Column(type="uuid", length=16, options={"fixed"=true}, nullable=true) */ private $uuId; } |
This comment stuff in Doctrine is not well-documented and probably a lot of people experienced the same behaviour in the schema tool so I hope I prevented some nasty debug sessions in Doctrine’s core (although you learn a lot from it).