Re: How to synchronize two database tables in MySQL?

You don't want to synchronize them blindly. If all you ever do is add nullable columns, then you're find. However, if you remove a column or change a column to a different type or add restrictions (NOT NULL, foreigh key, etc), then you have a hard problem.

The best solution is twofold:

For each update, write an upgrade script for the DB to go from the most recent schema to this one.
Require your users to upgrade to each version. They cannot skip versions.

You can automate step 2 any number of ways. It will take them a little longer to upgrade, but with a better chance of a successful upgrade.

My criteria for good software:

Does it work?
Can someone else come in, make a change, and be reasonably certain no bugs were introduced?

Comment on Re: How to synchronize two database tables in MySQL?

Replies are listed 'Best First'.
Re^2: How to synchronize two database tables in MySQL? by Cap'n Steve (Friar) on Jan 08, 2006 at 06:03 UTC
I suppose an incremental upgrade would be the safest solution, although that seems less elegant. As I said, in the brief time I've worked on this problem it seems that adding a default value would solve most of the issues. I did however, run into some problems with keys, especially the fact that all auto_increment columns must be primary keys so you can't simply alter each column, then drop and re-add the indexes as was the original plan.	[reply]

Replies are listed 'Best First'.

Re^2: How to synchronize two database tables in MySQL?
by Cap'n Steve (Friar) on Jan 08, 2006 at 06:03 UTC

I suppose an incremental upgrade would be the safest solution, although that seems less elegant. As I said, in the brief time I've worked on this problem it seems that adding a default value would solve most of the issues. I did however, run into some problems with keys, especially the fact that all auto_increment columns must be primary keys so you can't simply alter each column, then drop and re-add the indexes as was the original plan.

[reply]