How to debug MySQL/Doctrine2 Queries?

Question

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$$

If you have a Doctrine query object, which is unclear in the code you have posted, you can run `$query->getSqlQuery()` to see the SQL. Find your query object in your code and output it to see whats going on. Alternately you can turn on query logging in SQL to see what it is. — thetaiko, Dec 31 '10 at 14:52

score 96 · Answer 1 · edited Feb 27 '14 at 10:27

96

Most simple solution for debugging queries in Doctrine 2:

$em->getConnection()
  ->getConfiguration()
  ->setSQLLogger(new \Doctrine\DBAL\Logging\EchoSQLLogger())
;

edited Feb 27 '14 at 10:27

flu

14,307
8
74
71

answered Jan 03 '11 at 14:51

beberlei

4,297
1
23
25

14

This way didnt work for me, I had to call it like this: $em->getConnection()->getConfiguration()->setSQLLogger( new Doctrine\DBAL\Logging\EchoSQLLogger()); – dimirc Nov 20 '11 at 20:35
1

This is by far the best answer here. – skrilled Nov 03 '14 at 00:38
In 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. – Nico Aug 12 '15 at 12:05
No way to set it via YAML configuration, is it? Look like [*DoctrineBundle* does not supports anything other than `logging`](https://github.com/doctrine/DoctrineBundle/blob/1.12.x/Resources/config/schema/doctrine-1.0.xsd#L37). – I tried to tag the default logger into *services.yaml*, with `Doctrine\DBAL\Logging\EchoSQLLogger: '@doctrine.dbal.logger'` but doesn't seem to sort any effect. – Kamafeather Nov 11 '20 at 00:29
I was doing it the other way around – Adding `doctrine.dbal.logger`(newline)`class: Doctrine\DBAL\Logging\EchoSQLLogger` inside `services.yaml` made it work! – Kamafeather Nov 11 '20 at 12:34
As 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. – andreas Sep 21 '21 at 23:22

Rowinson Gallego · Answer 2 · 2011-11-17T15:49:54.823

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);

score 6 · Accepted Answer · answered Dec 31 '10 at 15:03

6

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.

answered Dec 31 '10 at 15:03

ajreal

46,720
11
89
119

I can't enable it in CLI with `--general_log=1 --general_log_file="..."` but I managed to in MySQL with `SET global general_log = 'ON'` – Jiew Meng Jan 01 '11 at 02:18

score 5 · Answer 4 · answered Jan 01 '11 at 06:53

5

Use Doctrine2 profiler + Firebug

https://github.com/mridgway/ZendX_Doctrine2/

answered Jan 01 '11 at 06:53

Amree

2,890
3
30
51

score 2 · Answer 5 · answered May 06 '14 at 17:55

If your developing in ZF2 you can either use the solution above posted by beberlei, though this does echo it out to your display which is not exactly best practice but useful.

$em->getConnection()
  ->getConfiguration()
  ->setSQLLogger(new \Doctrine\DBAL\Logging\EchoSQLLogger())
;

or you could use ZendDeveloperTools which displays the execute query on the toolbar.

enter image description here

regilero · Answer 6 · 2015-09-25T13:34:22.520

2

Try to use Mysql proxy between you and the MySQL server (http://forge.mysql.com/wiki/MySQL_Proxy). Then you can configure this proxy to log all requests.

http://mysql.stu.edu.tw/tech-resources/articles/proxy-gettingstarted.html

edited Sep 25 '15 at 13:34

answered Dec 31 '10 at 14:53

regilero

29,806
6
60
99

Link broken, http://mysql.stu.edu.tw/tech-resources/articles/proxy-gettingstarted.html – Tony Sep 23 '15 at 10:11
1

Thanks, fixed. You could try an edit instead, it would be reviewed if you do not have enough reputations for a direct edit. – regilero Sep 25 '15 at 13:35

score 2 · Answer 7 · answered Dec 01 '11 at 16:50

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/

score 1 · Answer 8 · edited May 23 '17 at 12:10

1

Maybe you should use a Zend_Db_Profile in your [development] environment.

This will log your queries with runtime and other informations.

If you have an error log that logs your exceptions, then everything can be found in your log files.

See this link: Zend Enable SQL Query logging

Also see this Zend Framework Helper: https://github.com/jokkedk/ZFDebug

edited May 23 '17 at 12:10

Community

1
1

answered May 06 '14 at 18:01

Scriptlabs

498
3
16

How to debug MySQL/Doctrine2 Queries?

8 Answers8

Linked