| Bug #2606 | Strange result when using 'YEAR' datatype in PreparedStatements. | ||
|---|---|---|---|
| Submitted: | 2 Feb 2004 5:03 | Modified: | 2 Feb 2004 18:31 |
| Reporter: | [ name withheld ] | ||
| Status: | Closed | ||
| Category: | Connector/J | Severity: | S2 (Serious) |
| Version: | 3.1.1 | OS: | Microsoft Windows (Win98) |
| Assigned to: | Mark Matthews | Target Version: | |
[2 Feb 2004 7:50]
Mark Matthews
I'm not able to repeat this with Connector/J and MySQL-4.1.1. Please try version 4.1.1 of the server. There were quite a few issues with prepared statements in 4.1.0 that are fixed in 4.1.1. If you can still repeat it, we'd need a _full_ testcase which would include all related schema and data attached to this bug report.
[2 Feb 2004 16:51]
[ name withheld ]
Okay, I've done a deep inspection on the problem. Here are my results:
1) I upgraded (actually it was a clean install) to MySQL 4.1.1 on the Linux box.
The problem was still there, but something changed. The output of the test above in
this case:
2003<br>
ux L-standard binary /charsets/<br>
As you can see, only the message changed, but it is constant every time.
2) Wrote a simle java JDBC test program to see whether the problem is with Tomcat. It is
not. I've run the program both on the Win98 (JSDK1.4.2) and on linux (JSDK1.4.0). The
result is the same. (The database was on Linux in both cases.)
3) Installed MySQL 4.1.1 on the Win98. Tested with Tomcat. Guess what. Yes. But, the
result seems to be a bit nasty, it is not constant, it varies depending on the last sql
error.
A case:
2003
doesn't exist 6 nd
(Stange, huh?)
4) Run the java tester both from Win and from Linux, on the database resides on the Win
machine. Same.
I used a clean installation with a clean database with a simple table. The problem exists
on two platforms. Now I can't imagine why you can't reproduce it.
It looks like some pointer gets to misspoint. Or a routine thinks it is a string (and read
till the ending zero) instead of the first few bytes as a number.
5) I tried to change from .getString(1) to .getInt(1) and here it comes:
Invalid value for getInt() - ' out28800 pc-linux L-standard binary /charsets/
' in column 1
SQLState: S1009
VendorError: 0
So, I guess the JDBC driver does not recognises the bytes received in a puffer as a YEAR
value, and treats it as a string. (Probably I'm wrong, please ensure me that the data
stream got from the server goes through some decoding.)
Other client programs works fine. The problem is JDBC (Java) specific.
What's next?
Oh, and the java.lang.ArrayIndexOutOfBoundsException is still there too.
[2 Feb 2004 17:25]
Mark Matthews
I now see why the issue is happening in this specific case, MySQL is returning the data as type FIELD_TYPE_YEAR (0xd), but even in libmysql itself, there is a descrepancy between two different functions that read the data from a prepared statement, one version will read years correctly (fetch_results), and the other (mysql_bind_result) will raise an error of 'unknown data type'.
[2 Feb 2004 18:31]
Mark Matthews
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.
If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information
about accessing the source trees is available at
http://www.mysql.com/doc/en/Installing_source_tree.html
Additional info:
Thank you for your bug report. This is now fixed in the 3.1 tree. You can get the fix from
the nightly snapshot at http://downloads.mysql.com/snapshots.php
Description: - MySQL server 4.1.0-alpha on Linux. (Mandrake 8.1) - mysql-connector-java-3.1-nightly-20040129-bin.jar (on Win98) - JSDK 1.4.2 b-28 (on Win98) - Apache Tomcat 4.1.29 (on Win98) The error occurs during servlet execution. How to repeat: The table: CREATE TABLE test (iso_week int(2), iso_year year(4)); insert into test (iso_week, iso_year) values (1,2003); The code: java.sql.Statement stmt=conn.createStatement(); java.sql.ResultSet rs=stmt.executeQuery("select iso_year from test"); rs.next(); String setval= rs.getString(1); out.println(setval + "<br>"); rs.close(); stmt.close(); java.sql.PreparedStatement pstmt=conn.prepareStatement("select iso_year from test"); rs=pstmt.executeQuery(); rs.next(); String setval= rs.getString(1); out.println(setval + "<br>"); rs.close(); pstmt.close(); The result: 2003<br> eout28800 -standard /demo.pid 6 ish/ ish_ci cp850_general_ci latin1_german1_ci hp8_english_ci koi8r_general_ci latin1_swedish_ci latin2_general_ci swe7_swedish_ci ascii_general_ci ujis sjis cp1251_bulgar<br> Interestingly, above approximately 15000 rows in that table an exception is thrown: (The Trace): java.sql.SQLException: java.lang.ArrayIndexOutOfBoundsException at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1150) at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:804) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1559) Suggested fix: Workaround: do not to use YEAR datatype. :)