[darcybaston]

This problem began with downloading a MySQL dump from phpmyadmin 2.5.4.All the sql dumps have these weird ^M codes at the end of each line. With an iMac running OS X 10.2, SQL dumps had the 'right' line feeds and you could do a 'more filedump.sql' at the CLI and lines would be spaced properly.

I get this new iBook with 10.3, and all my phpmyadmin dumps now have these ^M characters at the end of each line which is confusing the heck out of mysql when trying to do a load with something like:

mysql -uroot -p databasename < dumpfile.sql

That line works fine with files that have line feeds properly formatted, but returns to the prompt without a single error code and does nothing if the .sql file has those ^M codes in it, which are viewable when you do a 'more dumpfile.sql' and look at it in the terminal.

So what gives? Where are these ^M codes coming from and why are they showing up in Panther and I've NEVER seen them with 10.2? Can I filter those line feeds out? Can I fix files that are malformed like that?

Edit : One way to make a proper mysql dump is to use a backup feature available in the administration panel of phpbb2. Now I wish I knew how come phpbb2 can make sql dumps with proper line feed/newline codes and phpmyadmin can't. Or is it a browser text translation that's taking place while using safari?

Just tried it with Mozilla and it too is making ^M riddled files, so it must be phpmyadmin. Will try their forums too.

[Arkham_c]

^M is a carriage return (ASCII 12). Just for reference, Macintosh text files end lines in a carriage return, UNIX files end lines in a linefeed (ASCII 10), and DOS files end in carriage return + linefeed (ASCII 12 + ASCII 10). If the file looks right other than the ^M characters, I'd guess you're getting DOS-formatted text files.

I don't know the cause, but I can offer a solution. Here is a simple python script that will work with 10.3 or later and convert any file (DOS, Mac, or UNIX) into a file with UNIX feeds.

Code:
#!/usr/bin/env python

import sys

def convertFile(filepath):
    inf = open(filepath, "rU")
    lines = inf.readlines()
    inf.close()
    outf = open(filepath, "w")
    for line in lines:
        outf.write(line)
    outf.close()

if __name__ == "__main__":
    convertFile( sys.argv[1] )

[darcybaston]

Hey thanks! That is one very cool solution. I pasted the text to a file in /usr/bin, chmod'ed it to +x and it works GREAT!

Thank you so much. I'll never have that problem again.

[philzilla]

i just use this perl one-liner:

Code:
perl -pi -e 's/\r\n?/\n/g' filename

is the output much different to that python script?

[darcybaston]

Oh hey...it's the Zilla man! Looked for you earlier to see if you had a solution for me heh.

I just tried your suggestion and it works too. Problem is, there's no way I'd remember that one, however an alias could be useful.

Anybody know which solution would perform quicker? I have 60 megabyte sql dumps to filter through.

[philzilla]

Originally posted by darcybaston:
Anybody know which solution would perform quicker? I have 60 megabyte sql dumps to filter through.

tried LineBreak?

try backing that 60Mb .sql file and trying the one-liner on it. it's easy enough to make an alias to it.

[darcybaston]

Performance test results.

I ran the python script and the perl solution on the same 58 megabyte file. I opened two shell windows, typed in the commands and then pressed enter in each window as quickly as I could in each.

Philzilla's perl one liner (which I had aliased) finished about 8 seconds sooner than the python solution (script in a file with +x in /usr/bin). I don't know what priority was given to each solution which may have interfered but from this little sloppy test, perl did it faster.

[philzilla]

RAAAWR!!!

glad to be of service, Darcy San

[darcybaston]

I bow to you both, oh wise masters of the CLI-Click-Clan.

[Sven G]

Another line ending conversion utility: mac2unix, available on the Mac GPG web site.

[drmbb2]

There's also strp which has been around for a few years. It compiles and runs fine on OS X.

[Camelot]

Originally posted by darcybaston:
[B][snip] I opened two shell windows, typed in the commands and then pressed enter in each window as quickly as I could in each.[snip]

FWIW, a more precise comparison can be achieved using the 'time' utility:

time <command>

It will run <command> then output the exact time taken to run, indicating CPU time as well as overall process time.

[[APi]TheMan]

Originally posted by Camelot:
FWIW, a more precise comparison can be achieved using the 'time' utility:

time <command>

It will run <command> then output the exact time taken to run, indicating CPU time as well as overall process time.

Ooh la la! Cool. That's pretty nifty.