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

Change of COLLATE unnecessarily requires ALGORITHM=COPY

    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

              • Assignee:
                kevg Eugene Kosov
                Reporter:
                marko Marko Mäkelä
              • Votes:
                0 Vote for this issue
                Watchers:
                6 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: