Posterous theme by Cory Watilo

Dumping MySQL to import into a new Spree instance

It seems every time I try to upgrade my Spree instance, it never goes right. It always boils down to migrations that don’t work, which is probably my fault since I’m sure I borked something about the migrations and schema at one point.

Last time I upgraded, I thought for sure I’d fixed it however. I did a new store from the ground up and imported all of my db info, fixing it manually where the schema had been modified. It worked for a good while as 0.70, then the upgrade to 1.1 came up.

Because it was such a large upgrade, it actually had to be performed in two steps, first to 1.0 and 1.1. So I correct my earlier statement in one case: the 0.70 to 1.0 upgrade went off without a hitch, migrations and all. Imagine how happy I was.

However, 1.1 bombed miserably. I ended up with failed migrations on the last step, the (now optional) product_groups migration. Suffice to say I learned a little better this time, now using the rake db:migrate:redo command to undo and redo the trouble migrations. Theoretically this should have taken care of the other considerations wrt schema.rb and who knows what else gets modified by rake db:migrate.

However, once the migrations seemed happy and the server started up, the index paged errored out with a message like “Association not found ‘images’”. I have no idea what this means.

To see if there were something wrong with my environment, I installed a spanking-new Spree 1.1, which worked grandly.

So, not knowing what else to do, I’m back to my method of creating a new store that’s known to work and finding a way to manually import my data into the new schema, making any necessary fixes manually. Here’s keeping my fingers crossed.

Which brings me to the point at hand, which is that I forgot to post the magic mysqldump command that let me do the database import in the first place.

It’s: mysqldump -u [user] -c -t [database] > [output file]

The -t is for “no-create-info”, which tells it not to include the table drops and creates that are in a normal sqldump. Since I’m importing into a database already set up by the new instance, I want to use its schemas, not the old db’s.

The -c is for “complete-insert”, which tells it to include column names in the INSERT statements. Since the new schema may (read: will) have new columns, the positional insert which implies the columns will not usually work. This method forces the values into the proper columns, and hopefully the column values not addressed will be ok as NULLs. Obviously, this will not be the case 100% of the time, but so far so good.

So basically, I dump the old db with this command, attempt to import it into the new db which was set up by the new store, look for errors and then fix them manually. Errors tend to crop up around table renames and unacceptable NULLs.