# Mysql vs. fork **URL:** \ **Category:** ruby-talk\ **Created:** [20 May 2009 13:14 UTC](https://rubytalk.org/t/mysql-vs-fork/53486 "2009-05-20T13:14:53Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![Daniel\_DeLorme](https://avatars.discourse-cdn.com/v4/letter/d/9d8465/32.png) [@Daniel\_DeLorme](https://rubytalk.org/u/Daniel_DeLorme)\ **Post date:** [20 May 2009 13:14 UTC](https://rubytalk.org/t/mysql-vs-fork/53486/1 "2009-05-20T13:14:53Z")
It's commonly known that database connections do not play well with Process.fork; I thought that using at\_exit{exit!} was the correct way to solve this problem, but it appears imperfect. Take this pseudo-code: 1 db1 = mysql\_connect() 2 Process.fork do 3 at\_exit{exit!} 4 db2 = mysql\_connect() 5 db2.query(...) 6 end 7 Process.wait 8 db1.query(...) With line 3, only the at\_exit handlers defined in the child process are run when the child exits, ensuring that db1 stays alive and line 8 keeps working. At least that's what I thought. But in the past 2 days I've been getting some "MySQL server has gone away" errors from our servers, indicating that at\_exit{exit!} is not enough to protect the db1 connection. It's not consistently reproducible; I can't get the error when directly testing but the flow of errormails is unmistakable. So what exactly was wrong in my understanding of at\_exit, sockets, mysql, and fork? -Daniel ---
**Author:** ![Ken\_Bloom](https://avatars.discourse-cdn.com/v4/letter/k/f08c70/32.png) [@Ken\_Bloom](https://rubytalk.org/u/Ken_Bloom)\ **Post date:** [20 May 2009 16:10 UTC](https://rubytalk.org/t/mysql-vs-fork/53486/2 "2009-05-20T16:10:04Z")
One solution is to encapsulate the database connection in a DRb server. I have written about this in ruby-talk:296032 (see http:// blade.nagaokaut.ac.jp/cgi-bin/scat.rb/ruby/ruby-talk/296032) > **···** > > On Wed, 20 May 2009 22:14:53 +0900, Daniel DeLorme wrote: > > > It's commonly known that database connections do not play well with > > Process.fork; I thought that using at\_exit{exit!} was the correct way to > > solve this problem, but it appears imperfect. Take this pseudo-code: > > > > 1 db1 = mysql\_connect() > > 2 Process.fork do > > 3 at\_exit{exit!} > > 4 db2 = mysql\_connect() > > 5 db2.query(...) > > 6 end > > 7 Process.wait > > 8 db1.query(...) > > > > With line 3, only the at\_exit handlers defined in the child process are > > run when the child exits, ensuring that db1 stays alive and line 8 keeps > > working. > > > > At least that's what I thought. But in the past 2 days I've been getting > > some "MySQL server has gone away" errors from our servers, indicating > > that at\_exit{exit!} is not enough to protect the db1 connection. It's > > not consistently reproducible; I can't get the error when directly > > testing but the flow of errormails is unmistakable. So what exactly was > > wrong in my understanding of at\_exit, sockets, mysql, and fork? > > > > -Daniel > > -- > Chanoch (Ken) Bloom. PhD candidate. Linguistic Cognition Laboratory. > Department of Computer Science. Illinois Institute of Technology. > [http://www.iit.edu/~kbloom1/](http://www.iit.edu/~kbloom1/) ---
**Author:** ![Robert\_K1](https://yyz1.discourse-cdn.com/flex029/user_avatar/rubytalk.org/robert_k1/32/1830_2.png) [@Robert\_K1](https://rubytalk.org/u/Robert_K1)\ **Post date:** [20 May 2009 19:50 UTC](https://rubytalk.org/t/mysql-vs-fork/53486/3 "2009-05-20T19:50:59Z")
> It's commonly known that database connections do not play well with > Process.fork; I thought that using at\_exit{exit!} was the correct way > to solve this problem, but it appears imperfect. Take this pseudo-code: > > 1 db1 = mysql\_connect() > 2 Process.fork do > 3 at\_exit{exit!} That line above looks dangerous. After all, what's the point in forcing an exit while we are exiting? > 4 db2 = mysql\_connect() > 5 db2.query(...) > 6 end > 7 Process.wait > 8 db1.query(...) > > With line 3, only the at\_exit handlers defined in the child process are > run when the child exits, ensuring that db1 stays alive and line 8 keeps > working. Not sure whether that's true. May well be that exit! interrupts the exit handler process... > At least that's what I thought. But in the past 2 days I've been getting > some "MySQL server has gone away" errors from our servers, indicating > that at\_exit{exit!} is not enough to protect the db1 connection. It's > not consistently reproducible; I can't get the error when directly > testing but the flow of errormails is unmistakable. So what exactly > was wrong in my understanding of at\_exit, sockets, mysql, and fork? The problem with your approach is that your client inherits the DB connection. That's bad because the server thinks he has one peer only. It's not much of an issue if the child does not use the socket but it would be better to close it. Unfortunately you cannot simply close it because you likely won't get access to it as it is buried somewhere in your DB connection class. And if you use the connection's close method chances are that the server shuts down the socket and both clients suffer. You can experiment a bit with these: #!/usr/bin/env ruby19 require 'socket' server = TCPServer.new 7887 client = server.accept client.each do |line|    puts line end #!/usr/bin/env ruby19 require 'socket' server = TCPSocket.new '127.0.0.1', 7887 fork do    server.puts "child"    server.close end sleep 2 server.puts "parent" server.close There is a much simpler solution - just don't inherit the open connection: 2 Process.fork do 4 db2 = mysql\_connect() 5 db2.query(...) 6 end 1 db1 = mysql\_connect() 7 Process.wait 8 db1.query(...) Btw, it may well be that mysql\_connect supports a block so you might be able to write this which is usually safer: fork do    mysql\_connect do |db|      db.query    end end mysql\_connect do |db|    db.query    Process.wait    db.query end Cheers   robert > **···** > > On 20.05.2009 15:14, Daniel DeLorme wrote: > > -- > remember.guy do |as, often| as.you\_can - without end > [http://blog.rubybestpractices.com/](http://blog.rubybestpractices.com/) ---
**Author:** ![Brian\_Candler](https://avatars.discourse-cdn.com/v4/letter/b/5f9b8f/32.png) [@Brian\_Candler](https://rubytalk.org/u/Brian_Candler)\ **Post date:** [21 May 2009 10:06 UTC](https://rubytalk.org/t/mysql-vs-fork/53486/4 "2009-05-21T10:06:58Z")
Daniel DeLorme wrote: > It's commonly known that database connections do not play well with > Process.fork; I thought that using at\_exit{exit!} was the correct way > to solve this problem, but it appears imperfect. Take this pseudo-code: > > 1 db1 = mysql\_connect() > 2 Process.fork do > 3 at\_exit{exit!} > 4 db2 = mysql\_connect() > 5 db2.query(...) > 6 end > 7 Process.wait > 8 db1.query(...) As long as the child process doesn't touch the db1 socket, nothing should happen. So I guess what's happening is that the mysql connection object has a finalizer, and that finalizer is sending something down the socket when the child terminates, which is of course the same socket that the parent process has. The traditional Unix way of handling this is immediately to close the db1 socket in the child (without sending any data down it, of course). I don't know if the MySQL API exposes the underlying socket in a convenient way: e.g. db1.socket.close > **···** > > -- > Posted via [http://www.ruby-forum.com/\](http://www.ruby-forum.com/%5C). ---
**Author:** ![Daniel\_DeLorme](https://avatars.discourse-cdn.com/v4/letter/d/9d8465/32.png) [@Daniel\_DeLorme](https://rubytalk.org/u/Daniel_DeLorme)\ **Post date:** [20 May 2009 23:43 UTC](https://rubytalk.org/t/mysql-vs-fork/53486/5 "2009-05-20T23:43:55Z")
Ken Bloom wrote: > One solution is to encapsulate the database connection in a DRb server. I have written about this in ruby-talk:296032 (see http:// > blade.nagaokaut.ac.jp/cgi-bin/scat.rb/ruby/ruby-talk/296032) Hmm, in that message when you wrote that "DRb connections will work just fine across forks" did you mean that the parent's connection won't be closed when the child process exits? Because otherwise, I don't see how both parent and child could share a forked socket. -Daniel ---
**Author:** ![Daniel\_DeLorme](https://avatars.discourse-cdn.com/v4/letter/d/9d8465/32.png) [@Daniel\_DeLorme](https://rubytalk.org/u/Daniel_DeLorme)\ **Post date:** [21 May 2009 00:38 UTC](https://rubytalk.org/t/mysql-vs-fork/53486/6 "2009-05-21T00:38:10Z")
Robert Klemme wrote: > > 1 db1 = mysql\_connect() > > 2 Process.fork do > > 3 at\_exit{exit!} > > That line above looks dangerous. After all, what's the point in forcing an exit while we are exiting? > > > 4 db2 = mysql\_connect() > > 5 db2.query(...) > > 6 end > > 7 Process.wait > > 8 db1.query(...) > > > > With line 3, only the at\_exit handlers defined in the child process are > > run when the child exits, ensuring that db1 stays alive and line 8 keeps > > working. > > Not sure whether that's true. May well be that exit! interrupts the exit handler process... Yes, that's exactly what it does. And since at\_exit handlers are run from last defined to first defined, line 3 causes any handlers defined in the parent process to be skipped. Far from dangerous, it's safer to have the at\_exit handlers run only once, in the parent. > The problem with your approach is that your client inherits the DB connection. That's bad because the server thinks he has one peer only. It's not much of an issue if the child does not use the socket but it would be better to close it. Unfortunately you cannot simply close it because you likely won't get access to it as it is buried somewhere in your DB connection class. And if you use the connection's close method chances are that the server shuts down the socket and both clients suffer. Thanks, that clears up some confusion I had. So closing a forked socket in a child is not a problem at all; the only problem is if the child somehow signals to the mysql server that it's ok the close the socket. With the at\_exit handler that \*shouldn't\* happen, but obviously it's happening somehow. Now gotta find out why. > There is a much simpler solution - just don't inherit the open connection: > > 2 Process.fork do > 4 db2 = mysql\_connect() > 5 db2.query(...) > 6 end > > 1 db1 = mysql\_connect() > 7 Process.wait > 8 db1.query(...) Well, in my actual code the 'db1' mysql connection is opened during the program's initialization stage, way before the fork ever happens. Actually that's how I tested that the problem was really due to the fork; having the parent process reconnect to mysql after the fork (as above) stopped the errors. But reconnecting to the db whenever I want to fork seems like such a kludge. I mean, there's a number of workarounds for this problem but what I'm seeking is the reason why it's happening in the first place. -Daniel ---
**Author:** ![Ken\_Bloom](https://avatars.discourse-cdn.com/v4/letter/k/f08c70/32.png) [@Ken\_Bloom](https://rubytalk.org/u/Ken_Bloom)\ **Post date:** [21 May 2009 13:39 UTC](https://rubytalk.org/t/mysql-vs-fork/53486/7 "2009-05-21T13:39:59Z")
On UNIX, parent and child can \*always\* share a forked socket, and that's how pipes work in the first place. The question is whether the protocol is designed so that having two processes using the same end of the socket will do something sensible or not. closing the socket -- the first process exiting doesn't tell server to shut down the connection on the second process. I'm not sure whether the protocol is still sensible when two processes are using it concurrently. If you keep following the chain of links back from the post I linked you to this time (and the other posts in those threads), then you'll see that I used this technique for recovering from a segfault in the child by respawning a new child. I don't guarantee that you could do something concurrent in the child. What you \*could\* do is open a new DRb connection to the same server in the child and use that. Then you won't have concurrency issues in the DRb protocol, though you may need to find out how well the MySQL client in the DRb server can handle concurrency. > **···** > > On Thu, 21 May 2009 08:43:55 +0900, Daniel DeLorme wrote: > > > Ken Bloom wrote: > > > > > One solution is to encapsulate the database connection in a DRb server. > > > I have written about this in ruby-talk:296032 (see http:// > > > blade.nagaokaut.ac.jp/cgi-bin/scat.rb/ruby/ruby-talk/296032) > > > > Hmm, in that message when you wrote that "DRb connections will work just > > fine across forks" did you mean that the parent's connection won't be > > closed when the child process exits? Because otherwise, I don't see how > > both parent and child could share a forked socket. > > > > -Daniel > > From my testing with DRb, they at least do something sensible regarding > -- > Chanoch (Ken) Bloom. PhD candidate. Linguistic Cognition Laboratory. > Department of Computer Science. Illinois Institute of Technology. > [http://www.iit.edu/~kbloom1/](http://www.iit.edu/~kbloom1/)