Hearing that the MySQL database server is delivering corrupt data is not how I like to start the day, but that was the greeting when I arrived to work a couple days ago.
The claim was that an INSERT...SELECT query buried in a batch process was producing a different result than running the same query manually via the mysql client.
Since this is a production system, and a busy one at that, we don't keep the general log enabled. Restarting the server to enable logging is not an option during peak loads either.
Luckily, I stumbled on a DTrace script out there in internet land and it let me see exactly what was happening. I wish I can remember where I got it so I could give due credit. Here is the script:
Thats it. Just execute it on your Solaris 10 server passing the mysqld pid as a command line parameter and you see each query issued to the server. When you have seen enough, just cntl + c to quit.
I used this to trace the batch process and in no time, we determined that there was not one, but two inserts buried within. MySQL was acquitted. Case closed.
This hardly scratches the surface of what I believe DTrace can be used for. I've used it a bit to trace some function calls in mysqld ( using the pid provider), and it does offer an interesting glimpse into MySQL's inner self.