| Bug #8564 | ResultSet returns string of previous record if string in current record is null | ||
|---|---|---|---|
| Submitted: | 17 Feb 2005 8:35 | Modified: | 17 Feb 2005 14:35 |
| Reporter: | Ralf Hauser | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | Connector / J | Severity: | S2 (Serious) |
| Version: | 3.1.6 | OS: | Windows (winXP, debian, redhat9) |
| Assigned to: | CPU Architecture: | Any | |
[17 Feb 2005 9:30]
Ralf Hauser
Don't think it is really related to http://lists.mysql.com/java/5141
[17 Feb 2005 14:35]
Mark Matthews
Not enough information was provided for us to be able to handle this bug. Please re-read the instructions at http://bugs.mysql.com/how-to-report.php If you can provide more information, feel free to add it to this bug and change the status back to 'Open'. Thank you for your interest in MySQL.
[17 Feb 2005 14:38]
Mark Matthews
We would need the full schema, or a schema and data that makes the testcase repeatable. Can you reproduce this behavior outside of the connection pool with a standalone connection? The JDBC driver has a testsuite that exercises this functionality of result set navigation quite completely, as well as the fact that 3.1.6 has been in production for some time, and we have received no bug reports like this before...Seeing as how this looks pretty serious, if this bug was in the JDBC driver, we should have heard about it by now. Without seeing your schema, and the type of data in it, we can't test your particular situation.
[20 Feb 2005 21:34]
Ralf Hauser
Mark,
It appears that the main problem is rather on the mysqld side than in connector/J.
I hope I don't waste your time with perhaps even a sql query error on my side, but here it goes:
SELECT TBL_SENDER.msg_id, AES_DECRYPT(TBL_SENDER.subject,'q2s3d4f5g6o0!;#[') AS subject FROM TBL_SENDER, TBL_MESSAGE WHERE TBL_SENDER.msg_id = TBL_MESSAGE.msg_id AND (TBL_SENDER.msg_id = 6123 OR TBL_SENDER.msg_id = 6147) ORDER BY TBL_SENDER.msg_id DESC;
produces the problem based on two records only.
SELECT TBL_SENDER.msg_id, AES_DECRYPT(TBL_SENDER.subject,'q2s3d4f5g6o0!;#[') AS subject FROM TBL_SENDER, TBL_MESSAGE WHERE TBL_SENDER.msg_id = TBL_MESSAGE.msg_id AND (TBL_SENDER.msg_id = 6123 OR TBL_SENDER.msg_id = 6147);
correctly shows 6147 as null and 6123 with the subject string.
So does
SELECT TBL_SENDER.msg_id, AES_DECRYPT(TBL_SENDER.subject,'q2s3d4f5g6o0!;#[') AS subject FROM TBL_SENDER WHERE (TBL_SENDER.msg_id = 6123 OR TBL_SENDER.msg_id = 6147) ORDER BY TBL_SENDER.msg_id DESC;
I am using mysqld Ver 4.0.21-log for pc-linux-gnu on i386 (Source distribution)
on debian. This can also be reproduced with other clients such as the old MySQLFront or Squirrel 1.2beta6.
When trying to build a sample program to reproduce it with connetor/J, correctly, I was not able enter the null value even though it was to be encrypted (so this is probably another error that the null value originally got into my DB, but I haven't found a reproducible input data yet).
So, the next idea was to simply insert the cypher-text without encrypting it in order to create you the test data out of the .java file.
Unfortunately, no luck there - despite binding it in a prepared statement array, it apparently contained characters the jdbc doesn't escape.
Temporarily encoding it didn't help either - so:
String aesEncVal = "%EF%BF%AF%13%EF%BF%90%2F%06Wx%03%EF%BE%92%0F%EF%BF%9E%EF%BF%8F%"
+ "04%EF%BF%A6%EF%BE%82%EF%BE%AEk%17%EF%BF%991%EF%BF%AE%EF%BF%8Cch%EF%BF%B2"
+ "%EF%BF%97%21%EF%BE%8C%EF%BF%90D%EF%BF%9C%09%3B%EF%BE%90%EF%BF%96n%EF%BE%91"
+ "%5B%EF%BF%80%0A%3B%EF%BF%BA%EF%BE%BB%3Ad%EF%BF%AB%2F";
insSenderStmt = conn.prepareStatement(
"INSERT INTO bug8564 subject " + " VALUES ?);",
Statement.RETURN_GENERATED_KEYS);
insSenderStmt.setString(1, URLDecoder
.decode(aesEncVal, "UTF-8"));
retVal = insSenderStmt.executeUpdate();
would not insert, but complain about an invalid query. So far I thought that any string should be insertable with a prepared statement, but obviously I am wrong here or this is only valid for iso8859-1, but not utf-8?
Any hints would be highly appreciated! Ralf
[20 Feb 2005 21:43]
Ralf Hauser
I am happy to send you the relevant parts of my tables to reproduce. The DB overall is unfortunately several 100 MB. The problem is: how do I export it such delicate contents in a way that it doesn't get converted to 7bit or alike? Also, I would assume that migrating to mysqld 4.023 wouldn't make a big difference?
[22 Feb 2005 5:57]
Ralf Hauser
ok, got around fixing the cryptogram insert issue, the problem is now reproducible as in http://bugs.mysql.com/8669
Description: When retrieving null strings from the DB, the jdbc simply takes the value from a previous record where the string was not null. One peculiarity is that the column is encrypted. Also, the latest commons-dbcp-1.2.1.jar is used. Thus when stepping through in eclipse, I see that the java.sql.ResultSet really is a org.apache.commons.dbcp.DelegatingResultSet - so perhaps the error is there? selDraftStmt = conn.prepareStatement( "SELECT TBL_SENDER.msg_id, " ... + " AES_DECRYPT(TBL_SENDER.subject,?) AS subject, " ... + " FROM TBL_SENDER ... + " WHERE TBL_SENDER.msg_id = TBL_MESSAGE.msg_id" ...); selDraftStmt.setString(1, Globals.getInstance().getAESkey()); ... log.debug( "selDraftStmt: " + ((DelegatingPreparedStatement) selDraftStmt) .getDelegate() .toString()); } java.sql.ResultSet crs = selDraftStmt.executeQuery(); ... msgHeader.setSubject(crs.getString("subject")); excerpt from the scheme: CREATE TABLE TBL_SENDER ( ... subject CHAR(255) NOT NULL, but probably, since AES_ENCRYPT is done, the DBMS doesn't notice that the content is null anyway! How to repeat: see the description in http://jira.codehaus.org/browse/DISPL-191 Suggested fix: return a zero length string ""