Mysql vs. fork

Ruby
1

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

2

On Wed, 20 May 2009 22:14:53 +0900, Daniel DeLorme wrote:

7 Process.wait
testing but the flow of errormails is unmistakable. So what exactly was
wrong in my understanding of at_exit, sockets, mysql, and fork?

-Daniel

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)

3

On 20.05.2009 15:14, 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!}

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

4

Robert K. wrote:

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

5

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

6

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

7

On Thu, 21 May 2009 08:43:55 +0900, Daniel DeLorme wrote:

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

From my testing with DRb, they at least do something sensible regarding
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.