Skip to main content

DEPLOY.LEGACY.OSC_ARTIFACT — What an online-schema-change tool left behind

  • Category: safety
  • Severity: low for a leftover table, medium for a leftover trigger — the axis earns the difference: a table costs storage, a trigger costs every write on a live table
  • Level: 0
  • Downtime class: online — the artifacts exist and, apart from the triggers, do nothing
  • Stability: stable
  • Suites: deploy
  • Command: sqlens:postdeploy only
  • Applies to: MySQL 8.4 and newer
  • Not transferable to MariaDB: this page describes MySQL 8.4 behavior and the schemes of the tools that run against it

What it reads​

information_schema.TABLES and information_schema.TRIGGERS, for names a tool generates:

patterntoolwhat it was
_<table>_ghogh-ostthe ghost table the new schema is built in
_<table>_ghcgh-ostthe changelog table it tracks its own progress in
_<table>_delgh-ostthe original, renamed out of the way at cut-over
_<table>_newpt-oscthe new table it copies into
_<table>_oldpt-oscthe original after the atomic rename
#sql-…, #sql2-…, #sql-ib…InnoDBthe temporary table an ALGORITHM=COPY rebuild works in
pt_osc_<db>_<table>_ins|upd|delpt-oscthe triggers that keep its copy in step

This is not a suffix hunt, and the leading underscore is why​

Its PostgreSQL sibling DEPLOY.LEGACY.ORPHAN_TRANSITION_OBJECT matches shapes a person writes when they mean "temporary", and says out loud that a name is not evidence. This list is narrower: no project writes _orders_gho by hand, and #sql-1234_a cannot even be created without backticks because the # makes the name unquotable in ordinary SQL.

The discriminator is the leading underscore. photo_gho ends in _gho and is an ordinary permanent name; _orders_gho is a tool's output. Both are in the test fixture, and only one is reported.

It is still undetermined, for a different reason​

The sibling is undetermined because a name proves nothing. Here the name genuinely is a tool's output — but a run still in flight looks exactly like one that died. gh-ost creates _t_gho and works in it for hours; catching that mid-migration and calling it wreckage reports the healthy case as the broken one, which is the more expensive of the two mistakes.

So: confirm nothing is running, then remove it.

--expect-shadow can settle it for you​

The one thing that separates a dead artifact from a live one, without asking you to check by hand, is whether any migration describes it. It never should — an online-schema-change tool creates these itself — so an artifact that is also absent from a full replay of your migrations is wreckage rather than work in progress:

php artisan sqlens:postdeploy --expect-shadow

That replays your migrations into a throwaway shadow database and compares. When the comparison reports the same object as unexpected_in_database, the finding is raised from undetermined to fail. It never goes the other way: a comparison that could not run, or that accounted for the object, leaves the finding at the rung it earned.

The option is off by default because it creates a database and therefore falls under the production guard — and a run without it says so, on stderr and in the report's run.expectation block, so a green post-deploy can never quietly mean nobody looked.

The trigger is the one to look at today​

Everything else on this list costs storage and backup time — real, and payable at leisure. A leftover pt_osc_* trigger fires on every INSERT, UPDATE and DELETE on the real table, for as long as it exists, writing into a table nothing reads.

That is why it carries medium where the tables carry low, and why its message says so in its own sentence rather than being folded into "an artifact was found".

DEPLOY.LEGACY.INVALID_INDEX on MySQL answers NOT APPLICABLE, out loud​

InnoDB has no invalid-index state: a build is either committed or rolled back, and there is no equivalent of PostgreSQL's indisvalid = false.

The tempting shape is no check at all. That is exactly the silent green this package refuses — a user who read the PostgreSQL documentation, ran the same command against MySQL and saw nothing has been told two things at once, there is no wreckage and this question was never asked, with no way to tell which. On the engine where somebody is most likely to be comparing two databases, that is the worst place to leave the distinction unmade.

So the check runs, answers undetermined, names the reason, and points at the case that is real on this engine: the #sql- temporary table above. It reads nothing — no query, no session, no privilege — because the answer is a property of the engine rather than of the instance.

Primum non nocere​

Reports; never drops. A #sql- table in particular may be one InnoDB is using right now, and the message says so rather than leaving the reader to find out.

Sources​