| Bug #22290 | Calling SP with BigDecimal param set as BigDecimal(string) calls setString | ||
|---|---|---|---|
| Submitted: | 13 Sep 2006 2:28 | Modified: | 27 Sep 2006 3:32 |
| Reporter: | Ben Anderson | ||
| Status: | Closed | ||
| Category: | Connector/J | Severity: | S3 (Non-critical) |
| Version: | 3.1.13 and 5.0.3 | OS: | Microsoft Windows (Windows XP) |
| Assigned to: | Target Version: | ||
| Tags: | jdbc, truncation, decimal | ||
[13 Sep 2006 2:44]
Ben Anderson
I suppose my main point is that adding two values with a scale of 2 together will always produce a result with a scale 2. I don't see how truncation can occur in this scenario so I'm interested to know why it is being reported.
[15 Sep 2006 4:26]
Ben Anderson
Test case that extends BaseBugReport
Attachment: TruncationBugReport.java (text/x-java), 1.95 KiB.
[15 Sep 2006 5:34]
Ben Anderson
The same behaviour is observed when using Connector/J 5.0.3. The MySQL server version is 5.0.24.
[18 Sep 2006 1:43]
Ben Anderson
Setting 'useServerPrepStmts=false' on the JDBC connection also prevents the problem occurring - although it sounds like we would be hurting performance by turning this off.
[19 Sep 2006 22:13]
Tonci Grgin
Test case
Attachment: TestBug22290.java (text/x-java), 1.18 KiB.
[19 Sep 2006 22:21]
Tonci Grgin
Hi Ben and thanks for excellent problem report. We do appreciate it. I was unable to repeat the problem reported with attached test case on: - MySQL 5.0.24 BK on WinXP Pro SP2 localhost - Both 3.1 and 5.0 connector/J (SVN) - JDK 1.5.07 - All connect options tested as you suggested, my final connect string was: &jdbcCompliantTruncation=true&useServerPrepStmts=true Results: mysql> select * from testbug22290; +----+------+ | id | cost | +----+------+ | 1 | 2.11 | +----+------+ 1 row in set (0.00 sec) -- Done. Connected to 5.0.24-log Query to be executed: com.mysql.jdbc.ServerPreparedStatement[1] - update testbug22290 set cost = cost + '1.11' where id = 1 Time: 1,156 OK (1 test) Ben, if you see anything in my test case that should be changed in order to repeat problem, please reopen the report.
[20 Sep 2006 2:30]
Ben Anderson
MySQL initialisation file
Attachment: my.ini (application/octet-stream, text), 9.03 KiB.
[20 Sep 2006 2:34]
Ben Anderson
Tonci, I tried your test case and the issue is still there. I should have included the my.ini file that I was using (I've now attached it). The sql-mode I am using is "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" I could make your test case pass I've I removed the "STRICT_TRANS_TABLES" option. Could you recheck your test case with the STRICT_TRANS_TABLES option set? Ben
[20 Sep 2006 9:32]
Tonci Grgin
Ben, your report still makes perfect sense to me but I wasn't able to repeat it yesterday even with STRICT_TRANS_TABLES as you can see from my log: 060919 22:08:19 7 Connect root@localhost on test 7 Query SET NAMES latin1 7 Query SET character_set_results = NULL 7 Query SHOW VARIABLES 7 Query SHOW COLLATION 7 Query SET autocommit=1 7 Query SET sql_mode='STRICT_TRANS_TABLES' 7 Query SELECT VERSION() 7 Query DROP TABLE IF EXISTS testbug22290 7 Query CREATE TABLE testbug22290 (`id` int(11) NOT NULL default '1',`cost` decimal(10,2) NOT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 7 Query INSERT INTO testbug22290 (`id`,`cost`) VALUES (1,'1.00') 7 Prepare [1] 060919 22:08:29 7 Execute [1] update testbug22290 set cost = cost + '1.11' where id = 1 060919 22:08:52 8 Connect root@localhost on test 060919 22:09:00 8 Query select * from testbug22290 060919 22:09:13 7 Query DROP TABLE IF EXISTS testbug22290 7 Quit Then I tried more... We introduced 'STRICT_TRANS_TABLES' server mode because it's much more efficient than checking warnings after every statement for truncations. From the manual: "If a numeric expression contains any strings, they are converted to double-precision floating-point values and the expression is approximate." Your test fail in mysql cl client (expected) so I changed code to use numbers instead of strings and test passes (jdbcCompliantTruncation and useServerPrepStmts *both* true): Done. Connected to 5.0.24-log Query to be executed: com.mysql.jdbc.ServerPreparedStatement[1] - update testbug22290 set cost = cost + '1.1100000000000000976996261670137755572795867919921875' where id = 1 Time: 0,579 OK (1 test) -- mysql> update testbug22290 set cost = cost + '1.11' where id = 1; Query OK, 1 row affected, 1 warning (0.03 sec) Rows matched: 1 Changed: 1 Warnings: 1 mysql> show warnings; +-------+------+-------------------------------------------+ | Level | Code | Message | +-------+------+-------------------------------------------+ | Note | 1265 | Data truncated for column 'cost' at row 1 | +-------+------+-------------------------------------------+ and mysql> update testbug22290 set cost = cost + 1.11 where id = 1; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 Concluding from this test, error you get has nothing to do with connector/J and/or stored procedures but with used server mode which requires more attention when writing queries.
[20 Sep 2006 10:02]
Ben Anderson
Thanks for looking at this again Tonci but I don't understand your explanation. The only statement that I am sending to the server is via the prepared statement "update test set cost = cost + ? where id = 1" I don't see where in my code the statement is being converted to "update test set cost = cost + '?' where id = 1" - this can only be happening in the JDBC driver or server itself surely?
[20 Sep 2006 10:07]
Ben Anderson
In fact, it looks like your log highlights the problem: 7 Query INSERT INTO testbug22290 (`id`,`cost`) VALUES (1,'1.00') 7 Prepare [1] 060919 22:08:29 7 Execute [1] update testbug22290 set cost = cost + '1.11' where id = 1 ^^^^^^ Where did the single quotes come from? They were not part of my original prepared statement and should not be added by whatever is creating the update statement executed by the server.
[20 Sep 2006 10:55]
Tonci Grgin
Ben, in your example you are sending string '1.11' to server in (setBigDecimal(1,"1.11")).
Passing string value to c/J causes buffer type for parameter to be recognized as string
type (253 I think) causing server to receive "1.11" instead of 1.11 which, in turn,
triggers conversion STRING -> FLOAT triggering DATA TRUNCATION on the server in
STRICT_TRANS_TABLES mode. Try using numbers like this:
this.pstmt.setBigDecimal(1, new BigDecimal(1.11));
System.out.println("Query to be executed: "+this.pstmt.toString());
...
[20 Sep 2006 11:02]
Ben Anderson
Tonci,
In my test case and your test case, the same syntax has been used:
ps.setBigDecimal(1, new BigDecimal("1.11"));
This will always pass a BigDecimal value to the prepared statement, not a String.
Ben
[20 Sep 2006 11:06]
Ben Anderson
Tonci, I also found that attempting to change the sql_mode via a 'set' command did not actually change the sql_mode. You need to change your my.ini file and restart the server for it to take effect. Could you try the test case with the my.ini file that I attached?
[20 Sep 2006 11:47]
Tonci Grgin
Ben, I'm obviously not reaching you... Please, reread and reexamine my post. - I am using your my.ini file - I have rewrote test case to use setBigDecimal to pass decimal value, new BigDecimal(1.11), notice 1.11 *WITHOUT quotes*. Everything works with compliant truncation and server-side prepared statements set to true. The only question is whether parameter type buffer should be set to STRING when passing quoted value to setBigDecimal. If you want I can consult on this.
[20 Sep 2006 14:29]
Ben Anderson
Tonci, your method of instanciating a BigDecimal value is imprecise. Here is what the java api documentation has to say about passing in a double to the BigDecimal constructor, as you are suggesting: http://java.sun.com/j2se/1.4.2/docs/api/java/math/BigDecimal.html#BigDecimal(double) BigDecimal public BigDecimal(double val) Translates a double into a BigDecimal. The scale of the BigDecimal is the smallest value such that (10scale * val) is an integer. Note: the results of this constructor can be somewhat unpredictable. One might assume that new BigDecimal(.1) is exactly equal to .1, but it is actually equal to .1000000000000000055511151231257827021181583404541015625. This is so because .1 cannot be represented exactly as a double (or, for that matter, as a binary fraction of any finite length). Thus, the long value that is being passed in to the constructor is not exactly equal to .1, appearances notwithstanding. The (String) constructor, on the other hand, is perfectly predictable: new BigDecimal(".1") is exactly equal to .1, as one would expect. Therefore, it is generally recommended that the (String) constructor be used in preference to this one. ... The javadoc is telling me if I want a BigDecimal value that precisely represents 1.11 then I should be using the String constructor to do so. I accept that you're not seeing the issue but I'm certain the problem is not related to how I am constructing the BigDecimal value that gets passed to setBigDecimal!
[20 Sep 2006 15:21]
Tonci Grgin
Hi Ben. I agree with you but will have to change the synopsis. Calling SP with DECIMAL param passed as STRING to setDecimal calls setString which makes MySQL server do STRING -> FLOAT (documented) conversion resulting in (expected) truncation warning. Test case and details attached to report. I am not sure if this can be treated as bug at all but will ask for consult.
[20 Sep 2006 15:57]
Mark Matthews
The JDBC driver is operating as expected. The only way to call a stored procedure and have prepared semantics (as required by JDBC) requires that the driver use the emulated client-side prepared statements. Since in this case, the values are eventually sent as a normal statement, the parameters are exposed to type conversions that might not happen in a server-side statement. Prior versions of MySQL didn't do an intermediate float conversion (that I can remember) when it was known that the value was a DECIMAL, if the value was supplied as a string (it was string->decimal), and sending as a bare number actually caused conversion in some cases). We'll have to test if sending as a bare number is now a better solution, but Tonci should investigate whether the intermediate conversion is a bug (in my opinion it is, the server knows the parameter is a DECIMAL, and therefore should know that it can go direct from string -> DECIMAL).
[20 Sep 2006 20:23]
Keith Winton
We have recently tried to use the 5.0.3 driver for a customer, and most all of our application's prepared statements fail with that driver because of this error. The DB columns are decimal(15,5) (just fyi, to mirror how they exist on other db platforms, like the IBM iSeries), and the DAO objects do a setDouble() method (internal java object is a double). All flavors of IBM db's, and MS-SQL all work find, as do the prior Connector/J versions customer was using In general we want to use strict db checking, so we will have to tell this customer not to upgrade the driver, and hope it gets resolved.
[26 Sep 2006 21:22]
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/12565
[26 Sep 2006 21:32]
Mark Matthews
Fixed for 5.0.4/5.1.0, see nightly snapshots after 00:00 GMT Sep-27 at http://downloads.mysql.com/snapshots.php#connector-j if you want to test before release.
[27 Sep 2006 3:32]
Ben Anderson
Great! - thanks for the work on this one guys.
[1 Oct 2006 12:16]
Tonci Grgin
Continued in BUG#22869
Description: When adding particular decimal(10,2) values together in an 'update' statement that stores the result in a decimal(10,2) column, a com.mysql.jdbc.MysqlDataTruncation error is thrown by the JDBC driver. For example, I have a 'cost' column in an InnoDB table (named test) that has been defined as decimal(10,2). I have one row in the table with a 'cost' value of 1.00. I attempt to add '1.11' to the current value of the 'cost' column via an update statement using JDBC i.e. update test set cost = cost + ? where id = ? The following error is returned from the JDBC driver: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data truncated for column 'cost' at row 1 at com.mysql.jdbc.SQLError.convertShowWarningsToSQLWarnings(SQLError.java:709) at com.mysql.jdbc.MysqlIO.scanForAndThrowDataTruncation(MysqlIO.java:3461) at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1198) at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:677) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1357) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1274) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1259) Note that the update works without generating an exception for all even decimal(10,2) values that I've tried - but adding an odd decimal(10,2) value will nearly always generate exception, particularly if the current value of 'cost' is odd. I'm using the current GA release of the server (5.0.24) under Windows XP with an application that uses the production (3.1.13) Connector/J JDBC driver. How to repeat: Create the following table: CREATE TABLE `bentest`.`test` ( `id` int(11) NOT NULL default '1', `cost` decimal(10,2) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Prime the table with the following row: INSERT INTO `bentest`.`test` (`id`,`cost`) VALUES (1,'1.00'); Run the following java program: public class MyBug { public static void main(String[] args) { Connection conn = null; try { Class.forName("com.mysql.jdbc.Driver").newInstance(); conn = DriverManager .getConnection("jdbc:mysql://localhost:3306/bentest" + "?user=bentest&password=password"); PreparedStatement ps = conn.prepareStatement(// "update test set cost = cost + ? where id = 1"); ps.setBigDecimal(1, new BigDecimal("1.11")); ps.executeUpdate(); } catch (Exception e) { e.printStackTrace(); } finally { if (conn != null) { try { conn.close(); } catch (Exception e) { } } } } } Re-run the test class using BigDecimal values of 1.10 or 1.12 (these values work ok - only odd values appear to cause the problem). Suggested fix: Setting the jdbcCompliantTruncation=false will prevent the JDBC driver from throwing an exception. Modifying the update statement to truncate the result of the addition also works (although is an unacceptable workaround for our application) i.e. "update test set cost = truncate(cost + ?, 2) where id = 1" Note that the following modification does not work (i.e. data truncation exception still occurs): "update test set cost = cost + truncate(?, 2) where id = 1" This suggests that the problem is not related to how the BigDecimal parameter is being passed to the server (changing the java prepared statement handling to use setString instead of setBigDecimal has no effect either).