MySQL Bugs: #42197: Query cache and autocommit

Bug #42197	Query cache and autocommit
Submitted:	19 Jan 2009 12:24	Modified:	7 Jul 2017 9:45
Reporter:	Davi Arnaut (OCA)	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Query Cache	Severity:	S3 (Non-critical)
Version:	5.0, 5.1, 6.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
If autocommit is off, query cache won't serve from the cache the first query that causes a transaction to start. The problem is that the server_status is stored in the packets saved by query cache and it can only be served backup if the server_status of the saved query matches the current server_status of the server, and the server_status when autocommit is off will only become equal during the execution of the query and a query cache hit is checked way before this happens.

Problem due to the patch for Bug#36326

How to repeat:
SET autocommit=OFF;
CREATE TABLE t1 (a INT) ENGINE=InnoDB;
INSERT INTO t1 VALUE (1);
COMMIT;
SELECT * FROM t1;
COMMIT;
SELECT * FROM t1;
SELECT * FROM t1;
COMMIT;
SHOW STATUS LIKE "Qcache_hits"; # Should be 2

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/63538

2966 Davi Arnaut	2009-01-19
      Bug#42197: Query cache and autocommit
      
      Disabled test case. Pending fix and/or resolution.

Pushed into 6.0.10-alpha (revid:joro@sun.com-20090119171328-2hemf2ndc1dxl0et) (version source revid:joro@sun.com-20090119131756-d2cx8pq4ep5on9jh) (merge vers: 6.0.10-alpha) (pib:6)

The underlying cause of the problem is that the query cache stores eof packets that contain the server_status of the time when the statement is executed and those packets can't be served back to a client if they don't match the server_status at the time when the statement is looked up in the cache. For example:

CREATE TABLE t1 (a INT) ENGINE=InnoDB;
SET autocommit = OFF;
# 1) Implicitly starts a transaction (flag SERVER_STATUS_IN_TRANS is set in server_status)
SELECT * FROM t1;
# 2) Cache with current server_status (same as above)
SELECT 1 FROM t1;
# 3) Commits transaction and clears the SERVER_STATUS_IN_TRANS
COMMIT;
# 4) Not served from cache as at lookup time SERVER_STATUS_IN_TRANS is not set yet
SELECT 1 FROM t1;
# 5) Served from cache as SERVER_STATUS_IN_TRANS was set
SELECT 1 FROM t1;
COMMIT;

The problem is that at 4) the cache is looked up before the server enters a transaction and to support this usage scenario it would be needed to know before hand (at cache lookup time) if the statement will cause the server to start a transaction. To support this scenario we would basically need to do following at lookup:

1. Look up with current server_status
2. If not found and autocommit is off:
3.   Lookup with there is a entry with server_status in transaction
4.   If found:
5.     Check if statement would cause server to start a transaction (ask engine)
6.     If yes, serve from cache. Otherwise, execute.

This cause the time holding the cache lock to increase as two lookups is required and adds a need to ask the handler for allowance. A more appropriate fix would be re-factoring the query cache to not store the server_status (ignore SERVER_STATUS_IN_TRANS while caching) and to set it on-the-fly when serving it back to a client.

Since this involves quite some coding effort for what actually is a corner case and after discussion with teammates, I decided to close this bug as Won't fix as supporting this scenario at this time is not worth the effort.

If someone later finds reasons to pursue this further, please re-open the bug.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/66154

2827 Magnus Svensson	2009-02-13
      Bug #42197  	Query cache and autocommit
       - disable ndb_cache_trans in 5.1-telco-* waiting for fix

Pushed into 5.1.32-ndb-6.3.23 (revid:tomas.ulin@sun.com-20090217131017-6u8qz1edkjfiobef) (version source revid:tomas.ulin@sun.com-20090216083408-rmvyaxjt6mk8sg1y) (merge vers: 5.1.32-ndb-6.3.23) (pib:6)

Pushed into 5.1.32-ndb-6.4.3 (revid:tomas.ulin@sun.com-20090217134419-5ha6xg4dpedrbmau) (version source revid:tomas.ulin@sun.com-20090216083646-m8st11oj1hhfuuh5) (merge vers: 5.1.32-ndb-6.4.3) (pib:6)

