Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.4.15, 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5
-
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.
Attachments
Activity
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*/);
Another fix proposal
https://github.com/MariaDB/server/commit/d9dd22da99532bc8f9e2591c1b35daf64354574a
https://github.com/MariaDB/server/commit/d9dd22da99532bc8f9e2591c1b35daf64354574a is OK to push. Thanks.
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)
) ENGINE=myisam);/*!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) */