2

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). I would like to see what that line is.

The best way I have found to do this is like this:

head -149351 dump.sql | tail

However, this is very slow. I tried loading the file in vi, but it wasn't able to handle a file that big. I am on Windows XP and have cygwin.

Improve this question
4
  • 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, 2009 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, 2009 at 16:10
  • 1
    There's a dupe of this on SO already.
    – Adam Davis
    May 8, 2009 at 17:39
  • A quick search didn't pull it up though, so you'll have to look yourself...
    – Adam Davis
    May 8, 2009 at 17:41

9 Answers 9

Reset to default
5 
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.

Improve this answer
2
  • It may be a peculiarity of cygwin, but this does seem a little faster. Thanks.
    – Jeremy French
    May 11, 2009 at 9:43
  • To see 10 lines: sed -n '149531,+10p' dump.sql .
    – Brian
    Aug 29, 2015 at 1:24
4

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.

Improve this answer
3

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
Improve this answer
4
  • 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, 2009 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, 2009 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, 2009 at 14:41
  • Fair enough - I'm used to bash, which will guarantee an alphabetic sort of a glob replacement.
    – MikeyB
    May 10, 2009 at 5:18
2

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

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

Improve this answer
3
  • 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, 2009 at 15:49
  • Actually it is windows with cygwin.
    – Jeremy French
    May 8, 2009 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, 2009 at 16:21
2

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").

Improve this answer
1
  • You got my +1, agreed.
    – user1797
    May 8, 2009 at 17:23
2

Surprised no one suggested:

grep -n 149351 dump.sql
Improve this answer
2

Since its windows I'm surprised no one has suggested

get-content -readcount 0 | select-object -index 149350

Improve this answer
1

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...

Improve this answer
2
  • cygwin vi just barfs when trying to open the file.
    – Jeremy French
    May 8, 2009 at 15:39
  • even the latter option?
    – Konrad Gajewski
    Aug 29, 2015 at 4:25
1

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

Improve this answer

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.