System Versioning: move DML to handler layer

Summary

Move system-versioned row processing from individual SQL-layer DML implementations into the common handler interface.

The wrappers:

• handler::ha_write_row()
• handler::ha_update_row()
• handler::ha_delete_row()

must apply the required system-versioning behavior before invoking the storage-engine methods.

For timestamp-precise versioning, the handler layer must:

• Initialize period fields for newly inserted current rows.
• Generate a history row during an update.
• Convert a logical delete into creation of a final historical version.
• Preserve existing special handling for negative row lifetimes and history-key collisions.
• Support partitioned and non-partitioned tables.
• Produce the same binary-log events, affected-row counts, trigger behavior, and errors as the current implementation.

The caller must explicitly select whether the handler:

• Generates versioning metadata and history.
• Writes supplied current or historical rows without generating another version.
• Performs a physical operation that bypasses versioning.

Transaction-precise versioning remains implemented by InnoDB in this task, but it must be controlled through the same explicit handler-level operation mode.

Rationale

System-versioning DML is currently implemented in several independent places.

Normal SQL DML generates timestamp-based history in the SQL layer. Row-based replication and online ALTER TABLE contain separate versions of the same logic because they invoke the handler directly. InnoDB implements transaction-precise history internally and attempts to infer some SQL-layer operation types from trigger-event flags.

This duplication creates several risks:

• A DML path can omit history generation.
• The same row operation can behave differently under SQL execution, replication, or online ALTER.
• Fixes must be applied to several implementations.
• SQL-layer state can be interpreted differently by the storage engine.
• Adding another handler-based row-writing path requires another copy of versioning logic.
• Partition selection, error handling, and binary logging can diverge between paths.
• The handler interface does not fully express whether an operation is a logical versioned operation or a physical row operation.

Every row-changing operation ultimately passes through a handler interface. Applying system-versioning semantics there provides one common enforcement point for normal DML, replication from an unversioned source, and other handler-based operations.

This is also a preparatory step toward implementing system versioning as a storage-engine wrapper or another independent handler layer rather than embedding it in SQL command implementations.

Current implementation

Timestamp-based history generation for normal SQL DML is distributed across several SQL-layer paths.

For UPDATE:

• Sql_cmd_update::update_single_table() updates the current row through ha_update_row().
• multi_update::do_updates() implements equivalent behavior for multi-table updates.
• vers_insert_history_row() restores the old row from record[1], sets its ROW END through TABLE::vers_update_end(), and inserts it through ha_write_row().

For DELETE:

• TABLE::delete_row() normally converts the delete into an update that sets ROW END.
• It handles negative row lifetime.
• It handles history collisions reported as HA_ERR_FOUND_DUPP_KEY or HA_ERR_FOREIGN_DUPLICATE_KEY.
• In the supported collision cases, it falls back to physically deleting the current row.

For insert-like statements:

• Write_record::replace_row() contains system-versioning handling for REPLACE.
• Write_record::insert_on_duplicate_update() handles the update branch of INSERT ... ON DUPLICATE KEY UPDATE.
• These paths call vers_insert_history_row() or TABLE::delete_row<true>() as needed.

Initialization of current-row ROW START and ROW END is performed by TABLE::vers_update_fields(). Its separate refactoring is covered by MDEV-19597.

Replication and online ALTER

Row-based replication applies row events directly through the handler and bypasses normal SQL DML code.

When row events originate from a non-versioned source and are applied to a versioned destination, m_vers_from_plain causes the replication code to generate versioning metadata and history itself.

The duplicated logic includes:

• Update_rows_log_event::do_exec_row() calling vers_update_fields(), ha_update_row(), and vers_insert_history_row().
• Delete_rows_log_event::do_exec_row() setting ROW END and calling ha_update_row() instead of ha_delete_row().
• Write_rows_log_event::write_row() handling vers_update_fields(), secure_timestamp, and vers_fix_old_timestamp().
• record_compare() applying special comparison rules to system fields.

The same row-event implementation is used by online_alter_read_from_binlog().

