Description:
The output formats of SHOW CREATE TABLE command after CREATE TABLE and ALTER TABLE are inconsistent.
The difference is the path of data directory. The output after CREATE TABLE is with extra OS_PATH_SEPARATOR '/'. The output after ALTER TABLE doesn't have the extra '/' symbol.
How to repeat:
CREATE TABLE emp (
id INT NOT NULL,
store_name VARCHAR(30),
parts VARCHAR(30),
store_id INT
)
PARTITION BY LIST (store_id) (
PARTITION east VALUES IN (10, 20, 30)
DATA DIRECTORY 'MYSQL_TMP_DIR/alt_dir_east',
PARTITION north VALUES IN (40, 50, 60)
DATA DIRECTORY 'MYSQL_TMP_DIR/alt_dir_north',
PARTITION west VALUES IN (70, 80, 100)
DATA DIRECTORY 'MYSQL_TMP_DIR/alt_dir_west'
);
mysql-8.0.26 > SHOW CREATE TABLE emp\G
*************************** 1. row ***************************
Table: emp
Create Table: CREATE TABLE `emp` (
`id` int NOT NULL,
`store_name` varchar(30) DEFAULT NULL,
`parts` varchar(30) DEFAULT NULL,
`store_id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY LIST (`store_id`)
(PARTITION east VALUES IN (10,20,30) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_east/' ENGINE = InnoDB,
PARTITION north VALUES IN (40,50,60) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_north/' ENGINE = InnoDB,
PARTITION west VALUES IN (70,80,100) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_west/' ENGINE = InnoDB) */
The above output has an extra '/' at the end of data directory path of partitions, which is inconsistent with the user input.
ALTER TABLE emp DROP PARTITION east;
If we execute the `ALTER TABLE emp DROP PARTITION east;` command or restart the server, and SHOW CREATE TABLE again, the output will be different. The data directory path is without tail '/'.
mysql-8.0.26 > SHOW CREATE TABLE emp\G
*************************** 1. row ***************************
Table: emp
Create Table: CREATE TABLE `emp` (
`id` int NOT NULL,
`store_name` varchar(30) DEFAULT NULL,
`parts` varchar(30) DEFAULT NULL,
`store_id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY LIST (`store_id`)
(PARTITION north VALUES IN (40,50,60) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_north' ENGINE = InnoDB,
PARTITION west VALUES IN (70,80,100) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_west' ENGINE = InnoDB) */
Suggested fix:
The root cause is that innobase will automatically add '/' to data directory path in create_table_info_t::set_remote_path_flags() during creating table process and doesn't restore this after creating.
When executing `SHOW CREATE TABLE` command, it will update partition_element::data_file_name with the modified path in ha_innopart::update_part_elem() and make the output inconsistent.
After `ALTER TABLE` command or restarting the server, innobase will read the path from data directory and output the right result.
Description: The output formats of SHOW CREATE TABLE command after CREATE TABLE and ALTER TABLE are inconsistent. The difference is the path of data directory. The output after CREATE TABLE is with extra OS_PATH_SEPARATOR '/'. The output after ALTER TABLE doesn't have the extra '/' symbol. How to repeat: CREATE TABLE emp ( id INT NOT NULL, store_name VARCHAR(30), parts VARCHAR(30), store_id INT ) PARTITION BY LIST (store_id) ( PARTITION east VALUES IN (10, 20, 30) DATA DIRECTORY 'MYSQL_TMP_DIR/alt_dir_east', PARTITION north VALUES IN (40, 50, 60) DATA DIRECTORY 'MYSQL_TMP_DIR/alt_dir_north', PARTITION west VALUES IN (70, 80, 100) DATA DIRECTORY 'MYSQL_TMP_DIR/alt_dir_west' ); mysql-8.0.26 > SHOW CREATE TABLE emp\G *************************** 1. row *************************** Table: emp Create Table: CREATE TABLE `emp` ( `id` int NOT NULL, `store_name` varchar(30) DEFAULT NULL, `parts` varchar(30) DEFAULT NULL, `store_id` int DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!50100 PARTITION BY LIST (`store_id`) (PARTITION east VALUES IN (10,20,30) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_east/' ENGINE = InnoDB, PARTITION north VALUES IN (40,50,60) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_north/' ENGINE = InnoDB, PARTITION west VALUES IN (70,80,100) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_west/' ENGINE = InnoDB) */ The above output has an extra '/' at the end of data directory path of partitions, which is inconsistent with the user input. ALTER TABLE emp DROP PARTITION east; If we execute the `ALTER TABLE emp DROP PARTITION east;` command or restart the server, and SHOW CREATE TABLE again, the output will be different. The data directory path is without tail '/'. mysql-8.0.26 > SHOW CREATE TABLE emp\G *************************** 1. row *************************** Table: emp Create Table: CREATE TABLE `emp` ( `id` int NOT NULL, `store_name` varchar(30) DEFAULT NULL, `parts` varchar(30) DEFAULT NULL, `store_id` int DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!50100 PARTITION BY LIST (`store_id`) (PARTITION north VALUES IN (40,50,60) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_north' ENGINE = InnoDB, PARTITION west VALUES IN (70,80,100) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_west' ENGINE = InnoDB) */ Suggested fix: The root cause is that innobase will automatically add '/' to data directory path in create_table_info_t::set_remote_path_flags() during creating table process and doesn't restore this after creating. When executing `SHOW CREATE TABLE` command, it will update partition_element::data_file_name with the modified path in ha_innopart::update_part_elem() and make the output inconsistent. After `ALTER TABLE` command or restarting the server, innobase will read the path from data directory and output the right result.