Pushed into 5.1.32-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090217134216-5699eq74ws4oxa0j) (version source revid:msvensson@mysql.com-20090213150347-apn02c03cp5wtiqb) (merge vers: 5.1.32-ndb-6.2.17) (pib:6)

[Original]
How to repeat:
SET autocommit=OFF;
CREATE TABLE t1 (a INT) ENGINE=InnoDB;
INSERT INTO t1 VALUE (1);
COMMIT;
SELECT * FROM t1;
COMMIT;
SELECT * FROM t1;
SELECT * FROM t1;
COMMIT;

[Workaround 1: Use COMMIT AND CHAIN instead of COMMIT]
SET autocommit=OFF;
CREATE TABLE t1 (a INT) ENGINE=InnoDB;
INSERT INTO t1 VALUE (1);
COMMIT AND CHAIN;
SELECT * FROM t1;
COMMIT AND CHAIN;
SELECT * FROM t1;
SELECT * FROM t1;
COMMIT AND CHAIN;

[Workaround 2: Use START TRANSACTION or BEGIN explicitly after COMMIT;]
SET autocommit=OFF;
CREATE TABLE t1 (a INT) ENGINE=InnoDB;
INSERT INTO t1 VALUE (1);
COMMIT;
BEGIN; -- Added.
SELECT * FROM t1;
COMMIT;
BEGIN; -- Added.
SELECT * FROM t1;
SELECT * FROM t1;
COMMIT;

I for one would like to see this bug reopened and fixed. We access MySQL through JDO and autocommit is off for all our connections by default. There are some fairly long running queries (> 10 seconds) that we have and the query cache had previously been effective in keeping our pages fast (except for the initial hit). On the pages where a long running query was the first in a transaction - it has really slowed down these pages - because it never ever benefits from the cache, no matter how many times that page has been seen. We can't use the suggested workarounds - commands are sent through to MySQL for us since we are working with the JDO layer. Of course we can do our own caching mechanism on top, or reorganise our code to make sure long running queries don't appear first but previous to 5.1.32 it was all performing well and should be transparent.

I'm running MySQL version 5.1.41-3ubuntu12.6 and bug still exists. Would you push the patch to 5.1.X source?

Ups. I just noticed that those patches are not bugfixes. So we're still looking forward for this issue to be fixed.

I noticed that this bug still exists in 5.5.  Which release can we expect this fix in?

Thanks,
JS

Hi,

I have noticed that the bug still exists in 5.5 and exists not only when autocommit=0 but also when transactions are started explicitly via START TRANSACTION. See the test case below:

--- MySQL 5.5.28
mysql [localhost] {msandbox} (test) > set autocommit=1;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {msandbox} (test) > flush status;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {msandbox} (test) > CREATE TABLE t1 (a INT) ENGINE=InnoDB;
Query OK, 0 rows affected (0.16 sec)

mysql [localhost] {msandbox} (test) > INSERT INTO t1 VALUE (1);
Query OK, 1 row affected (0.06 sec)

mysql [localhost] {msandbox} (test) > SHOW STATUS LIKE "Qcache_hits";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Qcache_hits | 0 |
+---------------+-------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (test) > start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {msandbox} (test) > SELECT * FROM t1;
+------+
| a |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (test) > SHOW STATUS LIKE "Qcache_hits";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Qcache_hits | 0 |
+---------------+-------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (test) > SELECT * FROM t1;
+------+
| a |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (test) > SHOW STATUS LIKE "Qcache_hits"; <--- the first statement in the transaction does not use the query cache, but this one does
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Qcache_hits | 1 |
+---------------+-------+
1 row in set (0.00 sec)

I have noted that START TRANSACTION does not actually start the transaction, but rather the first statement inside the transaction does so, but that does not seem to be documented anywhere, and hence this causes the first transaction to not use the query_cache.

The expected behaviour is that when you explicitly start a transaction, the first statement should use the query_cache.

MySQL will no longer invest in the query cache, see:

http://mysqlserverteam.com/mysql-8-0-retiring-support-for-the-query-cache/