Announcement Module
No announcement yet.

pt-online-schema-change UPDATE/DELETE query

Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • pt-online-schema-change UPDATE/DELETE query

    I want to continue our production operations while adding a column and an index to a table that contains roughly 1.6 billion rows. It is expected that there would be a continuous stream of UPDATE and UPSERT queries on the original table while the tool is running. However, I came across an interesting observation:
    The tool uses triggers to propagate any change done on the original table to the new table. Since triggers are "triggered" immediately after the query on the original table completes, what happens if an UPDATE or DELETE query is executed and the corresponding rows have not been copied yet to the new table? Am I right to assume that the tool would fail to reflect the changes on the new table once the rows are finally copied to the new table?

  • #2
    r4z0r: not sure where do you see the problem in. If a row is not copied yet, trigger will not update/delete anything in _new table, just the original table's row is updated. So when it comes to copying that row, new value will be reflected in the new table. There is no inconsistency issue here.
    You can try yourself if you don't trust this mechanism - start altering big table, update some rows with higher ids, make sure those are not yet present in new table, and after the tool's job is finished - check the row is updated properly.