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

Partitioning tables with custom data directories moves data back to default directory

    XMLWordPrintable

    Details

      Description

      Partitioning tables ignores specified data directory and moves tables back to default data directory. This has serious consequences when there is a larger alternate mount bigger partitioned tables and not enough space to hold that data in the default data directory.

      When tables are created with an individually specified data directory different from the default, this definition is lost during partitioning, and the engine re-creates the table back in the default data directory. Alter table operations allow the specification of a data directory with partitioning commands, but ignore the specified directory, showing a warning.

      Other alter table operations, such as adding a column, do not have this problem.

      create table partest(
      id int(10) unsigned not null auto_increment,
      val int(10) unsigned not null default 0,
      primary key (id, val)
      ) engine=InnoDB
      default charset=utf8mb4
      data directory='/tmp/maria-partest/';
       
      insert into partest(val) values(1);
      insert into partest(val) values(1001);
       
      ~# ls -alh /tmp/maria-partest/test/
      total 128K
      drwxrwx--- 2 mysql mysql   25 Nov 16 23:01 .
      drwxrwx--- 3 mysql mysql   18 Nov 16 22:55 ..
      -rw-rw---- 1 mysql mysql 128K Nov 16 23:01 partest.ibd
       
      ~# ls -alh /var/lib/mysql/test/
      total 12K
      drwx------ 2 mysql mysql  58 Nov 16 23:22 .
      drwxr-xr-x 5 mysql mysql 289 Nov 16 22:49 ..
      -rw-rw---- 1 mysql mysql  65 Nov 16 21:39 db.opt
      -rw-rw---- 1 mysql mysql 947 Nov 16 23:22 partest.frm
      -rw-rw---- 1 mysql mysql  35 Nov 16 23:22 partest.isl
       
      alter table partest partition by range(val)(
      partition p0 values less than (1000),
      partition p1 values less than (2000),
      partition pFFFF values less than maxvalue
      );
       
      ~# ls -alh /tmp/maria-partest/test/
      total 0
      drwxrwx--- 2 mysql mysql  6 Nov 16 23:09 .
      drwxrwx--- 3 mysql mysql 18 Nov 16 22:55 ..
       
      ~# ls -alh /var/lib/mysql/test/
      total 300K
      drwx------ 2 mysql mysql  133 Nov 16 23:11 .
      drwxr-xr-x 5 mysql mysql  289 Nov 16 22:49 ..
      -rw-rw---- 1 mysql mysql   65 Nov 16 21:39 db.opt
      -rw-rw---- 1 mysql mysql 1.2K Nov 16 23:11 partest.frm
      -rw-rw---- 1 mysql mysql   44 Nov 16 23:11 partest.par
      -rw-rw---- 1 mysql mysql  96K Nov 16 23:09 partest#P#p0.ibd
      -rw-rw---- 1 mysql mysql  96K Nov 16 23:09 partest#P#p1.ibd
      -rw-rw---- 1 mysql mysql  96K Nov 16 23:09 partest#P#pFFFF.ibd
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              nayuta-yanagisawa Nayuta Yanagisawa
              Reporter:
              juan.vera Juan
              Votes:
              1 Vote for this issue
              Watchers:
              8 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.