MySQL schema to schema syncronization through triggers?

Question

Quick note: I have 19 days to figure my client's problems out.

Background: Client hired a contractor who boasted he could get a new App out the door in 3 months. Two months and some days later I'm brought in and the individual was let go; there is no complete code, no thought put into the schema, and an abomination for a UI.

I have two applications: one production and mature and the other that needs some love. One has all the data I need and the other that doesn't. I'm writing new code TDD style and aiming for a partially jury rigged SOA infrastructure that covers all issues but the data itself. If I had more time I could use liquibase to refactor the schemas into abomination shards ( use your imagination ) but I don't...so plan B is as follows:

App A (inserts|updates|deletes) entity Foo which updates AppASchema.FooTable which via a post trigger updates the AppBSchema.FooLikeTable and vice versa.

I know this is an insane idea but its the least of the worst ideas I've got, my concerns are

1. Possible to create an endless loop (AppA trigger updates AppB which updates AppA )
2. There isn't high load, but this basically doubles ops to n*2 so if I hit half the capacity of the MySQL server, it seems like would be effectively at or near full capacity for basic stuff like updating indexes and such.
3. As a mixed blessing, the original schema designers made all tables InnoDB engine... thats horrible for performance but could this setup ensure a higher chance of keeping integrity.

My time budget to implement the triggers is 12 hours or bust.

Answer 1

Are AppASchema.FooTable and AppBSchema.FooLikeTable similar enough that you could reimplement one of them as an updatable view? You might have to create a few extra tables to hold columns that are unique to one of the app schemata. This would be much more maintainable than a bunch of triggers.

If not, and you have to implement it using triggers, you are right that you will have to be very careful to be sure there are no recursive trigger dependencies. If there are a handful of tables and they are fairly similar, this won't be too difficult. If there are a lot of tables or the similarities are few, it's going to take time.