mysqldump: dump history in XML

Summary

Allow mariadb-dump (and the legacy mysqldump command name) to use -dump-history together with -xml.

For a system-versioned table, the XML dump must contain:

• Current rows.
• All historical row versions.
• Explicit ROW START and ROW END values.
• Implicit system-versioning period columns, even when they are normally hidden.
• Sufficient column metadata to identify the period columns in the XML output.

For a non-versioned table, --dump-history must not change the generated XML.

Rationale

mariadb-dump --dump-history allows current and historical versions of rows from a system-versioned table to be included in a logical dump.

mariadb-dump --xml provides a structured, well-formed XML representation of table definitions and data. It is useful for external backup tools, data-processing pipelines, auditing systems, and applications that consume dump output without parsing SQL statements.

The options currently cannot be used together:

mariadb-dump --xml --dump-history test t1

Current result:

mariadb-dump: --xml can't be used with --dump-history.

There is no fundamental conflict between selecting historical rows and serializing the resulting rows as XML.

The SQL dump path retrieves history using:

SELECT ...

FROM table_name

FOR SYSTEM_TIME ALL;

The XML path can use the same temporal query and serialize each returned row through its existing XML row writer.

History dumping requires special handling of period columns. A table created with simplified system-versioning syntax may have implicit ROW START and ROW END columns that are not included in an ordinary projection. These values must be selected explicitly so that different versions of an otherwise identical row remain distinguishable in the XML output.

The XML format is an export format, not an executable SQL restore script. Therefore, SQL-only statements such as enabling system_versioning_insert_history must not be emitted into the XML document.

Current behavior

Option validation explicitly rejects this combination:

mariadb-dump --xml --dump-history test t1

As a result, users must choose between:

• Dumping history in SQL format.
• Dumping only current rows in XML format.

This prevents XML consumers from receiving the complete contents of a system-versioned table.

Expected behavior

The following command must succeed:

mariadb-dump --xml --dump-history test t1

For a system-versioned table, its data must be retrieved using semantics equivalent to:

SELECT explicit_columns, row_start, row_end

FROM t1

FOR SYSTEM_TIME ALL;

The output must be well-formed XML using the normal mariadb-dump --xml document structure.

For example:

<?xml version="1.0"?>

<mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <database name="test">

    <table_structure name="t1">

      ...

    </table_structure>

    <table_data name="t1">

      <row>

        <field name="id">1</field>

        <field name="value">old value</field>

        <field name="row_start">2026-01-01 10:00:00.000000</field>

        <field name="row_end">2026-01-02 10:00:00.000000</field>

      </row>

      <row>

        <field name="id">1</field>

        <field name="value">current value</field>

        <field name="row_start">2026-01-02 10:00:00.000000</field>

        <field name="row_end">2038-01-19 03:14:07.999999</field>

      </row>

    </table_data>

  </database>

</mysqldump>

The exact representation of the current-row infinity value must match the value returned by the server and the existing XML serialization rules.

Option handling

Remove the unconditional incompatibility check for:

--xml --dump-history

The following option combinations must be supported:

mariadb-dump --xml --dump-history database

mariadb-dump --xml --dump-history database table

mariadb-dump --xml --dump-history --no-create-info database

mariadb-dump --xml --dump-history --no-data database

mariadb-dump --xml --dump-history --where="id > 10" database table

mariadb-dump --xml --dump-history --single-transaction database

Existing incompatible option combinations unrelated to XML must remain rejected.

In particular:

• -dump-history and -as-of remain mutually exclusive.
• -xml and -tab remain mutually exclusive.
• Restrictions required by unsupported transaction-precise history remain unchanged.
• Options that affect SQL INSERT syntax have no effect on XML row serialization.

Versioned-table detection

When --dump-history is enabled, the XML path must determine whether each dumped object is a system-versioned base table.

Detection must not rely on parsing localized output or assuming that every table in the dump is versioned.

For each object:

• A system-versioned base table is queried with FOR SYSTEM_TIME ALL.
• An ordinary table is queried normally.
• A view is handled according to existing view-dump behavior.
• A sequence is handled according to existing sequence-dump behavior.
• An ignored table remains ignored.
• A table for which data dumping is disabled is not queried for rows.

The check should use the same system-versioned-table detection logic as the SQL dump path.

