In my server error log file I see things like:

[Thu Jan 18 00:33:56 2007] [error] [client 72.86.29.196] DBD::mysql::st
execute failed: You have an error in your SQL syntax near ' (NOW(),
"Setup [precheck] failed for 72.86.29.196 port 1239 [72.86.29.196]
(Con' at line 1 at /var/www/html/blah/mysql-help.pl line 34.

Is there a MySQL setting I can change somewhere so that it will log the
entire SQL query that generated the error, instead of just an excerpt?
It's a lot easier to figure out what went wrong if I can see the whole
query.

On Wed, 17 Jan 2007 22:13:46 -0800, bennett wrote:

> Is there a MySQL setting I can change somewhere so that it will log the
> entire SQL query that generated the error, instead of just an excerpt?
> It's a lot easier to figure out what went wrong if I can see the whole
> query.

RTFM..!

5.11.2. The General Query Log

"The general query log is a general record of what mysqld is doing. The
server writes information to this log when clients connect or disconnect,
and it logs each SQL statement received from clients."

"To enable the general query log, start mysqld with the --log[=file_name]
or -l [file_name] option. If no file_name value is given, the default name
is host_name.log in the data directory."

--
Regards/mvh Joachim Mæland

If everything seems under control, you're just not going fast enough.
-Mario Andretti.

Joachim Mæland wrote:
> On Wed, 17 Jan 2007 22:13:46 -0800, bennett wrote:
>
> > Is there a MySQL setting I can change somewhere so that it will log the
> > entire SQL query that generated the error, instead of just an excerpt?
> > It's a lot easier to figure out what went wrong if I can see the whole
> > query.
>
> RTFM..!
>
> 5.11.2. The General Query Log
>
> "The general query log is a general record of what mysqld is doing. The
> server writes information to this log when clients connect or disconnect,
> and it logs each SQL statement received from clients."
>
> "To enable the general query log, start mysqld with the --log[=file_name]
> or -l [file_name] option. If no file_name value is given, the default name
> is host_name.log in the data directory."
>
> --
> Regards/mvh Joachim Mæland

But this will log *every* query, right? Even ones that don't generate
an error? That's too much to search for just to find the ones that
caused an error, and I don't know if I have enough disk space anyway.
Is there a way to log only the error-generating ones?

-Bennett

> But this will log *every* query, right? Even ones that don't generate
> an error? That's too much to search for just to find the ones that
> caused an error, and I don't know if I have enough disk space anyway.
> Is there a way to log only the error-generating ones?


Not from MySQL itself. But any client program you write would surely be
capable of doing that. For example, in PHP, you can query
mysql_errno(<connection>) for the code (>0 if error encountered) and
mysql_error(<connection>) for the message (which is truncated like you
describe). It is your responsibility as a programmer to check for and
handle errors. And you can handle them any way you like: send them by
e-mail, store them in a file or database table, or even ignore them.

In general, errors should not be stored on a database server, because it
is for storing data.

Yes, the query log stores ALL queries. So you do not want that on a
production server either. This feature is great for a (localhost)
development server though.

Best regards

Dikkie Dik wrote:
> > But this will log *every* query, right? Even ones that don't generate
> > an error? That's too much to search for just to find the ones that
> > caused an error, and I don't know if I have enough disk space anyway.
> > Is there a way to log only the error-generating ones?
>
>
> Not from MySQL itself. But any client program you write would surely be
> capable of doing that. For example, in PHP, you can query
> mysql_errno(<connection>) for the code (>0 if error encountered) and
> mysql_error(<connection>) for the message (which is truncated like you
> describe). It is your responsibility as a programmer to check for and
> handle errors.

I tried that. Unfortunately, I ran into the problem that if I input a
query with bad syntax, as soon as I call $sth->execute , the perl
script dies and I have no opportunity to check the error or do anything
else. I read through all the documentation at
http://search.cpan.org/dist/DBD-mysql/lib/DBD/mysql.pm
searching for documentation on "errors" but found nothing. How do I
keep the script going after the invalid syntax is detected?

Here for example:

>>>
my $database_handle =
DBI->connect("DBI:mysql:database=peacefire;host=localh ost",
'root', # username
'<my p***word>', # p***word
{'RaiseError' => 1 }
);

my $querystring = "SELECT FROM THIS QUERY SYNTAX IS DELIBERATELY
INVALID;";

my $sth = $database_handle->prepare($querystring);

# at this point in the code, $database_handle->{'mysql_errno'} is still
empty, I checked

$sth->execute;
# The previous line causes the script to die. How do I instead check
for the syntax error and mail it to myself?
>>>