1

I'm currently going through the excruciating task of moving MySQL databases from one shared web hosting environment to another.

This has been a multi-month battle (and I'm learning a lot). After having issues with PHPMyAdmin due to large tables (and getting annoyed having to always check it had moved the data correctly when it, in fact, was silently failing), and experiencing trouble with every other package I could find to help (since I have some large tables that have to be chunked and some packages would not let me exclude corrupt tables properly), I've custom coded my own solution in PHP and AJAX to migrate data from the old host to the new host. As a bonus, I can now finally have a working solution to back up the data as well.

I've overcome many challenges, and at this point I almost have things fully working. The checksum feature is super handy to be able to know if I moved the data across correctly (or there was some problem) without checking row by row (which would take a really long time on larger tables).

In order to do a checksum, I run this query:

CHECKSUM TABLE `<tablename>`

On both the old web host/database (accessed remotely) and the new web host/database (accessed locally). For most tables, these numbers match.

My transfer process copies the table structure (SHOW CREATE TABLE) then copies the data row by row. Larger tables are chunked, moved across, then reassembled. The entire process is fully automated through AJAX calls.

However, I'm running into some problems where some of the tables have a different checksum after having been moved across. The problem is, both the structure and data appear to be exactly intact. When I create a PHP script to check row by row, it reports that every row matches in the old and new databases. This is my script to check the tables:

  $qGetTable = "SELECT * FROM `".$sTableName."`";
  if (!($rGetTableOld = $sqlOld->query($qGetTable))) {
    die("Failed to get table in old database! ".$sqlOld->error);
  }
  if (!($rGetTableNew = $sqlNew->query($qGetTable))) {
    die("Failed to get table in new database! ".$sqlNew->error);
  }

  while ($aGetTableOld = $rGetTableOld->fetch_assoc()) {
    if (!($aGetTableNew = $rGetTableNew->fetch_assoc())) {
      print_r($aGetTableOld);
      die("Ran out of rows in new table!");
    }
    if ($aGetTableOld != $aGetTableNew) {
      echo "Found the mismatch!\n";
      print_r($aGetTableOld);
      print_r($aGetTableNew);
    }
  }

As you see, I'm even checking row by row so even order changes should be discovered. It does not find any mismatches. SHOW CREATE TABLE gives the exact same response on both servers. (Double checked with DiffChecker.)

As per, https://dev.mysql.com/doc/refman/5.7/en/checksum-table.html

If the checksums for two tables are different, then it is almost certain that the tables are different in some way.

However, it's quite vague about what to check to troubleshoot any difference.

CHECKSUM TABLE reports a checksum for the contents of a table. You can use this statement to verify that the contents are the same before and after a backup, rollback, or other operation that is intended to put the data back to a known state.

I noted one thing, but I'm not using TIME, DATETIME, or TIMESTAMP. I always store time in plain UNSIGNED INT.

The checksum value depends on the table row format. If the row format changes, the checksum also changes. For example, the storage format for temporal types such as TIME, DATETIME, and TIMESTAMP changed in MySQL 5.6 prior to MySQL 5.6.5, so if a 5.5 table is upgraded to MySQL 5.6, the checksum value may change.

I can't figure out what is different since my data looks fine and intact. However, the different checksum bothers me a lot and I want to understand what could possibly be different. Only a few tables are affected and the rest seem to work fine. Does anyone have any insight into what I should be checking or looking into to get to the bottom of this?

Thanks very much and I really appreciate your help.

  • 1
    Try pt-table-checksum -- it has probably fixed the problem you are encountering, – Rick James Nov 12 at 19:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.