The best solution is twofold:

1. For each update, write an upgrade script for the DB to go from the most recent schema to this one.
2. 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.

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.

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:

1. Take a dump (mysqldump) of any existing data
2. Install the new tables
3. Massage the old data so that it fits into the new table structure
4. 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 :)

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:

1. Examine table structure to determine which version they have
2. 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.

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 ...

It's been a long time since I used Alzabo, so I'm not sure of the current status or whether this plan is feasible. Perhaps someone else can provide more up to date information.

Usually I just diff the schema dumps and migrate by hand. But as I have only one deployment it's easy.

mysqldump dev  -d --add-drop-table=false > dev.sql
mysqldump live -d --add-drop-table=false > live.sql

perl -pi -e 's/^--.*\n//;s{/\*.*\*/;\n?}{}' dev.sql live.sql 

diff -B -U 5 dev.sql live.sql > schema.diff
[download]

Brad

I had the same problem, and decided that the simplest solution was to purchase a 3rd party tool. I bought SQLBalance. It was really buggy, my questions went unanswered, and in the end, I forked out yet more money for Sync Database. It was much more expensive, but at least it worked

So why spend money? Well, I couldn't find any OS solutions, and I needed something at short notice. Generally you can compare the db definitions and generate delta SQL yourself, but there's also possibilities that there may be bugs in your code, and I needed something quick - and which worked. I lucked out with SQLBalance, but at least I discovered the problems in my test run. Sync Database has worked for me, but I personally I think that it's way overpriced.

It's a mySQL problem, but there's a Perl solution: SQL::Translator, aka SQLFairy.

So yes, other people have had the problem before you. I've never used it for what you're describing, as I only came across it last week, but it has a 'sqldiff', which will attempt to take two schemas, and give you the changes necessary to turn the first into the second. (see the documentation, as there are some special notes about the diff it generates)

this_version_tablename1.col1|version-1_table_col|version-2...

this_version_tablename1.col2|...

...

this_version_tablename2.col1|...

...

...

Leaving blank the places where the column did not exist in the relevant version. i.e. the columns delimited by '|' represent database versions and each such column could be in the form version.table.column

Then given any source version, all the data can be migrated automatically to any target version according to the table with the help of a simple script that:

- reads the mapping file into a AoHoH (array of version of hash of table of hash of column)

- uses that to iterate reading in from any old version database and to lookup where in the new database to put it.