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

          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.