Can i use gh-ost to migrate uuid fields from char(36) to binary(18)

[erfanium]

I want to migrate our UUID columns (or other hex columns) to the binary data type in MySQL, with minimal downtime.

My first idea was to create ghost tables manually and keep them in sync with the original table using triggers. But I wonder if I can go triggerless and use binary logs instead.

[arthurschreiber]

The way we do this is through a multi step process:

1. Add the new column (maybe call it uuid_bin or something to distinguish it from the existing column). You should be able to define a default like unhex(replace(uuid, '-', '')) to give the column a default value based on the existing uuid column.
2. Add indexes as appropriate to support the new column.
3. Change your application to write to both the old and the new column, and to use the new column exclusively for reading.
4. Make the new column non-nullable, remove the default.
5. Stop writing to the old column, exclusively reading and writing to the new column.
6. Drop the old column.

[erfanium]

@arthurschreiber Thanks! Your suggestion is good for non-primary key columns, but we have many tables that use CHAR(36) as the primary key, and changing them is not possible in this way. We would need to change the primary key of the table, which means a full recreation of the entire table. So in result, we need a tool like gh-ost to change the primary key without downtime for the final step.

I think it would be really helpful to be able to migrate these tables in a single step using only gh-ost. gh-ost can easily handle this conversion during the migration process.

[arthurschreiber]

I don't think I understand your concerns? You can change the primary key of a table with gh-ost, which you can do after step 4. You'd change the primary key from your uuid column to the uuid_bin column. You just need one shared UNIQUE KEY between the before / after table for the migration (which in this case would be a unique key on the uuid_bin column, added in step 2).