| Bug #26173 | useCursorFetch corrupting values (was: SQLException for BigDecimal) | ||
|---|---|---|---|
| Submitted: | 8 Feb 2007 1:22 | Modified: | 13 Apr 2007 8:34 |
| Reporter: | Mason Sharp | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / J | Severity: | S2 (Serious) |
| Version: | 5.0.4 | OS: | Linux (CentOS 4.4) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | BIGDECIMAL, EXTRACT | ||
[8 Feb 2007 16:38]
Mason Sharp
I just would like to add that the problem appears to happen with the 5.0.27 server , too.
[11 Feb 2007 0:30]
Mason Sharp
I have some more information to add. It turns out I was using useCursorFetch. If I remove that from the JDBC url, this works ok. So, useCursorFetch may be somehow corrupting the values returned, perhaps depending on how many select expressions are projected, and their types, etc. I figured this out because I encountered another related problem. I did a query like this: SELECT <somecolumns>, 1 as myconstant FROM .... When viewing the results in the ResultSet, it had changed the value of 1 to 2147483647. After removing useCursorFetch from the JDBC url, the value was set correctly to 1.
[12 Feb 2007 9:59]
Tonci Grgin
Hi Mason and thanks for contacting us. Can you please make this a usable report by: - adding complete Java test case with table structures/data necessary to reproduce the problem - What is your JRE/JDK version? - What is the bug report you're refering to in "(was: SQLException for BigDecimal)"? - Is it reproduceable only on CentOS 4.4?
[13 Feb 2007 11:12]
Mason Sharp
Reproducible case
Attachment: CursorFetchTest.java (text/java), 2.07 KiB.
[13 Feb 2007 11:16]
Mason Sharp
I just attached a file that contains a reproducible case.
I will also post it in here for reference.
This case is simpler than the lineitem one I referred to earlier. It runs ok, but returns invalid results.
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
/**
* Test case for MySQL CursorFetch bug
*/
public class CursorFetchTest
{
private static final String DRIVER = "com.mysql.jdbc.Driver";
private static final String CONNECT_URI = "jdbc:mysql://localhost:3306/DB1N1?useCursorFetch=true";
private static final String USER = "user";
private static final String PASSWORD = "password";
public static void main(String[] args) throws Exception
{
Class.forName(DRIVER);
Connection conn = DriverManager.getConnection(args[0], args[1], args[2]);
try
{
// Create test table
Statement stmtWrite = conn.createStatement();
stmtWrite.executeUpdate("create table foo (foo_key int)");
try
{
Statement stmtRead = conn.createStatement();
stmtRead.setFetchSize(1000);
// Insert test data
stmtWrite.executeUpdate("insert into foo values (1)");
stmtWrite.executeUpdate("insert into foo values (2)");
stmtWrite.executeUpdate("insert into foo values (3)");
stmtWrite.executeUpdate("insert into foo values (4)");
// Execute test query
ResultSet rs = stmtRead.executeQuery("select 5 as expression1, foo.foo_key from foo where (foo.foo_key = 3)");
try
{
// Dump results
while (rs.next())
{
System.out.printf("%d\t%d\n", new Object[] {rs.getInt(1), rs.getInt(2)});
}
}
finally
{
rs.close();
}
}
finally
{
// Clean up
stmtWrite.executeUpdate("drop table foo");
}
}
finally
{
conn.close();
}
}
}
When this is fixed, we'd be happy to help test a patch.
Thanks,
Mason
[13 Feb 2007 11:18]
Mason Sharp
Regarding the previous entry, please pass in the JDBC url to see how it affects the test case, with and without useCursorFetch=true.
[19 Feb 2007 14:58]
Tonci Grgin
Mason, I tried but can't repeat reported behavior. Can you please try with latest snapshot of c/J, http://downloads.mysql.com/snapshots/mysql-connector-java-5.0/mysql-connector-java-5.0-nig... , and inform me of result?
[20 Feb 2007 0:48]
Mason Sharp
Some good news- it does indeed appear to work ok with the development branch of the JDBC Driver after a quick test. Please do not close this out quite yet- we will conduct some more tests and let you know how that goes.
[21 Feb 2007 11:18]
Mason Sharp
Just noting that in the dev branch, useCursorFetch does not seem to have an effect.
However, we were able to get it to have an effect when we also add useServerPrepStmts=true to the JDBC url as well.
>
> //
> // Handle cursor-based fetch first
> //
>
> if (this.connection.versionMeetsMinimum(5, 0, 2)
> && this.connection.getUseCursorFetch()
> && isBinaryEncoded
> && callingStatement != null
> && callingStatement.getFetchSize() != 0
> && callingStatement.getResultSetType() == ResultSet.TYPE_FORWARD_ONLY) {
>
> the condition is false due to isBinaryEncoded == false.
> This parameter came from MysqlIO.sqlQueryDirect():
>
>
> ResultSet rs = readAllResults(callingStatement, maxRows,
> resultSetType,
> resultSetConcurrency, streamResults, catalog, resultPacket,
> //amart: isBinaryEncoded hardcoded here
> false,
> -1L, unpackFieldInfo, null /* we don't need metadata for cached MD in
> this case */);
We are leaving this issue open for now pending more testing of the work-around.
[23 Feb 2007 13:11]
Mason Sharp
Another case
Attachment: CursorFetchTest.java (text/java), 1.45 KiB.
[23 Feb 2007 13:16]
Mason Sharp
I just uploaded another case another developer put together. I realized that it does not include the DDL. Sorry, I will send an updated case later.
[24 Feb 2007 14:27]
Mason Sharp
Second test
Attachment: CursorFetchTest.java (text/java), 1.99 KiB.
[24 Feb 2007 14:30]
Mason Sharp
I just added an additional file, pasted here later below for reference, too.
We are receiving the bad format for BigDecimal error.
This happens with the latest 5.0-nightly driver. With 5.0.4 error also happens without useServerPrepStmts=true.
I hope this gives you all of the information you need.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
/**
*
*/
/**
* Test case for MySQL CursorFetch bug
*/
public class CursorFetchTest
{
private static final String DRIVER = "com.mysql.jdbc.Driver";
private static final String CONNECT_URI = "jdbc:mysql://192.168.201.2:3306/test?useCursorFetch=true&useServerPrepStmts=true";
private static final String USER = "user";
private static final String PASSWORD = "password";
public static void main(String[] args) throws Exception
{
Class.forName(DRIVER);
Connection conn = DriverManager.getConnection(CONNECT_URI, USER, PASSWORD);
try
{
// Create test table
Statement stmtWrite = conn.createStatement();
stmtWrite.executeUpdate("create table foo (fkey int, fdate date, fprice decimal(15, 2), fdiscount decimal(5,3))");
try
{
Statement stmtRead = conn.createStatement();
stmtRead.setFetchSize(1000);
// Insert test data
stmtWrite.executeUpdate("insert into foo values (1, '2007-02-23', 99.9, 0.02)");
// Execute test query
ResultSet rs = stmtRead.executeQuery("select extract(year from fdate) as fyear, fprice * (1 - fdiscount) as fvalue from foo");
try
{
// Dump results
while (rs.next())
{
System.out.printf("%s\t%s\n", new Object[] {rs.getString(1), rs.getString(2)});
}
}
finally
{
rs.close();
}
}
finally
{
// Clean up
stmtWrite.executeUpdate("drop table foo");
}
}
finally
{
conn.close();
}
}
}
[7 Mar 2007 14:37]
Tonci Grgin
Hi Mason. I have slightly modified your test case from [24 Feb 15:27]:
ResultSetMetaData rsmd= rs.getMetaData();
int columnCount=rsmd.getColumnCount();
try
{
// Dump results
while (rs.next())
{
for(int i=0;i<columnCount;i++) {
System.out.print(rsmd.getColumnName(i+1)+"="+rs.getObject(i+1));
System.out.print('\t');
}//System.out.printf("%s\t%s\n", new Object[] {rs.toString() , rs.toString(2)});// rs.getString(1), rs.getString(2)
}
}
finally
{
rs.close();
}
What I have observed is this:
- *Not* using SS PS leads to correct result as statement doesn't get prepared:
070307 12:31:02 5 Connect root@localhost on test
5 Query SHOW VARIABLES
5 Query SHOW COLLATION
5 Query SET character_set_results = NULL
5 Query SET autocommit=1
5 Query SET sql_mode='STRICT_TRANS_TABLES'
5 Query drop table if exists foo26173
5 Query create table foo26173 (fkey int, fdate date, fprice decimal(15, 2), fdiscount decimal(5,3))
5 Query insert into foo26173 values (1, '2007-02-23', 99.9, 0.02)
070307 12:31:03 5 Prepare [1] select extract(year from fdate) as fyear, fprice * (1 - fdiscount) as fvalue from foo26173
5 Execute [1] select extract(year from fdate) as fyear, fprice * (1 - fdiscount) as fvalue from foo26173
5 Query drop table foo26173
5 Quit
and the result: fyear=2007 fvalue=97.90200
- *Using* SS PS produces reported error (statement gets prepared):
070307 15:25:56 6 Connect root@localhost on test
6 Query SHOW VARIABLES
6 Query SHOW COLLATION
6 Query SET character_set_results = NULL
6 Query SET autocommit=1
6 Query SET sql_mode='STRICT_TRANS_TABLES'
6 Query drop table if exists foo26173
6 Query create table foo26173 (fkey int, fdate date, fprice decimal(15, 2), fdiscount decimal(5,3))
6 Query insert into foo26173 values (1, '2007-02-23', 99.9, 0.02)
6 Query select extract(year from fdate) as fyear, fprice * (1 - fdiscount) as fvalue from foo26173
6 Query drop table foo26173
6 Quit
RETURNED:
[com.mysql.jdbc.Field@1aed5f9
catalog:
table name:
original table name:
column name: fyear
original column name:
MySQL data type: 8
Data as received from server:
03 64 65 66 00 00 00 05 . d e f . . . .
66 79 65 61 72 00 0c 3f f y e a r . . ?
00 04 00 00 00 08 80 00 . . . . . . . .
00 00 00 00 . . . .
, com.mysql.jdbc.Field@161dfb5
catalog:
table name:
original table name:
column name: fvalue
original column name:
MySQL data type: 246
Data as received from server:
03 64 65 66 00 00 00 06 . d e f . . . .
66 76 61 6c 75 65 00 0c f v a l u e . .
3f 00 17 00 00 00 f6 80 ? . . . . . . .
00 05 00 00 00 . . . . .
]
and the stack trace:
fyear=3330193155986294743 Exception in thread "main" java.sql.SQLException: !ResultSet.Bad_format_for_BigDecimal____86!0200 ... (contains some unpasteable characters)
catalog:
table name:
original table name:
column name: fvalue
original column name:
MySQL data type: 246
Data as received from server:
03 64 65 66 00 00 00 06 . d e f . . . .
66 76 61 6c 75 65 00 0c f v a l u e . .
3f 00 17 00 00 00 f6 80 ? . . . . . . .
00 05 00 00 00 . . . . .
).
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:910)
at com.mysql.jdbc.ResultSet.getNativeConvertToString(ResultSet.java:3614)
at com.mysql.jdbc.ResultSet.getNativeString(ResultSet.java:4540)
at com.mysql.jdbc.ResultSet.getStringInternal(ResultSet.java:5799)
at com.mysql.jdbc.ResultSet.getString(ResultSet.java:5597)
at com.mysql.jdbc.ResultSet.getObject(ResultSet.java:4929)
at testsuite.simple.CursorFetchTest.main(CursorFetchTest.java:50)
As can be seen in non-functioning case (SS PS), even fyear value is wrong (fyear=3330193155986294743) which means something got messed up completely and it doesn't seem to be DECIMAL value nor CentOS related to me.
[7 Mar 2007 14:42]
Tonci Grgin
Ah, hm. Tested on MySQL Server version: 5.0.34-log Source distribution on WinXP Pro SP2 localhost with JDK 1.5.0_11 and lates sources from c/J BK tree.
[7 Mar 2007 14:46]
Tonci Grgin
Please be patient with me as I'm still sick... General query logs are pasted in wrong order, sorry... - *Not* using SS PS leads to correct result as statement doesn't get prepared and takes different execution path: 070307 15:25:56 6 Connect root@localhost on test 6 Query SHOW VARIABLES 6 Query SHOW COLLATION 6 Query SET character_set_results = NULL 6 Query SET autocommit=1 6 Query SET sql_mode='STRICT_TRANS_TABLES' 6 Query drop table if exists foo26173 6 Query create table foo26173 (fkey int, fdate date, fprice decimal(15, 2), fdiscount decimal(5,3)) 6 Query insert into foo26173 values (1, '2007-02-23', 99.9, 0.02) 6 Query select extract(year from fdate) as fyear, fprice * (1 - fdiscount) as fvalue from foo26173 6 Query drop table foo26173 6 Quit and the result: fyear=2007 fvalue=97.90200 - *Using* SS PS produces reported error (statement gets prepared): 070307 12:31:02 5 Connect root@localhost on test 5 Query SHOW VARIABLES 5 Query SHOW COLLATION 5 Query SET character_set_results = NULL 5 Query SET autocommit=1 5 Query SET sql_mode='STRICT_TRANS_TABLES' 5 Query drop table if exists foo26173 5 Query create table foo26173 (fkey int, fdate date, fprice decimal(15, 2), fdiscount decimal(5,3)) 5 Query insert into foo26173 values (1, '2007-02-23', 99.9, 0.02) 070307 12:31:03 5 Prepare [1] select extract(year from fdate) as fyear, fprice * (1 - fdiscount) as fvalue from foo26173 5 Execute [1] select extract(year from fdate) as fyear, fprice * (1 - fdiscount) as fvalue from foo26173 5 Query drop table foo26173 5 Quit
[8 Mar 2007 0:22]
Mason Sharp
Hi Tonci, Sorry to hear you are not feeling so well... I hope it is nothing serious. Thanks for looking into this. We ended up using SS PS because without it, it did not seem to be using the "cursor fetch" feature we wanted. So we added it just as a work-around to make sure that cursor fetch was indeed used. We need to iterate through large result sets and avoid the OutOfMemoryException that may occur. That is why we need useCursorFetch. If we try other work-arounds like limit and multiple queries, it will not meet performance requirements. In reference to your comments regarding BIGDECIMAL, just as the last test case we submitted returns incorrect results for integer, there is a similar problem with result set corruption when using BIGDECIMAL. We basically would like to be able to use the cursor fetch feature for handling large results and not have the ResultSet data get corrupted. Again, thank you for taking the time to investigate, and I hope you get well soon.
[8 Mar 2007 2:19]
Mark Matthews
I found the bug. The driver was "trusting" the metadata that was sent by the server during prepare(), which was wrong in the case for cursor-fetched rows. The server originally tells the driver that the first column will be a LONGLONG (i.e. 8 bytes). At execute, it changes its mind, and decides it's just a 32-bit integer, and sends new metadata, which the driver was ignoring in preference to the original metadata. Since the driver expected to read 8 bytes, it did, which made the value a very large integer, rather than the value of 2007. After some more testing tomorrow, we'll post the fix, and it should be available in nightly builds after 00:00 GMT tomorrow evening for testing.
[8 Mar 2007 2:28]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/21447
[13 Apr 2007 8:34]
MC Brown
A note has been added to the 5.0.6 changelog.
Description: This works in the mysql command line, so I assume it is a Connector/J problem, but it could just be an incompatibility between Connector/J 5.0.4 and the 5.1.14 beta version. select extract(year from l_shipdate) as o_year, lineitem.l_extendedprice * (1 - lineitem.l_discount) as volume from lineitem where l_orderkey < 1000 results in this Exception: ava.sql.SQLException: !ResultSet.Bad_format_for_BigDecimal____86!1.5543^@K^@' a lready exists^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@!Resu ltSet.___in_column__87!4(com.mysql.jdbc.Field@5e7020 catalog: table name: original table name: column name: volume original column name: MySQL data type: 246 Data as received from server: 03 64 65 66 00 00 00 06 . d e f . . . . 76 6f 6c 75 6d 65 00 0c v o l u m e . . 3f 00 21 00 00 00 f6 80 ? . ! . . . . . 00 04 00 00 00 . . . . . ). What is interesting is that if I remove either projection, the command works ok: select extract(year from l_shipdate) as o_year from lineitem where l_orderkey < 1000; select lineitem.l_extendedprice * (1 - lineitem.l_discount) as volume from lineitem where l_orderkey < 1000; Both of the above work ok. How to repeat: select extract(year from l_shipdate) as o_year, lineitem.l_extendedprice * (1 - lineitem.l_discount) as volume from lineitem where l_orderkey < 1000