When row events originate from a versioned source, the binary log already contains the generated historical rows as separate events. The replica must apply these events as supplied and must not generate another history row.

InnoDB implementation

InnoDB currently implements transaction-precise system versioning internally.

Relevant behavior includes:

• ha_innobase::update_row() inserts the old row with ROW_INS_HISTORICAL.
• ha_innobase::delete_row() performs VERSIONED_DELETE.
• upd_node_t::vers_make_delete() constructs the versioned delete.
• Foreign-key cascade history is generated in row0ins.cc and row0upd.cc.

The SQL layer avoids generating its own history for VERS_TRX_ID tables.

InnoDB also attempts to determine whether an update represents a logical delete. For timestamp-based tables, calc_row_difference() derives VERSIONED_DELETE from TRG_EVENT_DELETE in TABLE_LIST::trg_event_map or slave_fk_event_map.

Using trigger-event state to infer handler operation semantics is indirect and fragile. The caller must instead pass versioned-delete intent explicitly.

Proposed architecture

Implement system-versioned write, update, and delete processing in the public handler::ha_*_row() wrappers or in one common helper invoked exclusively by those wrappers.

The storage-engine entry points must remain lower-level physical operations.

Conceptually:

SQL, replication, ALTER, or internal caller

        |

        v

handler::ha_write_row()

handler::ha_update_row()

handler::ha_delete_row()

        |

        +-- apply selected system-versioning mode

        +-- generate metadata/history when required

        +-- perform binary logging

        |

        v

storage-engine write_row(), update_row(), delete_row()

The common handler path must distinguish between:

• A logical current-row operation requiring versioning.
• An explicit current or historical row supplied by the caller.
• A physical maintenance operation that must not create history.
• An engine-managed transaction-precise operation.

The system-versioning mode must be established explicitly before entering the handler wrapper. It must not be inferred from unrelated SQL-layer state.

Operation modes

Replace or generalize TABLE::vers_write with an explicit operation mode carried by TABLE, handler, or a scoped handler-operation context.

The implementation should provide modes equivalent to:

Exact enum and member names are implementation details.

The mode must not be a collection of ambiguous independent Boolean flags. Illegal combinations should be impossible or detected with debug assertions.

The selected mode must be scoped to one operation and restored afterward. It must not leak into:

• A subsequent row.
• Trigger-executed DML.
• Another table.
• Error cleanup.
• A retried operation.
• Another replication event.

Generate-history mode

Generate-history mode is used for logical DML against a system-versioned table.

This includes:

• Normal SQL INSERT.
• UPDATE.
• DELETE.
• REPLACE.
• The update branch of INSERT ... ON DUPLICATE KEY UPDATE.
• LOAD DATA.
• Multi-table DML.
• Row-based replication from an unversioned source into a versioned destination.
• Other handler-based logical DML that currently requires SQL-layer versioning.

For non-versioned tables, the mode must have no effect.

For a timestamp-precise versioned table, this mode causes the handler wrapper to implement the complete versioning operation.

Apply-supplied-version mode

Apply-supplied-version mode writes row images without generating additional history.

It is required when the caller already supplies complete period values or when history exists as a separate row operation.

Use cases include:

• Row-based replication from a versioned source.
• @@system_versioning_insert_history.
• Applying an explicit historical row.
• Copy-based ALTER TABLE.
• Rebuilding or converting a table while preserving existing period values.
• Logical restore of versioned history where supported.

In this mode:

• Explicit ROW START and ROW END values are preserved.
• An incoming history row is not versioned again.
• An incoming current row is not assigned a new statement timestamp unless existing compatibility logic requires correction.
• secure_timestamp and vers_fix_old_timestamp() behavior remains supported.
• No additional history row is emitted into the binary log.

Physical-operation mode

Physical-operation mode bypasses automatic history generation.

It is required for operations whose purpose is to physically maintain or remove already versioned records, including:

• DELETE HISTORY.
• Truncating history partitions.
• Partition maintenance that moves existing row versions.
• Internal cleanup.
• Table destruction or truncation.
• Other operations that intentionally delete or copy physical versions.

