Bug #4213 innodb table engine not notifying query cache to invalidate

• Submitted: 18 Jun 2004 21:53
• Modified: 22 Jun 2004 13:02
• Reporter: Matthew Lord
• Status: Closed
• Category: MySQL Server
• Severity: S1 (Critical)
• Version: 4.0.x
• OS: Any (any)
• Assigned to: Oleksandr Byelkin
• CPU Architecture: Any

[18 Jun 2004 21:53] Matthew Lord

Description:
When performing data changing queries inside of a begin; || start transaction; block the innodb table 
handler is not telling the query cache to invalidate the queries pertaining to the table(s) changed.

How to repeat:
create database csc3013;

mysql -u root csc3013 < csc3013.dump (posted with bug report)

set global query_cache_size=1024*1024*5; // if not already on
flush status;
reset query cache;

//* ***************************************** */
//* * Make sure there's no records in s3 * */
//* ***************************************** */
delete from s3 where s1_id = 1 and s2_id = 1;

//* ************************* */
//* * Run joined query so that it is stored in query cache* */
//* ************************* */
select s1.* from s1, s2, s3 where s3.state & 1 = 0 and s3.s1_id = s1.id and s3.s2_id = s2.id and s1.id = 
1 order by s1.a asc;

//* ************************** */
//* * Begin a transaction * */
//* ************************** */
begin;

// create a new row
insert into s3 VALUES ( NULL, 1, 1, 2 );

//* ********************************************** */
//* * Try to insert the exact same info again * */
//* ********************************************** */
insert into s3 VALUES ( NULL, 1, 1, 2 );

//* ***************************** */
//* * Commit the transaction * */
//* ***************************** */
commit;

//* *************************************** */
//* * Run the same query again. * */
//* * Notice how it's not showing us the * */
//* * inserted record. * */
//* * It's using the chached results * */
//* * from the first query. * */
//* *************************************** */
select s1.* from s1, s2, s3 where s3.state & 1 = 0 and s3.s1_id = s1.id and s3.s2_id = s2.id and s1.id = 
1 order by s1.a asc;

[18 Jun 2004 21:57] Matthew Lord

Test data

Attachment: csc3013.dump (application/octet-stream, text), 1.16 KiB.

[20 Jun 2004 22:46] Oleksandr Byelkin

ChangeSet 
  1.1886 04/06/20 23:44:21 [e-mail] +3 -0 
  do not clear list of changed tables on one statement rollback (Bug #4213)

[22 Jun 2004 13:02] Oleksandr Byelkin

Thank you for bug report. Patch for this bug pushed in our internal source 
repository and will be in next server release.