Data missing from MySQL replication due to column mismatch

If you are running MySQL replication, you may find that the structure of a table is brought over, but not the data. You can troubleshoot this by logging into the slave MySQL and showing the status.


You may find an error similar to the following.

Table definition on master and slave does not match: 
Column 1 size mismatch - master has size 150, example_db.test_table 
on slave has size 50. Master's column size should be <= the slave's column size.

This is usually caused because you are using different character sets. In the example above, the master is set to UTF8 (utf8_general_ci) while the slave database is set up in Latin1. You can fix this in two ways (and should probably do both).

First, to change the database, log into phpMyAdmin and go to the operations tab. Here you change it from the dropdown and then hit save.

Secondly, you should change the default charset in MySQL to be UTF8 (you could also change it to Latin1 if that is what your master is running but in this day and age, everything should really be running UTF8). You can do this by editing your my.cnf.


After editing it, you will need to restart MySQL. Any databases created from then on will default to UTF8.



Don't have time to check my blog? Get a weekly email with all the new posts. This is my personal blog, so obviously it is 100% spam free.


Tags: , , , ,

This entry was posted on Saturday, February 16th, 2013 at 9:03 am and is filed under Life, Tech. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.