Excel report causing H2 error code 50100

[balanceBryce]

Hello,

I've been trying to set up an excel report on our Mango system but am mostly getting this error:

ERROR 2018-10-29T12:26:28,740 (com.infiniteautomation.mango.excelreports.rt.ExcelReportWorkItem.execute:705) - PreparedStatementCallback; uncategorized SQLException; SQL state [HYC00]; error code [50100]; Feature not supported: "Index on BLOB or CLOB column: DATA4 CLOB"; SQL statement:
(select pv.dataPointId, pv.dataType, pv.pointValue, pva.textPointValueShort, pva.textPointValueLong, pv.ts, pva.sourceMessage from pointValues pv   left join pointValueAnnotations pva on pv.id = pva.pointValueId where pv.dataPointId=? AND pv.ts <= ? ORDER BY ts DESC LIMIT 1)  UNION (select pv.dataPointId, pv.dataType, pv.pointValue, p
org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException; SQL state [HYC00]; error code [50100]; Feature not supported: "Index on BLOB or CLOB column: DATA4 CLOB"; SQL statement:
(select pv.dataPointId, pv.dataType, pv.pointValue, pva.textPointValueShort, pva.textPointValueLong, pv.ts, pva.sourceMessage from pointValues pv   left join pointValueAnnotations pva on pv.id = pva.pointValueId where pv.dataPointId=? AND pv.ts <= ? ORDER BY ts DESC LIMIT 1)  UNION (select pv.dataPointId, pv.dataType, pv.pointValue, pva.textPointValueShort, pva.textPointValueLong, pv.ts, pva.sourceMessage from pointValues pv   left join pointValueAnnotations pva on pv.id = pva.pointValueId where pv.dataPointId=? AND pv.ts <= ? ORDER BY ts DESC LIMIT 1)  UNION (select pv.dataPointId, pv.dataType, pv.pointValue, pva.textPointValueShort, pva.textPointValueLong, pv.ts, pva.sourceMessage from pointValues pv   left join pointValueAnnotations pva on pv.id = pva.pointValueId where pv.dataPointId=? AND pv.ts <= ? ORDER BY ts DESC LIMIT 1)  [50100-196]; nested exception is org.h2.jdbc.JdbcSQLException: Feature not supported: "Index on BLOB or CLOB column: DATA4 CLOB"; SQL statement:
(select pv.dataPointId, pv.dataType, pv.pointValue, pva.textPointValueShort, pva.textPointValueLong, pv.ts, pva.sourceMessage from pointValues pv   left join pointValueAnnotations pva on pv.id = pva.pointValueId where pv.dataPointId=? AND pv.ts <= ? ORDER BY ts DESC LIMIT 1)  UNION (select pv.dataPointId, pv.dataType, pv.pointValue, pva.textPointValueShort, pva.textPointValueLong, pv.ts, pva.sourceMessage from pointValues pv   left join pointValueAnnotations pva on pv.id = pva.pointValueId where pv.dataPointId=? AND pv.ts <= ? ORDER BY ts DESC LIMIT 1)  UNION (select pv.dataPointId, pv.dataType, pv.pointValue, pva.textPointValueShort, pva.textPointValueLong, pv.ts, pva.sourceMessage from pointValues pv   left join pointValueAnnotations pva on pv.id = pva.pointValueId where pv.dataPointId=? AND pv.ts <= ? ORDER BY ts DESC LIMIT 1)  [50100-196]
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:89) ~[spring-jdbc-5.0.8.RELEASE.jar:5.0.8.RELEASE]
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-5.0.8.RELEASE.jar:5.0.8.RELEASE]
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-5.0.8.RELEASE.jar:5.0.8.RELEASE]
        at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1402) ~[spring-jdbc-5.0.8.RELEASE.jar:5.0.8.RELEASE]
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:620) ~[spring-jdbc-5.0.8.RELEASE.jar:5.0.8.RELEASE]
        at com.serotonin.m2m2.db.dao.PointValueDaoSQL.wideBookendQuery(PointValueDaoSQL.java:1056) ~[mango-3.5.1.jar:?]
        at com.infiniteautomation.mango.excelreports.rt.ExcelReportWorkItem.execute(ExcelReportWorkItem.java:586) ~[?:?]
        at com.serotonin.m2m2.rt.maint.BackgroundProcessingImpl$WorkItemRunnable.run(BackgroundProcessingImpl.java:676) ~[mango-3.5.1.jar:?]
        at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) ~[?:1.8.0_181]
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) ~[?:1.8.0_181]
        at java.lang.Thread.run(Unknown Source) [?:1.8.0_181]
