[Bucardo-general] Concurrency errors when replicating a master to two slaves and installation experiences

[Greg Sabino Mullane]

> 1) Reading password from ~/.pgpass will fail if SELINUX is in 
> enforcing mode with it's "permission denied" message.  I had to 
> disable SELINUX to allow bucardo to read the file and connect to 
> databases

Good to know, worth a doc patch if anyone wants to step up (or 
just edit the wiki).

> 2) There seems to be an undocumented ~20 character limit to the 
> length of a sync name.  If I create a sync with a name 25 characters 
> long, it will be accepted by bucardo_ctl and even perform an initial
> sync but future updates will fail with key constraint errors like 
> this:
>         [Thu Oct 21 17:53:33 2010]  CTL Controller exiting at 
>         cleanup_controller. Reason: DBD::Pg::st execute failed: ERROR
>         :  insert or update on table "q" violates foreign key 
>         constraint "q_tdb_fk"
>         DETAIL:  Key (targetdb)=(jefferson_a) is not present in table
>         "db". at /usr/lib/perl5/vendor_perl/5.8.8/Bucardo.pm line 
>         3235.
> The database did exist in the db table but bucardo had truncated the 
> name it was searching for for some reason.  To fix the error, I just
> had to alter sync to have a name of 20 or less characters. (bug?)

There should be no length limit. There is some munging of the auto-generated 
trigger and notification names and the like to avoid running into the 
compiled 63 character limit on names of things in the database, but I can't 
see how that would affect the above. Are any of the syncs using uppercase 
characters by chance? That's caused some issues in the past.

> Looking at all other triggers, the 
> bucardo.bucardo_triggerkick_SYNCNAME one was missing the "SECURITY 
> DEFINER" keywords to take on the role of the bucardo user.  Once I 
> added this, validated syncs and restarted bucardo I was able to 
> truncate tables as normal. 

Not sure I understand this one yet. The triggerkick functions should 
only be doing a NOTIFY, which requires no extra perms. Can you verify 
what those functions are doing with a \df+?

> It looks like there's a race condition between the two sync triggers 
> as they each try to execute:
>         DELETE FROM bucardo.bucardo_delta WHERE txntime <= now() AND
>         tablename = $g->{oid}

Thanks, this is a known problem that probably won't get fixed in the 
current version, but will be avoided in future ones. Obviously, limiting 
the number of sync will help this out.

> The previously executed SQL was:
>         $SQL = 'UPDATE bucardo.bucardo_sequences SET value=?, 
>         iscalled=? WHERE tablename=?';
> I couldn't see a failed SQL statement that would cause this in the 
> postgres logs, although there were some errors about failing to 
> insert some records with duplicate primary keys.  I tried running 
> the same SQL again and I didn't get the error from bucardo.

There's definitely a bad race condition with the sequences table. 
I've not back-patched it yet, but need to. Keep reminding me. :)

> When I do a bucardo_ctl status on any of my syncs, I always get a 
> date in the future, eg a moment ago I got:
...
> Our servers are in Tasmania, Australia which is currently GMT +10.  I
> suspect this has something to do with it but I'm not sure what.

Thanks, I'll dig into this, probably related to that +10. A bug filing 
would be great.

> Does anyone have an idea of how to fix the concurrency problems I'm
> experiencing?

The best thing for now is to use a single sync, and have the master 
go to a targetgroup consisting of the two slaves.

-- 
Greg Sabino Mullane greg at endpoint.com
End Point Corporation
PGP Key: 0x14964AC8
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 163 bytes
Desc: not available
Url : https://mail.endcrypt.com/pipermail/bucardo-general/attachments/20101101/bcd7769a/attachment.bin