| Bug #7829 | Data truncated for column | ||
|---|---|---|---|
| Submitted: | 12 Jan 2005 8:26 | Modified: | 12 Jan 2005 14:28 |
| Reporter: | Marwan Totah | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | Connector / J | Severity: | S3 (Non-critical) |
| Version: | 3.1.16 | OS: | Windows (windows/XP) |
| Assigned to: | CPU Architecture: | Any | |
[12 Jan 2005 11:34]
Aleksey Kishkin
It would be great if you provide us testcase for this error.
[12 Jan 2005 12:02]
Marwan Totah
I did more analysis...the following is happening:
first some background: historically, I had the following problem with float numbers, example:
create table xx ( c decimal(28,15) default 0);
insert into xx values(10.20);
insert into xx values('10.20'); //notice the quotes
select * from xx;
+--------------------+
| c |
+--------------------+
| 10.199999999999999 |
| 10.200000000000000 |
+--------------------+
I want to store exact 10.20 in the column not 10.19999 ...
to achieve this, in my code, I use:
resultSet.updateString(x, bigDecimal.toString())
instead of:
resultSet.updateBigDecimal(x, bidDecimal)
when I switched my code back to updateBigDecimal() the exception did not happen.
the value that was causing the exception was : 471.18644067796610150000000000000000
which has 16 decimals and the column is defined for 15?
I fixed my code not to pass more than 15 decimals to rs.updateString() and that worked too.
Q: is this an issue the driver should handle?
Q: is there better way to keep my '10.20' value and not have it parsed as a float, also I am concerned about preformance here.
[12 Jan 2005 12:48]
Aleksey Kishkin
We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug. Support on using our products is available both free in our forums at http://forums.mysql.com and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/ Thank you for your interest in MySQL.
[12 Jan 2005 14:28]
Mark Matthews
If you don't want truncation checked for, add 'jdbcCompliantTruncation=false' to your JDBC url, and the driver will act as Connector/J 3.0 did.
Always a good idea to check the CHANGES file that comes with the driver when upgrading:
"- The driver is more strict about truncation of numerics on
ResultSet.get*(), and will throw a SQLException when truncation is
detected. You can disable this by setting 'jdbcCompliantTruncation' to
false (it is enabled by default, as this functionality is required
for JDBC compliance)."
Description: trying the new driver on a batch that works fine with the old driver 3.0.x I got the following exception: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data truncated for column 'C_RAWTOTAL' at row 1 at com.mysql.jdbc.SQLError.convertShowWarningsToSQLWarnings(SQLError.java:695) at com.mysql.jdbc.MysqlIO.scanForAndThrowDataTruncation(MysqlIO.java:3321) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1744) at com.mysql.jdbc.Connection.execSQL(Connection.java:2297) at com.mysql.jdbc.Connection.execSQL(Connection.java:2226) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1812) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1906) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1831) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1693) at com.mysql.jdbc.UpdatableResultSet.insertRow(UpdatableResultSet.java:387) ======================================== more info: the column in question 'C_RAWTOTAL' is of type decimal(28,15) with default 0. I am using updateable resultset, and insertRow in this case. db is 4.1.8 standard running on RedHat Ent 3.0 on the same lan. How to repeat: I've tried it twice with the same error. I can't give you a sample to repeat the error because this batch collects data from 2 legacy different db's and inserts them into mysql. I'll try to zoom on the problem and give more feedback.