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:S3 (Non-critical)
Version:5.7.10 OS:Microsoft Windows (Windows 7)
Assigned to: Marc Alff CPU Architecture:Any

[27 Jan 2016 11:11] d sech
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.
[27 Jan 2016 11:25] d sech
PHP sample script

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

[28 Jan 2016 8:26] Umesh Shastry
Hello Dorian,

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

Thanks,
Umesh
[28 Jan 2016 8:26] Umesh Shastry
Also, https://bugs.mysql.com/bug.php?id=69218
[28 Jan 2016 8:27] Umesh Shastry
Also, http://bugs.mysql.com/bug.php?id=76284
[31 May 2017 9:51] Anushree Prakash B
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.