Transaction group database write failure resulted in successful transaction

I made a historical transaction, and messed up a datatype in the PLC on accident, which resulted in the timestamp not being in the correct format for the database.

So when the transaction group went to store that data in the database, it got an error saying the value for that column wasn’t valid. Makes sense.

But transaction groups still wrote back a successful handshake back to the PLC, and didn’t flag the execution as failed. Isn’t that the point, so that if the data doesn’t get successfully written to the database, it throws a flag and writes a failed handshake?

I did find the error in the logs, which is as it should be:

> DatasourceForwardTransaction	26Sep2018 20:00:13	Error forwarding data
> java.sql.BatchUpdateException: Data truncation: Incorrect datetime value: '659147-09-02 21:53:54.0' for column 'PLC_TimeStamp' at row 1

> at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

> at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)

> at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)

> at java.lang.reflect.Constructor.newInstance(Unknown Source)

> at com.mysql.cj.util.Util.handleNewInstance(Util.java:210)

> at com.mysql.cj.util.Util.getInstance(Util.java:185)

> at com.mysql.cj.util.Util.getInstance(Util.java:192)

> at com.mysql.cj.jdbc.exceptions.SQLError.createBatchUpdateException(SQLError.java:218)

> at com.mysql.cj.jdbc.ClientPreparedStatement.executeBatchSerially(ClientPreparedStatement.java:864)

> at com.mysql.cj.jdbc.ClientPreparedStatement.executeBatchInternal(ClientPreparedStatement.java:453)

> at com.mysql.cj.jdbc.StatementImpl.executeBatch(StatementImpl.java:839)

> at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297)

> at com.inductiveautomation.ignition.gateway.datasource.DelegatingStatement.executeBatch(DelegatingStatement.java:60)

> at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper$SRStatement.executeBatch(SRConnectionWrapper.java:735)

> at com.inductiveautomation.ignition.gateway.history.BasicHistoricalRecord.storeToConnection(BasicHistoricalRecord.java:257)

> at com.inductiveautomation.ignition.gateway.history.sinks.RecordDatasourceSink.storeDataToDatasource(RecordDatasourceSink.java:40)

> at com.inductiveautomation.ignition.gateway.history.sf.sinks.AbstractDatasourceSink.storeToDatasource(AbstractDatasourceSink.java:137)

> at com.inductiveautomation.ignition.gateway.history.sf.sinks.AbstractDatasourceSink.storeData(AbstractDatasourceSink.java:115)

> at com.inductiveautomation.ignition.gateway.history.sf.sinks.AggregateSink.storeData(AggregateSink.java:170)

> at com.inductiveautomation.ignition.gateway.history.forwarders.ForwarderThread.run(ForwarderThread.java:149)

> Caused by: com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Incorrect datetime value: '659147-09-02 21:53:54.0' for column 'PLC_TimeStamp' at row 1

> at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:104)

> at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:960)

> at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1116)

> at com.mysql.cj.jdbc.ClientPreparedStatement.executeBatchSerially(ClientPreparedStatement.java:843)

Here’s the status window of the transaction afterward:

So I’m wondering, why did transaction manager handle this as though it were successful? How can I simulate a bad transaction that actually would say failed?

The ‘Handshake’ for successful execution is somewhat misleading with default transaction group settings - since in the Advanced options, groups default to using Store and Forward. Thus, a “good” handshake is returned once the group has given its data to the (asynchronous) store and forward system. To guarantee an accurate handshake, you need to disable store and forward for the group.

2 Likes