DBD::Multiplex, mysql replication and transactions

Hi all,

We've just setup MySQL replication, using the example from the DBD::Multiplex
pod to connect to the master and slaves. This works beautifully: writes go to
the master, and reads go to the slaves.

But now I'm running into an issue with transactions. The problem is that newly
created records aren't replicated to the slaves until the transaction is
commited. This in turn means that I can't do SELECTs on those records from
inside the transaction.

Now, I fully understand why this happens, and how. Each part of the process
makes perfect sense. But, I need a quick fix to DBD::Multiplex, so that it
performs SELECTS on the master during a transaction.

I took a quick stroll through the source code, and I believe the following
change may do the trick:

# change line 151 (version 1.98) from
if ($parent_handle->{'mx_master_id'} &&
&DBD::Multiplex::mx_is_modify_statement(\$statement)) {

# to
if ($parent_handle->{'mx_master_id'} && (
&DBD::Multiplex::mx_is_modify_statement(\$statement) or
$parent_handle->{AutoCommit}==0 )) {


Can anybody confirm that this is a sensible change to make? Or is there a
better way to notice an ongoing transaction?

Oh, I do realise that this probably isn't a general patch. But I do believe it
would be useful to have some mechanism for dealing with transactions. In my
case transactions need to go through the master handle. In other situations,
there probably should be something that binds transactions to a specific
handle as well.

Thoughts?

Rhesa


--
#!/usr/bin/perl
tie %rope, 'Tree' && hang $self;

Re: DBD::Multiplex, mysql replication and transactions

On Sun, May 14, 2006 at 02:05:32PM +0200, Rhesa Rozendaal wrote:
> Hi all,
>
> We've just setup MySQL replication, using the example from the
> DBD::Multiplex
> pod to connect to the master and slaves. This works beautifully: writes go
> to
> the master, and reads go to the slaves.
>
> But now I'm running into an issue with transactions. The problem is that newly
> created records aren't replicated to the slaves until the transaction is
> commited. This in turn means that I can't do SELECTs on those records from
> inside the transaction.
>
> Now, I fully understand why this happens, and how.

(The joys of open source.)

> Each part of the process
> makes perfect sense. But, I need a quick fix to DBD::Multiplex, so that it
> performs SELECTS on the master during a transaction.
>
> I took a quick stroll through the source code, and I believe the following
> change may do the trick:
>
> # change line 151 (version 1.98) from
> if ($parent_handle->{'mx_master_id'} &&
> &DBD::Multiplex::mx_is_modify_statement(\$statement)) {
>
> # to
> if ($parent_handle->{'mx_master_id'} && (
> &DBD::Multiplex::mx_is_modify_statement(\$statement) or
> $parent_handle->{AutoCommit}==0 )) {
>
> Can anybody confirm that this is a sensible change to make? Or is there a
> better way to notice an ongoing transaction?

Looks reasonable to me.

> Oh, I do realise that this probably isn't a general patch. But I do believe it
> would be useful to have some mechanism for dealing with transactions. In my
> case transactions need to go through the master handle. In other situations,
> there probably should be something that binds transactions to a specific
> handle as well.
>
> Thoughts?

More flexible control would be good, but I value safety and accuracy so
I think this change should be added as-is. Anyone needing more flexibity
can send a patch to provide it :)

Thanks.

Hopefully Thomas Kishel is listening and can release an update.
(I also need to upgrade the version that ships with the DBI.)

Tim.