如何使用 C API 为 MySQL 查询设置超时 [英] How to set a timeout for MySQL query using C API

问题描述

我知道这里有很多类似的问题，也有很多结果，当我用谷歌搜索时，但没有一个能回答我的问题.我读了这个，这个，这个 和 this 但它们都不适合我.我不谈论任何锁，我不想使用 MySQL C++ 连接器来做到这一点，只是 C API.

I know there are lots of similar questions here, also there are lots of results, when I google it, but none of them answers my question. I read this, this, this and this but none of them works for me. I don't talk about any locks, I don't wanna do this using MySQL c++ connector, just the C API.

此外，这里非常重要的是:我在 LINUX 上执行此操作.为什么我提到这个?因为在 mysql_options 的文档中:

Also, what is very important here: I do this on LINUX. Why I mention this? Because in the documentation for mysql_options:

MYSQL_OPT_READ_TIMEOUT - ...This option works only for 
TCP/IP connections and, prior to MySQL 5.0.25, only for Windows.

MYSQL_OPT_WRITE_TIMEOUT- ... This option works only for 
TCP/IP connections and, prior to MySQL 5.0.25, only for Windows

那么，有没有办法为 5.0.25 之前的版本设置查询超时?

So, is there any way to set a query timeout for versions, prior 5.0.25?

我的 MySQL 版本:

My MySQL version:

[root@xxx kiril]# mysql --version
mysql  Ver 14.12 Distrib 5.0.22, for redhat-linux-gnu (i686) using readline 5.0

至少，有没有办法取消查询?我可以作为不同的线程启动一个计时器，但是当它到期时..我可以以某种方式取消查询吗?

At least, is there any way to cancel a query? I can start a timer as different thread, but when it expires.. can I cancel the query somehow?

推荐答案

好的，我找到了解决方案.感谢 Will 和 PRR(我的同事).

Okay, I found a solution.. Thanks to Will and PRR( my co-worker ).

我无法在每个查询上启动一个新线程，因为这是一个实时应用程序，应该每秒处理 1000 多条消息..(无论如何，感谢 R.. 的想法).

I cannot start a new thread on each query, as this is a real-time application, that is supposed to process 1000+ messages per second..(anyway, thanks to R.. for the idea).

此外，无法通过库终止连接，也无法取消/终止查询，因为问题出在数据库服务器上..

Also, it was not possible to terminate the connection through the library, nor to cancel/kill the query, as the problem was in the DB server..

这是一个蛮力解决方案，但比 _EXIT( FAILURE ) 更好:这是相关问题:如何在 Linux 上强制关闭套接字?" - 所以，我只是使用系统调用关闭了套接字.

And here's a brute-force solution, but still much better that _EXIT( FAILURE ): Here's the related question: "How to force closing socket on Linux?" - so, I just closed the socket using a system call.

重要提示:(感谢 Will) - 事实证明，我们的 MySQL 库包装器具有故障安全"标志，因此在关闭的套接字(或其他严重错误)上，它会尝试以解决"问题，因此在我的情况下，它会自行重新打开套接字.所以，我刚刚关闭了这个选项，现在一切都很好 - 执行因异常而终止 - 这是执行此操作的最软"方式.
这当然应该通过另一个线程来完成 - 例如一个计时器.

Important NOTE: (thanks Will) - It turned out, that our MySQL library wrapper has s "fail-safe" flag, so that on closed socket (or other critical error), it tries to "solve" the problem, so it reopens the socket, by itself, in my case. So, I just turned off this option and everything is fine now - the execute is terminated because of an exception - this is the "softest" way to do this.
This should be done through another thread, of course - a timer, for example.

超时对于 5.0.25 之后的版本确实有效.但是，至少在 RHEL4 和 RHEL5 上，由于某种原因，超时时间增加了三倍！例如，如果某些超时设置为 20 秒，则实际超时为 ~60 秒..
此外，另一件重要的事情是，这些超时(与任何其他选项一样)必须设置 after mysql_init 和 before> mysql_connect 或 mysql_real_connect.

The timeouts are really working for versions after 5.0.25. But, at least on RHEL4 and RHEL5, the timeouts are tripled for some reason! For example, if some of the timeouts is set to 20sec, the real timeout is ~60sec..
Also, another important thing is, that these timeouts(as any other options) MUST be set after mysql_init and before mysql_connect or mysql_real_connect.

这篇关于如何使用 C API 为 MySQL 查询设置超时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！