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
– thetaiko, Commented Dec 31, 2010 at 14:52

flu · Accepted Answer · 2014-02-27 10:27:05Z

96

Most simple solution for debugging queries in Doctrine 2:

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

edited Feb 27, 2014 at 10:27

flu

14.8k8 gold badges78 silver badges73 bronze badges

answered Jan 3, 2011 at 14:51

beberlei

4,3371 gold badge25 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

dimirc Over a year ago

This way didnt work for me, I had to call it like this: $em->getConnection()->getConfiguration()->setSQLLogger( new Doctrine\DBAL\Logging\EchoSQLLogger());

skrilled Over a year ago

This is by far the best answer here.

Nico Over a year ago

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.

Kamafeather Over a year ago

No 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, with Doctrine\DBAL\Logging\EchoSQLLogger: '@doctrine.dbal.logger' but doesn't seem to sort any effect.

Kamafeather Over a year ago

I was doing it the other way around – Adding doctrine.dbal.logger(newline)class: Doctrine\DBAL\Logging\EchoSQLLogger inside services.yaml made it work!

|

Rowinson Gallego · Accepted Answer · 2011-11-17 15:49:54Z

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

ajreal · Accepted Answer · 2010-12-31 15:03:18Z

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, 2010 at 15:03

ajreal

47.4k11 gold badges98 silver badges119 bronze badges

1 Comment

Jiew Meng Over a year ago

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'

Amree · Accepted Answer · 2011-01-01 06:53:31Z

5

Use Doctrine2 profiler + Firebug

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

answered Jan 1, 2011 at 6:53

Amree

2,9104 gold badges31 silver badges53 bronze badges

Comments

Pete Mitchell · Accepted Answer · 2011-12-01 16:50:14Z

2

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/

answered Dec 1, 2011 at 16:50

Pete Mitchell

2,8791 gold badge19 silver badges22 bronze badges

Comments

Lionel Morrison · Accepted Answer · 2014-05-06 17:55:44Z

2

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

answered May 6, 2014 at 17:55

Lionel Morrison

6065 silver badges15 bronze badges

Comments

regilero · Accepted Answer · 2015-09-25 13:34:22Z

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, 2015 at 13:34

answered Dec 31, 2010 at 14:53

regilero

30.7k6 gold badges65 silver badges101 bronze badges

2 Comments

Tony Over a year ago

Link broken, mysql.stu.edu.tw/tech-resources/articles/…

regilero Over a year ago

Thanks, fixed. You could try an edit instead, it would be reviewed if you do not have enough reputations for a direct edit.

Community · Accepted Answer · 2017-05-23 12:10:05Z

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, 2017 at 12:10

CommunityBot

11 silver badge

answered May 6, 2014 at 18:01

Scriptlabs

4983 silver badges16 bronze badges

Collectives™ on Stack Overflow

How to debug MySQL/Doctrine2 Queries?

8 Answers 8

6 Comments

Comments

1 Comment

Comments

Comments

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

6 Comments

Comments

1 Comment

Comments

Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related