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

ALTER TABLE EXCHANGE PARTITION does not work properly when used with DATA DIRECTORY

    XMLWordPrintable

    Details

    • Sprint:
      10.2.13

      Description

      ALTER TABLE EXCHANGE PARTITION does not work properly if the tables and partitions involved were created with the DATA DIRECTORY option specified.

      To reproduce, first create a data directory:

      sudo mkdir -p /mysql/
      sudo chown mysql:mysql /mysql/
      sudo chmod 0750 /mysql/
      

      Then create a partitioned table that uses the new data directory:

      CREATE TABLE test_swap_part_data_dir
      (
         myid INT(11) NOT NULL,
         myval VARCHAR(10),
         PRIMARY KEY (myid)
      ) ENGINE=INNODB PARTITION BY KEY (myid)
      (
         PARTITION p0001 DATA DIRECTORY = '/mysql/' ENGINE = INNODB,
         PARTITION p0002 DATA DIRECTORY = '/mysql/' ENGINE = INNODB,
         PARTITION p0003 DATA DIRECTORY = '/mysql/' ENGINE = INNODB,
         PARTITION p0004 DATA DIRECTORY = '/mysql/' ENGINE = INNODB,
         PARTITION p0005 DATA DIRECTORY = '/mysql/' ENGINE = INNODB,
         PARTITION p0006 DATA DIRECTORY = '/mysql/' ENGINE = INNODB,
         PARTITION p0007 DATA DIRECTORY = '/mysql/' ENGINE = INNODB,
         PARTITION p0008 DATA DIRECTORY = '/mysql/' ENGINE = INNODB,
         PARTITION p0009 DATA DIRECTORY = '/mysql/' ENGINE = INNODB,
         PARTITION p0010 DATA DIRECTORY = '/mysql/' ENGINE = INNODB,
         PARTITION p0011 DATA DIRECTORY = '/mysql/' ENGINE = INNODB,
         PARTITION p0012 DATA DIRECTORY = '/mysql/' ENGINE = INNODB,
         PARTITION p0013 DATA DIRECTORY = '/mysql/' ENGINE = INNODB,
         PARTITION p0014 DATA DIRECTORY = '/mysql/' ENGINE = INNODB,
         PARTITION p0015 DATA DIRECTORY = '/mysql/' ENGINE = INNODB
      );
      

      And then create an identical non-partitioned table that uses the data directory:

      CREATE TABLE test_swap_part_data_dir_p0001
      (
         myid INT(11) NOT NULL,
         myval VARCHAR(10),
         PRIMARY KEY (myid)
      ) ENGINE=INNODB DATA DIRECTORY = '/mysql/';
      

      And then swap the tablespace of this table with one of the partitions:

      ALTER TABLE test_swap_part_data_dir EXCHANGE PARTITION p0001 WITH TABLE test_swap_part_data_dir_p0001;
      

      And then if you look at the definition of test_swap_part_data_dir, you can see that the DATA DIRECTORY value for partition p0001 is no longer correct:

      MariaDB [db1]> SHOW CREATE TABLE test_swap_part_data_dir\G
      *************************** 1. row ***************************
             Table: test_swap_part_data_dir
      Create Table: CREATE TABLE `test_swap_part_data_dir` (
        `myid` int(11) NOT NULL,
        `myval` varchar(10) DEFAULT NULL,
        PRIMARY KEY (`myid`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1
       PARTITION BY KEY (`myid`)
      (PARTITION `p0001` DATA DIRECTORY = '/mysql/test_swap_part_data_dir_p0001' ENGINE = InnoDB,
       PARTITION `p0002` DATA DIRECTORY = '/mysql' ENGINE = InnoDB,
       PARTITION `p0003` DATA DIRECTORY = '/mysql' ENGINE = InnoDB,
       PARTITION `p0004` DATA DIRECTORY = '/mysql' ENGINE = InnoDB,
       PARTITION `p0005` DATA DIRECTORY = '/mysql' ENGINE = InnoDB,
       PARTITION `p0006` DATA DIRECTORY = '/mysql' ENGINE = InnoDB,
       PARTITION `p0007` DATA DIRECTORY = '/mysql' ENGINE = InnoDB,
       PARTITION `p0008` DATA DIRECTORY = '/mysql' ENGINE = InnoDB,
       PARTITION `p0009` DATA DIRECTORY = '/mysql' ENGINE = InnoDB,
       PARTITION `p0010` DATA DIRECTORY = '/mysql' ENGINE = InnoDB,
       PARTITION `p0011` DATA DIRECTORY = '/mysql' ENGINE = InnoDB,
       PARTITION `p0012` DATA DIRECTORY = '/mysql' ENGINE = InnoDB,
       PARTITION `p0013` DATA DIRECTORY = '/mysql' ENGINE = InnoDB,
       PARTITION `p0014` DATA DIRECTORY = '/mysql' ENGINE = InnoDB,
       PARTITION `p0015` DATA DIRECTORY = '/mysql' ENGINE = InnoDB)
      1 row in set (0.00 sec)
      

      Of course, this data directory doesn't even exist:

      $ sudo ls -l /mysql/test_swap_part_data_dir_p0001
      ls: cannot access /mysql/test_swap_part_data_dir_p0001: No such file or directory
      

      But surprisingly, the table can still be queried:

      MariaDB [db1]> SELECT * FROM test_swap_part_data_dir;
      Empty set (0.00 sec)
       
      MariaDB [db1]> CHECK TABLE test_swap_part_data_dir;
      +-----------------------------+-------+----------+----------+
      | Table                       | Op    | Msg_type | Msg_text |
      +-----------------------------+-------+----------+----------+
      | db1.test_swap_part_data_dir | check | status   | OK       |
      +-----------------------------+-------+----------+----------+
      1 row in set (0.00 sec)
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                holyfoot Alexey Botchkov
                Reporter:
                GeoffMontee Geoff Montee
              • Votes:
                1 Vote for this issue
                Watchers:
                6 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: