Re: [Maria-developers] ef2519fee4e: MDEV-16546 System versioning setting to allow history modification

newer
[Maria-developers] Tips on...

older
[Maria-developers] Request for...

Aleksey Midenkov

7 May 2019 7 May '19

8:34 a.m.

Hello, Sergei! On Fri, May 3, 2019 at 8:43 PM Sergei Golubchik wrote:

...

Hi, Aleksey!

On May 03, Aleksey Midenkov wrote:

...
revision-id: ef2519fee4e (versioning-1.0.5-17-gef2519fee4e) parent(s): 56145be2951 author: Aleksey Midenkov committer: Aleksey Midenkov timestamp: 2018-06-28 13:42:09 +0300 message:

MDEV-16546 System versioning setting to allow history modification

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).

I thought more about this idea. We don't really want to have the history editable, do we?

Well, I'm thinking about rollback table data to specific point in time. That could be a useful feature.

...

But it's needed for replication, to keep the master and slave identical. That's what secure_timestamp is for.

The idea was that this new variable, system_versioning_modify_history, will be just a convenience feature, it will not allow history editing any more than one can do without it.

But now I suspect that even with secure_timestamp=NO one cannot truly edit history. One can only insert new rows with arbitrary timestamps. For example, to insert a row with row_start=1000 and row_end=2000, one needs to do (if secure_timestamp=NO):

set timestamp=1000; insert row; set timestamp=2000; delete row;

But I don't see how one can update or delete a history row with secure_timestamp=NO.

Now, with a SUPER privilege and secure_timestamp=NO or SUPER, one can use the BINLOG command and truly edit the history arbitrarily, by faking row events.

I don't really get it why this is so important: since there is some limitation by configuration and privilege, we are just fine. Everything can be changed at filesystem level after all.

...

The conclusion, I believe, is that system_versioning_modify_history should allow INSERTs when secure_timestamp=NO, and it should allow UPDATE/DELETE only for a SUPER user when secure_timestamp=NO or SUPER.

I don't see a reason to argue on that. The only thing that is not clear, why we don't allow INSERTs when secure_timestamp=SUPER?

...

The second thing I don't like at all, is when a table is created like

CREATE TABLE t1 (a int) WITH SYSTEM VERSIONING

