Bug #27472 Query Cache is not flushed out when updated with JDBC (InnoDB)
Submitted: 27 Mar 2007 12:54 Modified: 31 Mar 2014 12:33
Reporter: Balaji Ramamoorthy Email Updates:
Status: Can't repeat Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:5.0.37 OS:Windows (Windows XP / 2003)
Assigned to: Alexander Soklakov CPU Architecture:Any

[27 Mar 2007 12:54] Balaji Ramamoorthy
Description:
MySQL Query cache is not invalidated when even after the table is updated thru JDBC. I perform a SELECT1-UPDATE-SELECT2 operation and the SELECT2 returns same results as SELECT1, though UPDATE changed the table. This is because the query cache is not invalidated during UPDATE. When the same operation is performed with mysql client, gives expected results.

If SELECT2 is changed (adding blank space/ adding alias to table), this gets upto date results, as they are fetched from database, rather than the cache.

Details about the environment:
1. database product version=5.0.37-community-nt-log
2. database driver version=mysql-connector-java-3.1.14 ( $Date: 2006-10-18 17:40:15 +0200 (Wed, 18 Oct 2006) $, $Revision: 5888 $ )
3. Storage Engine type : InnoDB
4. Non transactional
5. AUTOCOMMIT=1
6. Query cache enabled and configured as 64MB.

How to repeat:
I have a InnoDB table (IQTopoViewMonitorSetting):

+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| VIEWID      | int(11)     | NO   | PRI |         |       |
| GROUPID     | int(11)     | NO   | PRI |         |       |
| MONITORNAME | varchar(45) | NO   | PRI |         |       |
| POSX        | int(11)     | NO   |     |         |       |
| POSY        | int(11)     | NO   |     |         |       |
+-------------+-------------+------+-----+---------+-------+

CREATE TABLE `iqtopoviewmonitorsetting` (
  `VIEWID` int(11) NOT NULL,
  `GROUPID` int(11) NOT NULL,
  `MONITORNAME` varchar(45) NOT NULL,
  `POSX` int(11) NOT NULL,
  `POSY` int(11) NOT NULL,
  PRIMARY KEY  (`VIEWID`,`GROUPID`,`MONITORNAME`),
  KEY `IQTopoViewMonitorSetting_index_2` (`VIEWID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 

step#1
SELECT * FROM IQTopoViewMonitorSetting ;
+--------+---------+--------------+------+------+
| VIEWID | GROUPID | MONITORNAME  | POSX | POSY |
+--------+---------+--------------+------+------+
|      1 |       1 | MAC:0A:06:37 |  971 |  394 |
+--------+---------+--------------+------+------+

step#2

UPDATE IQTopoViewMonitorSetting SET POSX=397,POSY=336 WHERE  VIEWID=1 AND MONITORNAME='MAC:0A:06:37' AND GROUPID=1;

step#3

SELECT * FROM IQTopoViewMonitorSetting ;
+--------+---------+--------------+------+------+
| VIEWID | GROUPID | MONITORNAME  | POSX | POSY |
+--------+---------+--------------+------+------+
|      1 |       1 | MAC:0A:06:37 |  971 |  394 |
+--------+---------+--------------+------+------+

step#4

SELECT * FROM IQTopoViewMonitorSetting I; (Query is changed a bit)
+--------+---------+--------------+------+------+
| VIEWID | GROUPID | MONITORNAME  | POSX | POSY |
+--------+---------+--------------+------+------+
|      1 |       1 | MAC:0A:06:37 |  397 |  336 |
+--------+---------+--------------+------+------+

When step1-3 is performed using a mysql client (Query browser/mysql), the SELECT2 (step#3) returns valid results. I am not sure whether it could be a problem with the JDBC connector.

If Query cache is disabled, even JDBC based operations works fine and SELECT2 returns valid results. This confirms that Query cache has some problem when used with JDBC.
[27 Mar 2007 13:04] Balaji Ramamoorthy
I see the same problem when used with latest Driver too.

database driver version=mysql-connector-java-5.0.5 ( $Date: 2007-03-01 00:01:06 +0100 (Thu, 01 Mar 2007) $, $Revision: 6329 $ )
[27 Mar 2007 13:05] Balaji Ramamoorthy
My.ini file used

Attachment: my.ini (application/octet-stream, text), 20.53 KiB.

[27 Mar 2007 13:06] Balaji Ramamoorthy
The functionality seems to be working with 5.0.27. I will test it and confirm.
[28 Mar 2007 17:03] Tonci Grgin
Hi and thanks for your report.

I would like you to attach *complete* test case exhibiting this behavior every time it's run. Also, please start server with --log (if not enabled already) and paste/attach portion from the moment your Java program connects till the end of execution (you can omit inserts/updates with sensitive data). 
What is your JDK/JRE version?
[28 Apr 2007 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[1 May 2007 13:48] Tonci Grgin
Feedback is still needed...
[1 Jun 2007 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[31 Mar 2014 12:33] Alexander Soklakov
I close this report as "Can't repeat" because there is no feedback for a long time and codebase is too old. Please, feel free to reopen it if the problem still exists in current driver.