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:
[code]mysql -ufoo -pbar database_name < database_name.sql[/code]
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.
Comments
One response to “Nightmare MySQL migration”
Just seen this one after a random search on the net, I can’t believe he got that table to compile in the first place, he’s either very good at what he does to be able to get away with that, or, as I suspect, very inexperienced indeed….it’s quite astonishing!
The columns in the table also don’t seem to reflect the table name in my mind either, strange, though at least you got it working, not good you had to do it with a virtual server though, needs must I guess.
~ Chris