Bug #72945 Partitions are opened for information_schema.partitions queries

• Submitted: 10 Jun 2014 21:24
• Modified: 11 Jun 2014 15:03
• Reporter: Domas Mituzas
• Status: Verified
• Category: MySQL Server: Partitions
• Severity: S3 (Non-critical)
• Version: 5.*
• OS: Any
• CPU Architecture: Any

[10 Jun 2014 21:24] Domas Mituzas

Description:
mysql fills statistics for partitions when information_schema.partitions table is queried.
problem is, quite often nobody wants those statistics, and it is extremely costly to read them. 

How to repeat:
select * from information_schema.partitions

Suggested fix:
diff --git a/sql/sql_show.cc b/sql/sql_show.cc
index b06cfed..93cf203 100644
--- a/sql/sql_show.cc
+++ b/sql/sql_show.cc
@@ -5973,7 +5973,10 @@ static void store_schema_partitions_record(THD *thd, TABLE *schema_table,
   CHARSET_INFO *cs= system_charset_info;
   PARTITION_STATS stat_info;
   MYSQL_TIME time;
-  file->get_dynamic_partition_info(&stat_info, part_id);
+
+  if (file)
+      file->get_dynamic_partition_info(&stat_info, part_id);
+
   table->field[0]->store(STRING_WITH_LEN("def"), cs);
   table->field[12]->store((longlong) stat_info.records, TRUE);
   table->field[13]->store((longlong) stat_info.mean_rec_length, TRUE);
@@ -6006,7 +6009,7 @@ static void store_schema_partitions_record(THD *thd, TABLE *schema_table,
     table->field[20]->store_time(&time);
     table->field[20]->set_notnull();
   }
-  if (file->ha_table_flags() & (ulong) HA_HAS_CHECKSUM)
+  if (file && file->ha_table_flags() & (ulong) HA_HAS_CHECKSUM)
   {
     table->field[21]->store((longlong) stat_info.check_sum, TRUE);
     table->field[21]->set_notnull();
@@ -7873,22 +7876,22 @@ ST_FIELD_INFO triggers_fields_info[]=
 
 ST_FIELD_INFO partitions_fields_info[]=
 {
-  {"TABLE_CATALOG", FN_REFLEN, MYSQL_TYPE_STRING, 0, 0, 0, OPEN_FULL_TABLE},
-  {"TABLE_SCHEMA",NAME_CHAR_LEN, MYSQL_TYPE_STRING, 0, 0, 0, OPEN_FULL_TABLE},
-  {"TABLE_NAME", NAME_CHAR_LEN, MYSQL_TYPE_STRING, 0, 0, 0, OPEN_FULL_TABLE},
-  {"PARTITION_NAME", NAME_CHAR_LEN, MYSQL_TYPE_STRING, 0, 1, 0, OPEN_FULL_TABLE},
+  {"TABLE_CATALOG", FN_REFLEN, MYSQL_TYPE_STRING, 0, 0, 0, OPEN_FRM_ONLY},
+  {"TABLE_SCHEMA",NAME_CHAR_LEN, MYSQL_TYPE_STRING, 0, 0, 0, OPEN_FRM_ONLY},
+  {"TABLE_NAME", NAME_CHAR_LEN, MYSQL_TYPE_STRING, 0, 0, 0, OPEN_FRM_ONLY},
+  {"PARTITION_NAME", NAME_CHAR_LEN, MYSQL_TYPE_STRING, 0, 1, 0, OPEN_FRM_ONLY},
   {"SUBPARTITION_NAME", NAME_CHAR_LEN, MYSQL_TYPE_STRING, 0, 1, 0,
-   OPEN_FULL_TABLE},
+   OPEN_FRM_ONLY},
   {"PARTITION_ORDINAL_POSITION", 21 , MYSQL_TYPE_LONGLONG, 0,
-   (MY_I_S_MAYBE_NULL | MY_I_S_UNSIGNED), 0, OPEN_FULL_TABLE},
+   (MY_I_S_MAYBE_NULL | MY_I_S_UNSIGNED), 0, OPEN_FRM_ONLY},
   {"SUBPARTITION_ORDINAL_POSITION", 21 , MYSQL_TYPE_LONGLONG, 0,
-   (MY_I_S_MAYBE_NULL | MY_I_S_UNSIGNED), 0, OPEN_FULL_TABLE},
-  {"PARTITION_METHOD", 18, MYSQL_TYPE_STRING, 0, 1, 0, OPEN_FULL_TABLE},
-  {"SUBPARTITION_METHOD", 12, MYSQL_TYPE_STRING, 0, 1, 0, OPEN_FULL_TABLE},
-  {"PARTITION_EXPRESSION", 65535, MYSQL_TYPE_STRING, 0, 1, 0, OPEN_FULL_TABLE},
+   (MY_I_S_MAYBE_NULL | MY_I_S_UNSIGNED), 0, OPEN_FRM_ONLY},
+  {"PARTITION_METHOD", 18, MYSQL_TYPE_STRING, 0, 1, 0, OPEN_FRM_ONLY},
+  {"SUBPARTITION_METHOD", 12, MYSQL_TYPE_STRING, 0, 1, 0, OPEN_FRM_ONLY},
+  {"PARTITION_EXPRESSION", 65535, MYSQL_TYPE_STRING, 0, 1, 0, OPEN_FRM_ONLY},
   {"SUBPARTITION_EXPRESSION", 65535, MYSQL_TYPE_STRING, 0, 1, 0,
-   OPEN_FULL_TABLE},
-  {"PARTITION_DESCRIPTION", 65535, MYSQL_TYPE_STRING, 0, 1, 0, OPEN_FULL_TABLE},
+   OPEN_FRM_ONLY},
+  {"PARTITION_DESCRIPTION", 65535, MYSQL_TYPE_STRING, 0, 1, 0, OPEN_FRM_ONLY},
   {"TABLE_ROWS", 21 , MYSQL_TYPE_LONGLONG, 0, MY_I_S_UNSIGNED, 0,
    OPEN_FULL_TABLE},
   {"AVG_ROW_LENGTH", 21 , MYSQL_TYPE_LONGLONG, 0, MY_I_S_UNSIGNED, 0,
@@ -7906,10 +7909,10 @@ ST_FIELD_INFO partitions_fields_info[]=
   {"CHECK_TIME", 0, MYSQL_TYPE_DATETIME, 0, 1, 0, OPEN_FULL_TABLE},
   {"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},
-  {"NODEGROUP", 12 , MYSQL_TYPE_STRING, 0, 0, 0, OPEN_FULL_TABLE},
+  {"PARTITION_COMMENT", 80, MYSQL_TYPE_STRING, 0, 0, 0, OPEN_FRM_ONLY},
+  {"NODEGROUP", 12 , MYSQL_TYPE_STRING, 0, 0, 0, OPEN_FRM_ONLY},
   {"TABLESPACE_NAME", NAME_CHAR_LEN, MYSQL_TYPE_STRING, 0, 1, 0,
-   OPEN_FULL_TABLE},
+   OPEN_FRM_ONLY},
   {0, 0, MYSQL_TYPE_STRING, 0, 0, 0, SKIP_OPEN_TABLE}
 };

[10 Jun 2014 21:50] Domas Mituzas

disregard the patch for now, it has some Problems

[10 Jun 2014 23:51] Domas Mituzas

a patch that actually works: http://p.defau.lt/?1eMAUlYKgwI0_W2vdFBotw ;-)

[11 Jun 2014 15:03] MySQL Verification Team

Thank you for the bug report.

[2 Nov 2018 9:41] Gopal Shankar

Posted by developer:
 
This problem would not be seen on MySQL 8.0.
In MySQL 8.0, the I_S.PARTITIONS is a system view over data dictionary tables.
This means that I_S.PARTITIONS view is processed by optimizer. If user does
not request a view column reading a dynamic statistic, then the optimizer excludes
respective view column from query projection and hence we do not invoke
storage engine to read the statistics.