CODEWITHSUNDEEP
debuggingdoctrinetypo3typo3-8.x
Garvin
Garvin

Reputation: 63

Use Doctrine SQLLogger in TYPO3 Backend to log all executed SQL queries

We are facing very long load times for the record list view in many pages. To understand what is causing this, we want to analyze the SQL queries executed. (Ultimately, we might want a admin panel for the backend).

Theoretically, Doctrine DBAL supports hooking SQLLogger, but we don't know where the proper Class replacement/injection for this would be.

We did find the ability to patch the file typo3/cms/typo3/sysext/core/Classes/Database/Connection.php and simply create a new method overwriting the parent:

 /**
 * Debug all the things
 *
 * If the query is parametrized, a prepared statement is used.
 * If an SQLLogger is configured, the execution is logged.
 *
 * @param string                                      $query  The SQL query to execute.
 * @param array                                       $params The parameters to bind to the query, if any.
 * @param array                                       $types  The types the previous parameters are in.
 * @param \Doctrine\DBAL\Cache\QueryCacheProfile|null $qcp    The query cache profile, optional.
 *
 * @return \Doctrine\DBAL\Driver\Statement The executed statement.
 *
 * @throws \Doctrine\DBAL\DBALException
 */
public function executeQuery($query, array $params = array(), $types = array(), QueryCacheProfile $qcp = null) {
    $fp = fopen('/var/www/html/logs/mysql.log', 'a'); fwrite($fp, '[QUERY-DOCTRINE] ' . $query . "\n"); fclose($fp);

    return parent::executeQuery($query, $params, $types, $qcp);
}

But that approach isn't really portable and requires to patch composer-generated vendor files. Also, it's lacking proper raw logging and the abilities of sophisticated SQLLoggers. So somewhere we should be able to call the Doctrine Configuration class methods?

Upvotes: 2

Views: 1148

Answers (1)

Peter Niederlag
Peter Niederlag

Reputation: 31

You can modify/extend the connection class and make the database layer use yor custom class with the config option of wrapperClass that is part of the configuration for the database connection.

work in progress @medz, just created an extension. https://packagist.org/packages/datenbetrieb/sqllog requires some config

$GLOBALS['TYPO3_CONF_VARS']['DB']['Connections']['Default']['wrapperClass']= \Datenbetrieb\Sqllog\Database\Connection::class;
$GLOBALS['TYPO3_CONF_VARS']['LOG']['Datenbetrieb']['Sqllog']['Database']['Logging']['writerConfiguration'] = array(
    \TYPO3\CMS\Core\Log\LogLevel::DEBUG => array(
        'TYPO3\\CMS\\Core\\Log\\Writer\\FileWriter' => array(),
    ),
);

Upvotes: 3

Related Questions