[ale] database conversion

[David Muse]

On Thu, 10 Feb 2005 10:54:18 -0500
"James P. Kinney III" <jkinney at localnetsolutions.com> wrote:

> On Thu, 2005-02-10 at 10:16 -0500, Christopher Fowler wrote:
> > One method I thought of was to create a system in which there is
> > wrappers my code uses that will send inserts to multiple servers. 
> > Problem is that this system is not very robust.
> 
> I have been considering that method as well. The drawbacks are
> intermittent connectivity (fixable with a secondary script to cache
> and retry when connectivity returns) the overall size of the
> originating codebase (Interchange is freaking HUGE!)

Those are some of the challenges that I ran into while considering how
to implement front-end replication in SQL Relay.

If one of the databases goes down, the replication system has to know
about it, cache new queries and run them when it comes back up.  But if
the database went down in the middle of a transaction then what about
the queries that had already been run in that transaction?  Presumably
they'll be rolled back when the database recovers (but not necessarily,
depending on the database).  If they were rolled back though, you'll
need to rerun them too.  So, the replication system really has to cache
all queries off somewhere and it can empty the cache when a commit or
rollback succeeds.

There are challenges with auto-incrementing fields and oracle
sequences too.  Integrated replication systems can handle them
easily because they can do behind-the-scenes stuff but front-end
replication systems have to do a bit more work.

You also have to parse each query to make sure you only rerun
insert/update/delete/create/drop queries, but some databases support
obscure queries that insert/update/delete as a side effect so you really
need a fully fledged query parser to handle those.

Then there are stored procedures and triggers, who knows what they were
off doing when the database went down.

Some databases support dblinks,  where queries can be run against tables
in other databases.  They present challenges too.

And, of course, you have to make sure that no one runs any
insert/update/delete queries against any of the databases unless they're
run through the front-end replication system.

It seems like there are even more issues too, especially with
non-transactional databases, but I don't remember them offhand.  It's
fun stuff.  99% of it is pretty easy, but the remaining 1% is really
hard.  Every time I'd get geared up to work on it, I'd chicken out and
work on somethig else :)

Dave
david.muse at firstworks.com