with row_start/row_end implicit. You don't have it in the test, but anyway one should be able to load history into such a table, while the table does not have row_start and row_end columns. From the user point of view these columns don't exist, they're pseudo-columns, like ROWID. They just cannot be insertable-into, conceptually. But a user will want to restore the history, right? I don't have a solution for this yet :( Any ideas?

We don't have to follow the conception if it doesn't help us. Since we have physical row_start/row_end, we don't have to pretend they don't exist. Who will win from that?

...

See below a couple of minor comments about the patch itself.

...

These are going to be fixed.

...

Regards, Sergei Chief Architect MariaDB and security@mariadb.org

-- All the best, Aleksey Midenkov @midenok

Show replies by thread

Sergei Golubchik

22 Sep 22 Sep

2:31 p.m.

New subject: [Maria-developers] ef2519fee4e: MDEV-16546 System versioning setting to allow history modification

Hi, Aleksey! Wow, this comes so late, sorry! :( But it's fixVersion=10.6 and on the top my list, so here you are. On May 07, Aleksey Midenkov wrote:

...

...
The conclusion, I believe, is that system_versioning_modify_history should allow INSERTs when secure_timestamp=NO, and it should allow UPDATE/DELETE only for a SUPER user when secure_timestamp=NO or SUPER.

I don't see a reason to argue on that. The only thing that is not clear, why we don't allow INSERTs when secure_timestamp=SUPER?

Sure, I meant back then that INSERT/UPDATE/DELETE would be allowed when secure_timestamp=SUPER. But now I think we shouldn't really allow UPDATE/DELETE at all. I wrote earlier that "Now, with a SUPER privilege and secure_timestamp=NO or SUPER, one can use the BINLOG command and truly edit the history arbitrarily, by faking row events." Which is true, but we want to change it. In https://jira.mariadb.org/browse/MDEV-18432. So, it's looks reasonable to allow only INSERTs - they can fake new history, which is needed for mysqldump, but at least there will be no command to selectively delete or modify history.

...

...
The second thing I don't like at all, is when a table is created like

CREATE TABLE t1 (a int) WITH SYSTEM VERSIONING

with row_start/row_end implicit. You don't have it in the test, but anyway one should be able to load history into such a table, while the table does not have row_start and row_end columns. From the user point of view these columns don't exist, they're pseudo-columns, like ROWID. They just cannot be insertable-into, conceptually. But a user will want to restore the history, right? I don't have a solution for this yet :( Any ideas?

We don't have to follow the conception if it doesn't help us. Since we have physical row_start/row_end, we don't have to pretend they don't exist. Who will win from that?

Yes, you're right. Then for the purpose of mysqldump they should be insertable-into. And mysqldump should only care to list them explicitly in the column list, like with any other insivible columns. This looks easy. Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org

Aleksey Midenkov

24 Nov 24 Nov

11:40 a.m.

New subject: [Maria-developers] ef2519fee4e: MDEV-16546 System versioning setting to allow history modification

Hi Sergei! On Tue, Sep 22, 2020 at 8:31 PM Sergei Golubchik wrote:

...

Hi, Aleksey!

Wow, this comes so late, sorry! :(

But it's fixVersion=10.6 and on the top my list, so here you are.

On May 07, Aleksey Midenkov wrote:

...
...
The conclusion, I believe, is that system_versioning_modify_history should allow INSERTs when secure_timestamp=NO, and it should allow UPDATE/DELETE only for a SUPER user when secure_timestamp=NO or SUPER.

I don't see a reason to argue on that. The only thing that is not clear, why we don't allow INSERTs when secure_timestamp=SUPER?

Sure, I meant back then that INSERT/UPDATE/DELETE would be allowed when secure_timestamp=SUPER.

But now I think we shouldn't really allow UPDATE/DELETE at all.

I wrote earlier that "Now, with a SUPER privilege and secure_timestamp=NO or SUPER, one can use the BINLOG command and truly edit the history arbitrarily, by faking row events."

Which is true, but we want to change it. In https://jira.mariadb.org/browse/MDEV-18432.

So, it's looks reasonable to allow only INSERTs - they can fake new history, which is needed for mysqldump, but at least there will be no command to selectively delete or modify history.

...
...
The second thing I don't like at all, is when a table is created like

CREATE TABLE t1 (a int) WITH SYSTEM VERSIONING

with row_start/row_end implicit. You don't have it in the test, but anyway one should be able to load history into such a table, while the table does not have row_start and row_end columns. From the user point of view these columns don't exist, they're pseudo-columns, like ROWID. They just cannot be insertable-into, conceptually. But a user will want to restore the history, right? I don't have a solution for this yet :( Any ideas?

We don't have to follow the conception if it doesn't help us. Since we have physical row_start/row_end, we don't have to pretend they don't exist. Who will win from that?

Yes, you're right.

Then for the purpose of mysqldump they should be insertable-into. And mysqldump should only care to list them explicitly in the column list, like with any other insivible columns. This looks easy.

And mysqldump should take care of setting system_versioning_insert_history sysvar. Right? Is everything else in the task description OK? 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).

...

Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org

-- All the best, Aleksey Midenkov @midenok

Sergei Golubchik

1 Dec 1 Dec

1:54 p.m.

New subject: [Maria-developers] ef2519fee4e: MDEV-16546 System versioning setting to allow history modification

Hi, Aleksey! On Nov 24, Aleksey Midenkov wrote:

...

And mysqldump should take care of setting system_versioning_insert_history sysvar. Right? Is everything else in the task description OK?

Right.

...

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).

Yes. It is correct, but could be expressed simpler: when @@system_versioning_insert_history=TRUE, one can directly insert into row_start/row_end columns iff one can set @@timestamp variable. In other words, if one can create an arbitrary history by manipulating @@timestamp variable, @@system_versioning_insert_history allows to do it more conveniently. But if one cannot - he cannot. Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org

Aleksey Midenkov

25 Feb 25 Feb

7:09 a.m.

New subject: [Maria-developers] ef2519fee4e: MDEV-16546 System versioning setting to allow history modification

Hi, Sergei! On Tue, Dec 1, 2020 at 7:54 PM Sergei Golubchik wrote:

...

Hi, Aleksey!

On Nov 24, Aleksey Midenkov wrote:

...
And mysqldump should take care of setting system_versioning_insert_history sysvar. Right? Is everything else in the task description OK?

Right.

...
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).

Yes. It is correct, but could be expressed simpler:

when @@system_versioning_insert_history=TRUE, one can directly insert into row_start/row_end columns iff one can set @@timestamp variable.

In other words, if one can create an arbitrary history by manipulating @@timestamp variable, @@system_versioning_insert_history allows to do it more conveniently. But if one cannot - he cannot.

Then do we need additional setting @@system_versioning_insert_history? Iff one can manipulate history via @@timestamp variable let him set row_start/row_end columns.

...

Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org

-- All the best, Aleksey Midenkov @midenok

Sergei Golubchik

5:10 p.m.

New subject: [Maria-developers] ef2519fee4e: MDEV-16546 System versioning setting to allow history modification

Hi, Aleksey! On Feb 25, Aleksey Midenkov wrote:

...

...
On Nov 24, Aleksey Midenkov wrote:

...
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).

Yes. It is correct, but could be expressed simpler:

when @@system_versioning_insert_history=TRUE, one can directly insert into row_start/row_end columns iff one can set @@timestamp variable.

In other words, if one can create an arbitrary history by manipulating @@timestamp variable, @@system_versioning_insert_history allows to do it more conveniently. But if one cannot - he cannot.

Then do we need additional setting @@system_versioning_insert_history? Iff one can manipulate history via @@timestamp variable let him set row_start/row_end columns.

Sure, that's possible. I just thought @@system_versioning_insert_history could be like an extra safety (not security) measure. To prevent history from being modified unintentionally. Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org

Aleksey Midenkov

26 Feb 26 Feb

7:02 a.m.

New subject: [Maria-developers] ef2519fee4e: MDEV-16546 System versioning setting to allow history modification

Sergei, On Thu, Feb 25, 2021 at 11:09 PM Sergei Golubchik wrote:

...

Hi, Aleksey!

On Feb 25, Aleksey Midenkov wrote:

...
...
On Nov 24, Aleksey Midenkov wrote:

...
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).

