October 13th, 2013
This week, I ran into a problem when I was profiling that didn’t seem to be very well documented anywhere. I was trying to profile a query that included a view. The error message that I got when I tried to execute my EXPLAIN was: “EXPLAIN/SHOW can not be issued; lacking privileges for underlying table”
After a lot of flailing out, me and the DBA for this project figured out that in order to be able to execute my EXPLAIN, I needed to have SELECT on the key columns that I was using in my query for the table behind the view. Simple enough, but not something made easy to figure out.
Hopefully this helps someone else figure this out faster.
Tags: database, dba, explain, mysql, permissions, profiling, query, sysadmin
Posted in Code, Systems | No Comments »
June 25th, 2013
If you ever need to see the SQL that Zend DB has created, so that you can run it manually or any other reason, here’s how to do it.
// Turn on the profiler
// Run your query
‘thisField’ => 0.12551,
‘anotherField’ => ‘xxxxxxxxxxxx’
), ‘whatId = ‘ . $cdsDB->quote(‘james’));
// Output the last query
echo "\n" . $db->getProfiler()->getLastQueryProfile()->getQuery() . "\n";
// And the parameters for it
// Then turn the profiler off, unless you want to do more
And the output is
UPDATE `someTable` SET `thisField` = ?, `anotherField` = ? WHERE (whatId = 186459)
 => 0.12551
 => xxxxxxxxxxxx
Tags: dump, profile, query, sql, statement, zend, zenddb
Posted in Code | No Comments »