Linux – How to log error queries in thesql

configurationlinuxloggingMySQL

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?

Best Answer

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.

Related Topic