| Bug #22639 | PreparedStatement and data truncation | ||
|---|---|---|---|
| Submitted: | 24 Sep 2006 14:28 | Modified: | 25 Sep 2006 21:16 |
| Reporter: | Alexander Hristov (Candidate Quality Contributor) | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | Connector / J | Severity: | S2 (Serious) |
| Version: | 5.0.3 | OS: | Microsoft Windows (Windows XP SP2) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | data truncation, double, prepared statement | ||
[25 Sep 2006 13:03]
Tonci Grgin
Alexander, thanks for your problem report.
From the manual:
"If a numeric expression contains any strings, they are converted to double-precision floating-point values and the expression is approximate."
I believe that actual failing value is something like this: 9734.1300000154354212454. Since this behavior was fixed in connector/J please try latest nightly build from our pages.
Test case:
-Xmx256M -Dcom.mysql.jdbc.testsuite.url.default=jdbc:mysql://localhost:3306/test?user=root&password=&jdbcCompliantTruncation=true&useServerPrepStmts=true
package testsuite.simple;
import testsuite.BaseTestCase;
import java.math.BigDecimal;
public class TestBug22290 extends BaseTestCase {
public TestBug22290 (String name) {
super(name);
}
/*
*Test for Bugs: #22290: Updating value in DECIMAL column with same datatype causes data truncation error,
* @throws Exception
*/
public void testbug22290() throws Exception {
createTable("testbug22290", "(`id` int(11) NOT NULL default 1,`cost` decimal(6,2) NOT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8");
assertEquals(this.stmt.executeUpdate("INSERT INTO testbug22290 (`id`,`cost`) VALUES (1,1.00)"),1);
try {
this.pstmt = this.conn.prepareStatement("update testbug22290 set cost = cost + ? where id = 1");
this.pstmt.setBigDecimal(1, new BigDecimal(1.11));
System.out.println("Query to be executed: "+this.pstmt.toString());
assertEquals(this.pstmt.executeUpdate(),1);
this.pstmt.clearParameters();
//BUG#22693
double val = 8517.45;
assertEquals(this.stmt.executeUpdate("INSERT INTO testbug22290 (`id`,`cost`) VALUES (2,"+val+")"),1);
this.pstmt = this.conn.prepareStatement("INSERT INTO testbug22290 VALUES(3, ?)");
this.pstmt.setDouble(1, val);
System.out.println("Query to be executed: "+this.pstmt.toString());
assertEquals(this.pstmt.executeUpdate(),1);
} finally {
closeMemberJDBCResources();
}
}
/**
* @param args
*/
public static void main(String[] args) {
junit.textui.TestRunner.run(TestBug22290.class);
}
}
Results:
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
Query to be executed: com.mysql.jdbc.ServerPreparedStatement[2] - INSERT INTO testbug22290 VALUES(3, 8517.45)
Time: 116,282
OK (1 test)
C:\mysql507\bin>mysql -uroot test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9 to server version: 5.0.24-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> select * from testbug22290;
+----+---------+
| id | cost |
+----+---------+
| 1 | 2.11 |
| 2 | 8517.45 |
| 3 | 8517.45 |
+----+---------+
3 rows in set (0.00 sec)
mysql>
060925 14:53:30 11 Connect root@localhost on test
11 Query SET NAMES utf8
11 Query SET character_set_results = NULL
11 Query SHOW VARIABLES
11 Query SHOW COLLATION
11 Query SET autocommit=1
11 Query SET sql_mode='STRICT_TRANS_TABLES'
060925 14:53:31 11 Query SELECT VERSION()
11 Query DROP TABLE IF EXISTS testbug22290
11 Query CREATE TABLE testbug22290 (`id` int(11) NOT NULL default 1,`cost` decimal(6,2) NOT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8
11 Query INSERT INTO testbug22290 (`id`,`cost`) VALUES (1,1.00)
060925 14:53:47 11 Prepare [1]
060925 14:53:50 11 Execute [1] update testbug22290 set cost = cost + '1.1100000000000000976996261670137755572795867919921875' where id = 1
11 Query INSERT INTO testbug22290 (`id`,`cost`) VALUES (2,8517.45)
11 Prepare [2]
11 Execute [2] INSERT INTO testbug22290 VALUES(3, 8517.45)
060925 14:53:53 9 Query select * from testbug22290
[25 Sep 2006 19:38]
Alexander Hristov
I don't quite understand the quote about numeric expressions with strings, as it is the prepared statement with a single double parameter that fails. The parameter is literally assigned, it doesn't come from some computation. Never mind, I upgraded MySQL from 5.0.18 to 5.0.24a and I tried with mysql-connector-java-5.0-nightly-20060925.zip but it is still failing for the same value (when using server prepared statements) Any more information that I can give you in order to pinpoint the problem?
[25 Sep 2006 19:44]
Alexander Hristov
Curiously, it does NOT fail with MySQL Server 5.0.22 running on Linux (Fedora Core 5)
[25 Sep 2006 21:16]
Alexander Hristov
Sorry, the Linux Server wasn´t operating in strict mode. Setting it to strict causes the error to appear in that version too. However, it seems that this issue is not related to Connector/J as with Connector/NET the same thing happens, but Connector/NET chooses not to raise an exception when the warning arrives
Description: (This bug is very similar to 22290, but it doesn't involve BigDecimals, just ordinary doubles) I'm using MySQL 5.0.18-nt and MySQL Connector/J 5.0.3, and I'm also having strange issues with prepared statements and numeric (decimal) fields. A field created as numeric(6,2) will accept some double values and reject others. An example rejected value is 8517.45 Can this be a server issue? Now I have no idea of the format of the packets sent to the server, but in this case the packet 00 00 00 00 17 01 00 00 . . . . . . . . 00 00 01 00 00 00 00 01 . . . . . . . . 05 00 9a 99 99 99 b9 a2 . . . . . . . . c0 40 . @ seems to have the correct IEEE representation of that value. Nonetheless, the server issues a Note 1265 Data truncated for column 'weight' at row 1 Here are some more test cases: failed for 40c30310a3d70a3d 9734.13 failed for 40c0fc8f5c28f5c3 8697.12 failed for 40c2bf5ae147ae14 9598.71 failed for 40c0aa1851eb851f 8532.19 failed for 40c27ddae147ae14 9467.71 failed for c08ab9999999999a -855.2 failed for 40c0a14666666666 8514.55 failed for 40c2c23c28f5c28f 9604.47 failed for c0c2cb30a3d70a3d -9622.38 failed for 40c2078f5c28f5c3 9231.12 failed for c0c121e666666666 -8771.8 failed for 40c0dcee147ae148 8633.86 failed for c0c2568e147ae148 -9389.11 failed for 40c104a3d70a3d71 8713.28 failed for c0c31810a3d70a3d -9776.13 failed for c0c1dc8666666666 -9145.05 failed for c056c66666666666 -91.1 failed for 40c0448f5c28f5c3 8329.12 failed for 40c08be666666666 8471.8 failed for 40c3569851eb851f 9901.19 failed for 40c28110a3d70a3d 9474.13 failed for c0c16cd1eb851eb8 -8921.64 How to repeat: public class Test { public static void main(String[] args) throws Exception { Class.forName("com.mysql.jdbc.Driver"); Properties p = new Properties(); p.setProperty("user","..."); p.setProperty("password","..."); p.setProperty("useServerPrepStmts","true"); Connection con = (Connection)DriverManager.getConnection("jdbc:mysql://localhost/test",p); Statement sql = con.createStatement(); sql.executeUpdate("drop table if exists foo "); sql.executeUpdate("Create table foo (weight numeric(6,2))"); double value = 8517.45; // This succeeds sql.executeUpdate("insert into foo values ("+value+")"); // This fails if useServerPrepStmts is true, suceeds if useServerPrepStmts // is false PreparedStatement ps = con.prepareStatement("insert into foo values (?)"); ps.setDouble(1,value); ps.executeUpdate(); } Suggested fix: useServerPrepStmts = false