| Bug #33287 | Can't retrieve Partition Comment, if the Table has Subpartitioning | ||
|---|---|---|---|
| Submitted: | 17 Dec 2007 10:46 | Modified: | 17 Dec 2007 18:22 |
| Reporter: | Santo Leto | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Information schema | Severity: | S2 (Serious) |
| Version: | 5.1.22-rc | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | information_schema.partitions, partition comment | ||
[17 Dec 2007 18:22]
Susanne Ebrecht
Many thanks for writing a bug report. Verified as described. I only get the subpartion comment and not the partition comment.
[12 Jan 2012 20:52]
Mattias Jonsson
There is also a bug in that when specifying subpartitions with comments, the comment for the partitions is never recorded. easy to verify by: SHOW CREATE TABLE ts; Table Create Table ts CREATE TABLE `ts` ( `id` int(11) DEFAULT NULL, `purchased` date DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE ( YEAR(purchased)) SUBPARTITION BY HASH ( TO_DAYS(purchased)) (PARTITION p0 VALUES LESS THAN (1990) (SUBPARTITION s0 COMMENT = 'comment for s0' ENGINE = MyISAM, SUBPARTITION s1 COMMENT = 'comment for s1' ENGINE = MyISAM), PARTITION p1 VALUES LESS THAN (2000) (SUBPARTITION s2 COMMENT = 'comment for s2' ENGINE = MyISAM, SUBPARTITION s3 COMMENT = 'comment for s3' ENGINE = MyISAM), PARTITION p2 VALUES LESS THAN MAXVALUE (SUBPARTITION s4 COMMENT = 'comment for s4' ENGINE = MyISAM, SUBPARTITION s5 COMMENT = 'comment for s5' ENGINE = MyISAM)) */ No comments on p0, p1 or p2. The solution to fix the I_S.PARTITIONS will be to add a new field SUBPARTITOIN_COMMENT.
[12 Jan 2012 22:14]
Mattias Jonsson
It can be discussed if this is 'Not a bug': Let us look at the syntax of partition_definition (from the manual): http://dev.mysql.com/doc/refman/5.6/en/create-table.html And call all the below options for partition_storage_options: [[STORAGE] ENGINE [=] engine_name] [COMMENT [=] 'comment_text' ] [DATA DIRECTORY [=] 'data_dir'] [INDEX DIRECTORY [=] 'index_dir'] [MAX_ROWS [=] max_number_of_rows] [MIN_ROWS [=] min_number_of_rows] If specified on partition level, then it should be used as defaults for all subpartitions within that partition (I have not verified this!). If one specifies subpartitions, then all subpartitions needs to be specified. (i.e. not using SUBPARTITIONS N only but adds SUBPARTITION subpart_name [options]) Then the options from the partition will only be used if they are not specified for the subpartition. Which results in all given options on the partition level will be used if not overridden by the subpartition. So there are no meaning with recording them for the partitioning level, as they are only used as defaults for the subpartitions, and when the subpartitions are configured, they will have the correct options. I understand that the comment can be useful on both levels independently, but since it is handled just like the other options it is natural that it is not stored on partition level, but only as default for subpartitions. The diff for also storing the options given on partitioning level, when using named subpartitions is: === modified file 'sql/sql_partition.cc' --- sql/sql_partition.cc revid:tor.didriksen@oracle.com-20120111143530-5s zscbo4fo5ba3vj +++ sql/sql_partition.cc 2012-01-12 21:16:28 +0000 @@ -2602,6 +2602,8 @@ char *generate_partition_syntax(partitio } else { + if (show_partition_options) + err+= add_partition_options(fptr, part_elem); err+= add_string(fptr, "\n"); err+= add_space(fptr); err+= add_begin_parenthesis(fptr);
[12 Jan 2012 22:24]
Mattias Jonsson
And the diff for implementing SUBPARTITION_COMMENT is:
=== modified file 'sql/sql_show.cc'
--- sql/sql_show.cc revid:tor.didriksen@oracle.com-20120111143530-5szscbo4fo
5ba3vj
+++ sql/sql_show.cc 2012-01-12 15:44:20 +0000
@@ -5894,27 +5894,22 @@ static void store_schema_partitions_reco
}
if (part_elem)
{
- if (part_elem->part_comment)
- table->field[22]->store(part_elem->part_comment,
- strlen(part_elem->part_comment), cs);
- else
- table->field[22]->store(STRING_WITH_LEN(""), cs);
if (part_elem->nodegroup_id != UNDEF_NODEGROUP)
- table->field[23]->store((longlong) part_elem->nodegroup_id, TRUE);
+ table->field[24]->store((longlong) part_elem->nodegroup_id, TRUE);
else
- table->field[23]->store(STRING_WITH_LEN("default"), cs);
+ table->field[24]->store(STRING_WITH_LEN("default"), cs);
- table->field[24]->set_notnull();
+ table->field[25]->set_notnull();
if (part_elem->tablespace_name)
- table->field[24]->store(part_elem->tablespace_name,
+ table->field[25]->store(part_elem->tablespace_name,
strlen(part_elem->tablespace_name), cs);
else
{
char *ts= showing_table->s->tablespace;
if(ts)
- table->field[24]->store(ts, strlen(ts), cs);
+ table->field[25]->store(ts, strlen(ts), cs);
else
- table->field[24]->set_null();
+ table->field[25]->set_null();
}
}
return;
@@ -6164,6 +6159,13 @@ static int get_schema_partitions_record(
table->field[11]->set_notnull();
}
+ /* PARTITION_COMMENT */
+ if (part_elem->part_comment)
+ table->field[22]->store(part_elem->part_comment,
+ strlen(part_elem->part_comment), cs);
+ else
+ table->field[22]->store(STRING_WITH_LEN(""), cs);
+
if (part_elem->subpartitions.elements)
{
List_iterator<partition_element> sub_it(part_elem->subpartitions);
@@ -6178,7 +6180,14 @@ static int get_schema_partitions_record(
/* SUBPARTITION_ORDINAL_POSITION */
table->field[6]->store((longlong) ++subpart_pos, TRUE);
table->field[6]->set_notnull();
-
+
+ /* SUBPARTITION_COMMENT */
+ if (subpart_elem->part_comment)
+ table->field[23]->store(subpart_elem->part_comment,
+ strlen(subpart_elem->part_comment), cs);
+ else
+ table->field[23]->store(STRING_WITH_LEN(""), cs);
+
store_schema_partitions_record(thd, table, show_table, subpart_elem,
file, part_id);
part_id++;
@@ -6188,6 +6197,9 @@ static int get_schema_partitions_record(
}
else
{
+ /* SUBPARTITION_COMMENT */
+ table->field[23]->set_notnull();
+
store_schema_partitions_record(thd, table, show_table, part_elem,
file, part_id);
part_id++;
@@ -7740,6 +7752,7 @@ ST_FIELD_INFO partitions_fields_info[]=
{"CHECKSUM", 21 , MYSQL_TYPE_LONGLONG, 0,
(MY_I_S_MAYBE_NULL | MY_I_S_UNSIGNED), 0, OPEN_FULL_TABLE},
{"PARTITION_COMMENT", 80, MYSQL_TYPE_STRING, 0, 0, 0, OPEN_FULL_TABLE},
+ {"SUBPARTITION_COMMENT", 80, MYSQL_TYPE_STRING, 0, 0, 0, OPEN_FULL_TABLE},
{"NODEGROUP", 12 , MYSQL_TYPE_STRING, 0, 0, 0, OPEN_FULL_TABLE},
{"TABLESPACE_NAME", NAME_CHAR_LEN, MYSQL_TYPE_STRING, 0, 1, 0,
OPEN_FULL_TABLE},
Description: The following contradiction occurs: if you have a partition which has one or more subpartitions, you can set a partition comment but you can't retrieve it. How to repeat: Test 1) - USING SUBPARTITIONING DROP DATABASE IF EXISTS `partition_contradiction`; CREATE DATABASE `partition_contradiction`; USE `partition_contradiction`; CREATE TABLE `ts` (id INT, purchased DATE) PARTITION BY RANGE( YEAR(purchased) ) SUBPARTITION BY HASH( TO_DAYS(purchased) ) ( PARTITION p0 VALUES LESS THAN (1990) COMMENT = 'comment for p0'( SUBPARTITION s0 COMMENT = 'comment for s0', SUBPARTITION s1 COMMENT = 'comment for s1' ), PARTITION p1 VALUES LESS THAN (2000) COMMENT = 'comment for p1'( SUBPARTITION s2 COMMENT = 'comment for s2', SUBPARTITION s3 COMMENT = 'comment for s3' ), PARTITION p2 VALUES LESS THAN MAXVALUE COMMENT = 'comment for p2' ( SUBPARTITION s4 COMMENT = 'comment for s4', SUBPARTITION s5 COMMENT = 'comment for s5' ) ); SELECT `PARTITION_NAME`,`PARTITION_COMMENT`, `PARTITION_ORDINAL_POSITION`, `PARTITION_METHOD`, `PARTITION_EXPRESSION`, `PARTITION_DESCRIPTION`, SUM(`TABLE_ROWS`) AS `Total Partition Rows`, `AVG_ROW_LENGTH`, SUM(`DATA_LENGTH`) AS `Total Data Length`,`MAX_DATA_LENGTH`, SUM(`DATA_FREE`) AS `Total Data Free`, `NODEGROUP`, `TABLESPACE_NAME` FROM `INFORMATION_SCHEMA`.`PARTITIONS` WHERE `TABLE_SCHEMA` = 'partition_contradiction' AND `TABLE_NAME` = 'ts' AND `PARTITION_NAME` = 'p1' GROUP BY `PARTITION_DESCRIPTION`\G SELECT * FROM `INFORMATION_SCHEMA`.`PARTITIONS` WHERE `TABLE_SCHEMA` = 'partition_contradiction' AND `TABLE_NAME` = 'ts' AND `SUBPARTITION_NAME` = 's5'\G Retrieving information about a partition: mysql> SELECT `PARTITION_NAME`,`PARTITION_COMMENT`, `PARTITION_ORDINAL_POSITION` , `PARTITION_METHOD`, `PARTITION_EXPRESSION`, `PARTITION_DESCRIPTION`, SUM(`TABL E_ROWS`) AS `Total Partition Rows`, `AVG_ROW_LENGTH`, SUM(`DATA_LENGTH`) AS `Tot al Data Length`,`MAX_DATA_LENGTH`, SUM(`DATA_FREE`) AS `Total Data Free`, `NODEG ROUP`, `TABLESPACE_NAME` FROM `INFORMATION_SCHEMA`.`PARTITIONS` WHERE `TABLE_SCH EMA` = 'partition_contradiction' AND `TABLE_NAME` = 'ts' AND `PARTITION_NAME` = 'p1' GROUP BY `PARTITION_DESCRIPTION`\G *************************** 1. row *************************** PARTITION_NAME: p1 PARTITION_COMMENT: comment for s2 PARTITION_ORDINAL_POSITION: 2 PARTITION_METHOD: RANGE PARTITION_EXPRESSION: YEAR(purchased) PARTITION_DESCRIPTION: 2000 Total Partition Rows: 0 AVG_ROW_LENGTH: 0 Total Data Length: 0 MAX_DATA_LENGTH: 2251799813685247 Total Data Free: 0 NODEGROUP: default TABLESPACE_NAME: NULL 1 row in set (0.02 sec) Retrieving information about a subpartition: SELECT * FROM `INFORMATION_SCHEMA`.`PARTITIONS` WHERE `TABLE_SCHEMA` = 'partition_contradiction' AND `TABLE_NAME` = 'ts' AND `SUBPARTITION_NAME` = 's5'\G mysql> SELECT * FROM `INFORMATION_SCHEMA`.`PARTITIONS` WHERE `TABLE_SCHEMA` = 'p artition_contradiction' AND `TABLE_NAME` = 'ts' AND `SUBPARTITION_NAME` = 's5'\G *************************** 1. row *************************** TABLE_CATALOG: NULL TABLE_SCHEMA: partition_contradiction TABLE_NAME: ts PARTITION_NAME: p2 SUBPARTITION_NAME: s5 PARTITION_ORDINAL_POSITION: 3 SUBPARTITION_ORDINAL_POSITION: 2 PARTITION_METHOD: RANGE SUBPARTITION_METHOD: HASH PARTITION_EXPRESSION: YEAR(purchased) SUBPARTITION_EXPRESSION: TO_DAYS(purchased) PARTITION_DESCRIPTION: MAXVALUE TABLE_ROWS: 0 AVG_ROW_LENGTH: 0 DATA_LENGTH: 0 MAX_DATA_LENGTH: 2251799813685247 INDEX_LENGTH: 1024 DATA_FREE: 0 CREATE_TIME: 2007-12-17 10:59:32 UPDATE_TIME: 2007-12-17 10:59:34 CHECK_TIME: NULL CHECKSUM: NULL PARTITION_COMMENT: comment for s5 NODEGROUP: default TABLESPACE_NAME: NULL 1 row in set (0.01 sec) Test 2) - SIMPLE PARTITIONING DROP DATABASE IF EXISTS `partition_contradiction_simple`; CREATE DATABASE `partition_contradiction_simple`; USE `partition_contradiction_simple`; CREATE TABLE `ts_simple` (id INT, purchased DATE) PARTITION BY RANGE( YEAR(purchased) ) SUBPARTITION BY HASH( TO_DAYS(purchased) ) ( PARTITION p0 VALUES LESS THAN (1990) COMMENT = 'comment for p0', PARTITION p1 VALUES LESS THAN (2000) COMMENT = 'comment for p1', PARTITION p2 VALUES LESS THAN MAXVALUE COMMENT = 'comment for p2' ); SELECT `PARTITION_NAME`, `PARTITION_COMMENT`, `PARTITION_ORDINAL_POSITION`, `PARTITION_METHOD`, `PARTITION_EXPRESSION`, `PARTITION_DESCRIPTION`, `TABLE_ROWS`, `AVG_ROW_LENGTH`, `DATA_LENGTH`, `MAX_DATA_LENGTH`, `INDEX_LENGTH`, `DATA_FREE`, `CREATE_TIME`, `UPDATE_TIME`, `CHECK_TIME`, `CHECKSUM`, `NODEGROUP`, `TABLESPACE_NAME` FROM `INFORMATION_SCHEMA`.`PARTITIONS` WHERE `TABLE_SCHEMA` = 'partition_contradiction_simple' AND `TABLE_NAME` = 'ts_simple' AND `PARTITION_NAME` = 'p1'\G Retrieving information about a partition: mysql> SELECT `PARTITION_NAME`, `PARTITION_COMMENT`, `PARTITION_ORDINAL_POSITION `, `PARTITION_METHOD`, `PARTITION_EXPRESSION`, `PARTITION_DESCRIPTION`, `TABLE_R OWS`, `AVG_ROW_LENGTH`, `DATA_LENGTH`, `MAX_DATA_LENGTH`, `INDEX_LENGTH`, `DATA_ FREE`, `CREATE_TIME`, `UPDATE_TIME`, `CHECK_TIME`, `CHECKSUM`, `NODEGROUP`, `TAB LESPACE_NAME` FROM `INFORMATION_SCHEMA`.`PARTITIONS` WHERE `TABLE_SCHEMA` = 'par tition_contradiction_simple' AND `TABLE_NAME` = 'ts_simple' AND `PARTITION_NAME` = 'p1'\G *************************** 1. row *************************** PARTITION_NAME: p1 PARTITION_COMMENT: comment for p1 PARTITION_ORDINAL_POSITION: 2 PARTITION_METHOD: RANGE PARTITION_EXPRESSION: YEAR(purchased) PARTITION_DESCRIPTION: 2000 TABLE_ROWS: 0 AVG_ROW_LENGTH: 0 DATA_LENGTH: 0 MAX_DATA_LENGTH: 2251799813685247 INDEX_LENGTH: 1024 DATA_FREE: 0 CREATE_TIME: 2007-12-17 11:07:10 UPDATE_TIME: 2007-12-17 11:07:12 CHECK_TIME: NULL CHECKSUM: NULL NODEGROUP: default TABLESPACE_NAME: NULL 1 row in set (0.00 sec) Suggested fix: In the case of simple partitioning, the server adds a row in information_schema.partitions for each partition and you can retrieve metadata. In the case of partitioning with subpartitioning, the server adds a row in information_schema.partitions for each subpartition. Thus, if you have a partition with 2 subpartitions, the server adds just 2 rows, relating to the subpartitions and it doesn't store the partition comment (and of course it can't). A simple solution (and probably an elegant way) is adding 3 rows: one for the partition and 2 for the subpartitions. But you shoud consider that, in this way, you do NOT preserve backward compatibility. Maybe, you should find a solution that preserve backward compatibility. For example (but probably that's not an elegant way), you could add and extra-column in information_schema.partitions. Suppose you add a `PARENT_PARTITION_COMMENT` column. Thus you could have something like: mysql> SELECT `PARTITION_NAME`,`PARTITION_COMMENT`, `PARENT_PARTITION_COMMENT`, `PARTITION_ORDINAL_POSITION`, `PARTITION_METHOD`, `PARTITION_EXPRESSION`, `PARTITION_DESCRIPTION`, SUM(`TABLE_ROWS`) AS `Total Partition Rows`, `AVG_ROW_LENGTH`, SUM(`DATA_LENGTH`) AS `Total Data Length`,`MAX_DATA_LENGTH`, SUM(`DATA_FREE`) AS `Total Data Free`, `NODEGROUP`, `TABLESPACE_NAME` FROM `INFORMATION_SCHEMA`.`PARTITIONS` WHERE `TABLE_SCHEMA` = 'partition_contradiction' AND `TABLE_NAME` = 'ts' AND `PARTITION_NAME` = 'p1' GROUP BY `PARTITION_DESCRIPTION`\G *************************** 1. row *************************** PARTITION_NAME: p1 PARTITION_COMMENT: comment for s2 PARENT_PARTITION_COMMENT: comment for p1 PARTITION_ORDINAL_POSITION: 2 PARTITION_METHOD: RANGE PARTITION_EXPRESSION: YEAR(purchased) PARTITION_DESCRIPTION: 2000 Total Partition Rows: 0 AVG_ROW_LENGTH: 0 Total Data Length: 0 MAX_DATA_LENGTH: 2251799813685247 Total Data Free: 0 NODEGROUP: default TABLESPACE_NAME: NULL 1 row in set (0.02 sec)