Historical-row selection

For a system-versioned table, XML history dumping must use:

FROM table_name FOR SYSTEM_TIME ALL

The clause must be placed before any applicable WHERE and ORDER BY clauses.

Conceptually:

SELECT column_list

FROM table_name

FOR SYSTEM_TIME ALL

WHERE user_condition

ORDER BY selected_order;

The command must include:

• Every current row visible in FOR SYSTEM_TIME ALL.
• Every historical row visible in FOR SYSTEM_TIME ALL.
• Rows from all physical history partitions.
• Rows with identical user-column values but different system-versioning periods.
• Rows whose primary or unique-key values changed between versions.

No historical row may be removed merely because its user columns duplicate another version.

Period columns

A history dump must include the system-versioning period values for every row.

For a table with explicitly declared period columns:

CREATE TABLE t1 (

  id INT,

  row_start TIMESTAMP(6) GENERATED ALWAYS AS ROW START,

  row_end TIMESTAMP(6) GENERATED ALWAYS AS ROW END,

  PERIOD FOR SYSTEM_TIME (row_start, row_end)

) WITH SYSTEM VERSIONING;

the XML must use the declared column names:

<field name="row_start">...</field>

<field name="row_end">...</field>

If different names are declared, those names must be preserved.

For a table using implicit system-versioning columns:

CREATE TABLE t1 (

  id INT

) WITH SYSTEM VERSIONING;

mariadb-dump must explicitly include the implicit period columns in the selected field list.

The implementation must not rely on SELECT * to include hidden period columns.

Column order

The order of fields in every XML row must match the column metadata emitted for the corresponding table.

For a history dump:

• User columns retain their normal table order.
• Explicit period columns retain their declared positions.
• Implicit period columns are added using the same canonical order as SQL history dumps.
• Every row contains the same field sequence.
• The number of selected fields matches the number of serialized fields.

If implicit columns are appended by the dump client, use:

1. ROW START.
2. ROW END.

The column list must be built once for the table and used consistently for its query, metadata, and row output.

XML structure metadata

When --dump-history is active, the XML table structure must describe every column present in table_data, including implicit period columns added to the history projection.

The metadata must allow an XML consumer to determine:

• The field name.
• The SQL data type.
• Nullability.
• Whether the column is generated.
• Whether it is ROW START or ROW END, when that information is available through the existing XML metadata model.

The implementation may extend the existing XML field metadata with attributes or rows needed to represent system-versioning roles, provided:

• Existing XML output remains compatible when --dump-history is absent.
• The result remains well-formed XML.
• User-defined column names are preserved.
• Implicit period columns are represented consistently.
• XML special characters are escaped using the existing XML writer.

The table_data section must not contain fields that are absent from the corresponding structural metadata.

XML serialization

Historical rows must use the existing XML value-serialization rules.

This includes correct handling of:

• SQL NULL.
• Empty strings.
• The literal string NULL.
• Binary data.
• BLOB values.
• Character-set conversion.
• XML-reserved characters.
• Control characters supported by the existing XML format.
• Numeric values.
• Date and time values.
• Fractional-second precision.

For example, SQL NULL must remain distinguishable from an empty string:

<field name="nullable_value" xsi:nil="true" />

<field name="empty_value"></field>

History support must not introduce a second XML escaping implementation.

Timestamp values

ROW START and ROW END values must be serialized without losing precision.

The output must preserve:

• Fractional-second precision.
• Current-row infinity representation.
• Session-independent interpretation under the existing --tz-utc behavior.
• Values required to distinguish adjacent row versions.
• Explicit period-column data types.

The dump must not replace period values with the dump execution time.

The dump must not calculate period values on the client.

Transaction-precise history

Existing restrictions for transaction-precise system-versioned tables must also apply to XML history dumps.

If mariadb-dump -dump-history does not support transaction-precise history in the SQL path, combining it with -xml must return the same unsupported-operation error rather than silently producing incomplete data.

The XML implementation must not:

• Treat transaction identifiers as timestamps.
• Omit transaction-precise period columns.
• Dump current rows only while claiming that history was included.
• Generate invalid period values.

Support for transaction-precise history is outside the scope of this task unless it is implemented for all --dump-history formats.

WHERE handling

--where must filter the complete temporal result.

For example:

