如何在mysql中记录错误查询？

I know that there is general_log that logs all queries, but I want to find out which query has an error, and get the error message. I have tried running an error query on purpose, but it logs as a normal query and doesn't report it with error. Any ideas?

我知道有general_log记录所有查询,但我想找出哪个查询有错误,并获取错误消息。我试图故意运行错误查询,但它记录为普通查询,并且不报告错误。有任何想法吗?

5 个解决方案

#1

I know this is ancient now, but for anyone having the same issue that lands here from Google, here's my two cents.

我知道这个现在已经很古老了,但对于那些从谷歌来到这里的人来说,这是我的两分钱。

If you're using the cli client, you can simply redirect your sterr to a file, and then parse through that.

如果你正在使用cli客户端,你可以简单地将你的sterr重定向到一个文件,然后解析它。

mysql -u user -p 2> errors.log

#2

There is no functionality in MySQL to do this.

MySQL中没有功能可以做到这一点。

You will have to stick with application-layer logging.

您将不得不坚持应用层日志记录。

#3

It is possible to log error queries with the MariaDB Audit Plugin.

可以使用MariaDB Audit Plugin记录错误查询。

The MariaDB Audit Plugin works for MariaDB, MySQL and Percona Server.

MariaDB Audit Plugin适用于MariaDB,MySQL和Percona Server。

For example, for these queries

例如,对于这些查询

select now();
select now()+();
select 9+();
select 'hello';

log seems like this:

日志看起来像这样:

20150807 23:00:36,mnv-Satellite-L300D,root,localhost,82,377,QUERY,`test`,'select now()
LIMIT 0, 1000',0
20150807 23:00:37,mnv-Satellite-L300D,root,localhost,82,379,QUERY,`test`,'select now()+()',1064
20150807 23:00:37,mnv-Satellite-L300D,root,localhost,82,382,QUERY,`test`,'select 9+()',1064
20150807 23:00:38,mnv-Satellite-L300D,root,localhost,82,383,QUERY,`test`,'select \'hello\'
LIMIT 0, 1000',0

Last column is return code. 0 is Ok. Else - error.

最后一列是返回码。 0是好的。否则 - 错误。

#4

Even though this question is quite old I hope it will be useful to someone who searched for mysql log error queries or similar terms.

即使这个问题很老,我希望它对搜索mysql日志错误查询或类似术语的人有用。

Not too long ago I also required mysqld to log only erroneous queries. I found that mysql-proxy enables you to do that and wrote a small LUA script:

不久前,我还要求mysqld只记录错误的查询。我发现mysql-proxy使你能够这样做并写了一个小的LUA脚本:

local err_flag = false
function read_query( packet )
    if packet:byte() == proxy.COM_QUERY then
        local user = proxy.connection.client.username
        local host = proxy.connection.client.src.name
        if user:lower() == 'someuser' then -- change this to any condition where errors should be logged
            proxy.queries:append(1, packet, {resultset_is_needed = true})
            proxy.queries:append(2, string.char(proxy.COM_QUERY) .. "SET @last_query = '" .. string.sub(packet, 2) .. "'", {resultset_is_needed = true} )
            proxy.queries:append(3, string.char(proxy.COM_QUERY) .. "SHOW WARNINGS", {resultset_is_needed = true} )
        end
        return proxy.PROXY_SEND_QUERY
    end
end


function insert_query(err_t, err_n, err_m)
  local query = "INSERT INTO `somedb`.`mysql_error` " .. -- change log destination 
    "(`date`, `err_num`,`err_type`, `err_message`, `problem_query`, `conn_id`)" ..
    " VALUES ( NOW(), " ..
    err_n  ..  "," .. "\"" ..
    err_t .."\"" .. "," .. "\"" ..
    err_m .. "\"" .. "," ..
    "@last_query" .. "," ..
    proxy.connection.server.thread_id .. ")"
    proxy.queries:append(4, string.char(proxy.COM_QUERY) .. query, {resultset_is_needed = true})
    return proxy.PROXY_SEND_QUERY
end


function read_query_result(inj)
    local res = assert(inj.resultset)
    if inj.id == 1 then
        err_flag = false
        if res.query_status == proxy.MYSQLD_PACKET_ERR then
            err_flag = true
            return proxy.PROXY_IGNORE_RESULT
        end
    elseif inj.id == 2 then
        return proxy.PROXY_IGNORE_RESULT
    elseif inj.id == 3 then
        if err_flag == true then
            for row in res.rows do
                proxy.response.type = proxy.MYSQLD_PACKET_ERR
                proxy.response.errmsg = row[3]
                insert_query(row[1], row[2], row[3])
            end
            return proxy.PROXY_SEND_RESULT
        end
        return proxy.PROXY_IGNORE_RESULT
    elseif inj.id == 4 then
        return proxy.PROXY_IGNORE_RESULT
    end
end

DDL needed for logging table, adjust somedb.mysql_error to liking, but don't forget to do so in the above LUA script also.

记录表需要DDL,将somedb.mysql_error调整为喜欢,但也不要忘记在上面的LUA脚本中也这样做。

CREATE TABLE `somedb`.`mysql_error` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `date` datetime NOT NULL,
    `err_num` smallint(6) NOT NULL,
    `err_type` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
    `err_message` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
    `problem_query` varchar(8000) COLLATE utf8_unicode_ci NOT NULL,
    `conn_id` int(11) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

To use the script, run

要使用该脚本,请运行

/path/to/mysql-proxy --proxy-lua-script=/path/to/mysql-proxy-log-error-queries.lua

or if that fails (>=v0.9)

或者如果失败(> = v0.9)

/path/to/mysql-proxy --proxy-lua-script=/path/to/mysql-proxy-log-error-queries.lua --plugins=proxy

/ path / to / mysql-proxy --proxy-lua-script = / path / to / mysql-proxy-log-error-queries.lua --plugins = proxy

The proxy runs on port 4040 by default, to test:

默认情况下,代理在端口4040上运行,以测试:

mysql -u username -p --host=127.0.0.1 --port=4040

mysql -u username -p --host = 127.0.0.1 --port = 4040

and run some faulty sql.

并运行一些错误的SQL。

When everything seems to be in order set the port in your application to 4040 instead of the actual mysqld port and you have mysql error logging on the database level.

当一切似乎都按顺序将应用程序中的端口设置为4040而不是实际的mysqld端口,并且您在数据库级别上记录了mysql错误。

Final note: mysql-proxy is beta. Use with caution I guess. Been running here for almost half a year now without problems, however YMMV.

最后说明:mysql-proxy是beta。我猜是谨慎使用。 YMMV,现在已经在这里运行了将近半年没有问题。

#5

I have tried running an error query on purpose, but it logs as a normal query and doesn't report it with error. Any ideas?

我试图故意运行错误查询,但它记录为普通查询,并且不报告错误。有任何想法吗?

so, you did it wrong. No other idea without the code.

所以,你做错了。没有代码就没有其他想法。

in PHP I'm doing it this way (assume you're using mysql driver):

在PHP中我这样做(假设你使用的是mysql驱动程序):

$res=mysql_query($sql) or trigger_error(mysql_error().$sql);

it will log all erroneous queries if you have log_errors setting on (and you have to)

如果你设置了log_errors设置,它将记录所有错误的查询(你必须)

EDIT: I see now, you want global level logging,m not application level. But may be application level will suit you as well?

编辑:我现在看到,你想要全局级别的日志记录,而不是应用程序级别。但是应用程序级别也适合你吗?

#1