Caused by: org.h2.jdbc.JdbcSQLException: Feature not supported: "Index on BLOB or CLOB column: DATA4 CLOB"; SQL statement:
(select pv.dataPointId, pv.dataType, pv.pointValue, pva.textPointValueShort, pva.textPointValueLong, pv.ts, pva.sourceMessage from pointValues pv   left join pointValueAnnotations pva on pv.id = pva.pointValueId where pv.dataPointId=? AND pv.ts <= ? ORDER BY ts DESC LIMIT 1)  UNION (select pv.dataPointId, pv.dataType, pv.pointValue, pva.textPointValueShort, pva.textPointValueLong, pv.ts, pva.sourceMessage from pointValues pv   left join pointValueAnnotations pva on pv.id = pva.pointValueId where pv.dataPointId=? AND pv.ts <= ? ORDER BY ts DESC LIMIT 1)  UNION (select pv.dataPointId, pv.dataType, pv.pointValue, pva.textPointValueShort, pva.textPointValueLong, pv.ts, pva.sourceMessage from pointValues pv   left join pointValueAnnotations pva on pv.id = pva.pointValueId where pv.dataPointId=? AND pv.ts <= ? ORDER BY ts DESC LIMIT 1)  [50100-196]
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:345) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.message.DbException.get(DbException.java:179) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.message.DbException.get(DbException.java:155) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.message.DbException.getUnsupportedException(DbException.java:216) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.index.BaseIndex.checkIndexColumnTypes(BaseIndex.java:79) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.index.TreeIndex.<init>(TreeIndex.java:37) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.table.RegularTable.addIndex(RegularTable.java:254) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.result.ResultTempTable.createIndex(ResultTempTable.java:115) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.result.ResultTempTable.<init>(ResultTempTable.java:70) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.result.LocalResult.addRow(LocalResult.java:305) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.command.dml.Select.queryFlat(Select.java:523) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.command.dml.Select.queryWithoutCache(Select.java:625) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.command.dml.Query.queryWithoutCacheLazyCheck(Query.java:114) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.command.dml.Query.query(Query.java:371) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.command.dml.Query.query(Query.java:333) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.command.dml.SelectUnion.queryWithoutCache(SelectUnion.java:229) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.command.dml.Query.queryWithoutCacheLazyCheck(Query.java:114) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.command.dml.Query.query(Query.java:347) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.command.dml.Query.query(Query.java:333) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.command.CommandContainer.query(CommandContainer.java:113) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.command.Command.executeQuery(Command.java:201) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.jdbc.JdbcPreparedStatement.execute(JdbcPreparedStatement.java:200) ~[h2-1.4.196.jar:1.4.196]
        at com.serotonin.m2m2.db.dao.PointValueDaoSQL$BookendSinglePointValuesPreparedStatementCreator.doInPreparedStatement(PointValueDaoSQL.java:918) ~[mango-3.5.1.jar:?]
        at com.serotonin.m2m2.db.dao.PointValueDaoSQL$BookendSinglePointValuesPreparedStatementCreator.doInPreparedStatement(PointValueDaoSQL.java:862) ~[mango-3.5.1.jar:?]
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:605) ~[spring-jdbc-5.0.8.RELEASE.jar:5.0.8.RELEASE]
        ... 6 more

