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

create table like fails if source table is partitioned and engine is myisam or aria with data directory.

    XMLWordPrintable

    Details

      Description

      KB noted here - https://mariadb.com/kb/en/create-table/#create-table-like

      Use the LIKE clause instead of a full table definition to create a table with the same definition as another table, including columns, indexes, and table options. Foreign key definitions, as well as any DATA DIRECTORY or INDEX DIRECTORY table options specified on the original table, will not be created.
      

      However, create table like innodb table with data directory works and have `DATA DIRECTORY ` definition.

      Here is InnoDB example.

      MariaDB [test]> CREATE TABLE test.innodb_partitioned_table1 (
          -> myid BIGINT(20) NOT NULL,
          -> PRIMARY KEY (myid)
          -> ) ENGINE=INNODB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
          -> PARTITION BY HASH (myid)
          -> (PARTITION p0001 DATA DIRECTORY = '/data/mysqldata/mysql' ENGINE = INNODB,
          -> PARTITION p0002 DATA DIRECTORY = '/data/mysqldata/mysql' ENGINE = INNODB,
          -> PARTITION p0003 DATA DIRECTORY = '/data/mysqldata/mysql' ENGINE = INNODB,
          -> PARTITION p0004 DATA DIRECTORY = '/data/mysqldata/mysql' ENGINE = INNODB,
          -> PARTITION p0005 DATA DIRECTORY = '/data/mysqldata/mysql' ENGINE = INNODB
          -> );
      Query OK, 0 rows affected (0.043 sec)
       
      -- files were created under `/data/mysqldata/mysql/test` directory. In this case `test` directory was created automatically.
      [root@node1 mysql]# ll test/
      total 480
      -rw-rw---- 1 mysql mysql 98304 Jun 15 10:00 innodb_partitioned_table1#P#p0001.ibd
      -rw-rw---- 1 mysql mysql 98304 Jun 15 10:00 innodb_partitioned_table1#P#p0002.ibd
      -rw-rw---- 1 mysql mysql 98304 Jun 15 10:00 innodb_partitioned_table1#P#p0003.ibd
      -rw-rw---- 1 mysql mysql 98304 Jun 15 10:00 innodb_partitioned_table1#P#p0004.ibd
      -rw-rw---- 1 mysql mysql 98304 Jun 15 10:00 innodb_partitioned_table1#P#p0005.ibd
       
      MariaDB [test]> CREATE TABLE test.innodb_partitioned_table2 LIKE test.innodb_partitioned_table1;
      Query OK, 0 rows affected (0.034 sec)
       
      -- files were created under `/data/mysqldata/mysql/`
      [root@node1 mysql]# ll
      total 24
      drwxrwx--- 3 mysql mysql 4096 Jun 15 10:01 innodb_partitioned_table1#P#p0001
      drwxrwx--- 3 mysql mysql 4096 Jun 15 10:01 innodb_partitioned_table1#P#p0002
      drwxrwx--- 3 mysql mysql 4096 Jun 15 10:01 innodb_partitioned_table1#P#p0003
      drwxrwx--- 3 mysql mysql 4096 Jun 15 10:01 innodb_partitioned_table1#P#p0004
      drwxrwx--- 3 mysql mysql 4096 Jun 15 10:01 innodb_partitioned_table1#P#p0005
      drwxrwx--- 2 mysql mysql 4096 Jun 15 10:00 test
      

      Here is MyISAM example.

      MariaDB [test]> CREATE TABLE test.myisam_partitioned_table1 (
          -> myid BIGINT(20) NOT NULL,
          -> PRIMARY KEY (myid)
          -> ) ENGINE=MYISAM DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
          -> PARTITION BY HASH (myid)
          -> (PARTITION p0001 DATA DIRECTORY = '/data/mysqldata/mysql' ENGINE = MYISAM,
          -> PARTITION p0002 DATA DIRECTORY = '/data/mysqldata/mysql' ENGINE = MYISAM,
          -> PARTITION p0003 DATA DIRECTORY = '/data/mysqldata/mysql' ENGINE = MYISAM,
          -> PARTITION p0004 DATA DIRECTORY = '/data/mysqldata/mysql' ENGINE = MYISAM,
          -> PARTITION p0005 DATA DIRECTORY = '/data/mysqldata/mysql' ENGINE = MYISAM
          -> )
          -> ;
      Query OK, 0 rows affected (0.004 sec)
       
      -- files were created under '/data/mysqldata/mysql'
      [root@node1 mysql]# ll
      total 0
      -rw-rw---- 1 mysql mysql 0 Jun 15 10:08 myisam_partitioned_table1#P#p0001.MYD
      -rw-rw---- 1 mysql mysql 0 Jun 15 10:08 myisam_partitioned_table1#P#p0002.MYD
      -rw-rw---- 1 mysql mysql 0 Jun 15 10:08 myisam_partitioned_table1#P#p0003.MYD
      -rw-rw---- 1 mysql mysql 0 Jun 15 10:08 myisam_partitioned_table1#P#p0004.MYD
      -rw-rw---- 1 mysql mysql 0 Jun 15 10:08 myisam_partitioned_table1#P#p0005.MYD
       
      MariaDB [test]> CREATE TABLE test.myisam_partitioned_table2 LIKE test.myisam_partitioned_table1;
      ERROR 1 (HY000): Can't create/write to file '/data/mysqldata/mysql/myisam_partitioned_table1#P#p0001.MYD/myisam_partitioned_table2#P#p0001.MYD' (Errcode: 20 "Not a directory")
      
      

      Above error also applies to aria storage engine.

        Attachments

          Activity

            People

            Assignee:
            holyfoot Alexey Botchkov
            Reporter:
            allen.lee@mariadb.com Allen Lee
            Votes:
            0 Vote for this issue
            Watchers:
            6 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.