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…

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.

Best Answer

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.

Related Solutions

Mysql – Memcache implementation

You should decide what exactly you need, before deciding those 3 options.

My suggestion is as following:

Install memcache and the necesary php module to use with memcache. Once you install memcache, you will have an IP and PORT to connect to, for SET / GET operations
Decide where exactly in your Wordpress php code you need to inject the code that displays the "featured" stuff
Add a php code similar to:
- CONNECT to memcache
- Check for the existance of a KEY/VALUE pair, named "FEATURED STUFF"
- If there is, GET that pair, and display the pair's value
- If not, run the SELECT query, FETCH the results, build a string that will contain the HTML body of "FEATURED STUFF", PUT the string in memcache as a KEY/VALUE pair.
The code above should not be cached with WP Super Cache. Let memcache do the cacheing for you.

If you need help with the actual implementation, let me know.

Sample PHP Code:

$memcache = new Memcache;
$memcache->connect('localhost', 11211) or die ("Could not connect");

if ($gotten = $memcache->get('featured_stuff')) {
    echo 'CACHE HIT'</br>';
    echo $gotten;
} else {
    # Never mind - fetch it and store for next time!
    $gotten = dbfetch($query, ...);
    $memcache->set($featured_stuff,$gotten);
    echo $gotten;
}

Thesqldump: Got error: 1017: Can’t find file: ‘drupal_install_test’ (errno: 2) when using LOCK TABLES

You apparently have a missing drupal_install_test in your database schema. As the name says, it's a test table that is created at installation time to test the performance of the database and of the database user you provided for your site. At the end of the installation process, it gets dropped from the database.

The problem here is that the table has been dropped but mysql still thinks it exists. The reason why that table has remained in information_schema.tables should definitely be investigated and may reflect some issues in the database or in your server's configuration.

That said, if drupal_install_test is the only corrupted table in your database, you can try and drop it and proceed with your backup. I recommend that you make a full backup of the mysql data directory (in a typical linux installation /var/lib/mysql) and issue a DROP TABLE drupal_install_test command. Mysql should be able to restore the integrity of the schema tables and you should be able to proceed with your backup.

Best Answer

Related Solutions

Mysql – Memcache implementation

Thesqldump: Got error: 1017: Can’t find file: ‘drupal_install_test’ (errno: 2) when using LOCK TABLES

Related Question