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 :(.