9

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?

Improve this question

6 Answers 6

Reset to default
7

There is no functionality in MySQL to do this.

You will have to stick with application-layer logging.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

why? one question - why? is it too hard to… make? just log the query and error when throwing it back to the client..
@Serge: I don't know. Seems like it shouldn't be too hard to implement. Perhaps a separation-of-concerns thing (i.e. it's the client's responsibility to get the SQL statement right, and it is free to log failure as it wishes).
4

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

The MariaDB Audit Plugin works for MariaDB, MySQL and 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.

Improve this answer

3 Comments

I give a download url: downloads.mariadb.com/Audit-Plugin/MariaDB-Audit-Plugin
That download URL downloads.mariadb.com/Audit-Plugin/MariaDB-Audit-Plugin appears to be broken.
this is the best diamond i got for the last year!!
3

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. 

mysql -u user -p 2> errors.log
Improve this answer

1 Comment

Thanks, didn't know could do this, rather than logging to a file. I made it log to a database instead. Cheers.
2

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.

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:

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.

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)

/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:

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

and run some faulty 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.

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

Improve this answer

3 Comments

Nice one, had a go at it and took awhile to get it finally working, but works like a charm. BTW, it's not even beta, it's still alpha. dev.mysql.com/downloads/mysql-proxy
Isnt it a performance killer ? I think that error handling on client side would be fine
Good to hear that! :)
1

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):

$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)

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

Improve this answer

5 Comments

I input sql through phpmyadmin 3.3.2 are they doing it wrong?
@user phpmyadmin is single-user application, thus it don't need any logging.
Does trigger_error write to mysql log or php log? Because I got shell scripts that use mysql as well.
@user that's php directive. It's writing into standard PHP error log.
thanks for answer, but does not solve my problem, this is php dependent.
1

MariaDB is capable to do this with a plugin https://mariadb.com/kb/en/sql-error-log-plugin/ which is distributed with mariadb.

I tried this yesterday and it works as advertised.

One need to just run:

install plugin SQL_ERROR_LOG soname 'sql_errlog';

and the queries with errors will go to $datadir/sql_errors.log that is on most linux install at /var/lib/mysql/sql_errors.log.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.