MySQL Bugs: #80173: Prepared statements created through C API not logged in performance schema

Bug #80173	Prepared statements created through C API not logged in performance schema
Submitted:	27 Jan 2016 11:11	Modified:	28 Jan 2016 8:26
Reporter:	d sech	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Performance Schema	Severity:	S4 (Feature request)
Version:	5.7.10	OS:	Windows (Windows 7)
Assigned to:	Marc ALFF	CPU Architecture:	Any

Description:
When using PHP mysqli, the queries in prepared statements are not being logged in performance_schema.events_statements_summary_by_digest on MySQL 5.7.10 default installation.

The docs suggest this should work starting with 5.7.4
http://dev.mysql.com/doc/refman/5.7/en/prepared-statements-instances-table.html

In `setup_instruments` both statement/com/Execute and statement/com/Prepare have ENABLED and TIMED set to YES (as per default installation).

How to repeat:
Run the following PHP script, which uses the MySQL C API

<?php

$db_host = '127.0.0.1';
$db_user = 'root';
$db_pass = '';
$db_name = 'mysql';

$conn = mysqli_connect($db_host, $db_user, $db_pass, $db_name);

/*
 * Regular queries are properly logged in performance_schema.events_statements_summary_by_digest
 * DIGEST_TEXT: SELECT * FROM `mysql` . `user` 
 */
//$conn->query('SELECT * FROM mysql.user');

/*
 * C API statements not logged
 * No entry created in performance_schema.events_statements_summary_by_digest
 */
$stmt = $conn->stmt_init();
$stmt->prepare('SELECT * FROM mysql.user');
$stmt->execute();

/*
 * The statement appears in `prepared_statement_instances` before closing it
 */
sleep(10);
$stmt->close();

?>

Expected result:
An entry in `events_statements_summary_by_digest` WITH DIGEST_TEXT: SELECT * FROM `mysql` . `user` should have been created.

Actual result:
No entry in `events_statements_summary_by_digest`, as if the statement was never executed.

PHP sample script

Attachment: prep.php (application/octet-stream, text), 698 bytes.

Hello Dorian,

Thank you for the report and test case.
Verified as described with 5.7.10 build.

Thanks,
Umesh

Also, https://bugs.mysql.com/bug.php?id=69218

Also, http://bugs.mysql.com/bug.php?id=76284

Posted by developer:
 
Analysis: After comparing the current behaviour of instrumentation
of prepared statements from SQL prompt and from C API, we figured
out that in both these cases, the instrumentation of the actual
query used in prepared statement is not being done. Although
for prepared statements from SQL prompt, only some top level
statements are added to digest, the present aggregated statistics 
will not help much in monitoring because the query part is 
being replaced by a placeholder.

Solution: It requires calculating and saving the digest of 
a non-top-level statement particularly the query part of the
statement being prepared. The solution also needs to make sure
that the behaviour is consistent for prepared statements 
from both the SQL prompt and the C API.

After further analysis and discussions with the development team, 
we have come to a conclusion that it's essentially a feature 
request as prepared statements are instrumented in a different way
in the present design. Right now we are instrumenting the 
prepared statements, but not *together* with the non-prepared ones
This would require some design level changes in the performance
schema code and hence does not qualify to go into the GA versions.