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.
