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.
SHOW SLAVE STATUS \G;
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.
[mysqld]
default-character-set=utf8
After editing it, you will need to restart MySQL. Any databases created from then on will default to UTF8.
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.
SHOW SLAVE STATUS \G;
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.
[mysqld] default-character-set=utf8
After editing it, you will need to restart MySQL. Any databases created from then on will default to UTF8.