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

Auto-create history partitions for system-versioned tables

    XMLWordPrintable

Details

    Description

      Syntax change

      Keyword AUTO enables partition auto-creation.

      create table t1 (x int) with system versioning
      		 
      partition by system_time interval 1 hour auto;
      		 
       
      		 
      create table t1 (x int) with system versioning
      		 
      partition by system_time interval 1 month
      		 
      starts '2021-01-01 00:00:00' auto partitions 12;
      		 
       
      		 
      create table t1 (x int) with system versioning
      		 
      partition by system_time limit 1000 auto;

      Or with explicit partitions:

      create table t1 (x int) with system versioning
      		 
      partition by system_time interval 1 hour auto
      		 
      (partition p0 history, partition pn current);

      Description

      Before executing history-generating DML command add N history
      partitions, so that N would be sufficient for potentially generated
      history. N > 1 may be required when history is rotated by INTERVAL and
      timestamp was jumped to future further than interval value.

      If last history partition exceeds LIMIT records before DML command
      then new history partition is created and the history filling is
      switched to the new partition. Thus LIMIT does not carry strict
      limitation and the history partition size must be planned as LIMIT
      records plus average number of history one DML generates.

      Auto-creation is implemented by synchronous
      fast_alter_partition_table() call from the thread of the executed DML
      command before the command itself (by the fallback and retry mechanism
      similar to Discovery feature, see Open_table_context).

      The name for newly added partitions are generated like default
      partition names with extension of MDEV-22155 (which avoids name
      clashes by extending assignment counter to next free-enough gap).

      These DML commands trigger auto-creation:

      • DELETE (including multi-delete, excluding DELETE HISTORY)
      • UPDATE (including multi-update)
      • REPLACE (including REPLACE .. SELECT)
      • INSERT .. ON DUPLICATE KEY UPDATE
      • LOAD DATA .. REPLACE

      MDEV-23642 Locking timeout caused by auto-creation affects original DML

      The reasons for this are:

      • Do not disrupt main business process (the history is auxiliary
        service);
      • Consequences are non-fatal (history is not lost, but comes into wrong
        partition; fixed by partitioning rebuild);
      • There is more freedom for application to fail in this case or
        not: it may read warning info and find corresponding error number.
      • While non-failing command is easy to handle by an application and
        fail it, the opposite is hard to handle: there is no automatic
        actions to fix failed command and retry, DBA intervention is
        required and until then application is non-functioning.

      Attachments

        Issue Links

          blocks

          Bug - A problem which impairs or prevents the functions of the product. MDEV-23639 Auto-create does not work under LOCK TABLES or inside triggers

          • Major - Major loss of function.
          • Closed

          New Feature - A new feature of the product, which has yet to be developed. MDEV-25495 Auto-drop old history partition for system-versioned tables that use auto-creation

          • Critical - Crashes, loss of data, severe memory leak.
          • In Review
          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25477 Auto-create breaks replication when triggering event was not replicated

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27456 Assertion `!thd->is_error()' fails in vers_create_partitions upon DML with ER_UNKNOWN_PARTITION

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-28567 Assertion `0' in open_tables upon function-related operations

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29872 MSAN/Valgrind uninitialised value errors in TABLE::vers_switch_partition

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29873 MSAN uninitialized value errors in bcmp / prep_alter_part_table upon re-partitioning by system time

          • Major - Major loss of function.
          • Closed
          includes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-20068 History partition rotation is not done under LOCK TABLES

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25347 DML events for auto-partitioned tables are written into binary log twice

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-23639 Auto-create does not work under LOCK TABLES or inside triggers

          • Major - Major loss of function.
          • Closed
          relates to

          Task - A task that needs to be done. MDEV-19903 Setup default partitions for system versioning

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-21003 Per-partition INTERVAL for history partitions

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

          Task - A task that needs to be done. MDEV-21747 Refactor partition auto-creation via direct fast_alter_partition_table()

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25475 Auto-create: LIMIT partitions are not auto-created quite as documented

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25478 Auto-create: Auto partitioning is unsafe for row-based replication

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25479 Auto-create: 2nd and further executions of PS or SP fail to create partition

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25482 Auto-create: Server hangs after a failed attempt to create partition

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25529 Auto-create: Pre-existing historical data is not partitioned as specified by ALTER

          • Major - Major loss of function.
          • Stalled

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25531 Auto-create: Bad error message upon failure to create partitions due to ER_TOO_MANY_PARTITIONS_ERROR

          • 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-25540 Auto-create: There is no clear way to disable auto-creation without ruining existing partitions

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25545 Auto-create: ODKU does not cause partition creation

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25547 Auto-create: Undetected deadlock lasts longer than the configured timeout

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25559 Auto-create: infinite loop after interrupted lock wait

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25567 Auto-create: Partition creation happens also when modified columns are not versioned

          • Trivial - Cosmetic problem like misspelt words or misaligned text.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-28207 Auto-create: Many partitions generate error: Out of resources when opening file './test/' (Errcode: 24 "Too many open files")

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-28288 System versioning doesn't support correct work for engine=connect and doesn't always give any warnings/errors

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-28337 Update documentation for SYSTEM_TIME partitioning

          • Major - Major loss of function.
          • Stalled

          Bug - A problem which impairs or prevents the functions of the product. MDEV-28338 Auto-create: doesn't work for engine=MyRocks with LIMIT

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-28413 System versioning is incorrect in Spider

          • Major - Major loss of function.
          • Stalled

          Bug - A problem which impairs or prevents the functions of the product. MDEV-28414 Auto-create: If XA transaction is rolled back the created partitions are kept empty and not reused

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

          Task - A task that needs to be done. MDEV-17553 Enable setting start datetime for interval partitioned history of system versioned tables

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

          Task - A task that needs to be done. MDEV-19938 Reorganize multiple history partitions

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-20336 Assertion bitmap_is_set(read_partitions) upon SELECT FOR UPDATE from versioned table

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-22247 History partition overflow leads to wrong SELECT result

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-23640 Versioned InnoDB table doesn't auto-create partitions in time due to imprecise statistics

          • 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-23641 Default settings for partitioning and auto-partitioning by system time are not very practical

          • 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-23642 Locking timeout caused by auto-creation affects original DML

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25390 Pruning boundary for history partitions is wrong by 1 second

          • Major - Major loss of function.
          • Closed

          Activity

            People

              midenok Aleksey Midenkov
              umoser Ulrich Moser (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              9 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.