Bug #23401 data truncation for column decimal '0.7415'
Submitted: 18 Oct 2006 0:50 Modified: 18 Oct 2006 2:01
Reporter: YangZD yang Email Updates:
Status: Duplicate Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:5.0.3 OS:Microsoft Windows (windowserver2003)
Assigned to: CPU Architecture:Any
Tags: data truncation decimal 0.7415

[18 Oct 2006 0:50] YangZD yang
Description:
table yangzd
column 'id':type = int;
column 'count': type=decimal,length=5,decimals=4;
mysql 5.0.22,connector/j 5.03
code:jt.update("INSERT INTO yangzd VALUES(?,?)",
new Object[] {11,0.7415});
org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [INSERT INTO yangzd VALUES(?,?)]; SQL state [01004]; error code [0]; Data truncation: Data truncated for column 'count' at row 1; nested exception is com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data truncated for column 'count' at row 1
Caused by: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data truncated for column 'count' 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)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:101)
at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:712)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:499)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:706)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:765)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:773)
at com.szwzb.yangzd.YangzdJDBCDAO.save(YangzdJDBCDAO.java:8)
at com.szwzb.yangzd.YangzdDAOTest.testSave(YangzdDAOTest.java:28)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at junit.framework.TestCase.runTest(TestCase.java:154)
at junit.framework.TestCase.runBare(TestCase.java:127)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at org.eclipse.jdt.internal.junit.runner.junit3.JUnit3TestReference.run(JUnit3TestReference.java:128)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)

How to repeat:
1.create a table including a decimal column(length=5,decimals=4)
2.insert 0.7415 into the table using connector/j
[18 Oct 2006 1:07] Mark Matthews
If you're using PreparedStatement.setBigDecimal(...), then this is a duplicate of Bug#22290 (fixed in the source repository for Connector/J for some time now, please test with a nightly snapshot from http://downloads.mysql.com/snapshots.php#connectorj).

If you're using something like setString() or setDouble(), then (1) Don't, as there's floating point math involved, you should only use setBigDecimal() with DECIMAL type columns, and more than likely you're hitting Bug#23260 (a server bug, recently verified, not yet fixed).
[18 Oct 2006 2:01] YangZD yang
It had fixed in mysql-connector-java-5.0-nightly-20061018-bin.jar