Tracking Table Maintenance

All maintenance here is a DBA’s work, done with a DBA account: GoArchive’s own account holds no DELETE or DROP on the tracking schema. The examples use a job_schema named goarchive and job id 42; substitute yours.

Inspection queries #

All jobs

SELECT id, job_name, root_table, job_type, job_status,
       TIMESTAMPDIFF(SECOND, last_heartbeat_at, UTC_TIMESTAMP()) AS heartbeat_age_sec
FROM goarchive.archiver_job
ORDER BY id;

Is anything running? A heartbeat older than 60 seconds is stale.

SELECT job_name, root_table,
       TIMESTAMPDIFF(SECOND, last_heartbeat_at, UTC_TIMESTAMP()) AS heartbeat_age_sec,
       CASE WHEN last_heartbeat_at IS NULL
                 OR TIMESTAMPDIFF(SECOND, last_heartbeat_at, UTC_TIMESTAMP()) > 60
            THEN 'STALE' ELSE 'live' END AS liveness
FROM goarchive.archiver_job
WHERE job_status = 1;

Status breakdown for one job

SELECT ga_status,
       CASE ga_status WHEN 1 THEN 'copied' WHEN 2 THEN 'completed' WHEN 4 THEN 'deleting' END AS name,
       COUNT(*) AS roots
FROM goarchive.archiver_job_log_42
GROUP BY ga_status;

Roots the next run will replay (archive: 1; purge: 4)

SELECT * FROM goarchive.archiver_job_log_42 WHERE ga_status IN (1, 4) ORDER BY k1;

The job’s binding

SELECT source_server_uuid, source_database, destination_database, config_fingerprint, root_key_columns
FROM goarchive.archiver_job WHERE job_name = 'archive_old_orders';

What is safe to delete #

Rule zero: never touch tracking tables while the job runs. Confirm job_status = 0, or job_status = 1 with a stale heartbeat and a process verified to be gone (below ).

To clear all tracking state, first confirm that no log holds a copied or deleting root (otherwise that is abandonment, for every such job), then drop goarchive_meta, archiver_job and every archiver_job_log_<id> together. Prune large logs in chunks (DELETE … WHERE ga_status = 2 LIMIT 50000, repeated), and export them first if the record has compliance value.

Abandoning logged roots #

Pending roots are interrupted work. An archive root at copied has its whole tree in the destination, and some of its source rows, children first, may already be deleted. A purge root at deleting may be partly deleted. Only the log can finish them: recovery rediscovers each tree by key, whatever the where.

Prefer finishing them: restore the original configuration or source and run once, or re-bind after a failover . Until they are finished or abandoned, no other job whose tree shares one of this job’s tables runs on the same source data.

Abandoning means deleting their log rows, so the next run binds to the current configuration and never finishes them. Do it only when the original configuration or source is gone, with backups of both servers, and resolve what is left by hand first:

Then, with the job not running:

SELECT ga_status, COUNT(*) FROM goarchive.archiver_job_log_42 GROUP BY ga_status;
DELETE FROM goarchive.archiver_job_log_42 WHERE ga_status = 1;   -- archive: copied
DELETE FROM goarchive.archiver_job_log_42 WHERE ga_status = 4;   -- purge: deleting
DELETE FROM goarchive.archiver_job_log_42 WHERE ga_status = 2;   -- copy-only: completed

Re-binding a job after a failover #

After a source failover, the promoted server holds the same data under a new server_uuid, and the binding check refuses a job with pending work. Once you have confirmed it is the same data, point the job at the new server, then rerun it to finish the logged work:

-- On the new source:
SELECT @@GLOBAL.server_uuid;
-- On the destination, with that value:
UPDATE goarchive.archiver_job SET source_server_uuid = '<new server_uuid>'
WHERE job_name = 'archive_old_orders';

This is a deliberate act: replaying a log against a source that does not hold the same data deletes or skips the wrong rows. Write the identity exactly: a value that is not a well-formed uuid is refused by the job’s own run, and keeps blocking overlapping jobs.

Until the re-bind, other jobs whose trees share the stopped job’s tables are refused on the new server too when it holds the old server’s transactions, does not run with gtid_mode = ON, or is a Group Replication member. Two cases are not detected, and need the re-bind before any other job runs on those tables: a server that received the data only before a GTID migration, and a server outside a group that holds the group’s data.

Retiring a job completely #

To remove a job and all its state, for a name you no longer use, or one you want to give another type, root table or root key. A job with pending work cannot simply be retired: dropping its log drops the only record of its interrupted trees.

-- 1. Confirm it is not running.
SELECT job_name, job_status,
       TIMESTAMPDIFF(SECOND, last_heartbeat_at, UTC_TIMESTAMP()) AS heartbeat_age_sec
FROM goarchive.archiver_job WHERE job_name = 'archive_old_orders';

-- 2. Confirm it has no pending work: this must return 0 for archive and purge.
--    Otherwise finish the job first, or treat this as abandonment (above).
SELECT COUNT(*) FROM goarchive.archiver_job_log_42 WHERE ga_status IN (1, 4);

-- 3. Drop its log table (substitute the id).
DROP TABLE IF EXISTS goarchive.archiver_job_log_42;

-- 4. Remove the job row: DELETE, never TRUNCATE.
DELETE FROM goarchive.archiver_job WHERE job_name = 'archive_old_orders';

A job created again under the name starts with an empty log. Without pending work, archive and purge lose only the record. A copy-only job loses its resume skips: its rerun copies every root again, and fails on duplicates in a destination that still holds them.

Clearing a stale running job #

A crashed run leaves job_status = 1 with an ageing heartbeat. That job, and any job whose log holds copied or deleting roots whatever its status, refuses the runs of other jobs whose trees share one of its tables:

engine: job "purge_logs" shares source tables in `shop` with jobs that are marked running or
stopped with unfinished work (registry `goarchive`.`archiver_job`). This run did nothing.
  archive_old_orders: marked running, heartbeat 1h4m1s ago, unfinished roots in its log; shares payment_logs
Rerun each job with unfinished roots so that its recovery finishes them, then rerun this one. A job
that cannot be rerun (its source or config is gone): docs/README_JOBS_SCHEMA.md, "Abandoning logged roots".
A job marked running whose heartbeat stopped: confirm its process is gone first
(docs/README_JOBS_SCHEMA.md, "Clearing a stale running job").

The documents the message names are the sections Abandoning logged roots and Clearing a stale running job on this page.

First verify that the old process is gone. A stale heartbeat does not release the job lock; only the closing of its database session does. On the destination:

SELECT IS_USED_LOCK(CONCAT('goarchive:j:', LEFT(SHA2(CONCAT(LOWER('<job_schema>'), CHAR(0), LOWER('<job_name>')), 256), 32)));

NULL means no session holds it. A connection id is a live session.

Then rerun the job itself, with no UPDATE: its log drives recovery, and once its unfinished roots are finished it blocks nobody. Mark a job idle (UPDATE … SET job_status = 0 WHERE id = <id>) only when its log holds no copied or deleting root: marking does not finish the work, and while the log holds such roots the job still blocks overlapping jobs. Do not delete the log rows; that is abandonment.

Backups #

• The tracking tables are InnoDB and transactional. Include them in the destination’s normal backups, taken together with the archived data: a log restored from a different point in time than the data it describes misleads recovery and copy-only resume.
• Restoring the destination data without its tracking tables leaves jobs unregistered. Archive and purge start afresh; copy-only copies everything again, and fails on the duplicates.
• A separate job_schema keeps tracking state out of backups of the archived data, and is granted on its own.