Objective: Log all queries to a text file.
Problem: I have multiple database connection services and not all queries are being logged.
I have two database configurations
...
$di->set('db', function () use ($di, $config) {
$eventsManager = new EventManager();
//Get a shared instance of the DbProfiler
$profiler = $di->getProfiler();
//Listen to all the database events
$eventsManager->attach('db', function($event, $connection) use ($profiler) {
if ($event->getType() == 'beforeQuery') {
$profiler->startProfile($connection->getSQLStatement());
}
if ($event->getType() == 'afterQuery') {
$profiler->stopProfile();
}
});
$section = 'database_' . ENV;
$connection = new DbAdapter(array(
'host' => $config->{$section}->host,
'username' => $config->{$section}->username,
'password' => $config->{$section}->password,
'dbname' => $config->{$section}->dbname
));
$connection->setEventsManager($eventsManager);
return $connection;
});
$di->set('db_old', function () use ($di, $config) {
$eventsManager = new EventManager();
//Get a shared instance of the DbProfiler
$profiler = $di->getProfiler();
//Listen to all the database events
// $eventsManager->attach('db_old', function($event, $connection) use ($profiler) { // First attempt using "db_old"
$eventsManager->attach('db', function($event, $connection) use ($profiler) {
if ($event->getType() == 'beforeQuery') {
$profiler->startProfile($connection->getSQLStatement());
}
if ($event->getType() == 'afterQuery') {
$profiler->stopProfile();
}
});
$section = 'database_' . ENV . '_old';
$connection = new DbAdapter(array(
'host' => $config->{$section}->host,
'username' => $config->{$section}->username,
'password' => $config->{$section}->password,
'dbname' => $config->{$section}->dbname
));
$connection->setEventsManager($eventsManager);
return $connection;
});
...
All of my controllers extend ControllerBase.php in which I have this:
...
public function afterExecuteRoute($dispatcher) {
$config = $this->getDi()->get('config');
$logs_dir = realpath($config->application->logsDir);
$logger = new \Phalcon\Logger\Adapter\File($logs_dir . '/query_log.log');
$profiles = $this->getDi()->get('profiler')->getProfiles();
if ($profiles) {
$total_query_time = 0;
foreach ($profiles as $profile) {
$total = (float) $profile->getTotalElapsedSeconds();
$query_profile = array(
'ts' => date("c"),
'host' => gethostname(),
'sql' => $profile->getSQLStatement(),
'start' => $profile->getInitialTime(),
'end' => $profile->getFinalTime(),
'total' => $profile->getTotalElapsedSeconds()
);
$profile = json_encode($query_profile);
$logger->log($profile, Logger::INFO);
$total_query_time += (float) $total;
}
$logger->log(json_encode(array('TotalPageQueryTime' => $total)), Logger::INFO);
}
}
...
All of the queries from "db" are logged and I can log the first query from db_old, but any queries done in the view, for example:
{% for taping in tapings.items %}
{% set settings = taping.getTapingStats() %}
{% set show = taping.getShows() %}
<tr {{ taping.getTapingActive() == '0' ? 'class="danger"' : '' }}>
<td>{{ taping.getTapingId() }}</td>
<td>{{ show.getShowTitle() }}</td>
<td>{{ taping.getTapingActive() == '0' ? 'No' : 'Yes' }}</td>
<td>{{ date("D, M d, Y @ g:i a", strtotime(taping.getTapingDate())) }}</td>
<td>{{ settings ? settings.getGroupsAllowed()|default('0') ? 'Yes' : 'No' : '-' }}</td>
<td>{{ settings ? settings.getGroupsNeeded()|default('-') : '-' }}</td>
<td>{{ settings ? settings.getGroupsNeeded() - taping.getGroupBookings().count() : '-' }}</td>
<td>{{ settings ? settings.getHasDonations() ? 'Yes' : 'No' : '-' }}</td>
<td>{{ settings ? settings.getHasTransportation() ? 'Yes' : 'No' : '-' }}</td>
<td class="text-center"><a href="{{ url({'for':'admin_tapings_view', 'show_id' : show.getShowId()}) }}">View</a></td>
</tr>
{% endfor %}
The query for paginating the data in "tapings" is logged, all internal ACL queries are logged, but when I call "taping.getShows()" on each iteration, they are NOT logged but I do get results from them...
I've tried changing the eventManager settings from 'db' to 'db_old' but it only reduces the number of logged queries I get.
Eventually, these logs will be used in the ELK stack to monitor real-time query performance so making sure I can log all queries is a must.