On Tue, 2006-12-12 at 20:22 +0100, Andreas Kostyrka wrote:
> * Andrew Sullivan <ajs at crankycanuck.ca> [061212 19:27]:
> > On Tue, Dec 12, 2006 at 06:48:16PM +0100, hubert depesz lubaczewski wrote:
> > > question is - what is the worst case scenario? what should happen for me to
> > > get punished for this?
> > 
> > - Inserts to the master break
> > - Replication attempts break (on a set containing the data that's
> > causing the problem) -- which blocks all subsequent replication too,
> > note.
> > 
> > > as for now - even with bad (kvvvv instead of kvvvvv) triggers i still get
> > > good results (kudos to dev team).
> > 
> > No, you're _not_ getting good results.  You're getting lucky.  The
> > problem crops up in a way that makes it hard to predict when it will
> > happen (it's not indeterminate, it's just got a lot of variables).
> > 
> > > is it safe but not sugested, or there is some scenario which could lead to
> > > something bad? if yes, then what scenario and what bad?
> >
> > No, it is not safe.  This is the basis of the discussion recently. 
> > See the recap above.
> 
> Ok, so how does the 
> psql -c 'alter table x add column y text;' -h slave
> psql -c 'alter table x add column y text;' -h master
> 
> break? I need to ask as I've been using it for over 6 months without
> troubles.

As mentioned, the slony triggers are aware of the columns on the tables.
When you add a column manually, it is not aware of that column. 

> The only thing that I've noticed is, that in some cases (related to
> foreign key constraints when dropping columns and probably the way
> that slony breaks the schema data), one needs to apply the
> changes to the slaves via EXECUTE SCRIPT on the slave nodes only.
> 
> > 
> > If you need to do DDL on a resplicated table, you REALLY REALLY do
> > need to allow the blocking.  Sorry.  (And are you really telling me,
> > anyway, that you can't block for even 5 minutes one time in a
> > planned way?  Slony does not provide "five 9s", you know.)
> <sarcasm source=some_devs_at_the_office>
> Well, mysql seems to have "sensible" replication that handles DDLs
> sensibly. Well, that's why PostgreSQL and slony are scheduled here to
> be phased out ;)
> </sarcasm>

MySQL replication is littered with all sorts of gotchas (some involving
data loss to the replicas) - under normal operation.  

Here's the official ones:

http://dev.mysql.com/doc/refman/5.0/en/replication-features.html

There are others floating around that involve data being silently lost.
Google around a bit.

> Yes, it's possible to do planned shutdowns. It's just very bothersome,
> and doesn't look to well, when all the other components are being
> optimized for upgrades under load, and I need to kill the DB
> connections to add a field? While I can rollout major upgrades to the
> application without interrupting service?

Is this not better than explaining to your superiors why you tanked your
replicas, or worse, you data, by ignoring absolutes about the way your
tool works?

-- 
Brad Nicholson  416-673-4106
Database Administrator, Afilias Canada Corp.