Re: How to synchronize two database tables in MySQL?

This seems to be a MySQL question rather than a Perl one.

Having said that, I'll still offer a few comments (which may or may not be helpful)

The information you've provided is a little sketchy, although I believe I have a high-level understanding of what your problem is.

I've faced similar challenges in the past, and the approach I've taken is basically as follows:

Take a dump (mysqldump) of any existing data
Install the new tables
Massage the old data so that it fits into the new table structure
Pump the old data into the new tables.

Of course, all this is done in a test environment before it goes anywhere near a production system. And if step 3 requires anything more than some basic manipulation then I'll usually turn to Perl.

Hope this helps,
Darren :)

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 01:46 UTC
I'm not really concerned about data right now, just structure. I'll be setting default values for any new columns I might add, but I need everyone to have the same table definitions. Here's how this is currently done: Examine table structure to determine which version they have Run different queries depending on the version What I want to do is store the table format within the install script, which would then examine the database and run the appropriate query, whether it's a DROP or ALTER or CREATE query. In the course of thinking about this, my theoretical structure looks something like this: `my %tables = ('table1' => ( 'column1' => 'column definition', 'column2' => 'column definition') );` [download] with a separate hash for keys and storage engine types.	[reply] [d/l]
Re^3: How to synchronize two database tables in MySQL? by davidrw (Prior) on Jan 08, 2006 at 01:55 UTC
When you do #2, what are the various queries? i.e. are you making a set of queries to bring verion X up to current for all previous versions? If so, would be a lot easier (for this and future releases) if the set queries were to bring version X up to version Y (version X+1), and just apply step #2 iteratively until you're at the current state. You could obsolete #1 by starting to store the schema version number in the schema somewhere .. if you already have a config file/table could just put it in there .. or maybe a new table version_history with columns version_number, version_name, version_description, release_date, install_date ...	[reply]