mariadb-dump \

  --xml \

  --dump-history \

  --where="id = 10" \

  test t1

must use semantics equivalent to:

SELECT ...

FROM t1

FOR SYSTEM_TIME ALL

WHERE id = 10;

The predicate therefore applies to both current and historical row versions.

Predicates may explicitly reference visible period columns.

If implicit period columns become available only through the generated projection, existing SQL name-resolution behavior determines whether they can be referenced by --where.

Ordering

--order-by-primary must remain compatible with XML history dumping.

The option may order rows by the user-defined primary or unique key according to existing behavior.

Because several versions may share the same user key, the relative order of those versions is not guaranteed unless period columns are also part of the generated ordering.

This task does not require changing the ordering contract.

However:

• Ordering must not eliminate duplicate versions.
• Ordering must not cause field-count mismatches.
• Ordering must not omit rows with repeated keys.
• The XML document must remain well formed if an ordered query fails.

No-data mode

The following command must produce structure metadata without row data:

mariadb-dump --xml --dump-history --no-data test t1

The XML must remain well formed.

If history mode causes implicit period columns to be exposed in the XML structure, they must still be described when --no-data is used.

No temporal data query should be executed when data dumping is disabled.

No-create-info mode

The following command must produce table data without the normal table-structure section:

mariadb-dump --xml --dump-history --no-create-info test t1

The output must include current and historical rows.

Every row must still include the period-column fields required to distinguish versions.

The absence of table_structure must follow existing --no-create-info XML behavior.

Multiple tables

When a database contains both versioned and non-versioned tables:

mariadb-dump --xml --dump-history test

the result must contain:

• Current and historical rows for system-versioned tables.
• Normal current rows for ordinary tables.
• Existing structure handling for views.
• Existing handling for sequences.
• One well-formed XML document.

--dump-history must not cause FOR SYSTEM_TIME ALL to be appended to queries against ordinary tables.

An error in one table must follow existing dump error and --force behavior without corrupting the XML syntax of successfully emitted sections where avoidable.

Empty tables

An empty system-versioned table must produce an empty table_data element according to the existing XML style:

<table_data name="t1">

</table_data>

A table containing only current rows must emit those rows.

A table containing only retained history, where such a state is supported, must emit all visible historical rows.

Empty output must not be interpreted as evidence that the table is non-versioned.

Consistent snapshots

--single-transaction must retain its existing consistency semantics.

When used with --xml --dump-history:

• Current and historical rows must be read from the same transaction snapshot where the storage engine supports it.
• The temporal query must not require a second independently timed dump pass.
• A concurrent update must not cause one row version to be omitted or represented twice because current and history were selected separately.

The implementation should retrieve current and historical versions through one FOR SYSTEM_TIME ALL query per table.

SQL-only restore directives

XML output must not contain SQL statements such as:

SET system_versioning_insert_history=1;

SET system_versioning_insert_history=0;

These statements are used by SQL dump restoration and are not valid elements in a well-formed XML dump.

The XML output must contain the period values as data fields, leaving interpretation and import behavior to the XML consumer.

This task does not add an XML restore mode to mariadb-dump or the mariadb client.

Output errors

If retrieving history fails, mariadb-dump must:

• Return a non-zero exit status.
• Write the diagnostic to standard error.
• Avoid writing the error message as unescaped text inside the XML document.
• Follow --force behavior where applicable.
• Close already opened XML elements where the existing output architecture permits.
• Never silently fall back to dumping current rows only.

A server that does not support FOR SYSTEM_TIME ALL or the required metadata must produce a clear compatibility error.

Backward compatibility

Without -dump-history, -xml output must remain unchanged.

The following command must continue to dump only rows visible through an ordinary table query:

mariadb-dump --xml test t1

The change must not alter:

• Existing XML element names.
• Existing XML escaping.
• NULL representation.
• Ordinary-table field order.
• View handling.
• Sequence handling.
• --as-of behavior.
• SQL-format history dumps.
• Delimited-text output.
• --tab restrictions.
• Client-visible behavior for unsupported transaction-precise history.

Existing tools consuming ordinary XML dumps must not be required to handle new history metadata unless --dump-history is specified.

Implementation guidance

The implementation should reuse the SQL history-dump path where possible.

The common logic should:

