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

Change of COLLATE unnecessarily requires ALGORITHM=COPY

    XMLWordPrintable

Details

    Description

      The COLLATE attribute of a column only affects the way how any indexes that are built on the column will be sorted. Thus, it should be possible to change the collation of a column without rebuilding the table, except if the column is part of the PRIMARY KEY.

      Currently, changing the collation requires the table to be rebuilt with ALGORITHM=COPY, in all cases below:

      --source include/have_innodb.inc
      		 
      CREATE TABLE t1 (
      		 
       a CHAR(1) COLLATE utf8_bin PRIMARY KEY,
      		 
       b CHAR(1) COLLATE utf8_bin UNIQUE,
      		 
       c CHAR(1) COLLATE utf8_bin
      		 
      ) ENGINE=InnoDB;
      		 
      ALTER TABLE t1 CHANGE COLUMN c c CHAR(1) COLLATE utf8_general_ci,
      		 
      ALGORITHM=INSTANT;
      		 
      ALTER TABLE t1 CHANGE COLUMN b b CHAR(1) COLLATE utf8_general_ci,
      		 
      ALGORITHM=NOCOPY; # implies DROP INDEX b, ADD UNIQUE INDEX(b)
      		 
      ALTER TABLE t1 CHANGE COLUMN a a CHAR(1) COLLATE utf8_general_ci,
      		 
      ALGORITHM=INPLACE; # rebuild due to DROP PRIMARY KEY, ADD PRIMARY KEY(a)
      		 
      DROP TABLE t1;

      (When testing versions predating MDEV-13134, replace the ALGORITHM=INSTANT and ALGORITHM=NOCOPY with the broader clause ALGORITHM=INPLACE.)

      Attachments

        Issue Links

          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-22771 Assertion `fields[i].same(instant.fields[i])' failed in dict_index_t::instant_add_field

          • Critical - Crashes, loss of data, severe memory leak.
          • 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-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-18961 Converting charset to BINARY should be instantaneous

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-19283 Move the code from Field_str::is_equal() to Field_string::is_equal()

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-18583 Avoid copying when changing the type of an indexed column

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

          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

          Task - A task that needs to be done. MDEV-15564 Avoid table rebuild in ALTER TABLE on collation or charset changes

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-17300 MariaDB Galera cluster crash after issuing ALTER table

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29436 Change text column collation unnecessarily requires ALGORITHM=COPY

          • Major - Major loss of function.
          • Open

          Activity

            People

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