search/replace in an incredibly large file

[Red Squirrel]

My old sql server used to add junk to the table declarations in dumps. For example:

CREATE TABLE `ibf_admin_sessions` (
  `session_id` varchar(32) NOT NULL default '',
  `session_ip_address` varchar(32) NOT NULL default '',
  `session_member_name` varchar(250) NOT NULL default '',
  `session_member_id` mediumint(8) NOT NULL default '0',
  `session_member_login_key` varchar(32) NOT NULL default '',
  `session_location` varchar(64) NOT NULL default '',
  `session_log_in_time` int(10) NOT NULL default '0',
  `session_running_time` int(10) NOT NULL default '0',
  PRIMARY KEY  (`session_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

But that last line should just be ENGINE=MyISAM;

I upgraded but now the problem is that the other server won't take the sql file because of this added stuff and it does not support it. I tried a windows search and replace util, but it does not seem to work, it just won't replace it. Is there a tool in linux I can do a search/replace on rediculusly large files? I'm talking 1GB here, so no way I can open that in a windows editor. (I've tried, it ain't pretty)

 

[n0cmonkey]

sed is the answer.

 

[Red Squirrel]

Odd it seems all file replace utils I've tried ingore the string. Maybe because they can only replace alpha numeric or something. So plan B, is there a way to get mysql to accept it that way anyway? Like get it ingore the charset or something? Since there's no way in hell I'm manually editing all that, since the only editor I can open that with is vim, windows based editors bail out since they try to load the whole thing in memory. Would be living hell to try and replace all that in vim, unless it has a search/replace feature I don't know about.

 

[n0cmonkey]

Open it in vim. If the line you gave me is the exact line in the file try this:
:%s/\ DEFAULT\ CHARSET\=latin1//g

EDIT: That's sed syntax, and it would be very similar in sed. Probably something like:
sed 's/\ DEFAULT\ CHARSET\=latin1//g' filename

But I'd have to test it out to make sure that's right.

 

[Red Squirrel]

Just says pattern not found.

I found this command called "replace" but I can't figure out how to use it. The documentation says I type replace "string" to "newstrong" filename but when I do, it just sits there waiting for data input. Tried to do <file, did not work either. It's part of mysql ironicly but the man file says it's also good for general purpose replacing.

 

[Red Squirrel]

Hey cool, got it working. The man file was wrong, or was not explaining it right. The right usage is:


replace "string "tostring" <inputfile >outputfile


Seems to be working, I'm now processing the sql file now and so far no sql errors.

 

[n0cmonkey]

Originally posted by: RedSquirrel
Just says pattern not found.

Then you mistyped something.

 

[Red Squirrel]

Freaky, while processing the sql file, I got an error on line 6666. What are the odds. Something to do with a USE_TIMESTAMP_NOW parameter. My other sql server was wacked. Was about time I change it. Let's just hope that was produced by the local dump and not the external one, since that table is from one of my sites I run and I like to resync them regularly. I'll see what happends when I resync. That replace command saved me though. And -f in mysql also, since it forces it to keep going, I'll worry about the missing tables later, and if it's site tables it's no biggie. It's the business stuff and site stats that's important.

But thanks for the help anyway, even though I ended up finding that replace command which is what really helped. This is probably my smoothest server install ever so far. Debian is a great distro so far.