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

ALGORITHM=INSTANT or NOCOPY fails to fail on MyISAM tables

    XMLWordPrintable

    Details

      Description

      The ALGORITHM sometimes fails to have impact on MyISAM tables:

      diff --git a/mysql-test/main/type_varchar_mysql41.test b/mysql-test/main/type_varchar_mysql41.test
      index 5624e9edaaa..dac240f5f11 100644
      --- a/mysql-test/main/type_varchar_mysql41.test
      +++ b/mysql-test/main/type_varchar_mysql41.test
      @@ -47,6 +47,13 @@ copy_file $MYSQL_TEST_DIR/std_data/vchar.frm $MYSQLD_DATADIR/test/t1old.frm;
       TRUNCATE TABLE t1old;
       SHOW CREATE TABLE t1old;
       CALL p1('v');
      +INSERT INTO t1old VALUES (REPEAT('foo',10),'bar','abc','text');
      +INSERT IGNORE INTO t1old VALUES (REPEAT('snafu  ',5),'afu','def','best');
      +--enable_info
      +ALTER TABLE t1old MODIFY v VARCHAR(60), ALGORITHM=INSTANT;
      +--disable_info
      +INSERT INTO t1old VALUES (REPEAT('snafu ',10),'xyz','ghi','rest');
      +SELECT * FROM t1old;
       DROP TABLE t1old;
       
       let $MYSQLD_DATADIR= `SELECT @@datadir`;
      @@ -54,6 +61,18 @@ copy_file $MYSQL_TEST_DIR/std_data/bug19371.frm $MYSQLD_DATADIR/test/t1old.frm;
       TRUNCATE TABLE t1old;
       SHOW CREATE TABLE t1old;
       CALL p1('a');
      +INSERT INTO t1old VALUES (REPEAT(0xe4,255),REPEAT('snafu',51));
      +SET alter_algorithm=inplace;
      +--enable_info
      +ALTER TABLE t1old MODIFY a VARBINARY(260), CHANGE b c VARCHAR(300);
      +SELECT * FROM t1old;
      +--error ER_ALTER_OPERATION_NOT_SUPPORTED
      +ALTER TABLE t1old MODIFY a VARBINARY(260), CHANGE c b VARCHAR(240);
      +ALTER TABLE t1old CHANGE c bin VARCHAR(300);
      +SET alter_algorithm=default;
      +--disable_info
      +INSERT IGNORE INTO t1old VALUES (REPEAT('lots of data',25),REPEAT('cams',75));
      +SELECT * FROM t1old;
       DROP TABLE t1old;
       
       DROP PROCEDURE p1;
      

      You will see output like this:

      10.4 c2c637c583b9803a358d2b07ca98f3cb9868bb68

      ALTER TABLE t1old MODIFY v VARCHAR(60), ALGORITHM=INSTANT;
      affected rows: 2
      info: Records: 2  Duplicates: 0  Warnings: 0
      

      ALGORITHM=INPLACE will be duly refused in this case.

      This may be related to conversion from pre-5.0.3 VARCHAR or VARBINARY types. Here is another problematic test:

      let $MYSQLD_DATADIR= `SELECT @@datadir`;
      copy_file $MYSQL_TEST_DIR/std_data/bug19371.frm $MYSQLD_DATADIR/test/t1old.frm;
       
      CREATE TABLE t1 (a varbinary(255), b varchar(255)) ENGINE=MyISAM;
      TRUNCATE TABLE t1old;
      SHOW CREATE TABLE t1old;
       
      ALTER TABLE t1old MODIFY a VARCHAR(260) CHARSET utf8, CHANGE b c VARCHAR(300),
      ALGORITHM=INSTANT;
      ALTER TABLE t1 MODIFY a VARCHAR(260) CHARSET utf8, CHANGE b c VARCHAR(300),
      ALGORITHM=INSTANT;
       
      DROP TABLE t1,t1old;
      

      We do allow the conversion for t1old but not for t1. Note that it should not be safe to instantly convert from VARBINARY to a multi-byte character set.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              bar Alexander Barkov
              Reporter:
              marko Marko Mäkelä
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

                Dates

                Created:
                Updated: