How to debug MySQL/Doctrine2 Queries?
Solution 1
how about using mysql general query log?
The general query log is a general record of what mysqld is doing. The server writes information to this log when clients connect or disconnect, and it logs each SQL statement received from clients. The general query log can be very useful when you suspect an error in a client and want to know exactly what the client sent to mysqld.
Solution 2
Most simple solution for debugging queries in Doctrine 2:
$em->getConnection()
->getConfiguration()
->setSQLLogger(new \Doctrine\DBAL\Logging\EchoSQLLogger())
;
Solution 3
You have to use a DBAL SQLLogger. You can use the basic native SQL Logger \Doctrine\DBAL\Logging\EchoSQLLogger, or you can implement yours with an interface of Doctrine\DBAL\Logging\SQLLogger.
For the basic logger you must attach the SQL Logger to the EntityManager. So, in your Doctrine bootstrap file use:
$config = new Doctrine\ORM\Configuration ();
// ... config stuff
$config->setSQLLogger(new \Doctrine\DBAL\Logging\EchoSQLLogger());
$connectionParams = array(
'dbname' => 'example',
'user' => 'example',
'password' => 'example',
'host' => 'localhost',
'driver' => 'pdo_mysql');
//make the connection through an Array of params ($connectionParams)
$em = EntityManager::create($connectionParams, $config);
Note that we create our EntityManager from the $connectionParams array.
Important:. If you use a DBAL Connection for creating your EntityManager, you have to attach it in both, the DBAL Connection and the ORM EntityManager. For example, in Zend Framework,
You do this:
$config = new Doctrine\ORM\Configuration ();
// ...config stuff
// LOGGER
$config->setSQLLogger(new \Doctrine\DBAL\Logging\EchoSQLLogger());
// make the connection through DBAL (DriverManager::getConnection)
// note that we attach $config in $connApp(DBAL) and $emApp(ORM)
$connApp = DriverManager::getConnection($connectionParams, $config);
$emApp = EntityManager::create($connApp, $config);
Zend_Registry::set('emApp', $emApp);
Zend_Registry::set('connApp', $connApp);
Solution 4
Use Doctrine2 profiler + Firebug
https://github.com/mridgway/ZendX_Doctrine2/
Solution 5
I wrote a blog article on this topic with some instructions for setting up profiling Doctrine 2 in Zend Framework
ZFDebug is already very nice, and there is a Doctrine 2 plugin for it
http://labs.ultravioletdesign.co.uk/profiling-doctrine-2-with-zend-framework/
Related videos on Youtube
Jiew Meng
Web Developer & Computer Science Student Tools of Trade: PHP, Symfony MVC, Doctrine ORM, HTML, CSS, jQuery/JS Looking at Python/Google App Engine, C#/WPF/Entity Framework I hope to develop usable web applications like Wunderlist, SpringPad in the future
Updated on July 09, 2022Comments
-
Jiew Meng almost 2 years
I am using MySQL with Zend Framework & Doctrine 2. I think even if you don't use Doctrine 2, you will be familiar with errors like
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ASC' at line 1
The problem is that I don't see the full query. Without an ORM framework, I could probably echo the sql easily, but with a framework, how can I find out what SQL its trying to execute? I narrowed the error down to
$progress = $task->getProgress();
$progress
is declared// Application\Models\Task /** * @OneToMany(targetEntity="TaskProgress", mappedBy="task") * @OrderBy({"seq" = "ASC"}) */ protected $progress;
In MySQL, the task class looks like
CREATE TABLE `tasks` ( `id` int(11) NOT NULL AUTO_INCREMENT, `owner_id` int(11) DEFAULT NULL, `assigned_id` int(11) DEFAULT NULL, `list_id` int(11) DEFAULT NULL, `name` varchar(60) NOT NULL, `seq` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `tasks_owner_id_idx` (`owner_id`), KEY `tasks_assigned_id_idx` (`assigned_id`), KEY `tasks_list_id_idx` (`list_id`), CONSTRAINT `tasks_ibfk_1` FOREIGN KEY (`owner_id`) REFERENCES `users` (`id`), CONSTRAINT `tasks_ibfk_2` FOREIGN KEY (`assigned_id`) REFERENCES `users` (`id`), CONSTRAINT `tasks_ibfk_3` FOREIGN KEY (`list_id`) REFERENCES `lists` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1$$
-
Jiew Meng over 13 yearsI can't enable it in CLI with
--general_log=1 --general_log_file="..."
but I managed to in MySQL withSET global general_log = 'ON'
-
dimirc over 12 yearsThis way didnt work for me, I had to call it like this: $em->getConnection()->getConfiguration()->setSQLLogger( new Doctrine\DBAL\Logging\EchoSQLLogger());
-
skrilled over 9 yearsThis is by far the best answer here.
-
Nico over 8 yearsIn most cases it's ok, but in certain case it's not sufficient... when you have problems with your parameters... mysql General query log give the exact SQL query which will be executed with the parameters replaced.
-
Tony over 8 yearsLink broken, mysql.stu.edu.tw/tech-resources/articles/…
-
regilero over 8 yearsThanks, fixed. You could try an edit instead, it would be reviewed if you do not have enough reputations for a direct edit.
-
Kamafeather over 3 yearsNo way to set it via YAML configuration, is it? Look like DoctrineBundle does not supports anything other than
logging
. – I tried to tag the default logger into services.yaml, withDoctrine\DBAL\Logging\EchoSQLLogger: '@doctrine.dbal.logger'
but doesn't seem to sort any effect. -
Kamafeather over 3 yearsI was doing it the other way around – Adding
doctrine.dbal.logger
(newline)class: Doctrine\DBAL\Logging\EchoSQLLogger
insideservices.yaml
made it work! -
andreas over 2 yearsAs of today (22 Sep 2021), the EchoSqlLogger class is deprecated. You can of course easily move the code to your project and continue using the logger.