1

For THIS reason, I want to try something new - close the socket using some system call.

The situation in two words - can't set query timeout of the mysql library (the C API, refer to the link for more info), so I want to try closing the socket to see how the library will react. Probably this is not a good idea, but still wanna try it.

Here's what I've done - there's another started thread - a timer. So, after a specific timeout (let's say 10 second), if there's no response, I want to close the socket. The MYSQL struct has member net, that is also a struct, and holds the fd. But when I try to do this:

shutdown( m_pOwner->m_ptrDBConnection->m_mysql.net.fd, SHUT_RDWR );
close( m_pOwner->m_ptrDBConnection->m_mysql.net.fd );

nothing happens. The returned values from shutdown and close are 0, but the socket is still opened (because after 60sec waiting, there's a returned result from the DB, that means that the mysql client is still waiting for response from the DB.

Any ideas?

Thanks

EDIT - Yes, there's a running transaction, while I'm trying to close the socket. But this is the actual problem - I cannot terminate the query, nor to close the connection, nothing, and I don't wanna wait the whole timeout, which is 20min and 30 sec, or something like this. That's why I'm looking for a brute-force.. :/

Community
  • 1
  • 1
Kiril Kirov
  • 37,467
  • 22
  • 115
  • 187

3 Answers3

1

Just a shot in the dark, but make sure you cancel/terminate any running transactions. I'm not familiar with the MySQL C API, but I would imagine there is a way to check if there are any active connections/queries. You may not be able to close the socket simply because there are still things running, and they need to be brought to some "resolved" state, be that either committed or rolled back. I would begin there and see what happens. You really don't want to shutdown the socket "brute force" style if you have anything pending anyway because your data would not be in a reliable "state" afterwards - you would not know what transactions succeeded and which ones did not, although I would imagine that MySQL would rollback any pending transactions if the connection failed abruptly.

EDIT: From what I have found via Googling "MySQL stopping runaway query", the consensus seems to be to ask MySQL to terminate the thread of the runaway/long-running query using

KILL thread-id

I would imagine that the thread ID is available to you in the MySQL data structure that contains the socket. You may want to try this, although IIRC to do so requires super user priviledges.

EDIT #2: Apparently MySQL provides a fail-safe mechanism that will restart a closed connection, so forcefully shutting down the socket will not actually terminate the query. Once you close it, MySQL will open another and attempt to complete the query. Turning this off will allow you to close the socket and cause the query to terminate.

The comments below show how the answer was found, and the thought process involved therein.

Will
  • 3,500
  • 4
  • 30
  • 38
  • I completely agree with you about the brute-force, but I still wanna try it. It's too long for explanation why. And yes - there's a running transaction and this is what I'm trying to do - to cancel it somehow. I tried lots of ways - nothing helped. That's why I reached this state and I wanna try to force closing the socket. – Kiril Kirov Dec 16 '10 at 14:30
  • Do you know which transaction when you go to close the socket, as in, do you have a handle/pointer to the transaction, or is it simply that one is active and you do not have a way to reference it? – Will Dec 16 '10 at 14:32
  • I have a pointer to the prepared query and call `Execute` on it. If this is what you mean. – Kiril Kirov Dec 16 '10 at 14:38
  • May want to look at http://stackoverflow.com/questions/2065396/how-to-stop-a-running-mysql-query ... article mentions java instead of C, but the SQL commands would be the same. – Will Dec 16 '10 at 14:42
  • Yep, but this is not possible, because I want to kill query, when the server is down, and not to wait for response. To execute `KILL` statement, I need the id, that is stored only in the DB, that I don't have access to(as the server is down). I have no idea why the hell `Execute` doesn't understand, that the server is down..(this happens on changed floating IP - detached from the first server and attached to another) – Kiril Kirov Dec 16 '10 at 14:46
  • Oh, I see ... that is odd; I would imagine when the server goes down, the TCP connection would terminate anyway, and the connection would receive some form of error (EPIPE, for example) telling it that the connection was closed by peer. – Will Dec 16 '10 at 14:53
  • Perhaps that is why the `shutdown` and `close` calls are returning `0` - the socket is already "closed", even though the connection trying to read data from it has not yet realized it. You may want to try kicking off the connection on a separate thread. Then you should be able to, at the very least, `shutdown` & `close` the socket, then kill the thread. – Will Dec 16 '10 at 15:09
  • If the socket really is closed, then why this happens: repeat the same situation, but don't drop the connection to the server, but put a long sleep in the query - 60sec. After 10sec, close and shutdown are called, the result is 0, but after 50 more seconds, a response is returned through the same socket.. :/ The only thing I can think of, is that I'm trying to close the wrong socket.. but I'm 99% percent sure, that I'm not wrong. – Kiril Kirov Dec 16 '10 at 15:12
  • Hmm ... in that case, verify the value of the `fd` before/after the calls to shutdown/close and when the response returns. If it is different when the response returns, there could be something internally reopening the socket as a "fail-safe". I will have to read through some MySQL documentation before I can venture any further guesses at this. Will post more when I've had a chance to do so. – Will Dec 16 '10 at 15:19
  • Argh, right, this is absolutely possible. I haven't thought about that. Thanks – Kiril Kirov Dec 16 '10 at 15:29
  • @Will - thanks maaaan!!! This is, that was the problem - there's "fail-safe" of MySQL library, I turned it off and everything is more that perfect !!! Thanks a lot!! Please, write this in another, separate, answer, and I will accept it. – Kiril Kirov Dec 17 '10 at 09:17
  • 1
    Very cool ... actually will edit this answer. Glad I could help. – Will Dec 17 '10 at 14:25
0

It looks like you are running into an issue with the TCP wait timer, meaning it will close eventually. [Long story short] it is sort of unavoidable. There was another discussion on this.

close vs shutdown socket?

Community
  • 1
  • 1
Brad
  • 11,262
  • 8
  • 55
  • 74
0

As far as I know, If shutdown() and close() both return 0 there's no doubt you had successfully closed a socket. The fact is that you could have closed the wrong fd. Or the server could not react properly to a correct shutdown (if so, this could be considered a bug of the server: no reason to still wait for data incoming). I'd keep looking for a supported way to do this.

ceztko
  • 14,736
  • 5
  • 58
  • 73
  • Yes, according to the man pages, both `shutdown` and `close` return 0 on success. And I agree, a *supported* way would be a better choice than trying to simply *pull the plug* on the socket, so to speak. – Will Dec 16 '10 at 14:30
  • I'm looking for such "supported" way for days and nothing :/ – Kiril Kirov Dec 16 '10 at 14:36