How to set a timeout for MySQL query using C API

Question

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.

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

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

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

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?

@Kiril Kirov - You can use kill command in mysql to kill a running query - http://dev.mysql.com/doc/refman/5.0/en/kill.html — ajreal, Nov 25 '10 at 11:05
@Kiril Kirov - PS: do not kill a write command, it will get your data table corrupted — ajreal, Nov 25 '10 at 11:42

score 2 · Accepted Answer · edited May 23 '17 at 12:11

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

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

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.

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.

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.

From the MYSQL_OPT_READ_TIMEOUT_DOCS: "The timeout in seconds for each attempt to read from the server. There are retries if necessary, so the total effective timeout value is three times the option value." That's why the actual interval is 3 times the timeout. — jssebastian, Jun 20 '14 at 21:35

score 1 · Answer 2 · edited May 23 '17 at 12:19

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.

edited May 23 '17 at 12:19

Community

1
1

answered Nov 26 '10 at 09:58

Simon Courtenage

406
3
9

I've had a similar problem with OCCI and it was "solved" this way - with timer. But the problem was bigger there - so, when the timer expire, I call `exit(1);` (awful, I know, but no other solution was found, even a developer from Oracle couldn't help). Anyway, this is a bad solution, I'm looking for a more intelligent one. Thanks anyway for the idea (Y) – Kiril Kirov Nov 26 '10 at 10:27
I think this is the general way to handle external resources without locking up. This is essentially how any blocking resource call is handled if the operating system doesn't have that functionality built in. See socket programming. – Ape-inago Dec 02 '10 at 06:08

SubniC · Answer 3 · 2010-11-23T12:13:56.360

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

Regards

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

edited Nov 23 '10 at 12:13

answered Nov 23 '10 at 08:50

SubniC

9,807
4
26
33

Please put above under comments, OP has stated clearly his question – ajreal Nov 23 '10 at 08:52
The only thing this post(the link) says is a quote from the documentation, the exact I posted in my post. – Kiril Kirov Nov 23 '10 at 09:16
Sorry then, i did my best in this matter. – SubniC Nov 24 '10 at 16:08

score 0 · Answer 4 · answered Dec 02 '10 at 03:59

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.

answered Dec 02 '10 at 03:59

R.. GitHub STOP HELPING ICE

208,859
35
376
711

I can't do this for every query, as this will slow down the application's work, that is supposed to process 1000+ messages per second. Thanks anyway – Kiril Kirov Dec 16 '10 at 14:10
This should not be a performance problem with 1000 threads per second, but you might quickly run out of kernel resources to create that many threads if they don't exit quickly. :( – R.. GitHub STOP HELPING ICE Dec 16 '10 at 15:59

How to set a timeout for MySQL query using C API

4 Answers4

Linked