mysql-query_cache_stores_empty_result not functioning as expected – caches empty result irrespective of variable value

[rahim-kanji]

Issue Description:

When using the mysql-query_cache_stores_empty_result variable, the expected behavior is that if a query's result is empty (i.e., zero rows) and caching is specified in the mysql_query_rules with cache_empty_result column not being populated, ProxySQL should check the value of the mysql-query_cache_stores_empty_result variable.

• Expected behavior:

  • If mysql-query_cache_stores_empty_result is set to true (or 1), the empty result should be cached.
  • If mysql-query_cache_stores_empty_result is set to false (or 0), the empty result should not be cached.

However, the actual behavior is that ProxySQL seems to ignore the value of the mysql-query_cache_stores_empty_result variable completely. Empty results are always cached, regardless of whether the variable is set to true or false.

Steps to Reproduce:

1. Connect to the ProxySQL admin interface using your preferred method (e.g., via the MySQL client or a script).
   Example:
   mysql -uadmin -padmin -h 127.0.0.1 -P6032
2. After connecting successfully, configure a query caching rule within mysql_query_rules without specifying cache_empty_result option:

   INSERT INTO mysql_query_rules (rule_id, active, match_digest, cache_ttl)  VALUES (2, 1, '^SELECT', 4000);
   LOAD MYSQL QUERY RULES TO RUNTIME;
   

3. Set the mysql-query_cache_stores_empty_result variable to false (0):

   SET mysql-query_cache_stores_empty_result = 0;
   LOAD MYSQL VARIABLES TO RUNTIME;
   

4. Connect to ProxySQL's MySQL interface port and execute a query that returns an empty result (zero rows):

   mysql -uroot -proot -h 127.0.0.1 -P6033
   SELECT 1 WHERE 1 != 1;
   

5. Observe that ProxySQL still caches the empty result.

   MySQL [(test)]> SELECT Variable_Name, Variable_Value  FROM stats_mysql_global  WHERE Variable_Name LIKE 'Query_Cache%';
   +--------------------------+----------------+
   | Variable_Name            | Variable_Value |
   +--------------------------+----------------+
   | Query_Cache_Memory_bytes | 3890           |
   | Query_Cache_count_GET    | 2              |
   | Query_Cache_count_GET_OK | 0              |
   | Query_Cache_count_SET    | 1              |
   | Query_Cache_bytes_IN     | 50             |
   | Query_Cache_bytes_OUT    | 0              |
   | Query_Cache_Purged       | 0              |
   | Query_Cache_Entries      | 1              |
   +--------------------------+----------------+
   

Expected Result:

Empty result should not be cached when mysql-query_cache_stores_empty_result is set to false.

Actual Result:

Empty result is cached regardless of the value set for mysql-query_cache_stores_empty_result.