Historian provider create table error (MemoryForwardTransaction)

Ignition
1

Hello,

I’ve stumbled upon an odd behavior of an Historian provider. The historian provider is paired with an MS-SQL 2022 Standard database connection.

When the historian provider is enabled this error shows in the logs:

CREATE TABLE sqlt_data_1_2025_11 (tagid int,intvalue bigint,floatvalue double,stringvalue varchar(255),datevalue datetime,dataintegrity int,t_stamp bigint,PRIMARY KEY (tagid,t_stamp))

Seems that he wants to create a “double” column (floatvalue) which is not a valid type for MSSQL.

I’ve tried to recreate the database connection and the historian provider but without any luck.

DISCLAIMER:

This is a 8.3 restored gateway on a new machine and before the restore, everything was working accordingly.

Database and OS are the same versions used on the machine where the project was originally developed.

Full error log:

com.inductiveautomation.ignition.gateway.storeforward.exceptions.DataStorageException: java.lang.RuntimeException: java.lang.Exception: Error running query: CREATE TABLE sqlt_data_1_2025_11 (tagid int,intvalue bigint,floatvalue double,stringvalue varchar(255),datevalue datetime,dataintegrity int,t_stamp bigint,PRIMARY KEY (tagid,t_stamp))

at com.inductiveautomation.ignition.gateway.storeforward.sinks.AbstractDatasourceSink.onStorageFailure(AbstractDatasourceSink.java:261)

at com.inductiveautomation.sqlhistorian.gateway.storage.TagHistoryDatasourceSink.onStorageFailure(TagHistoryDatasourceSink.java:425)

at com.inductiveautomation.ignition.gateway.storeforward.sinks.AbstractDatasourceSink.storeToDatasource(AbstractDatasourceSink.java:213)

at com.inductiveautomation.sqlhistorian.gateway.storage.TagHistoryDatasourceSink.storeToDatasource(TagHistoryDatasourceSink.java:436)

at com.inductiveautomation.ignition.gateway.storeforward.sinks.AbstractDatasourceSink.storeData(AbstractDatasourceSink.java:118)

at com.inductiveautomation.ignition.gateway.storeforward.sinks.AbstractSink$SinkWriter.flush(AbstractSink.java:411)

at com.inductiveautomation.ignition.gateway.storeforward.sinks.AbstractSink$SinkWriter.close(AbstractSink.java:422)

at com.inductiveautomation.ignition.gateway.storeforward.engine.PipelineEngine$Pipeline$ForwardTask.run(PipelineEngine.java:1783)

at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)

at java.base/java.util.concurrent.FutureTask.run(Unknown Source)

at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source)

at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)

at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

at java.base/java.lang.Thread.run(Unknown Source)

Caused by: java.lang.RuntimeException: java.lang.Exception: Error running query: CREATE TABLE sqlt_data_1_2025_11 (tagid int,intvalue bigint,floatvalue double,stringvalue varchar(255),datevalue datetime,dataintegrity int,t_stamp bigint,PRIMARY KEY (tagid,t_stamp))

at com.inductiveautomation.ignition.common.functional.FragileConsumer.lambda$wrap$1(FragileConsumer.java:47)

at java.base/java.util.stream.ForEachOps$ForEachOp$OfRef.accept(Unknown Source)

at java.base/java.util.stream.SortedOps$SizedRefSortingSink.end(Unknown Source)

at java.base/java.util.stream.AbstractPipeline.copyInto(Unknown Source)

at java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(Unknown Source)

at java.base/java.util.stream.ForEachOps$ForEachOp.evaluateSequential(Unknown Source)

at java.base/java.util.stream.ForEachOps$ForEachOp$OfRef.evaluateSequential(Unknown Source)

at java.base/java.util.stream.AbstractPipeline.evaluate(Unknown Source)

at java.base/java.util.stream.ReferencePipeline.forEach(Unknown Source)

at com.inductiveautomation.sqlhistorian.gateway.storage.TagHistoryDatasourceSink.storeHistorySet(TagHistoryDatasourceSink.java:505)

at com.inductiveautomation.sqlhistorian.gateway.storage.TagHistoryDatasourceSink.storeToConnection(TagHistoryDatasourceSink.java:478)

