Bug #69218 P_S doesn't record SQL or digest for prepared statements
Submitted: 13 May 2013 17:57
Reporter: Todd Farmer (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Prepared statements Severity:S2 (Serious)
Version:5.6.11 OS:Any
Assigned to: CPU Architecture:Any

[13 May 2013 17:57] Todd Farmer
Description:
PERFORMANCE_SCHEMA does not include digest or SQL text for prepared statements (COM_PREPARE and COM_EXECUTE protocol commands).

mysql> SELECT * FROM events_statements_history_long\G
...
*************************** 8. row ***************************
              THREAD_ID: 592
               EVENT_ID: 16
           END_EVENT_ID: 16
             EVENT_NAME: statement/com/Prepare
                 SOURCE: mysqld.cc:923
            TIMER_START: 504269015511265465
              TIMER_END: 504269015617144800
             TIMER_WAIT: 105879335
              LOCK_TIME: 0
               SQL_TEXT: NULL
                 DIGEST: NULL
            DIGEST_TEXT: NULL
         CURRENT_SCHEMA: test
            OBJECT_TYPE: NULL
          OBJECT_SCHEMA: NULL
            OBJECT_NAME: NULL
  OBJECT_INSTANCE_BEGIN: NULL
            MYSQL_ERRNO: 0
      RETURNED_SQLSTATE: NULL
           MESSAGE_TEXT: NULL
                 ERRORS: 0
               WARNINGS: 0
          ROWS_AFFECTED: 0
              ROWS_SENT: 0
          ROWS_EXAMINED: 0
CREATED_TMP_DISK_TABLES: 0
     CREATED_TMP_TABLES: 0
       SELECT_FULL_JOIN: 0
 SELECT_FULL_RANGE_JOIN: 0
           SELECT_RANGE: 0
     SELECT_RANGE_CHECK: 0
            SELECT_SCAN: 0
      SORT_MERGE_PASSES: 0
             SORT_RANGE: 0
              SORT_ROWS: 0
              SORT_SCAN: 0
          NO_INDEX_USED: 0
     NO_GOOD_INDEX_USED: 0
       NESTING_EVENT_ID: NULL
     NESTING_EVENT_TYPE: NULL
*************************** 9. row ***************************
              THREAD_ID: 592
               EVENT_ID: 18
           END_EVENT_ID: 18
             EVENT_NAME: statement/com/Execute
                 SOURCE: mysqld.cc:923
            TIMER_START: 504269016680128625
              TIMER_END: 504269016732090515
             TIMER_WAIT: 51961890
              LOCK_TIME: 0
               SQL_TEXT: NULL
                 DIGEST: NULL
            DIGEST_TEXT: NULL
         CURRENT_SCHEMA: test
            OBJECT_TYPE: NULL
          OBJECT_SCHEMA: NULL
            OBJECT_NAME: NULL
  OBJECT_INSTANCE_BEGIN: NULL
            MYSQL_ERRNO: 0
      RETURNED_SQLSTATE: NULL
           MESSAGE_TEXT: NULL
                 ERRORS: 0
               WARNINGS: 0
          ROWS_AFFECTED: 0
              ROWS_SENT: 0
          ROWS_EXAMINED: 0
CREATED_TMP_DISK_TABLES: 0
     CREATED_TMP_TABLES: 0
       SELECT_FULL_JOIN: 0
 SELECT_FULL_RANGE_JOIN: 0
           SELECT_RANGE: 0
     SELECT_RANGE_CHECK: 0
            SELECT_SCAN: 0
      SORT_MERGE_PASSES: 0
             SORT_RANGE: 0
              SORT_ROWS: 0
              SORT_SCAN: 0
          NO_INDEX_USED: 0
     NO_GOOD_INDEX_USED: 0
       NESTING_EVENT_ID: NULL
     NESTING_EVENT_TYPE: NULL

(also not available in events_statements_summary_by_digest)

How to repeat:
* Issue server-side prepared statement with P_S enabled
* Note that no digest or SQL text is recorded

Suggested fix:
Record digest and SQL text.
[12 May 2016 15:58] Kenny Gryp
I guess this is fixed in MySQL 5.7 with the prepared_statements_instances table?

 https://dev.mysql.com/doc/refman/5.7/en/prepared-statements-instances-table.html
[12 May 2016 16:02] Kenny Gryp
While in my previous comment I noted prepared_statements_instances includes prepared statements information in PFS, the summary table is missing for it :(.
[22 Mar 3:17] Aristotle Po
This still affects latest 8.0. Any plans to fix this bug? This will be helpful in investigating application SQL errors.

node2 [localhost:8362] {root} ((none)) > select @@version, @@version_comment;
+-----------+------------------------------+
| @@version | @@version_comment            |
+-----------+------------------------------+
| 8.0.36    | MySQL Community Server - GPL |
+-----------+------------------------------+
1 row in set (0.00 sec)

node2 [localhost:8362] {root} ((none)) > SELECT THREAD_ID, EVENT_ID, SQL_TEXT, DIGEST_TEXT, EVENT_NAME
    ->      , MYSQL_ERRNO, RETURNED_SQLSTATE, MESSAGE_TEXT, ERRORS
    ->   FROM performance_schema.events_statements_history_long t1
    ->  WHERE ERRORS > 0 \G
*************************** 1. row ***************************
        THREAD_ID: 176
         EVENT_ID: 1
         SQL_TEXT: NULL
      DIGEST_TEXT: NULL
       EVENT_NAME: statement/com/Prepare
      MYSQL_ERRNO: 1295
RETURNED_SQLSTATE: HY000
     MESSAGE_TEXT: This command is not supported in the prepared statement protocol yet
           ERRORS: 1
*************************** 2. row ***************************
        THREAD_ID: 176
         EVENT_ID: 13
         SQL_TEXT: NULL
      DIGEST_TEXT: NULL
       EVENT_NAME: statement/com/Execute
      MYSQL_ERRNO: 3101
RETURNED_SQLSTATE: 40000
     MESSAGE_TEXT: Plugin instructed the server to rollback the current transaction.
           ERRORS: 1
2 rows in set (0.02 sec)
[22 Mar 3:32] Aristotle Po
Found the updated link and will explore it https://dev.mysql.com/doc/refman/8.0/en/performance-schema-prepared-statements-instances-t...
[22 Mar 3:45] Aristotle Po
Event if instruments are enabled. No data is found in performance_schema.prepared_statements_instances.

node2 [localhost:8362] {root} (performance_schema) > SELECT * FROM performance_schema.setup_instruments WHERE NAME IN ('statement/com/Prepare', 'statement/com/Execute', 'statement/sql/prepare_sql', 'statement/sql/execute_sql');
+---------------------------+---------+-------+------------+-------+------------+---------------+
| NAME                      | ENABLED | TIMED | PROPERTIES | FLAGS | VOLATILITY | DOCUMENTATION |
+---------------------------+---------+-------+------------+-------+------------+---------------+
| statement/com/Execute     | YES     | YES   |            | NULL  |          0 | NULL          |
| statement/com/Prepare     | YES     | YES   |            | NULL  |          0 | NULL          |
| statement/sql/execute_sql | YES     | YES   |            | NULL  |          0 | NULL          |
| statement/sql/prepare_sql | YES     | YES   |            | NULL  |          0 | NULL          |
+---------------------------+---------+-------+------------+-------+------------+---------------+
4 rows in set (0.00 sec)

node2 [localhost:8362] {root} (performance_schema) > SELECT * FROM  performance_schema.prepared_statements_instances;
Empty set (0.00 sec)
[26 Mar 7:38] Aristotle Po
I opened a related bug report so it can be check for the latest 8.0 version and non-mysql clients.
Bug #114480