Incorrect Empty Result set In CentOs, MySQL

[Atousa Mir]

I have one web application which is run on centos 5.5. My database is Mysql 5.5. One of the queries returns empty result set when it must return at least one row result. This is my code:

public void loadBooksInfo(DataModule dm, String bId) {
BookInfo BookInfo = null;
ResultSet rs = null;
Statement st = null;
DBConnection mdc = null;

try {
String query = "SELECT * FROM " + Settings.BOOKTABLE.toUpperCase()
+ " WHERE " + Settings.BOOKID.toUpperCase() + "='" + bId + "'";
mdc = DBConnection.getConnection(dbUrl, user, password);
st = mdc.createStatement();
rs = mdc.executeQuery(st, query);
ResultSetMetaData rsmd = rs.getMetaData();
int size = rsmd.getColumnCount();
while (rs.next()) {
if (size > 0) {
BookInfo = new BookInfo();
for (int i = 1; i <= size; i++) {
// ...
// Some codes here...
// ...
}
setAcqPasswords(BookInfo);
dm.addBookToList(BookInfo);
BookInfo = null;
}
}
}
catch (Exception e) {
// Some codes...
}
closeResultSet(rs);
if (mdc != null) mdc.closeStatement(st);
closeConnection(mdc);
}

The problem is that select statement sometimes is empty even for one unique bId, and most often returns correct result. For example, for the query

SELECT * FROM BOOK WHERE BOOKID='1000'

the result is correct and returns 1 row result, but for the same query in another time result is empty, although I expected to be 1 row again. No changes have been applied on data during this period. I debugged the code. During debugging code, for a query, which found empty result incorrectly, I append schema name at the first of the table name. Result was not empty and was the same as I expected! The changed query was:

SELECT * FROM bookdb.BOOK WHERE BOOKID='1000'

I don’t know why no result is returned. I think the problem is not related to schema name, because I put the schema name in the Database URL. In addition, all other queries of application return expected results. This query is run too much in my application.

When I run this code on windows (JDBC driver + mysql 5.5) it works fine, but when I run it on CentOS (same JDBC driver + mysql 5.5) it returns empty result set sometimes.

This is my Environment: OS: CentOS 5.5 (Linux 2.6.18-348.6.1.el5 x86) JAVA: 1.6.0_17 (openjdk-1.7.10-1.20.b17.el5) MYSQL: 5.5 (mysql-5.5.32.18.el5) And these are my JDBC properties: Specification-Title: JDBC Specification-Version: 3.0 Specification-Vendor: Sun Microsystems Inc. Implementation-Title: MySQL Connector/J Implementation-Version: 5.0.0-beta Implementation-Vendor: MySQL AB

I appreciate any helps. Why sometimes result set is empty although we are sure data is stored in database. Thanks in advance.