Documented at https://mariadb.com/kb/en/library/system-versioned-tables/#limitations - can close this report if this is sufficient, or reassign to look at the primary issue.

Now when MDEV-15923 is pushed, there are two ways to fix this.

• dump the table as a set of insert/update/delete statements, setting @@timestamp between them to recreate the correct history.
• dump the table as a sequence of BINLOG statements, Write_row events that can insert directly into versioning fields.

The second is simpler to implement and will create smaller dumps. The former will create huge but more readable dumps.

Perhaps, doing BINLOG statements with decoded INSERT statements in comments would be the best combination?

The issue type is Task (if it influences anything).

Data is dumped as normal INSERT statements after allowing history modification (MDEV-16546).

Task progress

--xml option should be supported as well. This imposes following requirements:

1. --xml option produces history. System fields contain Extra="ROW START/END GENERATED" option. If system fields are system-hidden they are included in <table_structure> anyway with Extra="SYSTEM INVISIBLE ROW START/END GENERATED" option. <table_structure> includes System_versioning="Y" option.

Example:

<?xml version="1.0"?>

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

<database name="test">

        <table_structure name="t1" System_versioning="Y">

                <field Field="x" Type="int(11)" Null="YES" Key="" Default="NULL" Extra="" Comment="" />

                <field Field="row_start" Type="timestamp(6)" Null="NO" Key="" Extra="SYSTEM INVISIBLE ROW START GENERATED" Comment="" />

                <field Field="row_end" Type="timestamp(6)" Null="NO" Key="" Extra="SYSTEM INVISIBLE ROW END GENERATED" Comment="" />

                <options Name="t1" Engine="MyISAM" Version="10" Row_format="Fixed" Rows="2" Avg_row_length="19" Data_length="38" Max_data_length="5348024557502463" Index_length="1024" Data_free="0" Create_time="2018-07-03 09:30:09" Update_time="2018-07-03 09:30:09" Collation="latin1_swedish_ci" Create_options="" Comment="" Max_index_length="17179868160" Temporary="N" />

        </table_structure>

        <table_data name="t1">

        <row>   

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

                <field name="row_start">2018-07-03 09:30:09.491532</field>

                <field name="row_end">2018-07-03 09:30:09.491928</field>

        </row>

        <row>   

                <field name="x">2</field>

                <field name="row_start">2018-07-03 09:30:09.491532</field>

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

        </row>

        </table_data>

</database>

</mysqldump>

2. LOAD XML loads such data in case system_versioning_modify_history is ON. When system_versioning_modify_history is OFF LOAD XML fails to set row_start, row_end fields.

3. mysqlimport imports such tables with history.

midenok, make them into separate bugs, please. Let's first just make dump/restore to work, this is what most people need. And they would appreciate a quick fix, instead of waiting for a big solution that covers every possible use case and dump format.

serg Sure, that what I thought.

Testing

Would it be possible to get MDEV-16830 looked at? I submitted the bug last week but it remains unassigned. Although not related to this, it is the one thing that is blocking our usage of the 10.3 branch besides this issue. I'm guessing its a quick fix. Thank you, and I apologize in advance for posting this here.

Has testing finished on this?

This has been InReview for quite a while... any idea if it will make the next 10.3 release?

I'm reviewing it right now, after that we'll know

Unfortunately, it's unlikely that this feature will get into 10.3.10. It depends on MDEV-16546, and there's still some work do to on the latter one. But I'd expect it to be in 10.3.11.

This looks like its been In Review for a long time - will this make the next release?

Does it look like this will make it into 10.3.11 ?
Just trying to help a client plan.

Unfortunately, no, not in 10.3.11

We are also waiting for this feature.

Any idea of when/if this will be implemented?

Hopefully in 10.5. But generally you should be better off using mariabackup, it'll backup tables with all history just fine.

I was not aware of that... how do you restore with history records?

in bb-10.11-MDEV-16546 branch

I have no objections against pushing bb-10.11-MDEV-16546 as of 87fca0525 into 10.11 branch and releasing it with 10.11.1.

Some notes in no particular order (greenman FYI):

• the option --dump-history only applies to timestamp-based versioning; an error is thrown and mysqldump aborts if any precision-versioned tables are found upon dumping with the option;
• the abort can be avoided with --force flag, then transaction-precise tables will be dumped without history;
• combination of --dump-history and --as-of options is forbidden;
• combination of --dump-history and --replace options is forbidden (see notes in MDEV-16456, REPLACE is not supported);
• combination of --dump-history and --compact or --skip-tz-utc (in the usual INSERT format, without --tab option) is allowed, but should be used with caution due to a possible inadvertent timezone conversion;
• combination of --dump-history} and --tab is allowed, but should be used with extreme caution due to a very likely inadvertent timezone conversion. One way to avoid it would be using --skip-tz-utc, which in this case (unlike the previous one) is possibly less dangerous than the default --tz-utc;
• while re-loading history into a versioned table with limit-based partitioning, the user must be aware of MDEV-29674, all historical rows will be loaded into the first partition. It can be important if the goal of partitioning is to periodically drop oldest historical partitions, after reloading the next such drop may remove the whole previous history;
• see also notes in MDEV-16546.

Hello, any plans to support precision versioning as well (transaction based)?

I'd very like to be able to get dumps with history from my AWS RDS instance, but it looks like it just not possible for now.
I cannot get physical dump using `mariabackup` since RDS is a managed service and I don't have access to it's disk.
And `mariadb-dump` isn't able to capture and recreate tables with precision versioning (which btw isn't mentioned on it's documentation page).

rembrandt I wouldn't recommend precise versioning until MDEV-16226 as it has many deficiencies/bugs. Please watch that ticket and add vote as this can influence task priority.