2

I am seeing many errors like the following for my MySQL instance. It appears that it is complaining about an index that does not exist anymore. This is an InnoDB table. What do I need to do to update the innodb_index_stats to reflect the new indexes? I tried ANALYZE table goal_summary but that did not change anything.

2017-07-14 15:35:21 2b6d11b47700 InnoDB: Ignoring strange row from mysql.innodb_index_stats WHERE database_name = 'network_00576' AND table_name = 'goal_summary' AND index_name = 'goal_summary_id' AND stat_name = 'n_diff_pfx03'; because stat_name is out of range, the index has 2 unique columns

More info:

select * from mysql.innodb_index_stats
 where database_name="network_00576"
   and table_name="goal_summary"
   and index_name="goal_summary_id"
   and stat_name = 'n_diff_pfx03';

Result:

+---------------+--------------+-----------------+---------------------+--------------+------------+-------------+------------------------------+
| database_name | table_name   | index_name      | last_update         | stat_name    | stat_value | sample_size | stat_description             |
+---------------+--------------+-----------------+---------------------+--------------+------------+-------------+------------------------------+
| network_00576 | goal_summary | goal_summary_id | 2017-02-01 22:32:12 | n_diff_pfx03 |        407 |           1 | goal_summary_id,offerid,date |
+---------------+--------------+-----------------+---------------------+--------------+------------+-------------+------------------------------+

And:

show keys from goal_summary where Key_name="goal_summary_id";

Result:

+--------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table        | Non_unique | Key_name        | Seq_in_index | Column_name     | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| goal_summary |          1 | goal_summary_id |            1 | goal_summary_id | A         |         694 |     NULL | NULL   |      | BTREE      |         |               |
+--------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
RDFozz
  • 11,631
  • 4
  • 24
  • 38
Rob L
  • 21
  • 4
  • 1
    ever figure this out? – mbourgon Oct 11 '17 at 15:24
  • No, never got this figured out and kind of got put on the back burner due to higher priority concerns – Rob L Oct 12 '17 at 15:59
  • I had the same problem in my Aurora MySQL 5.6 cluster with the Aurora 1.23.1 patch installed. It existed for many older tables ~1 year old but also for some new tables created a few days ago. The only way I found to fix it was to drop the offending key (index) and recreate it. It had to be done in two separate commands so I couldn't have ADD KEY and DROP KEY in the same ALTER statement. Once I did it, the bad statistic was removed and the error disappeared. – Alf47 Apr 16 '21 at 16:41

0 Answers0