Sunday, October 12, 2008

Gotcha Of The Day - mysqldump and database views

I'm in the middle of migrating from a windows server to a Unix server, and needed to dump and restore a fairly large database. Using the definitive phpMyAdmin I was able to generate a dump. But, it failed to load into the target mysql database.

Here's what I needed to correct to get it to work:

  • Various PHP notices were getting included in the dump regarding some unknown offset. I ended up manually removing these by searching for the regular expression ^[<]. Though, probably could have cleaned the file all at once by saying:
     grep -v '^[<]' dump.sql > dump-clean.sql
  • The handling of database views was whacky. There was CREATE VIEW statement, followed by INSERT statements into that view (what the?!), followed by the actual CREATE TABLE statements which were used in the view. The result: the view failed to be created because the tables didn't exist, and then all the rows failed to insert because no table existed. In other words, mysqldump and views don't work.

    The latest version of mysqldump hints that this has been corrected.

    The best solution I could find was to not dump the views (manually unselect them), load in the database, and then re-create the views from my source tree's SQL files. Clumsy, but workable.

Don't get me wrong - I like MySQL and use it plenty. But, I do miss using PostgreSQL, as it has none of this nonsense.

1 comment:

  1. First off Cheers! on ditching MS and heading to Unix. Now are we talking Unix or Linux, anyways, if its open source a second Cheers!

    As for Postgre, I'm definitely a fan. Seems to serve all of my groups needs in a simple no complaint way. And I agree migrating from machine to machine is a breeze, we occasionally have to move tables/schemas between our old testing Sun server, developer Macs and linux workstations and our core Linux server and all with ease.

    Haven't had to migrate MySQL yet, the only installation we have using it is our wordpress site, but if we ever start using it more, I'll keep this in mind, don't think we'll ever be moving from MS since I don't plan on ever using it... but you never know.