| 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: | |
| Category: | MySQL Server: Prepared statements | Severity: | S2 (Serious) |
| Version: | 5.6.11 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[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 2024 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 2024 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 2024 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 2024 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
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.