Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I am trying to import a large MySql dump file and am getting a consistent error on a certain line 149,351 (out of 4207442).

There must be an easy way to find out what is on this line. The best I can find is

head -149351 dump.sql | tail

Their must be a quicker way than this. I tried vi but that was having none of it. I have am on win XP and have cygwin.

share|improve this question
    
Only after asking this does it occur to me that I could ask the question about the original problem on SF as well –  Jeremy French May 8 '09 at 15:25
    
Why do you think there must be a quicker way? The way you've suggested is exactly what I would have done... –  rjmunro May 8 '09 at 16:10
    
There's a dupe of this on SO already. –  Adam Davis May 8 '09 at 17:39
    
A quick search didn't pull it up though, so you'll have to look yourself... –  Adam Davis May 8 '09 at 17:41

7 Answers 7

up vote 1 down vote accepted
sed -n '149351p' dump.sql

might be slightly faster than head/tail combinations (but maybe not.) Vartec is correct; there is no quicker way than reading at least the first 149351 lines.

--
bmb

share|improve this answer
    
It may be a peculiarity of cygwin, but this does seem a little faster. Thanks. –  Jeremy French May 11 '09 at 9:43

You can see the individual line with the following command:

tail -n+<line number> <file>|head -n1

in your case: tail -n+149351 dump.sql|head -n1

That command tails the file starting on line number 149351, and uses the head command to only display the first line of the tail results.

share|improve this answer

I would recommend using the split command to break that huge dump into more manageable pieces:

split -l20000 mysql.dump mysql.dump.

Will create files with names mysql.dump.aa, mysql.dump.ab, .... Each file will contain 20000 lines - editing tools should be able to handle those small files easily!

Once you've fixed the problem, recombine them easily:

cat mysql.dump.* > mysql.dumptest

or

cat mysql.dump.* | mysqlimportcommand
share|improve this answer
1  
the split will have to scan whole file for line endings, so unless you're doing repeated searches on same file, you gain nothing. –  vartec May 8 '09 at 16:22
    
Well, you gain abilities to see it in full context as well to parse it once then work with it more later. It's very possible that there are more errors in the file. If this turns out to be the case, he'll now be able to go right to the spot where the next error is without having to reparse the whole thing. –  MikeyB May 8 '09 at 16:46
    
Shell globbing provides 0 guarantees that mysql.dump.* will return in a sorted order (although it is likely that they will return in the order created). If you want to use this method, its a good idea to sort the names of the files before cat'ing them –  Dave Cheney May 9 '09 at 14:41
    
Fair enough - I'm used to bash, which will guarantee an alphabetic sort of a glob replacement. –  MikeyB May 10 '09 at 5:18

I usually just fire up TextPad, even in 1GB files.

Ctrl+G is the Go To menu in which you can choose line number.

share|improve this answer
    
He is trying to do that on Linux (*NIX) box. Doing what you recommend implies a transfer of the file to a Windows machine. –  user1797 May 8 '09 at 15:49
    
Actually it is windows with cygwin. –  Jeremy French May 8 '09 at 15:55
    
I second this idea. There are several very capable text editors which can open multi GB files quickly. TextPad is one. Personally I use the now defunct CodeWright. Same difference. –  Simon Gillbee May 8 '09 at 16:21

If the lines are of variable lengths, there really is no quicker way, then scanning through first 149351 (which is exactly what you do with "head").

share|improve this answer
    
You got my +1, agreed. –  user1797 May 8 '09 at 17:23

Surely in vi you can set the line number using:

:set number

and then go to line 149351 using:

:149351

alternatively you can start vi at a specific line number using:

vi +36 blah

hope that helps...

share|improve this answer
    
cygwin vi just barfs when trying to open the file. –  Jeremy French May 8 '09 at 15:39

Open it with nano (with -w), and once open hit CTRL _ and enter the line number to go to.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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