1. Detect whether the table is system-versioned.
2. Build the complete selected-column list.
3. Add implicit ROW START and ROW END columns when required.
4. Append FOR SYSTEM_TIME ALL for --dump-history.
5. Apply WHERE and ORDER BY after the temporal clause.
6. Execute one query returning current and historical versions.
7. Pass the result to the selected SQL or XML serializer.

XML-specific behavior should be limited to:

• Writing XML structure metadata.
• Serializing result fields and rows.
• Avoiding SQL restore directives.
• Maintaining well-formed XML.

Do not implement history selection separately in a way that can diverge between SQL and XML output.

Acceptance criteria

The task is complete when all of the following are satisfied:

• -xml and -dump-history can be specified together.
• The command no longer reports that the options are incompatible.
• Ordinary tables are dumped normally.
• System-versioned tables are queried with FOR SYSTEM_TIME ALL.
• Current rows are included.
• Every historical row version is included.
• Explicit ROW START and ROW END columns are included.
• Implicit ROW START and ROW END columns are included.
• Period-column values retain their precision.
• Rows with identical user values and different periods remain separate XML rows.
• XML field order matches the selected column metadata.
• SQL NULL, empty strings, and the string NULL remain distinguishable.
• XML special characters remain correctly escaped.
• --where filters current and historical versions.
• --no-data produces valid structure-only XML.
• --no-create-info produces valid history data without structure output.
• --single-transaction remains supported.
• Mixed versioned and ordinary tables are handled correctly.
• SQL restore directives are not emitted in XML.
• Unsupported transaction-precise history produces the existing error.
• Query failures do not silently produce current-only output.
• The process returns a non-zero status on unrecoverable failure.
• Existing XML output remains unchanged when --dump-history is absent.

Test coverage

Add tests covering at least:

• --xml --dump-history for one system-versioned table.
• The legacy mysqldump command name, where tested separately.
• A system-versioned table with one current row.
• A system-versioned table with several historical versions.
• Several versions with identical user-column values.
• Several versions with the same primary-key value.
• A primary-key value changed by an update.
• Explicit period columns.
• Implicit period columns.
• Visible period columns.
• Invisible period columns.
• Timestamp precision of zero.
• Fractional timestamp precision.
• Current-row infinity value.
• SQL NULL.
• Empty string.
• Literal string NULL.
• XML characters in text values: &, <, >, quotes, and apostrophes.
• Binary and BLOB values.
• Multibyte character sets.
• An empty versioned table.
• A versioned table containing only current rows.
• A table with history partitions.
• Mixed versioned and ordinary tables.
• Views and sequences in the same database.
• --no-data.
• --no-create-info.
• --where.
• --order-by-primary.
• --single-transaction.
• --quick.
• --force with one failing table.
• --all-databases.
• --databases.
• --ignore-table.
• --ignore-table-data.
• Rejection of --dump-history --as-of.
• Continued rejection of --xml --tab.
• Unsupported transaction-precise system versioning.
• Server error while executing FOR SYSTEM_TIME ALL.
• Field-count validation with implicit period columns.
• Verification that the output parses with a standard XML parser.
• Verification that no SQL SET or INSERT statements appear in XML.
• Regression comparison showing unchanged XML without --dump-history.
• Regression test for ordinary SQL-format --dump-history.

A basic regression test should perform:

CREATE TABLE t1 (

  id INT PRIMARY KEY,

  value VARCHAR(100)

) WITH SYSTEM VERSIONING;

INSERT INTO t1 VALUES (1, 'first');

UPDATE t1 SET value = 'second' WHERE id = 1;

DELETE FROM t1 WHERE id = 1;

Then run:

mariadb-dump --xml --dump-history test t1

The XML must contain three row versions with their corresponding ROW START and ROW END values.

Documentation

Update the mariadb-dump documentation to:

• Remove the statement that -xml and -dump-history are incompatible.
• Document that --xml --dump-history exports current and historical row versions.
• Explain that period columns are included even when implicitly generated.
• Explain how explicit period-column names appear in XML.
• Clarify that the result is an XML export, not an executable restore script.
• Clarify that system_versioning_insert_history statements are not emitted.
• Document interaction with -where, no-data, no-create-info, and -single-transaction.
• Document the existing restriction for transaction-precise history.
• Include an example XML history dump.