A physical delete must call the storage engine's physical delete rather than being converted into a versioned delete.

The mode must be used narrowly. Ordinary SQL DELETE must continue to use logical versioned-delete semantics.

Handler write

For handler::ha_write_row() in generate-history mode on a timestamp-precise versioned table:

• Initialize ROW START for a new current row.
• Initialize ROW END to the current-row maximum value.
• Preserve authorized explicit values.
• Evaluate generated columns that depend on the final period values.
• Invoke the normal storage-engine write.
• Emit the same row-based binary-log image as before.
• Preserve duplicate-key and error behavior.

Initialization should use the generic system-field mechanism implemented by MDEV-19597 when that work is available.

The handler must not overwrite explicitly supplied period values in apply-supplied-version mode.

Handler update

For handler::ha_update_row() in generate-history mode on a timestamp-precise versioned table, one logical update consists of:

1. Preserve the old current row.
2. Initialize the new current row's system fields.
3. Update the current physical row.
4. Construct a history row from the old row.
5. Preserve its original ROW START.
6. Set its ROW END to the statement's version boundary.
7. Select the correct history partition when partitioning is used.
8. Insert the history row.
9. Report any error using existing statement semantics.

The result must be equivalent to the current sequence:

ha_update_row(old_row, new_row);

vers_insert_history_row();

The SQL update implementation must no longer call vers_insert_history_row() after the handler wrapper implements the operation.

The history row must not cause SQL-level update triggers to run a second time.

Handler delete

For handler::ha_delete_row() in generate-history mode on a timestamp-precise versioned table, a logical delete must create the final historical version instead of simply removing the row without history.

The handler path must preserve existing semantics for:

• Setting ROW END to the statement's version boundary.
• Negative row lifetime.
• Multiple changes to the same row within one transaction.
• Duplicate-key collision with an existing history row.
• Foreign-key duplicate errors currently treated as history collisions.
• Fallback to physical deletion where current behavior requires it.
• Binary logging.
• Error reporting.

A caller issuing a logical delete must continue invoking ha_delete_row(). It must not need to know whether the implementation internally performs an update, an insert, or a physical delete.

Negative lifetime

The existing negative-lifetime rule must move into the common handler-level versioned-delete or versioned-update implementation.

When the row's current version began at or after the version boundary at which it would be closed, the operation must preserve existing behavior.

Depending on the current semantics, this may require:

• Suppressing creation of a zero-duration or negative-duration history row.
• Physically removing the current row.
• Updating the existing version rather than adding another history version.

The refactoring must not alter the SQL-visible result for repeated changes to the same logical row within one transaction or statement timestamp.

History collisions

The existing treatment of history-row key collisions must be preserved.

When insertion or conversion of a history row reports:

• HA_ERR_FOUND_DUPP_KEY.
• HA_ERR_FOREIGN_DUPLICATE_KEY.

the handler-level versioning code must apply the same fallback currently implemented by TABLE::delete_row() and related SQL paths.

A collision that is intentionally tolerated by current versioning semantics must not become a statement error.

Other duplicate-key errors must remain errors.

The handler implementation must distinguish:

• A tolerated collision of an equivalent history version.
• A genuine user-key conflict.
• A storage-engine error unrelated to versioning.

Multiple row changes

The implementation must preserve the current behavior when one logical row is changed more than once within:

• One statement.
• One transaction.
• A trigger chain.
• INSERT ... ON DUPLICATE KEY UPDATE.
• REPLACE.
• A foreign-key cascade.

The handler must not create invalid negative-lifetime versions or duplicate history rows.

Existing rules about whether an intermediate version is retained, merged, or physically replaced must remain unchanged.

Partitioning

System-versioned partitioning requires history generation at the top-level handler.

ha_partition invokes row methods of its child handlers. If each child independently applies system-versioning semantics, the operation can generate duplicate history rows or recursively version internal writes.

The rules are:

