MySQLDataTruncation: Out of range value for column
You appear to have misunderstood how defining a DECIMAL
(or potentially NUMERIC
) column works in SQL -
Specifically, when defining the column as DECIMAL(x, y)
, x
is the total number of digits the column will store, and y
is the number of digits after the decimal point. So, in your column definition, you specified a range of -1 < column < 1. The datatype is correct, but you're attempting to specify a value outside the range the column was told to allow.
Fortunately, the fix is simple: change the column definition. I'm assuming you want to have up to 5 digits before the decimal point, which would make the column definition DECIMAL(10, 5)
.
David
Updated on June 04, 2022Comments
-
David almost 2 years
I am inserting a row using ibatis and a java webapp. The POJO has a field which needs to store a number (like 3.0 or 2.34). I have tried using BigDecimal and Double on the java side. On the MySQL side, I am using a Decimal(5, 5) data type.
When I try to insert a row which has "4" as the value for this numeric field, MySQL and iBatis throw the following exception:
org.springframework.jdbc.UncategorizedSQLException: SqlMapClient operation: encountered SQLException [ --- The error occurred in org/mySQL.xml. --- The error occurred while applying a result map. --- Check the mySQL.insertQuery. --- The error happened while setting a property on the result object. --- Cause: java.lang.RuntimeException: org.springframework.jdbc.UncategorizedSQLException: SqlMapClient operation: encountered SQLException [ --- The error occurred while applying a parameter map. --- Check the insertQuery-InlineParameterMap. --- Check the statement (update failed). --- Cause: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Out of range value for column 'numericColumn' at row 316]; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException: at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:188) at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithRowHandler(GeneralStatement.java:133) at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryWithRowHandler(SqlMapExecutorDelegate.java:649) at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryWithRowHandler(SqlMapSessionImpl.java:156) at com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryWithRowHandler(SqlMapClientImpl.java:133) at org.springframework.orm.ibatis.SqlMapClientTemplate$5.doInSqlMapClient(SqlMapClientTemplate.java:267) at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:165) at org.springframework.orm.ibatis.SqlMapClientTemplate.queryWithRowHandler(SqlMapClientTemplate.java:265) at org.myClass(myClass.java:83) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:324) at org.springframework.util.MethodInvoker.invoke(MethodInvoker.java:248) at org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean$MethodInvokingJob.executeInternal(MethodInvokingJobDetailFactoryBean.java:165) at org.springframework.scheduling.quartz.QuartzJobBean.execute(QuartzJobBean.java:66) at org.quartz.core.JobRunShell.run(JobRunShell.java:191) at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:516)
Am I using the wrong java data type for the value to be inserted? If not, why won't MySQL let me insert the value?