at com.inductiveautomation.ignition.gateway.storeforward.sinks.AbstractDatasourceSink.storeToDatasource(AbstractDatasourceSink.java:196)

... 11 common frames omitted

Caused by: java.lang.Exception: Error running query: CREATE TABLE sqlt_data_1_2025_11 (tagid int,intvalue bigint,floatvalue double,stringvalue varchar(255),datevalue datetime,dataintegrity int,t_stamp bigint,PRIMARY KEY (tagid,t_stamp))

at com.inductiveautomation.ignition.gateway.db.schema.DBTableSchema.executeCommandSet(DBTableSchema.java:139)

at com.inductiveautomation.ignition.gateway.db.schema.DBTableSchema.createTable(DBTableSchema.java:108)

at com.inductiveautomation.ignition.gateway.db.schema.DBTableSchema.verifyAndUpdate(DBTableSchema.java:91)

at com.inductiveautomation.sqlhistorian.gateway.storage.partitions.AbstractDirectorPartitionManager.createPartitionTable(AbstractDirectorPartitionManager.java:175)

at com.inductiveautomation.sqlhistorian.gateway.storage.partitions.AbstractDirectorPartitionManager.queryOrCreatePartition(AbstractDirectorPartitionManager.java:112)

at com.inductiveautomation.sqlhistorian.gateway.storage.partitions.AbstractDirectorPartitionManager.getPartition(AbstractDirectorPartitionManager.java:60)

at com.inductiveautomation.sqlhistorian.gateway.storage.TagHistoryDatasourceSink.breakupByPartition(TagHistoryDatasourceSink.java:1327)

at com.inductiveautomation.sqlhistorian.gateway.storage.TagHistoryDatasourceSink.insertTagValues(TagHistoryDatasourceSink.java:1041)

at com.inductiveautomation.sqlhistorian.gateway.storage.TagHistoryDatasourceSink.storeHistorySet(TagHistoryDatasourceSink.java:529)

at com.inductiveautomation.sqlhistorian.gateway.storage.TagHistoryDatasourceSink.lambda$storeHistorySet$8(TagHistoryDatasourceSink.java:507)

at com.inductiveautomation.ignition.common.functional.FragileConsumer.lambda$wrap$1(FragileConsumer.java:45)

... 22 common frames omitted

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ','.

at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:265)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1662)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:898)

at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:793)

at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7417)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3488)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:262)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:237)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeUpdate(SQLServerStatement.java:733)

at org.apache.commons.dbcp2.DelegatingStatement.executeUpdate(DelegatingStatement.java:331)

at org.apache.commons.dbcp2.DelegatingStatement.executeUpdate(DelegatingStatement.java:331)

at com.inductiveautomation.ignition.gateway.datasource.DelegatingStatement.executeUpdate(DelegatingStatement.java:80)

at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper$SRStatement.executeUpdate(SRConnectionWrapper.java:867)

at com.inductiveautomation.ignition.gateway.db.schema.DBTableSchema.executeCommandSet(DBTableSchema.java:137)

... 32 common frames omitted
2

On the Connections > Databases > Settings page (http://localhost:8088/app/connections/databases/settings/drivers-jdbc), what 'translator' is the MSSQL driver configured to use?

If you go to the 'Translators' tab (http://localhost:8088/app/connections/databases/settings/translators), what values are listed in the 'Data Type Mapping' at the bottom of the MSSQL connection?

image
image830×342 16.7 KB

Did you restore a .gwbk disabled, by any chance?

3

Hi Paul,

First of all, yes it was restored with the “disable” option checked.

The MSSQL driver configuration had no “translator” selected, which turned out to be because all translators were disabled. After enabling them and confirming they were correctly set in the “drivers-jdbc” settings for each driver, I restarted the gateway, and the partition table was created successfully.

Now, I’d like your advice, since this isn’t the first issue I’ve encountered after restoring the gateway.

I understand that restoring with the “disable” option is somewhat unreliable at the moment and is expected to be fixed. Do you think that restoring the gateway again without the “disable” option would resolve these recurring issues?

4

Yes, if you restore again your state will be reset to whatever is in the .gwbk you're starting from. Assuming the reason you used 'restore disabled' in the first place was to prevent things writing out to other parts of your system unintentionally, you may want to first do that restoration in some network-isolated system and then individually 'disable' the relevant things yourself before resuming network connectivity.

2 Likes