Logging query errors in MySQL

Question

We have an application that uses several SQL queries and might at times generate the odd error.

For example, it could be a :

INSERT INTO TABLE (ID, FIELD) VALUES (1, "field value");

which would result in a:

ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

Because the unique primary key constraint has been violated.

Is it possible in MySQL to somehow log the error along with the query that caused it? I have tried to enable the error-log and general-log in the /etc/mysql/my.cnf but it never produced the expected result. I could enable logging of every query without their errors (pretty useless for us, we're only interested in queries that result in errors).

The errors can be caught by the applications (in our case they are, we are using Perl DBI), however when there are several statements in a stored procedure then we do not know which one as the error message does not include the text of the query, or even the name of the table involved. This makes troubleshooting quite difficult.

I am sure I am missing something obvious. For example, in Oracle this is the default behavior, query errors are logged into a text file where they can be easily identified.

This is somewhat platform dependent. Which OS and distribution are you using? And how did you install mysql? — jishi
– jishi, Commented Apr 7, 2011 at 12:13
OS: Ubuntu 10.04 LTS MySQL: 5.5 Installed with "aptitude install mysql" — emx
– emx, Commented Apr 7, 2011 at 12:19

Isotopp · Accepted Answer · 2011-04-07 11:48:03Z

2

This is a client thing. Isolate database accesses in an access layer and generate the log on the client. The database cannot log this.

answered Apr 7, 2011 at 11:48

Isotopp

3,4131 gold badge18 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

emx Over a year ago

Thanks, although I have no idea what an "access layer" is. All I want is the same functionality that Oracle provides, namely being able to identify query errors in a log file, regardless of where that query comes from (client, applications, command line, etc.)

Isotopp Over a year ago

In Perl, you are likely to use DBI or ClassDBI. Modify ClassDBI to do what you want (it already does half of it), or wrap your calls to DBI in functions that provide the required checking and logging automatically. You want to be able get a list of queries that you are sending to the database with result set sizes and execution times from your app anyway, even when nothing fails.

Isotopp Over a year ago

The database does not log this. The only tap point is the client.

emx Over a year ago

Well the Database server is the entity that detects and process the error, is it not? Then why wouldn't it be able to log it in a file?

Isotopp Over a year ago

5.5 welcomes you to write such a plugin yourself. dev.mysql.com/doc/refman/5.5/en/writing-audit-plugins.html

|

Collectives™ on Stack Overflow

Logging query errors in MySQL

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related