SQL inserts from file failed at unknown point…what do I do?

I was running a nicely pieced together sql text file filled with INSERTs we’ll call ‘something.sql’ recently in a screen session. Unfortunately, when I reconnected much later my data import had failed at some unknown point without an error. Sigh.

Terminal Import I could see that many of the hundreds of thousands of rows had been imported, but how many? I needed to know where to restart the import and I did not want to delete all that data to restart it. That would be its own difficult reclamation project because I already had another import going.

Let’s pretend that I was importing comments for blog posts. The posts were all in the database already. So, how do you know where your import failed? I started with a guess.

 

I knew that another import on the same VM ran for about 5 hours before it completed and I was able to guesstimate that around 300,000 of the rows were probably inserted. The next steps composed a manual binary search. First, I wanted to know an upper bound on my search space.

awk ‘NR==300000 { print }’ something.sql

This shows me the 300,000th INSERT statement. It has an id I can use to check the database.

SELECT count(*) FROM comments WHERE  comments.post_id = <ID>

> 0

We now know that none of the comments for that post made it in and that we’re looking for some point earlier in the file. I decided to limit my search space.

head -n 300000 something.sql > 300k.txt

This creates a separate file with only the first 300k lines or INSERT statements. Now we can binary search, but with a little guess work. I’m fairly sure at least 200,000 made it in and I can test that assumption. I’ll reuse awk to show me the Nth line of the file.

awk ‘NR==200000 { print }’ something.sql

Turns out, that INSERT did make it in. Next up was checking line 245,000. It’s not there.

222, 000 is there. 233,000 is not. I kept on like this until I found a post that had 111 entries in the database. Was 111 the right number? We can grep the lines of the file for the post id and count the matches using wc.

cat 300k.txt | grep ‘<POST-ID>’ | wc -l

> 467

Now I know that it failed somewhere in those 467 INSERTS, but I don’t know where in the list I’m pointing to. I could be at any of those 467 INSERTs. I needed to know the line number of the first one in the file. Print out the line number of the first match for me, awk.

awk ‘/POST-ID/{ print NR; exit }’ 300k.txt

> 222,137

It failed between 222,137 and 222,604 (467 later). In fact, we know there were 111 INSERTs for that post so we’re pretty sure it failed on (222,137+111=) 222,248. We can verify with a few SQL queries similar to the one above and by verifying the comments we expect are present or not. Lastly, we need to restart that import:

tail -n +222,248 something.sql > missing.sql

psql -f missing.sql

tail helped us keep only the last lines we wanted and psql is off and running.

And that is that. It took two guesses, about 10 spot checks, and a few simple SQL queries to save hours.