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

Alter InnoDB Partitioned Table Moves Files (which were originally not in the datadir) to the datadir

    Details

      Description

      Alter InnoDB Partitioned Table Moves Files (which were originally not in the datadir) to datadir.

      This is also an upstream bug, reported here:

      https://bugs.mysql.com/bug.php?id=78164

      There is a test case there. Here is also a test case provided to us:

      To recreate (replace <path_outside_datadir> with a path outside the datadir):

      CREATE TABLE my_part_table (
      my_id BIGINT(20) NOT NULL,
      my_value SMALLINT(6) NOT NULL,
      insert_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP(),
      PRIMARY KEY (my_id)
      ) ENGINE=INNODB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
      PARTITION BY HASH (my_id)
      (PARTITION p0001 DATA DIRECTORY = '<path_outside_datadir>' ENGINE = INNODB,
      PARTITION p0002 DATA DIRECTORY = '<path_outside_datadir>' ENGINE = INNODB,
      PARTITION p0003 DATA DIRECTORY = '<path_outside_datadir>' ENGINE = INNODB,
      PARTITION p0004 DATA DIRECTORY = '<path_outside_datadir>' ENGINE = INNODB,
      PARTITION p0005 DATA DIRECTORY = '<path_outside_datadir>' ENGINE = INNODB
      );
      

      Now check the table (before the ALTER) and you see it shows the correct paths:

      SHOW CREATE TABLE my_part_table;
       
      CREATE TABLE my_part_table (
      my_id BIGINT(20) NOT NULL,
      my_value SMALLINT(6) NOT NULL,
      insert_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP(),
      PRIMARY KEY (my_id)
      ) ENGINE=INNODB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
      PARTITION BY HASH (my_id)
      (PARTITION p0001 DATA DIRECTORY = '<path_outside_datadir>' ENGINE = INNODB,
      PARTITION p0002 DATA DIRECTORY = '<path_outside_datadir>' ENGINE = INNODB,
      PARTITION p0003 DATA DIRECTORY = '<path_outside_datadir>' ENGINE = INNODB,
      PARTITION p0004 DATA DIRECTORY = '<path_outside_datadir>' ENGINE = INNODB,
      PARTITION p0005 DATA DIRECTORY = '<path_outside_datadir>' ENGINE = INNODB)
      

      Now, issue an ALTER:

      ALTER TABLE my_part_table
      ADD COLUMN update_date DATETIME DEFAULT CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP()
      AFTER insert_date;
      

      Re-run SHOW CREATE:

      SHOW CREATE TABLE my_part_table;
       
      CREATE TABLE my_part_table (
      my_id BIGINT(20) NOT NULL,
      my_value SMALLINT(6) NOT NULL,
      insert_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP(),
      update_date DATETIME DEFAULT CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP(),
      PRIMARY KEY (my_id)
      ) ENGINE=INNODB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
      PARTITION BY HASH (my_id)
      (PARTITION p0001 ENGINE = INNODB,
      PARTITION p0002 ENGINE = INNODB,
      PARTITION p0003 ENGINE = INNODB,
      PARTITION p0004 ENGINE = INNODB,
      PARTITION p0005 ENGINE = INNODB)
      

      Note the non-standard paths are gone and all is in the datadir.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                kevg Eugene Kosov
                Reporter:
                ccalender Chris Calender
              • Votes:
                0 Vote for this issue
                Watchers:
                6 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: