# Controlling the character set of the binlog

**URL:** <https://forums.percona.com/t/controlling-the-character-set-of-the-binlog/5490>\
**Category:** Other MySQL® Questions\
**Created:** [March 17, 2017, 2:46pm UTC](https://forums.percona.com/t/controlling-the-character-set-of-the-binlog/5490 "2017-03-17T14:46:31Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![mtaluc](https://avatars.discourse-cdn.com/v4/letter/m/cc9497/32.png) [@mtaluc](https://forums.percona.com/u/mtaluc)\
**Post date:** [March 17, 2017, 2:46pm UTC](https://forums.percona.com/t/controlling-the-character-set-of-the-binlog/5490/1 "2017-03-17T14:46:31Z")

</div>

We came across an issue with Tungsten replicator where it was incorrectly interpreting multi-byte characters. After some deeper investigation, it looks like it’s not an issue with Tungsten, but with the way data is being written to the binlogs by MySQL. The following illustrates the problem:

I created a table in mysql (Percona 5.6). The default character set of the server is latin1. It has row level logging.

create table mt\_test(`machine_name` varchar(64) CHARACTER SET utf8 COLLATE utf8\_unicode\_ci DEFAULT NULL) default charset=utf8;

I did a setnames and ran an insert of a right-hand upper quote (unicode hex=E28099):

mysql\> set names utf8;  
mysql\> insert into mt\_test select machine\_name from play\_wide where id=13130;

Here we confirm the value of the quote:

mysql\> select machine\_name, hex(substring(machine\_name,8,1)) from mt\_test;  
±---------------------------------±---------------------------------+  
| machine\_name | hex(substring(machine\_name,8,1)) |  
±---------------------------------+  
| Treessa’s MacBook Pro |E28099 |  
±---------------------------------+ ----------------------------------+

For reference:

mysql\> show variables like ‘%char%’;  
±-------------------------±---------------------------+  
| Variable\_name | Value |  
±-------------------------±---------------------------+  
| character\_set\_client | utf8 |  
| character\_set\_connection | utf8 |  
| character\_set\_database | latin1 |  
| character\_set\_filesystem | binary |  
| character\_set\_results | utf8 |  
| character\_set\_server | latin1 |  
| character\_set\_system | utf8 |  
| character\_sets\_dir | /usr/share/mysql/charsets/ |  
±-------------------------±---------------------------+

But in the binlog I see the character misinterpreted as though it were latin1 where the unicode bytes get split into 3 separate characters:

/_!\C utf8 //!_/;  
SET @@session.character\_set\_client=33,@@session.collation\_connection=33,@@session.collation\_server=8/_!_/;  
BEGIN  
/_!_/;

# at 1010181

#170317 19:13:39 server id 18913 end\_log\_pos 1010239 CRC32 0x061bfc35 Table\_map: `usage_data`.`mt_test` mapped to number 96

# at 1010239

#170317 19:13:39 server id 18913 end\_log\_pos 1010299 CRC32 0xe903ce6f Write\_rows: table id 96 flags: STMT\_END\_F

### INSERT INTO `usage_data`.`mt_test`

### SET

### @1=‘Treessaâ\<80\>\<99\>s MacBook Pro’

I’ve tried various permutations of configuration settings and table settings, but can’t seem to make the log write the correct value. Note that the OS locale is UTF8. Any ideas of how this can be controlled?