Bug #114400 Create partition table with DATA DIRECTORY execution result is incorrect
Submitted: 19 Mar 9:34 Modified: 19 Mar 12:15
Reporter: hel le Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:8.0.32, 8.0.36 OS:Any
Assigned to: CPU Architecture:Any

[19 Mar 9:34] hel le
Description:
After the database is initialized, mysqld is started for the first time. Run the following statement to create a partition table. The statement has DATA DIRECTORY options and DATA DIRECTORY is a relative path. This path does not exist. The first execution succeeds, and other executions do not fail.

Statement:
-----------------------------
CREATE TABLE tb_extended_subpartition_002 (a int DEFAULT NULL) ENGINE = InnoDB DEFAULT CHARSET = utf8 PARTITION BY RANGE (a) (
  PARTITION q1 
      VALUES 
        LESS THAN (2) ENGINE = InnoDB comment = 'this is the comment' MAX_ROWS = 5 MIN_ROWS = 2 DATA DIRECTORY = 'var/lib/engine/data'  TABLESPACE `innodb_file_per_table`, 
  PARTITION q2 
      VALUES 
        LESS THAN (5) ENGINE = InnoDB comment = 'this is the comment' MAX_ROWS = 5 MIN_ROWS = 2 DATA DIRECTORY = 'var/lib/engine/data'  TABLESPACE `innodb_file_per_table`
);
------------------------------

How to repeat:
1. init mysqld and start mysqld with datadir=/workdir/mjxue/install/mysql/data
2. Create a range partition table, create sql has DATA DIRECTORY options and DATA DIRECTORY is a relative path. This path does not exist. The statement should fail to be executed, but the actual execution succeeds.

mysql> CREATE TABLE tb1 (a int DEFAULT NULL) ENGINE = InnoDB PARTITION BY RANGE (a) (
    ->   PARTITION q1 VALUES LESS THAN (2) ENGINE = InnoDB comment = 'this is the comment' MAX_ROWS = 5 MIN_ROWS = 2 DATA DIRECTORY = 'var/lib/engine/data'  TABLESPACE `innodb_file_per_table`, 
    ->   PARTITION q2 VALUES LESS THAN (5) ENGINE = InnoDB comment = 'this is the comment' MAX_ROWS = 5 MIN_ROWS = 2 DATA DIRECTORY = 'var/lib/engine/data'  TABLESPACE `innodb_file_per_table`
    -> );
Query OK, 0 rows affected (0.09 sec)

mysql> show create table tb1;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb1   | CREATE TABLE `tb1` (
  `a` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (`a`)
(PARTITION q1 VALUES LESS THAN (2) TABLESPACE = `innodb_file_per_table` MAX_ROWS = 5 MIN_ROWS = 2 DATA DIRECTORY = 'var/lib/engine/data/' COMMENT = 'this is the comment' ENGINE = InnoDB,
 PARTITION q2 VALUES LESS THAN (5) TABLESPACE = `innodb_file_per_table` MAX_ROWS = 5 MIN_ROWS = 2 DATA DIRECTORY = 'var/lib/engine/data/' COMMENT = 'this is the comment' ENGINE = InnoDB) */ |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

3. Run the same statement that only replace table_name. The statement failed to be executed. 

mysql> CREATE TABLE tb2 (a int DEFAULT NULL) ENGINE = InnoDB PARTITION BY RANGE (a) (
    ->   PARTITION q1 VALUES LESS THAN (2) ENGINE = InnoDB comment = 'this is the comment' MAX_ROWS = 5 MIN_ROWS = 2 DATA DIRECTORY = 'var/lib/engine/data'  TABLESPACE `innodb_file_per_table`, 
    ->   PARTITION q2 VALUES LESS THAN (5) ENGINE = InnoDB comment = 'this is the comment' MAX_ROWS = 5 MIN_ROWS = 2 DATA DIRECTORY = 'var/lib/engine/data'  TABLESPACE `innodb_file_per_table`
    -> );
ERROR 1525 (HY000): Incorrect path value: 'var/lib/engine/data'

Because relative path 'var/lib/engine/data' will be converted to absolute path '/workdir/mjxue/install/mysql/data/var/lib/engine/data' and this path contain mysql data home directory and it report error in check_partition_dirs function. So this statement return err.

Suggested fix:
This statement shouldn't succeed
[19 Mar 12:15] MySQL Verification Team
Hello hel le,

Thank you for the report and test case.

regards,
Umesh