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

Specifying DATA DIRECTORY in tables leads to failing EXCHANGE PARTITION

    XMLWordPrintable

Details

    Description

      Inconsistency with slashes when specifying DATA DIRECTORY in tables with and without partitions leads to failing EXCHANGE PARTITION

      1. Create directory to be used (with correct permissions), in this example, C:/mariadb

      2. Test case:

      use test;
       
      CREATE TABLE t1 (
      id1 INT(11) NOT NULL,
      id2 INT(11) NOT NULL
      ) ENGINE=INNODB DEFAULT CHARSET=latin1
      /*!50100 PARTITION BY RANGE (id2)
      (PARTITION p0 VALUES LESS THAN (6) DATA DIRECTORY = 'C:/mariadb' ENGINE = InnoDB,
      PARTITION p1 VALUES LESS THAN (11) DATA DIRECTORY = 'C:/mariadb' ENGINE = InnoDB,
      PARTITION p2 VALUES LESS THAN (16) DATA DIRECTORY = 'C:/mariadb' ENGINE = InnoDB,
      PARTITION p3 VALUES LESS THAN MAXVALUE DATA DIRECTORY = 'C:/mariadb' ENGINE = InnoDB) */;
       
      CREATE TABLE t2 (
      id1 INT(11) NOT NULL,
      id2 INT(11) NOT NULL
      ) ENGINE=INNODB DEFAULT CHARSET=latin1 DATA DIRECTORY='C:/mariadb';
       
      ALTER TABLE t1 EXCHANGE PARTITION p0 WITH TABLE t2;
      

      ERROR 1731 (HY000): Non matching attribute 'DATA DIRECTORY' between partition and table
      

      If we examine the SHOW CREATE TABLE outputs, we see that in t2, DATA DIRECTORY appended an extra slash at the end, whereas that of t1 did not:

      mysql> show create table t1\G
      *************************** 1. row ***************************
             Table: t1
      Create Table: CREATE TABLE `t1` (
        `id1` int(11) NOT NULL,
        `id2` int(11) NOT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1
      /*!50100 PARTITION BY RANGE (id2)
      (PARTITION p0 VALUES LESS THAN (6) DATA DIRECTORY = 'C:/mariadb' ENGINE = InnoDB,
       PARTITION p1 VALUES LESS THAN (11) DATA DIRECTORY = 'C:/mariadb' ENGINE = InnoDB,
       PARTITION p2 VALUES LESS THAN (16) DATA DIRECTORY = 'C:/mariadb' ENGINE = InnoDB,
       PARTITION p3 VALUES LESS THAN MAXVALUE DATA DIRECTORY = 'C:/mariadb' ENGINE = InnoDB) */
      1 row in set (0.01 sec)
       
      mysql> show create table t2\G
      *************************** 1. row ***************************
             Table: t2
      Create Table: CREATE TABLE `t2` (
        `id1` int(11) NOT NULL,
        `id2` int(11) NOT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='C:/mariadb/'
      1 row in set (0.00 sec)
      

      In the above, note how t2 appended a slash to the end of the value for DATA DIRECTORY, but t1 did not. Both were created without the extra slash.

      If you do not specify DATA DIRECTORY, this all works fine:

      drop schema test;
      create schema test;
      use test;
       
      CREATE TABLE t1 (
      id1 INT(11) NOT NULL,
      id2 INT(11) NOT NULL
      ) ENGINE=INNODB DEFAULT CHARSET=latin1
      /*!50100 PARTITION BY RANGE (id2)
      (PARTITION p0 VALUES LESS THAN (6) ENGINE = InnoDB,
      PARTITION p1 VALUES LESS THAN (11) ENGINE = InnoDB,
      PARTITION p2 VALUES LESS THAN (16) ENGINE = InnoDB,
      PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */;
       
      CREATE TABLE t2 (
      id1 INT(11) NOT NULL,
      id2 INT(11) NOT NULL
      ) ENGINE=INNODB DEFAULT CHARSET=latin1;
       
      mysql> ALTER TABLE t1 EXCHANGE PARTITION p0 WITH TABLE t2;
      Query OK, 0 rows affected (0.04 sec)
      

      You cannot ALTER t2 to remove the extra slash:

      mysql> ALTER TABLE t2 DATA DIRECTORY='C:/mariadb';
      Query OK, 0 rows affected, 1 warning (0.01 sec)
      Records: 0  Duplicates: 0  Warnings: 1
       
      mysql> show warnings;
      +---------+------+---------------------------------+
      | Level   | Code | Message                         |
      +---------+------+---------------------------------+
      | Warning | 1618 | <DATA DIRECTORY> option ignored |
      +---------+------+---------------------------------+
      1 row in set (0.00 sec)
      

      Similarly, you cannot ALTER t1 to add the extra slashes:

      CREATE TABLE t1 (
      id1 INT(11) NOT NULL,
      id2 INT(11) NOT NULL
      ) ENGINE=INNODB DEFAULT CHARSET=latin1
      /*!50100 PARTITION BY RANGE (id2)
      (PARTITION p0 VALUES LESS THAN (6) DATA DIRECTORY = 'C:/mariadb/' ENGINE = InnoDB,
      PARTITION p1 VALUES LESS THAN (11) DATA DIRECTORY = 'C:/mariadb/' ENGINE = InnoDB,
      PARTITION p2 VALUES LESS THAN (16) DATA DIRECTORY = 'C:/mariadb/' ENGINE = InnoDB,
      PARTITION p3 VALUES LESS THAN MAXVALUE DATA DIRECTORY = 'C:/mariadb/' ENGINE = InnoDB) */;
       
      mysql> show create table t1\G
      *************************** 1. row ***************************
             Table: t1
      Create Table: CREATE TABLE `t1` (
        `id1` int(11) NOT NULL,
        `id2` int(11) NOT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1
      /*!50100 PARTITION BY RANGE (id2)
      (PARTITION p0 VALUES LESS THAN (6) DATA DIRECTORY = 'C:/mariadb' ENGINE = InnoDB,
       PARTITION p1 VALUES LESS THAN (11) DATA DIRECTORY = 'C:/mariadb' ENGINE = InnoDB,
       PARTITION p2 VALUES LESS THAN (16) DATA DIRECTORY = 'C:/mariadb' ENGINE = InnoDB,
       PARTITION p3 VALUES LESS THAN MAXVALUE DATA DIRECTORY = 'C:/mariadb' ENGINE = InnoDB) */
      1 row in set (0.01 sec)
      

      Note it ignores/removes them.

      Attachments

        Issue Links

          Activity

            People

              serg Sergei Golubchik
              ccalender Chris Calender (Inactive)
              Votes:
              1 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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