Trouble restoring 4.0.12 dump file into 5.0.45

[SantaMufasa]

I successfully created a dump file from a 4.0.12 database instance (on a Windows server) using this command:

mysqldump --opt -u<username> -p<password> -r<dump file name> <database name>
****************************************************************************

I copied the resulting dump file over to another Windows server on which I have installed MySQL 5.0.45. On the new server, I created an empty database, naming it the same name as the source database on the 4.0.12 instance.

On the server with the 5.0.45 instance, I issued this restore command:

mysql -u<username> -p<password> <database name> < <dump file name>

After chugging away (successfully) for several minutes, the restore process threw this error:

ERROR 1064 (42000) at line 7847: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near
'condition varchar(240) default NULL,
 report_style_id int(11) NOT NULL default '
at line 4

Since I cannot successfully edit this 771MB dump file (using any editor of which I am aware -- e.g. Notepad, Wordpad, Word, even "vi"), how can I see, troubleshoot, then fix, the full text of the offending code to make it 5.0.45 compliant?

Or, is there some method of my "pre-validating" a 4.0.12 dump file for use in 5.0.45?

Or, is there a better way to migrate large databases from 4.0.12 to 5.0.45?


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[SantaMufasa]

Update: The syntax error results from 4.0.12's allowance of the column name, "condition", but in version 5.0.45, 'condition' is a reserved word which throws a syntax error during restore into 5.0.45.

To preserve application-software integrity, we must preserve use of column label, 'condition', which we can do if we enclose the dump-file references to 'condition' within double quotes.

Our current problem then is, What software can we use to edit the double quotes into the dump files? We have found that Notepad, Word, WordPad, and "vi" will not accommodate the 770+MB dump files due to either overall file sizes or buffer overflow on individual line sizes.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[hoinz]

Hallo Dave,

looks like you will need a hex editor,

http://en.wikipedia.org/wiki/Hex_editor

I never tried one of them myself, so cannot give further advice.

Or another approach, if there is a programmer at hand:
It should not be that difficult to write a little program, e.g. in C, that will do exactly what you need.

hope this helps