Bug #67283 confusing records in innodb_cmp_per_index while a table was dropped

• Submitted: 18 Oct 2012 7:46
• Modified: 1 Mar 2013 15:56
• Reporter: zhai weixiang (OCA)
• Status: Closed
• Category: MySQL Server: Information schema
• Severity: S3 (Non-critical)
• Version: 5.6.7
• OS: Any
• CPU Architecture: Any

[18 Oct 2012 7:46] zhai weixiang

Description:
innodb_cmp_per_index  was introduced in MySQL5.6.7 to desplay compress information of every index using compression.

but if the table was dropped, then the database_name and table_name was set to 'unknown'

I think it's better to delete the record from page_zip_stat_per_index rather than output some confusing information 

How to repeat:
mysql> set global innodb_cmp_per_index_enabled=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from information_schema.innodb_cmp_per_index;
Empty set (0.00 sec)

mysql> create table t1 (a int primary key ,b int ) row_format=compressed key_block_size=4;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from information_schema.innodb_cmp_per_index;
+---------------+------------+------------+--------------+-----------------+---------------+----------------+-----------------+
| database_name | table_name | index_name | compress_ops | compress_ops_ok | compress_time | uncompress_ops | uncompress_time |
+---------------+------------+------------+--------------+-----------------+---------------+----------------+-----------------+
| test          | t1         | PRIMARY    |            1 |               1 |             0 |              0 |               0 |
+---------------+------------+------------+--------------+-----------------+---------------+----------------+-----------------+
1 row in set (0.00 sec)

mysql> drop table t1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from information_schema.innodb_cmp_per_index;
+---------------+------------+-------------+--------------+-----------------+---------------+----------------+-----------------+
| database_name | table_name | index_name  | compress_ops | compress_ops_ok | compress_time | uncompress_ops | uncompress_time |
+---------------+------------+-------------+--------------+-----------------+---------------+----------------+-----------------+
| unknown       | unknown    | index_id:27 |            1 |               1 |             0 |              0 |               0 |
+---------------+------------+-------------+--------------+-----------------+---------------+----------------+-----------------+
1 row in set (0.00 sec)

Suggested fix:
a rough patch
 
--- a/storage/innobase/handler/i_s.cc   2012-09-19 07:06:30.000000000 +0800
+++ b/storage/innobase/handler/i_s.cc   2012-10-18 15:38:35.728683184 +0800
@@ -1746,15 +1746,17 @@
                        field_store_string(fields[IDX_INDEX_NAME],
                                           index->name);
                } else {
-                       /* index not found */
-                       ut_snprintf(name, sizeof(name),
-                                   "index_id:" IB_ID_FMT, iter->first);
-                       field_store_string(fields[IDX_DATABASE_NAME],
-                                          "unknown");
-                       field_store_string(fields[IDX_TABLE_NAME],
-                                          "unknown");
-                       field_store_string(fields[IDX_INDEX_NAME],
-                                          name);
+           /*delete the record from page_zip_stat_per_index*/
+            mutex_exit(&dict_sys->mutex);
+
+            mutex_enter(&page_zip_stat_per_index_mutex);
+            page_zip_stat_per_index_t::iterator tmp_iter;
+            tmp_iter = page_zip_stat_per_index.find(iter->first);
+            page_zip_stat_per_index.erase(tmp_iter);     
+            mutex_exit(&page_zip_stat_per_index_mutex);
+            
+            mutex_enter(&dict_sys->mutex);
+            continue;
                }
 
                fields[IDX_COMPRESS_OPS]->store(

[18 Oct 2012 11:58] MySQL Verification Team

Thank you for the bug report.

[1 Mar 2013 15:56] Paul DuBois

Noted in 5.7.1 changelog.

If a table had rows in the INFORMATION_SCHEMA.INNODB_CMP_PER_INDEX
table, dropping the table did not remove those rows.