Doctrine 2.5 query logging: Unknown column type "2" requested

Question

Apologies for the long question.

I have a ZF3 based application that uses Doctrine.

I'm trying to log the "final" query that is sent to the MySQL server to the application log. For this purpose, I have implemented a solution based on this particular answer.

Note: I am aware of ZF Debug Bar, but I need to log the queries to the application log for production debugging, if needed.

However, in some situations, the logger fails with a fatal error:

Unknown column type "2" requested. Any Doctrine type that you use has to be registered with \Doctrine\DBAL\Types\Type::addType(). You can get a list of all the known types with \Doctrine\DBAL\Types\Type::getTypesMap(). If this error occurs during database introspection then you might have forgot to register all database types for a Doctrine Type. Use AbstractPlatform#registerDoctrineTypeMapping() or have your custom types implement Type#getMappedDatabaseTypes(). If the type name is empty you might have a problem with the cache or forgot some mapping information.

Strange part is that the error appears in the logger alone - the query executes just fine if I disable the logger.

My implementation looks like this (I use UUIDs for primary keys):

'doctrine' => [
    'configuration' => [
        'orm_default' => [
            'types' => [
                UuidType::NAME => UuidType::class
            ],
            'sql_logger' => DoctrineLog::class
        ]
    ],
]


class DoctrineLog extends DebugStack
{
    public function startQuery($sql, array $params = null, array $types = null) {
        parent::startQuery($sql, $params, $types);

        // If there are no parameters set, just log the query as-is
        if (empty($params)) {
            Log::debug('Executing query: ', $sql, ';');
            return;
        }

        $convertedSql = str_replace('?', '%s', $sql);
        $convertedParams = [];

        foreach ($params as $index => $param) {
            $type = Type::getType($types[$index]);
            $value = $type->convertToDatabaseValue($param, self::$dbPlatform);
            $convertedParams[] = var_export($value, true);
        }

        Log::debug('Executing query: ', sprintf($convertedSql, ...$convertedParams), ';');
    }
}

I investigated a bit and I have found that one particular query that fails when run through the logger is built using the QueryBuilder:

$queryBuilder->select('m')
    ->from(Mod::class, 'm')
    ->where('m.userId = :userId')
    ->orderBy('m.dateCreated', 'desc')
    ->setParameter('userId', $userId);

If I create the same query using the findBy repository method, it logs properly:

$entityManager->getRepository(Mod::class)->findBy(['userId' => $user->getId()], ['dateCreated' => 'desc']);

I have narrowed it down to the $types parameter that is passed to the startQuery method in my DoctrineLog class.

When the query logs properly (when created using the findBy repository method), the $types array looks like this:

[
    0 => 'uuid'
]

When the query fails to log (when created using the QueryBuilder), the $types array looks like this:

[
    0 => 2
]

I have absolutely no clue what that 2 is supposed to mean. Like I said, the strange part is that the query executes successfully, regardless of how it is created.

While for this particular query I could dump the QueryBuilder, this failure also happens when running a update query, so I need to either find the cause or find another way of logging the whole query.

Any pointers would be appreciated.

Note: the types list, as returned by \Doctrine\DBAL\Types\Type::getTypesMap() is this:

[
    [array] => Doctrine\DBAL\Types\ArrayType
    [simple_array] => Doctrine\DBAL\Types\SimpleArrayType
    [json_array] => Doctrine\DBAL\Types\JsonArrayType
    [object] => Doctrine\DBAL\Types\ObjectType
    [boolean] => Doctrine\DBAL\Types\BooleanType
    [integer] => Doctrine\DBAL\Types\IntegerType
    [smallint] => Doctrine\DBAL\Types\SmallIntType
    [bigint] => Doctrine\DBAL\Types\BigIntType
    [string] => Doctrine\DBAL\Types\StringType
    [text] => Doctrine\DBAL\Types\TextType
    [datetime] => Doctrine\DBAL\Types\DateTimeType
    [datetimetz] => Doctrine\DBAL\Types\DateTimeTzType
    [date] => Doctrine\DBAL\Types\DateType
    [time] => Doctrine\DBAL\Types\TimeType
    [decimal] => Doctrine\DBAL\Types\DecimalType
    [float] => Doctrine\DBAL\Types\FloatType
    [binary] => Doctrine\DBAL\Types\BinaryType
    [blob] => Doctrine\DBAL\Types\BlobType
    [guid] => Doctrine\DBAL\Types\GuidType
    [uuid] => Ramsey\Uuid\Doctrine\UuidType
]

Answer 1

I had the exact same error message. Eventually, I figured out that that the type I was passing in one of my entities for ClassMetadataBuilder->addField( 'trace', ParameterType::STRING ); was indeed "2" ( the ParameterType::STRING). Make sure to use type from \Doctrine\DBAL\Types\Types (like Types::uuid) or atleast define a type your self like it states in the error.