Details
-
Task
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
Description
Add a session server variable @@system_versioning_insert_history which allows to use ROW_START and ROW_END columns in the INSERT (unless they are normal visible (in SELECT *) fields, they have to be explicitly specified in INSERT as any invisible fields are). And if @@secure_timestamp allows the current user to modify @@timestamp then he should be able to insert directly into ROW_START/ROW_END columns.
The use case of this is to be able to dump the history with mysqldump and to load it back later (MDEV-16029). It provides a convenient way to have a row in the system versioned table with row_start=A and row_end=B. Without @@system_versioning_insert_history it can be achieved with
set @@timestamp=A; |
insert t1 values (...); |
set @@timestamp=B; |
delete from t1 where ... -- a condition to match the row that was just inserted |
So this new feature does not provide any new functionality, but allows to load the history dump much faster than with timestamp manipulations as above. In particular it does not allow to do anything that wasn't possible to do before, it requires exactly the same set of privileges as the snippet above, and is subject to the same set of restrictions.
Attachments
Issue Links
- blocks
-
-
- Closed
-
- causes
-
MDEV-29674 History modification inserts records into a wrong partition without warning
-
- Stalled
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Open
-
- duplicates
-
-
- Closed
-
- includes
-
-
- Closed
-
- is blocked by
-
MDEV-18727
System Versioning: optimize DML operation
-
- Closed
-
-
-
- Closed
-
- is part of
-
-
- Closed
-
- relates to
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
- links to
Activity
| Field | Original Value | New Value |
|---|---|---|
| Description |
1. Add SQL mode {{ALLOW_HISTORY_MODIFY}} which will allow to set values for *row_start*, *row_end* in DML operations.
2. If {{secure_timestamp}} is YES or REPLICATION, {{ALLOW_HISTORY_MODIFY}} does not have effect. If {{secure_timestamp}} is SUPER, {{ALLOW_HISTORY_MODIFY}} requires special privilege (same as for setting current timestamp). |
1. Add SQL mode {{ALLOW_HISTORY_MODIFY}} which will allow to set values for *row_start*, *row_end* in DML operations.
2. If {{secure_timestamp}} is *YES* or *REPLICATION*, {{ALLOW_HISTORY_MODIFY}} does not have effect. If {{secure_timestamp}} is *SUPER*, {{ALLOW_HISTORY_MODIFY}} requires special privilege (same as for setting current timestamp). |
| Link |
This issue blocks |
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Description |
1. Add SQL mode {{ALLOW_HISTORY_MODIFY}} which will allow to set values for *row_start*, *row_end* in DML operations.
2. If {{secure_timestamp}} is *YES* or *REPLICATION*, {{ALLOW_HISTORY_MODIFY}} does not have effect. If {{secure_timestamp}} is *SUPER*, {{ALLOW_HISTORY_MODIFY}} requires special privilege (same as for setting current timestamp). |
1. Add server variable *system_versioning_modify_history* which will allow to set values for *row_start*, *row_end* in DML operations.
2. If {{secure_timestamp}} is *YES* or *REPLICATION*, *system_versioning_modify_history* does not have effect. If {{secure_timestamp}} is *SUPER*, *system_versioning_modify_history* requires special privilege (same as for setting current timestamp). |
| Attachment | google-chrome.desktop [ 45812 ] |
| Assignee | Aleksey Midenkov [ midenok ] | Sergei Golubchik [ serg ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Fix Version/s | 10.3 [ 22126 ] |
| Link |
This issue relates to |
| Attachment | google-chrome.desktop [ 45812 ] |
| Link |
This issue blocks |
| Remote Link | This issue links to "End user hitting problem in dba.stackexchange.com (Web Link)" [ 28701 ] |
| Link | This issue relates to MDEV-15990 [ MDEV-15990 ] |
| Fix Version/s | 10.4 [ 22408 ] |
| Fix Version/s | 10.3 [ 22126 ] |
| Link |
This issue is blocked by |
| Comment | [ Updated task is in https://github.com/MariaDB/server/pull/1289 ] |
| Link |
This issue blocks |
| Link |
This issue relates to |
| Link |
This issue relates to |
| Link | This issue blocks MDEV-15990 [ MDEV-15990 ] |
| Link | This issue relates to MDEV-15990 [ MDEV-15990 ] |
| Fix Version/s | 10.5 [ 23123 ] | |
| Fix Version/s | 10.4 [ 22408 ] |
| Priority | Major [ 3 ] | Critical [ 2 ] |
| Fix Version/s | 10.5 [ 23123 ] |
| Link |
This issue relates to |
I am very anxious to use system versioning, but I cannot make the changeover until this feature is available. This is due to the fact that I have existing history data that needs to be moved over to the new system. I am glad to see that it is In Review, and hope to see it in the next MariaDB release.
| Link |
This issue blocks |
| Link |
This issue blocks |
| Fix Version/s | 10.6 [ 24028 ] |
| Rank | Ranked higher |
| Assignee | Sergei Golubchik [ serg ] | Aleksey Midenkov [ midenok ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
I also interested in the system versioning. If I could get logical backup by mysqldump, it would be great for me.
The ticket for such a feature seems to be blocked by this ticket. There are several PRs for this JIRA issue, while they are not merged. Could anyone let me know how things are going?
| Description |
1. Add server variable *system_versioning_modify_history* which will allow to set values for *row_start*, *row_end* in DML operations.
2. If {{secure_timestamp}} is *YES* or *REPLICATION*, *system_versioning_modify_history* does not have effect. If {{secure_timestamp}} is *SUPER*, *system_versioning_modify_history* requires special privilege (same as for setting current timestamp). |
1. Add server variable *system_versioning_modify_history* which will allow INSERT history rows.
2. If {{secure_timestamp}} is *YES* or *REPLICATION*, *system_versioning_modify_history* does not have effect. If {{secure_timestamp}} is *SUPER*, *system_versioning_modify_history* requires special privilege (same as for setting current timestamp). |
| Description |
1. Add server variable *system_versioning_modify_history* which will allow INSERT history rows.
2. If {{secure_timestamp}} is *YES* or *REPLICATION*, *system_versioning_modify_history* does not have effect. If {{secure_timestamp}} is *SUPER*, *system_versioning_modify_history* requires special privilege (same as for setting current timestamp). |
1. Add server variable *system_versioning_insert_history* which will allow INSERT history rows.
2. If {{secure_timestamp}} is *YES* or *REPLICATION*, *system_versioning_insert_history* does not have effect. If {{secure_timestamp}} is *SUPER*, *system_versioning_insert_history* requires special privilege (same as for setting current timestamp). |
| Status | Stalled [ 10000 ] | In Progress [ 3 ] |
| Link |
This issue duplicates |
| Link |
This issue relates to |
| Description |
1. Add server variable *system_versioning_insert_history* which will allow INSERT history rows.
2. If {{secure_timestamp}} is *YES* or *REPLICATION*, *system_versioning_insert_history* does not have effect. If {{secure_timestamp}} is *SUPER*, *system_versioning_insert_history* requires special privilege (same as for setting current timestamp). |
1. Add server variable force_fields_visible which makes
system-invisible and user-invisible fields visible on next table open (FLUSH TABLES required for already opened tables). 2. If secure_timestamp allows to modify timestamp variable then following DML commands: INSERT, INSERT..SELECT and LOAD DATA can specify row_start and row_end system field values. |
| Assignee | Aleksey Midenkov [ midenok ] | Sergei Golubchik [ serg ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Link |
This issue includes |
| Description |
1. Add server variable force_fields_visible which makes
system-invisible and user-invisible fields visible on next table open (FLUSH TABLES required for already opened tables). 2. If secure_timestamp allows to modify timestamp variable then following DML commands: INSERT, INSERT..SELECT and LOAD DATA can specify row_start and row_end system field values. |
Add a session server variable {{@@system_versioning_insert_history}} which allows to use ROW_START and ROW_END columns in the INSERT (they behave as invisible fields, have to be explicitly specified in INSERT). And if {{@@secure_timestamp}} allows the current user to modify {{@@timestamp}} then he should be able to insert directly into ROW_START/ROW_END columns. |
| Assignee | Sergei Golubchik [ serg ] | Aleksey Midenkov [ midenok ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
If anyone can do this, I see a security risk.
Will this require the SUPER privilege?
| Fix Version/s | 10.6 [ 24028 ] |
f_razzoli, it's just a convenience feature. It doesn't allow to do anything that one wasn't allowed to do before. Say, you want to have a fake history record that tells that a row with the value V existed from timestamp A to timestamp B. You can do it as
set @@timestamp=A; |
insert into t1 values (V); |
set @@timestamp=B; |
delete from t1 where value=V; |
Note, that this is only allowed for certain values of @@secure_timestamp variable. For example, with @@secure_timestamp=YES no one can fake history.
In this issue we want to simplify the above to
set @@system_versioning_insert_history=1; |
insert t1 values (V, A, B); |
This is primarily to be able to use multi-row inserts in mysqldump. This convenience feature won't do any additional privilege checks, it will work for everyone who can change @@timestamp as above, so it'll obey @@secure_timestamp setting.
| Fix Version/s | 10.6 [ 24028 ] |
| Status | Stalled [ 10000 ] | In Progress [ 3 ] |
| Fix Version/s | 10.7 [ 24805 ] | |
| Fix Version/s | 10.6 [ 24028 ] |
| Assignee | Aleksey Midenkov [ midenok ] | Sergei Golubchik [ serg ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Comment | [ A comment with security level 'Developers' was removed. ] |
| Priority | Critical [ 2 ] | Major [ 3 ] |
| Priority | Major [ 3 ] | Critical [ 2 ] |
| Priority | Critical [ 2 ] | Major [ 3 ] |
| Fix Version/s | 10.8 [ 26121 ] | |
| Fix Version/s | 10.7 [ 24805 ] |
| Priority | Major [ 3 ] | Critical [ 2 ] |
| Fix Version/s | 10.9 [ 26905 ] | |
| Fix Version/s | 10.8 [ 26121 ] |
| Workflow | MariaDB v3 [ 87977 ] | MariaDB v4 [ 131768 ] |
| Assignee | Sergei Golubchik [ serg ] | Aleksey Midenkov [ midenok ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Fix Version/s | 10.10 [ 27530 ] | |
| Fix Version/s | 10.9 [ 26905 ] |
| Fix Version/s | 10.11 [ 27614 ] | |
| Fix Version/s | 10.10 [ 27530 ] |
| Status | Stalled [ 10000 ] | In Progress [ 3 ] |
| Assignee | Aleksey Midenkov [ midenok ] | Sergei Golubchik [ serg ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Comment | [ A comment with security level 'Developers' was removed. ] |
| Assignee | Sergei Golubchik [ serg ] | Aleksey Midenkov [ midenok ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Status | Stalled [ 10000 ] | In Progress [ 3 ] |
| Assignee | Aleksey Midenkov [ midenok ] | Sergei Golubchik [ serg ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
I've interrupted the review, as I stumbled on a bug, try this test case:
create table t1(y int primary key, |
row_start timestamp(6) as row start, |
row_end timestamp(6) as row end, |
period for system_time (row_start, row_end)) |
with system versioning; |
|
|
insert into t1 values (1, NULL, NULL); |
--error ER_WARNING_NON_DEFAULT_VALUE_FOR_GENERATED_COLUMN
|
insert into t1 values (2, '1980-01-01 00:00:00', '1980-01-01 00:00:01'); |
set @@system_versioning_insert_history= 1; |
insert into t1 values (3, '1980-01-01 00:00:00', '1980-01-01 00:00:01'); |
select * from t1 for system_time all; |
drop table t1; |
This INSERT uses a different fill_record() function and does not go into Item_field::fix_fields().
| Assignee | Sergei Golubchik [ serg ] | Aleksey Midenkov [ midenok ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
Fixed. It should not go into Item_field::fix_fields() because there is no explicit field list (so no Item_field). It uses different fill_record() for implicit field list, that's normal.
| Status | Stalled [ 10000 ] | In Progress [ 3 ] |
| Assignee | Aleksey Midenkov [ midenok ] | Sergei Golubchik [ serg ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
I wanted to split THD::vers_insert_history(Field *) into Field::vers_insert_history(THD *) and THD::vers_insert_history(). But they both cannot be inline (or it sort of hard to rule out without inline files) because Field::vers_insert_history(THD *) must call THD::vers_insert_history() but sql_class.h is not included into field.h. serg, I remember about your inline request.
midenok, you haven't fixed the issue yet. You don't have the test case for it. If you do
-insert into t3(z, row_start, row_end) values (5, '1980-01-01 00:00:00', '1980-01-01 00:00:01');
|
+insert into t3 values (5, '1980-01-01 00:00:00', '1980-01-01 00:00:01'); |
you'll see that values are ignored
| Assignee | Sergei Golubchik [ serg ] | Aleksey Midenkov [ midenok ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Assignee | Aleksey Midenkov [ midenok ] | Sergei Golubchik [ serg ] |
| Status | Stalled [ 10000 ] | In Review [ 10002 ] |
| Status | In Review [ 10002 ] | In Testing [ 10301 ] |
| Assignee | Sergei Golubchik [ serg ] | Elena Stepanova [ elenst ] |
| Link |
This issue is part of |
| Link | This issue relates to TODO-3607 [ TODO-3607 ] |
| Description | Add a session server variable {{@@system_versioning_insert_history}} which allows to use ROW_START and ROW_END columns in the INSERT (they behave as invisible fields, have to be explicitly specified in INSERT). And if {{@@secure_timestamp}} allows the current user to modify {{@@timestamp}} then he should be able to insert directly into ROW_START/ROW_END columns. |
Add a session server variable {{@@system_versioning_insert_history}} which allows to use ROW_START and ROW_END columns in the INSERT (unless they are normal visible (in {{SELECT *}}) fields, they have to be explicitly specified in INSERT as any invisible fields are). And if {{@@secure_timestamp}} allows the current user to modify {{@@timestamp}} then he should be able to insert directly into ROW_START/ROW_END columns.
The use case of this is to be able to dump the history with mysqldump and to load it back later ( {code:sql} set @@timestamp=A; insert t1 values (...); set @@timestamp=B; delete from t1 where ... -- a condition to match the row that was just inserted {code} So this new feature does not provide any new functionality, but allows to load the history dump much faster than with timestamp manipulations as above. |
| Description |
Add a session server variable {{@@system_versioning_insert_history}} which allows to use ROW_START and ROW_END columns in the INSERT (unless they are normal visible (in {{SELECT *}}) fields, they have to be explicitly specified in INSERT as any invisible fields are). And if {{@@secure_timestamp}} allows the current user to modify {{@@timestamp}} then he should be able to insert directly into ROW_START/ROW_END columns.
The use case of this is to be able to dump the history with mysqldump and to load it back later ( {code:sql} set @@timestamp=A; insert t1 values (...); set @@timestamp=B; delete from t1 where ... -- a condition to match the row that was just inserted {code} So this new feature does not provide any new functionality, but allows to load the history dump much faster than with timestamp manipulations as above. |
Add a session server variable {{@@system_versioning_insert_history}} which allows to use ROW_START and ROW_END columns in the INSERT (unless they are normal visible (in {{SELECT *}}) fields, they have to be explicitly specified in INSERT as any invisible fields are). And if {{@@secure_timestamp}} allows the current user to modify {{@@timestamp}} then he should be able to insert directly into ROW_START/ROW_END columns.
The use case of this is to be able to dump the history with mysqldump and to load it back later ( {code:sql} set @@timestamp=A; insert t1 values (...); set @@timestamp=B; delete from t1 where ... -- a condition to match the row that was just inserted {code} So this new feature does not provide any new functionality, but allows to load the history dump much faster than with timestamp manipulations as above. In particular it does not allow to do anything that wasn't possible to do before, it requires exactly the same set of privileges as the snippet above, and is subject to the same set of restrictions. |
| Link | This issue causes MDEV-29674 [ MDEV-29674 ] |
| Link |
This issue causes |
| Link |
This issue causes |
| Link |
This issue relates to |
| Link |
This issue causes |
| Link |
This issue relates to |
| Link |
This issue relates to |
| Link |
This issue relates to |
| Link |
This issue causes |
| Link |
This issue causes |
| Link |
This issue relates to |
| Link |
This issue causes |
| Link |
This issue causes |
| Link |
This issue causes |
| Link |
This issue causes |
| Link | This issue blocks MDEV-15990 [ MDEV-15990 ] |
| Link |
This issue causes |
| Link | This issue causes MDEV-29837 [ MDEV-29837 ] |
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 final implementation allows INSERT, CREATE .. INSERT, and LOAD; does not allow REPLACE, LOAD .. REPLACE, UPDATE, INSERT .. ON DUPLICATE KEY UPDATE
- in addition to sufficient permissions to change the timestamp, the user performing the operation naturally also needs standard grants for inserting into the table;
- the variable is already in the KB, but the description needs to indicate that it only applies to timestamp-based versioning;
- while inserting history into a versioned table with limit-based partitioning, the user must be aware of MDEV-29674, all injected historical rows are inserted into the first partition. It may be fixed in future;
- in general, functionality is mainly aimed for re-loading the real history by the means of mysqldump / restoration. While arbitrary history inserting also works, the effect may be not quite what the user expects to achieve.
- see also notes in
MDEV-16029.
| Assignee | Elena Stepanova [ elenst ] | Sergei Golubchik [ serg ] |
| Status | In Testing [ 10301 ] | Stalled [ 10000 ] |
| Link |
This issue is blocked by |
| Fix Version/s | 10.11.1 [ 28454 ] | |
| Fix Version/s | 10.11 [ 27614 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Stalled [ 10000 ] | Closed [ 6 ] |
| Labels | Preview_10.11 |
| Zendesk Related Tickets | 139667 114975 111284 |
no sql mode please. make it a separate sysvar.