[ILUG] MySQL V4 with SSL has problems with iptables

[Bailey, Darragh]

Some background:

Have a problem at work with MySQL V4.1.22 on RHEL4 using SSL connections, where an iptables restart/reconfig will result in connections to the DB over SSL hanging for 10-15 minutes, until the TCP timeout kicks in.

What we have is a cluster with an instance of MySQL running on one node and all other nodes connecting to the DB server over the network. For security reasons we decided to make all the connections to DB authenticated using SSL cert's (and encrypted at the same time).

We also wanted to utilise iptables to lock down network access. But we found that restarting iptables on any of the nodes that were connecting to the DB would cause any open connections to hang, despite all outbound traffic being permitted by the iptables.

The problem I believe is a bug in MySQL's network socket handling.
Previously there was a bug found in the standard network code of MySQL which could cause problems with iptables, apparently it was doing a read which would block, when they should have been doing a select to check to see if there was a response first. Eventually the tcp connections would time out. This was fixed, but it appears not in the code path used when SSL is enabled.

It was eventually fixed in V5, however that's of limited benefit when using RedHat EL 4, since many programs were built against the older library and that library has not been fixed. V5 is libmysqlclient.so.15.0.0, V4 is libmysqlclient.so.14.0.0. Even the shared compat package for MySQL V5 on rhel4 is only including the old library which is broken.

For our own code using the C API, we can of course recompile against the newer library and all is well (already confirmed that), but we also use perl and python scripts which need to connect to the DB, and also any other package that is linked against the V4 client libs will have to be recompiled before they would work using SSL and not risk a hang if iptables is reconfigured.


Some solutions:

One solution is to avoid restarting iptables. Well we try to, but there's no guarantee that adding rules to or removing them from iptables without a restart won't trigger the problem at the most inconvenient time. An iptables restart will definitely trigger the problem but I'd like to avoid depending on that to keep everything working.


Another is to recompile everything that uses the mysql libraries that will be connecting to the DB instance over SSL. I'd like to avoid that, since it would involve recompiling updates from RedHat as well before deploying them.


What I'm Looking at:

Upgrading to MySQL V5, using the standard shared libraries and installing a custom rpm that will create symlinks for the older libraries to point to the newer V5 client libs and hope that they are very compatible. This would mean that all programs linked against the mysql client libraries would be using the newer V5 client library instead.


I've also considered trying to place some other app in between the client and server and try to tunnel the connections and see if by handling the network communication via that program if it would avoid the problem with the mysql client hanging on a iptables restart. I believe though that since mysql would open a connection over another port for the client to send queries over, it would need to detect the new connection and do some mangling to continue to tunnel the actual queries. Otherwise the only thing being tunnelled is the initial handshake part.


Anyone have any alternative ideas of what else I can do, besides badgering MySQL to fix the bug :)

--
Regards,
Darragh Bailey

Systems Software Engineer
Hewlett Packard Galway Ltd.
+353 91 75-4674

Postal Address:    Hewlett Packard Galway Limited, Ballybrit Business Park, Galway
Registered Office: Hewlett Packard Galway Limited, 63-74 Sir John Rogerson's Quay Dublin 2
Registered Number: 361933

_______________________________________________
The contents of this message and any attachments to it are confidential and may be legally privileged. If you have received this message in error you should delete it from your system immediately and advise the sender.
To any recipient of this message within HP, unless otherwise stated you should consider this message and attachments as "HP CONFIDENTIAL".