Atomic Rename Updating one Table to Another

MySQL & MariaDB
,
1

I am trying to update one table in Mysql called blpu with updated rows from another table using online schema change. I had created a new identical table _blpu and loaded the rows. When I run online schema change the original table stays the same. Neither table is a master or a slave to each other.

The command I used was
pt-online-schema-change D=osdata, t=blpu, h=host.docker.internal, u=root, p=pwd --execute

I had tried to force the rename with
pt-online-schema-change D=osdata,t=blpu,h=host.docker.internal,u=root,p=pwd RENAME TABLE ‘osdata.blpu’ TO ‘osdata.blpu_old’, ‘osdata._blpu’ TO ‘osdata.blpu’ --execute

this throws an sql error plus the documentation says RENAME TABLE can’t be used. I am new to pt-online-schema-change please excuse my lack of knowledge on this.

OS is Ubuntu 22.04
Mysql version 8.0.26

Thanks

Simon

2

Hello, you are misunderstanding how pt-osc works. I suggest you read through the documentation. You don’t need to explicitly rename the table or copy the rows, that happens as part of pt-online-schema-change automatically. Instead feed pt-osc the ALTER TABLE command that you want to run on the original table.

3

Hi,

Read through the documentation, maybe missed how that scenario works, can add columns etc no problem, I don’t quite get how pt-osc would overwrite one table with another.

Thanks for your help

Simon

4

Hello Simon, there is no overwrite. The general steps are:

  1. new table gets created empty with the new structure
  2. rows are copied from old to new table
  3. while (2) happens, any on-going operations to the old table are replicated to the new table via triggers
  4. when both old and new tables are in-sync, there is an atomic rename operation to put the new table in place of the old table

Hope that helps

5

Hi Ivan.

Thank you that has made things a lot more clear.

Maybe I am using the wrong tool as the main thing I am doing is updating rows rather than making changes to the table’s structure itself. We will be adding columns etc to live tables regularly so pt-osc will be very useful for that.

Thanks

Simon