5

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,'54.227.215.70','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.

migrated from stackoverflow.com Aug 11 '17 at 8:25

This question came from our site for professional and enthusiast programmers.

  • 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 Aug 11 '17 at 6:51
  • File size is the same. How would I break it up? – xendi Aug 11 '17 at 6:54
  • 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 Aug 11 '17 at 14:43
  • question updated with more info – xendi Aug 14 '17 at 4:47
  • What was immediately before 286,...? – Rick James Aug 18 '17 at 3:04
4

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

SIMPLIFY EXPORT CONTENTS

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.

DUMP ONE ROW AT A TIME

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

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

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

SUGGESTION

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

GIVE IT A TRY !!!

  • 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 Aug 14 '17 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 :-( – RolandoMySQLDBA Aug 14 '17 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 Aug 14 '17 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 Aug 19 '17 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 Aug 1 '18 at 8:34
0

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, privacy policy and cookie policy

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