MySQL Bugs: #46645: Insert outside of an java application won't be synchronized with drivers cache

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:	Windows (XP with SP3)
Assigned to:		CPU Architecture:	Any
Tags:	driver, insert, java, SELECT, XP

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!

Other OS constellations were not examined

Maybe it is mentionable to say that table engine is MyISAM

Other important issues to system environment

Compiler compliance level: 1.6
Database: MySQL 5.1.36.community-edition via tcp/ip

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.

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

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.

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?

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...

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".

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.

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".

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.