Re: [codership-team] Galera in master write, slave failover setup and legacy code

[Alex Yurchenko]

Hi Christian,

On 2012-09-27 15:04, Christian Nygaard wrote:
> Hi, I'm thinking of using Galera with the Vbulletin forum software. I
> have
> read the limitation pages on the Codership wiki. I'm thinking of
> using
> Galera in a master write node, slave fail-over, slave fail-over
> 3-node db
> configuration, since its a legacy app I'm a bit concerned over how
> well and
> transparent it would be to use a master-master active write setup. It
> would

First thing to check, does it work with InnoDB tables if it is so
"legacy" that uses LOCK/UNLOCK?

> seem safer only to have one master write node. I still like the
> convenience
> and automatic replication and recovery of Galera that's very good
> compared
> to ordinary MySQL Master/Slave clustering. In the case of master
> failure I
> would use HAProxy to switch over from the failed master write node to
> one
> of the failover slave nodes.
>
> Regarding 1 DELETE below. I have made ALTER auto_increment primary
> key table statements to the database tables since there was 20+
> tables
> without primary keys. So although I would have added primary keys to
> the
> tables, I'm not sure that DELETE statements from the forum software
> would
> actually use the primary keys would that still be beneficial to
> Galera?
> What I'm trying to figure out is, does Galera require primary keys
> for
> internal use during DELETE operations or does the software have to
> use
> DELETE ... WHERE primary_key for it to be considered supported/the
> right
> way?

No, the application does not have to use primary keys. Primary key role
is to uniquely identify a row, so being present there is enough ;)

However you should watch for range DELETEs which can generate very big
writesets with ROW-based replication. E.g. TRUNCATE tbl is preferable to
DELETE FROM tbl. But I guess you have little control over it.

> Regarding Unsupported quires below. If you are using only one master
> read/write node, would local TABLE locking still work on that node
> when
> Galera replication is enabled to slave read nodes? There is for sure
> LOCK/UNLOCK table statements in the forum code.

Yes, it should be safe to use LOCK/UNLOCK in master-slave setup.

> Should I use wsrep_convert_LOCK_to_trx or would it be sufficient just
> to
> ensure all writes goes to one Master write node?

No, just make sure all LOCKs go to one node.

wsrep_convert_LOCK_to_trx does not really make LOCK/UNLOCK statements
work in multi-master configuration. It changes semantics of these
statements in an incompatible way and it is more likely to break things
than help. In fact it will most certainly break things. The only excuse
to use it is that you want/need to use read locks on slaves. Note that
this complicates things as you need to disable it when you failover
master to one of the slaves. On master it always should be OFF.

Regards,
Alex

> From the Galera Limitations page "
>
> 1. DELETE operation is unsupported on tables without primary key.
> Also
> rows in tables without primary key may appear in different order
> on
> different nodes. As a result SELECT…LIMIT… may return slightly
> different
> sets. *Don't* use tables without primary key. It is always
> possible to
> add an AUTO_INCREMENT column to a table without breaking your
> application.
> 2. Unsupported queries:
> - LOCK/UNLOCK TABLES cannot be supported in master-master
> replication.
> - lock functions (GET_LOCK(), RELEASE_LOCK()…)"
>
> //Christian

--
Alexey Yurchenko,
Codership Oy, www.codership.com
Skype: alexey.yurchenko, Phone: +358-400-516-011