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

Avoid table rebuild in ALTER TABLE on collation or charset changes

Details

    Description

      Some character encodings are proper subsets of others. ASCII is a proper subset of latin1 or latin2 or UTF-8. The 3-byte UTF-8 is a proper subset of the 4-byte UTF-8.

      When the character encoding of a column changes such that the storage encoding does not change, we should avoid rebuilding the table.

      Likewise, when the collation of a column changes such that the encoding stays the same, we should avoid rebuilding the table.

      Collations do not matter if the column is not indexed.

      If the collation of an indexed column changes, then the affected indexes may have to be rebuilt. For certain collations we might avoid that as well. For example, changing the encoding and collation from binary 3-byte to binary 4-byte UTF-8 might not require any change.

      When the collation change involves a column that is part of the PRIMARY KEY and we have determined that a collation change is necessary, then the whole table will have to be rebuilt.

      Attachments

        Issue Links

          blocks

          Task - A task that needs to be done. MDEV-11424 Instant ALTER TABLE of failure-free record format changes

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-18584 Avoid copying when altering CHAR column in InnoDB table

          • Major - Major loss of function.
          • Confirmed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-19284 INSTANT ALTER with ucs2-to-utf16 conversion produces bad data

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-19285 INSTANT ALTER from ascii_general_ci to latin1_general_ci produces corrupt data

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-19524 Server crashes in Bitmap<64u>::is_clear_all / Field_longstr::csinfo_change_allows_instant_alter

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-20565 Assertion failure on CHANGE COLUMN…SYSTEM VERSIONING

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-22333 Assertion `len <= fixed_len' failed in rec_get_converted_size_comp_prefix_low on ALTER + INSERT

          • Major - Major loss of function.
          • Confirmed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-22334 Assertion `col->len == len' failed in innobase_rename_or_enlarge_column_try on ALTER

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27280 server crashes on CHECK TABLE after COLLATE change for utf8mb4

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

          Task - A task that needs to be done. MDEV-15563 Instant failure-free data type conversions

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

          Task - A task that needs to be done. MDEV-17965 Allow instant VARCHAR increase of indexed fields

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-17773 Avoid table rebuild in ALTER TABLE on collation or charset changes for ENUM and SET

          • 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-27859 Instant change of ENUM is refused because of COLLATE mismatch

          • Major - Major loss of function.
          • Confirmed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-17301 Change of COLLATE unnecessarily requires ALGORITHM=COPY

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26294 Duplicate entries in unique index not detected when changing collation with INPLACE / NOCOPY algoritm

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27864 Alter table modify column for same data type copying table

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-28323 Redundant Item_func_conv_charset on WHERE utf8mb4_field=utf8mb3_field

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

          Activity

            Ascending order - Click to sort in descending order
            marko Marko Mäkelä created issue -
            marko Marko Mäkelä made changes -
            Field Original Value New Value
            kevg Eugene Kosov (Inactive) made changes -
            Assignee Thirunarayanan B [ thiru ] Eugene Kosov [ kevg ]
            kevg Eugene Kosov (Inactive) made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            kevg Eugene Kosov (Inactive) made changes -
            Status In Progress [ 3 ] Stalled [ 10000 ]
            kevg Eugene Kosov (Inactive) made changes -
            Status Stalled [ 10000 ] In Progress [ 3 ]
            julien.fritsch Julien Fritsch made changes -
            Epic Link PT-80 [ 68561 ]
            ralf.gebhardt Ralf Gebhardt made changes -
            Priority Major [ 3 ] Critical [ 2 ]
            ralf.gebhardt Ralf Gebhardt made changes -
            Target end 12/Feb/19 [ 2019-02-12 ]
            kevg Eugene Kosov (Inactive) made changes -
            Comment [ test ]
            kevg Eugene Kosov (Inactive) made changes -
            Comment [ @marko, hello.
            From Jira:
            > Correct: ROW_FORMAT=REDUNDANT will reserve n*mbmaxlen bytes for {{CHAR(n)}}. It will do that even when the column is NULL. The idea is to allow update-in-place.

            It's impossible in this case:

            ```
            set names utf8mb4;

            create table t (
              a char(4) charset utf8mb3
            ) engine=innodb, row_format=redundant;

            insert into t values ('фыва');

            alter table t
              modify a char(4) charset utf8mb4,
              algorithm=instant;

            update t set a=':thinking_face::thinking_face::thinking_face::thinking_face:';
            ```

            Actual allocated size for field `a` should be checked and it's small DELETE/INSERT should be performed instead. But inserting ':thinking_face::thinking_face::thinking_face:' is fine for update-in-place.

            So it looks like instant `ALTER CHARSET` is possible for `ROW_FORMAT=REDUNDANT` but additional checks are needed to be added. For example in `calc_row_difference()`: read an actual buffer length from an old record. ]
            marko Marko Mäkelä made changes -
            kevg Eugene Kosov (Inactive) made changes -
            Assignee Eugene Kosov [ kevg ] Marko Mäkelä [ marko ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            marko Marko Mäkelä made changes -
            Assignee Marko Mäkelä [ marko ] Eugene Kosov [ kevg ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            marko Marko Mäkelä made changes -
            kevg Eugene Kosov (Inactive) made changes -
            kevg Eugene Kosov (Inactive) made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            kevg Eugene Kosov (Inactive) made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            issue.field.resolutiondate 2019-02-14 18:44:02.0 2019-02-14 18:44:02.868
            marko Marko Mäkelä made changes -
            Fix Version/s 10.4.3 [ 23230 ]
            Fix Version/s 10.4 [ 22408 ]
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            NRE Projects NRE-310017 RM_104_NRE NRE-310017 RM_104_NRE RM_102ES_CANDIDATE RM_103ES_CANDIDATE
            marko Marko Mäkelä made changes -
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            kevg Eugene Kosov (Inactive) made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 86023 ] MariaDB v4 [ 133495 ]
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            bar Alexander Barkov made changes -
            marko Marko Mäkelä made changes -

            People

              kevg Eugene Kosov (Inactive)
              marko Marko Mäkelä
              Votes:
              3 Vote for this issue
              Watchers:
              6 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.