php 如何调试 MySQL/Doctrine2 查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4570608/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to debug MySQL/Doctrine2 Queries?
提问by Jiew Meng
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
我正在使用 MySQL 和 Zend Framework & Doctrine 2。我想即使你不使用 Doctrine 2，你也会熟悉这样的错误
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
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
问题是我没有看到完整的查询。如果没有 ORM 框架，我可能可以轻松地回显 sql，但是有了框架，我如何才能找出它试图执行的 SQL？我将错误缩小到
$progress = $task->getProgress();
$progressis declared
$progress被宣布
// Application\Models\Task
/**
* @OneToMany(targetEntity="TaskProgress", mappedBy="task")
* @OrderBy({"seq" = "ASC"})
*/
protected $progress;
In MySQL, the task class looks like
在 MySQL 中，任务类看起来像
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$$
采纳答案by ajreal
how about using mysql general query log?
使用mysql通用查询日志怎么样？
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.
一般查询日志是对mysqld正在做什么的一般记录。当客户端连接或断开连接时，服务器将信息写入此日志，并记录从客户端收到的每个 SQL 语句。当您怀疑客户端有错误并想确切知道客户端发送给 mysqld 的内容时，通用查询日志非常有用。
回答by beberlei
Most simple solution for debugging queries in Doctrine 2:
在 Doctrine 2 中调试查询的最简单解决方案：
$em->getConnection()
->getConfiguration()
->setSQLLogger(new \Doctrine\DBAL\Logging\EchoSQLLogger())
;
回答by Rowinson Gallego
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.
您必须使用 DBAL SQLLogger。您可以使用基本的本机 SQL Logger \Doctrine\DBAL\Logging\EchoSQLLogger，或者您可以使用Doctrine\DBAL\Logging\SQLLogger接口实现您的。
For the basic logger you must attach the SQL Logger to the EntityManager. So, in your Doctrine bootstrap file use:
对于基本记录器，您必须将 SQL 记录器附加到 EntityManager。所以，在你的 Doctrine 引导文件中使用：
$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 $connectionParamsarray.
请注意，我们从$connectionParams数组创建了我们的 EntityManager 。
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,
重要：。如果您使用 DBAL 连接来创建 EntityManager，则必须将它附加到 DBAL 连接和 ORM EntityManager 中。例如，在 Zend 框架中，
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);
回答by Amree
Use Doctrine2 profiler + Firebug
使用 Doctrine2 分析器 + Firebug
回答by regilero
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.
尝试在您和 MySQL 服务器之间使用 Mysql 代理 ( http://forge.mysql.com/wiki/MySQL_Proxy)。然后您可以配置此代理以记录所有请求。
http://mysql.stu.edu.tw/tech-resources/articles/proxy-gettingstarted.html
http://mysql.stu.edu.tw/tech-resources/articles/proxy-gettingstarted.html
回答by Pete Mitchell
I wrote a blog article on this topic with some instructions for setting up profiling Doctrine 2 in Zend Framework
我写了一篇关于这个主题的博客文章，其中包含在 Zend Framework 中设置分析 Doctrine 2 的一些说明
ZFDebug is already very nice, and there is a Doctrine 2 plugin for it
ZFDebug 已经很不错了，并且有一个 Doctrine 2 插件
http://labs.ultravioletdesign.co.uk/profiling-doctrine-2-with-zend-framework/
http://labs.ultravioletdesign.co.uk/profiling-doctrine-2-with-zend-framework/
回答by Lionel Morrison
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.
如果您在 ZF2 中开发，您可以使用上面由beberlei发布的解决方案，尽管这确实将其反映到您的显示器上，这不是最佳实践，但很有用。
$em->getConnection()
->getConfiguration()
->setSQLLogger(new \Doctrine\DBAL\Logging\EchoSQLLogger())
;
or you could use ZendDeveloperToolswhich displays the execute query on the toolbar.
或者您可以使用ZendDeveloperTools在工具栏上显示执行查询。
回答by Scriptlabs
Maybe you should use a Zend_Db_Profilein your [development]environment.
也许您应该在您的[开发]环境中使用Zend_Db_Profile。
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
请参阅此链接：Zend 启用 SQL 查询日志记录
Also see this Zend Framework Helper: https://github.com/jokkedk/ZFDebug
另请参阅此 Zend 框架助手：https: //github.com/jokkedk/ZFDebug
