I had this perplexing problem yesterday where a mysql dump and restore was producing different results when using MaatKit mk-table-checksum.

mk-table-checksum --algorithm=BIT_XOR h=192.168.X.XX,u=user,p=password --databases=db1 --tables=c
DATABASE TABLE   CHUNK HOST         ENGINE      COUNT         CHECKSUM TIME WAIT STAT  LAG
db1      c           0 192.168.X.XX InnoDB     215169         d1d52a31    2    0 NULL NULL
mk-table-checksum --algorithm=BIT_XOR h=localhost,u=user,p=password --databases=db1 --tables=c
DATABASE TABLE   CHUNK HOST      ENGINE      COUNT         CHECKSUM TIME WAIT STAT  LAG
db1      c           0 localhost InnoDB     215169         91e7f182    0    0 NULL NULL

It was rather crazy until I reviewed the mysqldump settings I was using, and I realized I was using –no-set-names.

So just what does this option remove. Here is a diff of mysqldump with and without.

5a6,10
>
> /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
> /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
> /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
> /*!40101 SET NAMES utf8 */;
153a159,161
> /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
> /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
> /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
156c164

As you can see it executes a SET NAMES utf8. The problem here is I’m exporting a table, and it is DEFAULT CHARSET=latin1, and no columns are defined as utf8.

I’m not expert in character sets, but this strikes me as strange, and a problem that remains unresolved to my satisfaction, resolved, but not to my comfort level.