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.