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
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.
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
Hello Simon, there is no overwrite. The general steps are:
- new table gets created empty with the new structure
- rows are copied from old to new table
- while (2) happens, any on-going operations to the old table are replicated to the new table via triggers
- 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
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.