ALTER from ENUM to VARCHAR ends with numeric values in VARCHAR

[fuyar]

Hey Schlomi,

After having issues with the SET field, I tried the same thing with a ENUM.

I'm using latest gh-ost (1.0.46) release on linux Ubuntu trusty.

There is no problem with the copy part but the binlog streamer will insert the decimal value instead of the literal value.

Example :

CREATE TABLE enum_binlog (
  id int(11) NOT NULL AUTO_INCREMENT,
  color enum('blue','red','green','yellow','black','white','brown','purple','pink','orange') NOT NULL,
  PRIMARY KEY (`id`)
);

insert into enum_binlog (color) values ('blue');
insert into enum_binlog (color) values ('orange');

In the binlog :

### INSERT INTO `test`.`enum_binlog`
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */

### INSERT INTO `test`.`enum_binlog`
### SET
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2=10 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */

Thanks again for your work !

[shlomi-noach]

s/I tried the same thing with a SET/I tried the same thing with a ENUM/

[shlomi-noach]

enums work with both string and numeric values. enums are tested in gh-ost and verified to work well. Are you seeing a bug? Does your data get corrupted?

[fuyar]

Yeah I saw on bug when I tried to switch from an enum to a varchar field.

The binlog streamer process insert decimal values. It doesn't convert the decimal into litteral values before inserting the values.

If I take my example in the issue :

CREATE TABLE enum_binlog (
  id int(11) NOT NULL AUTO_INCREMENT,
  color enum('blue','red','green','yellow','black','white','brown','purple','pink','orange') NOT NULL,
  PRIMARY KEY (`id`)
);

insert into enum_binlog (color) values ('blue');
insert into enum_binlog (color) values ('orange');

Then I start gh-host with a postpone flag in order to keep binlog streamer running.

gh-ost \
--host=127.0.0.1 \
--user=root \
--ask-pass \
--database=test \
--table=enum_binlog \
--alter="MODIFY color varchar(10)" \
--chunk-size=10000 \
--max-load=Threads_running=1000 \
--initially-drop-ghost-table \
--postpone-cut-over-flag-file=/tmp/ghost-postpone.flag \
--initially-drop-old-table \
--exact-rowcount \
--concurrent-rowcount \
--allow-on-master \
--verbose 

Here's what I get while waiting for the cut-over :

mysql> select * from enum_binlog;
+----+--------+
| id | color  |
+----+--------+
|  1 | blue   |
|  2 | orange |
+----+--------+
2 rows in set (0.00 sec)

mysql> select * from _enum_binlog_gho;
+----+--------+
| id | color  |
+----+--------+
|  1 | blue   |
|  2 | orange |
+----+--------+
2 rows in set (0.00 sec)

Now I insert a new line in the source table :

insert into enum_binlog (color) values ('pink');

Here's the issue :

mysql> select * from enum_binlog;
+----+--------+
| id | color  |
+----+--------+
|  1 | blue   |
|  2 | orange |
|  3 | pink   |
+----+--------+
3 rows in set (0.00 sec)

mysql> select * from _enum_binlog_gho;
+----+--------+
| id | color  |
+----+--------+
|  1 | blue   |
|  2 | orange |
|  3 | 9      |
+----+--------+
3 rows in set (0.00 sec)

Kinda same thing happens if you change the enum order :

--alter="MODIFY color enum('blue','red','green','yellow','black','white','grey','brown','purple','pink','orange')" \

mysql> select * from enum_binlog;
+----+--------+
| id | color  |
+----+--------+
|  1 | blue   |
|  2 | orange |
|  3 | pink   |
+----+--------+
3 rows in set (0.00 sec)

mysql> select * from _enum_binlog_gho;
+----+--------+
| id | color  |
+----+--------+
|  1 | blue   |
|  2 | orange |
|  3 | purple |
+----+--------+
3 rows in set (0.00 sec)

Hope that's clearer

[shlomi-noach]

It's clearer now, thanks!

[h0tw1r3]

Should be obvious, but the same bug with ENUM to CHAR bit me today.

[makmanalp]

Also hit something similar here

[disser4]

We ran into the same issue. Essentially when you postpone flags, the binary logs write to the _gho table the numeric index of the enum.

[shlomi-noach]

I'll be tracking this in #963

[shlomi-noach]

Correction: tracked in openark#18

[shlomi-noach]

openark#18 seems to solve the issue. Anyone care to confirm in their environment?

[shlomi-noach]

Binaries from openark#18 can be found here

[shlomi-noach]

Released downstream in https://github.com/openark/gh-ost/releases/tag/v1.1.3