I am trying to migrate database A to database B. I have tried various methods for this, including...
- Using Navicat, do Tools > Data Transfer, copying structure and data from A to B
- Using Navicat, delete all tables from B, then drag and drop all tables (structure and data) from A to B
- 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.