Previously I was able to get around this with 2 of the 8 data points I want to bring into the Excel Report (so we were only populating a fraction of the report) but now no amount of exclusion gets rid of the error. I've checked the H2 error 50100 and this corresponds to an unsupported SQL feature.

I've tried to diagnose the difference between those two points and the other six but can't figure it out.

Look forward to hearing back.

Bryce

[phildunlap]

Hi Bryce,

I have seen this before: https://forum.infiniteautomation.com/topic/3617/excel-report

We moved to email and he sent in his H2 database backup and logs and template and I couldn't reproduce the issue. I'm not sure if he ever attempted to do a database backup / restore cycle when it was recommended, as one would if they were shrinking the database. So, maybe you can let us know if that solves it?

[balanceBryce]

Hi Phil,

Sorry for the late reply, I got pulled onto another project and am only just getting back to this issue.

I have tried running the SQL database backup built in to Mango (Administration>System Settings> SQL database backup > BACKUP NOW) and get the following error:

WARN 2019-01-17T16:43:36,408 (com.serotonin.m2m2.rt.maint.work.DatabaseBackupWorkItem.execute:203) - org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [SCRIPT DROP TO 'c:\Users\admin\Downloads\free-m2m2-core-3.4.4\backup\core-database-H2-Jan-17-2019_164317.zip' COMPRESSION ZIP; ]; SQL state [90030]; error code [90030]; File corrupted while reading record: "index not found 228". Possible solution: use the recovery tool; SQL statement:SCRIPT DROP TO 'c:\Users\admin\Downloads\free-m2m2-core-3.4.4\backup\core-database-H2-Jan-17-2019_164317.zip' COMPRESSION ZIP; [90030-196]; nested exception is org.h2.jdbc.JdbcSQLException: File corrupted while reading record: "index not found 228". Possible solution: use the recovery tool; SQL statement:SCRIPT DROP TO 'c:\Users\admin\Downloads\free-m2m2-core-3.4.4\backup\core-database-H2-Jan-17-2019_164317.zip' COMPRESSION ZIP; [90030-196]

What is the recovery tool mentioned?

Bryce

[phildunlap]

What is the recovery tool mentioned?

That would be the H2 recovery tool. I've not used it before, so I'm not sure how it would help.

It sounds like you have Mango running, so perhaps on the /sqlConsole.shtm page (or the /ui/administration/sql-console page) you can run.

drop index if exists "DATA4";

Do you have any existing core database backups in the Mango/backup directory? You could move your existing Mango/databases/ directory while Mango is off, then try restoring an older backup via the system settings page. If that works you could move in your old mangoTSDB directory to keep your data.

[balanceBryce]

@phildunlap

Thanks for getting back to me.

It sounds like you have Mango running, so perhaps on the /sqlConsole.shtm page (or the /ui/administration/sql-console page) you can run.

drop index if exists "DATA4";

I just gave this a try but it didn't seem like DATA4 existed. If it did, deleting it didn't resolve the issue.

Do you have any existing core database backups in the Mango/backup directory? You could move your existing Mango/databases/ directory while Mango is off, then try restoring an older backup via the system settings page. If that works you could move in your old mangoTSDB directory to keep your data.

Unfortunately I didn't pick up this issue for a while so our latest backup is from 03/12/18. I will try using the recovery tool and hopefully that resolves unless you have a different idea?

Bryce

[phildunlap]

I would unzip the backup and check the SQL file inside for DATA4. That's what I did in the other thread when he sent in his backup and I didn't find any references. I restored it and didn't encounter the issue. I would advise that you restore the backup into a clean database to check it out (move Mango/databases when Mango is off so that it will start on a new database) and perhaps keep a JSON configuration export and output of SELECT id, xid FROM dataPoints; on hand, in case you need to restore via JSON rather than the database backup.