Bug #60695 False positives for tables w/o keys for 5.5 with perf schema
Submitted: 30 Mar 2011 8:24 Modified: 23 Aug 2011 10:29
Reporter: Daniël van Eeden Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Enterprise Monitor: Advisors/Rules Severity:S3 (Non-critical)
Version:2.3.1 OS:Any
Assigned to: CPU Architecture:Any
Tags: Contribution, patch
Triage: Needs Triage: D3 (Medium)

[30 Mar 2011 8:24] Daniël van Eeden
Description:
The "Tables Found with No Primary or Unique Keys" (v 1.2) gives false positives for the performance_schema in 5.5.

------------------------------------------------
Advice
Investigate why your database has tables without primary or unique keys. Add such keys where appropriate.

Here is the (possibly truncated) list of tables without primary or unique keys:

    * performance_schema.cond_instances,
    * performance_schema.events_waits_current,
    * performance_schema.events_waits_history,
    * performance_schema.events_waits_history_long,
    * performance_schema.events_waits_summary_by_instance,
    * performance_schema.events_waits_summary_by_thread_by_event_name,
    * performance_schema.events_waits_summary_global_by_event_name,
    * performance_schema.file_instances,
    * performance_schema.file_summary_by_event_name,
    * performance_schema.file_summary_by_instance,
    * performance_schema.mutex_instances,
    * performance_schema.performance_timers,
    * performance_schema.rwlock_instances,
    * performance_schema.setup_consumers,
    * performance_schema.setup_instruments,
    * performance_schema.setup_timers,
    * performance_schema.threads
------------------------------------------------

The recommended action is not complete. It only helps you to find the tables w/o keys. It should also recommend something like 'ALTER TABLE `test` ADD PRIMARY KEY (`id`)'
------------------------------------------------
Recommended Action
SELECT t.table_schema, t.table_name
FROM information_schema.tables t
LEFT JOIN information_schema.table_constraints c
ON (t.table_schema = c.table_schema
AND t.table_name = c.table_name
AND c.constraint_type IN ('PRIMARY KEY','UNIQUE'))
WHERE t.table_schema NOT IN ('mysql','information_schema')
AND t.engine NOT IN ('ARCHIVE','FEDERATED')
AND c.table_name IS NULL;
------------------------------------------------

How to repeat:
Schedule the advisor on a host running 5.5.8

Suggested fix:
Ignore the performance_schema for this check.

--- items-mysql-monitor.xml.old	2011-03-30 10:23:24.000000000 +0200
+++ items-mysql-monitor.xml	2011-03-30 10:24:08.000000000 +0200
@@ -126,7 +126,7 @@
   ON (t.table_schema = c.table_schema
   AND t.table_name = c.table_name
   AND c.constraint_type IN ('PRIMARY KEY','UNIQUE'))
-WHERE t.table_schema NOT IN ('mysql','information_schema')
+WHERE t.table_schema NOT IN ('mysql','information_schema','performance_schema')
   AND t.engine NOT IN ('ARCHIVE','FEDERATED')
   AND c.table_name IS NULL]]></query>
 	</class>
[31 Mar 2011 10:40] Valeriy Kravchuk
Thank you for the bug report and code contributed.
[23 Aug 2011 10:29] Stefan Hinz
Thank you for your bug report. This issue has already been fixed in the latest released version of that product, which you can download at

  http://www.mysql.com/downloads/
[23 Aug 2011 10:30] Stefan Hinz
Added to 2.3.4 changelog:

The "Tables Found with No Primary or Unique Keys" rule gave incorrect
warnings for the performance_schema tables in MySQL 5.5.