How to log error queries in mysql?

Question

I know that there is general_log that logs all queries, but I want to find out which query has an error, and get the error message. I have tried running an error query on purpose, but it logs as a normal query and doesn't report it with error. Any ideas?

Answer 1

Not possible AFAIK. You could hack something together using MySQL Proxy or tcpdump/tshark.

Answer 2

In short, There's no easy answer.
But if you have no other choice then to log errors on server rather then in client app, you [Luke] can use the source.
I give directions rather then patch, and it may apply differently to your mysqld version. In mysql source dir, in file sql/sql_parce.cc, in function
void mysql_parse(THD *thd, const char inBuf, uint length, const char * found_semicolon)
after statement bool err= parse_sql(thd, & parser_state, NULL);
in else clause to following if(!err) statement, that looks like

      else
        {
          DBUG_ASSERT(thd->is_error());
          DBUG_PRINT("info",("Command aborted. Fatal_error: %d",
                             thd->is_fatal_error));

          query_cache_abort(&thd->net);
        }

insert string like DBUG_PRINT("info",("query was: %s",inBuf));
so it should look like

      else
        {
          DBUG_ASSERT(thd->is_error());
          DBUG_PRINT("info",("Command aborted. Fatal_error: %d",
                             thd->is_fatal_error));
          DBUG_PRINT("info",("Query was: %s",inBuf));
          query_cache_abort(&thd->net);
        }

Then, run ./configure with flag --with-debug (among others that you use), build, and run mysqld command with flags as usual, but add debugging flag -#d,info:f,mysql_parse, well, just like this:

sudo -u mysql /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --port=3306 -#d,info:f,mysql_parse

then, what you've got in output looks like this:

100605  3:05:55  InnoDB: Started; log sequence number 2 2219911338
100605  3:05:56 [Note] Event Scheduler: Loaded 0 events
100605  3:05:56 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.37-1ubuntu5.1-debug-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
mysql_parse: info: Command aborted. Fatal_error: 0
mysql_parse: info: query was: aldjflsajlfjslfjlsfkjlsdjflsjfljsdlkfjsdf

Also, you may find useful these links:

http://dev.mysql.com/doc/refman/5.0/en/making-trace-files.html
http://dev.mysql.com/doc/refman/5.0/en/the-dbug-package.html

Hope that helps.