Bug #60919 Prepared statements rule gives false positives.
Submitted: 19 Apr 2011 10:07 Modified: 23 Aug 2011 10:32
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.2 OS:Any
Assigned to: CPU Architecture:Any

[19 Apr 2011 10:07] Daniël van Eeden
Description:
Prepared Statements Not Being Used Effectively (v 1.2 *) 

The rule is:
(%Com_stmt_prepare% > 1) && ((100 - ((%Com_stmt_prepare% / (%Com_stmt_execute% + 1)) * 100)) < THRESHOLD)

The advice tells the user to monitor PREPARE and EXECUTE statements.

This rule could generate a critical event even if there are not PREPARE and/or EXECUTE statements.

According to the documentation the monitored variables are monitoring API calls and not SQL statements. There are status variables for SQL prepared statements.
http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html#statvar_Com_xxx

root@localhost [(none)]> SHOW GLOBAL STATUS LIKE 'Com_stmt%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Com_stmt_close          | 26837912 |
| Com_stmt_execute        | 26837932 |
| Com_stmt_fetch          | 0        |
| Com_stmt_prepare        | 26837962 |
| Com_stmt_reprepare      | 5        |
| Com_stmt_reset          | 22864717 |
| Com_stmt_send_long_data | 0        |
+-------------------------+----------+
7 rows in set (0.00 sec)

root@localhost [(none)]> SHOW GLOBAL STATUS LIKE 'Com_%_sql';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| Com_dealloc_sql | 5     |
| Com_execute_sql | 11    |
| Com_prepare_sql | 7     |
+-----------------+-------+
3 rows in set (0.00 sec)

How to repeat:
Schedule this rule.

Suggested fix:
Change rule from:
(%Com_stmt_prepare% > 1) && ((100 - ((%Com_stmt_prepare% / (%Com_stmt_execute% + 1)) * 100)) < THRESHOLD)

To:
(%Com_prepare_sql% > 1) && ((100 - ((%Com_prepare_sql% / (%Com_execute_sql% + 1)) * 100)) < THRESHOLD)

Alternative fix:
Update the advice to match the rule.
[19 Apr 2011 12:47] Valeriy Kravchuk
Thank you for the problem report.
[23 Aug 2011 10:32] Stefan Hinz
Added to 2.3.4 changelog:

The "Prepared Statements Not Being Used Effectively" rule was updated
to look at the status variables relating to SQL-based prepared
statements (PREPARE, EXECUTE, and so on) rather than API-based (for
example, mysql_stmt_prepare()) status counters.
[23 Aug 2011 10:32] 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/