Bug #46645 Insert outside of an java application won't be synchronized with drivers cache
Submitted: 11 Aug 2009 9:30 Modified: 13 Apr 2015 8:07
Reporter: Uenhan Inay Email Updates:
Status: Can't repeat Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:5.1.7, 5.1.8 OS:Microsoft Windows (XP with SP3)
Assigned to: CPU Architecture:Any
Tags: driver, insert, java, SELECT, XP

[11 Aug 2009 9:30] Uenhan Inay
Description:
First select in console application delivers right number of rows of a table.
After inserting a new row to this table, which is succesfully commited, the next select statement in console application delivers cached statements per default.
The inserted item will never be showed, even if the os system is going off.

Maybe there is a synchronization problem with the drivers cache...

How to repeat:
I was using a vm with xp and sp3. As IDE was Eclipse Ganymede chosen. MySQL 5.1 was installed. 

1. A table with columns of integer, datetime and character data have to be created. 
2. Just call select on this table with a simple console app. 
3. Insert a new row with my sql browser or another sql client. Commit or not
4. Now call your console application again. Result of select statement is the first call

PS: U will never get an error or a exception message by the driver or your system.
This is suited for a unit test by junit!
[11 Aug 2009 9:40] Uenhan Inay
Other OS constellations were not examined
[11 Aug 2009 10:53] Uenhan Inay
Maybe it is mentionable to say that table engine is MyISAM
[11 Aug 2009 11:29] Uenhan Inay
Other important issues to system environment

Compiler compliance level: 1.6
Database: MySQL 5.1.36.community-edition via tcp/ip
[11 Aug 2009 13:33] Mark Matthews
The driver has no cache, so it's not caching results. Without a testcase that shows the DDL of the table in question, as well as the series of SELECTs that are issued, we can't repeat this issue, and thus can't begin to solve it.
[12 Aug 2009 7:13] Uenhan Inay
1. Given table look like this one:

DROP TABLE IF EXISTS `a0055555`.`c11111`;
CREATE TABLE  `a0055555`.`c11111` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `DateAndTime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `Status` int(11) NOT NULL DEFAULT '0',
  `TerminalNr` int(11) NOT NULL DEFAULT '0',
  `Data` varchar(255) NOT NULL DEFAULT '',
  `TimeDifference` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `DateAndTime` (`DateAndTime`,`Status`),
  KEY `Data` (`Data`)
) ENGINE=MyISAM AUTO_INCREMENT=68612 DEFAULT CHARSET=utf8 COMMENT='MyCompany Testdb';

2. Choose this select in java console app for calls from app:

SELECT `ID`, `DateAndTime`, `Data` 
 FROM c11111
 WHERE `TerminalNr` = 1002 
 ORDER BY `DateAndTime` DESC

3. Run java app first time

Expected result: n records

4. Insert a new record per SQL client
INSERT INTO c11111 (DateAndTime, Status, TerminalNr, Data, TimeDifference) VALUES (current_timestamp(), 1, 1000, 'MyStamp=2009-07-29_10:21:45;SomeIdentifier=20300;GPS=A,5238.6000,N,00758.1290,E,;DigitalInput=01---;table=DB', 11);

5. Run java app second time

Expected result: n+1 records (but this one fails)

PS: I have changed some data, but hope this fulfills your needed feedback ,-)
[12 Aug 2009 7:33] Tonci Grgin
Hi Uenhan and thanks for your report.

Now, if you can just put all the stuff into small, self sufficient test case that would really be nice.
[12 Aug 2009 9:29] Uenhan Inay
I have to apologize for following task: Insert a new record per SQL client
By mistake copy&paste i have choosen the wrong insert statement

The right one is the following:

INSERT INTO c11111 (DateAndTime, Status, TerminalNr, Data, TimeDifference) VALUES (NOW(),1, 1000, 'MyStamp=2009-07-29_10:21:45;SomeIdentifier=20300;GPS=A,5238.6000,N,00758.1290,E,;DigitalInput=01---;table=DB', 11);

What have to be done to deliver a self sufficient test case? Should i write a Junit Test? Can u do this? Is there any instruction how to put a self sufficient test case?
[12 Aug 2009 10:07] Tonci Grgin
Uenhan, just pick any of the c/J reports that are closed or verified and check for test cases there.
Self-sufficient means I can run it and check for error. This includes and DDL/DML statements needed, connection string and so on...
[12 Sep 2009 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".
[14 Sep 2009 6:31] Tonci Grgin
Even though we think this is not possible, nor this is a bug per-se, I'd still like to see complete test case attached.
[14 Oct 2009 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".
[13 Apr 2015 8:07] Alexander Soklakov
Posted by developer:
 
There is no feedback for almost 6 years and the situation was never reported again. So I close this report as Can't repeat.