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.

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 $ )

Any help or ideas would be appreciated.