I have a table that contains almost 4 million rows. I exported it with mysqldump and transferred it via scp to another server and imported it with the mysql command but now it's missing several hundred thousand rows. I've repeated this process several times and each time, it's missing a different amount of rows.

I have tried mysqldump --compatible=ansi because on one export, there was an error in the syntax for some reason. That dump wound up having the most rows on import but still missing hundreds of thousands.

Edit: I tried the -f option but it still prints out a mysql error before I'm returned to the command prompt:

ERROR 1064 (42000) at line 41458: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '286,'','http://example.com/'),(81547367,'CbLc4lXH',1501560286,'54.7' at line 1

I'm not sure if it exited because of the error or not but I do know I'm missing about a million records after import. The file is 40GB in size and I don't have access to the previous mysql server anymore to do another export. I have all the data I need. I just can't get it imported.

  • 1
    Are the file sizes the same before and after the transfer? Have you tried breaking the export into smaller chunks and doing multiple imports?
    – Sloan Thrasher
    Commented Aug 11, 2017 at 6:51
  • File size is the same. How would I break it up?
    – xendi
    Commented Aug 11, 2017 at 6:54
  • 1
    A few thoughts to help troubleshoot: How are you couting the number of rows (SHOW STATUS or SELECT COUNT(*))? Note that the former is an estimated number of rows. What ENGINE is the table? Have you tried to reload the sql dump back onto the originating MySQL instance in a separate DB as a test? Or even skipping dump and just copying the table.
    – Riedsio
    Commented Aug 11, 2017 at 14:43
  • question updated with more info
    – xendi
    Commented Aug 14, 2017 at 4:47
  • What was immediately before 286,...?
    – Rick James
    Commented Aug 18, 2017 at 3:04

2 Answers 2


Sometimes this can occur because of a combination of a certain sequence of characters and the character set of the MySQL client at the time of the import.

You could have trapped such errors without additional tools by doing the following

mysql -u dbuser -p -f -D thedb < importfile.sql 2>errors_encountered.txt

This would have recorded your trouble spots into errors_encountered.txt

What could have been done with the mysqldump to begin with ???


You could have used the --hex-blob options to convert the characters into a hexadecimal representation. This could have eased any character set misinterpretations or misunderstandings on the part of the MySQL client program.


Even when using --force for the MySQL client, this can still result in hundreds or thousands of rows not being inserted. Why ?

By default, mysqldump has --opt enabled. This sets the following

  --opt               Same as --add-drop-table, --add-locks, --create-options,
                      --quick, --extended-insert, --lock-tables, --set-charset,
                      and --disable-keys. Enabled by default, disable with

Notice that one of the options is --extended-insert. This causes mysqldump to set up inserts in chunks of hundreds or thousands of rows at a time.

If just one row in a chunk of rows had an issue with being imported and you are using --force, the entire chunk of rows is not inserted.

How do you get a hold of all the good parts of a chunk. For a mysqldump created with --extended-insert, there is no simple way. What can you do when this happens ? I have good news and bad news.

GOOD NEWS: You have to launch a new mysqldump with --skip-extended-insert. That forces a mysqldump to create an INSERT for every row. That way, when using --force during import, an invalid INSERT due to whenever circumstance will not affect surrounding rows.

BAD NEWS #1 : This makes the resulting mysqldump file much larger.

BAD NEWS #2 : Importing the resulting mysqldump file takes much, much longer.

This is something I recommended Aug 09, 2013 (Backup / Export data from MySQL 5.5 attachments table keeps failing!)


To nullify the size problem this creates, you could do the following

gzip the mysqldump while doing mysqldump

nohup mysqldump ... | gzip > importfile.sql.gz &

ungzip the mysqldump and pipe it to mysql for import

gzip -d < importfile.sql.gz | mysql -u dbuser -p -f -D thedb


  • Something strange is going on. I imported the backup and got over 3,400,000 rows to import but I wanted all 3.8 million so I keep trying again and all I get are 2.8 million. I can't run the export again because the server was wiped. I have 3 different versions of the export. I have tried using -f but each time I do it, it exits and tells me there was a syntax error during the import. The file is almost 40 gigs so I can't edit it.
    – xendi
    Commented Aug 14, 2017 at 2:34
  • I got bad news: you may have to write a C, python or go script to parse the individual rows and insert each row one by one. You should have kept the the old server around until the import was done right. Sorry :-( Commented Aug 14, 2017 at 2:53
  • I was thinking about that. I've updated the question BTW. This is a great answer. Want to get this resolved before accepting one though.
    – xendi
    Commented Aug 14, 2017 at 4:48
  • I wound up getting it to import. It was strange. The best imports I ever had were immediately after decompressing from tar-gzip. If I decompressed and then moved the file around, transferred via scp offsite and back on, it was always only import a fraction. I wound up decompressing one more time and importing before I was going to give up. It imported every row. I don't know what was going on but the best results were obtained by importing immediately after decompression (Before doing anything else with the .sql file).
    – xendi
    Commented Aug 19, 2017 at 19:21
  • This was very useful for me. Did not know about --skip-extended-insert. There were no errors being caught, but doing one by one solved the issue for me anyways. Thanks.
    – Omar Trejo
    Commented Aug 1, 2018 at 8:34

I think I ran into this issue today. I was dumping an entire table and importing it again on another server. The table on the new server had less rows than the original and at least 1 integer value changed from 1 to 0.

Very curious indeed!

Using --skip-extended-insert like suggested in the other answer fixed this, but makes the importing extremely slow.

I ended up using --net_buffer_length=16384 which makes the inserts shorter. I chose 16384 because this is the default value for MySQL and it still makes the inserts a lot shorter than I originally had them.

So a very big warning to all: NOT USING --net_buffer_length=16384 CAN BREAK YOUR BACKUPS AND CORRUPT DATA!

Actually, the backups themselves are OK. The problem is created when restoring the dump, but you may not have a choice when your server breaks and you can only restore from a bad file, or have fun breaking up the insert SQL statements into smaller chunks by hand :(

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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