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>
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>