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?
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.
i’m curious about this as well, and am hoping to get a little more information about how the triggers work in this scenario. i’m also concerned about ordering if the target table has an auto-increment id (also trying to understand replication and statement-based vs. mixed-mode implications for pt-osc).
assume i have the following table, but there are thousands of rows between each record below (illustrating for purposes of chunking / nibbler)…
id | value
10000 | a
20000 | b
30000 | c
40000 | d
50000 | e
60000 | f
70000 | g
80000 | h
90000 | i
10000 | j
if pt-osc is run, and chunking is grabbing these in batches, and is currently 40% done, the new table should look like this…
10000 | a
20000 | b
30000 | c
40000 | d
CASE 1: table row order, update to a not-yet copied row
if i get an update to row 80000, setting it’s value to hhh, what actually happens with that update and related trigger? (insert, update and delete trigger from pt-osc)
- update statement is executed against the original table, so the original table now contains a row that looks like “80000 | hhh”
- trigger is executed after the update, using a “REPLACE INTO …” syntax to add that row to the new temporary table
QUESTION: won’t my new table have a different row order from the original table, and won’t this differ from slaves?
CASE 2: copy chunks & delete trigger
if rows 20000 to 30000 are queried for copy from original table to the new temporary table, via “SELECT …” what happens if a delete trigger executes during the chunk copy?
- rows 20000 to 30000 are queried via select for copy from original table to new temporary table
- an incoming “DELETE …” statement for a row in the copy chunk is executed
- the row is deleted in the original table
- the delete trigger “DELETE IGNORE FROM …” is executed
QUESTION: at what point is the trigger executed against the new temporary table? is it blocked by the “LOCK IN SHARE MODE” on the chunk row copy and therefore guaranteed to execute after the chunk copy?
if yes, then the delete trigger would execute after the chunked copy completes, keeping the two tables in sync.
however, since the chunk target rows don’t exist in the new temporary table, wouldn’t this require a table lock (you can’t lock rows that don’t exist can you)?
if the trigger isn’t blocked, the DELETE IGNORE would execute before or during the chunk copy and silently fail if it hadn’t been copied yet. the row in question could then erroneously be copied into the into the new table after the delete trigger. the “deleted” row would no longer exist in the original table but WOULD exist in the new temporary table.
can someone verify this?
CASE 3: insert new row & auto-increment vs. table row order
if an incoming new insert is executed against the original table, what happens to row order in the new table?
- insert statement is run against original table, row is appended to end of table and id assigned to auto-increment value
- insert trigger is executed
QUESTION: if the trigger executes immediately, or between chunks, won’t the insert into the new table result in the new table being in a different order from the original table? similarly, will downstream slave replicas have a different table order from the master?
Many thanks to anyone who can provide definitive information. It would be helpful for this to be included on the pt-online-schema-change documentation page. It would also be helpful to understand implications for replication in general (how the tool should be used in a standard master / slave topology).