[tpop3d-discuss] multiple mysql servers implementation

[Yann GROSSEL]

On Wed, 16 Oct 2002 12:46:57 +0100
Chris Lightfoot <chris@ex-parrot.com> wrote:

> > - multiple mysql servers (we'll be using a mysql cluster). I've
> > seen that this feature has been added to the TODO file in CVS and
> > I'm willing to implement it. I've already done a small patch that
> > seem to work but it still has a few problems. I'll keep working on
> > it.
> 
> Excellent. What approach are you using?

Well, I'll try to explain. The goal is to allow the user to define several
mysql server hostnames or ip addresses for the auth-mysql-hostname
parameter in tpop3d.conf, instead of only one. tpop3d will use the first
mysql server defined until it goes down. It will then switch to the second
one, and so on.

My current patch adds an array of MYSQL handles in auth_mysql.c. The
auth_mysql_init() function creates and initialize one MYSQL handle for
each mysql server defined in the config file, and then immediately do a
call to mysql_real_connect() to connect to the server.

The 'mysql' variable is now just a pointer to one of the MYSQL handles -
the one currently beeing used. If at any moment the current handle stops
working (the mysql server goes down), all handles from the array are tried
until one is found working.

This approach has two main problems :

- there is one TCP connection created for each mysql server defined, but
  only one of these connections is used until it fails. That's not very
  pretty, and after some hours the unused mysql connections will be
  timeouted by the mysql servers (I think a mysql_ping() may reactivate a
  timeouted connection, but I'm not sure).

- If during startup, any of the defined mysql servers is down, it won't be
  included in the array of handles - and thus won't ever be used even if
  it goes up again and all of the others servers go down ! That is really
  bad.

I've choosen this approach because at first I wanted to keep all the
strclr() calls at the end of auth_mysql_init(). These strclr() clear the
configuration values (mysql server hostnames, user, password) from memory,
so it's impossible to connect to any server during runtime. So
unfortunaltely I think there isn't a lot of other possible approaches if
we have to keep using the strclr().

The strclr() logic is aimed to prevent dangerous data (mysql passwords) to
be visible in coredumps in case of the daemon crash. That's a good try,
but I think it's not that useful : the mysql_real_connect() function does
a strdup() of these data when called and store them in the MYSQL handle
structure. It's done that way in order to allow libmysql to reconnect
automatically to the mysql server at any time if it goes away. So in any
way, the mysql usernames/passwords will be present in a coredump of the
daemon... even if these data are strclr()ed after init :)

I'd like to rewrite my patch from scratch, using a better approach :
connecting to only one server at a time, and doing new
mysql_real_connect() only when the currently used server goes down. But
I'll have to get rid of all strclr() :)

So my question is: 

will anybody be too upset if I go and remove these strclr() ? :)
(or if someone sees a different way of doing things, please tell me).

Regards,

Yann