Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Fixed
-
10.11.4
-
None
-
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.
steve.shaw@intel.com is right. The server has two status variables: Com_empty_query and Empty_queries. The first is
while the second is
INFORMATION_SCHEMA.USER_STATISTICS.EMPTY_QUERIES aggregates per user the value of the second variable, not the first.