Issue with SET field
- Dominant language
- Go
- Stars
- 13.6k
- Forks
- 1.4k
- Avg merge
- 2h 31m
- Merged PRs (30d)
- 4
Description
Hey there, I'm using latest gh-ost (1.0.46) release on linux Ubuntu trusty.
I've been trying to alter a table containing a SET field (i'm not touching the SET field) but gh-ost fails as soon as the binlog applier tries to apply a row with a warning :
'Data truncated for the set field'.
Gh-ost output shows a really big number that is not equal on what I can get on the DB.
I watched the binlog and the binary representation of the SET field is different on what we could expect. So you can't really just convert it to decimal and use it directly.
Here's an easy example :
```create table set_binlog (
id int not null auto_increment,
value SET ('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17') not null,
primary key (id)
);
insert into set_binlog (value) values ('1');
insert into set_binlog (value) values ('1,2');
insert into set_binlog (value) values ('1,2,3');
insert into set_binlog (value) values ('1,2,3,4');
insert into set_binlog (value) values ('1,2,3,4,5');
insert into set_binlog (value) values ('1,2,3,4,5,8');
insert into set_binlog (value) values ('1,2,3,4,5,8,9');
insert into set_binlog (value) values ('1,2,3,4,5,8,9,12');
insert into set_binlog (value) values ('1,2,3,4,5,8,9,12,17');
```
Here's what we get :
value | CONV(value+0,10,2) | binlog representation
------------ | ------------- | -------------------------------
1 | 1 | 000000010000000000000000
1,2 | 11 | 000000110000000000000000
1,2,3 | 111 | 000001110000000000000000
1,2,3,4 | 1111 | 000011110000000000000000
1,2,3,4,5 | 11111 | 000111110000000000000000
1,2,3,4,5,8 | 10011111 | 100111110000000000000000
1,2,3,4,5,8,9 | 110011111 | 100111110000000100000000
1,2,3,4,5,8,9,12 | 100110011111 | 100111110000100100000000
1,2,3,4,5,8,9,12,17 | 10000100110011111 | 100111110000100100000001
In the binlog, MySQL writes on the first bytes then moves forward to the second etc ...
So to get the correct binary value from the representation you need to cut each bytes and put it at its right place :
100111110000100100000001 => 10011111 00001001 00000001 => 00000001 00001001 10011111 => 10000100110011111
I hope it's clear enough !
Contributor guide
Assessment
This issue has not been assessed yet.