I have a server with 4.0 mysql version innodb engine.

I need to migrate to other server with 5.5 version (linux) ok

I dump the full database using mysqldump, when i try to restore on the 5.5 server it has a problem, because the file generate in the 4.0 set the variable on the sql scritp TYPE=INNODB and mysql 5.5 accepts only ENGINE=INNODB format on the time table creation and it abort the process.

I search on google, call any friends and nothing! the sql file has 16 gigabytes. Is not possible edit with an file editor.

error exemple:

CREATE TABLE tabadi ( 
nAdicao char(3) default '0', 
nSeqAdic char(3) NOT NULL default '', 
cFabricante varchar(60) NOT NULL default '', 
vDescDi varchar(15) NOT NULL default '', 
xPed int(6) NOT NULL default '0', 
nItemPed int(6) NOT NULL default '0', 
nDI varchar(12) NOT NULL default '' 
) TYPE=InnoDB 

ERROR 1064 (42000) at line 19: 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 'TYPE=InnoDB' at line 9

Ps: I did try to change TYPE TO ENGINE and its solved the problem.

Can anybody help me? Thanks a lot.

  • use an editor that can handle files that size, like notepad++ and then do a search-replace of "TYPE=InnoDB" to "ENGINE=InnoDB" Aug 23 '13 at 23:27

This should be as simple as piping the output of mysqldump through a utility that can manipulate the data stream with a regular expression pattern match, like sed or perl.

mysqldump [options] | perl -pe 's/^\)\sTYPE=InnoDB/) ENGINE=InnoDB/' > dumpfile.sql

For any line that begins with ) TYPE=InnoDB this will replace the starting portion of the line with the new string ) ENGINE=InnoDB and leave the rest of the line intact. It won't do anything to lines that don't match (they'll pass right through).

For quick and simple validation that this does what you want, use the --no-data option in mysqldump to dump only the table structures, not the data, and examine the resulting file. If it looks right, you can repeat the process without --no-data.

A similar expression in sed should be this:

mysqldump [options] | sed 's/^) TYPE=InnoDB/) ENGINE=InnoDB/' > dumpfile.sql

...however, I'm not as conversant in sed as I am in perl, so I'd recommend the first approach, although I tested both versions and they appear to work identically.

Both perl and sed should be already present on any *nix system, and both are freely downloadable for Windows. On Windows, you'll probably need " rather than ' surrounding the expressions.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.