• Apply system-versioning logic exactly once at the top-level table handler.
• Calls from ha_partition to child handlers are physical engine operations.
• Do not recursively execute versioning logic in child ha_*_row() wrappers.
• Select the history partition using vers_set_hist_part() or its replacement.
• Allow the history row to be written to a partition different from the current row's partition.
• Restore partition-selection state after success or failure.
• Preserve SYSTEM_TIME, LIMIT, INTERVAL, and AUTO rotation behavior.

The implementation needs an explicit distinction between an externally invoked handler operation and a composite handler's internal child operation.

Binary logging

Row-based binary logging is performed inside the ha_*_row() path through binlog_log_row().

The refactoring must preserve the current event sequence.

For a logical update on a timestamp-versioned source:

• The changed current row must be logged.
• The generated history row must be logged as a separate row operation, as it is currently.
• A replica recognizing a versioned source must apply both supplied versions without generating another history row.

For a logical delete:

• The binary-log representation must remain compatible with current behavior.
• The replica must receive enough information to reproduce the current and historical state.
• The change must not alter supported row-event formats.

Internal writes performed to create history must pass through the required binary-logging path exactly once.

The operation must not:

• Omit generated history from the binary log.
• Log the same history row twice.
• Re-enter logical versioning while logging an internal history write.
• Change statement-based logging semantics.

Row-based replication

The replication applier must select handler operation mode based on the source and destination definitions.

When applying events from an unversioned source to a versioned destination:

• Use generate-history mode.
• Initialize destination period fields.
• Generate history for updates and deletes.
• Preserve secure_timestamp behavior.
• Preserve vers_fix_old_timestamp() behavior.

When applying events from a versioned source:

• Use apply-supplied-version mode.
• Apply current and history row events exactly as logged.
• Preserve explicit period values.
• Do not create additional history.

When applying to an unversioned destination:

• Preserve existing compatibility behavior.
• Do not invoke destination versioning logic.

Remove the duplicated history-generation code from:

• Update_rows_log_event::do_exec_row().
• Delete_rows_log_event::do_exec_row().
• Write_rows_log_event::write_row().
• Related online-ALTER event handling.

Retain only the logic required to select the correct handler mode, locate rows, and apply row images.

Record comparison

Review the system-field special cases in record_compare().

If the incoming replication row is applied in generate-history mode, comparison must ignore or normalize period fields in the same way as the current implementation.

If the event supplies explicit versioned values, comparison must preserve the required period-field matching semantics.

Move common policy into the handler-operation mode or a shared helper where possible. Do not leave replication with a separate implementation of versioning-field generation.

Online ALTER TABLE

online_alter_read_from_binlog() uses the row-event application path and must inherit the new behavior automatically.

For an online rebuild of a system-versioned table:

• Existing copied current and historical rows are applied without generating new history.
• Concurrent DML captured from an unversioned logical source generates destination history where currently required.
• Concurrent DML captured from a versioned source does not duplicate history.
• Period values remain stable.
• Rows are not lost or applied twice.
• Partition routing remains correct.
• Final table contents match the source at the ALTER synchronization point.

The refactoring must not introduce separate versioning logic specifically for online ALTER.

Transaction-precise versioning

InnoDB continues to generate transaction-precise history in this task.

The common handler mode must tell InnoDB whether to:

• Perform normal engine-managed versioning.
• Apply an explicitly supplied current or historical version.
• Execute a physical operation without generating history.
• Perform a logical versioned delete.

The SQL layer must not also generate timestamp-based history for VERS_TRX_ID tables.

Unifying transaction-precise and timestamp-precise history generation in one generic wrapper is outside this task.

Explicit versioned delete

The caller must pass logical versioned-delete intent explicitly to the handler or storage engine.

InnoDB must no longer need to infer VERSIONED_DELETE from:

• TABLE_LIST::trg_event_map.
• slave_fk_event_map.
• TRG_EVENT_DELETE inside calc_row_difference().

The explicit state must be available when calculating the engine update vector and foreign-key behavior.

This change must preserve the MDEV-16210 requirement that foreign-key checks do not treat history rows as current candidate rows.

The state must be operation-scoped and must not remain set for a later ordinary update.

Foreign keys

Foreign-key cascades may execute below the SQL handler wrapper.

