Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Are the source tables MyISAM or InnoDB? –  Michael - sqlbot May 15 '13 at 18:01
    
All the tables are InnoDb –  Dave May 15 '13 at 20:19

1 Answer 1

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.

After reviewing the file, if the content looks right (i.e., find the line in the file that represents the 438/2255 row you mentioned above, and see which version it represents... and make sure there's only one such line in the dump file) load it onto the target server and see what the results look like. If the contents of the dump file don't look right, then of course, restoring it would be unnecessary and we need to look at the origin server for something strange.

share|improve this answer
    
Thanks for the detailed response. I'll have to try this later on down the road. I had to manually sync up the records that were found to be in error, and now the environment is currently in use, so I can't risk destabilizing it with another migration at this time. –  Dave May 16 '13 at 4:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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