Details
-
Task
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
Description
Concurrent threads may produce history rows with equal row_end and primary key values. Since key uniqueness is now solved by adding row_end column to all unique keys such concurrent operation may generate duplicate key errors. That would be acceptable if we skipped generating history for the identical updates but the requirement of "guaranteed history" in MDEV-23446 forbids such history skipping.
Another problem that special column can solve is to unambiguously link current row and its history rows. This is usable for systems that require "guaranteed history" concept as when you need such guaranteed history you probably want to know which row change produced it.
HISTORY_ID nullable unique column identifies history row in unique keys instead of row_end.
ROW_ID non-nullable non-unique auto-incremented column links current row and history row.
When new row is inserted its ROW_ID is assigned new value from incremented counter. HISTORY_ID is set to NULL. When row is updated its ROW_ID is copied to history row and new unique value is assigned to its HISTORY_ID.
As ROW_ID and HISTORY_ID increase data size and processing time if not significantly but noticeably, the presence of these columns should be configurable on per-table basis.
Also replication and data/dump restore may lift up additional requirements which is subject for further elaboration. Like HISTORY_ID and ROW_ID value clashes and whether they should be dumped/replicated.
Attachments
Issue Links
- relates to
-
MDEV-22540 ER_DUP_ENTRY upon REPLACE or Assertion `transactional_table || !changed || thd->transaction.stmt.modified_non_trans_table' failed
-
- Closed
-
-
-
- Closed
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Link |
This issue relates to |
| Link |
This issue relates to |
| Description |
Concurrent threads may produce history rows with equal row_end and primary key values. Since key uniqueness is now solved by adding row_end column to all unique keys such history rows generate duplicate key error. That would be acceptable when we skip generating history for the identical updates but the requirement of "guaranteed history" in Another problem that special column can solve is to unambiguously link current row and its history rows. HISTORY_ID nullable unique column identifies history row in unique keys instead of row_end. ROW_ID non-nullable non-unique auto-incremented column links current row and history row. When new row is inserted its ROW_ID is assigned new value from incremented counter. HISTORY_ID is set to NULL. When row is updated its ROW_ID is copied to history row and new unique value is assigned to its HISTORY_ID. As ROW_ID and HISTORY_ID increase data size and processing time if not significantly but noticeably, the presence of these columns should be configurable on per-table basis. Also replication and data/dump restore may lift up additional requirements which subject for further elaboration. Like HISTORY_ID and ROW_ID value clashes and whether they should be dumped/replicated. |
Concurrent threads may produce history rows with equal row_end and primary key values. Since key uniqueness is now solved by adding row_end column to all unique keys such concurrent operation may generate duplicate key errors. That would be acceptable when we skip generating history for the identical updates but the requirement of "guaranteed history" in Another problem that special column can solve is to unambiguously link current row and its history rows. HISTORY_ID nullable unique column identifies history row in unique keys instead of row_end. ROW_ID non-nullable non-unique auto-incremented column links current row and history row. When new row is inserted its ROW_ID is assigned new value from incremented counter. HISTORY_ID is set to NULL. When row is updated its ROW_ID is copied to history row and new unique value is assigned to its HISTORY_ID. As ROW_ID and HISTORY_ID increase data size and processing time if not significantly but noticeably, the presence of these columns should be configurable on per-table basis. Also replication and data/dump restore may lift up additional requirements which subject for further elaboration. Like HISTORY_ID and ROW_ID value clashes and whether they should be dumped/replicated. |
| Description |
Concurrent threads may produce history rows with equal row_end and primary key values. Since key uniqueness is now solved by adding row_end column to all unique keys such concurrent operation may generate duplicate key errors. That would be acceptable when we skip generating history for the identical updates but the requirement of "guaranteed history" in Another problem that special column can solve is to unambiguously link current row and its history rows. HISTORY_ID nullable unique column identifies history row in unique keys instead of row_end. ROW_ID non-nullable non-unique auto-incremented column links current row and history row. When new row is inserted its ROW_ID is assigned new value from incremented counter. HISTORY_ID is set to NULL. When row is updated its ROW_ID is copied to history row and new unique value is assigned to its HISTORY_ID. As ROW_ID and HISTORY_ID increase data size and processing time if not significantly but noticeably, the presence of these columns should be configurable on per-table basis. Also replication and data/dump restore may lift up additional requirements which subject for further elaboration. Like HISTORY_ID and ROW_ID value clashes and whether they should be dumped/replicated. |
Concurrent threads may produce history rows with equal row_end and primary key values. Since key uniqueness is now solved by adding row_end column to all unique keys such concurrent operation may generate duplicate key errors. That would be acceptable if we skipped generating history for the identical updates but the requirement of "guaranteed history" in Another problem that special column can solve is to unambiguously link current row and its history rows. HISTORY_ID nullable unique column identifies history row in unique keys instead of row_end. ROW_ID non-nullable non-unique auto-incremented column links current row and history row. When new row is inserted its ROW_ID is assigned new value from incremented counter. HISTORY_ID is set to NULL. When row is updated its ROW_ID is copied to history row and new unique value is assigned to its HISTORY_ID. As ROW_ID and HISTORY_ID increase data size and processing time if not significantly but noticeably, the presence of these columns should be configurable on per-table basis. Also replication and data/dump restore may lift up additional requirements which subject for further elaboration. Like HISTORY_ID and ROW_ID value clashes and whether they should be dumped/replicated. |
| Description |
Concurrent threads may produce history rows with equal row_end and primary key values. Since key uniqueness is now solved by adding row_end column to all unique keys such concurrent operation may generate duplicate key errors. That would be acceptable if we skipped generating history for the identical updates but the requirement of "guaranteed history" in Another problem that special column can solve is to unambiguously link current row and its history rows. HISTORY_ID nullable unique column identifies history row in unique keys instead of row_end. ROW_ID non-nullable non-unique auto-incremented column links current row and history row. When new row is inserted its ROW_ID is assigned new value from incremented counter. HISTORY_ID is set to NULL. When row is updated its ROW_ID is copied to history row and new unique value is assigned to its HISTORY_ID. As ROW_ID and HISTORY_ID increase data size and processing time if not significantly but noticeably, the presence of these columns should be configurable on per-table basis. Also replication and data/dump restore may lift up additional requirements which subject for further elaboration. Like HISTORY_ID and ROW_ID value clashes and whether they should be dumped/replicated. |
Concurrent threads may produce history rows with equal row_end and primary key values. Since key uniqueness is now solved by adding row_end column to all unique keys such concurrent operation may generate duplicate key errors. That would be acceptable if we skipped generating history for the identical updates but the requirement of "guaranteed history" in Another problem that special column can solve is to unambiguously link current row and its history rows. This is usable for systems that require "guaranteed history" concept as when you need such guaranteed history you probably want to know which row change produced it. HISTORY_ID nullable unique column identifies history row in unique keys instead of row_end. ROW_ID non-nullable non-unique auto-incremented column links current row and history row. When new row is inserted its ROW_ID is assigned new value from incremented counter. HISTORY_ID is set to NULL. When row is updated its ROW_ID is copied to history row and new unique value is assigned to its HISTORY_ID. As ROW_ID and HISTORY_ID increase data size and processing time if not significantly but noticeably, the presence of these columns should be configurable on per-table basis. Also replication and data/dump restore may lift up additional requirements which subject for further elaboration. Like HISTORY_ID and ROW_ID value clashes and whether they should be dumped/replicated. |
| Labels | system_versioned_tables |
| Component/s | Versioned Tables [ 14303 ] |
| Component/s | Backup [ 13902 ] | |
| Component/s | Data Manipulation - Delete [ 11300 ] | |
| Component/s | Data Manipulation - Insert [ 10101 ] | |
| Component/s | Data Manipulation - Update [ 10805 ] | |
| Component/s | Replication [ 10100 ] | |
| Component/s | Server [ 13907 ] |
| Fix Version/s | 10.7 [ 24805 ] |
| Description |
Concurrent threads may produce history rows with equal row_end and primary key values. Since key uniqueness is now solved by adding row_end column to all unique keys such concurrent operation may generate duplicate key errors. That would be acceptable if we skipped generating history for the identical updates but the requirement of "guaranteed history" in Another problem that special column can solve is to unambiguously link current row and its history rows. This is usable for systems that require "guaranteed history" concept as when you need such guaranteed history you probably want to know which row change produced it. HISTORY_ID nullable unique column identifies history row in unique keys instead of row_end. ROW_ID non-nullable non-unique auto-incremented column links current row and history row. When new row is inserted its ROW_ID is assigned new value from incremented counter. HISTORY_ID is set to NULL. When row is updated its ROW_ID is copied to history row and new unique value is assigned to its HISTORY_ID. As ROW_ID and HISTORY_ID increase data size and processing time if not significantly but noticeably, the presence of these columns should be configurable on per-table basis. Also replication and data/dump restore may lift up additional requirements which subject for further elaboration. Like HISTORY_ID and ROW_ID value clashes and whether they should be dumped/replicated. |
Concurrent threads may produce history rows with equal row_end and primary key values. Since key uniqueness is now solved by adding row_end column to all unique keys such concurrent operation may generate duplicate key errors. That would be acceptable if we skipped generating history for the identical updates but the requirement of "guaranteed history" in Another problem that special column can solve is to unambiguously link current row and its history rows. This is usable for systems that require "guaranteed history" concept as when you need such guaranteed history you probably want to know which row change produced it. HISTORY_ID nullable unique column identifies history row in unique keys instead of row_end. ROW_ID non-nullable non-unique auto-incremented column links current row and history row. When new row is inserted its ROW_ID is assigned new value from incremented counter. HISTORY_ID is set to NULL. When row is updated its ROW_ID is copied to history row and new unique value is assigned to its HISTORY_ID. As ROW_ID and HISTORY_ID increase data size and processing time if not significantly but noticeably, the presence of these columns should be configurable on per-table basis. Also replication and data/dump restore may lift up additional requirements which is subject for further elaboration. Like HISTORY_ID and ROW_ID value clashes and whether they should be dumped/replicated. |
| Fix Version/s | 10.7 [ 24805 ] |
| Component/s | Server [ 13907 ] |
| Workflow | MariaDB v3 [ 115121 ] | MariaDB v4 [ 131400 ] |