Quick Tip: Debugging MySQL with MySQL’s General Log

Sometimes when developing in PHP esp with Frameworks like Doctrine, I don’t know what SQL is executed so when I get an error like

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

I don’t know whats the SQL, the error just says “near ‘ASC’” but I don’t know whats before and after that.

I found that enabling MySQL General Query Log will help things

I can’t seem to enable it via CLI

> mysqld --general_log=1 --general_log_file="/path/to/log"

But I got it working within MySQL

> set global general_log = 'ON';

As you run your app, you can see what queries are run in your log eg “mysql data dir/host.log”

110101 11:18:02    26 Connect   root@localhost on tickle
       26 Query SELECT l0_.id AS id0, l0_.name AS name1, l0_.seq AS seq2, l0_.owner_id AS owner_id3, l0_.project_id AS project_id4 FROM lists l0_ WHERE l0_.id = 1
       26 Query SELECT t0.id AS id1, t0.name AS name2, t0.seq AS seq3, t0.owner_id AS owner_id4, t0.assigned_id AS assigned_id5, t0.list_id AS list_id6 FROM tasks t0 WHERE list_id = '1' ORDER BY t0.seq ASC, t0.id DESC
       26 Query SELECT t0.id AS id1, t0.due AS due2, t0.completed AS completed3, t0.task_id AS task_id4, t0.stage_id AS stage_id5 FROM task_progress t0 WHERE task_id = '2'
       26 Query SELECT t0.id AS id1, t0.due AS due2, t0.completed AS completed3, t0.task_id AS task_id4, t0.stage_id AS stage_id5 FROM task_progress t0 WHERE task_id = '1'
       26 Query SELECT t0.id AS id1, t0.seq AS seq2, t0.name AS name3, t0.list_id AS list_id4 FROM stages t0 WHERE t0.id = '1'
       26 Query SELECT t0.id AS id1, t0.seq AS seq2, t0.name AS name3, t0.list_id AS list_id4 FROM stages t0 WHERE t0.id = '2'
       26 Query SELECT t0.id AS id1, t0.seq AS seq2, t0.name AS name3, t0.list_id AS list_id4 FROM stages t0 WHERE t0.id = '3'
       26 Query SELECT t0.id AS id1, t0.seq AS seq2, t0.name AS name3, t0.list_id AS list_id4 FROM stages t0 WHERE list_id = '1' ORDER BY t0.seq ASC
       26 Query SELECT t0.id AS id1, t0.due AS due2, t0.completed AS completed3, t0.task_id AS task_id4, t0.stage_id AS stage_id5 FROM task_progress t0 WHERE stage_id = '1'
       26 Query SELECT t0.id AS id1, t0.due AS due2, t0.completed AS completed3, t0.task_id AS task_id4, t0.stage_id AS stage_id5 FROM task_progress t0 WHERE stage_id = '2'
       26 Query SELECT t0.id AS id1, t0.due AS due2, t0.completed AS completed3, t0.task_id AS task_id4, t0.stage_id AS stage_id5 FROM task_progress t0 WHERE stage_id = '3'
       26 Query SELECT t0.id AS id1, t0.due AS due2, t0.completed AS completed3, t0.task_id AS task_id4, t0.stage_id AS stage_id5 FROM task_progress t0 WHERE stage_id = '4'
       26 Query START TRANSACTION
       26 Query INSERT INTO stages (list_id, seq, name) VALUES (1, 1, 'New')
       26 Query INSERT INTO task_progress (task_id, stage_id, due, completed) VALUES (1, 21, NULL, '2011-01-02 00:00:00')
       26 Query DELETE FROM task_progress WHERE id = '9'
       26 Query DELETE FROM task_progress WHERE id = '10'
       26 Query DELETE FROM task_progress WHERE id = '11'
       26 Query DELETE FROM stages WHERE id = '1'
       26 Query DELETE FROM stages WHERE id = '2'
       26 Query DELETE FROM stages WHERE id = '3'
       26 Query DELETE FROM stages WHERE id = '4'
       26 Query COMMIT
       26 Quit