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

ALGORITHM=INSTANT fails while adding new column

    XMLWordPrintable

    Details

      Description

      when trying to add a column to a big innodb table we get an error saying the column type can not be changed with ALGORITHM=INSTANT, but we are trying to add a column.

      MariaDB [regressiondb]> show create table testAndSuite_status\G
      *************************** 1. row ***************************
             Table: testAndSuite_status
      Create Table: CREATE TABLE `testAndSuite_status` (
        `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
        `topo_id` int(10) unsigned NOT NULL,
        `vmTopo_id` int(10) unsigned NOT NULL DEFAULT 0,
        `extraKey_id` int(10) NOT NULL DEFAULT 0,
        `testAndSuite_id` int(10) unsigned NOT NULL,
        `status` enum('PASSED','FAILED','SKIPPED','UNKNOWN','OUT','GHOST') COLLATE latin1_bin NOT NULL DEFAULT 'GHOST',
        `rotten` tinyint(1) NOT NULL DEFAULT 0,
        `duration` varchar(45) COLLATE latin1_bin DEFAULT NULL,
        `regressRun_id_Pass` int(11) DEFAULT NULL,
        `regressRun_id_Fail` int(11) DEFAULT NULL,
        `origin` enum('scanResult','scanGash','admin','gui','expand') COLLATE latin1_bin NOT NULL,
        `registered` tinyint(1) NOT NULL DEFAULT 0,
        `changedDate` timestamp NOT NULL DEFAULT current_timestamp(),
        `remarks` varchar(140) COLLATE latin1_bin DEFAULT NULL,
        `passCount` int(11) NOT NULL DEFAULT 0,
        `failCount` int(11) NOT NULL DEFAULT 0,
        `bossKey` tinyint(1) NOT NULL,
        `aggregate` int(2) NOT NULL DEFAULT 0 COMMENT '0=normal, 2=agg, 1=pointing to agg',
        PRIMARY KEY (`id`),
        KEY `testAndSuite` (`testAndSuite_id`),
        KEY `topo` (`topo_id`),
        KEY `regressRun_id_Fail` (`regressRun_id_Fail`),
        KEY `regressRun_id_Pass` (`regressRun_id_Pass`),
        KEY `rotten` (`rotten`),
        KEY `extrakey` (`extraKey_id`),
        KEY `vmTopo_id` (`vmTopo_id`)
      ) ENGINE=InnoDB AUTO_INCREMENT=2717022889 DEFAULT CHARSET=latin1 COLLATE=latin1_bin
      1 row in set (0.000 sec)
       
      MariaDB [regressiondb]> alter table testAndSuite_status ADD master_tas_id INT(11) UNSIGNED NULL DEFAULT NULL, ALGORITHM=INSTANT;
      ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY
       
      MariaDB [regressiondb]> select VERSION();
      +--------------------+
      | VERSION()          |
      +--------------------+
      | 10.4.6-MariaDB-log |
      +--------------------+
      1 row in set (0.000 sec)
       
      MariaDB [regressiondb]> show variables like '%row_format%';
      +---------------------------+---------+
      | Variable_name             | Value   |
      +---------------------------+---------+
      | innodb_default_row_format | dynamic |
      +---------------------------+---------+
      1 row in set (0.002 sec)
      

        Attachments

          Activity

            People

            Assignee:
            marko Marko Mäkelä
            Reporter:
            Maikel Punie Maikel Punie
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated: