operational question: how does one decide whether a _gho or _ghc are abandoned? about gh-ost

[shlomi-noach]

If the gh-ost process dies, we could theoretically resume the operation, as long as we know where to resume in the table copy process, know where to resume in the binary log for incoming changes, and have the binary logs to resume from.

omg.

So, I've been thinking about this: we only need to know where to resume in the table-copy process, actually. It turns out, and I need to put this in detailed writing, that replaying the RBR is idempotent!! Which means we can just replay it from some point in the past (but of course we must never skip entries).

But, I would suggest this is still way in the future.

[shlomi-noach]

The quick answer is to select max(last_update) from _whatever_ghc. An active gh-ost operation will routinely update that table (within the second) even while throttling. Thus, if max(last_update) is, say, a minute ago (are you checking on master? That's definite. Are you checking on replica? Make sure lag is OK) indicates the migration is dead.

[jonahberquist]

I think abandoned and dead are two different states. With trigger-based migration tools, if the triggers are gone, we have to give up and start over, but that's not the case here. If the gh-ost process dies, we could theoretically resume the operation, as long as we know where to resume in the table copy process, know where to resume in the binary log for incoming changes, and have the binary logs to resume from.

[shlomi-noach]

The official query to see if a migration is running is:

select last_update from _tbl_ghc where hint='heartbeat';

(to get last known activity), or

select last_update > now() - interval 1 minute as is_alive from _tbl_ghc where hint='heartbeat';

for a heuristic "if it hasn't been updated in the last minute it must be dead"
cc @tomkrouper

[pbitty]

The above sounds great. Having the ability to resume would be a great feature.

Now that binlog events are applied in a transaction, it should be possible to confidently store and read the last-processed binlog position. The same could be done with the table copy process, if the statements are also wrapped in a transaction.