Transfer MySQL database with illegal field names

I want to transfer my large MySQL database from OSX Server 10.3.9 (MySQL 4.0.24-log) to OSX Server 10.6.3 (MySQL 5.0.88-log)

I'm using mysqldump to generate an SQL file, and then importing it with mysql < xxx.sql.  My sql file is 600 megs and contains 186 tables.

The problem is that I have many illegal characters in my column names. Most of the illegal characters are spaces and question marks.  It wouldn't take too long to adjust the sql backup file to new names, but that's not the heart of the problem.

The problem is that I have years of custom applications with hard coded field names.  We're talking php, vb, and many many access tables, forms, reports and vba code.   It would take us a month or more to track down every field name.

Obiviously MySQL 4 has no problem with illegal characters.  It's when I transfer that info into SQL that the problem arises.

Any suggestions as to how I can transfer my database without having to rename each column?
LVL 1
MasterWoodsmanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

8080_DiverCommented:
In all likelyihood, you are simply not going to be able to transfer your database without the associated column renaming pain.
Did anyone mention to you , back when you started using the illegal characters in the column names, that using illegal characters and/or "Reserved Words" is not a good practice?  If so, this is precisely why.  Either way, this is a self-inflicted pain and, although I feel for you, I am afraid I can't hold out much hope for an alternative.
Sorry.
AmickCommented:
I just ran this little snippet of code on my system with success:

CREATE  TABLE IF NOT EXISTS `test`.`table2` (
  `idtable?1` INT NOT NULL ,
  `3 mile island` VARCHAR(45) NULL )
ENGINE = InnoDB

Have you tried wrapping the offending names in ` tick marks (ascii  character \x60 - the unshifted tilde on my North American keyboard)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MasterWoodsmanAuthor Commented:
Thanks... I'll give it a twirl....
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

8080_DiverCommented:
MasterWoodsman,
I would highly recomment analyzing your database and application and creating a plan for revising the naming scheme for the offending columns.  You may be able to avoid the spaces by wrapping the names in single/double quotes or square brackets; however, the "?" is a wild card in many situations and may still be one, even if wrapped in quotes or square brackets.  
Carefully executed global search and replace moves can update most programs.  (The key part is the "carefully". ;-)  While the pain may be unpleasant, it will save a lot fo on-going pain.  (Have your read, "Who moved my cheese?" ;-)
AmickCommented:
I second 8080 Diver's advice.  While you may be able to work around the problem for the time being, in the long run it is sensible to "get legal" with your code.
MasterWoodsmanAuthor Commented:
It worked!

Wrapping ` marks around the offending column names enabled me to keep my poor naming practices alive a while longer.

It took a while to edit my 1.1 Gb SQL file to fix those names.  I created a lengthy script with many "sed" commands so that i can clean the mysqldump output anytime I want.  I'm impressed that OSX's textedit allows editing such monster files.

I appreciate the other comments that it is worth the effort to make the correction rather than extend the problem.  When I consider how much work it would take (we're talking 20 years of legacy coding here!), I'd much rather push the problem into the future.  Life is too short for perfection.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Mac OS X

From novice to tech pro — start learning today.