MySQL Log of invalid Queries

2019-03-18 03:11发布

I AM NOT RUNNING THE COMMANDS FROM PHP!

I have MySQL log_error value set to /var/log/mysql/error.log

However when I connect to a database and run an SQL command, the error does not appear in the log.


SELECT * 
FROM some_table
where this_is_invalid_and_will_fail="Doesn't matter because column doesn't exist!";

There are commands running from some sort of windows application. All I want to know is what invalid commands its sending to the MySQL server so that I can attempt to resolve them.

5条回答
虎瘦雄心在
2楼-- · 2019-03-18 03:48

This is not so trivial. The best way to do this, is to log bad queries in your application. There is no built-in way.

查看更多
何必那么认真
3楼-- · 2019-03-18 03:50

Could you enable the General Query Log? That should tell you everything you need to know.

查看更多
兄弟一词,经得起流年.
4楼-- · 2019-03-18 03:59

As of mysql version 5.6.3 (released 2011-10-03), there is a variable called log-raw which allows you to include invalid queries in the general query log:

https://dev.mysql.com/doc/refman/5.6/en/server-options.html#option_mysqld_log-raw

You will need to turn on general query log using general-log and general-log-file, e.g.:

[mysqld]
general-log=1
log-raw=1
general-log-file=/var/log/mysql/general.log
查看更多
该账号已被封号
5楼-- · 2019-03-18 04:00

Basically, there are 2 ways.

1) setup some kind of proxy, which can log error queries. There are a lot of forks of the original mysql proxy (which was mentioned by @Mchl) - https://github.com/search?utf8=%E2%9C%93&q=MySQL+Proxy Also you can find latest version of the original mysql proxy here https://github.com/mysql/mysql-proxy

I dont like this way, because its kind of too complicated for quick debug

2) you can enable general log in mysql (which will log ALL queries). It will create big overhead and doesnt fit production requirements, but its fast and easy way to fix bugs in development environment.

Just login to your mysql and execute SET GLOBAL general_log = 'ON'; SHOW GLOBAL VARIABLES LIKE 'general_log%';

You will see logs location, like +------------------+------------------------+ | Variable_name | Value | +------------------+------------------------+ | general_log | OFF | | general_log_file | /mnt/ssd/mysql/s.log | +------------------+------------------------+

After that execute mysqladmin flush-logs -u root -p

When you will need to stop logs - just execute SET GLOBAL general_log = 'OFF';

查看更多
神经病院院长
6楼-- · 2019-03-18 04:01

Error log doesn't do that: https://dev.mysql.com/doc/refman/8.0/en/error-log.html

The error log contains information indicating when mysqld was started and stopped and also any critical errors that occur while the server is running.

MySQL doesn't log invalid/failed queries anywhere.


If it's for debugging purposes, you might try setting up a MySQL Proxy, which could log this I think:

http://dev.mysql.com/downloads/mysql-proxy/

查看更多
登录 后发表回答