# Mysql – Why does copied MySQL database have different data than source

drupalMySQL

I am trying to migrate database A to database B. I have tried various methods for this, including…

1. Using Navicat, do Tools > Data Transfer, copying structure and data from A to B
2. Using Navicat, delete all tables from B, then drag and drop all tables (structure and data) from A to B
3. Using Sequel Pro, export A to SQL file, then import SQL file into B

Option 1 always fails, and I'm not sure why. The error message just says "failed".

Options 2 and 3 seem to work fine, but then some tables have slightly different data, which makes absolutely no sense to me. For example, here is a row of data from database A…

node    book    0   438 2255    und 0   foo NULL    full_html


…and here is the same row in database B…

node    book    0   438 2255    und 0   bar NULL    full_html


The "foo" and "bar" are HTML content for web page #438 at revision #2255. I have no idea how the HTML content for the same exact revision can be different, when database B should be a straight copy of A.

Mysteriously, "foo" represents the most recent version of page #438, and "bar" actually reflects the prior version of page #438. How is this even possible?

Also, if any of you DBAs happen to be familiar with Drupal, note that this is for a Drupal site. Incidentally, when I try to restore a backup on this site (a ZIP or SQL file made with the Backup and Migrate module), I get an unhelpful "site encountered an unexpected error" message and nothing else in the error log. The same ZIP and SQL files restore just fine in other test environments, so something about this one site just seems wonky.

Honestly, I expected you to say MyISAM since I've never seen InnoDB generate a backup that seems inconsistent with what you think is in the table.
As a DBA, I don't trust my data to anything that I can't watch while it's working and peer into its intermediate stages, so I rarely use GUI tools. My approach to this issue would be to use mysqldump to extract the data into a file and then review that file manually. Satisfied with the sanity of it, I would load it onto the target server with the mysql command line client and see what the results are.
The specific option I'd suggest with mysqldump in this case would be --skip-extended-insert, which generates one insert statement for each row row in each table. This is in contrast to the normal format, which combines groups of rows into a smaller number of insert statements as an optimization for faster restoring... a file created with this option will restore somewhat more slowly than one created without it, but the tradeoff is that the files are substantially easier to parse with your eyeballs and see what the data looks like during this intermediate stage of the process.
Since my bet on MyISAM didn't pan out so well, I'm really unsure whether to put money on the dump file looking right and some kind of strangeness on the target server to scramble it up on the way in... or for the dump file to come out looking wrong, though I really will be surprised if the dump file comes out looking wrong... but then again, if the dump file looks right, I'd be pretty surprised if it doesn't restore correctly.