Pt-online-schema-change not handling rename column properly

Polyglot Projects Percona Toolkit
1

Hey
I have a table with columns x and x_new

i’m running pt-online-schema-change with the following alter

DROP COLUMN x, RENAME COLUMN x_new TO x

the tool finishes successfully, now table has only column x, but the values in it are the values that were before in x, not in x_new

version 3.5.7

I see I can solve it by making the alter

drop column x, CHANGE x_new x int

but in this case I get an error that I must specify --no-check-alter, and that I might lose data.

What is the best way to do such a change?

2

The way pt-osc works is as follows:

  • create new table foo_new like foo
  • alter table foo_new <insert user’s --alter statement>
  • copy rows from foo to foo_new
  • drop foo, rename foo_new to foo

Because you have drop column, rename column in 1 command, the foo_new table only has x column in it because you dropped it, then renamed the _new one. Then when ptosc copies rows from original table, it’s copying rows from x.

The ALTER TABLE is not a two-step process. It is not doing “drop X, copy data, then rename x_new to x” It performs the entire alter first, then copies the data. This behavior you are experiencing is expected.

This won’t solve it. you’ll have the same result because of what I described above.

If x_new already has the data you want, then run ptosc to handle the DROP only. Once done, run a regular alter to rename the column. Renaming a column does not require rebuilding the table (mysql 8.0).

1 Like
3

Thanks for the response, notice when said changing the alter would work, it is because I tested it, it worked as intended with the “change” version

4

Could you provide the PTDEBUG=1 of your test? I would be interested in seeing what is actually happening when you use CHANGE vs RENAME COLUMN in this case because the resulting column name is the same before the data copy happens.

5

No problem

I see now that the working version I got was two changes, not drop and change, not sure if that is what you didn’t see how it worked, but anyway :

create table test123(id int(10) not null, v1 int(10), v2 int(10), v3 int(10),v3_new int(10), primary key(id));

(Attachment pt_result is missing)

6

pt_result.txt (57.6 KB)

7

Looking at the source code for pt-osc:

   my $alter_change_col_re = qr/\bCHANGE \s+ (?:COLUMN \s+)?
                                ($table_ident) \s+ ($table_ident)/ix;

pt-osc has a specific check for CHANGE which tracks the old column name and the new name. That’s why it works! This is interesting and something I didn’t know pt-osc did.

Thanks for the debug info.