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

ALGORITHM=INSTANT fails because of surprise change of timestamp type

    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

        Issue Links

          Activity

            People

              bar Alexander Barkov
              Maikel Punie Maikel Punie
              Votes:
              1 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.