Yes. It is correct, but could be expressed simpler:

when @@system_versioning_insert_history=TRUE, one can directly insert into row_start/row_end columns iff one can set @@timestamp variable.

In other words, if one can create an arbitrary history by manipulating @@timestamp variable, @@system_versioning_insert_history allows to do it more conveniently. But if one cannot - he cannot.

Then do we need additional setting @@system_versioning_insert_history? Iff one can manipulate history via @@timestamp variable let him set row_start/row_end columns.

Sure, that's possible.

I just thought @@system_versioning_insert_history could be like an extra safety (not security) measure. To prevent history from being modified unintentionally.

Well, unless one specified row_start/row_end explicitly he is safe. But, since we need to specify implicit system fields we cannot avoid adding one more session variable. In my current iteration I made @@force_fields_visible which is more straightforward in what it does: --- a/sql/table.cc +++ b/sql/table.cc @@ -4024,6 +4024,9 @@ enum open_frm_error open_table_from_share(THD *thd, TABLE_SHARE *share, { if (!((*field_ptr)= share->field[i]->clone(&outparam->mem_root, outparam))) goto err; + if (thd->variables.force_fields_visible && + (*field_ptr)->invisible <= INVISIBLE_SYSTEM) + (*field_ptr)->invisible= VISIBLE; } (*field_ptr)= 0; // End marker This variable is more powerful as it affects any SQL command and I hope this can be helpful. Please review the updated task in bb-10.6-midenok -- All the best, Aleksey Midenkov @midenok

Sergei Golubchik

5 Apr 5 Apr

2:05 p.m.

New subject: [Maria-developers] ef2519fee4e: MDEV-16546 System versioning setting to allow history modification

Hi, Aleksey! On Feb 26, Aleksey Midenkov wrote:

...

...
...
...
In other words, if one can create an arbitrary history by manipulating @@timestamp variable, @@system_versioning_insert_history allows to do it more conveniently. But if one cannot - he cannot.

Then do we need additional setting @@system_versioning_insert_history? Iff one can manipulate history via @@timestamp variable let him set row_start/row_end columns.

Sure, that's possible.

I just thought @@system_versioning_insert_history could be like an extra safety (not security) measure. To prevent history from being modified unintentionally.

Well, unless one specified row_start/row_end explicitly he is safe.

But, since we need to specify implicit system fields we cannot avoid adding one more session variable. In my current iteration I made @@force_fields_visible which is more straightforward in what it does:

I'm sorry, I don't understand. First, visibility is pretty much unrelated concept. row_start/row_end can be visible or invisible, and they can be writable or not writable - those are orthogonal concepts. And second, the name is wrong, there are no "fields" row_start and row_end unless the user creates then explicitly. They are pieces of metadata that every row has, something that Oracle, for example, calls "pseudocolumns". Something like @@system_versioning_row_start_row_end_visible would be more correct, but ugly. In fact, I'd say that @@system_versioning_insert_history was the best one. Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org

Aleksey Midenkov

6 Apr 6 Apr

7:43 a.m.

New subject: [Maria-developers] ef2519fee4e: MDEV-16546 System versioning setting to allow history modification

Hi Sergei, On Mon, Apr 5, 2021 at 8:04 PM Sergei Golubchik wrote:

...

Hi, Aleksey!

On Feb 26, Aleksey Midenkov wrote:

...
...
...
...
In other words, if one can create an arbitrary history by manipulating @@timestamp variable, @@system_versioning_insert_history allows to do it more conveniently. But if one cannot - he cannot.

Then do we need additional setting @@system_versioning_insert_history? Iff one can manipulate history via @@timestamp variable let him set row_start/row_end columns.

Sure, that's possible.

I just thought @@system_versioning_insert_history could be like an extra safety (not security) measure. To prevent history from being modified unintentionally.

Well, unless one specified row_start/row_end explicitly he is safe.

But, since we need to specify implicit system fields we cannot avoid adding one more session variable. In my current iteration I made @@force_fields_visible which is more straightforward in what it does:

I'm sorry, I don't understand.

First, visibility is pretty much unrelated concept. row_start/row_end can be visible or invisible, and they can be writable or not writable - those are orthogonal concepts.

To be able to specify them in INSERT command they must be at least user-invisible. System-invisible fields are ignored.

...

And second, the name is wrong, there are no "fields" row_start and row_end unless the user creates then explicitly. They are pieces of metadata that every row has, something that Oracle, for example, calls "pseudocolumns". Something like @@system_versioning_row_start_row_end_visible would be more correct, but ugly. In fact, I'd say that @@system_versioning_insert_history was the best one.

I think you are complicating things where complication is not needed. Pseudo- or not they are fields. Besides, the variable is not about system versioning. It can make any system-invisible fields visible.

...

Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org

-- All the best, Aleksey Midenkov @midenok

Sergei Golubchik

9:30 a.m.

New subject: [Maria-developers] ef2519fee4e: MDEV-16546 System versioning setting to allow history modification

Hi, Aleksey! On Apr 06, Aleksey Midenkov wrote:

...

...
...
But, since we need to specify implicit system fields we cannot avoid adding one more session variable. In my current iteration I made @@force_fields_visible which is more straightforward in what it does:

I'm sorry, I don't understand.

First, visibility is pretty much unrelated concept. row_start/row_end can be visible or invisible, and they can be writable or not writable - those are orthogonal concepts.

To be able to specify them in INSERT command they must be at least user-invisible. System-invisible fields are ignored.

Sure, that's what @@system_versioning_insert_history would do.

...

...
And second, the name is wrong, there are no "fields" row_start and row_end unless the user creates then explicitly. They are pieces of metadata that every row has, something that Oracle, for example, calls "pseudocolumns". Something like @@system_versioning_row_start_row_end_visible would be more correct, but ugly. In fact, I'd say that @@system_versioning_insert_history was the best one.

I think you are complicating things where complication is not needed. Pseudo- or not they are fields.

No, this is internal implementation detail that can change and it should not leak into the UI. Like, sql_select.cc has a function called sub_select(), but we never tell users that what it does is "subselect", not in the documentation, not in error messages, never. Because it doesn't (it performs one step in a nested-loop join). The fact that some internal enum value is named INVISIBLE_SYSTEM is not something that should affect the user visible behavior.

...

Besides, the variable is not about system versioning. It can make any system-invisible fields visible.

Which is incorrect. ROWNUM (MDEV-24089) is a pseudocolumn, and it cannot be "visible" in the sense of @force_fields_visible. We don't support ROWID, but if we would — it cannot be visible in the sense of @force_fields_visible either. Basically this variable can only apply to row_start/row_end pseudocolumns, despite its generic name. Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org

Aleksey Midenkov

27 Apr 27 Apr

10:07 a.m.

New subject: [Maria-developers] ef2519fee4e: MDEV-16546 System versioning setting to allow history modification

Hi Sergei! On Tue, Apr 6, 2021 at 3:29 PM Sergei Golubchik wrote:

...

Hi, Aleksey!

On Apr 06, Aleksey Midenkov wrote:

...
...
...
But, since we need to specify implicit system fields we cannot avoid adding one more session variable. In my current iteration I made @@force_fields_visible which is more straightforward in what it does:

I'm sorry, I don't understand.

First, visibility is pretty much unrelated concept. row_start/row_end can be visible or invisible, and they can be writable or not writable - those are orthogonal concepts.

To be able to specify them in INSERT command they must be at least user-invisible. System-invisible fields are ignored.

Sure, that's what @@system_versioning_insert_history would do.

That's not about permission of inserting history which can be controlled by @@secure_timestamp setting. But rather that's about allowing to put system-invisible fields into INSERT command. You can suggest a better name for it.

...

...
...
And second, the name is wrong, there are no "fields" row_start and row_end unless the user creates then explicitly. They are pieces of metadata that every row has, something that Oracle, for example, calls "pseudocolumns". Something like @@system_versioning_row_start_row_end_visible would be more correct, but ugly. In fact, I'd say that @@system_versioning_insert_history was the best one.

I think you are complicating things where complication is not needed. Pseudo- or not they are fields.

No, this is internal implementation detail that can change and it should not leak into the UI. Like, sql_select.cc has a function called sub_select(), but we never tell users that what it does is "subselect", not in the documentation, not in error messages, never. Because it doesn't (it performs one step in a nested-loop join).

Every concept should have a proper application. I don't think "double design" in hiding system versioning fields adds any value. I can not be sure about "subselect" design, but I suspect it will never be replaced by anything else. In any case each and every solution should be judged by whether it adds usability (i.e. ease of use) or not. I have a suspicion that the current design of hidden row_start/row_end adds more obstacles to a user than helps him.

...

The fact that some internal enum value is named INVISIBLE_SYSTEM is not something that should affect the user visible behavior.

...
Besides, the variable is not about system versioning. It can make any system-invisible fields visible.

Which is incorrect. ROWNUM (MDEV-24089) is a pseudocolumn, and it cannot be "visible" in the sense of @force_fields_visible. We don't support ROWID, but if we would — it cannot be visible in the sense of @force_fields_visible either. Basically this variable can only apply to row_start/row_end pseudocolumns, despite its generic name.

That is just the subject for a new feature request, isn't it? In any case, please suggest a different name. I can think of @@system_versioning_show_row_timestamps but this looks a bit long to me.

...

Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org

-- All the best, Aleksey Midenkov @midenok

Aleksey Midenkov

10:15 a.m.

New subject: [Maria-developers] ef2519fee4e: MDEV-16546 System versioning setting to allow history modification

Sergei, sorry, I forgot to add that implementation with @@force_fields_visible is cleaner and shorter. Also that helps to deprecate sysvers_show and MDEV-16587 which I would greatly appreciate. On Tue, Apr 27, 2021 at 4:07 PM Aleksey Midenkov wrote:

...

Hi Sergei!

On Tue, Apr 6, 2021 at 3:29 PM Sergei Golubchik wrote:

...
Hi, Aleksey!

On Apr 06, Aleksey Midenkov wrote:

...
...
...
But, since we need to specify implicit system fields we cannot avoid adding one more session variable. In my current iteration I made @@force_fields_visible which is more straightforward in what it does:

I'm sorry, I don't understand.

First, visibility is pretty much unrelated concept. row_start/row_end can be visible or invisible, and they can be writable or not writable - those are orthogonal concepts.

To be able to specify them in INSERT command they must be at least user-invisible. System-invisible fields are ignored.

Sure, that's what @@system_versioning_insert_history would do.

That's not about permission of inserting history which can be controlled by @@secure_timestamp setting. But rather that's about allowing to put system-invisible fields into INSERT command. You can suggest a better name for it.

...
...
...
And second, the name is wrong, there are no "fields" row_start and row_end unless the user creates then explicitly. They are pieces of metadata that every row has, something that Oracle, for example, calls "pseudocolumns". Something like @@system_versioning_row_start_row_end_visible would be more correct, but ugly. In fact, I'd say that @@system_versioning_insert_history was the best one.

I think you are complicating things where complication is not needed. Pseudo- or not they are fields.

No, this is internal implementation detail that can change and it should not leak into the UI. Like, sql_select.cc has a function called sub_select(), but we never tell users that what it does is "subselect", not in the documentation, not in error messages, never. Because it doesn't (it performs one step in a nested-loop join).

Every concept should have a proper application. I don't think "double design" in hiding system versioning fields adds any value. I can not be sure about "subselect" design, but I suspect it will never be replaced by anything else. In any case each and every solution should be judged by whether it adds usability (i.e. ease of use) or not. I have a suspicion that the current design of hidden row_start/row_end adds more obstacles to a user than helps him.

...
The fact that some internal enum value is named INVISIBLE_SYSTEM is not something that should affect the user visible behavior.

...
Besides, the variable is not about system versioning. It can make any system-invisible fields visible.

Which is incorrect. ROWNUM (MDEV-24089) is a pseudocolumn, and it cannot be "visible" in the sense of @force_fields_visible. We don't support ROWID, but if we would — it cannot be visible in the sense of @force_fields_visible either. Basically this variable can only apply to row_start/row_end pseudocolumns, despite its generic name.

That is just the subject for a new feature request, isn't it? In any case, please suggest a different name. I can think of @@system_versioning_show_row_timestamps but this looks a bit long to me.

...
Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org

-- All the best,

Aleksey Midenkov @midenok

-- All the best, Aleksey Midenkov @midenok

Sergei Golubchik

27 May 27 May

6:32 a.m.

New subject: [Maria-developers] ef2519fee4e: MDEV-16546 System versioning setting to allow history modification

Hi, Aleksey! On Apr 27, Aleksey Midenkov wrote:

...

Sergei,

sorry, I forgot to add that implementation with @@force_fields_visible is cleaner and shorter. Also that helps to deprecate sysvers_show and MDEV-16587 which I would greatly appreciate.

"sysvers_show" is just a debugging hack. It should not leak into the UI, because users don't give a damn about how we debug. We can redo our debugging hacks and hooks anytime and nobody will be affected. In fact, this is precisely, why you can even suggest to replace this "sysvers_show". Because it's an internal debugging tool, if it were a user visible variable, it would've been here to stay. So, let's keep internal debugging hooks, indeed, internal. Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org

Sergei Golubchik

6:42 a.m.

New subject: [Maria-developers] ef2519fee4e: MDEV-16546 System versioning setting to allow history modification

Hi, Aleksey! On Apr 27, Aleksey Midenkov wrote:

...

...
On Apr 06, Aleksey Midenkov wrote:

...

...
...
To be able to specify them in INSERT command they must be at least user-invisible. System-invisible fields are ignored.

Sure, that's what @@system_versioning_insert_history would do.

That's not about permission of inserting history which can be controlled by @@secure_timestamp setting. But rather that's about allowing to put system-invisible fields into INSERT command. You can suggest a better name for it.

yes, @@system_versioning_insert_history is not @@system_versioning_allow_permissisions_to_insert_history. So it's not about permissions, it's about "insert history [rows]" - with everything that's needed.

...

...
No, this is internal implementation detail that can change and it should not leak into the UI. Like, sql_select.cc has a function called sub_select(), but we never tell users that what it does is "subselect", not in the documentation, not in error messages, never. Because it doesn't (it performs one step in a nested-loop join).

Every concept should have a proper application. I don't think "double design" in hiding system versioning fields adds any value. I can not be sure about "subselect" design, but I suspect it will never be replaced by anything else. In any case each and every solution should be judged by whether it adds usability (i.e. ease of use) or not.

Yes, and the intended and documented behavior, that you've implemented, is - either a user specifies row start/end fields explicitly, as the standard requires, and they exist as normal fields - or the user doesn't, and they don't. The internal implementation specifics don't necessarily have to leak into UI. For example, SEQUENCE objects - they are separate objects in the standard and mostly behave as such in MariaDB too. But internally they're implemented as some special kind of a table. Stored routines are also a completely separate kind of objects. Despite the fact that we store them as rows in mysql.proc table. So, if a user hasn't created row start/end columns - there are no row start/end columns. The server stores this information _somewhere_ and makes it available in SELECT queries as pseudocolumns. With @@system_versioning_insert_history these pseudocolumns are allowed in INSERT too. Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org

Aleksey Midenkov

6:59 a.m.

New subject: [Maria-developers] ef2519fee4e: MDEV-16546 System versioning setting to allow history modification

Hi, Sergei! That "double standard" is an obstacle for users to achieve maximum potential of software. Advanced users must know how internals work and because this is not documented it is frequently hard work. I'm just telling the downsides of the double concept. I know there are upsides of course: less documentation, smooth learning curve. But now and then developers make the dumbest from a user limiting his possibilities to a real frustration. I just want you to see my point and I feel like this happens every time with every software. On Thu, May 27, 2021 at 12:41 PM Sergei Golubchik wrote:

...

Hi, Aleksey!

On Apr 27, Aleksey Midenkov wrote:

...
...
On Apr 06, Aleksey Midenkov wrote:

...
...
...
To be able to specify them in INSERT command they must be at least user-invisible. System-invisible fields are ignored.

Sure, that's what @@system_versioning_insert_history would do.

That's not about permission of inserting history which can be controlled by @@secure_timestamp setting. But rather that's about allowing to put system-invisible fields into INSERT command. You can suggest a better name for it.

yes, @@system_versioning_insert_history is not @@system_versioning_allow_permissisions_to_insert_history. So it's not about permissions, it's about "insert history [rows]" - with everything that's needed.

...
...
No, this is internal implementation detail that can change and it should not leak into the UI. Like, sql_select.cc has a function called sub_select(), but we never tell users that what it does is "subselect", not in the documentation, not in error messages, never. Because it doesn't (it performs one step in a nested-loop join).

Every concept should have a proper application. I don't think "double design" in hiding system versioning fields adds any value. I can not be sure about "subselect" design, but I suspect it will never be replaced by anything else. In any case each and every solution should be judged by whether it adds usability (i.e. ease of use) or not.

Yes, and the intended and documented behavior, that you've implemented, is - either a user specifies row start/end fields explicitly, as the standard requires, and they exist as normal fields - or the user doesn't, and they don't.

The internal implementation specifics don't necessarily have to leak into UI. For example, SEQUENCE objects - they are separate objects in the standard and mostly behave as such in MariaDB too. But internally they're implemented as some special kind of a table.

Stored routines are also a completely separate kind of objects. Despite the fact that we store them as rows in mysql.proc table.

So, if a user hasn't created row start/end columns - there are no row start/end columns. The server stores this information _somewhere_ and makes it available in SELECT queries as pseudocolumns.

With @@system_versioning_insert_history these pseudocolumns are allowed in INSERT too.

Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org

-- All the best, Aleksey Midenkov @midenok

Sergei Golubchik

8 Jun 8 Jun

6:14 a.m.

New subject: [Maria-developers] ef2519fee4e: MDEV-16546 System versioning setting to allow history modification

Hi, Aleksey! Yes, I see your point. It's always a tradeoff. On May 27, Aleksey Midenkov wrote:

...

Hi, Sergei!

That "double standard" is an obstacle for users to achieve maximum potential of software. Advanced users must know how internals work and because this is not documented it is frequently hard work. I'm just telling the downsides of the double concept. I know there are upsides of course: less documentation, smooth learning curve. But now and then developers make the dumbest from a user limiting his possibilities to a real frustration. I just want you to see my point and I feel like this happens every time with every software.

Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org

1145

Age (days ago)

1908

Last active (days ago)

List overview

Download

15 comments

2 participants

participants (2)

Aleksey Midenkov
Sergei Golubchik

Re: [Maria-developers] ef2519fee4e: MDEV-16546 System versioning setting to allow history modification

tags

participants (2)