HSQL Error in 7.8.2

Ignition
1

I am getting the below error in 7.8.2, which I would think is not possible since the move to SQLLite, it appears to be coming from a large record insert from SF engine. Any ideas?

[code]10:09:48 AM HSQLDataStore Error storing to data cache.

java.sql.BatchUpdateException: data exception: string data, right truncation; table: DATASTORE_DATA column: DATA[/code]

2

Hi,

Yes, the store and forward system still uses hsql, and it appears that it’s trying to store more than the data column allows (it is capped to 1mb). Do you think this is from a transaction group, or many tags in a single historical scan class? I’m just curious, as we haven’t seen this be a problem before, as far as I’m aware.

Since the column is technically a varbinary, I think we should bump the limit, as I think it will only use as much storage space as needed. In the mean time, you can alter the column type yourself. It’s not simple, but all the tools are available:

  1. Go to Store and Forward settings for your database. Edit them and disable the local cache. This will detach the data store file.
  2. Go to {InstallDir}\lib\core\gateway, and double click hsql-2.3.3.jar. A UI should appear.
  3. Select Type=Standalone, and in the URL, replace the placeholder with the full path to:
    {InstallDir}\data\datacache\DBNAME\DBNAME (the dbname is the folder and the name of the file, which is why it’s repeated twice).
    Username: SA
    Password: dstorepass
  4. Run:
alter table datastore_data alter column data varbinary(10485760);

then:

commit;
shutdown;

This would set it to 10mb.
5) Close the UI and go back to Ignition, and reenable the cache.

Let me know if you have any questions/problems.

Regards,

3

That makes a lot of sense. The error was getting generated from a gateway script that I have loading CSV log files from ~100 equipment into the DB, then they are processed using the instrument interface parse engine. As a temp fix I just switched to not use SF. But I like the idea of bumping up the limit anyways to avoid any future issues.

With the change to SQLite I assumed HSQL was done, is there future plans to migrate SF to SQLite as well?

4

Hi,

The particular problems we were having with HSQL haven’t seemed to show up in the way we use it for the data cache, but we might switch it for consistency. We may also focus on building something a little more purpose built for tag history, which could be based on SQLite, for 7.9.

Regards,