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

ALGORITHM=INSTANT fails because of surprise change of timestamp type

Details

    • Bug
    • Status: Confirmed (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.4.6, 10.3(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL), 10.11, 11.0(EOL), 11.1(EOL), 11.2(EOL), 11.3(EOL), 11.4
    • 10.5, 10.6, 10.11
    • Storage Engine - InnoDB

    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

          relates to

          Task - A task that needs to be done. MDEV-11424 Instant ALTER TABLE of failure-free record format changes

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-15225 Can't import .ibd file with temporal type format differing from mysql56_temporal_format

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          mentioned in

          Page Failed to load

          Activity

            Ascending order - Click to sort in descending order
            Maikel Punie Maikel Punie created issue -
            Maikel Punie Maikel Punie made changes -
            Field Original Value New Value
            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)
            		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.




            {noformat}
            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)
            {noformat}
            Maikel Punie Maikel Punie made changes -
            Summary ALGORITHM=INSTANT wrong error message on adding of column ALGORITHM=INSTANT fails while adding new column
            elenst Elena Stepanova made changes -
            Fix Version/s 10.4 [ 22408 ]
            Assignee Marko Mäkelä [ marko ]
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 98249 ] MariaDB v4 [ 141405 ]
            marko Marko Mäkelä made changes -
            Status Open [ 1 ] Confirmed [ 10101 ]
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            Fix Version/s 10.5 [ 23123 ]
            Fix Version/s 10.6 [ 24028 ]
            Fix Version/s 10.11 [ 27614 ]
            Affects Version/s 10.3 [ 22126 ]
            Affects Version/s 10.5 [ 23123 ]
            Affects Version/s 10.6 [ 24028 ]
            Affects Version/s 10.7 [ 24805 ]
            Affects Version/s 10.8 [ 26121 ]
            Affects Version/s 10.9 [ 26905 ]
            Affects Version/s 10.10 [ 27530 ]
            Affects Version/s 10.11 [ 27614 ]
            Affects Version/s 11.0 [ 28320 ]
            Affects Version/s 11.1 [ 28549 ]
            Affects Version/s 11.2 [ 28603 ]
            Affects Version/s 11.3 [ 28565 ]
            Affects Version/s 11.4 [ 29301 ]
            Assignee Marko Mäkelä [ marko ] Alexander Barkov [ bar ]
            Summary ALGORITHM=INSTANT fails while adding new column ALGORITHM=INSTANT fails because of surprise change of timestamp type
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 10.4 [ 22408 ]

            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.