For transaction-precise InnoDB tables, history generated by cascade processing in row0ins.cc and row0upd.cc must remain engine-managed.

The handler layer must not generate a second history row for the same cascading action.

The implementation must cover:

• ON UPDATE CASCADE.
• ON DELETE CASCADE.
• ON DELETE SET NULL.
• Self-referencing foreign keys.
• Multi-level cascades.
• Cascades between versioned and non-versioned tables.
• Timestamp-precise and transaction-precise destination tables where supported.

Foreign-key validation must continue to exclude historical versions where current behavior requires it.

Triggers

Trigger invocation remains in the SQL layer.

The handler-level physical implementation must not change logical trigger semantics.

In particular:

• Logical DELETE fires DELETE triggers even if the handler implements it using an update or history insert.
• Logical UPDATE fires UPDATE triggers once.
• The internal history-row insertion does not fire INSERT triggers.
• The internal update used to close a version does not fire additional UPDATE triggers.
• Trigger order remains unchanged.
• OLD and NEW values remain unchanged.
• Trigger-generated DML on another versioned table enters that table's handler with a new correctly scoped mode.

The handler must not invoke SQL triggers for its internal row operations.

REPLACE

REPLACE may logically delete a conflicting row and insert a new row.

The existing behavior must be retained:

• The replaced row receives the correct final historical version.
• The replacement row receives current-row period values.
• Internal history generation happens once.
• Trigger semantics remain those of REPLACE.
• Affected-row counts remain unchanged.
• Binary logging remains compatible.

Remove direct calls to vers_insert_history_row() and TABLE::delete_row<true>() from Write_record::replace_row() when their work is handled by the handler mode.

INSERT ON DUPLICATE KEY UPDATE

For INSERT ... ON DUPLICATE KEY UPDATE:

• The insert branch uses normal versioned write behavior.
• The update branch generates the old row's history exactly once.
• The current row receives the correct new period values.
• Affected-row count remains compatible.
• CLIENT_FOUND_ROWS behavior remains unchanged.
• Trigger behavior remains unchanged.
• Binary logging remains compatible.

Remove versioning-specific row-generation logic from Write_record::insert_on_duplicate_update() after the handler wrapper provides equivalent behavior.

Row counters

Internal history writes must not change SQL-visible row counters unless current behavior already includes them.

Preserve:

• ROW_COUNT().
• Affected rows returned to the client.
• Matched-row and changed-row counts.
• REPLACE affected-row rules.
• INSERT ... ON DUPLICATE KEY UPDATE affected-row rules.
• Deleted-row counts.
• Updated-row counts.
• Replication event accounting.
• Handler statistics currently visible to the optimizer or Performance Schema, unless physical writes are intentionally counted there today.

The internal history row must not be reported as an additional user-requested insert.

Error handling

Errors from internal versioning operations remain errors of the originating statement.

The implementation must preserve:

• Storage-engine error translation.
• Duplicate-key diagnostics.
• Deadlock and lock-wait handling.
• Foreign-key diagnostics.
• Statement and transaction rollback.
• Killed-query behavior.
• Disk-full handling.
• Partition-routing errors.
• Binary-log errors.

If updating the current row succeeds but writing its history row fails, the complete logical operation must fail according to the storage engine's normal statement atomicity guarantees.

Cleanup must restore:

• Record buffers.
• Partition-selection state.
• Handler operation mode.
• Explicit-value state.
• Versioned-delete state.
• Binary-log state.

A failed operation must not cause a subsequent handler call to use stale versioning context.

Atomicity

One logical versioned update or delete may contain multiple physical handler operations, but it must remain one atomic logical operation.

For transactional engines:

• The current-row change and history-row insertion commit or roll back together.
• A failure in either part rolls back both.
• Savepoint behavior remains unchanged.

For non-transactional engines:

• Existing partial-failure behavior must not regress.
• Error ordering and diagnostics must remain compatible.
• The refactoring must not claim stronger atomicity than the engine provides.

The handler wrapper must avoid partially updating shared SQL-layer state before all required