# HSQL Error in 7.8.2

**URL:** <https://forum.inductiveautomation.com/t/hsql-error-in-7-8-2/11425>\
**Category:** Ignition\
**Created:** [March 28, 2016, 3:01pm UTC](https://forum.inductiveautomation.com/t/hsql-error-in-7-8-2/11425 "2016-03-28T15:01:14Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![ryanjmclaughlin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ryanjmclaughlin/32/4318_2.png) [@ryanjmclaughlin](https://forum.inductiveautomation.com/u/ryanjmclaughlin)\
**Post date:** [March 28, 2016, 3:01pm UTC](https://forum.inductiveautomation.com/t/hsql-error-in-7-8-2/11425/1 "2016-03-28T15:01:14Z")

</div>

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]

---

<div class="post-metadata">

**Author:** ![Colby.Clegg](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/colby.clegg/32/3282_2.png) [@Colby.Clegg](https://forum.inductiveautomation.com/u/Colby.Clegg)\
**Post date:** [April 22, 2016, 8:46pm UTC](https://forum.inductiveautomation.com/t/hsql-error-in-7-8-2/11425/2 "2016-04-22T20:46:48Z")

</div>

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:

```auto
alter table datastore_data alter column data varbinary(10485760);
```

then:

```auto
commit;
```

```auto
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,

---

<div class="post-metadata">

**Author:** ![ryanjmclaughlin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ryanjmclaughlin/32/4318_2.png) [@ryanjmclaughlin](https://forum.inductiveautomation.com/u/ryanjmclaughlin)\
**Post date:** [April 23, 2016, 12:58pm UTC](https://forum.inductiveautomation.com/t/hsql-error-in-7-8-2/11425/3 "2016-04-23T12:58:41Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![Colby.Clegg](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/colby.clegg/32/3282_2.png) [@Colby.Clegg](https://forum.inductiveautomation.com/u/Colby.Clegg)\
**Post date:** [April 25, 2016, 3:32pm UTC](https://forum.inductiveautomation.com/t/hsql-error-in-7-8-2/11425/4 "2016-04-25T15:32:18Z")

</div>

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,