[MDEV-25917] create table like fails if source table is partitioned and engine is myisam or aria with data directory. Created: 2021-06-15  Updated: 2022-04-19  Resolved: 2022-01-25

Status: Closed
Project: MariaDB Server
Component/s: Partitioning
Affects Version/s: 10.4.15, 10.2, 10.3, 10.4, 10.5
Fix Version/s: 10.2.42, 10.3.33, 10.4.23, 10.5.14, 10.6.6, 10.7.2, 10.8.1

Type: Bug Priority: Critical
Reporter: Allen Lee (Inactive) Assignee: Alexey Botchkov
Resolution: Fixed Votes: 0
Labels: None


 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.

 Comments   
Comment by Alice Sherepa [ 2021-06-15 ]

I repeated as described on 10.1-10.5, 5.5 and 10.0 InnoDb does the same as on versions >10.1, but myisam creates a table in the data directory (so no error, but also does not ignore data directory, just uses the same data definition) 

CREATE TABLE tc (
 
myid BIGINT(20) NOT NULL,
 
PRIMARY KEY (myid)
 
) ENGINE=myisam 
PARTITION BY HASH (myid)
 
(PARTITION p0001 DATA DIRECTORY ='/tmp/1'  ENGINE = myisam,
 
PARTITION p0002 DATA DIRECTORY = '/tmp/2'  ENGINE = myisam,
 
PARTITION p0003 DATA DIRECTORY = '/tmp'  ENGINE = myisam,
 
PARTITION p0004 DATA DIRECTORY = '/tmp'  ENGINE = myisam,
 
PARTITION p0005 DATA DIRECTORY = '/tmp' ENGINE = myisam
 
);
 
create table t like tc;
 
show create table t;
 
Table	Create Table
 
t	CREATE TABLE `t` (
 
  `myid` bigint(20) NOT NULL,
 
  PRIMARY KEY (`myid`)
 
) ENGINE=MyISAM DEFAULT CHARSET=latin1
 
/*!50100 PARTITION BY HASH (myid)
 
(PARTITION p0001 DATA DIRECTORY = '/tmp/1' ENGINE = MyISAM,
 
 PARTITION p0002 DATA DIRECTORY = '/tmp/2' ENGINE = MyISAM,
 
 PARTITION p0003 DATA DIRECTORY = '/tmp' ENGINE = MyISAM,
 
 PARTITION p0004 DATA DIRECTORY = '/tmp' ENGINE = MyISAM,
 
 PARTITION p0005 DATA DIRECTORY = '/tmp' ENGINE = MyISAM) */

Comment by Alexey Botchkov [ 2022-01-22 ]

https://github.com/MariaDB/server/commit/5fe3919fe10e38a0fb03b68c1ffd0d370d3083f2

Comment by Alexander Barkov [ 2022-01-24 ]

holyfoot, I suggest the following changes:

  • Add tests for subpartitions with their own data/index dir.
  • Add a new parameter to get_clone(), e.g.: 

    partition_info *partition_info::get_clone(THD *thd, bool copy_index_and_data_dir)

    I think this should reduce the patch size: The new code won't be needed at all, instead we'll just call like this:

     thd->work_part_info= src_table->table->part_info->get_clone(thd, false/*don't copy data/index dir*/);

Comment by Alexey Botchkov [ 2022-01-24 ]

Another fix proposal
https://github.com/MariaDB/server/commit/d9dd22da99532bc8f9e2591c1b35daf64354574a

Comment by Alexander Barkov [ 2022-01-24 ]

https://github.com/MariaDB/server/commit/d9dd22da99532bc8f9e2591c1b35daf64354574a is OK to push. Thanks.

Comment by Alexey Botchkov [ 2022-01-25 ]

https://github.com/MariaDB/server/commit/50e66db018d0d0ee49fd2b7196f30ed4594dc2b3

Generated at Thu Feb 08 09:41:23 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.