Description:
One of my client using MySQL 5.7 in their production workload.
We have enabled performance schema to get all the query visible on the dashboard & tune it.
However the version mysql installed does not show CALL procedure in performance schema when we have SELECT in single line.
How to repeat:
Step 1: Create procedure with SELECT in individual line:
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_reports_SalesRegister`(IN `supp_gstin` VARCHAR(500),IN from_dt VARCHAR(20),IN to_dt VARCHAR(20),IN ndoc_type VARCHAR(100),IN nsub_location VARCHAR(1000),IN nbuyerGstin VARCHAR(500),IN nsuppType VARCHAR(1000))
BEGIN
DECLARE fromdt Date;
DECLARE todt Date;
SET @fromdt=STR_TO_DATE(from_dt, '%d-%m-%Y');
SET @todt=STR_TO_DATE(to_dt, '%d-%m-%Y');
select
i.irn_no 'IRN No',
h.supplier_gstin 'Supplier GSTIN',
h.doc_type 'Doc Type',
h.supply_type 'Supply Type.',
h.ecom_gstin 'Ecom GSTIN',
Step 2: Call procedure:
CALL sp_reports_SalesRegister('1','1','1','1','1','1','1');
Step 3: Check performance schema output blank:
select * from performance_schema.events_statements_history where timer_start > now() - interval 1 hour and sql_text like 'call%'
Empty set (0.07 sec)
Note: We are getting other queries logged correctly:
select * from performance_schema.events_statements_history where timer_start > now() - interval 1 hour limit 5;
THREAD_ID EVENT_ID END_EVENT_ID EVENT_NAME SOURCE TIMER_START TIMER_END TIMER_WAIT LOCK_TIME SQL_TEXT DIGEST DIGEST_TEXT CURRENT_SCHEMA OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME OBJECT_INSTANCE_BEGIN MYSQL_ERRNO RETURNED_SQLSTATE MESSAGE_TEXT ERRORS WARNINGS ROWS_AFFECTED ROWS_SENT ROWS_EXAMINED CREATED_TMP_DISK_TABLES CREATED_TMP_TABLES SELECT_FULL_JOIN SELECT_FULL_RANGE_JOIN SELECT_RANGE SELECT_RANGE_CHECK SELECT_SCAN SORT_MERGE_PASSES SORT_RANGE SORT_ROWS SORT_SCAN NO_INDEX_USED NO_GOOD_INDEX_USED NESTING_EVENT_ID NESTING_EVENT_TYPE NESTING_EVENT_LEVEL
50301449 1 1 statement/sql/select socket_connection.cc:101 2.8276086705927E+018 2.8276086708059E+018 213200000 0 /* mysql-connector-java-8.0.15 (Revision: 79a4336f140499bd22dd07f02b708e163844e3d5) */SELECT @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@collation_connection AS collation_connection, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_write_timeout AS net_write_timeout, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@tx_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout f14c5c3ec863b3f92d8aa0f16a0cc0ef SELECT @@SESSION . `auto_increment_increment` AS `auto_increment_increment` , @@`character_set_client` AS `character_set_client` , @@`character_set_connection` AS `character_set_connection` , @@`character_set_results` AS `character_set_results` , @@`character_set_server` AS `character_set_server` , @@`collation_server` AS `collation_server` , @@`collation_connection` AS `collation_connection` , @@`init_connect` AS `init_connect` , @@`interactive_timeout` AS `interactive_timeout` , @@`license` AS `license` , @@`lower_case_table_names` AS `lower_case_table_names` , @@`max_allowed_packet` AS `max_allowed_packet` , @@`net_write_timeout` AS `net_write_timeout` , @@`query_cache_size` AS `query_cache_size` , @@`query_cache_type` AS `query_cache_type` , @@`sql_mode` AS `sql_mode` , @@`system_time_zone` AS `system_time_zone` , @@`time_zone` AS `time_zone` , @@`tx_isolation` AS `transaction_isolation` , @@`wait_timeout` AS `wait_timeout` einvoice_prod \N \N \N \N 0 \N \N 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 \N \N 0
-------------------------------------------------------------
More details on performance schema configuration:
select * from setup_instruments where name like '%call%' ;
NAME ENABLED TIMED
statement/sql/call_procedure YES YES
memory/sql/sp_head::call_mem_root NO NO
select * from performance_schema.setup_actors
HOST USER ROLE ENABLED HISTORY
% % % YES YES
select * from setup_consumers;
NAME ENABLED
events_stages_current NO
events_stages_history NO
events_stages_history_long NO
events_statements_current YES
events_statements_history YES
events_statements_history_long NO
events_transactions_current NO
events_transactions_history NO
events_transactions_history_long NO
events_waits_current NO
events_waits_history NO
events_waits_history_long NO
global_instrumentation YES
thread_instrumentation YES
statements_digest YES
Description: One of my client using MySQL 5.7 in their production workload. We have enabled performance schema to get all the query visible on the dashboard & tune it. However the version mysql installed does not show CALL procedure in performance schema when we have SELECT in single line. How to repeat: Step 1: Create procedure with SELECT in individual line: CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_reports_SalesRegister`(IN `supp_gstin` VARCHAR(500),IN from_dt VARCHAR(20),IN to_dt VARCHAR(20),IN ndoc_type VARCHAR(100),IN nsub_location VARCHAR(1000),IN nbuyerGstin VARCHAR(500),IN nsuppType VARCHAR(1000)) BEGIN DECLARE fromdt Date; DECLARE todt Date; SET @fromdt=STR_TO_DATE(from_dt, '%d-%m-%Y'); SET @todt=STR_TO_DATE(to_dt, '%d-%m-%Y'); select i.irn_no 'IRN No', h.supplier_gstin 'Supplier GSTIN', h.doc_type 'Doc Type', h.supply_type 'Supply Type.', h.ecom_gstin 'Ecom GSTIN', Step 2: Call procedure: CALL sp_reports_SalesRegister('1','1','1','1','1','1','1'); Step 3: Check performance schema output blank: select * from performance_schema.events_statements_history where timer_start > now() - interval 1 hour and sql_text like 'call%' Empty set (0.07 sec) Note: We are getting other queries logged correctly: select * from performance_schema.events_statements_history where timer_start > now() - interval 1 hour limit 5; THREAD_ID EVENT_ID END_EVENT_ID EVENT_NAME SOURCE TIMER_START TIMER_END TIMER_WAIT LOCK_TIME SQL_TEXT DIGEST DIGEST_TEXT CURRENT_SCHEMA OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME OBJECT_INSTANCE_BEGIN MYSQL_ERRNO RETURNED_SQLSTATE MESSAGE_TEXT ERRORS WARNINGS ROWS_AFFECTED ROWS_SENT ROWS_EXAMINED CREATED_TMP_DISK_TABLES CREATED_TMP_TABLES SELECT_FULL_JOIN SELECT_FULL_RANGE_JOIN SELECT_RANGE SELECT_RANGE_CHECK SELECT_SCAN SORT_MERGE_PASSES SORT_RANGE SORT_ROWS SORT_SCAN NO_INDEX_USED NO_GOOD_INDEX_USED NESTING_EVENT_ID NESTING_EVENT_TYPE NESTING_EVENT_LEVEL 50301449 1 1 statement/sql/select socket_connection.cc:101 2.8276086705927E+018 2.8276086708059E+018 213200000 0 /* mysql-connector-java-8.0.15 (Revision: 79a4336f140499bd22dd07f02b708e163844e3d5) */SELECT @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@collation_connection AS collation_connection, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_write_timeout AS net_write_timeout, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@tx_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout f14c5c3ec863b3f92d8aa0f16a0cc0ef SELECT @@SESSION . `auto_increment_increment` AS `auto_increment_increment` , @@`character_set_client` AS `character_set_client` , @@`character_set_connection` AS `character_set_connection` , @@`character_set_results` AS `character_set_results` , @@`character_set_server` AS `character_set_server` , @@`collation_server` AS `collation_server` , @@`collation_connection` AS `collation_connection` , @@`init_connect` AS `init_connect` , @@`interactive_timeout` AS `interactive_timeout` , @@`license` AS `license` , @@`lower_case_table_names` AS `lower_case_table_names` , @@`max_allowed_packet` AS `max_allowed_packet` , @@`net_write_timeout` AS `net_write_timeout` , @@`query_cache_size` AS `query_cache_size` , @@`query_cache_type` AS `query_cache_type` , @@`sql_mode` AS `sql_mode` , @@`system_time_zone` AS `system_time_zone` , @@`time_zone` AS `time_zone` , @@`tx_isolation` AS `transaction_isolation` , @@`wait_timeout` AS `wait_timeout` einvoice_prod \N \N \N \N 0 \N \N 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 \N \N 0 ------------------------------------------------------------- More details on performance schema configuration: select * from setup_instruments where name like '%call%' ; NAME ENABLED TIMED statement/sql/call_procedure YES YES memory/sql/sp_head::call_mem_root NO NO select * from performance_schema.setup_actors HOST USER ROLE ENABLED HISTORY % % % YES YES select * from setup_consumers; NAME ENABLED events_stages_current NO events_stages_history NO events_stages_history_long NO events_statements_current YES events_statements_history YES events_statements_history_long NO events_transactions_current NO events_transactions_history NO events_transactions_history_long NO events_waits_current NO events_waits_history NO events_waits_history_long NO global_instrumentation YES thread_instrumentation YES statements_digest YES