Data truncation: Incorrect truncated value DOUBLE: "3ML80909540"
I have a legacy database and the relevant domain classes are like
class Assets{
String id
AssetsFinancial assetsFinancial = new AssetsFinancial()
static constraints = {
assetsFinancial(nullable: true)
}
static mapping = {
version false
id generator: "assigned", column: '`id`'
assetsFinancial column: '`id`', insertable: false, updateable: false
}
}
and
class AssetsFinancial{
Integer appraisal
Boolean doubleTerVar
static mapping = {
version false
id generator: "assigned"
}
static constraints = {
appraisal nullable: true
doubleTerVar nullable: true
}
}
when I update the AssetsFinancial property for example assetsFinancialInstance.appraisal=2222
and hibernate tries to "commit" its changes, it gives me
| Error 2014-12-25 18:56:34,459 [http-bio-8080-exec-9] ERROR spi.SqlExceptionHelper - Data truncation: Truncated incorrect DOUBLE value: '3ML80909540'
| Error 2014-12-25 18:56:34,726 [http-bio-8080-exec-9] ERROR errors.GrailsExceptionResolver - MysqlDataTruncation occurred when processing request: [POST] /someController/someAction/1295448
Data truncation: Truncated incorrect DOUBLE value: '3ML80909540'. Stacktrace follows:
Message: could not execute statement
Line | Method
->> 53 | doFilter in grails.plugin.springsecurity.web.filter.GrailsAnonymousAuthenticationFilter
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
| 49 | doFilter in grails.plugin.springsecurity.web.authentication.RequestHolderAuthenticationFilter
| 82 | doFilter in grails.plugin.springsecurity.web.authentication.logout.MutableLogoutFilter
| 67 | doFilter in com.studentsonly.grails.plugins.uiperformance.CacheFilter
| 270 | doFilter in com.planetj.servlet.filter.compression.CompressingFilter
| 1110 | runWorker in java.util.concurrent.ThreadPoolExecutor
| 603 | run . . . in java.util.concurrent.ThreadPoolExecutor$Worker
^ 722 | run in java.lang.Thread
Caused by MysqlDataTruncation: Data truncation: Truncated incorrect DOUBLE value: '3ML80909540'
->> 3560 | checkErrorPacket in com.mysql.jdbc.MysqlIO
I spent many hours to find the cause, but could not figure out the reason, any hint would be helpful for me. If you need more information, please ask.
Update: Hibernate logs
2014-12-26 14:51:35,099 [http-bio-8080-exec-3] DEBUG hibernate.SQL - update assets_financial set appraisal=?, double_ter_var=? where id=?
2014-12-26 14:51:35,100 [http-bio-8080-exec-3] TRACE sql.BasicBinder - binding parameter [1] as [INTEGER] - [1003]
2014-12-26 14:51:35,100 [http-bio-8080-exec-3] TRACE sql.BasicBinder - binding parameter [2] as [BOOLEAN] - [false]
2014-12-26 14:51:35,101 [http-bio-8080-exec-3] TRACE sql.BasicBinder - binding parameter [3] as [BIGINT] - [1298944]
source to share
And finally I found the culprit
As you can see from the SQL logging
2014-12-26 14:51:35,101 [http-bio-8080-exec-3] TRACE sql.BasicBinder - binding parameter [3] as [BIGINT] - [1298944]
Here the datatype is BIGINT, but it must be VARCHAR, then I add String id
to mineAssetsFinancial
class AssetsFinancial{
String id
//rest of code
}
and now everything is fine :)
source to share