Nightmare MySQL migration

I've been migrating a large number of websites over from one server to another, always an enjoyable task.

So I get to the last one, copy the database over and do the normal mysql import along the lines of:

mysql -ufoo -pbar database_name < database_name.sql

Errors++

Turns out the Donke^H^H^H^HDeveloper who built the application didn't care about things like reserved words or spaces in the table and field names. Check out this example of a table, made up but containing valid examples:

[mysql] CREATE TABLE Site-Password ( # ^^^^^^^^^^^^^ HYPHEN and RESERVED WORD Name of AdviceClient varchar(255) default NULL, # ^^^^^^^^^^^^^^^^^^^^ SPACES Date - TEXT varchar(50) default NULL, # ^^^^^^^^^^^ TWO RESERVED WORDS!, SPACES and HYPHEN MeetingDate datetime default NULL, NextMeetingDate datetime default NULL, Advice Type - 1 varchar(255) default 'Initial Interview', # ^^^^^^^^^^^^^^^ SPACES, HYPHEN KEY Key Carer (Key Carer), # ^^^^^^^^^ RESERVED WORD and SPACES (Genius) # );[/mysql]

Anyway, I got it working in the end by hosting it on it's own virtual server, with exactly the same version of MySQL (4.0.17) and using the mysqldump flag --quote-names.

Reckon I lost 4 hours on that.