c mysql timeout_如何使用C API設置MySQL查詢超時

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.

我知道這里有很多類似的問題，也有很多的結果，當我用它的時候，但是沒有一個能回答我的問題。我讀過這個，這個，這個，還有這個，但是沒有一個適合我。我不討論任何鎖，我不想使用MySQL c++連接器，僅僅是c API。

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

另外，這里非常重要的一點是:我在LINUX上做這個。為什么我提到這個?因為在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

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

那么，有沒有辦法在5.0.25之前為版本設置查詢超時?

My MySQL version:

我的MySQL版本:

[root@xxx kiril]# mysql --version

mysql Ver 14.12 Distrib 5.0.22, for redhat-linux-gnu (i686) using readline 5.0

EDIT: 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?

編輯:至少，有辦法取消查詢嗎?我可以啟動一個計時器作為不同的線程，但當它過期。我可以取消查詢嗎?

4 个解决方案

#1

2

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

好吧，我找到了解決辦法。感謝Will和PRR(我的同事)。

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

我不能在每個查詢上啟動一個新的線程，因為這是一個實時應用程序，它應該每秒處理1000多條消息。(無論如何,由於R . .這個想法)。

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..

此外，不可能通過庫終止連接，也不可能取消/終止查詢，因為問題是在DB服務器上。

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.

這里有一個蠻力解決方案，但更好的是_EXIT(FAILURE):這里有一個相關的問題:“如何在Linux上強制關閉套接字?”-我剛剛用系統調用關閉了插座。

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.

重要提示:(謝謝Will)——事實證明，我們的MySQL庫包裝器有一個“故障安全”標志，因此在關閉套接字(或其他關鍵錯誤)上，它試圖“解決”問題，因此在我的例子中，它自己重新打開套接字。所以，我關閉了這個選項，現在一切都好了——執行因為一個異常而終止——這是最“軟”的方式。當然，這應該通過另一個線程來完成——例如計時器。

EDIT: 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.

編輯:超時對5.0.25以后的版本有效。但是，至少在RHEL4和RHEL5上，由於某些原因，超時時間增加了兩倍!例如，如果某些超時設置為20秒，則實際超時為大約60秒。另外，另一件重要的事情是，必須在mysql_init和mysql_connect或mysql_real_connect之前設置這些超時(與任何其他選項一樣)。

#2

1

I suppose you could implement a timeout for the C function call (as described in this thread C++: How to implement a timeout for an arbitrary function call?), but you would need to think carefully about what kind of state you would leave the DB in - presumably these are just for reads of the database, not inserts/updates.

我想你可以實現C函數調用的超時(在這個線程c++描述:如何實現對任意函數調用超時?),但您需要仔細思考什么樣的狀態,你將把DB,想必這些都是讀取數據庫的,而不是插入/更新。

#3

0

I have never tryed to do that, but i've been reading and i think that could means that MYSQL_OPT_WRITE_TIMEOUT and MYSQL_OPT_READ_TIMEOUT are only for windows prior MySQL version 5.0.25 but now sould be working for every TCP/IP connection. Take a look here

我從來沒有嘗試過這樣做，但是我一直在閱讀，我認為這意味着MYSQL_OPT_WRITE_TIMEOUT和MYSQL_OPT_READ_TIMEOUT只適用於windows之前的MySQL版本5.25，但是現在應該為每個TCP/IP連接工作。看一看這里

Regards

問候

EDIT: I would try to update my mysql server to a newer version and try if it works.

編輯:我將嘗試更新我的mysql服務器到一個更新的版本，並嘗試它是否有效。

#4

0

If you don't mind using threads, you could start the query from a new thread and have the main thread perform a short pthread_cond_timedwait for the new thread to set a condition variable that it make the connection. Then you can let the thread linger around until the actual mysql call times out. Just make sure it's detached so its resources get freed when it finally does time out. This solution isn't pretty, but it should at least work.

如果您不介意使用線程，您可以從一個新線程啟動查詢，並讓主線程執行一個簡短的pthread_cond_timedwait，等待新線程設置一個條件變量，使其進行連接。然后，您可以讓線程在周圍徘徊，直到實際的mysql調用超時。只要確保它是分離的，以便當它最終超時時釋放它的資源。這個解決方案不是很好，但至少應該是可行的。