Re: server-side logging of query errors?
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?
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?
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?
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?
> 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?
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?
- 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?
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?
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?
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