php exceptions: how to include more information about problematic sql queries

Question

Allright, this is like thousandth time when I get kinda useless information from a php exception. It always looks like this:

Message: 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 '))' at line 1

Stack trace:

#0 /usr/share/php5/Zend/Db/Statement.php(300): Zend_Db_Statement_Pdo->_execute(Array)
#1 /usr/share/php5/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#2 /usr/share/php5/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('UPDATE `product...', Array)
#3 /usr/share/php5/Zend/Db/Adapter/Abstract.php(634): Zend_Db_Adapter_Pdo_Abstract->query('UPDATE `product...', Array)
#4 /usr/share/php5/Zend/Db/Table/Abstract.php(1132): Zend_Db_Adapter_Abstract->update('products', Array, 'shop = '1' AND ...')
#5 /var/www/simira/html/application/models/DbTable/Products.php(250): Zend_Db_Table_Abstract->update(Array, 'shop = '1' AND ...')
#6 /var/www/simira/html/application/controllers/ShopsController.php(567): Application_Model_DbTable_Products->updatePriceByShopCategories(Array, '1')
#7 /var/www/simira/html/application/controllers/ShopsController.php(597): ShopsController->processGlobalChangeRequest('Price', 'Products')
#8 /usr/share/php5/Zend/Controller/Action.php(516): ShopsController->globalChangePriceAction()
#9 /usr/share/php5/Zend/Controller/Dispatcher/Standard.php(295): Zend_Controller_Action->dispatch('globalChangePri...')
#10 /usr/share/php5/Zend/Controller/Front.php(954): Zend_Controller_Dispatcher_Standard->dispatch(Object(Zend_Controller_Request_Http), Object(Zend_Controller_Response_Http))
#11 /usr/share/php5/Zend/Application/Bootstrap/Bootstrap.php(97): Zend_Controller_Front->dispatch()
#12 /usr/share/php5/Zend/Application.php(366): Zend_Application_Bootstrap_Bootstrap->run()
#13 /var/www/simira/html/public/index.php(29): Zend_Application->run()
#14 {main}

What I really need to see now is the query. How can I include this information into an exception being thrown? Can I make it so that the query is not cut away in query('UPDATE product...', Array)? I'd like to know if there is a solution I can apply globally. I am on Zend Framework if that helps.

Your app is not throwing an exception, you have syntax error (looks like quotes). It seems to be happening in Application_Model_DbTable_Products->updatePriceByShopCategories(Array, '1'). Very likely you need to use ->quoteInto() when building queries, or at least this query. — RockyFord
– RockyFord, Commented Jan 30, 2012 at 14:32
The error was caused by invalid logic in a condition. I use quote/quoteInto everywhere. But thx. — clime
– clime, Commented Jan 30, 2012 at 14:57
The generated query contained WHERE columnName IN () as a result. — clime
– clime, Commented Jan 30, 2012 at 15:05
Glad you found it, sometimes debugging sql is a real b#tch. However when the profiler actually works it's great. — RockyFord
– RockyFord, Commented Jan 31, 2012 at 2:45

Phil · Accepted Answer · 2012-01-30 00:03:20Z

3

Zend Framework includes an awesome DB profiler interface.

My personal favourite is the FirePHP implementation. This shows complete query information in your Firebug console.

See http://framework.zend.com/manual/en/zend.db.profiler.html#zend.db.profiler.profilers.firebug

You can also enable MySQL query logging. See http://dev.mysql.com/doc/refman/5.5/en/query-log.html

answered Jan 30, 2012 at 0:03

Phil

166k25 gold badges265 silver badges269 bronze badges

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

9 Comments

clime Over a year ago

wow, that looks easier than I expected. Any chance to have this feature under chrome?

Phil Over a year ago

@clime I haven't tried any of them but you could try one of these extensions

clime Over a year ago

Thx, ill try to set it up under chrome and if that won't be possible, I'll use firefox.

clime Over a year ago

heh, screw chrome. I have just added these two lines into application.ini: resources.db.params.profiler.enabled = true resources.db.params.profiler.class = Zend_Db_Profiler_Firebug and it just magically beautifully works. I abandoned firefox for its memory eating but it looks like he is back!

clime Over a year ago

err, wait. The query that is causing the exception is not displayed in the console. There is no update, only three selects. How can I display the update query?

|

Collectives™ on Stack Overflow

php exceptions: how to include more information about problematic sql queries

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related