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
]
