I have the following table on MySQL. I am using 5.6.32. The table contains about ~40 million records. I am only sharing columns which I feel are necessary to understand the issue.
Table Structure
create table `random` (
`id` bigint(20) not null auto_increment,
`some_id` bigint(20) not null,
`latitude` decimal(20,14) default null,
`longitude` decimal(20,14) default null,
`new_column` varchar(255) collate utf8_unicode_ci default null,
primary key (`id`)
) engine=innodb auto_increment=40878872 default charset=utf8 collate=utf8_unicode_ci;
So, I added a new column in this table called new_column varchar(255). But, when I do length(new_column), there are entries which have more than 255 characters.
The actual value being inserted:
random*GS02,355234054262743,GPS:356728;A;N33.614073;E77.063096;0;0;230118,STT:400;0,ADC:0���&�������������r�������r�������r������*GS02,39233054663793,GPS:173158;A;N33.614057;E77.0263201;0;0;210118,STT:200;0,ADC:0;24.7;1;29.9;2;4.2;3;0.0
On the MySQL Master (say, machine #1, my application was able to insert this value in new_column in the table without an issue. I have a MySQL slave (say, machine #2) using native MySQL replication and it was also able to replicate this record easily. But then I have another slave replicating from machine #2 which is using tungsten replicator. Whenever there is a string which is more than 255 characters, tungsten throws the following error and replication breaks
pendingError : Event application failed: seqno=2395306016 fragno=0 message=java.sql.SQLDataException: Data too long for column 'new_column' at row 1
pendingExceptionMessage: java.sql.SQLDataException: Data too long for column 'new_column' at row 1
EDIT:
Variables on Master and both Slaves are set to
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
Collation while creating the table is set to utf8_unicode_ci on all instances.
Why is it that MySQL native replication is allowing more characters to be written to the column? And why is it that Tungsten replicator is preventing it?
