Space Wars Blog

Working on a clients question, I stumbled over an issue with replication and mixed character sets. I was asked, wether it is possible to replicate data to a table on a MySQL slave, where one column had a different character set, than the table on the master.

I set up to servers with identical table definitions and changed the character set on one column on the slave from “Latin1” to “UTF-8”. So far no problem, I was able to start the replication and set off some INSERT statements with special characters (like ä, ö, ü, …) in them. But when I went to look for them in the slave’s table, I could not find them.

Following the table definitions of the two servers:

Master

CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `data` varchar(255) DEFAULT NULL,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Slave

CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `data` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Taking the analysis of the problem a bit further, SHOW SLAVE STATUS, informed me of this error: Column 1 of table ‘test.test’ cannot be converted from type ‘varchar(255)’ to type ‘varchar(255)’

You might ask yourself now: But the tables have the same type, what is the problem? Trust me, I asked that myself too.

What is not shown in the error, is the fact that there are two different character sets.

The log file is of no help either. It only shows the same error and tells you to fix it.

2016-05-26 15:51:06 9269 [ERROR] Slave SQL: Column 1 of table 'test.test' cannot be converted from type 'varchar(255)' to type 'varchar(255)', Error_code: 1677
2016-05-26 15:51:06 9269 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'valkyrie_mysqld35701_binlog.000050' position 120
2016-05-26 15:53:39 9269 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013)

Skipping the statement will not help. The server will just fail again, when the next statement shows up.

For all those who are now running to change the character set: STOP!

Changing characters set of columns or tables containing data can be fatal when done incorrectly. MySQL offers a statement to convert tables and columns to the character set you wish to have.

To convert the entire table, you can write:
ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;

To convert a single column, you can write:
ALTER TABLE tbl_name MODIFY latin1_column TEXT CHARACTER SET utf8;

More details about this can be found in the ALTER TABLE documentation of MySQL.