[MDEV-32266] All queries in stored procedures increment empty_queries counter Created: 2023-09-27  Updated: 2023-10-03  Resolved: 2023-10-03

Status: Closed
Project: MariaDB Server
Component/s: Documentation
Affects Version/s: 10.11.4
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: Steve Shaw Assignee: Ian Gilfillan
Resolution: Fixed Votes: 0
Labels: None
Environment:

Ubuntu 20.04



 Description   

In user statistics empty_queries is described as follows: https://mariadb.com/kb/en/information-schema-user_statistics-table/
EMPTY_QUERIES The number of times this user's connections sent empty queries to the server.
When running the same workload with client SQL and stored procedures the difference in the counter was as follows (some empty queries are expected).
CLIENT SQL EMPTY_QUERIES: 77786
STORED PROCS EMPTY_QUERIES: 103697140

In a test case, the stored procedure is executing the queries correctly and returning the expected rows, however using SELECT INTO means empty_queries is incremented as data is not returned to the client.

SELECT d_next_o_id FROM district WHERE d_w_id=1 AND d_id=10
d_next_o_id is 3001
 
SELECT COUNT(DISTINCT (s_i_id)) FROM order_line, stock WHERE ol_w_id = 1 AND ol_d_id = 10 AND (ol_o_id < 3001) AND ol_o_id >= (3001 - 20) AND s_w_id = 1 AND s_i_id = ol_i_id AND s_quantity < 11
stock count is 6
 
EMPTY_QUERIES: 0
 
MariaDB [tpcc]> call slev(1,10,11,@stock_count);
Query OK, 2 rows affected (0.002 sec)
 
MariaDB [tpcc]> select @stock_count;
+--------------+
| @stock_count |
+--------------+
| 6            |
+--------------+
1 row in set (0.000 sec)
 
EMPTY_QUERIES: 2

Fix could be a better description in the documentation explaining that the counter is incremented if data is not returned rather than an empty query sent.
EMPTY_QUERIES The number of times this user's connections sent queries to the server that did not return data to the client.



 Comments   
Comment by Sergei Golubchik [ 2023-09-30 ]

steve.shaw@intel.com is right. The server has two status variables: Com_empty_query and Empty_queries. The first is

Number of queries to the server that do not produce SQL queries. An SQL query simply returning no results does not increment Com_empty_query - see Empty_queries instead

while the second is

Number of queries returning no results. Note this is not the same as Com_empty_query.

INFORMATION_SCHEMA.USER_STATISTICS.EMPTY_QUERIES aggregates per user the value of the second variable, not the first.

Generated at Thu Feb 08 10:30:04 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.