Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-16546

System versioning setting to allow history modification

Details

    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

          Task - A task that needs to be done. MDEV-16029 mysqldump: dump and restore historical data

          • Major - Major loss of function.
          • Closed
          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29674 History modification inserts records into a wrong partition without warning

          • Major - Major loss of function.
          • Stalled

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29721 Inconsistency upon inserting history with visible system versioning columns

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29722 History modification requires specifying values for both period columns

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29732 mysqlbinlog produces syntactically incorrect output with system_versioning_insert_history

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29738 REPLACE under system_versioning_insert_history allows to change existing historical records

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29741 SHOW BINLOG EVENTS shows garbage with system_versioning_insert_history=on

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29750 Triggers can modify history

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29805 Attempt to insert into system versioning columns on old server may make slave data diverge

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29812 Confusing behavior upon ODKU trying to insert/modify history

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29813 REPLACE/IGNORE does not work with historical records in InnoDB

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29830 Assertion `table->versioned()' in THD::vers_insert_history_fast

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29837 Column privileges prevent from direct history insert but not from timestamp tampering

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Open
          duplicates

          Task - A task that needs to be done. MDEV-16734 LOAD DATA and system_versioning_modify_history

          • Major - Major loss of function.
          • Closed
          includes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-16587 Vers: SQL sysvers_show SELECT fix

          • Major - Major loss of function.
          • Closed
          is blocked by

          Technical task - A technical task. MDEV-18727 System Versioning: optimize DML operation

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29833 CREATE ... SELECT system_versioned_table causes invalid defaults

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          is part of

          Task - A task that needs to be done. MDEV-29547 prepare 10.11.0 preview releases

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-22413 Server hangs upon UPDATE/DELETE on a view reading from versioned partitioned table

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29730 mysqldump --dump-history creates broken dump if there are precision-versioned tables

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29736 mysqldump sets system_versioning_insert_history=1 twice and doesn't restore previous value

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29737 mysqldump doesn't check for --tz-utc when it accepts --dump-history

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          links to

          Web Link End user hitting problem in dba.stackexchange.com

          Activity

            Ascending order - Click to sort in descending order
            midenok Aleksey Midenkov created issue -
            midenok Aleksey Midenkov made changes -
            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).

            midenok Aleksey Midenkov made changes -
            midenok Aleksey Midenkov made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            midenok Aleksey Midenkov made changes -
            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).

            midenok Aleksey Midenkov made changes -
            Attachment google-chrome.desktop [ 45812 ]
            midenok Aleksey Midenkov made changes -
            Assignee Aleksey Midenkov [ midenok ] Sergei Golubchik [ serg ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 10.3 [ 22126 ]
            midenok Aleksey Midenkov made changes -
            serg Sergei Golubchik made changes -
            Attachment google-chrome.desktop [ 45812 ]
            serg Sergei Golubchik made changes -
            danblack Daniel Black made changes -
            nikitamalyavin Nikita Malyavin made changes -
            serg Sergei Golubchik made changes -
            Fix Version/s 10.4 [ 22408 ]
            midenok Aleksey Midenkov made changes -
            Fix Version/s 10.3 [ 22126 ]
            midenok Aleksey Midenkov made changes -
            midenok Aleksey Midenkov made changes -
            Comment [ Updated task is in https://github.com/MariaDB/server/pull/1289 ]
            serg Sergei Golubchik made changes -
            midenok Aleksey Midenkov made changes -
            midenok Aleksey Midenkov made changes -
            serg Sergei Golubchik made changes -
            serg Sergei Golubchik made changes -
            serg Sergei Golubchik made changes -
            Fix Version/s 10.5 [ 23123 ]
            Fix Version/s 10.4 [ 22408 ]
            julien.fritsch Julien Fritsch made changes -
            Priority Major [ 3 ] Critical [ 2 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 10.5 [ 23123 ]
            midenok Aleksey Midenkov made changes -
            julien.fritsch Julien Fritsch made changes -
            julien.fritsch Julien Fritsch made changes -
            serg Sergei Golubchik made changes -
            Fix Version/s 10.6 [ 24028 ]
            serg Sergei Golubchik made changes -
            Rank Ranked higher
            serg Sergei Golubchik made changes -
            Assignee Sergei Golubchik [ serg ] Aleksey Midenkov [ midenok ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            midenok Aleksey Midenkov made changes -
            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).

            midenok Aleksey Midenkov made changes -
            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).

            midenok Aleksey Midenkov made changes -
            Status Stalled [ 10000 ] In Progress [ 3 ]
            midenok Aleksey Midenkov made changes -
            midenok Aleksey Midenkov made changes -
            midenok Aleksey Midenkov made changes -
            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.
            midenok Aleksey Midenkov made changes -
            Assignee Aleksey Midenkov [ midenok ] Sergei Golubchik [ serg ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            midenok Aleksey Midenkov made changes -
            serg Sergei Golubchik made changes -
            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.
            serg Sergei Golubchik made changes -
            Assignee Sergei Golubchik [ serg ] Aleksey Midenkov [ midenok ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            ralf.gebhardt Ralf Gebhardt made changes -
            Fix Version/s 10.6 [ 24028 ]
            midenok Aleksey Midenkov made changes -
            Fix Version/s 10.6 [ 24028 ]
            midenok Aleksey Midenkov made changes -
            Status Stalled [ 10000 ] In Progress [ 3 ]
            midenok Aleksey Midenkov made changes -
            Fix Version/s 10.7 [ 24805 ]
            Fix Version/s 10.6 [ 24028 ]
            midenok Aleksey Midenkov made changes -
            Assignee Aleksey Midenkov [ midenok ] Sergei Golubchik [ serg ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            midenok Aleksey Midenkov made changes -
            Comment [ A comment with security level 'Developers' was removed. ]
            serg Sergei Golubchik made changes -
            Priority Critical [ 2 ] Major [ 3 ]
            julien.fritsch Julien Fritsch made changes -
            Priority Major [ 3 ] Critical [ 2 ]
            serg Sergei Golubchik made changes -
            Priority Critical [ 2 ] Major [ 3 ]
            ralf.gebhardt Ralf Gebhardt made changes -
            Fix Version/s 10.8 [ 26121 ]
            Fix Version/s 10.7 [ 24805 ]
            serg Sergei Golubchik made changes -
            Priority Major [ 3 ] Critical [ 2 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 10.9 [ 26905 ]
            Fix Version/s 10.8 [ 26121 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 87977 ] MariaDB v4 [ 131768 ]
            serg Sergei Golubchik made changes -
            Assignee Sergei Golubchik [ serg ] Aleksey Midenkov [ midenok ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 10.10 [ 27530 ]
            Fix Version/s 10.9 [ 26905 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 10.11 [ 27614 ]
            Fix Version/s 10.10 [ 27530 ]
            midenok Aleksey Midenkov made changes -
            Status Stalled [ 10000 ] In Progress [ 3 ]
            midenok Aleksey Midenkov made changes -
            Assignee Aleksey Midenkov [ midenok ] Sergei Golubchik [ serg ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            midenok Aleksey Midenkov made changes -
            Comment [ A comment with security level 'Developers' was removed. ]
            serg Sergei Golubchik made changes -
            Assignee Sergei Golubchik [ serg ] Aleksey Midenkov [ midenok ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            midenok Aleksey Midenkov made changes -
            Status Stalled [ 10000 ] In Progress [ 3 ]
            midenok Aleksey Midenkov made changes -
            Assignee Aleksey Midenkov [ midenok ] Sergei Golubchik [ serg ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            serg Sergei Golubchik made changes -
            Assignee Sergei Golubchik [ serg ] Aleksey Midenkov [ midenok ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            midenok Aleksey Midenkov made changes -
            Status Stalled [ 10000 ] In Progress [ 3 ]
            midenok Aleksey Midenkov made changes -
            Assignee Aleksey Midenkov [ midenok ] Sergei Golubchik [ serg ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            serg Sergei Golubchik made changes -
            Assignee Sergei Golubchik [ serg ] Aleksey Midenkov [ midenok ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            serg Sergei Golubchik made changes -
            Assignee Aleksey Midenkov [ midenok ] Sergei Golubchik [ serg ]
            serg Sergei Golubchik made changes -
            Status Stalled [ 10000 ] In Review [ 10002 ]
            serg Sergei Golubchik made changes -
            Status In Review [ 10002 ] In Testing [ 10301 ]
            serg Sergei Golubchik made changes -
            Assignee Sergei Golubchik [ serg ] Elena Stepanova [ elenst ]
            serg Sergei Golubchik made changes -
            elenst Elena Stepanova made changes -
            serg Sergei Golubchik made changes -
            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 (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
            {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.
            serg Sergei Golubchik made changes -
            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
            {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 (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
            {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.
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            serg Sergei Golubchik made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            Assignee Elena Stepanova [ elenst ] Sergei Golubchik [ serg ]
            Status In Testing [ 10301 ] Stalled [ 10000 ]
            serg Sergei Golubchik made changes -
            serg Sergei Golubchik made changes -
            Fix Version/s 10.11.1 [ 28454 ]
            Fix Version/s 10.11 [ 27614 ]
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            ralf.gebhardt Ralf Gebhardt made changes -
            Labels Preview_10.11
            mariadb-jira-automation Jira Automation (IT) made changes -
            Zendesk Related Tickets 139667 114975 111284

            People

              serg Sergei Golubchik
              midenok Aleksey Midenkov
              Votes:
              8 Vote for this issue
              Watchers:
              17 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.