others-how to solve `com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: '' for column 'xxx' at row y` ?

Oct 13, 2022

1. Purpose

In this post, I will demonstrate how to solve the following problem when trying to insert a row in mysql database:

org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [INSERT INTO mytable(order_no, gmt_refund, gmt_close, fund_bill_list, voucher_detail_list) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);]; Data truncation: Incorrect datetime value: '' for column 'gmt_refund' at row 1; nested exception is com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: '' for column 'gmt_refund' at row 1
    at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:100)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:660)
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:909)
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:970)
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:980)
    at com.bswen.app1.dao.mydao.insertIpnRecord(mydao.java:570)
    at com.bswen.app1.controllers.AlipayController.lambda$insert_ipn_record$2(AlipayController.java:283)
    at com.bswen.app1.controllers.AlipayController$$Lambda$9/1686123214.accept(Unknown Source)
    at io.reactivex.internal.observers.LambdaObserver.onNext(LambdaObserver.java:63)
    at io.reactivex.internal.operators.observable.ObservableSubscribeOn$SubscribeOnObserver.onNext(ObservableSubscribeOn.java:58)
    at io.reactivex.internal.operators.observable.ObservableScalarXMap$ScalarDisposable.run(ObservableScalarXMap.java:248)
    at io.reactivex.internal.operators.observable.ObservableJust.subscribeActual(ObservableJust.java:35)
    at io.reactivex.Observable.subscribe(Observable.java:12090)
    at io.reactivex.internal.operators.observable.ObservableSubscribeOn$SubscribeTask.run(ObservableSubscribeOn.java:96)
    at io.reactivex.Scheduler$DisposeTask.run(Scheduler.java:578)
    at io.reactivex.internal.schedulers.ScheduledRunnable.run(ScheduledRunnable.java:66)
    at io.reactivex.internal.schedulers.ScheduledRunnable.call(ScheduledRunnable.java:57)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at java.lang.Thread.run(Thread.java:745)
Caused by: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: '' for column 'gmt_refund' at row 1
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3968)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3906)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2524)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2677)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2549)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861)
    at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2073)
    at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2009)
    at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5098)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1994)
    at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:105)
    at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:916)
    at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:909)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:644)

The core error messages are:

org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [INSERT INTO mytable(order_no, gmt_refund, gmt_close, fund_bill_list, voucher_detail_list) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);]; Data truncation: Incorrect datetime value: '' for column 'gmt_refund' at row 1; nested exception is com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: '' for column 'gmt_refund' at row 1

The table structure is as follows:

mysql> desc mytable;
+---------------------+---------------+------+-----+---------+-------+
| Field               | Type          | Null | Key | Default | Extra |
+---------------------+---------------+------+-----+---------+-------+
| order_no            | varchar(255)  | NO   | MUL |         |       |
| gmt_refund          | timestamp     | YES  |     | NULL    |       |
| fund_bill_list      | varchar(512)  | YES  |     | NULL    |       |
| voucher_detail_list | varchar(1024) | YES  |     | NULL    |       |
+---------------------+---------------+------+-----+---------+-------+
22 rows in set (0.01 sec)

The code that cause the problem is as follows:

code:

public void insertIpnRecord(String outTradeNo, String notifyId, Map<String, String> params) {
        String sqlInsert = "INSERT INTO mytable(order_no,  " +
                "gmt_refund, gmt_close, fund_bill_list, voucher_detail_list) " +
                "VALUES(?,?,?,?,?);";
        
        getJdbcTemplate().update(sqlInsert,
                new Object[] {
                     outTradeNo,
                        params.get("gmt_refund")==null?"":new Timestamp(DatetimeUtil.getTheTime(params.get("gmt_refund"))),
                        params.get("gmt_close")==null?"":new Timestamp(DatetimeUtil.getTheTime(params.get("gmt_close"))),
                     params.get("fund_bill_list"),
                     params.get("voucher_detail_list")
                }
         );
    }

2. Solution

You can see that ,we are trying to insert one row to the table, the column ‘gmt_refund’ that cause the problem has a type as follows:

 gmt_refund          | timestamp     | YES  |     | NULL    |       |

It’s mysql timestamp type, and can be null.

We are trying to insert the column that maybe null:

params.get("gmt_refund")==null?"":new Timestamp(DatetimeUtil.getTheTime(params.get("gmt_refund"))),

So apparently the problem is caused by the default value when it’s null, we supplied an empty string, which is not comptatible with mysql Timestamp column type. We can solve the problem by changing the java code as follows:

params.get("gmt_refund")==null?null:new Timestamp(DatetimeUtil.getTheTime(params.get("gmt_refund"))),

The core idea is to change the default value of a Timestamp column from an empty string to a null value.

Now it works~

3. Summary

In this post, I demonstrated how to solve the com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: '' for column 'xxx' at row y problem when trying to insert a nullable timestamp field into mysql, the core solution is to provide a null value for the column. That’s it, thanks for your reading.