Re: server-side logging of query errors?

2015-06-25 Thread Andrew Moore
Further more, use logstash to collect the audit events and you can filter
out anything that wasn't an error and move it to a query error log.

On Wed, Jun 24, 2015 at 5:32 PM, Singer Wang  wrote:

> Yep, as shown below:
>
> root@audit-db.ec2:(none)> select fark from fark from fark fark fark from
> frak;
> ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
> that corresponds to your MySQL server version for the right syntax to use
> near 'from fark fark fark from frak' at line 1
> root@audit-db.ec2:(none)>
>
> Jun 24 16:29:52 audit-db percona-audit:
>
> {"audit_record":{"name":"Query","record":"217_2015-06-24T16:29:52","timestamp":"2015-06-24T16:29:52
>
> UTC","command_class":"error","connection_id":"59","status":1064,"sqltext":"select
> fark from fark from fark fark fark from frak","user":"root[root] @
> localhost []","host":"localhost","os_user":"","ip":""}}
>
> error 1064
>
>
> On Wed, Jun 24, 2015 at 11:40 AM, Tomasz Chmielewski 
> wrote:
>
> > Normal general log also logs everything including bad queries (i.e.
> SLCECT
> > BLAH BLAH;) - although does not say if it was an error (i.e. syntax) or
> not.
> >
> > Does the audit plugin log the actual error?
> >
> >
> > Tomasz
> >
> > On 2015-06-25 00:32, Singer Wang wrote:
> >
> >> The performance hit of the Percona Audit is 15% for disk logging and
> >> for remote syslog we found it is lower. It logs everything including
> >> bad queries (eg. select fark from fark from fark fark fark from frak).
> >> You should be able to write a JSON parser that extracts what you want
> >> based on the log (eg. STATUS, COMMAND, NAME).
> >>
> >> On Wed, Jun 24, 2015 at 11:05 AM, Johan De Meersman
> >>  wrote:
> >>
> >>  -
> >>>
> >>>  FROM: "Singer X.J. Wang" 
> >>>> SUBJECT: Re: server-side logging of query errors?
> >>>>
> >>>
> >>>  You could log all queries using the audit plugin, 15% hit..
> >>>>
> >>> Fair point, though: maybe one of the different audit plugins has
> >>> the capability to specifically log faulty requests. Have a look
> >>> through the slides from Percona Live London 2014, there was a talk
> >>> about auditing.
> >>>
> >>> --
> >>>
> >>> Unhappiness is discouraged and will be corrected with kitten
> >>> pictures.
> >>>
> >>
> >
>


Re: server-side logging of query errors?

2015-06-24 Thread Singer Wang
Yep, as shown below:

root@audit-db.ec2:(none)> select fark from fark from fark fark fark from
frak;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to use
near 'from fark fark fark from frak' at line 1
root@audit-db.ec2:(none)>

Jun 24 16:29:52 audit-db percona-audit:
{"audit_record":{"name":"Query","record":"217_2015-06-24T16:29:52","timestamp":"2015-06-24T16:29:52
UTC","command_class":"error","connection_id":"59","status":1064,"sqltext":"select
fark from fark from fark fark fark from frak","user":"root[root] @
localhost []","host":"localhost","os_user":"","ip":""}}

error 1064


On Wed, Jun 24, 2015 at 11:40 AM, Tomasz Chmielewski 
wrote:

> Normal general log also logs everything including bad queries (i.e. SLCECT
> BLAH BLAH;) - although does not say if it was an error (i.e. syntax) or not.
>
> Does the audit plugin log the actual error?
>
>
> Tomasz
>
> On 2015-06-25 00:32, Singer Wang wrote:
>
>> The performance hit of the Percona Audit is 15% for disk logging and
>> for remote syslog we found it is lower. It logs everything including
>> bad queries (eg. select fark from fark from fark fark fark from frak).
>> You should be able to write a JSON parser that extracts what you want
>> based on the log (eg. STATUS, COMMAND, NAME).
>>
>> On Wed, Jun 24, 2015 at 11:05 AM, Johan De Meersman
>>  wrote:
>>
>>  -
>>>
>>>  FROM: "Singer X.J. Wang" 
>>>> SUBJECT: Re: server-side logging of query errors?
>>>>
>>>
>>>  You could log all queries using the audit plugin, 15% hit..
>>>>
>>> Fair point, though: maybe one of the different audit plugins has
>>> the capability to specifically log faulty requests. Have a look
>>> through the slides from Percona Live London 2014, there was a talk
>>> about auditing.
>>>
>>> --
>>>
>>> Unhappiness is discouraged and will be corrected with kitten
>>> pictures.
>>>
>>
>


Re: server-side logging of query errors?

2015-06-24 Thread Tomasz Chmielewski
Normal general log also logs everything including bad queries (i.e. 
SLCECT BLAH BLAH;) - although does not say if it was an error (i.e. 
syntax) or not.


Does the audit plugin log the actual error?


Tomasz

On 2015-06-25 00:32, Singer Wang wrote:

The performance hit of the Percona Audit is 15% for disk logging and
for remote syslog we found it is lower. It logs everything including
bad queries (eg. select fark from fark from fark fark fark from frak).
You should be able to write a JSON parser that extracts what you want
based on the log (eg. STATUS, COMMAND, NAME).

On Wed, Jun 24, 2015 at 11:05 AM, Johan De Meersman
 wrote:


-


FROM: "Singer X.J. Wang" 
SUBJECT: Re: server-side logging of query errors?



You could log all queries using the audit plugin, 15% hit..

Fair point, though: maybe one of the different audit plugins has
the capability to specifically log faulty requests. Have a look
through the slides from Percona Live London 2014, there was a talk
about auditing.

--

Unhappiness is discouraged and will be corrected with kitten
pictures.



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql



Re: server-side logging of query errors?

2015-06-24 Thread Singer Wang
The performance hit of the Percona Audit is 15% for disk logging and for
remote syslog we found it is lower. It logs everything including bad
queries (eg. select fark from fark from fark fark fark from frak). You
should be able to write a JSON parser that extracts what you want based on
the log (eg. STATUS, COMMAND, NAME).

On Wed, Jun 24, 2015 at 11:05 AM, Johan De Meersman 
wrote:

>
> --
>
> *From: *"Singer X.J. Wang" 
> *Subject: *Re: server-side logging of query errors?
>
> You could log all queries using the audit plugin, 15% hit..
>
> Fair point, though: maybe one of the different audit plugins has the
> capability to specifically log faulty requests. Have a look through the
> slides from Percona Live London 2014, there was a talk about auditing.
>
>
>
> --
> Unhappiness is discouraged and will be corrected with kitten pictures.
>


Re: server-side logging of query errors?

2015-06-24 Thread Johan De Meersman
> From: "Singer X.J. Wang" 
> Subject: Re: server-side logging of query errors?

> You could log all queries using the audit plugin, 15% hit..

Fair point, though: maybe one of the different audit plugins has the capability 
to specifically log faulty requests. Have a look through the slides from 
Percona Live London 2014, there was a talk about auditing. 

-- 
Unhappiness is discouraged and will be corrected with kitten pictures. 


Re: server-side logging of query errors?

2015-06-23 Thread Singer Wang
You could log all queries using the audit plugin, 15% hit..

On Tue, Jun 23, 2015 at 4:54 AM, Johan De Meersman 
wrote:

> - Original Message -
> > From: "Tomasz Chmielewski" 
>
> >> It would be a mild security risk; a malicious
> >> (or just stupid, see Hanlon's razor) user could spam your server with
> >> malformed requests until the logging disk runs full, at which point
> >> the daemon would suspend operations until space is freed.
> >
> > I don't think it's a valid argument - the same is true right now for
> > "general query log". Any stupid/malicious user can produce loads of
> > queries and fill the disk if one has "general query log" enabled.
> >
> > In short, anyone enabling any logging should consider what limitations
> > it brings.
>
> Including quite a bit of overhead, which is why its use is discouraged in
> production :-)
>
>
> --
> Unhappiness is discouraged and will be corrected with kitten pictures.
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql
>
>


Re: server-side logging of query errors?

2015-06-23 Thread Johan De Meersman
- Original Message -
> From: "Tomasz Chmielewski" 

>> It would be a mild security risk; a malicious
>> (or just stupid, see Hanlon's razor) user could spam your server with
>> malformed requests until the logging disk runs full, at which point
>> the daemon would suspend operations until space is freed.
> 
> I don't think it's a valid argument - the same is true right now for
> "general query log". Any stupid/malicious user can produce loads of
> queries and fill the disk if one has "general query log" enabled.
> 
> In short, anyone enabling any logging should consider what limitations
> it brings.

Including quite a bit of overhead, which is why its use is discouraged in 
production :-)


-- 
Unhappiness is discouraged and will be corrected with kitten pictures.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql



Re: server-side logging of query errors?

2015-06-23 Thread Tomasz Chmielewski

On 2015-06-23 17:29, Johan De Meersman wrote:

None that I'm aware of.


It's a pity!
This could ease debugging in many cases, without the need to change the 
client (i.e. PHP/Perl/Python code which sends the queries, and we 
suspect that some of them are bogus).




It would be a mild security risk; a malicious
(or just stupid, see Hanlon's razor) user could spam your server with
malformed requests until the logging disk runs full, at which point
the daemon would suspend operations until space is freed.


I don't think it's a valid argument - the same is true right now for 
"general query log". Any stupid/malicious user can produce loads of 
queries and fill the disk if one has "general query log" enabled.


In short, anyone enabling any logging should consider what limitations 
it brings.




Maybe one of the proxies out there has support for such logging, I'm
not really familiar with any of them.




--
Tomasz Chmielewski
http://wpkg.org


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql



Re: server-side logging of query errors?

2015-06-23 Thread Johan De Meersman

None that I'm aware of. It would be a mild security risk; a malicious (or just 
stupid, see Hanlon's razor) user could spam your server with malformed requests 
until the logging disk runs full, at which point the daemon would suspend 
operations until space is freed.

Maybe one of the proxies out there has support for such logging, I'm not really 
familiar with any of them.


- Original Message -
> From: "Tomasz Chmielewski" 
> To: "MySql" 
> Sent: Tuesday, 23 June, 2015 09:35:46
> Subject: server-side logging of query errors?

> Suppose I run a query which has a syntax error:
> 
> mysql> blah;
> ERROR 1064 (42000): You have an error in your SQL syntax; check the
> manual that corresponds to your MySQL server version for the right
> syntax to use near 'blah' at line 1
> 
> 
> How can I get mysql server to log this error?
> 
> 
> According to the documentation:
> 
> http://dev.mysql.com/doc/refman/5.5/en/server-logs.html
> 
> - "Error log" - will only log mysqld errors - so, it won't log syntax
> errors in the query
> - "General query log" - it will log all queries, but without indicating
> if it was an error or not
> 
> 
> Is there a way to log query syntax errors on the server? Please assume
> that connection and the query can be coming from PHP, perl etc. code, so
> any "/usr/bin/mysql" stderr output redirecting is not helping here.
> 
> 
> --
> Tomasz Chmielewski
> http://wpkg.org
> 
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql

-- 
Unhappiness is discouraged and will be corrected with kitten pictures.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql



server-side logging of query errors?

2015-06-23 Thread Tomasz Chmielewski

Suppose I run a query which has a syntax error:

mysql> blah;
ERROR 1064 (42000): You have an error in your SQL syntax; check the 
manual that corresponds to your MySQL server version for the right 
syntax to use near 'blah' at line 1



How can I get mysql server to log this error?


According to the documentation:

http://dev.mysql.com/doc/refman/5.5/en/server-logs.html

- "Error log" - will only log mysqld errors - so, it won't log syntax 
errors in the query
- "General query log" - it will log all queries, but without indicating 
if it was an error or not



Is there a way to log query syntax errors on the server? Please assume 
that connection and the query can be coming from PHP, perl etc. code, so 
any "/usr/bin/mysql" stderr output redirecting is not helping here.



--
Tomasz Chmielewski
http://wpkg.org


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql