PEAR::DB - debugging the executed MySQL queries

The context
Recently I had to import data from an XML file into a MySQL table - it was a table with 105 fields from which 71 were direct corespondents in the XML.

The approach
Taking in consideration that there are many fields and the fact that I am good programmer (to be read lazy), I had to automate this - so I found PEAR::DB and I loved the way it works with parameterized queries, sparing me of the headaches added by quoting/unquoting the values from the insert queries.

It was all nice and shiny, until I had to debug the executed SQL queries - After a few hours of intense googling and frustration I was not able to find on the whole internet a way to show from PEAR::DB the actually executed query (with the placeholders replaced by actual values). I needed this, because, although after

$result = & $db->query(...)

the

DB::isError ($result)

returned false, the records were not inserted into the table.
Not being able to find what I wanted on the application layer, I thought to go and get what I need from the DB layer, so I enabled query logging in MySQL.
The following steps are working on Linux (Ubuntu 10.04 in my case), but similarly, it can be done on other OSes
- edit my.cnf with

vim /etc/mysql/my.cnf

(/etc/mysql/ is the location of my.cnf on my Ubuntu machine, and vim is my preferred editor under ssh)
- add the line

log=/tmp/mysql.log

to the file (the carefully chosen /tmp/ location is to avoid the need to change other file/directories permissions)
- save and close the file
- restart the mysql service with

service mysql restart

- carefully watching the /tmp/mysql.log file for change with

tail -f /tmp/mysql.log

when calling the XML parser service that had to insert the rows into the DB

Tada - there is my much wanted INSERT query. I took it and executed it into phpMyAdmin, to receive a #1110 MySQL error that said "Column 'features' specified twice". I looked in my code and indeed it was true.

Conclusions

I don't know why DB::isError ($result) said "No error" and I still don't know if its possible to see the executed query from the PHP layer (if you can help me with this, you are welcomed to do so), but in the end its a happy end, because the query worked as charmed and the job was done, without having to monkey typing all the 105 fields.

PS>

Take out the query logging from the usual working of MySQL, because the log file goes really big, really fast

Share this article: