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
            View 5 older comments
            marko Marko Mäkelä added a comment -

            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. The later ROW_FORMAT optimize the space and allocate n*mbminlen to n*mbmaxlen bytes. Trailing space is ‘compressed away’. For example, 'фыва' is 4×2 bytes. If the column were CHAR(8), we would allocate only 8 bytes for it, because the trailing space 'фыва ' does not need to be stored. If there was one more letter before the trailing space, we would have to allocate more space.

            It could be that {{CHAR(n)}} with a variable-length character encoding is actually internally stored as VARCHAR, that is, an explicit length is stored. I cannot remember it.

            For ROW_FORMAT=REDUNDANT, I think that it should be OK to bend the existing rules and allow an instant extension of the maximum length of the column, that is, allow CHAR(200) to be converted to a bigger mbmaxlen without changing the existing data. This would be a step towards MDEV-15563, which would allow CHAR or VARCHAR to be instantaneously extended arbitrarily in ROW_FORMAT=REDUNDANT.

            marko Marko Mäkelä added a comment - 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. The later ROW_FORMAT optimize the space and allocate  n*mbminlen to n*mbmaxlen  bytes. Trailing space is ‘compressed away’. For example, 'фыва' is 4×2 bytes. If the column were CHAR(8) , we would allocate only 8 bytes for it, because the trailing space 'фыва ' does not need to be stored. If there was one more letter before the trailing space, we would have to allocate more space. It could be that {{CHAR(n)}} with a variable-length character encoding is actually internally stored as VARCHAR , that is, an explicit length is stored. I cannot remember it. For ROW_FORMAT=REDUNDANT , I think that it should be OK to bend the existing rules and allow an instant extension of the maximum length of the column, that is, allow CHAR(200) to be converted to a bigger mbmaxlen without changing the existing data. This would be a step towards MDEV-15563 , which would allow CHAR or VARCHAR to be instantaneously extended arbitrarily in ROW_FORMAT=REDUNDANT .
            marko Marko Mäkelä added a comment -

            Thanks, this looks generally good. I am mainly requesting some code refactoring.

            Please document (with examples) in which way the file format is being effectively changed, as witnessed by the change to the CHECK TABLE function btr_index_rec_validate(). We must prevent older MariaDB versions from accessing tables where the file format has been changed.

            marko Marko Mäkelä added a comment - Thanks, this looks generally good. I am mainly requesting some code refactoring. Please document (with examples) in which way the file format is being effectively changed, as witnessed by the change to the CHECK TABLE function btr_index_rec_validate() . We must prevent older MariaDB versions from accessing tables where the file format has been changed.
            marko Marko Mäkelä added a comment -

            Some necessary code changes are common between this and MDEV-15563. I think that MDEV-15563 is closer to completion at this point, and should be submitted first.

            marko Marko Mäkelä added a comment - Some necessary code changes are common between this and MDEV-15563 . I think that MDEV-15563 is closer to completion at this point, and should be submitted first.
            bar Alexander Barkov added a comment - - edited

            I've taken a look into a number of commits:

            @kevgs pushed 6 commits.

            20cb37e add GBK
            053e48e add UJIS
            4e3f6ef add SJIS
            54e4cc0 add BIG5
            90b4b4f add latin2
            3237b5e added latin7

            There is no need to list all ASCII compatible character set names and check them using strcmp().
            Please check:

            (cs->state & MY_CS_NONASCII)

            to know if a character set is not ASCII compatible.

            Note, SJIS is not ASCII compatible.
            This change is probably not correct:
            https://github.com/MariaDB/server/commit/54e4cc0b6571a6a929538eca46f940c465e42e68

            bar Alexander Barkov added a comment - - edited I've taken a look into a number of commits: @kevgs pushed 6 commits. 20cb37e add GBK 053e48e add UJIS 4e3f6ef add SJIS 54e4cc0 add BIG5 90b4b4f add latin2 3237b5e added latin7 There is no need to list all ASCII compatible character set names and check them using strcmp(). Please check: (cs->state & MY_CS_NONASCII) to know if a character set is not ASCII compatible. Note, SJIS is not ASCII compatible. This change is probably not correct: https://github.com/MariaDB/server/commit/54e4cc0b6571a6a929538eca46f940c465e42e68
            marko Marko Mäkelä added a comment -

            Due to conflicts with MDEV-15563, I refactored the InnoDB part of this and also the functions Field_str::is_equal() and Field_varstring::is_equal().

            The following bugs were filed for limitations that I think we should try to fix soon:
            MDEV-18583 Avoid copying when changing the type of an indexed column
            MDEV-18584 Avoid copying when altering CHAR column in InnoDB table

            marko Marko Mäkelä added a comment - Due to conflicts with MDEV-15563 , I refactored the InnoDB part of this and also the functions Field_str::is_equal() and Field_varstring::is_equal() . The following bugs were filed for limitations that I think we should try to fix soon: MDEV-18583 Avoid copying when changing the type of an indexed column MDEV-18584 Avoid copying when altering CHAR column in InnoDB table

            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.