Query Cache does not like Transactions

Version: mysql-max-4.0.13-unknown-freebsd4.7-i386
OS: FreeBSD 4.5

Query Cache does not seem to work on an INNODB table when using
transactions. Queries NOT using auto-commit (explicit transactions)
seem to cache correctly. Using an explicit BEGIN/COMMIT query,
however, causes the query to be skipped.
The only information in the manual (section 6.9) that relates to Query
Cache and Transactions states the following:

"Transactional InnoDB tables that have been changed will be invalidated
when a COMMIT is performed."

However, in the example below, the query does not update the table.
That would lead me to believe that it should cache just as if I was not
using transactions. Did I miss something?


How-To-Repeat:


Initial status is as follows:

Qcache_queries_in_cache = 0
Qcache_inserts = 0
Qcache_hits = 0
Qcache_lowmem_prunes = 0
Qcache_not_cached = 0
Qcache_free_memory = 16112
Qcache_free_blocks = 1
Qcache_total_blocks = 1

The following query:

SELECT * FROM TEST;

Produces the following status:

Qcache_queries_in_cache = 1
Qcache_inserts = 1
Qcache_hits = 0
Qcache_lowmem_prunes = 0
Qcache_not_cached = 0
Qcache_free_memory = 16112
Qcache_free_blocks = 1
Qcache_total_blocks = 4


The following query:

BEGIN;
SELECT * FROM TEST2;
COMMIT;

Produces the following status, indicating that the query was NOT
cached.:

Qcache_queries_in_cache = 1
Qcache_inserts = 1
Qcache_hits = 0
Qcache_lowmem_prunes = 0
Qcache_not_cached = 1
Qcache_free_memory = 16112
Qcache_free_blocks = 1
Qcache_total_blocks = 4


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: Query Cache does not like Transactions

Hi!

On Wed, May 21, 2003 at 04:10:22PM -0600, Ray wrote:
> Version: mysql-max-4.0.13-unknown-freebsd4.7-i386
> OS: FreeBSD 4.5
>
> Query Cache does not seem to work on an INNODB table when using
> transactions. Queries NOT using auto-commit (explicit transactions)
> seem to cache correctly. Using an explicit BEGIN/COMMIT query,
> however, causes the query to be skipped.
> The only information in the manual (section 6.9) that relates to
> Query Cache and Transactions states the following:

Thank you for report. It is expected behaviour for 4.0.x. but we forgot to
describe it in manual. Starting from next version of 4.1 (4.1.1) query cache
will work with innodb tables inside transactions (it will use version number
of table to detect is result actual or is not).

I'll change manual to clarify query cache behaviour inside transactions.

--
For technical support contracts, visit https://order.mysql.com/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Oleksandr Byelkin
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Lugansk, Ukraine
<___/ www.mysql.com

--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org