MasterWoodsman
asked on
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks... I'll give it a twirl....
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?" ;-)
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?" ;-)
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.
ASKER
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.
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.
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.