Professional Web Applications Themes

can error log show entire query instead of just excerpt - MySQL

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....

  1. #1

    Default can error log show entire query instead of just excerpt

    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.

    bennett@peacefire.org Guest

  2. #2

    Default Re: can error log show entire query instead of just excerpt

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

    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 Guest

  3. #3

    Default Re: can error log show entire query instead of just excerpt

    Joachim Mæland wrote: 
    >
    > 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[/ref]

    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

    bennett@peacefire.org Guest

  4. #4

    Default Re: can error log show entire query instead of just excerpt

    > But this will log *every* query, right? Even ones that don't generate 


    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 Guest

  5. #5

    Default Re: can error log show entire query instead of just excerpt


    Dikkie Dik wrote: 
    >
    >
    > 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.[/ref]

    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 doentation at
    http://search.cpan.org/dist/DBD-mysql/lib/DBD/mysql.pm
    searching for doentation on "errors" but found nothing. How do I
    keep the script going after the invalid syntax is detected?

    Here for example:
     [/ref][/ref]
    my $database_handle =
    DBI->connect("DBI:mysql:database=peacefire;host=localh ost",
    'root', # username
    '<my password>', # password
    {'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? [/ref][/ref]

    bennett@peacefire.org Guest

Similar Threads

  1. Query to show data but ignore dupes
    By Andrew in forum MySQL
    Replies: 3
    Last Post: August 9th, 06:51 PM
  2. Datagrid don't show all values from query
    By jorge_sms in forum Macromedia Flex General Discussion
    Replies: 1
    Last Post: September 19th, 03:31 PM
  3. A mysterious excerpt from UNPv1 by W. R. Stevens
    By e1p1s in forum UNIX Programming
    Replies: 5
    Last Post: August 4th, 09:03 AM
  4. SQL plugin and Show columns query
    By Eli in forum FileMaker
    Replies: 1
    Last Post: July 24th, 11:02 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139